实训五--Excel综合实训——制作教工情况表(原版)

实训五Excel综合实训——制作教工情况表一、情景描述下表是广美学院艺术系教工6月份的工资表的部分数据，请你协助该系财务处的会计根据每位教工的职称输入补贴、应发工资及实发工资，然后根据工资表作数据分析统计及制作图表。图4-5-1教工工资表选择输入教工的补贴，计算应发工资及实发工资。图4-5-2在下拉菜单中选择输入补贴设置工资表的行高、列宽、对齐方式、字体、边框及底纹等，结果如图4-5-3所示。图4-5-3格式化工资表统计出实发工资在1000以下、1200以下、1400以下及1400以上的人数，然后根据人数制作饼图，如图4-5-3所示。图4-5-4实发工资分布图分职称统计实发工资的平均值，如图4-5-3所所示，然后保护该工作表。图4-5-3各职称的平均实发工资二、任务分析为了顺利完成本次工作任务，我们除了需要巩固复习工作表的编辑与格式化及工作表的管理、公式与函数的使用、图表的建立与编辑、数据分析与管理等基本知识外，还需要具备以下知识。条件格式的使用。设置输入数据的有效性。使用自定义序列排序。工作表的保护。三、任务分解将本次工作任务分为以下4个子任务。子任务1将工资表内容补充完整并格式化工资表子任务2工作表的复制与管理子任务3制作实发工资分布情况图子任务4统计各职称平均实发工资并保护工作表四、操作步骤1、将工资表内容补充完整并格式化工资表（1）打开EXLX05.xls文件，在“水电费”列前增加“补贴”和“应发工资”列，在最后增加“科室”列，在第一行前增加标题行。提示1：选择F~G列可同时插入两列；提示2：插入行后，合并A1:J1；（2）为“补贴”列设置数据有效性并输入，补贴发放标准为：教授为110元，副教授为90元，讲师为70元，助讲为50元，无职称为10元。单元格提供下拉菜单，供选择输入；输入信息标题及输入信息如图4-5-2所示；出错警告样式为“停止”、出错信息为“不是“补贴”值！”，如图4-5-4所示。图4-5-4数据有效性的输入出错的提示信息步骤1：选择F3:F15区域，执行“数据”菜单中的“有效性”命令；步骤2：按照图4-5-5、4-5-6、4-5-7所示分别设置有效性条件、输入信息及出错警告；图4-5-5数据有效性的有效性条件图4-5-6数据有效性的输入信息图4-5-7数据有效性的出错警告步骤3：按照图4-5-2所示选择输入每位教工的补贴值。（3）使用公式计算应发工资和实发工资，公式如下：应发工资=基本工资+补贴实发工资=应发工资-水电费，然后并使用函数根据编号计算科室，编号的第一个字母为“A”即为“基础室”、为“B”即为“计算机室”、为“C”即为“电子室”。提示：在J3单元格输入如下公式，计算科室：=IF(LEFT(A3,1)=A,基础室,IF(LEFT(A3,1)=B,计算机室,电子室))然后将公式向下填充。（4）按照下列要求设置工资表格式（1~3步骤略）：1、标题行行高30、表头行行高20、其余行高18；所有列调整为最适合的列宽；2、字体：标题华文中宋、18号、加粗，表头楷体、12号、加粗；3、边框与底纹：外边框蓝色最粗实线，内竖线红色虚线、内横线黑色实线，第1行下边线双实线；标题底纹茶色；4、条件格式：实发工资中大于等于1500的单元格底纹设置为黄色。步骤1：选择I3:I15区域，执行“格式”菜单中的“条件格式”命令；步骤2：如图4-5-8所示设置条件，然后单击“格式”按钮，设置“图案”选项卡中的底纹颜色为黄色，确定。图4-5-8设置条件格式2、工作表的复制与管理将Sheet1工作表中的数据复制到Sheet2、Sheet3中，并分别将三个工作表标签重命名为“工资表”、“数据统计”、“数据分析”。3、制作实发工资分布情况图在“数据统计”工作表的以A16为左上角的区域建立如图4-5-9所示的实发工资分段统计数据表，用学过的函数统计各分段的人数，结果如图所示，然后根据分段情况和人数建立如图4-5-4所示的嵌入式分离型三维饼图，格式要求如下：图表标题：楷体、16号、加粗；数据标签值：TimesNewRoman，12号、加粗；图例：宋体、12号；扇形系列颜色：海绿、玫瑰红、象牙色、天蓝。图4-5-9实发工资分段统计提示1：使用FREQUENCY函数计算各分段人数，分段点如图所示；提示2：选择A16:A20,C16:C20区域作为图表数据源。4、统计各职称平均实发工资并保护工作表（1）在“数据分析”工作表中以“职称”为关键字按照教授、副教授、讲师、助讲、无职称的顺序排序步骤1：执行“工具”菜单中的“选项”命令，在“自定义序列”选项卡中输入如图4-5-10所示的序列，然后单击“添加”，确定；图4-5-10在自定义序列选项卡中添加序列步骤2：单击数据清单中任意单元格，执行“数据”菜单中的“排序”命令；步骤3：单击“排序”对话框中的“选项”按钮，弹出“排序选项”对话框，在“自定义排序次序”下拉菜单中选择刚刚定义的新序列，确定；步骤4：在主关键字中选择“职称”，方式为“升序”，确定；（2）分职称汇总实发工资的平均值。提示：分类字段为“职称”，汇总方式为“平均值”，汇总项为“实发工资”。（3）保护工作表“数据分析”，设定取消保护密码为1324。步骤1：执行“工具”菜单中“保护”子菜单的“保护工作表”命令；步骤2：在“取消工作表保护时使用的密码”下输入“1324”。五、实训小结在本次实训中我们将制作教工情况表这项工作分解为4个子任务，通过完善工资表的内容并修饰工作表，将工资表内容复制到新的工作表并对三个工作表进行管理，根据统计出的实发工资各段人数制作实发工资分布情况图表，最后根据职称排列统计各职称的平均实发工资，完成了本次任务。通过这次实训，巩固复习了工作表的编辑与格式化、工作表管理、图表的建立与编辑、数据管理与分析等知识，同时也学习了使用条件格式、设置数据有效性、使用自定义序列排序及保护工作表等新知识。六、课后拓展练习仿照这次实训的内容，建立班上部分同学的基本信息表，调查他们的每月生活费的使用情况（可包含生活费、手机电话费、饭卡消费、日常用品消费等），然后根据日常用品消费数额的不同统计各段人数，再根据人数制作“学生日常消费统计分布图”，最后统计出男、女生每月手机电话费的平均值。