数据透视表实现数据分析

马竹青

摘要：利用Excel数据透视表或数据透视图这类数据分析工具实现数据分析，是一种易学、简捷的平台，也是快速分析数据的工具，随着Excel版本更新，将具有更强大的数据分析能力。

关键词：数据分析；数据透视表

中图分类号：TP37 文献标识码：A 文章编号：1009-3044（2015）21-0058-02

数据透视表是Excel平台上一种交互式报表，也是对大量数据快速汇总、建立交叉列表的交互式动态表格，帮助用户更好地分析与组织数据，快速排序、汇总、筛选，实现数据的计算和统计，并按照数据表格的不同字段从多个视角进行透视；特点是从大量无关联的数据中寻找内在的联系，以体现“透视”表格的能力，数据透视表的操作便捷快速，只需要拖拽鼠标，就可以获得不同的报表，使复杂问题简单化，提高工作效率，数据透视表是Excel强大功能之一。使用数据透视表可以深入分析数值数据，以帮助用户挖掘关键数据，做出企业的决策。

1 数据透视表

1.1数据透视表的数据源

若要创建数据透视表，要求数据源是较规则的数据，例如，表格的第一行是字段名称，字段名称不能为空；数据中最好不要有空白单元格或布局里不要有合并单元格；每个字段中数据的数据类型必须一致，数据越规则，数据透视表使用起来越方便。如果数据量较大时使用更能体现数据透视表的优势。

数据源可以来自于Excel工作簿，也可以是其他格式的数据源，例如，Access数据库、SQL Sever数据库、纯文本文件、OLAP联机事务处理等，如图1所示。

1.2创建数据透视表

数据透视表的格局类似于三维空间的立体效应，如图2所示，它可以动态地改变其版面布局，以便按照不同模式分析数据，也可以重新安排行标签、列标签和页字段（报表筛选）。每一次改变字段位置时，数据透视表会立即重新统计汇总数据。另外，如果原始数据发生更改，也可以更新数据透视表来反映变化。

数据透视表字段列表有四个区域：

报表筛选：添加字段到报表筛选区可以使该字段包含在数据透视表的筛选区域中，以便对其独特的数据项进行筛选；

列标签：添加一个字段到“列标签”区域可以在数据透视表顶部显示来自该字段的独特的值；

行标签：添加一个字段到“行标签”区域可以在数据透视表左边的整个区域显示来自该字段的独特的值；

数值：添加一个字段到“数值”区域，可以使该字段包含在数据透视表的数值区域中，并使用该字段中的值进行指定的计算，例如，求和、计数、平均值、最大值等。

若将字段拖动到“行标签”区域，则此字段中的每类项目会成为一行；若将字段拖动到“列字段”区域，则此字段种的每类项目会成为一列；若将字段拖动到“数值”区域，则会自动计算此字段的汇总信息；若将字段拖动到“报表筛选”区域，则可以根据此字段对报表实现筛选，也可以理解为水平面方向截取不同截面获取不同统计结果，一般就将较大范围的分类拖动到此区域，以实现报表筛选，如图3所示。

1.3数据透视表的布局

Excel 2013提供了3种布局样式，新样式“压缩布局”是用户的数据透视表的默认样式：

以压缩形式显示：默认情况下所有数据透视表都使用压缩布局，在这种布局中，行标签区域中的多个字段挤压到一列中，压缩形式适合使用“展开”和“折叠”图标；默认情况下汇总显示在每组的顶部；

以大纲形式显示：此显示使最外层的行字段填充第一列，其他的字段占据第二、三列等。若希望将数据透视表的数值复制到新的位置，这种布局比较合适；默认情况下汇总显示在每组的顶部；

以表格形式布局：是传统的显示方式，使用分类汇总出现在组的底部，若希望在后续分析中得到的汇总数据，表格形式的布局也许是最好的布局。

1.4数据透视表的值显示方式

默认的值显示方式是“普通”，Excel还提供了更多的显示方式，有：差异、百分比、差异百分比、按某一字段汇总、占同行数据总和的百分比、占同列数据总和的百分比、占总和的百分比、指数等。

2 数据透视表的操作

2.1 一般操作

1）数据透视表的格式化

为了修饰与美化数据透视表，即对字体、边框、填充等设置格式，方法类似于工作表单元格格式化的设置，包含自动套用格式、条件格式等，同时，Excel数据透视表工具也提供了其他方法来实现格式化，例如，在“设计”选项卡的“数据透视表样式”组；

2）数据透视表的排序

数据透视表具有排序功能，分：升序、降序、手动拖曳方法，数字和字母排序一般是按ASCII码的大小来排序；汉字默认情况下按拼音排序，也可以设置按笔画排序；若以上排序规则达不到需要，可以使用手动排序，达到所需要的排序结果。

3）数据透视表的刷新

更新数据透视表或数据透视图中的内容以便反映基本源数据改变而带来变化。

4）数据透视表的复制或移动

若需要使用同一数据源创建另一个数据透视表，只需要复制第一个生成的数据透视表即可，免去从头开始创建的一系列操作，以提高效率；另外可以在同一工作表中复制多个数据透视表，实现数据比较和对照等。

5）数据透视表的分组

Excel通过对数字、日期、文本等不同数据类型的数据项提供了多种组合方式，极大增加了数据透视表分类汇总的灵活性。

6）数据透视表的多个分类汇总项

若需要对同类数据实现不同的汇总方式，可以利用“字段设置”对话框的自定义功能实现，以便获得同类数据的平均值、总和、最大值等。

7）数据再透视

将数据源先按照大的分类字段单独创建数据透视表，然后将各个分类字段的数据透视表作为数据源再创建一个多重合并计算数据区域的数据透视表，以提供不同数据汇总结果。

2.2整理数据透视表字段

1）重命名字段

当建立数据透视表时，所建立的字段名称被Excel改名了，类似于“销售总额”修改为“求和项：销售总额”或“平均值项：销售总额”，使列宽增加，影响表格美观。如果用户需要修改为“销售总额”，会失败的，因为它已经是数据的字段名称，只要略有不同就可以，方法是单击此单元格，输入“销量总额”，依次修改其他字段名称。

2）删除字段

对不需要在数据透视表中显示的字段，可以选中字段名称，鼠标右击，在打开的快捷菜单中选择“删除‘字段名”命令即可实现删除该字段。

3）隐藏字段标题

若不需要显示字段标题，可以通过“数据透视表工具”的“选项”选项卡里的“字段标题”命令实现。

4）折叠与展开字段

通过“数据透视表工具”的“选项”选项卡的“活动字段”组里的“展开整个字段”或“折叠整个字段”命令实现。

3 数据透视表的计算

数据透视表一旦建立后，不允许手工更改，例如，插入新的单元格，或者添加公式计算。实际上，如果增加了计算功能，数据透视表将具有更强的功能。在Excel提供了计算字段和计算项来实现。

3.1计算字段和计算项

计算字段是通过对数据透视表中现有的字段执行计算后得到的新字段，该字段使用用户创建的公式。计算字段可使用数据透视表中其他字段中的数据计算而生成。公式中不仅可以实现加、减、乘、除简单运算，还可以使用SUM、IF、AND、NOT、OR、COUNT、AVERAGE、TEXT等函数的复杂运算，但是不能使用单元格引用和名称。

计算项是在数据透视表的现有字段中插入新的项，通过对该字段的其他项执行计算后得到该项的值。

二者区别：计算字段是对不同列已有字段计算而添加的新字段；而计算项是对同一列中不同行的数据计算而添加的新项，即在同一字段中，不同项之间的运算而产生。

3.2数据透视表函数

数据透视表函数用于检索存储在数据透视表中的汇总数据。

语法：GETPIVOTDATA（data_field，pivot_table，field1，item1，field2，item2，...），其中：

Data_field ：包含要检索的数据的字段名称，用引号括起来；

Pivot_table ：用于决定哪个数据透视表含有要检索的数据，一般是数据透视表的左上角单元格地址；

Field1， Item1， Field2， Item2 ：用于描述要检索数据的字段名称和项名称，用引号引起来，可以按任何顺序排列，可以使用1 到 126 对。例如，"[产品]"，"[产品].[所有产品].[食品].[烤制食品]"

通俗含义如图4所示：3.3数据透视表的动态功能。

当生成数据透视表的数据源区域发生变化，例如，增删行或列的数据时，只要是数据区域连续的增删，也希望数据透视表随着新的数据源区域变化而变化，这就需要使用动态技术。这种技术就是利用函数定义名称，由名称代替改变的数据源区域。

4 数据分析案例

这是一个公司2011年1至6月份各部门采暖费、电费、水费等项目使用情况数据表，如图5所示是部分数据，针对这个数据表，进行各部门费用使用情况分析，了解变化趋势等。

图5

我们需要创建两个数据透视图和相关切片器，如图6所示，通过切片器选择单个或多个费用或部门，以获取不同费用在不同部门的情况、变化趋势或结构。

图6

利用数据透视表和数据透视图实现数据分析，方便简捷，易于掌握，对初学者更加容易上手。（由中国青年政治学院资助）