实验二EXCEL复杂报表.doc

实验二 EXCEL 复杂报表实验（一）2-1 职工工资报表实验一、实验任务某销售部门已有三张原始数据表：销售部员工资料表，考勤记录表，销售业绩表。、利用EXCEL建立工作簿“销售部信息.xls”，将三张原始数据表的内容录入，如图一、图二、图三所示。、在“销售部信息.xls”中添加一张新工作表命名为“工资表”，并完成该工作表中数据的计算和填充。、实验完成后提交工作簿“销售部信息.xls”，及其中所有的工作表和数据。图一 销售部员工资料表图二 考勤记录表图三 销售业绩表图四 工资表其中：1、基本工资 600职称工资 （其中职称工资是：专科以下0 ，专科400，本科800，硕士1200，博士1600，提示使用函数match（）2、收入提成是销售业绩表中 数量10053、应扣请假费信息在考情记录表中，迟到、早退一次扣30，旷工一次扣2004、加班费信息在考情记录表中，加班一次1005、住房补贴120；三金85 二、填充“工资表”的操作步骤：1、填充“员工编号”字段 ：选择工资表A3单元格,填入公式“=销售部员工资料表!A3”,得到第一个员工编号，用填充柄将以下的单元格进行填充，完成员工编号字段。2、填充姓名字段：选择工资表B3单元格，填入公式“=销售部员工资料表!B3”，得到第一个员工姓名，后用填充柄将以下单元格进行填充，完成姓名字段。3、填充基本工资字段，已知基本工资 600职称工资 （其中职称工资是：专科以下0 ，专科400，本科800，硕士1200，博士1600）职称工资 ：专科以下4000、专科4001、本科4002、硕士4003，博士4004。方法一：选择工资表C3单元格，填入公式“=600+(MATCH(销售部员工资料表!D3,专科以下,专科,本科,硕士,博士,0)-1)*400 ”，得到第一个基本工资，用填充柄将以下单元格进行填充，完成基本工资字段。【函数使用提示】 MATCH(lookup_value,lookup_array,match_type) 函数。其中match_type0时 返回指定数值匹配的数组中元素相应的位置。若使用函数：(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、填充收入提成字段，已知收入提成是销售业绩表中 数量1005选择工资表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”，得到第一个实发工资