超实用Excel编制工资表步骤详细讲解文档格式.docx
《超实用Excel编制工资表步骤详细讲解文档格式.docx》由会员分享,可在线阅读,更多相关《超实用Excel编制工资表步骤详细讲解文档格式.docx(14页珍藏版)》请在冰豆网上搜索。
A002
徐仁华
女
55
2800
2
A003
焱
36
2600
5
B001
昂
27
销售部
销售员
2000
B002
帆
32
B003
吴星
1500
15
B004
唐嘉
45
B005
丽
29
20
C001
许涛
21
生产部
工人
1200
C002
26
C003
王飞飞
22
16
表3-2-1员工根本工资和出勤情况表
分析或说明:
建立根本工资表主要是搭建工资表的框架,在工资表中列出一些有关员工工资的工程,并输入根本数据。
在输入数据时为了防止出错可以进展有效性控制。
步骤:
1、启动Excel应用程序,单击“文件〞→“另存为〞菜单项,在弹出的“另存为〞对话框中,输入文件名“工资〞,文件类型为“.xls〞。
2、双击工作表标签,将工作表“sheet1〞重命名为“2010年4月〞。
3、在工作表第一行输入如下标题:
职工代码、、性别、年龄、所属部门、职工类别、根本工资、岗位工资、奖金、应发合计、事假天数、事假扣款、病假天数、病假扣款、扣款合计、养老金、医疗金、公积金、应发工资、所得税、实发工资。
4、参照表3-2-1中的数据输入第一列“职工代码〞和第二列“〞。
5、光标放在C2单元格,单击“数据〞→“有效性〞菜单项,翻开“数据有效性〞对话框,如图3-2-1所示。
6、在“允许〞列表中选择“序列〞,在“来源〞中输入“男,女〞〔用英文逗号隔开〕,单击“确定〞按钮完成设置。
7、将光标移动到C2单元格右下角,当光标变为实心的十字形时,按住鼠标左键向下拖拉,将C2单元格的有效性控制复制到C列其他单元格。
图3-2-1数据有效性对话框
8、根据表格3-2-1的数据输入“性别〞、“年龄〞“所属部门〞和“职工类别〞等数据,其中“所属部门〞和“职工类别〞两列数据采用有效性控制输入,最后结果如图3-2-2所示。
图3-2-22010年4月工资表
2计算“岗位工资〞、“奖金〞和“应发合计〞
IF函数可以根据设定的条件在相应单元格中自动填入数据。
在2010年4月工资表中,岗位工资和奖金根据每位员工的职工类别不同而有所不同,具体情况如下表3-2-2所示。
本任务是先用IF函数输入岗位工资和奖金,然后用公式“应发合计=根本工资+岗位工资+奖金〞计算应发合计。
表3-2-2岗位工资和奖金表
职工类别
岗位工资
奖金
500
200
300
奖金=销售额×
提成比例〔销售额<
3万元,提成比例为2%,3万元≤销售额<10万元,比例为4%,销售额≥10万元,比例为8%〕
1、光标放在H2单元格,单击“插入〞→“函数〞菜单项,在弹出的“粘贴函数〞对话框中选择IF函数,单击“确定〞按钮。
2、在弹出的“函数参数〞对话框中,输入各项参数,如图3-2-3所示。
图3-2-3if函数参数对话框之一
3、用鼠标按住H2单元格右下角的填充柄向下拖拉复制函数,输入其他人员的岗位工资。
4、翻开素材文件夹中的“销售额.doc〞文档,单击“编辑〞→“复制〞菜单项,复制全部数据。
5、光标放在工作表sheet2的A1单元格,单击“编辑〞→“粘贴〞菜单项,将Word文档表格中的数据转换成Excel工作表,双击工作表标签“sheet2〞,将其重命名为“销售表〞。
6、光标放在“2010年4月〞工作表的I2单元格,单击“插入〞→“函数〞菜单项,在弹出的“粘贴函数〞对话框中选择IF函数,单击“确定〞按钮。
7、在弹出的“函数参数〞对话框中,输入各项参数,所图3-2-4所示。
8、用鼠标左键按住I2单元格右下角的填充柄向下拖拉复制函数,输入其他人员的奖金,结果如图3-2-5所示。
9、光标放在I5单元格,单击“插入〞→“函数〞菜单项,在弹出的“粘贴函数〞对话框中选择IF函数,单击“确定〞按钮。
10、在弹出的“函数参数〞对话框中,输入各项参数,所图3-2-6所示。
在Excel编辑栏可得到如下IF函数的表达式:
“=IF(F5="
销售员"
IF(销售表!
E2<
3,销售表!
E2*2%,IF(销售表!
E2>
=10,销售表!
E2*8%,销售表!
E2*4%))*10000)〞。
11、用鼠标左键按住I5单元格的填充柄,向下拖拉到I9为止,输入其他销售员的奖金。
12、单击J2单元格,输入公式:
“=G2+H2+I2〞,单击“确定〞按钮,计算应发合计。
13、用鼠标左键按住J2单元格的填充柄,向下拖拉输入其他人员的应发合计。
图3-2-4if函数参数对话框之二
图3-2-5计算奖金
图3-2-6if函数参数对话框之三
3计算“事假扣款〞、“病假扣款〞和“扣款合计〞
该公司规定,病事假应扣款额和病事假天数有关,具体扣款金额如下表3-2-3所示。
本任务利用IF函数计算病事假扣款,然后用公式“扣款合计=事假扣款+病假扣款〞计算扣款合计。
表3-2-3病事假扣款表
病事假天数
应扣款金额〔元〕
其他人员
≤15
〔应发合计/22〕×
事假天数
>15
应发合计×
80%
400
1、光标放在L2单元格中,单击“插入〞→“函数〞菜单项,在弹出的“粘贴函数〞对话框中选择IF函数,单击“确定〞按钮。
2、在弹出的“函数参数〞对话框中,输入各项参数,所图3-2-7所示。
在编辑栏得到IF函数的表达式为:
“=IF(K2<
15,J2/22*K2,J2*80%)〞。
图3-2-7if函数参数对话框之四
3、用鼠标左键按住L2单元格右下角的填充柄向下拖拉,计算其他人员的事假扣款。
4、光标放在N2单元格中,单击“插入〞→“函数〞菜单项,在弹出的“粘贴函数〞对话框中选择IF函数,单击“确定〞按钮。
5、在弹出的“函数参数〞对话框中,输入各项参数,所图3-2-8所示。
=IF(M2=0,0,IF(M2<
=15,IF(F2="
工人"
200,300),IF(F2="
400,500)))。
图3-2-8if函数参数对话框之五
6、用鼠标左键按住N2单元格右下角的填充柄向下拖拉,计算其他人员的病假扣款。
7、在O2单元格中输入公式:
“=L2+N2〞,单击“确定〞按钮,计算扣款合计。
8、用鼠标左键按住O2单元格右下角的填充柄向下拖拉,计算其他人员的扣款合计。
9、选择L2:
O12数据区域,单击“格式〞→“单元格〞菜单项,在弹出的对话框中单击“数字〞选项卡。
10、在“数字〞选项卡的“数值〞分类中,将事假扣款、病假扣款、扣款合计的小数位数设置为2位,如图3-2-9所示。
图3-2-9各项扣款
4计算“应发工资〞
应发工资=应发合计-扣款合计-养老金-医疗金-公积金。
其中,养老金按应发合计的8%扣除,医疗金按应发合计的2%扣除,公积金按应发合计的10%扣除。
本任务先分别计算“养老金〞、“医疗金〞和“公积金〞,然后再用上述公式计算应发工资。
1、光标放在P2单元格中,输入公式:
“=J2*8%〞,按回车键算出养老金。
2、用鼠标左键按住P2单元格右下角的填充柄向下拖拉,计算其他人员的养老金。
3、光标放在Q2单元格中,输入公式:
“=J2*2%〞,按回车键算出医疗金。
4、用鼠标左键按住Q2单元格右下角的填充柄向下拖拉,计算其他人员的医疗金。
5、光标放在R2单元格中,输入公式:
“=J2*10%〞,按回车键算出公积金。
6、用鼠标左键按住R2单元格右下角的填充柄向下拖拉,计算其他人员的公积金。
7、光标放在S2单元格中,输入公式:
“=J2-O2-P2-Q2-R2〞,按回车键算出应发工资。
8、用鼠标左键按住S2单元格右下角的填充柄向下拖拉,计算其他人员的应发工资,如图3-2-10所示。
图3-2-10计算应发工资
5计算“所得税〞和“实发工资〞
我国相关税法规定,工资、薪金所得应缴纳个人所得税。
即每月取得工资收入后,先减去个人承当的根本养老保险金、医疗保险金和失业保险金,以及按省级政府规定标准缴纳的住房公积金,再减去2000元/月〔个人所得税起征额为2000元〕,为应纳税所得额,按5%至45%的九级超额累进税率计算应缴纳个人所得税。
计算公式为:
“个人所得税=应纳税所得额×
税率-速算扣除数〞。
表3-2-4所得税税率表
级数
全月应纳税所得额
税率%
速算扣除数〔元〕
1
不超过500元的
超过500元至2000元的局部
10
25
3
超过2000元至5000元的局部
125
4
超过5000元至20000元的局部
375
超过20000元至40000元的局部
1375
6
超过40000元至60000元的局部
30
3375
7
超过60000元至80000元的局部
35
6375
8
超过80000元至100000元的局部
40
10375
9
超过100000元的局部
15375
1、光标放在T2单元格,单击“插入〞→“函数〞菜单项,在弹出的“粘贴函数〞对话框中选择IF函数,单击“确定〞按钮。
2、在弹出的“函数参数〞对话框中,输入各项参数,所图3-2-11所示。
“=IF(S2-2000<
500,0,IF(S2-2000<
2000,(S2-2000)*10%-25,IF(S2-2000<
5000,(S2-2000)*15%-125,IF(S2-2000<
20000,(S2-2000)*20%-375,"
复核"
))))〞。
3、考虑到该企业员工的应发工资在扣除2000元之后,没有超过20000元的,所以IF函数只判断到S2-2000<
20000,当S2-2000≥20000时,作复核处理。
图3-2-11if函数参数对话框之六
4、用鼠标左键按住T2单元格右下角的填充柄向下拖拉,计算其他人员的所得税。
5、光标放在U2单元格中,输入公式:
“=S2-T2〞,按回车键计算实发工资。
6、用鼠标左键按住U2单元格右下角的填充柄向下拖拉,计算其他人员的实发工资,如图3-2-12所示。
图3-2-12所得税和实发工资
总结
本工程是制作一员工工资表,包括工资表数据输入与统计分析。
在数据输入时可以利用有效性控制和IF函数,快速正确地输入指定数据。
工资表数据输入完成后,可以运用数据透视表及数据透视图进展汇总及统计分析。
拓展延伸
1、IF函数的使用
函数格式:
if(logical_test,value_if_true,value_if_false)。
其中:
“logical_test〞表示设定的条件;
“value_if_true〞表