Excel合并计算应用.docx

Excel合并计算应用.docx

《Excel合并计算应用.docx》由会员分享，可在线阅读，更多相关《Excel合并计算应用.docx（16页珍藏版）》请在冰豆网上搜索。

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-16RANK函数“参数设置”对话框

（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.建立如下图所示的“成绩单”工作表，其功能是输入学生的学号，即可得到该生的成绩、排名及奖学金的数据。