员工工资管理（PPT52页)

任务9员工工资管理工资管理是企业管理的一项重要内容，采用规范的工资管理办法可以调动员工工作积极性，维持企业的长久发展。由于员工工资的结算来自多项数据，如基本工资、奖金、考勤扣款、社会保险扣款等，应用OfficeExcel2013建立工资管理表格，既能便于数据之间的相互调用，又能应用Excel强有力的公式和函数功能实现快速计算，这样大大减小了工资统计与计算人员的工作负担，提高工作效率。下面是完成的公司员工工资管理表格设计及统计计算后的效果图。239.2任务分析9.3工资管理表格设计与计算拓展实训综合实践9.1任务情境为了充分调动员工的工作积极性，公司领导决定调整公司的工资发放办法，而且随着信息技术的发展，公司的各项管理办法和技术也都要与时俱进，实现科学化和现代化，那么公司员工的工资管理也不例外4知识目标：粘贴链接的使用批注的新建、编辑及删除Excel公式的编辑方法常用函数求和、求平均值、求最大值、最小值的使用方法排名函数RANK()的使用方法区分单元格的相对引用和绝对引用及各自应用场合查找和引用函数，HLOOKUP()和VLOOKUP()的用法与区别条件计数函数COUNTIF()的用法5能力目标：能够熟练使用常用函数求和、求平均值、求最大值、最小值，计数等能够熟练编辑EXCEL公式解决实际计算问题能够熟练应用IF函数解决实际问题会应用排名函数RANK()排名次会应用HLOOKUP()和VLOOKUP()函数，快速查找引用其他工作表中的数据会根据工资表制作工资条会为工作表奇数行和偶数行设置不同的填充颜色能够综合应用所学知识规划和设计企业工资管理表格及进行统计与计算67经过分析，完成员工的工资计算与统计需要进行以下工作。（1）设计员工工资调整表格，计算调整工资；（2）设计考勤表，计算考勤扣款；（3）由员工的销售业绩表计算销售提成；（4）设计员工的社会保险缴存表，计算员工的住房公积金、医疗保险、养老保险等扣款；（5）计算员工依法交纳的个人所得税；（6）由员工的基本信息导入工资表中所需的“工号、姓名、部门、基本工资”等信息；（7）统计与计算员工的月应发工资、所有扣款、实发工资。89.3.2员工工资调整表设计9.3.1建立员工工资管理工作簿9.3.4由员工考勤统计表计算考勤扣款9.3.6员工工资统计结算表设计9.3.5由员工销售提成表计算提成金额9.3.3员工社会保险缴存表设计便于操作和查看，把与工资统计有关的数据归类存储，在一个工作簿“企业员工工资管理.xlsx”中建立“员工工资统计表”、“工资调整表”、“考勤统计表”和“销售提成表”、“社会保险缴存表”五个工作表。操作如下。（1）新建工作簿，保存为“企业员工工资管理.xlsx”。（2）插入新工作簿Sheet1并重命名为“员工工资统计表”。（3）插入新工作簿Sheet2并重命名为“工资调整表”。（4）插入新工作簿Sheet3并重命名为“社会保险缴存表”。（5）插入工作表Sheet4并重命名为“考勤统计表”。（6）插入工作表Sheet5并重命名为“销售提成表”。910要想统计员工的工资，必须先对员工的工资调整情况进行统计。下面建立工资调整表，完成效果111．导入部分数据由于员工工资表中的基本信息如“工号、姓名、部门、基本工资”是和员工档案信息是相同的，所以可以直接从档案信息中导入部分数据。操作步骤如下。（1）复制任务8完成的“公司员工档案.xlsx”，并打开文件，按住“Ctrl”键选中“工号、姓名、部门、基本工资”四列信息，右击弹出快捷菜单，在快捷菜单中选择【复制】命令。（2）在新建的“企业员工工资管理”工作簿中，单击A1单元格，切换到【开始】选项卡，单击【剪贴板】选项组中的【粘贴】按钮，在弹出的下拉列表中选择【粘贴链接】选项，则导入了“工号、姓名、部门、基本工资”四列数据。122．完成表格设计在表格顶部插入三行，分别输入表格标题、制作日期和审批人，依次设置标题效果，并输入相应数据，设置表格边框和底纹。132．完成表格设计函数参数的范围默认为所选单元格左边的数值区域D4:F4，但是实际只需要求单元格D4:E4的和，所以必须修改默认的范围，用鼠标选择单元格区域D4:E4或者在编辑栏里修改F4为E4。然后单击单击编辑栏的按钮或者按回车键确认输入。双击G4的填充柄，复制公式至单元格H21，完成向下自动填充143．计算调整后工资单击G4单元格，切换到【公式】选项卡，单击【函数库】选项组中的【自动求和】按钮，在弹出的下拉列表的选择【∑求和】选项。15建立社会保险缴存表效果16操作步骤如下：（1）打开素材文件“社会保险缴存表.xlsx”，复制表中的信息到“企业员工工资管理.xlsx”中的“社会保险缴存表”工作表中17（2）计算个人应该缴存的住房公积金、医疗保险、养老保险、失业保险表9-1五险一金单位和个人缴存比例参考表项目单位个人住房公积金12%12%医疗保险10%2%+3养老保险20%8%失业保险1%0.2%工伤保险0.5%个人不缴纳生育保险0.8%个人不缴纳18（2）计算个人应该缴存的住房公积金、医疗保险、养老保险、失业保险①单击F2单元格，在F2单元格输入公式：“=(D2+E2)*12%”，如图9-9所示，按回车键或单击编辑栏的按钮，确认输入。19（2）计算个人应该缴存的住房公积金、医疗保险、养老保险、失业保险②双击单元格F2的填充柄，向下自动填充至单元格F19，自动计算其他员工的住房公积金数据。③计算医疗保险，在单元G2中输入公式：“=(D2+E2)*2%+3”。计算员工养老保险：在单元格I2中输入公式：“=(D2+E2)*8%”。计算员工失业保险：在单元格J2中输入公式：“=(D2+E2)*0.2%”。④在H2中输入大病保险缴存金额“5，双击填充柄向下填充20Excel的公式以“=”开头，输入公式时必须先输入“=”，公式与数学表达式基本相同，由参与运算的数据和运算符组成。公式中的运算符要用英文半角符号。当公式引用的单元格的数据修改后，公式的计算结果会自动更新。21（4）插入表格标题。格式化表格效果如图22（1）打开素材“员工月考勤表.xlsx”将“三月”工作表数据粘贴链接到“员工考勤统计表”工作表中。（2）给“应扣工资”所在的单元格插入批注。插入批注的操作步骤如下：①单击G1单元格，切换到【审阅】选项卡，单击【批注】选项组中的【新建批注】按钮23②在弹出的文本框中输入“事假扣除当天工资，病假扣除当天工资的25%。”输入完毕后单击工作表中的任意一个单元格，即可退出批注的编辑状态24（3）计算应扣工资。将单元格区域G2:G19的数据格式设置为小数位数是2位的货币类型。单击G2单元格，在G2单元格或编辑栏中输入公式：“=(D2/30)*E2+(D2/30)*25%*F2”。25（4）添加表格标题，格式化表格，效果如图26完成效果27（1）打开素材“销售提成统计表.xlsx”，复制“提成统计表”到工作表“员工销售提成表”。（2）填入提成比例。根据公司规定，员工基本销售任务是20000元，也就是销售额在20000元以下按照5%提成，销售额在20000～49999元的按照10%提成，销售额在5万至10万的按照20%提成，10万以上按照30%提成。28（2）填入提成比例①设置“销售提成表”的“提成比例”列的数据类型为百分比形式，没有小数位数。②单击D3单元格，切换到【公式】选项卡，单击【函数库】选项组中的【插入函数】按钮，弹出【插入函数】对话框。③在【插入函数】对话框中选择【HLOOKUP】函数29（2）填入提成比例④弹出查找和引用函数HLOOKUP的【函数参数】对话框，在对话框中输入函数各个参数，30（2）填入提成比例参数Lookup_value的值：“C3”指在参数Table_array设定的查找区域的首行查找C3单元格的值，即“26,500”。参数Table_array的值：“$B$12:$E$13”参数Row_index_num的值：“2”匹配有近似匹配和精确匹配两种选择，由Range_lookup参数设定。当设为近似匹配时，如果找不到精确匹配值，则返回小于lookup_value的最大数值。31（2）填入提成比例函数返回结果32（3）计算提成金额。单击E3单元格，输入公式：“=C3*D3”。销售提成计算结果：33（1）引用工作表“工资调整表”中的数据到“员工工资统计表”中。①选中“工资调整表”中的“工号、姓名、部门、基本工资、调后工资”，右击弹出快捷菜单，在快捷菜单中选择【复制】命令。②切换到“员工工资统计表”中，单击A1单元格。③切换到【开始】选项卡，单击【剪贴板】选项组中的【粘贴】按钮，在弹出的下拉列表中选择【粘贴链接】选项。（2）同样的方法复制“社会保险缴存表”中的“薪级工资、缴存合计额”粘贴到“员工工资统计表”中相应列中。（3）同样的方法引用“考勤统计表”中的“应扣工资”到“员工工资统计表”中。把M1单元格的值“应扣工资”改为“考勤扣款”。34（4）引用“销售提成表”中的提成金额到“员工工资统计表”中。选中单元格I1，在I1单元格里输入文本“销售提成”。按照“工号”从“销售提成表”中找到对应的销售人员的“提成金额”。由于只有销售人员才有销售提成，所以只有当部门名称是“销售部”时才填入“员工销售提成表”时提成金额，否则销售提成是0，所以需用“IF()”条件函数完成。单击单元格I2，输入IF条件函数：=IF(C2=销售部,VLOOKUP(A2,销售提成表!$A$3:$E$9,5,0),0)35VLOOKUP函数说明：①C2=“销售部”，则返回查找和引用函数VLOOKUP(A2,员工销售提成表!$A$3:$E$9,5,0)的值。36VLOOKUP(A2,销售提成表!$A$3:$E$9,5,0)的各个参数含义：参数1：A2表示查找A2单元格的值参数2：销售提成表!$A$3:$E$9表示在“销售提成表”的单元格区域A3:E9中查找参数3：5表示函数要返回的数据在查找区域的第5列即E列参数4：0表示设定为精确匹配。37②由于C3的值是“行政部”，即IF条件函数中的条件“C3=销售部”为假，所以函数值即I3的值为“0”。（5）计算应发工资。员工的应发工资＝调后工资＋薪级工资－缴存合计－考勤扣款＋销售提成，操作步骤如下。①选中单元格J1，输入“应发工资”。②选中单元格J2，输入公式：“=E2+F2-G2-H2+I2”。按回车键确认输入，如图9-22所示。③双击填充柄自动填充至单元格J19。38（6）计算个人所得税企业在发放工资之前，还应该根据员工的工资情况扣除其应缴纳的“个人所得税”。2015年我国个税起征点3500元。个人所得税的计算公式如下：个税＝应纳税额×税率－速算扣除数，税率和速算扣除数分别与不同的应纳税额对应。其中，应纳税额＝工资－三险一金－起征点，即，个税＝[(工资－三险一金－起征点)×税率]－速算扣除数三险一金：养老保险、医疗保险、失业保险、住房公积金。（属于五险一金的工伤保险和生育保险个人不缴纳。）39（6）计算个人所得税表9-2工资、薪金所得适用个人所得税税率表级数全月应纳税所得额全月应纳税所得额（不含税级距）税率（%）速算扣除数1不超过1500元不超过1455元的302超过1500元至4500元的部分超过1455元至4155元的部分101053超过4500元至9000元的部分超过4155元至7755元的部分205554超过9000元至35000元的部分超过7755元至27255元的部分2510055超过35000元至55000元的部分超过27255元至41255元的部分3027556超过55000元至80000元的部分超过41255元至57505元的部分3555057超过80000元的部分超过57505元的部分451350540（6）计算个人所得税计算本公司员工的个人所得税，操作步骤如下：①突出显示应发工资中的最大值和最小值，用来观察公司最高和最低工资限值，以便简化输入个人所得税的公式。选中J列，切换到【开始】选项卡，单击【样式