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