Excel在工资管理中的应用.docx
《Excel在工资管理中的应用.docx》由会员分享,可在线阅读,更多相关《Excel在工资管理中的应用.docx(15页珍藏版)》请在冰点文库上搜索。
Excel在工资管理中的应用
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(005,IF(P4-2000<=2000,(P4—2000)*0.1—25,IF(P4—2000<=5000,(P4-2000)*0。
15—125,(P4—2000)*0。
2—375))))
9、实发工资:
S4=P4-Q4-R4
三、任务三:
工资数据的查询与统计分析
1、在“工资核算表"中利用筛选功能进行工资数据的查询
(1)以“职工姓名”为依据进行进行查询。
例如:
查询职工姓名为“李丽”的职工的工资情况:
操作方法:
a、打开“工资核算”工作表,将光标放在工作表任务的单元格中;
b、单击“数据”菜单——筛选按钮,进入筛选姿态,如下图
C、单击“职工姓名”列按钮,选择“文本筛选”:
单击“等于",如下图:
查询结果如下图:
(2)以“部门”为依据进行查询,查询“机修部"所有职工的工资情况,结果如下图:
(3)退出筛选状态,单击“筛选”按钮,恢复数据.
2、将sheet3工作表命名为“工资总额汇总表”依据部门和职工类别的统计分析
(1)计算每一部门每一职工类别“应发工资"的汇总数:
如下图:
(2)创建透视图:
操作方法:
a、选择“工资总额汇总表”,插入—柱形图形——堆积柱形图,结果如下图:
(3)新建sheet4工作表,命名为“工资平均数汇总表”,计算每一部门每一职工类别“应发工资”的平均数汇总表,如下图:
(4)新建sheet5工作表,命名为“百分比表”,百比表的制作:
a、制作部门“应发工资”所占的百分比表
操作方法:
插入如下的透视表,值汇总方式为“求和",值显示方式为“全部汇总百分比",结果下图:
b、各职工类别应发工资占应发工资总和的百分比
设置方法:
值汇总方式为“求和”,值显示方式为“行汇总的百分比”,结果如下图:
C、各部门应发工资占应发工资总和的百分比
设置方法:
值汇总方式为“求和",值显示方式为“列汇总的百分比”,结果如下图:
(5)新建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%。
得到的计算结果如下图: