Excel在财务中的应用（第二版）课件 任务5 应用Excel核算和管理工资

Excel

在财务中的应用任务5应用Excel核算和管理工资子任务5.1建立员工工资表子任务5.2计算员工工资项目子任务5.3查询与统计分析员工工资子任务5.4打印员工工资发放条任务5重点和难点1.员工工资表的制作;2.工资项目的计算;3.工资数据的查询与统计。应用Excel核算和管理工资学习目标【知识目标】1.了解员工工资核算标准;2.掌握员工工资表的建立过程;3.掌握员工工资表各工资项目的计算。【技能目标】1.能够制作员工工资表;2.能够计算员工工资表各工资项目;3.能够查询、汇总分析工资数据;4.能够打印工资发放条。应用Excel核算和管理工资任务5任务概述工资是企业在一定时间内直接支付给本单位员工的劳动报酬,也是企业进行各种费用计提的基础。工资管理是企业管理的重要组成部分,是财务部门最基本的业务之一,不仅关系到每个员工的切身利益,也是直接影响产品成本核算的重要因素。手工进行工资核算,需要占用财务人员大量的精力和时间,并且容易出错,采用计算机进行工资核算可以有效提高准确性和工作效率。通过本任务的学习,学生可掌握工资表的建立以及工资项目的计算,了解Excel在工资核算中的处理流程。应用Excel核算和管理工资任务5任务要求1.掌握员工工资表的格式;2.能够制作员工工资表。建立员工工资表子任务5.1任务资料大连百大公司是一家小型工业企业,主要有管理部、生产部和销售部三个部门。该公司主要有管理人员、生产工人、销售人员三种员工类别。每个员工的工资项目有基本工资、岗位工资、住房补贴、奖金、事假扣款、病假扣款、养老保险扣款和医疗保险扣款等。除基本工资因人而异外(要求必须输入),其他工资项目将根据员工类别和部门确定,而且随时间的变化而变化。2024年12月员工基本工资情况和出勤情况如表5-1所示。建立员工工资表子任务5.1员工编号姓名部门性别员工类别基本工资(元)事假天数病假天数1001周星星管理部男管理人员4400

1002张子仪管理部女管理人员45003

1003李明管理部男管理人员3800

31004龚丽管理部女管理人员3300

1005王亚管理部女管理人员4300

1006刘军管理部男管理人员5300

2001祝钧管理部男管理人员3500

2002杨达生产部男生产工人2900

2003牛良生产部男生产工人290016

2004马涛生产部男生产工人2900

2005侯清生产部女生产工人2900

3001吕丹销售部女销售人员2900

3002赵本善销售部男销售人员4500

153003范伟销售部男销售人员2900

3004程龙销售部男销售人员3500

3005张东建销售部男销售人员4500

任务资料建立员工工资表子任务5.12024年员工累计专项附加扣除情况如表5

2

所示。员工编号姓名部门性别员工类别子女教育赡养老人住房贷款利息累计工资总额累计专项扣除1001周星星管理部男管理人员20003000

12540092401002张子仪管理部女管理人员

1500100012650093501003李明管理部男管理人员20003000100011880085801004龚丽管理部女管理人员20003000

11330080301005王亚管理部女管理人员

1500100012430091301006刘军管理部男管理人员

1500

135300102302001祝钧管理部男管理人员20001500

11550082502002杨达生产部男生产工人

3000100010670070402003牛良生产部男生产工人20003000

10670070402004马涛生产部男生产工人20003000

10670070402005侯清生产部女生产工人

1500100010670070403001吕丹销售部女销售人员20003000

11880081403002赵本善销售部男销售人员

3000100014410099003003范伟销售部男销售人员20001500

9130081403004程龙销售部男销售人员20001500100012650088003005张东建销售部男销售人员

300010001353009900任务资料3.设置员工工资数据。将F:J列、L列、N:V列的数字格式设置为“会计专用”,小数位2位,货币符号为“无”,并进行适当调整和修饰。4.在“大连百大公司工资核算”工作簿插入一个新的工作表,并命名为“销售总额表”。5.按如图5-3所示格式建立“销售总额表”工作表,并录入员工销售额信息。6.对“销售总额表”工作表进行格式修饰,并将F列的数字格式设置为“会计专用”,小数位2位,货币符号为“无”。建立员工工资表子任务5.1图5-3任务资料1.新建一个文件名为“大连百大公司工资核算”的工作簿,将Sheet1工作表重命名为“员工工资表”。步骤1打开Excel电子表格软件,新建一个工作簿,另存为“大连百大公司工资核算.xlsx”。步骤2选择Sheet1工作表,双击标签名,背景反白,输入工作表名称“员工工资表”,按Enter键确认。2.建立如图5-1所示的员工工资表,并录入员工基本工资数据。步骤1打开“大连百大公司工资核算”工作簿,选择“员工工资表”工作表。步骤2在“员工工资表”工作表中,按图5-1所示依次录入员工编号、姓名、部门、性别、员工类别、基本工资、岗位工资、住房补贴、奖金、应发合计、事假天数、事假扣款、病假天数、病假扣款、其他扣款、扣款合计、养老保险、医疗保险、应扣社保合计、应发工资、代扣税以及实发合计。步骤3在“员工工资表”工作表中,按图5-1所示依次录入员工基本工资信息。操作过程略。建立员工工资表子任务5.1任务资料3.设置员工工资数据。将F:J列、L列、N:V列的数字格式设置为“会计专用”,小数位2位,货币符号为“无”,并进行适当调整和修饰。步骤1

在“员工工资表”工作表中,依次选择F:J列、L列、N:V列,右击鼠标,在弹出的快捷菜单中选择“设置单元格格式”命令,打开“设置单元格格式”对话框,在对话框中选择“数字”选项卡,在下方的分类中选择“会计专用”,设置小数位数为“2”,货币符号为“无”,单击“确定”按钮。步骤2

在“员工工资表”工作表中,设置字体、字形、字号、行高、列宽以及边框等,对工作表进行格式调整和修饰(操作过程略)。4.在“大连百大公司工资核算”工作簿插入一个新的工作表,并命名为“销售总额表”。打开“大连百大公司工资核算”工作簿,选择“开始—单元格”命令组,再选择“插入”下拉列表中的“插入工作表”命令,系统自动在当前工作表前插入一个空白工作表。双击新插入工作表标签名,背景反白,输入“销售总额表”,按Enter键完成。5.按如图5-2所示格式建立“销售总额表”工作表,并录入员工销售额信息。在“销售总额表”工作表中,按如图5-2所示格式录入相关信息(操作过程略)。建立员工工资表子任务5.1任务资料6.对“销售总额表”工作表进行格式修饰,并将F列的数字格式设置为“会计专用”,小数位2位,货币符号为“无”。步骤1打开“销售总额表”工作表,设置字体、字形、字号、行高、列宽以及边框等,对工作表进行格式调整和修饰(操作过程略)。步骤2在“销售总额表”工作表中,选定F列,右击鼠标,在快捷菜单中选择“设置单元格格式”命令,在对话框中选择“数字”选项卡,在分类列表框中选择“会计专用”,并设置小数位数为“2”,货币符号为“无”。建立员工工资表子任务5.1任务要求1.掌握用函数计算工资项目的方法;2.能够计算员工工资项目。计算员工工资项目子任务5.2任务要求在大连百大公司员工工资数据表中,除员工编号、姓名、部门、性别、员工类别、基本工资、病假天数、事假天数项目外,其他项目按如下标准发放:(1)岗位工资:根据员工类别发放,管理人员为4000元,生产工人为3500元,销售人员为4500元。(2)住房补贴:根据员工类别发放,管理人员为1000元,生产工人为800元,销售人员为900元。(3)奖金:根据部门的效益确定,本月管理部奖金为2000元,生产部奖金为2500元,销售部奖金与个人销售额有关,完成基本销售额30万元的,奖金为500元,超额完成的,按超出金额的1%提成,未完成基本销售额的,没有奖金。(4)事假扣款:规定如果事假少于14天,将应发工资平均到每天(每月按22天计算),按天扣款;如果事假多于14天,扣除应发工资的80%。(5)病假扣款:规定如果病假少于14天,工人扣款500元,非工人扣款800元;如果病假多于14天,工人扣款800元,非工人扣款1200元。(6)养老保险扣款:按基本工资与岗位工资之和的8%扣除。(7)医疗保险扣款:按基本工资与岗位工资之和的2%扣除。(8)个人所得税:依据个人所得税税率表(见表5-4)进行扣除。计算员工工资项目子任务5.2任务要求计算员工工资项目子任务5.2级数全年应纳税所得额税率(%)速算扣除数1不超过36000元的302超过36000元至144000元的部分1025203超过144000元至300000元的部分20169204超过300000元至420000元的部分25319205超过420000元至660000元的部分30529206超过660000元至960000元的部分35859207超过960000元的部分45181920任务要求根据上述员工工资项目发放标准,完成如下工资项目的计算:1.根据员工类别的不同,设置“岗位工资”项目的计算公式。2.根据员工类别的不同,设置“住房补贴”项目的计算公式。3.根据各部门的效益,设置“奖金”项目的计算公式。4.设置“应发合计”项目的计算公式。5.根据事假天数,设置“事假扣款”项目的计算公式。6.根据病假天数,设置“病假扣款”项目的计算公式。7.设置“扣款合计”项目的计算公式。8.根据基本工资和岗位工资之和的百分比计算扣除“养老保险”“医疗保险”项目,并设置“应扣社保合计”项目的计算公式。9.设置“应发工资”项目的计算公式。10.根据应发工资的数额设置“代扣税”项目的计算公式,并设置“实发合计”项目的计算公式。计算员工工资项目子任务5.2任务实施1.根据员工类别的不同,设置“岗位工资”项目的计算公式。步骤1打开“员工工资表”工作表,选定G2单元格。步骤2在G2单元格输入计算“岗位工资”项目的计算公式“=IF(E2="管理人员",4000,IF(E2="生产工人",3500,4500))”,按Enter键确定公式输入。选择G2单元格,在编辑栏中显示公式,在G2单元格显示计算结果,如图5-3所示。

【小提示】G2单元格计算公式“=IF(E2="管理人员",4000,IF(E2="生产工人",3500,4500))”的意义:先判断E2单元格的值是否为“管理人员”,如果为管理人员,则岗位工资为4000元,否则,判断G2单元格的值是否为“生产工人”,如果为生产工人,则岗位工资为3500元,否则为销售人员,岗位工资为4500元。计算员工工资项目子任务5.2图5-3任务实施步骤3选定G2单元格,鼠标指向G2单元格右下角的填充柄,按下鼠标左键向下拖动至G17单元格,结果如图5-4所示。2.根据员工类别的不同,设置“住房补贴”项目的计算公式。步骤1

打开“员工工资表”工作表,选定H2单元格。步骤2

在H2单元格,输入“住房补贴”项目的计算公式“=IF(E2="管理人员",1000,IF(E2="生产工人",800,900))”,按Enter键确定公式输入。选择H2单元格,在编辑栏中显示公式,在H2单元格显示计算结果,如图5-5所示。计算员工工资项目子任务5.2图5-4图5-5任务实施步骤3选定H2单元格,鼠标指向H2单元格右下角的填充柄,按下鼠标左键向下拖动至H17单元格,结果如图5-6所示。3.根据各部门的效益,设置“奖金”项目的计算公式。步骤1设置管理部和生产部的奖金。打开“员工工资表”工作表,选定I2单元格。步骤2在I2单元格输入“奖金”项目的公式“=IF(C2="管理部",2000,IF(C2="生产部",2500,"销售部"))”,按Enter键确定输入。步骤3选定I2单元格,鼠标指向I2单元格右下角的填充柄,按下鼠标左键拖动至I17单元格,结果如图5-7所示。【小提示】在“员工工资表”工作表的I13:I17单元格显示“销售部”,表示这些人员为销售人员,销售人员的奖金根据本月销售额计算。计算员工工资项目子任务5.2图5-6图5-7任务实施步骤4

选定I13单元格,输入销售人员“奖金”项目的计算公式“=IF(AND(C13="销售部",销售总额表!F2>=30),2000+100*(销售总额表!F2-30),0)”,按Enter键确定输入,在I13单元格显示计算结果,在编辑栏中显示I13单元格的计算公式,如图5-8所示。【小提示】I13单元格计算公式“=IF(AND(C13="销售部",销售总额表!F2>=30),2000+100*(销售总额表!F2-30),0)”,其中AND是逻辑函数,用于判断当“C13="销售部"”和“销售总额表!F2>=30”两个条件均为真值时,IF函数返回“2000+超额30万元后计算的奖金额”,“100”(10000×0.01)表示万元转为元乘以提成比率。步骤5

选定I13单元格,鼠标指向I13单元格右下角的填充柄,按下鼠标左键拖动至I17单元格,结果如图5-9所示。计算员工工资项目子任务5.2图5-8图5-9任务实施4.设置“应发合计”项目的计算公式。“应发合计”项目金额为基本工资、岗位工资、住房补贴和奖金的合计数。步骤1

打开“员工工资表”工作表,选定J2单元格。步骤2

在J2单元格,输入“应发合计”项目的计算公式“=SUM(F2:I2)”,按Enter键确认输入。步骤3

选定J2单元格,鼠标指向J2单元格右下角的填充柄,按下鼠标左键拖动至J17单元格,结果如图5-10所示。计算员工工资项目子任务5.2图5-10任务实施5.根据事假天数,设置“事假扣款”项目的计算公式。步骤1

打开“员工工资表”工作表,选定L2单元格。步骤2

在L2单元格输入“事假扣款”项目的计算公式“=IF(K2>14,J2*0.8,J2/22*K2)”,按Enter键确认输入。步骤3选定L2单元格,鼠标指向L2单元格右下角的填充柄,按下鼠标左键拖动至L17单元格,结果如图5-11所示。【小提示】L2单元格的计算公式“=IF(K2>14,J2*0.8,J2/22*K2)”,表示使用IF函数判断K2单元格,也就是事假天数是否大于14天,如果为真值,则返回应发合计(J2单元格)的80%的值,否则,返回应发合计(J2单元格)的值除以每月工作日数22再乘以事假天数的值。计算员工工资项目子任务5.2图5-11任务实施6.根据病假天数,设置“病假扣款”项目的计算公式。步骤1打开“员工工资表”工作表,选定N2单元格。步骤2

在N2单元格输入“病假扣款”项目的计算公式“=IF(M2=0,0,IF(M2<=14,IF(E2="生产工人",500,800),IF(E2="生产工人",800,1200)))”,按Enter键确认输入。步骤3

选定N2单元格,鼠标指向N2单元格右下角的填充柄,按下鼠标左键拖动至N17单元格,结果如图5-12所示。计算员工工资项目子任务5.2图5-12任务实施7.设置“扣款合计”项目的计算公式。“扣款合计”项目金额为事假扣款、病假扣款、其他扣款的合计数。其中,其他扣款由用户手工输入。步骤1

打开“员工工资表”工作表,选定P2单元格。步骤2

在P2单元格输入“扣款合计”项目的计算公式“=SUM(L2,N2,O2)”,按Enter键确认输入。步骤3

选定P2单元格,鼠标指向P2单元格右下角的填充柄,按下鼠标左键拖动至P17单元格,结果如图5-13所示。计算员工工资项目子任务5.2图5-13任务实施8.根据基本工资和岗位工资之和的百分比计算扣除“养老保险”“医疗保险”项目,并设置“应扣社保合计”项目的计算公式。步骤1

打开“员工工资表”工作表,选定Q2单元格。步骤2

设置“养老保险”项目计算公式。在Q2单元格输入“养老保险”项目的计算公式“=(F2+G2)*0.08”,按Enter键确认输入。

【小提示】Q2单元格计算公式“=(F2+G2)*0.08”,表示“养老保险”项目按基本工资与岗位工资合计数的8%提取。步骤3

选定Q2单元格,鼠标指向Q2单元格右下角的填充柄,按下鼠标左键拖动至Q17单元格。步骤4

设置“医疗保险”项目计算公式。选定R2单元格,输入“医疗保险”项目的计算公式“=(F2+G2)*0.02”,按Enter键确认输入。步骤5

选定R2单元格,鼠标指向R2单元格右下角的填充柄,按下鼠标左键拖动至R17单元格。步骤6

设置“应扣社保合计”项目计算公式。选定S2单元格,输入“应扣社保合计”项目的计算公式“=Q2+R2”,按Enter键确认输入。计算员工工资项目子任务5.2任务实施步骤7选定S2单元格,鼠标指向S2单元格右下角的填充柄,按下鼠标左键拖动至S17单元格。“养老保险”“医疗保险”“应扣社保合计”项目的计算结果如图5-14所示。计算员工工资项目子任务5.2图5-14任务实施9.设置“应发工资”项目的计算公式。“应发工资”项目金额为应发合计减去扣款合计和应扣社保合计的金额。步骤1打开“员工工资表”工作表,选定T2单元格。步骤2

在T2单元格输入“应发工资”项目的计算公式“=J2-P2-S2”,按Enter键确认输入。步骤3

选定T2单元格,鼠标指向T2单元格右下角的填充柄,按下鼠标左键拖动至T17单元格,结果如图5-15所示。计算员工工资项目子任务5.2图5-15任务实施10.根据应发工资的金额设置“代扣税”项目的计算公式,并设置“实发合计”项目的计算公式。步骤1打开“员工工资表”工作表,选定U2单元格。步骤2在U2单元格，输入“代扣税”工资项目的计算公式“=IF（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2)<=0,0,IF（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2)<=36000,（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.03,IF（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2)<=144000,（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.1-2520,IF（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计计算员工工资项目子任务5.2任务实施专项附加扣除表！J2+Q2+R2)<=300000,（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.2-16920,IF（累计专项附加扣除表！I2+T2- (5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2)<=420000,（累计专项附加扣除表！ I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.25-31920,IF（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2)<=660000,（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.3-52920,IF（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2)<=960000,（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.35-82920,（累计专项附加扣除表！I2+T2-(5000关12)-（累计专项附加扣除表！F2+累计专项附加扣除表！G2+累计专项附加扣除表！H2)关12-（累计专项附加扣除表！J2+Q2+R2))关0.35-181920)))))))”,按Enter键确认输入。计算员工工资项目子任务5.2任务实施计算员工工资项目子任务5.2图5-16图5-17任务要求1.能够使用筛选功能对员工工资进行查询;2.能够使用VLOOKUP函数对员工工资进行查询;2.能够使用数据透视表对员工工资进行统计分析;查询与统计分析员工工资子任务5.3任务资料根据子任务5.1建立的员工工资数据清单,按下列要求完成员工工资的查询与统计分析:1.在“员工工资表”工作表中,利用筛选功能查询李明的工资信息。2.在“员工工资表”工作表中,利用筛选功能查询销售部的员工工资信息。3.在“员工工资表”工作表中,利用筛选功能查询销售人员中基本工资高于3000元的员工工资信息。4.在“员工工资表”工作表中,利用筛选功能查询本月请假的员工工资信息,包括事假和病假两种情况。5.使用数据透视表和数据透视图功能统计各部门实发工资汇总数据。查询与统计分析员工工资子任务5.3任务资料6.在“大连百大公司工资核算”工作簿中新建一个空白工作表,将工作表重命名“工资查询”。在新建的“工资查询”工作表中按如图5-18所示建立工资查询表,要求在姓名下方的A2单元格输入员工姓名后,在对应的其他单元格显示该员工的工资项目数据,如输入“李明”,结果如图5-19所示。查询与统计分析员工工资子任务5.3图5-19图5-18任务实施1.在“员工工资表”工作表中,利用筛选功能查询李明的工资信息。步骤1打开“大连百大公司工资核算”工作簿,选择“员工工资表”工作表。步骤2

在“员工工资表”工作表中,选定员工工资数据清单任意单元格,选择“数据—排序和筛选”命令组,单击“筛选”按钮。步骤3

单击“员工工资表”工作表中“姓名”标题列右侧下拉列表,取消“全选”,再单击“李明”,结果如图5-20所示。【小提示】步骤3也可以使用如下方法操作,单击“姓名”标题列右侧下拉列表中的“文本筛选”下级菜单中的“等于”命令,如图5-21所示。在弹出的“自定义自动筛选方式”对话框中,在“姓名”项下选择“等于”,在文本框中输入“李明”,如图5-22所示,单击“确定”按钮,工作表显示如图5-20所示的查询结果。查询与统计分析员工工资子任务5.3图5-20任务实施查询与统计分析员工工资子任务5.3图5-22图5-21知识积累2.在“员工工资表”工作表中,利用筛选功能查询销售部的员工工资信息。步骤1

打开“大连百大公司工资核算”工作簿,选择“员工工资表”工作表。步骤2

在“员工工资表”工作表中,选定员工工资数据清单任意单元格,选择“数据—排序和筛选”命令组,单击“筛选”按钮。步骤3

单击“员工工资表”工作表中“部门”标题列右侧下拉列表,取消“全选”,再单击“销售部”,结果如图5-23所示。查询与统计分析员工工资子任务5.3图5-23知识积累3.在“员工工资表”工作表中,利用筛选功能查询销售人员中基本工资高于3000元的员工工资信息。步骤1打开“大连百大公司工资核算”工作簿,选择“员工工资表”工作表。步骤2在“员工工资表”工作表中,选定员工工资数据清单任意单元格,选择“数据—排序和筛选”命令组,单击“筛选”按钮。步骤3单击“员工工资表”工作表中“员工类别”标题列右侧下拉列表,取消“全选”,再单击“销售人员”,结果如图5-24所示。查询与统计分析员工工资子任务5.3图5-24知识积累步骤4

在步骤3查询结果的基础上,单击“员工工资表”工作表中“基本工资”标题列右侧下拉列表中的“数字筛选”二级菜单中的“大于”命令,在弹出的“自定义自动筛选方式”对话框中,在基本工资大于文本框中输入“3000”,如图5-25所示,单击“确定”按钮,显示如图5-26所示的工资信息查询结果。查询与统计分析员工工资子任务5.3图5-26图5-25知识积累4.在“员工工资表”工作表中,利用筛选功能查询本月请假的员工工资信息,包括事假和病假两种情况。步骤1

打开“大连百大公司工资核算”工作簿,选择“员工工资表”工作表。步骤2

在“员工工资表”工作表下方预留一个空白行,然后将“员工工资表”工作表的标题复制到第19行,如图5-27所示。查询与统计分析员工工资子任务5.3图5-27知识积累步骤3

在K20单元格输入查询条件“>0”,表示查询事假天数大于0的结果;在M21单元格输入查询条件“>0”,表示查询病假天数大于0的结果,如图5-28所示。【小提示】如果将查询条件放在同一行,即在K20单元格输入“>0”,在M20单元格输入“>0”,则表示查询事假天数大于0,并且病假天数也大于0的结果。如果将查询条件分别放在不同行,则表示查询事假天数大于0,或者病假天数大于0的结果,即事假天数或病假天数大于0的结果,也就是本月有事假或病假的员工信息。查询与统计分析员工工资子任务5.3图5-28知识积累步骤4

选定“员工工资表”数据清单的任意一个单元格,选择“数据—排序和筛选”命令组,单击“高级”按钮,弹出“高级筛选”对话框,按如图5-29所示选择列表区域和条件区域,单击“确定”按钮,结果如图5-30所示。查询与统计分析员工工资子任务5.3图5-29图5-30知识积累5.使用数据透视表和数据透视图功能统计各部门实发工资汇总数据。步骤1

打开“大连百大公司工资核算”工作簿,选择“员工工资表”工作表。步骤2

在“员工工资表”工作表中,选定员工工资数据清单任意单元格,选择“插入—表格”命令组,单击“数据透视表”按钮,弹出“创建数据透视表”对话框,在对话框中选择“表/区域”为“员工工资表!$A$1:$V$17”,选择放置数据透视表的位置为“新工作表”,如图5-31所示,单击“确定”按钮创建一个新的数据透视表工作表,并显示“数据透视表字段”任务窗格,如图5-32所示。查询与统计分析员工工资子任务5.3图5-31图5-32知识积累步骤3

在“数据透视表字段”任务窗格,将“部门”字段拖动至行标签,将实发工资拖动至值标签。在生成的数据透视表任意位置单击,选择“数据透视表工具—设计—布局”命令组,单击“报表布局”下拉列表中的“以表格形式显示”命令,结果如图5-33所示。查询与统计分析员工工资子任务5.3图5-33知识积累步骤4

在生成的数据透视表上单击,选择“数据透视表工具—分析—工具”命令组,单击“数据透视图”按钮,弹出“插入图表”对话框,如图5-34所示,单击“确定”按钮,系统自动根据数据透视表生成数据透视图,如图5-35所示。查询与统计分析员工工资子任务5.3图5-34图5-35知识积累6.在“大连百大公司工资核算”工作簿中新建一个空白工作表,将工作表重命名“工资查询”。在新建的“工资查询”工作表中按如图5-18所示建立工资查询表,要求在姓名下方的A2单元格输入员工姓名后,在对应的其他单元格显示该员工的工资项目数据。步骤1在“大连百大公司工资核算”工作簿中新建一张工作表,并将其重命名为“工资查询”。步骤2

在“工资查询”工作表中,按图5-18所示输入各个工资项目。步骤3

在“工资查询”工作表中,选定B2单元格,输入查询A2单元格(姓名)的公式“=IF(ISNA(VLOOKUP($A$2,员工工资表!$B$2:$V$17,2,FALSE)),"",VLOOKUP($A$2,员工工资表!$B$2:$V$17,2,FALSE))”,按Enter键确认输入。

【小提示】B2单元格的公式使用VLOOKUP函数在“员工工资表!$B$2:$V$17”区域内查找$A$2单元格的值,也就是在员工工资表中查询输入的姓名。为了防止输入的姓名不存在或为空值时找不到工资数据,系统会提示“#N/A”错误,在公式中使用IF函数判断ISNA返回值是否为“#N/A”错误,如果为真值,则返回空值,否则,查询并返回查询区域的第2列也就是部门的值。另外,B2单元格公式的输入也可以使用插入函数,利用“函数参数”对话框进行设置,具体操作过程略。查询与统计分析员工工资子任务5.3知识积累步骤4复制B2单元格的公式,选定C2单元格,点击鼠标右键进行粘贴。双击C2单元格,修改公式,将第2列修改为第3列,也就是查询到A2单元格姓名,则返回查找区域第3列的值,即性别列的值。步骤5

重复步骤4的操作,依次修改单元格D2:K2以及B4:K4单元格公式,注意返回值列(操作过程略)。步骤6

测试查询。在A2单元格输入姓名“李明”,按Enter键确认输入,显示的查询结果如图5-36所示。查询与统计分析员工工资子任务5.3图5-36任务要求1.能够生成工资发放条;2.能够打印工资发放条。打印员工工资发放条子任务5.4任务资料根据子任务5.1建立的员工工资数据清单,按下列要求完成员工工资发放条的打印:1.生成员工工资发放条,如图5-37所示。

2.打印工资发放条。打印员工工资发放条子任务5.4图5-37任务资料1.生成员工工资发放条。步骤1

打开“大连百大公司工资核算”工作簿,新增一个空白工作表,并重命名为“工资发放条”。步骤2

复制“员工工资表”工作表的数据到“工资发放条”工作表。步骤3

在“工资发放条”工作表,选择A列,右击鼠标,在快捷菜单中选择“插入”命令,在最前面插入一个空白列,并在A1单元格输入“月份”,在A2单元格输入“2024年12月”。步骤4

选定A2单元格,使用快速填充功能将A2单元格的值填充到A3:A17区域。步骤5

在“工资发放条”工作表的第3行前插入3个空白行。复制员工工资表的标题(工作表第1行),将其粘贴到第5行,如图5-38所示。打印员工工资发放条子任务5.4图5-38任务资料步骤6

重复步骤5的操作过程,进行插入行与粘贴标题操作,结果如图5-39所示。

打印员工工资发放条子任务5.4图5-39任务资料2.打印工资发