信息技术基础模块5 项目3 工资管理表

模块四WPS表格管理分析

数据处理，让复杂变得简单工资管理表项目三导言：刘文是某公司的财务人员，他需要利用WPS表格制作一份员工的工资表。本项目主要包括以下几项内容。

新建工作簿重命名工作表导入外部数据DATEDIF、ROUND、VLOOKUP、IF函数的使用公式的使用制作数据透视表制作数据透视图本项目效果如图4-3-1所示，样例文件位于“教学资源包\主教材\模块四\4-3”文件夹中。图4-3-1项目效果任务1创建工作簿任务一：创建工作簿任务要求：本任务要求掌握WPS表格创建工作簿的方法，以及导入外部数据的方法。（1）启动WPS，新建一份空白工作簿，命名为“工资管理表”，并将工作簿中的“Sheet1”工作表重命名为“工资基础信息”。（2）选中“工资基础信息”工作表。单击【数据】→【导入数据】→【直接打开数据】→【选择数据源】命令，打开“导入文本文件”对话框，找到“员工信息”文件。如图4-3-2所示。图4-3-2“导入文件”对话框（3）单击“导入”按钮，在“文本编码”处选中“其他编码”，再选择“简体中文GBK”。（4）单击“下一步”按钮，在“原始数据类型”处选中“固定宽度”单选按钮；在“导入起始行”文本框中保持默认值“1”不变。如图4-3-3所示。任务一：创建工作簿图4-3-23设置“原始数据类型”（5）单击“下一步”按钮，设置字段宽度（列间隔）。部分，“入职时间”和“职称”列间缺少分列线，在相应位置单击以建立分列线。（6）单击“下一步”按钮，设置每列的数据类型，默认设置“列数据格式”为“常规”。这里，将“入职时间”设置为“日期”，其余列使用默认类型“常规”，设置“目标区域”为“=$A$1”单元格。如图4-3-4所示。任务一：创建工作簿图4-3-4设置“列数据类型”（7）单击“完成”按钮，文件“员工信息”的数据被导入到当前工作表中。如图4-3-5所示。任务一：创建工作簿图4-3-5“员工信息”导入效果任务2编辑工作表任务二：编辑工作表任务要求：本任务要求掌握按照公式计算的方法与技巧。（1）选中“工资基础信息”工作表。删除“职称”列的数据。分别在E1、F1、G1单元格中输入标题字段名称“基本工资”“绩效工资”“工龄工资”。输入“基本工资数据”。如图4-3-6所示。图4-3输入标题和表头内容图4-3-6“基本工资”数据（2）按照公式"绩效工资＝基本工资x40%"计算“绩效工资”。选中F2单元格，输入公式"=E2*0.4"，按【Enter】键确认。选中F2单元格，拖曳填充柄至F26单元格，将公式复制到F3:F26单元格区域中，可得到所有员工的绩效工资。(3）计算“工龄工资”。假设“工龄”超过10年的工龄工资为500元，否则，按每年50元计算（本案例截止日期为2023年7月14日）。选中G2单元格，单击【插入函数】命令，打开“插入函数”对话框，在“选择函数”列表框中选择“IF”，打开“函数参数”对话框，设置IF函数的参数“=IF(DATEDIF(D2,TODAY(),”y“)>10,500,DATEDIF(D2,TODAY(),”y“)*50)”。如图4-3-7所示。任务二：编辑工作表图4-3-7设置“IF函数参数”（4）选中G2单元格，拖曳填充柄至G26单元格，将公式复制到G3:G26单元格区域中，可得到所有员工的工龄工资。如图4-3-8所示。图4-3-8编辑完成的工作表任务二：编辑工作表任务3创建“加班费结算表”

任务要求：本任务要求掌握各种数据的输入方法与技巧。（1）复制“工资基础信息”工作表，将复制后的工作表重命名为“加班费结算表”。（2）删除“入职时间”、“绩效工资”和“工龄工资”列。在E1、F1单元格中分别输入标题“加班时间”和“加班费”。（3）输入加班时间。如图4-3-9所示。任务三：创建“加班费结算表”图4-3-9加班时间（4）按照公式“加班费＝（基本工资／30/8)x1.5x加班时间”计算加班费。选中F2单元格，输入公式"=D2/30/8*1.5*E2"，计算出相应的加班费。选中F2单元格，拖曳填充柄至F26单元格，可得到所有员工的加班费。如图4-3-10所示。任务三：创建“加班费结算表”图4-3-10加班费任务4创建“考勤结算表”任务四：创建“考勤结算表”任务要求：本任务要求掌握公式及函数SUM的使用方法。（1）复制“工资基础信息”工作表，将复制后的工作表重命名为“考勤扣款结算表”。删除“入职时间”“绩效工资”“工龄工资”列。（2）在E1:K1单元格区域中分别输入标题“迟到”、“迟到扣款”、“病假”、“病假扣款”、“事假”、“事假扣款”、“扣款合计”。（3）输入“迟到”“病假”“事假”列的数据。如图4-3-11所示。图4-3-11输入“迟到”“病假”“事假”列的数据任务四：创建“考勤结算表”（4）计算“迟到扣款”。假设每迟到一次扣款50元。选中F2单元格，输入公式“=E2*50”，计算出相应的迟到扣款。再选中F2单元格，拖曳填充柄至F26单元格，可得到所有员工的迟到扣款。（5）计算“病假扣款”。假设每请病假一天扣款为当日工资收入的50%，即“病假扣款＝基本工资／30x0.5x病假天数”。选中H2单元格，输入公式“=D2/30*0.5*G2”，计算出相应的病假扣款。再选中H2单元格，拖曳填充柄至H26单元格，可得到所有员工的病假扣款。（6）计算“事假扣款”。假设每请事假一天扣款为当日的全部工资收入，即“事假扣款＝基本工资／30＊事假天数”。选中J2单元格，输入公式“=D2/30*I2”，计算出相应的事假扣款。再选中J2单元格，拖曳填充柄至J26单元格，可得到所有员工的事假扣款。（8）计算“扣款合计”。选中K2单元格，插入函数"=SUM(F2,H2,J2)"，计算出相应的扣款合计。选中K2单元格，拖曳填充柄至K26单元格，可得到所有员工的扣款合计。如图4-3-12所示。任务四：创建“考勤结算表”图4-3-12“考勤结算表”计算结果任务5创建“工资明细表”任务五：

创建“工资明细表”任务要求：本任务要求掌握公式的应用以及IF嵌套函数的应用。（1）插入一张新工作表，将新工作表重命名为“工资明细表”，输入表头内容。（2）填充“编号”、“姓名”、”部门”列的数据。选中“工资基础信息”工作表的A2:C26单元格区域，单击【开始】→【复制】命令。再选中"工资明细表"的A3单元格，单击【开始】→【粘贴】命令，将"工资基础信息"工作表选定区域的数据粘贴到"工资明细表"中。（3）用同样的方法填充“基本工资”、“绩效工资”、”工龄工资”、“加班费”、“考勤扣款”列的数据。效果如图4-3-13所示。图4-3-13填充数据效果（4）计算“应发工资”。选中H3单元格，单击插入函数按钮，选择“SUM”函数，选择参数为单元格区域D3:G3即可计算出相应的应发工资。再选中H3单元格，拖曳填充柄至H27单元格，可计算出所有员工的应发工资。（5）计算“养老保险”。本任务中的养老保险数据为个人缴纳部分，一般的计算公式为“养老保险＝上一年度月平均工资x8%”，这里假设“上一年度月平均工资＝基本工资＋绩效工资”。选中I3单元格，输入公式“=(D3+E3)*8%”，即可计算出相应的养老保险。再选中13单元格，拖曳填充柄至I27单元格，计算出所有员工的养老保险。如图4-3-14所示。任务五：

创建“工资明细表”图4-3-14

“应发工资”、“养老保险”计算结果（6）计算“医疗保险”。本任务中的医疗保险数据为个人缴纳部分，公式为“医疗保险＝上一年度月平均工资x2%”，这里假设“上一年度月平均工资＝基本工资＋绩效工资”。选中J3单元格，输入公式“=(D3+E3)*2%”，计算出相应的医疗保险。再选中J3单元格，拖曳填充柄至J27单元格，即可计算出所有员工的医疗保险。（7）计算“失业保险”。本任务中的失业保险数据为个人缴纳部分，公式为“失业保险＝上一年度月平均工资x1%”，这里假设“上一年度月平均工资＝基本工资＋绩效工资”。选中K3单元格，输入公式“=(D3+E3)*1%”，计算出相应的失业保险。再选中K3单元格，拖曳填充柄至K27单元格，即可计算出所有员工的失业保险。如图4-3-15所示。任务五：

创建“工资明细表”图4-3-15

“医疗保险”、“失业保险”计算结果（8）计算“应税工资”。计算公式为“应税工资＝应发工资-（养老保险＋医疗保险＋失业保险）-5000”（5000为个人所得税起征点），选中M3单元格，输入公式“=H3-SUM(I3:K3)-5000”，计算出相应的应税工资。选中M3单元格，拖曳填充柄至M27单元格，即可计算出所有员工的应税工资。（9）计算“个人所得税”。选中N3单元格，单击插入函数命令，打开“插入函数”对话框，在“选择函数”列表框中选择“IF”，设置函数参数，“=IF(M3<3000,M3*3%,IF(M3<12000,M3*10%-210,M3*20%-1410))”。如图4-3-16所示。计算出相应的个人所得税。选中N3单元格，拖曳填充柄至N27单元格，即可计算出所有员工的个人所得税。任务五：

创建“工资明细表”图4-3-16设置“IF函数参数”任务五：

创建“工资明细表”（10）计算“实发工资”。计算公式为“实发工资＝应发工资-（养老保险＋医疗保险＋失业保险＋考勤扣款＋个人所得税）”。选中O3单元格，输入公式“=H3-SUM(I3:L3,N3),”，计算出相应的实发工资。再选中O3单元格，拖曳填充柄至O27单元格，即可计算出所有员工的实发工资。如图4-3-17所示。图4-3-17“工资明细表”计算结果任务6工作表格式化任务六：工作表格式化任务要求：本任务要求掌握工作表格式化的相关操作。(1）将工作表标题的对齐方式设置为“合并后居中”，设置标题的格式为“黑体、25磅、加粗"，标题行的行高为“60"。将列标题的格式设置为"加粗、居中"，行高设置为"30"。(2）将表中所有的数据项的格式设置为“会计专用”格式，保留2位小数，无货币符号。(3）为表格添加内细外粗的蓝色边框。为“应发工资”、“实发工资”列的数据添加“浅蓝色"的底纹。如图4-3-18所示。图4-3-18格式化“工资明细表”效果任务7创建“各部门工资汇总表”任务七：创建“各部门工资汇总表”任务要求：本任务要求掌握工作表分类汇总和创建图表