Excel在工资管理中的应用.docx

Excel在工资管理中的应用.docx

《Excel在工资管理中的应用.docx》由会员分享，可在线阅读，更多相关《Excel在工资管理中的应用.docx（18页珍藏版）》请在冰豆网上搜索。

Excel在工资管理中的应用

一、任务一输入工资数据

1、新建Excel工作簿，命名为“工资核算”，打开“工资核算表”工作薄，将sheet1命名为“基本工资信息表”。

在此表中输入如下图内容：

2、将sheet2命名为“工资核算单”,建立如下工资项目：

职工代码、职工姓名、性别、年龄、部门、工作岗位、职工类别、事假天数、病假天数、基本工资、岗位工资、职务津贴、奖金、事假扣款、病假扣款、应发工资、住房公积金、个人所得税和补发工资，如图：

3、设置数据有效性：

（1）“性别”列数据有性的设置

（2）“部门”数据有效性的设置

（3）“工作岗位”数据有效性的设置：

（4）职工类别数据有效性的设置：

4、输入数据：

根据表4—1中所给数据（课本76页），完成记录后的效果如下图：

5、添加批注：

操作方法：

选择单元格—单击右键，弹出下拉菜单—单击插入批注命令，在文本框中输入所需要信息即可。

如图所示

二、任务二设置工资项目

计算工资项目：

1、使用IF（）函数岗位工资：

2、职务津贴：

职位津贴是基本工资与岗位工资之和的10%：

因此：

L4=（J4+K4）*0.1

3、使用IF（）函数奖金：

奖金根据职工所在部门的不同而有所差别，具体规定如下表：

第一种方法：

=IF（E4="企划部",1000,IF（OR（E4="组装部",E4="机修部"）,900,IF（E4="销售部",600,800）））

第二种方法：

=IF（E4="企划部",1000,IF（OR（E4="财务部",E4="后勤部",E4="供应部"）,800,IF（OR（E4="组装部",E4="机修部"）,900,600）））

4、事假扣款：

ROUND（）四舍五入函数；

N4=ROUND（J4/22*H4,2）

5、病假扣款：

根据公司的规定，请一天病假扣款50元。

因此：

O4=I4*50

6、应发工资：

应发工资为基本工资、岗位工资、职务津贴与奖金和扣款和病假扣款。

P4=SUM（J4:

M4）-N4-O4

7、住房公积金：

根据公司规定，住房公积金为应发工资的15%，因此：

O4＝round（P4*15%,2）

8、个人所得税：

所得税根据应发工资的数额而确定

R4=IF（P4-2000<=0,0,IF（0

9、实发工资：

S4＝P4－Q4－R4

三、任务三：

工资数据的查询与统计分析

1、在“工资核算表”中利用筛选功能进行工资数据的查询

（1）以“职工姓名”为依据进行进行查询。

例如：

查询职工姓名为“李丽”的职工的工资情况：

操作方法：

a、打开“工资核算”工作表，将光标放在工作表任务的单元格中；

b、单击“数据”菜单——筛选按钮，进入筛选姿态，如下图

C、单击“职工姓名”列按钮，选择“文本筛选”：

单击“等于”，如下图：

查询结果如下图：

（2）以“部门”为依据进行查询,查询“机修部”所有职工的工资情况，结果如下图：

（3）退出筛选状态，单击“筛选”按钮，恢复数据。

2、将sheet3工作表命名为“工资总额汇总表”依据部门和职工类别的统计分析

（1）计算每一部门每一职工类别“应发工资”的汇总数：

如下图：

（2）创建透视图：

操作方法：

a、选择“工资总额汇总表”，插入—柱形图形——堆积柱形图，结果如下图：

（３）新建sheet4工作表，命名为“工资平均数汇总表”，计算每一部门每一职工类别“应发工资”的平均数汇总表，如下图：

（４）新建sheet５工作表，命名为“百分比表”，百比表的制作：

a、制作部门“应发工资”所占的百分比表

操作方法：

插入如下的透视表，值汇总方式为“求和”，值显示方式为“全部汇总百分比”，结果下图：

b、各职工类别应发工资占应发工资总和的百分比

设置方法：

值汇总方式为“求和”，值显示方式为“行汇总的百分比”，结果如下图：

C、各部门应发工资占应发工资总和的百分比

设置方法：

值汇总方式为“求和”，值显示方式为“列汇总的百分比”，结果如下图：

（５）新建sheet6工作表，命名为“工资总额统计表”，利用“工资核算”工作表制作“工资总额统计表”透视表，字段设置如下图

结果如下图：

任务四编制工资费用分配表

操作步骤：

一、新建sheet7工作表，命名为“工资费用分配表”。

1、打开“工资费用分配表”，选择A1单元格，输入“工资费用分配表”；

2、选择A1：

H1单元格区域，设置“合并及居中”。

3、根据下图设置工资费用分配表相关栏目：

4、按照（课本93页）5—12题的操作步骤设置表格，结果如下：

3、编制工资费用分配表

注释：

GETPIVOTDATA（）函数是从数据透表中查索汇总数据。

操作方法：

（1）选择D5单元格，输入“=”，点击“工资总额汇总表”，选择“企划部”对应的合计金额I9单元格，回车确认之后，“工资总额汇总表”I9单元格的数据就会引用过来。

同样，继续完成其他数据的输入

（2）定义“职工福利费”计算公式

在E5单元格输入“D5*14%，按回车键确认。

选择E5单元格，用填充柄把公式复制到E12单元格中。

（3）定义“合计”计算公式。

选择D5：

D12单元格区域，单元“自动求和”图表按钮，完成工资总额求和。

同样地，完成职工福利费求和运算。

（4）定义“工会经费”、“职工教育经费”、“社会保险金”计算公式。

在F13单元格内输入：

=D13*2%，在G13单元格输入：

=D13*1.5%，在H13单元格内输入：

=D13*32%。

得到的计算结果如下图：

18