【超实用】Excel编制工资表步骤详解

【超实用】Excel编制工资表步

骤详解

【超实用】Excel编制工资表步骤详解

。

1 建立员工工资表并输入基本数据

如下图：

职所基事病

工 姓性年属 职工 本 假 假

代名 别 龄 部类别 工天天

码 门 资 数 数

A0许管理人300

管

男 57 理

部

管徐

部 华

管

女 36 理 5

部

销

男 27 售销售员

部

销

男 32 售销售员

部

员 0 01 振

A0管理人280

02 员 0

A0张管理人260

仁女 55 理2

员 0 03 焱

200B0郑

0 01 昂

200B0李

0 02 帆

B0吴150

销

男 36 售销售员 15

部

销

男 45 售销售员

部

销

女 29 售销售员 20

部

生

男 21 产工人 2

部

生陈

工人 苏女 26 产

部 苏

生王

工人 飞女 22 产 16

部 飞

0 03 星

150B0唐

0 04 嘉

150B0孙

0 05 丽

120C0许

0 01 涛

120C0

0 02

120C0

0 03

表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-2 2010年4月工资表

2 计算“岗位工资”、“奖金”和“应发合计”

分析或说明：IF函数可以根据设定的条件在相

应单元格中自动填入数据。在2010年4月工资表中，

岗位工资和奖金根据每位员工的职工类别不同而有

所不同，具体情况如下表3-2-2所示。本任务是先用

IF函数输入岗位工资和奖金，然后用公式“应发合

计=基本工资+岗位工资+奖金”计算应发合计。

表3-2-2 岗位工资和奖金表

职工岗位工

类别 资

管理

人员

工人 200 300

500 200

奖金

销售

员

奖金＝销售额×提成比例（销售额<3万元，提成比例为2％，

300

3万元≤销售额＜10万元，比例为4％，销售额≥10万元，

比例为8％

）

步骤：

1、 光标放在H2单元格，单击“插入”→“函

数”菜单项，在弹出的“粘贴函数”对话框中选

择IF函数，单击“确定”按钮。

2、 在弹出的“函数参数”对话框中，输

入各项参数，如图3-2-3所示。

图3-2-3 if函数参数对话框之一

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-4 if函数参数对话框之二

图3-2-5计算奖金

图3-2-6 if函数参数对话框之三

3 计算“事假扣款”、“病假扣款”和“扣款合计”

分析或说明：该公司规定，病事假应扣款额和病

事假天数有关，具体扣款金额如下表3-2-3所示。本

任务利用IF函数计算病事假扣款，然后用公式“扣

款合计＝事假扣款+病假扣款”计算扣款合计。

表3-2-3 病事假扣款表

应扣款金额（元）

病事假病假

天数

事假

（应发合计

≤15 /22）×事假天200 300

数

＞15 应发合计×400 500

工其他

人 人员

80％

步骤：

1、 光标放在L2单元格中，单击“插入”→

“函数”菜单项，在弹出的“粘贴函数”对话框

中选择IF函数，单击“确定”按钮。

2、 在弹出的“函数参数”对话框中，输入

各项参数，所图3-2-7所示。在编辑栏内得到IF

函数的表达式为：

“=IF(K2<15,J2/22*K2,J2*80%)”。

图3-2-7 if函数参数对话框之四

3、 用鼠标左键按住L2单元格右下角的填充

柄向下拖拉，计算其他人员的事假扣款。

4、 光标放在2单元格中，单击“插入”→

“函数”菜单项，在弹出的“粘贴函数”对话框

中选择IF函数，单击“确定”按钮。

5、 在弹出的“函数参数”对话框中，输入

各项参数，所图3-2-8所示。在编辑栏内得到IF

函数的表达式为：

工人=IF(M2=0,0,IF(M2<=15,IF(F2="

",200,300),IF(F2="工人",400,500)))。

图3-2-8 if函数参数对话框之五

6、 用鼠标左键按住2单元格右下角的填充

柄向下拖拉，计算其他人员的病假扣款。

7、 在O2单元格中输入公式：“ =L2+2”，

单击“确定”按钮，计算扣款合计。

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 所得税税率表

速算扣级全月应纳税所得额 税

除数数 率

0 1 不超过500元的 5

25 2 超过500元至200010

125 3 超过2000元至500015

元的部分

元的部分

% （元）

4 超过5000元至20 375

20000元的部分

5 超过20000元至25 1375

40000元的部分

6 超过40000元至30 3375

60000元的部分

7 超过60000元至35 6375

80000元的部分

8 超过80000元至40 10375

100000元的部分

9 超过100000元的部45 15375

分

步骤：

1、 光标放在T2单元格，单击“插入”→“函数”

菜单项，在弹出的“粘贴函数”对话框中选择IF

函数，单击“确定”按钮。

2、 在弹出的“函数参数”对话框中，输入各项参

数，所图3-2-11所示。在编辑栏内得到IF函数

的表达式为：

“=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-11 if函数参数对话框之六

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”表示当目标单元格与设定条

件相符时返回的函数值；“value_if_false”表

示当目标单元格与设定条件不符时返回的函数

值。

IF函数可以嵌套使用，最多可以嵌套7层。以

表3-2-5为例，不同分数段要求输入不同的等级，则

IF函数为：“IF(成绩＜60，“不及格”，IF (成绩

＜70，“及格”，IF (成绩＜80,“中”，IF (成绩

＜90，“良”，“优”))))”。

表3-2-5 学生成绩表

成绩 等级

成绩＜60 不及格

60≤成绩＜70 及格

70≤成绩＜80 中

80≤成绩＜90 良

90≤成绩 优

2、有效性控制

Excel强大的制表功能给我们的工作带来了方

便，但是在数据录入过程中难免会出错，比如重复的

身份证号码，超出范围的无效数据等。其实可以通过

有效性控制来限定输入的内容，以避免错误数据的输

入。把既定的规律化的输入内容作为选项，在有效性

设置对话框中按“序列”进行设置。从序列的来源划

分，启用这种“序列”的方法也是有多种的，下面简

要描述设置方法。

① 简单序列值可直接输入

序列项目较少，并且固定不变的，可以直接在

相应的单元格设置有效性的时候，在数据来源框中输

入具体的选项。比如，希望在A1单元格只能输入“未

开始”、“进行中”、“已完成”、“已延期”四个

选项中的一个，则在有效性设置对话框中的来源输入

框中输入：“未开始,进行中,已完成,已延期”（选

项值之间的逗号是英文输入状态下的逗号）。此外，

通过有效性设置对话框的其他选项卡，可以设置“输

入信息”和“出错警告”信息。“输入信息”是指设

置有效性控制的单元格被选中时出现的输入提示性

信息；“出错警告”则是在设置有效性控制的单元格

中输入错误数据时出现的警告信息或说明。

② 导入数据序列

若在要设置有效性控制单元格的同一工作表中

已存在数据序列，则可通过导入的方式将数据序列导

入。

值得注意的是，数据有效性控制只是在直接向单

元格键入数据时才显示信息和阻止无效输入。当数据

是以复制、填充、或公式计算的方式输入时，将不会

出现有效性控制的提示。