实例九 员工出勤情况分析

9.1案例简介

9.1.1 案例需求与展示

王红是东方公司人力资源部经理，现在要根据公司1-3月份的考勤表统计第一季度员工的出勤情况，具体要求如下：

1）汇总第一季度员工出勤情况。

2）对汇总后的数据进行排序，以查看各部门员工的出勤情况。

3）筛选出秘书处迟到次数在10次及以上的人员上报总经理，筛选出硕士学历的员工缺席天数在5天及以上或早退次数在5次及以上的人员信息上报人力资源部。

4）汇总出第一季度各部门中不同学历人员的出勤情况。效果如图9-1所示。

图9-1员工出勤情况分析效果图

9.1.2知识技能目标

本案例涉及的知识点主要有：数据的合并计算、数据排序、数据的自动筛选、数据的高级筛选、数据分类汇总；

知识技能目标：

• 掌握Excel中对多个表格数据的合并计算。
• 掌握数据的多条件排序。
• 掌握数据的自动筛选与高级筛选。
• 掌握数据的分类汇总。

9.2案例实现

9.2.1合并计算

合并计算是Excel中内置的处理多区域汇总的工具。合并计算可以将几个工作表中具有相同类型的数据进行汇总，并可以在指定的位置显示计算结果。本案例中，需要将1-3月出勤考核表中的数据进行合并计算，具体操作如下：

1）打开素材中的工作簿文件“员工出勤情况表.xlsx”，单击“3月份出勤考核表”右侧的“新工作表”按钮，创建一个名为“Sheet1”的新工作表。

2）将“Sheet1”工作表重命名为“第一季度考勤汇总表”，在单元格A1中输入表格标题“第一季度考勤汇总表”，设置文本字体为“等线”、字号14。

3）在“第一季度考勤汇总表”的单元格区域A2：G2依次输入表格的列标题“工号”、“员工姓名”、“学历”、“所属部门”、“迟到次数”、“缺席天数”、“早退次数”。将“1月份出勤考核表”中的“工号”、“员工姓名”、“学历”、“所属部门”四列数据复制过来，对表格单元格进行合并居中、添加边框、设置对齐方式操作，如图9-2所示。

图9-2 新建“第一季度考勤汇总表”

4）选择“第一季度考勤汇总表”的单元格E3，切换到“数据”选项卡，单击“数据工具”功能组中的“合并计算”按钮，如图9-3所示。打开“合并计算”对话框。

图9-3 “合并计算”按钮

5）保持“函数”栏下方的“求和”不变，将光标定位到“引用位置”下方的文本框中，单击“1月份出勤考核表”工作表标签，并选择单元格区域F3：H34，返回“合并计算”对话框，单击“添加”按钮，在“所有引用位置”下方的列表框中将显示所选的单元格区域。

6）用同样的方法将“2月份出勤考核表”的单元格区域F3：H34和“3月份出勤考核表”的单元格区域F3：H34添加到“所有引用位置”的列表框中，如图9-4所示。设置完成后，单击“确定”按钮，即可在“第一季度考勤汇总表”中看到合并计算的结果，如图9-5所示。

图9-4 “合并计算”对话框

图9-5 合并计算后效果图（部分）

【合并计算】视频教程：

9.2.2数据排序

为了方便查看和对比表格中的数据，用户可以对数据进行排序。排序是按照某个字段或某几个字段的次序对数据进行重新排列，让数据具有某种规律。数据排序主要包括简单排序、复杂排序和自定义排序3种。

本案例中要查看第一季度各部门的考勤情况，我们可以对表格数据按部门进行升序排序，在部门相同的情况下分别按缺席天数、迟到次数、早退次数进行降序排序。此时的排序需要使用Excel中的复杂排序，具体操作如下：

1）复制“第一季度考勤汇总表”，并将其副本表格重命名为“第一季度考勤排序”。

2）将光标定位于“第一季度考勤排序”工作表数据区域的任意单元格中，切换到“数据”选项卡，单击“排序和筛选”功能组中的“排序”按钮，如图9-6所示。打开“排序”对话框。

图9-6 “排序”按钮

3）单击“主要关键字”右侧的下拉按钮，从下拉列表中选择“所属部门”选项，保持“排序依据”下拉列表的默认值不变，在“次序”的下拉列表中选择“升序”，之后单击“添加条件”按钮，对话框中出现“次要关键字”的条件行，设置“次要关键”为“缺席天数”、“次序”为“降序”，用同样的方法再添加两个“次要关键字”并进行如图9-7所示的设置。

图9-7 “排序”对话框

4）单击“确定”按钮，完成表格数据的多条件排序。

9.2.3 数据筛选

为了在表格中找出某些符合一定条件的数据，可以使用Excel中的数据筛选功能。在用户设定筛选条件后，系统会迅速找出符合所设条件的数据记录，并自动隐藏不满足筛选条件的记录。Exel中的数据筛选功能有自动筛选和高级筛选两种。

自动筛选一般用于简单的条件筛选，高级筛选一般用于条件比较复杂的条件筛选。高级筛选之前必须先设定筛选的条件区域。当筛选条件同行排列时，筛选出来的数据必须同时满足所有筛选条件，称为“且”高级筛选；当筛选条件位于不同行时，筛选出来的数据只需满足其中一个筛选条件即可，称为“或”高级筛选。

本案例中要求筛选出秘书处迟到次数在10次及以上的人员上报总经理，可利用自动筛选功能实现，具体操作如下：

1）复制“第一季度考勤汇总表”，并将其副本表格重命名为“第一季度考勤（上报总经理）”。

2）将光标定位于“第一季度考勤（上报总经理）” 工作表数据区域的任意单元格中，切换到“数据”选项卡，单击“排序和筛选”功能组中的“筛选”按钮，如图9-8所示。

图9-8 “筛选”按钮

3）此时工作表进入筛选状态，各标题字段的右侧均出现下三角按钮。

4）单击“所属部门”右侧的下三角按钮，在展开的下拉列表中取消勾选“财务部”、“企划部”、“销售部”、“研发部”复选框，只勾选“秘书处”复选框，如图9-9所示。

图9-9 设置筛选“所属部门”条件

5）单击“确定”按钮，表格中即筛选出了“秘书处”员工的出勤情况。

6）单击“迟到次数”右侧的下三角按钮，在展开的下拉列表中选择“数据筛选”级联菜单中的“大于或等于”命令，如图9-10所示，打开“自定义自动筛选方式”对话框。

7）设置“大于或等于”后的值为“10”，如图9-11所示。

8）单击“确定”按钮返回工作表，表格即显示了“秘书处”中“迟到次数”10次及以上员工的信息，如图9-12所示。

图9-10 设置筛选“迟到次数”条件

图9-11 “自定义自动筛选方式”对话框

图9-12 “自动筛选”效果图

本案例中要求筛选硕士学历的员工缺席天数在5天及以上或早退次数在5次及以上的人员信息上报人力资源部，可利用高级筛选功能实现。具体操作如下：

1）复制“第一季度考勤汇总表”，并将其副本表格重命名为“第一季度考勤（上报人力资源部）”。

2）在单元格区域I2：K2依次输入“学历”、“缺席天数”、“早退次数”。

3）选择I3单元格，并输入“硕士”，选择J3单元格，并输入“>=5”，选择I4单元格，并输入“硕士”，选择K4单元格，并输入“>=5”，如图9-13所示。

图9-13 设置筛选条件

4）将光标定位于“第一季度考勤（上报人力资源部）” 工作表数据区域的任意单元格中，切换到“数据”选项卡，在“排序和筛选”功能组中单击“高级”按钮，打开“高级筛选”对话框。

5）保持“方式”栏中“在原有区域显示筛选结果”单选按钮的选中，保持系统自动设置的“列表区域”A2：G34不变，单击“条件区域”后的文本框，之后选择所设置的筛选条件区域I2：K4，如图9-14所示。单击“确定”按钮，返回工作表，即可看到工作表的数据区域显示出了符合筛选条件的员工出勤信息，如图9-15所示。

图9-14 “高级筛选”对话框

图9-15 “高级筛选”结果

【数据排序与筛选】视频教程：

9.2.4 数据分类汇总

分类汇总是对Excel表格中的数据进行管理的工具之一，它可以快速地汇总各项数据，通过分级显示和分类汇总，可以从大量数据信息中提取有用的信息。分类汇总允许展开或收缩工作表，还可以汇总整个工作表或其中选定的一部分。分类汇总之前需要对数据进行排序。

本案例中要汇总出第一季度各部门中不同学历人员的出勤情况，可利用分类汇总嵌套实现，具体操作如下：

1）复制“第一季度考勤汇总表”，并将其副本表格重命名为“第一季度考勤（分类汇总）”。

2）将光标定位于“第一季度考勤（上报总经理）” 工作表数据区域的任意单元格中，切换到“数据”选项卡，单击“排序和筛选”功能组中的“排序”按钮，打开“排序”对话框，设置排序的“主要关键字”为“所属部门”、“次要关键字”为“学历”、“次序”均为“升序”，如图9-16所示。单击“确定”按钮，完成表格中数据的排序。

图9-16 “排序”对话框

3）在“数据”选项卡中单击“分级显示”功能组中的“分类汇总”按钮，如图9-17所示。打开“分类汇总”对话框。

图9-17 “分类汇总”按钮

4）设置对话框中的“分类字段”为“所属部门”、“汇总方式”为“求和”，在“选定汇总项”的列表框中勾选“迟到次数”、“制席天数”、“早退次数”复选框，保持“替换当前分类汇总”和“汇总结果显示在数据下方”复选框的选中，如图9-18所示。单击“确定”按钮，完成数据按“所属部门”进行的分类汇总操作，如图9-19所示。

图9-18 “分类汇总”对话框

图9-19 “分类汇总”效果图

5）再次打开“分类汇总”对话框，在对话框中设置“分类字段”为“学历”、“汇总方式”为“求和”、“选定汇总项”为“迟到次数”、“缺席天数”、“早退次数”，取消“替换当前分类汇总”复选框的勾选，如图9-20所示。单击“确定”按钮，完成分类汇总的嵌套，效果如图9-1所示。

图9-20 “分类汇总”对话框

6）单击“保存”按钮，保存工作簿文件，完成案例的制作。

【数据分类汇总】视频教程：

9.3案例小结

本案例通过分析员工出勤情况讲解了Excel中的合并计算，Excel数据分析中的排序、自动筛选、高级筛选和分类汇总等内容。在实际操作中大家还需要注意以下问题：

（1）Excel的排序功能很强，在“排序”对话框中隐藏着多个用户不熟悉的选项。

1）排序依据

排序依据除了默认的按“数值”排序以外，还有按“单元格颜色”、“字体颜色”、“单元格图标”进行排序，如图9-21所示。

图9-21 “排序依据”列表

2）排序选项

在“排序”对话框中做相应的设置，可完成一些非常规的排序操作，如“按行排序”、“按笔画排序”等，单击“排序”对话框中的“选项”按钮，可打开“排序选项”对话框，如图9-22所示。更改对话框的设置，即可实现相应的操作。

图9-22 “排序选项”对话框

（2）筛选时要注意自动筛选与高级筛选的区别，根据实际要求选择适当的筛选形式进行数据分析。

• 自动筛选不用设置筛选的条件区域，高级筛选必须先设定条件区域。
• 自动筛选可实现的筛选效果，用高级筛选也可以实现，反之则不一定能实现。
• 对于多条件的自动筛选，各条件之间是“与”的关系。对于多条件的高级筛选，当筛选条件在同一行，表示条件之间是“与”的关系；当筛选条件不在同一行，表示条件之间是“或”的关系。

（3）需要删除已设置的分类汇总结果时，可打开“分类汇总”对话框，单击“全部删除”按钮可实现删除已建立的分类汇总。需要注意的是，删除分类汇总的操作是不可逆的，不能通过“撤销”命令恢复。

9.4经验技巧

9.4.1对分类汇总后的汇总值排序

在实际操作中，经常会遇到要对分类汇总以后的汇总值进行排序的情况，如果直接进行排序会出现如图9-23所示的错误提示对话框。要避免此对话框的出现，以本案例为例，要对各部门的汇总后的数据，按“迟到次数”从高到低进行排序，可进行如下的操作：

图9-23 错误提示对话框

1）在已完成分类汇总操作的“第一季度考勤（分类汇总）”表中，单击二级显示按钮，以只显示汇总考勤情况，如图9-24所示。

图9-24 显示考勤汇总情况

2）选择单元E3，切换到“数据”选项卡，单击“排序和筛选”功能组中的“降序”按钮，即可实现汇总数据的排序，如图9-25所示。

图9-25 按汇总值排序后效果图

9.4.2使用通配符模糊筛选

通配符是一类键盘字符。通配符筛选是指使用通配符和文字组合的形式设置筛选条件，进行模糊筛选。通配符主要有星号（*）和问号（？）。“*”代表任意的一个或多个字符，“？”代表任意的单个字符。

本案例中，如果要筛选所有姓“王”的员工出勤情况，可进行如下操作：

1）切换到“第一季度考勤汇总表”，选择数据区域的任意单元格，切换到“数据”选项卡，单击“筛选”按钮，进入自动筛选状态。

2）单击“员工姓名”右侧的下三角按钮，在“文本筛选”下方的文本框中输入“王*”，如图9-26所示。

图9-26 设置筛选条件

3）单击“确定”按钮，工作表中只显示出了“王”姓员工的考勤情况信息，如图9-27所示。

图9-27 模糊筛选后的结果

9.5拓展练习

某公司销售部门主管大华拟对本公司产品前两季度的销售情况进行数据分析，打开素材文件夹中的工作簿文件“一二季度销售统计表.xlsx”，请按下述要求帮助大华完成统计工作：

1）复制“产品销售汇总表”，并将复制后的表格重命名为“产品销售汇总（排序）”，在新表格中按“产品类别代码”、“产品型号”升序，“一二季度总销售额”降序排序。

2）复制“产品销售汇总表”，并将复制后的表格重命名为“产品销售汇总（自动筛选）”，在新表格中筛选出“一季度销量”前10项中，“一二季度销售总额”在“1000000”元以上的产品信息。

3）复制“产品销售汇总表”，并将复制后的表格重命名为“产品销售汇总（高级筛选）”，在新表格中筛选出“一二季度销售总额”在“1000000”元以上或“总销售额排名”在前十的产品信息。

4）复制“产品销售汇总表”，并将复制后的表格重命名为“产品销售（分类汇总）”，在新表格中依据“产品类别代码”汇总出“一二季度销售总额”的平均值。（提示：因为表格套用了表样式，不能使用分类汇总功能，可以点击表格工具→设计→转换为区域，将数据区域进行转换，转换之后, 就可以用分类汇总了。）效果如图9-28所示。

图9-28 完成后的习题效果图

课件学习：