第2章 数据透视表在各行业中的典型应用

第2章 数据透视表在各行业中的典型应用

本章将介绍数据透视表在几个主要行业中的常见应用，包括人力资源管理、销售管理和财务管理3大行业。通过对这3个行业典型应用的讲解，可以让读者更好地了解在实际工作中是使用数据透视表来分析和处理数据的方法。

2.1在人力资源管理中的应用

本节将介绍使用数据透视表如何解决人力资源管理中的一些问题，包括统计各部门员工人数、统计员工文化程度、统计员工年龄分布情况等内容。

2.1.1统计公司各部门男女员工的人数

对于一个拥有人员数量众多的大中型公司，人力管理人员可能需要统计各部门员工的人数，以便为公司的长期人员储备及发展做好规划。使用公式和函数固然可以完成这类统计工作，但是如果使用数据透视表，则可以起到事半功倍的效果。如图2-1所示为要进行统计的公司员工的基本信息。现在需要统计出公司各部门男员工和女员工各有多少人。

图2-1 公司员工的基本信息

使用数据透视表完成这项统计工作的具体操作如下：

（1）单击原始数据区域中的任意一个单元格。

（2）选择【插入】Ö【表格】Ö【数据透视表】命令，打开【创建数据透视表】对话框，默认选中了原始数据的整个区域，如图2-2所示。可以选择将数据透视表放置在新工作表或现有工作表中，默认放置在新工作表中。

图2-2 设置【创建数据透视表】对话框

（3）单击【确定】按钮，在新工作表中创建一个空白的数据透视表，如图2-3所示。

图2-3 创建空白的数据透视表

（4）在【数据透视表字段】窗格中，对字段进行如下布局：

将【性别】字段拖动到【列】列表框中。

将【所属部门】字段拖动到【行】列表框中。

将【工号】字段拖动到【值】列表框中。

（5）完成字段布局后，确保光标位于数据透视表内，然后单击功能区中的【设计】Ö

【布局】Ö【报表布局】按钮并选择【以表格形式显示】命令，将数据透视表转换为表格形式的外观（后面的数据透视表也都将转换为表格形式）。最终的数据透视表和【数据透视表字段】窗格如图2-4所示。

由于在本例的原始数据中，没有数值类型的数据，而都是文本类型的数据。虽然对文本类型的数据无法进行数学计算，但是可以在数据透视表中使用对文本进行计数的方式统计诸如人员数量的汇总信息。

图2-4 使用数据透视表统计各部门男女员工人数

提示：在【值】区域中放置【工号】字段而不选择放置【姓名】字段，其目的主要是为了防止因为员工重名而产生的统计计错误。但是工号对于公司内的每名员工来说是唯一的，不会发生重复的情况。当然，如果确定公司内没有重名的员工，那么在【值】列表框中放置【姓名】字段来统计员工人数也是可以的。

2.1.2按不同部门统计员工文化程度分布情况

员工是公司发展的最主要部分，可以说是公司的血液。为了更好地了解公司技术力量的整体布局，那么就需要统计员工的文化程度分布情况。除了直接统计公司所有员工的文化程度分布外，还可以进行更加详细的统计分析。例如，可以统计每个部门中员工的文化程度分布，这里有两种布局方式：

在上例中数据透视表布局的基础上，将【所属部门】字段拖动到【行】列表框中，并将该字段放在【文化程度】字段上方，得到如图2-5所示的统计结果。

图2-5 第一种统计各部门员工文化程度分布的数据透视表布局方式 将【文化程度】字段拖动到【列】列表框中，然后将【所属部门】字段拖动到【行】

列表框中，将【工号】字段拖动到【值】列表框中，得到如图2-6所示的统计结果。

图2-6 第二种统计各部门员工文化程度分布的数据透视表布局方式可见，使用数据透视表统计条件不断变化的数据汇总信息十分方便，只需几下单击即可完成。然而，如果不使用数据透视表，而是通过设计公式来进行统计，那么当改变一个统计条件时，将需要重新设计公式模型，而且还极易出现错误，实在是费力不讨好。

2.1.3统计不同年龄段的员工分布情况

利用数据透视表，可以非常方便地按指定的年龄段统计出公司员工年龄的分布情况。

这样可以更好地了解公司内部人员的年龄比例，为以后的人员招聘计划提供帮助。由于原始数据中并没有员工年龄的数据，所以需要在包含原始数据的工作表中添加一列，并输入计算年龄的公式。然后以新的数据区域来创建数据透视表。具体操作如下：（1）在原始数据的单元格I1中输入“年龄”。然后在单元格I2中输入下面的公式，用于计算第一个员工的年龄：

=INT(YEARFRAC(TODAY(),G2,1))

这个公式使用YEARFRAC函数计算以年为单位的两个日期的差。参数1表示按当年的实际天数以及每个月的实际天数计算。由于该函数将返回一个小数，因此使用INT函数对计算结果取整，最后获得员工的年龄。

提示：在Excel 2013中可以直接使用YEARFRAC函数，而在Excel 2003中需要加载“分析工具库”才可使用。

还可以使用DATEDIF函数来计算员工的年龄，该函数并未出现在【插入函数】对话框中，需要手工输入它。公式如下：

=DATEDIF(G2,TODAY(),"y")

（2）得到第一个员工的年龄后，拖动单元格I2向下填充复制，即可得到其他员工的年龄，如图2-7所示。

图2-7 计算公司员工的年龄

（3）单击原始数据区域中的任意一个单元格，然后单击功能区中的【插入】Ö【表格】Ö【数据透视表】按钮，在打开的【创建数据透视表】对话框中单击【确定】按钮，创建空白数据透视表。

（4）在空白数据透视表中，需要对字段进行如下布局，即可统计出各年龄的员工人数，如图2-8所示。

将【工号】字段拖动到【值】列表框中。

将【性别】字段拖动到【列】列表框中。

将【年龄】字段拖动到【行】列表框中。

图2-8 统计员工年龄

上图的统计结果通常来说意义不大，因为一般只需按年龄段来统计员工人数，所以还需要对上面数据透视表中的年龄进行分组。右键单击数据透视表中【年龄】字段所在列的任意一个单元格，在弹出菜单中选择“创建组”命令，打开【组合】对话框，如图2-9所示。设置将年龄从20到60每隔10年进行分组。

单击【确定】按钮，即可得到如图2-10所示的数据透视表，显示了20-29、30-39、40-

49以及50-60等不同年龄段的员工人数。

图2-9 对年龄分组图2-10 按不同年龄段统计员工人数

2.2在销售管理中的应用

本节将介绍数据透视表在销售管理中的应用，由于销售数据的数据量通常都很庞大，

因此，使用数据透视表可以高效处理这些数据，使其快速转为有意义的统计结果。