excel合并计算应用.docVIP

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(C35,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. 计算四个季度的平均考核分数 ??? 我们使用Exc