实例一编制工资表.docx

1、实例一编制工资表实例一：编制工资表1.1 题目及要求：本实例要求使用Excel 2003编制一张“超新计算机有限公司员工工资表”。实例要求工资表中记录该公司每个员工的基本信息以及工资情况。通过该实例的操作，体会Excel 2003为我们提供的快速、准确、高效的数据处理功能。实例最终效果如图1.1所示：图1.1 员工工资表1.2 知识点应用：本例涉及的概念术语：（1） Excel电子表格（工作簿）（2） 工作表基本操作（3） 验证数据有效性（4） 数据清单（5） 记录单的使用（6） 使用公式自动计算1.3 操作步骤：下面就按照步骤进行“超新计算机有限公司员工工资表”实例的制作：1.3.1 建立工

2、资表：首先建立工资表，编制员工基本信息，包括员工编号、员工姓名、所在部门、所任职务。完成以上工作的具体操作步骤如下：（1） 新建一个Excel电子表格（工作簿）：打开Microsoft Office Excel 2003应用程序，新建一个Excel电子表格。（2） 页面设置：执行菜单栏中的【文件】|【页面设置】命令，打开“页面设置”对话框，单击【页面】标签，进行“页面设置”，如图1.2所示：图1.2 页面设置（3） 命名“工资表”：新建的Excel电子表格（工作簿）默认包含三张工作表，分别是“Sheet1”、“Sheet2”、“Sheet3”。将鼠标移动到“Sheet1”工作表标签，单击鼠标右

3、键，在弹出的快捷菜单中执行【重命名】命令（如图1.3所示），将Sheet1工作表重命名为“工资表”。图1.3 执行【重命名】命令删除工作表“Sheet2”和“Sheet3”：移动鼠标至相应工作表标签，单击鼠标右键，在弹出的快捷菜单中选择【删除】命令。（4） 设置表格标题：选中A1:O2单元格，单击“格式”工具栏中的【合并及居中】按钮（），在合并的单元格中输入表格标题“超新计算机有限公司员工工资表”。对表格标题进行格式化：通过“格式”工具栏中的选项将其字体设置为“华文琥珀”、字号设置为“16”号、字形设置为“加粗”，如图1.4所示：图1.4 字体、字号、字形设置（5） 设置列标题：在第3行中依次

4、输入列标题，分别为：编号、姓名、部门、职务、基本工资、加班金额、应发金额、病假扣款、事假扣款、失业保险金、养老保险金、应税所得额、扣税、扣款合计、实发金额。对列标题进行格式化：选中A3:O3单元格区域，执行菜单栏中的【格式】|【单元格】命令，打开“单元格格式”对话框。单击“字体”标签，进行字体、字号、字体颜色的设置。如图1.5所示：图1.5 字体、字号、字体颜色设置在“单元格格式”对话框中，单击“对齐”标签，进行“文本对齐方式”的设置。如图1.6所示：图1.6 水平对齐、垂直对齐设置在“单元格格式”对话框中，单击“边框”标签，分别单击“预置”选项区域中的【外边框】和【内部】按钮，为选中的单元格

5、区域添加边框。如图1.7所示：图1.7 边框设置在“单元格格式”对话框中，单击“图案”标签，进行“单元格底纹”的设置。如图1.8所示：图1.8 单元格底纹颜色设置单击【确定】按钮完成列标题格式化设置。设置后效果如图1.9所示：图1.9 完成列标题制作（6） 输入员工基本信息以及工资信息：在A4单元格中输入第一位员工编号“1”，设置其字体“宋体”，字号“10号”，对齐方式“居中”。使用“自动填充”功能快速生成员工编号：把光标移动至A4单元格右下角，光标变为“”形状。按住鼠标左键向下拖动，在合适的单元格里松开鼠标（有几个员工就拖动几行）。随即出现“自动填充选项”智能标记（），用鼠标左键单击“自动填

6、充选项”智能标记下三角按钮，从其列表中执行【以序列方式填充】命令，完成员工编号的快速生成。如图1.10所示：图1.10 “自动填充”员工编号填写“姓名”、“部门”、“职务”列中的内容，并设置其字体“宋体”、字号“10号”、对齐方式“居中”，完成后效果如图1.11所示：图1.11 输入基本数据1.3.2 验证数据输入的有效性：在本例的工资表中，E列是职工的“基本工资”信息，公司规定最低工资不低于800元，最高工资不高于8000元，为防止输入错误，可设置该列的数据有效性规则。具体操作步骤如下：（1） 进行格式化设置：选中“基本工资”列中的单元格，点击鼠标右键，在弹出的快捷菜单中执行【设置单元格格式

7、】命令，打开“单元格格式”对话框中的“数字选项卡”。在“分类”下拉列表中选择“货币”选项，并在“示例”选项区域中，将“小数位数”设置为“0”，“货币符号”设置为“￥”，如图1.12所示：图1.12 设置“基本工资”单元格的货币格式（2） 进行有效性设置：选中“基本工资”列中的数据单元格，执行菜单栏中的【数据】|【有效性】命令，系统打开“数据有效性”对话框。在“数据有效性”对话框中，单击【设置】标签，在“允许”下拉列表中选择“整数”，“数据”下拉列表中选择“介于”，在“最小值”输入框中输入800，在“最大值”输入框中输入8000。如图1.13所示：图1.13 设置有效性条件在“数据有效性”对话框

8、中，单击“输入信息”标签。选中“选定单元格时显示输入信息”复选框。在“标题”文本框中输入“输入规定：”，在“输入信息”文本框中输入“基本工资金额不得大于8000，小于800”。如图1.14所示：图1.14 设置提示信息在“数据有效性”对话框中，单击“出错警告”标签。在“样式”下拉列表中选择“警告”，在“标题”文本框中输入“输入错误”，在“错误信息”文本框中输入“请输入8008000之间的数据”，如图1.15所示：图1.15 设置出错警告（3） 输入基本工资数据：完成以上设置后，如果选定“基本工资”列中某个单元格，便会有提示语句出现，通过该提示可帮助输入人员正确输入数据。如图1.16所示：图1.

9、16 输入信息提示如果输入人员输入错误数据，当单击其他单元格时，会出现警告对话框，提醒输入人员所输入的数据违反有效性规则，可能是错误数据。如图1.17所示：图1.17 警告对话框1.3.3 使用记录单输入数据：使用记录单功能输入数据的操作步骤如下：（1） 选择单元格A3:O3区域，执行菜单栏中的【数据】|【记录单】命令，打开“记录单”窗口。如图1.18所示：图1.18 记录单窗口（2） 在“记录单”窗口中：【新建】按钮：新建一条记录。【删除】按钮：删除当前记录。【上一条】按钮：跳至上一条记录，可进行浏览、修改和删除操作。【下一条】按钮：跳至下一条记录，可进行浏览、修改和删除操作。【条件】按钮：

10、设置条件，对满足条件的记录进行浏览维护操作。【关闭】按钮：完成相应操作，关闭记录单窗口。（3） 使用“记录单”补充输入以下信息：使用记录单输入“加班金额”、“病假扣款”、“事假扣款”信息，完成之后的效果如图1.19所示：图1.19 完成数据输入1.3.4 使用公式自动计算：在本例的工资表中，“应发金额”、“失业保险金”、“养老保险金”、“应税所得额”、“扣税”、“扣款合计”和“实发金额”中的数据都需要使用公式计算获得。下面以工资表中第一位员工“刘新”的工资计算为例，逐一说明各公式的计算方法：（1） 计算“应发金额”：本例中，“应发金额”是“基本工资”和“加班金额”的总和。在单元格G4中输入公式

11、“=SUM(E4:F4)”，按Enter键，公式将出现在“公式栏”中，单元格G4中出现计算结果。如图1.20所示：图1.20 计算应发金额（2） 计算“失业保险金”：本例中，“失业保险金”是按照基本工资的1%计算的。在单元格J4中输入公式“=E4*0.01”，按Enter键，Excel 2003会自动计算出本月需扣除该员工的“失业保险金”为50.00。如图1.21所示：图1.21 计算失业保险金（3） 计算“养老保险金”：本例中，“养老保险金”是按照基本工资的1.4%计算的。在单元格K4中输入公式“=E4*1.4%”，按Enter键，Excel 2003将自动计算出本月需扣除的“养老保险金”为

12、70.00。如图1.22所示：图1.22 计算养老保险金（4） 计算“应税所得额”：在工资表中，所得税的计算相对复杂，不是所有人都按相同的税率纳税，也不是全部收入都要纳税。“应税所得额”是指需要纳税的那部分收入。当“应发工资”减去扣款额大于1000元时，“应税所得额”等于每个员工“应发工资”减去扣款额，再减去1000元后的值，否则，“应税所得额”等于0。这里需要用到函数IF函数。IF函数是用来进行逻辑判断与推力的函数，语法格式为：IF(Logical_test, value_if_true, value_if_false)其中参数： Logical_test，表示逻辑判断表达式。 value_

13、if_true，表示当Logical_test为TRUE时的返回值。 value_if_false，表示当Logical_test为FALSE时的返回值。在单元格L4中输入公式“=IF(G4-SUM(H4:K4)1000, G4-SUM(H4:K4)-1000,0)”）。计算结果如图1.23所示：图1.23 计算“应税所得额”（5） 计算“扣税”：不同的应税所得额有不同的税率，因此所应缴纳的税款也不同。表1.1是不同应税所得额对应的税率和速算扣除数：应税所得额税率（%）速算扣除数5005020001025500015125200002037540000251375600003033758000

14、0356375=1000004515375表1.1 税率及速算扣除数计算表前面已经计算出了“应税所得额”，现在计算需要缴纳的税款。利用IF函数的嵌套功能来实现，在单元格M4中输入公式“=IF(L4=500,L4=2000,L4=5000,L420000),L4*0.2-375)”（由于应税所得额未超过20000元，所以只计算到500020000元之间）。该公式的含义如下： “IF(L4=500,L4=2000,L4=5000,L420000),L4*0.2-375”：如果应税所得额高于5000元且低于20000元，需缴纳税金为“应税所得额20%-375”。按此公式计算，Excel自动计算出该员

15、工需要缴纳的税金为457.00。如图1.24所示：图1.24 计算所得税（6） 计算“扣款合计”：本例中，“扣款合计”是将工资表中“病假扣款”、“事假扣款”、“失业保险金”、“养老保险金”、“扣税”相加而得。在单元格N4中输入公式“=H4I4J4K4M4”，按Enter键，Excel自动计算出该员工的“扣款合计”为577.00。如图1.25所示：图1.25 计算扣款合计（7） 计算“实发金额”：“实发金额”是应发工资减去扣款合计。在单元格O4中输入公式“=G4-N4”，按Enter键，Excel自动计算出该员工的“实发金额”为4,423.00。如图1.26所示：图1.26 计算实发金额（8）

16、完成其他员工的工资计算：这时，第一位员工的工资全部计算完成，其余员工工资的计算可以使用自动填充功能。选中单元格G4，按住鼠标左键拖动单元格右下角的拖动句柄（）向下，至G列的最后以行，松开鼠标左键完成其他员工“应发金额”的公式填充。如图1.27所示：图1.27 自动填充应发金额选中单元格区域J4:O4，按住鼠标左键拖动单元格区域右下角的拖动句柄，向下至数据的最后一行，松开鼠标左键即可完成其他员工“失业保险金”、“养老保险金”、“应税所得额”、“扣税”、“扣款合计”和“实发金额”列的公式填充。如图1.28所示：图1.28 自动填充其他计算数据1.3.5 完成编制工资表：至此，完成工资表的编制。最终效果如图所示：“工资表”最终效果图1.4 实例总结：本例重点介绍了Excel公式和函数的使用，特别是IF函数的使用，此外还介绍了数据有效性的验证以及使用记录单高效地完成数据输入的功能。思考：（1） 如何快速查看所有工作表的公式？（2） 为什么单元格会出现“#！”？（3） 如何快速标注不符合有效性规则的数据？