kl800.com省心范文网

第10章(excel在人力资源管理中的应用)_图文

第10章 经典实例:管理员工工资福利

●10.1要点分析
●10.2制作员工工资统计表
●设置员工工资表的格式

●计算员工个税 ●计算员工税后工资

● 10.3制作员工工资单 ● 10.4制作工资发放零钞备用 表

●计算员工销售提成奖金

● 10.1 要点分析
本章主要介绍如何使用Excel制作管理员工工资福利的工资簿,在制 作过程中会用到SUMIF函数来提取员工销售提成奖金、ROUND函数来提 取税后工资等信息、OFFSET函数来制作员工工资单。为了便于读者使用 函数,下面先来了解每个函数的含义及使用方法。 1.ROUND函数
ROUND函数用来返回按指定位数进行四舍五入的数值。其语法格式为 ROUND(number, num_digits) 其中各参数的含义如下: ? number:要四舍五入的数字。 ? num_digits:位数,按此位数对 number 参数进行四舍五入。。

2.SUMIF函数
SUMIF函数是根据指定条件对若干单元格求和。其语法格式为 SUMIF(range,criteria,sum_range) 其中各参数的含义如下 ? range:用于进行条件判断的单元格区域。 ? criteria:用来确定哪些单元格将被相加求和,其形式可以为数字、表达式、 文本或单元格内容。 ? sum_range:需要求和的实际单元格。

2.OFFSET函数
OFFSET函数是以指定的引用为参照系,通过给定偏移量得到新的引用。其返回的 引用可以为一个单元格或单元格区域,并且还可以指定返回的行数或列数。其语法格 式为: OFFSET(reference,rows,cols,height,width)

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

新建员工工资工作表

设置字体格式

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

利用公式计算出员工编号

查看添加边框后的效果

步骤8 选中E3:I14单元格区域,打开“设置单元格格式”对话框,在“数 字”选项卡的“分类”列表框中单击“会计专用”选项,设置“小数位数”为 2。 步骤9 设置完成后单击“确定”按钮,选中单元格中的数据即保留两位小 数,并以¥显示。

步骤10 若要隐藏工作表中的网格线,可在“视图”选项卡的“显示”组别 中,取消选中“网格线”复选框,工作表中的网格线即被隐藏了。
● 10.2.2 计算员工销售提成奖金 计算员工销售提成奖金的操作步骤如下。 步骤1 在“员工工资.xlsx”工作簿中把Sheet2工作表重命名为“提成”, 然后在工作表中录入各员工的销售情况,包括员工的姓名,员工售出商品的 商品编码、商品名称、成交总金额、提成比例等信息,下面根据提成比例, 在 “提成金额”列中计算出每笔销售的提成金额。 步骤2 在“提成”工作表中选中F2单元格,输入公式“=D2*E2”。 步骤3 按下Enter键,计算出第一笔销售提成金额。然后再次选中F2单元 格,向下拖动填充柄,复制公式一直到 F29单元格,计算出其他销售提成金 额据。 步骤4 切换到“员工工资”工作表,选中G3单元格,输入公式“=SUMI F(提成!$A$2:$A$29,B3,提成!$F$2:$F$29)”。 步骤5 按下Ctrl+Shift+Enter组合键,计算出第一个员工的奖金。然后选 中G3单元格,向下拖动填充柄,复制公式一直到G14单元格,计算出其他员 工的奖金。

制作“提成”工作表

获取其他员工奖金

● 10.2.3 计算员工个税 只要工资超过一定的金额,个人所得税是一定要交的,下面就介绍计算个 人所得税的方法。 现在正在实行的所得税缴纳标准如下表所示。

表 所得税缴纳标
级 数 全月应纳税所得额 税 率 速扣数/元

1

不超过1500元的部分

3.00%

0

2

超过1500元~4500元的部分

10.00%

105

3

超过4500元~9000元的部分

20.00%

555

4

超过9000元~35000元的部分

25.00%

1005

5

超过35000元~55000元的部分

30.00%

2755

6

超过55000元~80000元的部分

35.00%

5505

7

超过800000元的部分

45.00%

13505

所得税起征点

步骤1 在“员工工资.xlsx”工作簿中创建“工资计算各种比率表”工作 表,然后在“员工工资”工作表中插入一列“应税所得额”。 步骤2 选中H3单元格,输入公式“=IF(E3+F3+G3<1500,0,E3+F3+G3)”, 按下Enter键,计算出第一个员工当月应税所得额。 步骤3 再次选中H3单元格,向下拖动填充柄,复制公式一直到H14单元 格,获取其他员工当月应税所得额。 步骤4 在I3单元格中输入计算员工应扣个税金额公式,计算出第一个员工 应扣个税金额。接着向下填充公式,计算其他员工的扣税金额。

选择“数据透视表”命令

创建“工资计算各种比率表”工作表

“创建数据透视表”对话框

步骤5 选中J3单元格,输入公式“=E3+F3+G3-I3”,计算出第一个员工税 后的工资。 步骤6 选中J3单元格,向下拖动填充柄,复制公式一直到J14单元格,计 算出其他员工税后的工资。

在J3单元格中输入公

计算出其他员工税后工资

● 10.3 制作员工工资单
下面通过编写简单的VBA程序来制作工资单,具体操作步骤如下。 步骤1 新建“VBA”工作表,然后在“开发工具”选项卡下的“代码” 组中单击Visual Basic按钮。 步骤2 激活Visual Basic编辑窗口,在“工程-VBAProject”窗格中双击 “Sheet1(员工工资)”选项。

单击Visual Basic按钮

激活Visual Basic编辑窗口

步骤3 弹出代码编辑窗口,输入代码:
Sub MakeSalaryList() Dim i As Integer Dim endrow As Integer endrow = Sheet1.Range("a65536").End(xlUp) Worksheets(1).Range("1:1").Copy (Worksheets(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.4 制作工资发放零钞备用表
当企业在以现金形式发放工资时,就需要准备大量的零钞,以便工资的 正常发放。首先需要根据员工工资建立员工工资发放零钞备用表,并计算出 各员工所需零钞数据以及各面值钞票所需数量。

步骤1 新建“工资发放零钞备用表.xlsx”工作簿,并在Sheet1工作表中制 作“工资发放零钞备用表”表格。 步骤2 选中C3单元格,然后打开“插入函数”对话框,选择函数类别为 “数学与三角函数”选项,在“选择函数”列表框中选择所需要的函数,在此 选择QUOTIENT函数,再单击“确定”按钮。 步骤3 弹出“函数参数”对话框,在Numerator文本框中输入“B3”,在 Denominator文本框中输入“100”。

选择QUOTIENT函数 员工工资发放零钞备用表

步骤4 单击“确定”按钮,返回工作表。 步骤5 选中D3单元格,再打开“插入函数”对话框,在“或选择类别” 下 拉列表 中选 择 “数 学与 三角函 数 ” 选项, 在“ 选择函 数” 列表中选择 QUOTIENT函数,最后单击“确定”按钮。 步 骤 6 弹 出 “ 函 数 参 数 ” 对 话 框 , 在 Numerator 文 本 框 中 输 入 “MOD(B3,100) ”,在 Denominator 文本框中输入“50”,表示将B3 单元格除 以100后的余数,再除以50取整,再单击“确定”按钮,返回工作表,此时可 以看到在目标单元格中显示了计算的结果。

设置QUOTIENT函数参数

计算员工的工龄

步骤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-20*E310*F3)/5)”,按Enter键得出结果。

设置INT函数参数

计算所需1元面值的张

步骤11 在 H3 单元格中输入公式“ =INT((B3-100*C3-50*D3-20*E3-10*F35*G3)/1) ”,在 I3 单元格中输入公式“ =INT((B3-100*C3-50*D3-20*E3-10*F35*G3-1*H3)/0.5)”,计算第一位员工工资发放所需要各面值人民币张数。 步骤12 选中单元格区域C3:I3,向下填充复制公式一直到C14:I14单元格区 域,即可得到各员工工资发放所需要各面值人民币的张数。

第一位员工工资发放所需要各面值人民币张数

各员工工资发放所需要各面值人民币的张数

步骤13 选中C16单元格,输入计算总张数公式“=SUM(C3:C14)”,按 Enter键,获取100元面值的总张数。

步骤14 选中C16单元格,向右拖动填充柄,复制公式一直到I16单元格, 即可得到各面值所需要的总张数。

输入公式获取100元面值所需总张数

复制公式得到各面值所需总张数


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

第10章(excel在人力资源管理中的应用) - 第10章 经典实例:管理员工工

第1章(excel在人力资源管理中的应用)_图文.ppt

第1章(excel在人力资源管理中的应用) - 第1章 认识Excel管理人力资

第6章(excel在人力资源管理中的应用)_图文.ppt

第6章(excel在人力资源管理中的应用) - 第6章 经典实例:管理员工合同

第5章(excel在人力资源管理中的应用)_图文.ppt

第5章(excel在人力资源管理中的应用) - 第5章 经典实例:管理人员招聘与

第8章(excel在人力资源管理中的应用) - 图文 - 百度文库.ppt

第8章(excel在人力资源管理中的应用) - 第8章 经典实例:管理员工休假

第2章(excel在人力资源管理中的应用)_图文.ppt

第2章(excel在人力资源管理中的应用) - 第2章 经典实例:制作人事资料管

EXCEL在人力资源管理中的应用(课件)_图文.ppt

EXCEL在人力资源管理中的应用(课件)_管理学_高等教育_教育专区。Excel在人力资源管理中的应用 人力资源部 2011-05 内数据录入类 统计分析类 创建图表类 在HRM中...

Excel在人力资源管理中的应用(共 43张PPT)_图文.ppt

Excel在人力资源管理中的应用(共 43张PPT) - LOGO Excel在人力资源管理中的应用 ARROW +0 课程大纲 数据录入 § 单元格的引用 § 数据有效性 § 条件格式...

Excel在人力资源管理中的应用课件(PPT 43张)_图文.ppt

Excel在人力资源管理中的应用课件(PPT 43张)_语文_初中教育_教育专区。Excel LOGO Excel在人力资源管理中的应用 课程大纲数据录入 ? 单元格的引用 ? 数据有效性 ...

Excel在人力资源管理中的应用_图文.ppt

Excel在人力资源管理中的应用 - Excel在人力资源管理中的应用 人力资源

Excel在人力资源管理中的应用(PPT 43张)_图文.ppt

Excel在人力资源管理中的应用(PPT 43张)_人力资源管理_经管营销_专业资料。Excel 人力资源部门 Excel在人力资源管理中的应用用好工具,高效工作 课程大纲数据录入 ?...

Excel在人力资源管理的应用(极其精美实用)_图文.ppt

Excel在人力资源管理的应用(极其精美实用) - LOGO Excel在人力资源管理中的应用 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO 认识Exc...

【Excel教程】Excel在人力资源管理的应用_图文.ppt

【Excel教程】Excel在人力资源管理的应用 - 人力资源部门 Excel在人力资源管理中的应用 用好工具,高效工作 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? ...

【深度解读】Excel在人力资源管理的应用 PPT课件_图文.ppt

【深度解读】Excel在人力资源管理的应用 PPT课件 - LOGO Excel在人力资源管理中的应用 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO 认...

EXCEL在人力资源管理中的应用_图文.ppt

EXCEL在人力资源管理中的应用_幼儿读物_幼儿教育_教育专区 人阅读|次下载 EXCEL在人力资源管理中的应用_幼儿读物_幼儿教育_教育专区。 ...

EXCEL在人力资源管理中的应用 PPT课件_图文.ppt

EXCEL在人力资源管理中的应用 PPT课件 - 什么是函数? ? Excel中

Excel在人力资源管理的应用1教程_图文.ppt

Excel在人力资源管理的应用1教程 - LOGO Excel在人力资源管理中的应用 ARROW +0 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO ...

office使用窍门-Excel在人力资源管理中的应用_图文.pdf

office使用窍门-Excel在人力资源管理中的应用 - LOGO Excel在人力资源管理中的应用 课程大纲 v 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO ...

Excel在人力资源管理的应用_图文.ppt

Excel在人力资源管理的应用 - 人力资源部门 Excel在人力资源管理中的应用 用好工具,高效工作 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 认识...

Excel_2010在人力资源管理中的应用_图文.pdf

Excel_2010在人力资源管理中的应用 - 第 章 Excel 2010在人力资源管理 中的应用 高手指引 在人力资源管理中,经常会用到表格的设计与建立,系统的建立和信息的筛选...