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

实训五--Excel综合实训——制作教工情况表(原版) 实训五--Excel综合实训——制作教工情况表(原版) PAGE 实训五--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）打开文件，在“水电费”列前增加“补贴”和“应发工资”列，在最后增加“科室”列，在第一行前增加标题行。 提示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号、加粗； 数据标签值：Times New Roman，12号、加粗； 图例：宋体、12号； 扇形系列颜色：海绿、玫瑰红、象牙色、天蓝。 图4-5-9 实发工资分段统计 提示1：使用FREQUENCY函数计算各分段人数，分段点如图所示； 提示2：选择A16:A20,C16:C20区域作为图表数据源。 4、统计各职称平均实发工资并保护工作表 （1）在“数据分析”工作表中以“职称”为关键