第10章(excel在人力资源管理中的应用).ppt

1、第第10章章经典实例：管理员工工资福利经典实例：管理员工工资福利 10.110.1要点分析要点分析 10.210.2制作员工工资统计表制作员工工资统计表 设置员工工资表的格式设置员工工资表的格式设置员工工资表的格式设置员工工资表的格式 计算员工销售提成奖金计算员工销售提成奖金计算员工销售提成奖金计算员工销售提成奖金 计算员工个税计算员工个税计算员工个税计算员工个税 计算员工税后工资计算员工税后工资计算员工税后工资计算员工税后工资 10.310.3制作员工工资单制作员工工资单 10.410.4制作工资发放零钞备用制作工资发放零钞备用表表 10.110.1要点分析要点分析 本章主要介绍如何使用Ex

2、cel制作管理员工工资福利的工资簿，在制作过程中会用到SUMIF函数来提取员工销售提成奖金、ROUND函数来提取税后工资等信息、OFFSET函数来制作员工工资单。为了便于读者使用函数，下面先来了解每个函数的含义及使用方法。1 1ROUNDROUND函数函数 ROUND函数用来返回按指定位数进行四舍五入的数值。其语法格式为ROUND(number,num_digits)其中各参数的含义如下：lnumber：要四舍五入的数字。lnum_digits：位数，按此位数对 number 参数进行四舍五入。2 2SUMIFSUMIF函数函数 SUMIF函数是根据指定条件对若干单元格求和。其语法格式为SUM

3、IF(range,criteria,sum_range)其中各参数的含义如下lrange：用于进行条件判断的单元格区域。lcriteria：用来确定哪些单元格将被相加求和，其形式可以为数字、表达式、文本或单元格内容。lsum_range：需要求和的实际单元格。2 2OFFSETOFFSET函数函数 OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新的引用。其返回的引用可以为一个单元格或单元格区域，并且还可以指定返回的行数或列数。其语法格式为：OFFSET(reference,rows,cols,height,width)10.210.2制作员工工资统计表制作员工工资统计表 10.2.

4、110.2.1设置员工工资表的格式设置员工工资表的格式设置员工工资表的格式设置员工工资表的格式 为了使员工工资表更加规范，并达到美观的效果，还需要对其格式进行相应的设置，具体操作步骤如下。步骤1 新建“员工工资”工作簿，然后将工作表Sheet1重命名为“员工工 资”，接着在该工作表中共设9列，分别为“员工编号”、“姓名”、“所属部门”、“职位”、“基本工资”、“岗位工资”、“奖金”、“应扣个税”和“税后工资”。步骤2 选中单元格区域A1:I1，右击选中的单元格，打开“设置单元格格式”对话框，切换至“对齐”选项卡，设置水平和垂直居中，选中“合并单元格”复选框。步骤3 切换至“字体”选项卡，设置字

5、体为“楷体”，字形为“加粗”，字号为20。设置字体格式 步骤3 切换“填充”选项卡，在“背景色”选项组中选择一种颜色，这里选择“橙色”，单击“确定”按钮。步骤4 选中单元格区域A2:H2，设置单元格字体水平和垂直居中，字形加 粗，填充色为“浅橙色”。步骤5 选择要输入员工编号的单元格区域A3:A14，输入公式“=ROW()-2”，输入公式之后，按下Ctrl+Enter组合键，将公式输入到选区中的每一个单元格并得到计算结果。步骤6 选中A1:I14单元格区域并右击，打开“设置单元格格式”对话框，在“边框”选项卡中选择线条样式，单击“外边框”和“内部”按钮。步骤7 切换至“对齐”选项卡，设置水平和

6、垂直居中。单击“确定”按钮，返回工作表，此时选中的单元格即添加了相应的边框样式。新建员工工资工作表查看添加边框后的效果 步骤8 选中E3:I14单元格区域，打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中单击“会计专用”选项，设置“小数位数”为2。步骤9 设置完成后单击“确定”按钮，选中单元格中的数据即保留两位小数，并以￥显示。利用公式计算出员工编号 10.2.210.2.2计算员工销售提成奖金计算员工销售提成奖金计算员工销售提成奖金计算员工销售提成奖金 计算员工销售提成奖金的操作步骤如下。步骤1 在“员工工资.xlsx”工作簿中把Sheet2工作表重命名为“提成”，然后在工作

7、表中录入各员工的销售情况，包括员工的姓名，员工售出商品的商品编码、商品名称、成交总金额、提成比例等信息，下面根据提成比例，在 “提成金额”列中计算出每笔销售的提成金额。步骤2 在“提成”工作表中选中F2单元格，输入公式“=D2*E2”。步骤3 按下Enter键，计算出第一笔销售提成金额。然后再次选中F2单元格，向下拖动填充柄，复制公式一直到F29单元格，计算出其他销售提成金额据。步骤4 切换到“员工工资”工作表，选中G3单元格，输入公式“=SUMIF(提成!$A$2:$A$29,B3,提成!$F$2:$F$29)”。步骤5 按下Ctrl+Shift+Enter组合键，计算出第一个员工的奖金。然

8、后选中G3单元格，向下拖动填充柄，复制公式一直到G14单元格，计算出其他员工的奖金。步骤10 若要隐藏工作表中的网格线，可在“视图”选项卡的“显示”组别中，取消选中“网格线”复选框，工作表中的网格线即被隐藏了。制作“提成”工作表获取其他员工奖金 10.2.310.2.3计算员工个税计算员工个税计算员工个税计算员工个税 只要工资超过一定的金额，个人所得税是一定要交的，下面就介绍计算个人所得税的方法。现在正在实行的所得税缴纳标准如下表所示。表 所得税缴纳标级级 数数全月应纳税所得额全月应纳税所得额税税 率率速扣数速扣数/元元1不超过1500元的部分3.00%02超过1500元4500元的部分10.

9、00%1053超过4500元9000元的部分20.00%5554超过9000元35000元的部分25.00%10055超过35000元55000元的部分30.00%27556超过55000元80000元的部分35.00%55057超过800000元的部分45.00%13505所得税起征点选择“数据透视表”命令“创建数据透视表”对话框 步骤1 在“员工工资.xlsx”工作簿中创建“工资计算各种比率表”工作 表，然后在“员工工资”工作表中插入一列“应税所得额”。步骤2 选中H3单元格，输入公式“=IF(E3+F3+G31500,0,E3+F3+G3)”，按下Enter键，计算出第一个员工当月应税所

10、得额。步骤3 再次选中H3单元格，向下拖动填充柄，复制公式一直到H14单元 格，获取其他员工当月应税所得额。步骤4 在I3单元格中输入计算员工应扣个税金额公式，计算出第一个员工应扣个税金额。接着向下填充公式，计算其他员工的扣税金额。创建“工资计算各种比率表”工作表在J3单元格中输入公 步骤5 选中J3单元格，输入公式“=E3+F3+G3-I3”，计算出第一个员工税后的工资。步骤6 选中J3单元格，向下拖动填充柄，复制公式一直到J14单元格，计算出其他员工税后的工资。计算出其他员工税后工资单击Visual Basic按钮 10.310.3制作员工工资单制作员工工资单 下面通过编写简单的VBA程序

11、来制作工资单，具体操作步骤如下。步骤1 新建“VBA”工作表，然后在“开发工具”选项卡下的“代码”组中单击Visual Basic按钮。步骤2 激活Visual Basic编辑窗口，在“工程-VBAProject”窗格中双击“Sheet1(员工工资)”选项。激活Visual Basic编辑窗口 步骤3 弹出代码编辑窗口，输入代码：Sub MakeSalaryList()Dim i As Integer Dim endrow As Integerendrow=Sheet1.Range(a65536).End(xlUp)Worksheets(1).Range(1:1).Copy(Worksheet

12、s(2).Cells(1,1)For i=3 To endrow Worksheets(1).Range(2:2).Copy(Worksheets(2).Cells(3*i-7,1)Worksheets(1).Range(Cells(i,1),Cells(i,256).Copy(Worksheets(2).Cells(3*i-6,1)Next i End Sub 代码编辑窗口选择“运行子过程/用户窗体”命令成功使用宏制作员工工资单 步骤4 在菜单栏中选择“运行”|“运行子过程/用户窗体”命令。步骤5 关闭编辑器，返回工作表，此时已经将员工工资转换成为员工工资单，在“VBA”工作表中显示。10.

13、410.4制作工资发放零钞备用表制作工资发放零钞备用表 当企业在以现金形式发放工资时，就需要准备大量的零钞，以便工资的正常发放。首先需要根据员工工资建立员工工资发放零钞备用表，并计算出各员工所需零钞数据以及各面值钞票所需数量。步骤1 新建“工资发放零钞备用表.xlsx”工作簿，并在Sheet1工作表中制作“工资发放零钞备用表”表格。步骤2 选中C3单元格，然后打开“插入函数”对话框，选择函数类别为“数学与三角函数”选项，在“选择函数”列表框中选择所需要的函数，在此选择QUOTIENT函数，再单击“确定”按钮。步骤3 弹出“函数参数”对话框，在Numerator文本框中输入“B3”，在Denom

14、inator文本框中输入“100”。员工工资发放零钞备用表选择QUOTIENT函数计算员工的工龄设置QUOTIENT函数参数 步骤4 单击“确定”按钮，返回工作表。步骤5 选中D3单元格，再打开“插入函数”对话框，在“或选择类别”下拉列表中选择“数学与三角函数”选项，在“选择函数”列表中选择QUOTIENT函数，最后单击“确定”按钮。步 骤 6 弹 出“函 数 参 数”对 话 框，在 Numerator文 本 框 中 输 入“MOD(B3,100)”，在Denominator文本框中输入“50”，表示将B3单元格除以100后的余数，再除以50取整，再单击“确定”按钮，返回工作表，此时可以看到在

15、目标单元格中显示了计算的结果。设置INT函数参数计算所需1元面值的张 步骤7 选中E3单元格，打开“插入函数”对话框，在“或选择类别”下拉列表框中选择“数学与三角函数”选项，在“选择函数”列表框中选择INT函数，再单击“确定”按钮。步骤8 弹出“函数参数”对话框，在Number文本框中输入需要进行计算的内容，在此输入“(B3-100*C3-50*D3)/20”，再单击“确定”按钮。步骤9 选中F3单元格，输入公式“=INT(B3-100*C3-50*D3-20*E3)/10)”，按Enter键，计算所需要10元面值张数。步骤10 在G3单元格中输入公式“=INT(B3-100*C3-50*D3

16、-20*E3-10*F3)/5)”，按Enter键得出结果。第一位员工工资发放所需要各面值人民币张数各员工工资发放所需要各面值人民币的张数 步骤11 在H3单元格中输入公式“=INT(B3-100*C3-50*D3-20*E3-10*F3-5*G3)/1)”，在I3单元格中输入公式“=INT(B3-100*C3-50*D3-20*E3-10*F3-5*G3-1*H3)/0.5)”，计算第一位员工工资发放所需要各面值人民币张数。步骤12 选中单元格区域C3:I3，向下填充复制公式一直到C14:I14单元格区域，即可得到各员工工资发放所需要各面值人民币的张数。步骤13 选中C16单元格，输入计算总张数公式“SUM(C3:C14)”，按Enter键，获取100元面值的总张数。输入公式获取100元面值所需总张数复制公式得到各面值所需总张数 步骤14 选中C16单元格，向右拖动填充柄，复制公式一直到I16单元格，即可得到各面值所需要的总张数。