项目5 员工工资管理系统

任务2 建立工资表表格。

【任务分析】

根据任务要求，要求使用已建立基本数据表格：基本工资表、员工考勤表、员工奖金表、员工补贴表等，来建立工资表。

【操作步骤】

1. 插入新工作表并重新命名为“工资表”，A1单元格内输入标题，A2单元格内输入单位名称和日期，A3:K3单元格内依次输入输入编号、姓名、部门、职务、基本工资、奖金、补贴、应发工资、扣款、扣税及实发工资等，对输入的相关数据进行格式化处理。

2. 为了防止输入错误，可以对“部门”列加以控制。选中C4，单击【数据】/【有效性】，在弹出的【数据有效性】对话框中切换到【设置】选项卡中，在【允许】下拉列表中选【序列】，点确定。然后在【来源】文本框中输入“办公室，销售部，物流部”，输入的时候要注意用“，”分隔。如图6-5所示：

6

图 5-5 在【来源】文本框中输入

单击“确定”返回到工作表中，拖动C4填充柄将有效性填充到其他单元格。

同样方法，也可设置“职务”列，只允许输入“总经理，经理，文员，员工”。

3. 依次在表格中输入“编号”、“姓名”，然后在“部门”、“职务”列下拉列表中选择数据，其他数据暂不输入。如图6-6所示：

图 5-6 工资明细表

4. 依次从相关工作表中将数据复制粘贴（如原来工作表使用公式计算，则要用选择性粘贴）到“基本工资”、“奖金”、“补贴”列中并对输入的数据进行格式化设置。计算应发工资，在单元格H4中输入公式：=SUM(E4:G4)，然后利用自动填充功能将公式填充到该列的其他单元格。

5. 计算扣款。假设请假扣款为“基本工资/30*请假天数”，选中I4单元格，选择【插入】/【函数】，在【插入函数】对话框中的【选择函数】列表中选择【ROUND】函数，

6

在参数对话框中的【Number】文本框中输入“E4/30*考勤表!E3”, 在参数对话框中的【Num_digits】文本框中输入“0”。如图5-7所示：

图 5-7【ROUND】函数参数设置

单击“确定”，返回工作表，利用自动填充功能将公式填充到该列的其他单元格并设置格式。

10. 计算扣税。假设只有基本工资超过3000元的员工才交所得税，而且交纳金额为“基本工资*15%”。选中J4，输入公式：=ROUND(IF(E4>3000,E4*0.15,0),0)，利用自动填充功能将公式填充到该列的其他单元格并设置格式。

11. 计算实发工资，在单元格K4中输入公式：=H4-I4-J4，然后利用自动填充功能将公式填充到该列的其他单元格。如图5-8所示：

6

图 5-8 工资表制作结果

5-1 模块小结

本模块主要介绍了如何建立基本数据表格：基本工资表、员工考勤表、员工奖金表、员工补贴表以及建立工资表表格。

建立基本数据表格的时候主要要用到工作簿的管理以及电子表格的编辑和格式化的相关知识。

建立工资表表格则要求对公式和函数能够熟练使用。

模块 5-1：工资数据的管理

学习目标：

1. 会对工作表中数据进行查询

6

2. 会对工作表中数据进行分析

5-2-1工作任务

1. 查询“销售部”工资数据。

2. 分析各部门不同职工类别的应发工资的平均值。

5-2-2 操作步骤

任务1 查询“销售部”工资数据。

【任务分析】

该任务是为在“工资表”中查询出所有“销售部”员工的工资情况。

【操作步骤】

1. 单击单元格区域“A3:K3”，选择【数据】/【筛选】/【自动筛选】菜单项，即进入可筛选状态。

2. 单击“部门”所在的单元格右侧的下拉按钮，从弹出的列表中选择【销售部】，则可显示筛选结果。如图5-9所示：

6

图 5-9 筛选出的数据信息

3. 如果想退出筛选状态，再次选择【数据】/【筛选】/【自动筛选】菜单项即可。

任务2 分析各部门不同职工类别的应发工资的平均值

【任务分析】

该任务是通过工资表中的数据能够分析出各部门不同职工类别的应发工资的平均值。

【操作步骤】

4. 选择【数据】/【数据透视表和数据透视图】菜单项，弹出【数据透视表和数据透视图向导-3步骤之1】对话框，在【请指定待分析数据的数据源类型】组合框中选择【Microsoft Office Excel数据列表或数据库】单选按钮，在【所需创建的报表类型】组合框中选择【数据透视图（及数据透视表）】。如图5-10所示：

6

图 5-10 选择报表类型

单击“下一步”，弹出【数据透视表和数据透视图向导-3步骤之2】对话框，单击【选定区域】文本框右侧按钮选择单元格区域“A3:K13”。

单击“下一步”，弹出【数据透视表和数据透视图向导-3步骤之3】对话框，在【数据透视表显示位置】组合框中选择【新建工作表】单选按钮。

5. 单击“布局”按钮，弹出【数据透视表和数据透视图向导-布局】对话框，将【应发工资】字段，【部门】字段和【职务】字段拖至相应位置。如图5-11所示：

6

图 5-11 设置布局

6. 双击“求和项：应发工资”按钮，在弹出的【数据透视表字段】对话框中将【汇总方式】列表中选择【平均值】选项。

7. 单击“确定”按钮，返回到【数据透视表和数据透视图向导-布局】对话框中，单击“确定”按钮，返回到【数据透视表和数据透视图向导-3步骤之3】对话框中，单击“完成”按钮，即可在此工作簿中插入一个工作表图表Chart1。如图5-12所示：

图 5-12 生成图表

5-2 模块小结

本模块主要介绍了如何对工作表中的数据进行查询以及利用工作表中的数据能够进行简单的数据分析。

工作表中的数据进行查询主要要用到EXCEL的筛选功能。

利用工作表中的数据能够进行简单的数据分析则主要要用到EXCEL的数据透视图功

6

能。

6

模块 5-3：工资条的管理

学习目标：

1. 会使用邮件合并功能

2. 会打印文档

5-3-1工作任务

1. 利用“工资表”数据制作工资条。

2. 打印工资条。

5-3-2 操作步骤

任务1 利用“工资表”数据制作工资条。

【任务分析】

该任务是利用“工资表”数据制作工资条，可使用WORD中的邮件合并功能。

【操作步骤】

1. 打开“员工工资”工作簿，在“工资表”标签上点右键，选“移动或复制工作表”，“建立副本”前打勾，按“确定”，生成“工资表”的副本“工资表 (2)”，在“工资表 (2)”

6

中删除第1、2行，使得使用邮件合并功能时能正确引用数据。

2. 新建WORD文档，保存为“工资条.doc”，选择【文件】/【页面设置】，将文档方向设置为横向。将工作表“工资表 (2)”中“A1:K1”区域数据复制到“工资条.doc”中，生成表格，将光标移动到最后一个单元格后按回车键，添加一行。对表格进行格式设置，结果如图5-13所示：

图 5-13 生成表格

3. 选择【工具】/【信函与邮件】/【邮件合并】菜单项，右侧任务窗格中出现“选择收件人”界面，如图5-14所示：

图 5-14 选择收件人

4. 收件人中选“使用现有列表”，然后单击“使用现有列表”区的“浏览”链接，打开

6

“选取数据源”对话框，选择“员工工资”工作簿，弹出“选择表格”对话框，数据存放在“工资表 (2)”工作表中，于是在“工资表 (2)”被选中的情况下单击“确定”按钮，如图5-15所示：。

图 5-15 选中“工资表 (2)”

然后弹出“邮件合并收件人”对话框，可以在这里选择哪些记录要合并到主文档，默认状态是全选。这里保持默认状态，单击“确定”按钮，返回Word编辑窗口。

5. 在右侧任务窗格中，单击“下一步：撰写信函”，右侧任务窗格中出现“撰写信函”界面，如图5-16所示：

图 5-16 “撰写信函”界面

6

6. 光标定位在“工资条.doc”中的“编号”下方的单元格中，接着单击任务窗格中的“其它项目”链接，打开“插入合并域”对话框，“数据库域”单选框被默认选中，“域(F)：”下方的列表中出现了数据源表格中的字段。接下来我们选中“编号”，单击“插入”按钮后，数据源中该字段就合并到文档中，如图5-17所示：

图 5-17 插入合并域

同样步骤完成剩余单元格的填充。如图5-18所示：

图 5-18插入合并域后的表格

7. 在右侧任务窗格中，单击“下一步：预览信函”，右侧任务窗格中出现“撰写信函”界面，如图5-19所示：

6

图 5-19 “预览信函”界面

可以看到文档中的带有“《》”符号的字段，变成数据源表中的第一条记录中信息的具体内容，单击任务窗格中的“<<”或“>>”按钮可以浏览批量生成的其它工资条。

8. 在右侧任务窗格中，单击“下一步：完成合并”，右侧任务窗格中出现“完成合并”界面，如图5-20所示：

图 5-20 “完成合并”界面

9. 单击“合并”区的“编辑个人信函”，在弹出“合并到新文档”对话框中还可以指定范围，这里我们选择“全部”，如图5-21所示：

6

图 5-21 “合并到文档”界面

按“确定”后，即可完成工资条的制作。

任务2 打印工资条

【任务分析】

该任务是通过制作完工资条后完成工资条的打印。

【操作步骤】

10. 返回到“完成合并”界面，单击“合并”区的“打印”，在弹出“合并到打印机”对话框中指定范围，这里我们选择“全部”。如图5-22所示：

图 5-22 “合并到打印机”界面

6

11. 如果你的电脑上装了打印机和打印纸，单击“确定”按钮，弹出“打印”对话框，再单击一次“确定”。即可完成工资条的打印。

5-3 模块小结

本模块主要介绍了如何利用“工资表”数据制作工资条以及打印工资条。

利用“工资表”数据制作工资条主要要用到邮件合并功能。

打印工资条则主要要用到邮件合并功能中的“合并到打印机”功能。

2

打开考生文件夹中的ex1.xls文件，进行如下操作：

(1) 在\"学生成绩表\"中，计算各学生的总成绩和平均成绩，平均成绩不显示小数，并根据总成绩由高到低的顺序，依次填入学生对应的名次（总成绩最高的为1，不考虑并列名次）；

步骤提示：利用求和和平均2个函数分别计算总成绩和平均成绩，注意数据范围。利用单元格的格式或工具栏按钮，修改小数的位数。利用排序按钮或者排序菜单对总成绩进行降序排序，注意选择方法。利用填充的方法填入学生名次。

(2) 按系别的升序排序后分类汇总各系学生英语、数学、计算机的平均成绩，汇总结果显示在数据下方；

步骤提示：按系别进行升序排序，然后选择分类汇总命令，分类字段为系别，汇总方式为平均，重点理解汇总字段需要选择的内容（提示：英语、数学、计算机）

(3) 折叠汇总项，基于\"系别\"、\"英语\"、\"数学\"、\"计算机\"四列数据，创建化学、生物、外语、物理各系汇总后数据的图表，并嵌入\"学生成绩表\"中，图表类型为\"簇状柱形图\"，要求系列产生于行，图表标题为\"各系平均成绩对照表\"；

步骤提示：理解折叠汇总项的含义，分析需要制作图表的数据（提示：选择\"系别\"、\"英语\"、\"数学\"、\"计算机\"四列数据，不包含总计数据），图表制作可按图表向导提示进行操作。

(5) 将工作簿以文件名：EX，文件类型：Microsoft Excel工作簿（*.XLS），存放于考

2

生文件夹中。步骤：单击“文件”下“另存为”，输入正确文件名，选择正确路径保存。