《员工工资管理》PPT课件.ppt

任务9 员工工资管理 工资管理是企业管理的一项重要内容，采用规范的工资管理办法可以调动员工工作积极性，维持企业的长久发展。由于员工工资的结算来自多项数据，如基本工资、奖金、考勤扣款、社会保险扣款等，应用Office Excel 2013建立工资管理表格，既能便于数据之间的相互调用，又能应用Excel强有力的公式和函数功能实现快速计算，这样大大减小了工资统计与计算人员的工作负担，提高工作效率。下面是完成的公司员工工资管理表格设计及统计计算后的效果图。公司员工工资统计计算完成效果公司员工工资统计计算完成效果2 目 录9.2 任务分析9.3 工资管理表格设计与计算 拓展实训 综合实践9.1 任务情境39.1 任务情境为了充分调动员工的工作积极性，公司领导决定调整公司的工资发放办法，而且随着信息技术的发展，公司的各项管理办法和技术也都要与时俱进，实现科学化和现代化，那么公司员工的工资管理也不例外4知识目标和能力目标知识目标：u粘贴链接的使用u批注的新建、编辑及删除uExcel公式的编辑方法u常用函数求和、求平均值、求最大值、最小值的使用方法u排名函数RANK()的使用方法u区分单元格的相对引用和绝对引用及各自应用场合u查找和引用函数，HLOOKUP()和VLOOKUP()的用法与区别u条件计数函数COUNTIF()的用法5知识目标和能力目标能力目标：u能够熟练使用常用函数求和、求平均值、求最大值、最小值，计数等u能够熟练编辑EXCEL公式解决实际计算问题u能够熟练应用IF函数解决实际问题u会应用排名函数RANK()排名次u会应用HLOOKUP()和VLOOKUP()函数，快速查找引用其他工作表中的数据u会根据工资表制作工资条u会为工作表奇数行和偶数行设置不同的填充颜色u能够综合应用所学知识规划和设计企业工资管理表格及进行统计与计算69.2 任务分析经过分析，完成员工的工资计算与统计需要进行以下工作。（1）设计员工工资调整表格，计算调整工资；（2）设计考勤表，计算考勤扣款；（3）由员工的销售业绩表计算销售提成；（4）设计员工的社会保险缴存表，计算员工的住房公积金、医疗保险、养老保险等扣款；（5）计算员工依法交纳的个人所得税；（6）由员工的基本信息导入工资表中所需的“工号、姓名、部门、基本工资”等信息；（7）统计与计算员工的月应发工资、所有扣款、实发工资。79.3.2 9.3.2 员工工资调整表设计员工工资调整表设计9.3.1 9.3.1 建立员工工资管理工作簿建立员工工资管理工作簿9.3.4 9.3.4 由员工考勤统计表计算考勤扣款由员工考勤统计表计算考勤扣款9.3.6 9.3.6 员工工资统计结算表设计员工工资统计结算表设计9.3.5 9.3.5 由员工销售提成表计算提成金额由员工销售提成表计算提成金额9.3.3 9.3.3 员工社会保险缴存表设计员工社会保险缴存表设计9.3 任务实现：工资管理表格设计与统计计算89.3.1 建立建立员工工工工资管理工作簿管理工作簿 便于操作和查看，把与工资统计有关的数据归类存储，在一个工作簿“企业员工工资管理.xlsx”中建立“员工工资统计表”、“工资调整表”、“考勤统计表”和“销售提成表”、“社会保险缴存表”五个工作表。操作如下。（1）新建工作簿，保存为“企业员工工资管理.xlsx”。（2）插入新工作簿Sheet1并重命名为“员工工资统计表”。（3）插入新工作簿Sheet2并重命名为“工资调整表”。（4）插入新工作簿Sheet3并重命名为“社会保险缴存表”。（5）插入工作表Sheet4并重命名为“考勤统计表”。（6）插入工作表Sheet5并重命名为“销售提成表”。99.3.2 员工工工工资调整表整表设计要想要想统计员工的工工的工资，必，必须先先对员工的工工的工资调整情况整情况进行行统计。下面建立工下面建立工资调整表，完成效果整表，完成效果109.3.2 员工工工工资调整表整表设计1导入部分数据入部分数据由于由于员工工工工资表中的基本信息如表中的基本信息如“工号、姓名、部工号、姓名、部门、基本工、基本工资”是和是和员工档案信息是相同的，所以可以直接从档案信息中工档案信息是相同的，所以可以直接从档案信息中导入入部分数据。操作步部分数据。操作步骤如下。如下。（1）复制任）复制任务8完成的完成的“公司公司员工档案工档案.xlsx”，并打开文件，按，并打开文件，按住住“Ctrl”键选中中“工号、姓名、部工号、姓名、部门、基本工、基本工资”四列信息，右四列信息，右击弹出快捷菜出快捷菜单，在快捷菜，在快捷菜单中中选择【复制复制】命令。命令。（2）在新建的）在新建的“企企业员工工工工资管理管理”工作簿中，工作簿中，单击A1单元格，元格，切切换到到【开始开始】选项卡，卡，单击【剪剪贴板板】选项组中的中的【粘粘贴】按按钮，在，在弹出的下拉列表中出的下拉列表中选择【粘粘贴链接接】选项，则导入了入了“工号、姓名、部工号、姓名、部门、基本工、基本工资”四列数据。四列数据。119.3.2 员工工工工资调整表整表设计2完成表格完成表格设计在表格在表格顶部插入部插入三行，分三行，分别输入入表格表格标题、制作、制作日期和日期和审批人，批人，依次依次设置置标题效效果，并果，并输入相入相应数据，数据，设置表格置表格边框和底框和底纹。129.3.2 员工工工工资调整表整表设计2完成表格完成表格设计函数参数的范函数参数的范围默默认为所所选单元格左元格左边的数的数值区域区域D4:F4，但是，但是实际只需要求只需要求单元格元格D4:E4的和，所以必的和，所以必须修改默修改默认的范的范围，用鼠，用鼠标选择单元格区域元格区域D4:E4或者在或者在编辑栏里修改里修改F4为E4。然后。然后单击单击编辑栏的的 按按钮或者按回或者按回车键确确认输入。双入。双击G4的填充柄，复制公式至的填充柄，复制公式至单元格元格H21，完成向下自，完成向下自动填充填充139.3.2 员工工工工资调整表整表设计3计算算调整后工整后工资单击G4单元格，切元格，切换到到【公式公式】选项卡，卡，单击【函数函数库】选项组中的中的【自自动求和求和】按按钮，在，在弹出的下拉列表的出的下拉列表的选择【求求和和】选项。149.3.3 员工社会保工社会保险缴存表存表设计建立社会保建立社会保险缴存表效果存表效果159.3.3 员工社会保工社会保险缴存表存表设计操作步操作步骤如下：如下：（1）打开素材文件打开素材文件“社会保社会保险缴存表存表.xlsx”，复制表中的信，复制表中的信息到息到“企企业员工工工工资管理管理.xlsx”中的中的“社会保社会保险缴存表存表”工作表工作表中中169.3.3 员工社会保工社会保险缴存表存表设计（2）计算个人算个人应该缴存的住房公存的住房公积金、医金、医疗保保险、养老保、养老保险、失、失业保保险179.3.3 员工社会保工社会保险缴存表存表设计（2）计算个人算个人应该缴存的住房公存的住房公积金、医金、医疗保保险、养老保、养老保险、失、失业保保险单击F2单元格，在元格，在F2单元格元格输入公式：入公式：“=(D2+E2)*12%”，如，如图9-9所示，按回所示，按回车键或或单击编辑栏的的 按按钮，确，确认输入入。189.3.3 员工社会保工社会保险缴存表存表设计（2）计算个人算个人应该缴存的住房公存的住房公积金、医金、医疗保保险、养老保、养老保险、失、失业保保险双双击单元格元格F2的填充柄，向下自的填充柄，向下自动填充至填充至单元格元格F19，自，自动计算其他算其他员工的住房公工的住房公积金数据。金数据。计算医算医疗保保险，在，在单元元G2中中输入公式：入公式：“=(D2+E2)*2%+3”。计算算员工养老保工养老保险：在：在单元格元格I2中中输入公式：入公式：“=(D2+E2)*8%”。计算算员工失工失业保保险：在：在单元格元格J2中中输入公式：入公式：“=(D2+E2)*0.2%”。在在H2中中输入大病保入大病保险缴存金存金额“5，双，双击填充柄填充柄向下填充向下填充199.3.3 员工社会保工社会保险缴存表存表设计uExcel的公式以的公式以“=”开开头，输入公式入公式时必必须先先输入入“=”，公式与数学表达式基本相同，由参与运算的，公式与数学表达式基本相同，由参与运算的数据和运算符数据和运算符组成。成。u公式中的运算符要用英文半角符号。公式中的运算符要用英文半角符号。u当公式引用的当公式引用的单元格的数据修改后，公式的元格的数据修改后，公式的计算算结果会自果会自动更新。更新。209.3.3 员工社会保工社会保险缴存表存表设计（4）插入表格）插入表格标题。格式化表格效果如。格式化表格效果如图219.3.4 由由员工考勤工考勤统计表表计算考勤扣款算考勤扣款（1）打开素材）打开素材“员工月考勤表工月考勤表.xlsx”将将“三月三月”工作表数据粘工作表数据粘贴链接接到到“员工考勤工考勤统计表表”工作表中。工作表中。（2）给“应扣工扣工资”所在的所在的单元格插入批注。元格插入批注。插入批注的操作步插入批注的操作步骤如下：如下：单击G1单元格，切元格，切换到到【审阅】选项卡，卡，单击【批注批注】选项组中的中的【新建批注新建批注】按按钮229.3.4 由由员工考勤工考勤统计表表计算考勤扣款算考勤扣款 在在弹出的文本框中出的文本框中输入入“事假扣除当天工事假扣除当天工资，病假扣除当天工，病假扣除当天工资的的25%。”输入完入完毕后后单击工作表中的任意一个工作表中的任意一个单元格，即可退出元格，即可退出批注的批注的编辑状状态239.3.4 由由员工考勤工考勤统计表表计算考勤扣款算考勤扣款（3）计算算应扣工扣工资。将将单元格区域元格区域G2:G19的数据格式的数据格式设置置为小数位数是小数位数是2位的位的货币类型。型。单击G2单元格，在元格，在G2单元格或元格或编辑栏中中输入公式：入公式：“=(D2/30)*E2+(D2/30)*25%*F2”。249.3.4 由由员工考勤工考勤统计表表计算考勤扣款算考勤扣款（4）添加表格）添加表格标题，格式化表格，效果如，格式化表格，效果如图259.3.5 由由员工工销售提成表售提成表计算提成金算提成金额完成效果完成效果269.3.5 由由员工工销售提成表售提成表计算提成金算提成金额（1）打开素材）打开素材“销售提成售提成统计表表.xlsx”，复制，复制“提成提成统计表表”到工作到工作表表“员工工销售提成表售提成表”。（2）填入提成比例。）填入提成比例。根据公司根据公司规定，定，员工基本工基本销售任售任务是是20 000元，也就是元，也就是销售售额在在20 000元以下按照元以下按照5%提成，提成，销售售额在在20 00049 999元的按照元的按照10%提提成，成，销售售额在在5万至万至10万的按照万的按照20%提成，提成，10万以上按照万以上按照30%提成。提成。279.3.5 由由员工工销售提成表售提成表计算提成金算提成金额（2）填入提成比例）填入提成比例设置置“销售提成表售提成表”的的“提成比例提成比例”列的数据列的数据类型型为百分百分比形式，没有小数位数。比形式，没有小数位数。单击D3单元格，切元格，切换到到【公式公式】选项卡，卡，单击【函函数数库】选项组中的中的【插入函数插入函数】按按钮，弹出出【插入函数插入函数】对话框。框。在在【插入函数插入函数】对话框框中中选择【HLOOKUP】函数函数289.3.5 由由员工工销售提成表售提成表计算提成金算提成金额（2）填入提成比例）填入提成比例弹出出查找和引用函数找和引用函数HLOOKUP的的【函数参数函数参数】对话框，在框，在对话框框中中输入函数各个参数入函数各个参数，299.3.5 由由员工工销售提成表售提成表计算提成金算提成金额（2）填入提成比例）填入提成比例参数参数Lookup_value的的值：“C3”指在参数指在参数Table_array设定的定的查找区域的首行找区域的首行查找找C3单元格的元格的值，即，即“26,500”。参数参数Table_array的的值：“$B$12:$E$13”参数参数Row_index_num的的值：“2”匹配有近似匹配和精确匹配两种匹配有近似匹配和精确匹配两种选择，由，由Range_lookup参数参数设定。当定。当设为近似匹配近似匹配时，如果找不到精确匹配，如果找不到精确匹配值，则返回小于返回小于lookup_value 的最大数的最大数值。309.3.5 由由员工工销售提成表售提成表计算提成金算提成金额（2）填入提成比例）填入提成比例函数返回函数返回结果果319.3.5 由由员工工销售提成表售提成表计算提成金算提成金额（3）计算提成金算提成金额。单击E3单元格，元格，输入公式：入公式：“=C3*D3”。销售提成售提成计算算结果：果：329.3.6 员工工工工资统计结算表算表设计（1）引用工作表）引用工作表“工工资调整表整表”中的数据到中的数据到“员工工工工资统计表表”中。中。选中中“工工资调整表整表”中的中的“工号、姓名、部工号、姓名、部门、基本工、基本工资、调后工后工资”，右右击弹出快捷菜出快捷菜单，在快捷菜，在快捷菜单中中选择【复制复制】命令。命令。切切换到到“员工工工工资统计表表”中，中，单击A1单元格。元格。切切换到到【开始开始】选项卡，卡，单击【剪剪贴板板】选项组中的中的【粘粘贴】按按钮，在在弹出的下拉列表中出的下拉列表中选择【粘粘贴链接接】选项。（2）同）同样的方法复制的方法复制“社会保社会保险缴存表存表”中的中的“薪薪级工工资、缴存合存合计额”粘粘贴到到“员工工工工资统计表表”中相中相应列中。列中。（3）同）同样的方法引用的方法引用“考勤考勤统计表表”中的中的“应扣工扣工资”到到“员工工工工资统计表表”中。把中。把M1单元格的元格的值“应扣工扣工资”改改为“考勤扣款考勤扣款”。339.3.6 员工工工工资统计结算表算表设计（4）引用）引用“销售提成表售提成表”中的提成金中的提成金额到到“员工工工工资统计表表”中。中。选中中单元格元格I1，在，在I1单元格里元格里输入文本入文本“销售提成售提成”。按照按照“工号工号”从从“销售提成表售提成表”中找到中找到对应的的销售人售人员的的“提成金提成金额”。由于只有由于只有销售人售人员才有才有销售提成，所以只有当部售提成，所以只有当部门名称是名称是“销售部售部”时才才填入填入“员工工销售提成表售提成表”时提成金提成金额，否，否则销售提成是售提成是0，所以需用，所以需用“IF()”条件函数完成。条件函数完成。单击单元格元格I2，输入入IF条件函数：条件函数：=IF(C2=销售部售部,VLOOKUP(A2,销售提成表售提成表!$A$3:$E$9,5,0),0)349.3.6 员工工工工资统计结算表算表设计VLOOKUP 函数函数说明：明：C2=“销售部售部”，则返回返回查找和引用函数找和引用函数VLOOKUP(A2,员工工销售提成售提成表表!$A$3:$E$9,5,0)的的值。359.3.6 员工工工工资统计结算表算表设计VLOOKUP(A2,销售提成表售提成表!$A$3:$E$9,5,0)的各个参数含的各个参数含义：参数参数1：A2 表示表示查找找A2单元格的元格的值参数参数2：销售提成表售提成表!$A$3:$E$9表示在表示在“销售提成表售提成表”的的单元格区域元格区域A3:E9中中查找找参数参数3：5 表示函数要返回的数据在表示函数要返回的数据在查找区域的第找区域的第5列即列即E列列参数参数4：0表示表示设定定为精确匹配。精确匹配。369.3.6 员工工工工资统计结算表算表设计由于由于 C3的的值是是“行政部行政部”，即，即IF条件函数中的条件条件函数中的条件“C3=销售部售部”为假，假，所以函数所以函数值即即I3的的值为“0”。（5）计算算应发工工资。员工的工的应发工工资调后工后工资薪薪级工工资缴存合存合计考勤扣款考勤扣款销售提成，操作步售提成，操作步骤如下。如下。选中中单元格元格J1，输入入“应发工工资”。选中中单元格元格J2，输入公式：入公式：“=E2+F2-G2-H2+I2”。按回。按回车键确确认输入，如入，如图9-22所示。所示。双双击填充柄自填充柄自动填充至填充至单元格元格J19。379.3.6 员工工工工资统计结算表算表设计（6）计算个人所得税算个人所得税企企业在在发放工放工资之前，之前，还应该根据根据员工的工工的工资情况扣除其情况扣除其应缴纳的的“个人所个人所得税得税”。2015年我国个税起征点年我国个税起征点3500元。元。个人所得税的个人所得税的计算公式如下：算公式如下：个税个税应纳税税额税率速算扣除数，税率和速算扣除数分税率速算扣除数，税率和速算扣除数分别与不同的与不同的应纳税税额对应。其中，其中，应纳税税额工工资三三险一金起征点，一金起征点，即，个税即，个税(工工资三三险一金起征点一金起征点)税率税率速算扣除数速算扣除数三三险一金：养老保一金：养老保险、医、医疗保保险、失、失业保保险、住房公、住房公积金。（属于五金。（属于五险一一金的工金的工伤保保险和生育保和生育保险个人不个人不缴纳。）。）389.3.6 员工工工工资统计结算表算表设计（6）计算个人所得税算个人所得税399.3.6 员工工工工资统计结算表算表设计（6）计算个人所得税算个人所得税计算本公司算本公司员工的个人所得税，操作步工的个人所得税，操作步骤如下：如下：突出突出显示示应发工工资中的最大中的最大值和最小和最小值，用来，用来观察公司最高和最低察公司最高和最低工工资限限值，以便，以便简化化输入个人所得税的公式。入个人所得税的公式。选中中J列，切列，切换到到【开始开始】选项卡，卡，单击【样式式】选项组中的中的【条件条件格式格式】按按钮，选择【项目目选取取规则】【前前10项】，弹出出【前前10项】对话框，将左框，将左侧微微调框中的数字框中的数字设置置为【1】，即指前，即指前1项，单击【确确定定】按按钮，则突出突出显示最大示最大值是是“38476”。409.3.6 员工工工工资统计结算表算表设计（6）计算个人所得税算个人所得税计算本公司算本公司员工的个人所得税，操作步工的个人所得税，操作步骤如下：如下：突出突出显示示应发工工资中的最大中的最大值和最小和最小值，用来，用来观察公司最高和最低察公司最高和最低工工资限限值，以便，以便简化化输入个人所得税的公式。入个人所得税的公式。419.3.6 员工工工工资统计结算表算表设计（6）计算个人所得税算个人所得税计算本公司算本公司员工的个人所得税，操作步工的个人所得税，操作步骤如下：如下：计算个人所得税。算个人所得税。选中中K2单元格，元格，输入入简化后的公式：化后的公式：=IF(J2=3500,0,IF(J2-3500)=1500,(J2-3500)*3%-0,IF(J2-3500)=4500,(J2-3500)*10%-105,IF(J2-3500)=9000,(J2-3500)*20%-555,(J2-2000)*25%-1005)429.3.6 员工工工工资统计结算表算表设计（7）计算算员工工实发工工资。员工的工的实发工工资应发工工资个人所得税。操个人所得税。操作如下：作如下：选中中单元格元格L1，输入入“实发工工资”。选中中单元格元格L2，输入公式：入公式：=J2-K2，回，回车确确认输入。入。双双击填充柄自填充柄自动计算其他算其他员工的工的实发工工资。（8）添加表格）添加表格标题、底、底纹，格式化工作表使其美，格式化工作表使其美观，数据清晰，数据清晰，439.3.6 员工工工工资统计结算表算表设计工工资明明细结算表效果算表效果44 拓拓 展展 实 训实训1：统计员工培工培训成成绩对员工的在职培训可以提高员工的业务能力，增强企业在市场中的竞争力。为了了解员工的培训效果，可以进行培训测试，请打开“在职培训成绩表”如图所示，统计与计算员工的在职培训成绩。45 拓拓 展展 实 训实训1：统计员工培工培训成成绩要求如下：（1）计算每个员工在职培训成绩的平均分，总分和名次。（2）求总分最高分。（3）求总分最低分。（4）统计考试总人数。46实训2：完善填充学生：完善填充学生奖学金信息表学金信息表素材“学生奖学金信息表.xlsx 拓拓 展展 实 训47实训2：完善填充学生：完善填充学生奖学金信息表学金信息表素材“学生奖学金信息表.xlsx 拓拓 展展 实 训48实训2：完善填充学生：完善填充学生奖学金信息表学金信息表其中学号的前四位代表年级，第5、6位表示学院代号，如：“201501130007”，2015表示2015年级，01为文学院代号。奖学金发放标准如下：奖学金等级为“一等”，奖学金额为5000元，奖学金等级为“二等”，奖学金额为4000元奖学金，其他为3000元。要求完成以下操作：（1）从学号中提取学生年级信息，填充年级列（2）从学号中提取学生所属学院代号并从工作表“学院信息”自动找到对应的学院名称（3）按照奖学金发放标准计算学生的奖学金（4）统计获得5000元奖学金的人数（5）为奇数行和偶数行设置不同的填充颜色 拓拓 展展 实 训49实训3：制作：制作员工工工工资条条工资条是发放工资时使用的一项清单，通常在发放工资时也将工资条发放给员工。由“工资明细表.xlsx”中的工作表制作出员工的工资条。拓拓 展展 实 训50制作效果制作效果 拓拓 展展 实 训51 调研一家你身边的企业，了解其工资发放规定和办法。假设让你负责这家企业每个月的员工工资统计与计算，请你根据公司的实际情况规划和设计员工工资管理表格并统计计算，制作和打印员工的工资条。综 合 实 践52