实验二EXCEL复杂报表.docVIP

实验二 EXCEL 复杂报表实验（一） 2-1 职工工资报表实验 一、实验任务 某销售部门已有三张原始数据表：销售部员工资料表，考勤记录表，销售业绩表。 １、利用EXCEL建立工作簿“销售部信息.xls”，将三张原始数据表的内容录入，如图一、图二、图三所示。 ２、在“销售部信息.xls”中添加一张新工作表命名为“工资表”，并完成该工作表中数据的计算和填充。 ３、实验完成后提交工作簿“销售部信息.xls”，及其中所有的工作表和数据。 图一 销售部员工资料表 图二 考勤记录表 图三 销售业绩表  图四 工资表 其中： 1、基本工资 ＝600＋职称工资 （其中职称工资是：专科以下＝0 ，专科＝400，本科＝800，硕士＝1200，博士＝1600，提示使用函数match（）） 2、收入提成是销售业绩表中 数量×100×5％ 3、应扣请假费信息在考情记录表中，迟到、早退一次扣30，旷工一次扣200 4、加班费信息在考情记录表中，加班一次100 5、住房补贴＝120；三金＝85 二、填充“工资表”的操作步骤： 1、填充“员工编号”字段 ： 选择工资表A3单元格,填入公式“=销售部员工资料表!A3”,得到第一个员工编号，用填充柄将以下的单元格进行填充，完成员工编号字段。 2、填充姓名字段： 选择工资表B3单元格，填入公式“=销售部员工资料表!B3”，得到第一个员工姓名，后用填充柄将以下单元格进行填充，完成姓名字段。 3、填充基本工资字段， 已知基本工资 ＝600＋职称工资 （其中职称工资是：专科以下＝0 ，专科＝400，本科＝800，硕士＝1200，博士＝1600） 职称工资 ：专科以下＝400×0、专科＝400×1、本科＝400×2、硕士＝400×3，博士＝400×4。 方法一： 选择工资表C3单元格，填入公式“=600+(MATCH(销售部员工资料表!D3,{专科以下,专科,本科,硕士,博士},0)-1)*400 ”，得到第一个基本工资，用填充柄将以下单元格进行填充，完成基本工资字段。 【函数使用提示】 MATCH(lookup_value,lookup_array,match_type) 函数。其中match_type＝0时 返回指定数值匹配的数组中元素相应的位置。 若使用函数：(MATCH(销售部员工资料表!D3,{专科以下,专科,本科,硕士,博士},0)”，则该函数返回值为： 专科以下＝1,专科＝2,本科＝3,硕士＝4,博士＝5，所以（该函数－1）×400可得到各职称的职称工资。 方法二： （１）新添加一张工作表命名为“职称工资表”，计算出每个人的职称工资； （２）通过职称工资表的值计算出工作表中基本工资的值。生成职称工资表，包含(员工编号,学历,职称工资)三个字段，前两个可以由销售部员工资料表得到，职称工资和学历有关系， （３）选择C3单元格，填入职称工资公式“＝ (IF(B3=专科以下,0,IF(B3=专科,1,IF(B3=本科,2,IF(B3=硕士,3,4)))))*400”，用填充柄将以下单元格进行填充，完成收入提成字段。 4、填充收入提成字段，已知收入提成是销售业绩表中 数量×100×5％ 选择工资表D3单元格，填入公式“=销售业绩表!D3*100*5%”，得到一个收入提成，然后用填充柄将以下单元格进行填充，完成收入提成字段。 5、完成住房补贴，三金两个字段，已知住房补贴＝120；三金＝85。 6、填充应扣请假费字段，“考勤记录表”中D、E两列对应迟到、早退，F列对应旷工，迟到、早退一次扣30，旷工一次扣200。 在工资表的F3单元格填入公式＝(考勤记录表!D3+考勤记录表!E3)*30+考勤记录表!F3*200，得到第一个应扣请假费，用填充柄将以下单元格进行填充，完成应扣请假费字段。 7、填充加班费字段，在考情记录表中，加班一次得100。 选择工资表H3单元格，填入公式“=考勤记录表!G3*100”，得到第一个加班费，用填充柄将以下单元格进行填充，完成加班费字段。 8、填充实发工资字段，实发工资＝基本工资＋收入提成＋住房补贴－应扣请假费＋三金＋加班费， 选择工资表I3单元格，填入公式“=C3+D3+E3-F3-G3+H3”，得到第一个实发工资，用填充柄将以下单元格进行填充，完成实发工资字段。 注意：在输入公式时，所有标点必须是半角，英文标点，否则会出现错误结果。 相关函数参考：员工工资表.XLS 三、“工资表”样张