[复习]Excel合并计算应用

[复习]Excel合并计算应用 Excel合并计算应用 实验目的 1. 掌握使用Excel进行考勤核算和奖惩核算的基本方法。 2. 掌握条件判断函数IF()的使用方法。 3. 掌握首列查找和引用函数VLOOKUP()的使用方法。 4. 掌握排位函数RANK()的使用方法。 5. 掌握Excel的合并运算功能。 实验内容 假设公司每个季度进行考核评比，计算评比分数，在年终评比时作汇总工作，依评比分数作 年终考核排名，依评比等级发给年终奖金、礼品并调整工资作为奖惩。我们使用Excel进行考勤核算和奖惩核算。 一、建立员工资料 1. 首先我们建立“员工资料表”工作表，该工作表存放员工的基本信息。如图6-1所示。 图6-1 员工资料表 2. 为以后书写方便，定义“员工资料表”A3:F16单元格区域名称为“员工资料表”。 (1)选取A3:F16单元格区域。 (2)单击菜单命令【插入】?【名称】?【定义】。 (3)在打开的“定义名称”对话框中输入名称和引用位置。如图6-2所示。 图6-2 定义名称“员工资料表” 二、建立缺勤记录表 1. 建立“缺勤记录表”工作表，该工作表记录每个员工一季度至四季度中缺勤的天数，以便年终评比参考使用。如图6-3所示。 图6-3 缺勤记录表 2. 为以后书写方便，定义“缺勤记录表”A3:F16单元格区域名称为“缺勤记录表”。 (1)选取A3:F16单元格区域。 (2)单击菜单命令【插入】?【名称】?【定义】。 (3)在打开的“定义名称”对话框中输入名称和引用位置。如图6-4所示。 图6-4 定义名称“缺勤记录表” 三、建立季度考勤核算表 建立季度考勤核算表时，我们预设公司的出勤总天数为70天。若请假或旷工一天就扣1，当缺勤超过5天时将自动显示“警告”两个字;若缺勤天数超过70天时，将自动显示“开除” 两个字，否则就显示抵扣后的出勤天数。如图6-5所示。 图6-5 输入第一个职工的记录 1. 输入查询姓名的公式 B3单元格公式:=VLOOKUP(A3,员工资料表,2) 说明:以员工编号字段为关键字在“员工资料表”中查询，返回查找到的“员工资料表”中的该行的第2列的值，即“姓名”字段。 2. 输入计算缺勤天数的公式 C3单元格公式:=VLOOKUP(A3,缺勤记录表,3) 说明:以员工编号字段为关键字在“缺勤记录表”中查询，返回查找到的“缺勤记录表”中的该行的第3列的值，即“缺勤天数”字段。 3. 输入计算出勤天数的公式 D3单元格公式:=IF(C3<5,70-C3,IF(C3>=50,"开除","警告")) 说明:当缺勤天数不足5天时，显示扣抵后的出勤天数;当缺勤天数超过5天时，自动显示“警告”;当缺勤天数超过50天时，自动显示“开除”。 4. 复制以上公式到其他员工记录 选中B3:D3单元格区域，使用填充柄向下复制到B16:D16。如图6-6所示。 图6-6 复制公式完毕 5. 计算考核成绩 季度考核成绩由出勤天数(占20%)和主管评分(占80%)两部分组成。 (1)F3单元格公式:=(70-C3)*0.2+E3*0.8 (2)使用填充柄将F3中的公式复制到该列其下单元格。如图6-7所示。 图6-7 复制后的F列 6. 用类似的步骤建立第二、三、四季度的“季度考勤核算表”。如图6-8、图6-9、图6-10所示。 图6-8 二季度考勤核算表 图6-9 三季度考勤核算表 图6-10 四季度考勤核算表 四、建立年度考勤核算表 1. 建立如图6-11所示的“年度考勤核算表”。 图6-11 年度考勤核算表 2. 查询员工资料 (1)输入查询姓名的公式 B3单元格公式:=VLOOKUP(A3,员工资料表,2) 说明:以员工编号字段为关键字在“员工资料表”中查询，返回查找到的“员工资料表”中的该行的第2列的值，即“姓名”字段。 (2) 输入查询基本工资的公式 C3单元格公式:=VLOOKUP(A3,员工资料表,5) 说明:以员工编号字段为关键字在“员工资料表”中查询，返回查找到的“员工资料表”中的该行的第5列的值，即“基本工资”字段。 (3)选取B3:C3单元格区域，使用填充柄复制B3和C3中的公式到其下单元格。如图6-12所示。 图6-12 复制公式后的B列和C列 3. 计算四个季度的平均考核分数 我们使用Excel的“合并计算”功能，计算四个季度的季度成绩平均值。 (1)选择D3单元格，选择菜单命令【数据】?【合并计算】，打开如图6-13所示的“合并计算”对话框。 (2)在“函数”下拉列表框中选择“平均值”函数。如图6-13所示。 (3)单击“引用位置”右边的折叠对话框按钮，选取“一季度核算”工作表中的F3:F16单元格区域后，单击折叠对话框按钮返回“合并计算”对话框。如图6-13所示。 图6-13 “合并计算”对话框 (4)单击“添加”按钮，将“一季度核算!$F$3:$F$16”单元格区域添加到计算区域中。“一季度核算!$F$3:$F$16”即显示在“所有引用区域”列表框中。 (5)依步骤(3)和步骤(4)，将二、三、四季度的数据加入到计算区域中。如图6-14所示。 图6-14 在计算区域中添加二、三、四季度的数据 (6)四个季度的数据全部加入后，单击“确定”按钮，即可完成合并计算。计算结果如图6-15所示。 图6-15 合并计算的计算结果 4. 建立成绩排名 计算每个员工的成绩排名，我们使用RANK()函数。 (1)E3单元格公式:=RANK(D3,D$3:D$16) 函数参数设置如图6-16所示。 图6-16 RANK函数“参数设置”对话框 (2)使用填充柄将E3中的公式复制到其下单元格。计算结果如图6-17所示。 图6-17 输入“考核排名”公式 5. 计算评比等级 假设年终以季平均成绩将员工分为优、良、中、差4个等级，评比的结果将影响年终奖金的 多寡、年终礼品的好坏以及工资结构的调整。 等级评比如下: 考勤分数 评比等级 90分-100分 优 85分-90分 良 80分-84分 中 0分-79分 差 我们使用条件判断函数IF()计算评比等级。 (1)F3单元格公式:=IF(D3>=90,"优",IF(D3>=85,"良",IF(D3>=80,"中","差"))) (2)使用填充柄将F3中的公式复制到其下单元格。计算结果如图6-18所示。 图6-18 输入“评比级别”公式 五、查询评比奖励数据 1. 建立如图6-19所示的“评比奖励”工作表 图6-19 “评比奖励”工作表 评比奖励说明: 若员工的评比级别为“优”，年终奖金=基本工资*(1+1/2)，年终礼品=香港旅游，调整工资=基本工资*(1+5%)。其余依次类推。 2. 计算年终奖金 G3单元格公式=C3*(1+VLOOKUP(F3,评比奖励!$A$2:$D$5,2,0)) 3. 计算年终礼品 H3单元格公式=VLOOKUP(F3,评比奖励!$A$2:$D$5,3,0) 4. 计算调整工资 I3单元格公式=C3*(1+VLOOKUP(F3,评比奖励!$A$2:$D$5,4,0)) 5. 使用填充柄将G3、H3、I3中的公式复制到其下单元格。计算结果如图6-20所示。 图6-20 输入“年终奖金”、“年终礼品”和“调整工资”三列的公式 下载本实验中用到的文件: 实验6.xls 习题6.xls 思考与练习 打开“习题6.xls”，其中包含以下成绩表: 1. 将每一位学生3次考试的成绩，利用平均函数合并计算至“学年成绩汇总”工作表中。 2. 在“学年成绩汇总”工作表中，使用函数计算每个学生的总分和平均分。 3. 在“学年成绩汇总”工作表中，按总分计算每个学生的排名。 4. 参照以下条件，计算符合条件的同学应该领取的奖学金金额。 平均分 奖学金 90-100分 2万元 80-89分 1万元 70-79分 3千元 5. 建立如下图所示的“成绩单”工作表，其功能是输入学生的学号，即可得到该生的成绩、排名及奖学金的数据。