Excel数据透视图

Excel数据透视表是强大的数据整合处理引擎，可非常方便和灵活地对数据进行汇总、加工、处理。架构在数据透视表基础上的数据透视图，具有灵活的数据查询功能，是Excel中最为直接的交互式图表。可借助数据透视表，结合定义名称，使用常规图表来制作交互图表。

传统应用

在实际作业中，我们往往需要处理未经加工处理的杂乱数据。图14.3-1的表格是一个混合了多种产品、多状态、多属性的进货与销售数据表，共使用了761条记录，其次该数据表的数据日期分布很零散，需按年按季度来汇总统计。这样的数据若使用图表表达，就必须借助如图14.3-2所示的数据透视表功能。否则，使用一般方法来处理数据将非常费事，且很难快速制作出有价值的图表。

图14.3-1　数据透视图案例原始数据

原始数据整理后的数据透视表

图14.3-2　原始数据整理后的数据透视表

在使用原始数据来生成图14.3-2的数据透视表时，该透视表的布局如图14.3-3和图14.3-4所示。当生成透视表后，需选中透视表的“发生日期”字段，单击鼠标右键，在弹出菜单中：Excel 2003选“组及显示明细数据”> “组合”；Excel 2007/2010选“创建组”。在弹出的对话框（如图14.3-5所示）中，点选“季度”与“年”，Excel 2007/2010另需在“年”字段的设置“布局和打印”中勾选“以表格形式显示项目标签”。

Excel 2003数据透视表布局

图14.3-3　Excel 2003数据透视表布局

Excel 2007/2010数据透视表布局

图14.3-4　Excel 2007/2010数据透视表布局

字段分组设置对话框

图14.3-5　字段分组设置对话框

由于数据透视表在处理该案例的“数量”和“金额”两项求和项时，交互作业无法直接使用图表上的交互按钮来完成，必须借助“数据透视表字段列表”窗格来处理，因此这个案例中分别使用如图14.3-6所示的两个数据透视表来处理“数量”和“金额”两项求和项。

原始数据整理后的最终数据透视表

图14.3-6　原始数据整理后的最终数据透视表

注：两个数据表间要保留足够的间隔单元格，因为数据透视图的交互展示，是直接对数据透视表的操作，这种操作会导致单元格格式的变化，尤其是透视表间的间距太小时。

当完成数据透视表的布局后，制作数据透视图就变得非常简单。直接选中相应的数据透视表，然后制作簇状柱形图，依次可生成如图14.3-7所示的两个数据透视图，这两个图表的交互作业完全依赖于数据透视图的交互按钮。

最终数据透视图

图14.3-7　最终数据透视图

虽然此类数据透视图表制作起来非常简单，但可视化效果并不好。以下列举了一些数据透视图表的局限，供读者使用数据透视图表时参考：

1．按钮类元素所占的图表面积太大且无法移动位置，由于数据透视图的数据信息一般都比较大，因此展示效果给人较强的局促感。

2．数据透视图中图表元素的格式化设置，往往随交互按钮的触发操作而导致丢失，因为每次的数据交互，数据透视图都进行了再次完全重绘。

3．数据透视图对组合类图表的支持非常有限。有时在交互操作后，系列的图表类型会发生变化，尤其是在改变图表系列个数时。

数据的OLAP操作

数据透视表可以实现数据处理中经常被提及的OLAP的多维分析操作，包括钻取（Drill-down）、上卷（Roll-up）、切片（Slice）、切块（Dice）以及旋转（Pivot）。这是一组对普通人而言不知是何物的术语，为了更好地使大家理解这些概念，以下将以图14.3-1的数据表展开简单说明。

名词解释：

■　OLAP（On-line Analytical Processing，联机分析处理）：

基于大数据量、多指标基础上的各种数据整理与分析方式集合，简单理解就是：Excel中基于数据透视表的各类透视过程。对于复杂的大型数据集而言，Excel的处理能力不足应付时，可以使用微软专门基于SQL Server的工具集Microsoft SQL Server OLAP Services，该工具是为处理数据的OLAP应用而生。

❶数据立方体（Data Cube）：

其实就是需要分析的原始数据集合，此处图片只是示意，实际的数据维度远不止3维。此处可以理解为图14.3-1的数据表。

❷钻取（Drill-down）：

将汇总数据拆分成更细节的数据，比如图14.3-2的透视表在未进行分组时的日期状态。

❸旋转（Pivot）：

即互换维的位置，简单而言就是行列转换，Excel中的转置功能即属于此操作。如基于图14.3-2的数据表透视表将“发生日期”和“品种”两个字段进行相应的交换。

❹切块（Dice）：

选择特定区间数据或者某特定指标进行分析，如选择图14.3-1数据表中所有产品上半年的销售数量与销售金额。

❺切片（Slice）：

选取特定维度的数据进行分析，如只选择图14.3-1数据表中各季度产品A的销售数量数据。

❻上卷（Roll-up）：

钻取的逆操作，简单地理解就是Excel的分类汇总，如只将图14.3-1数据表中日期销售数量汇总为各年度数据。

这些概念也许显得晦涩和难懂，但其实在制作交互式图表的过程中，我们都有意或无意在使用这些操作方法，因为交互式图表依赖这样的数据操作。如果单纯使用函数公式来处理如此庞杂的数据，将变得不具可操作性，因此借助Excel的数据透视表功能就显得非常必要。随着Excel的推陈出新，Excel在不断加强数据透视表功能，在Excel 2010中，已经添加了切片器功能，该功能基于数据透视表和SQL应用。

与定义名称结合

图14.3-7　透视图表在操作上的不友好，导致我们必须要考虑一个非常现实的问题：如何借助常规图表结合数据透视表来制作交互式图表。有个非常令人困扰的问题是：如果图表引用的数据来源自Excel的数据透视表，该图表会被Excel强制变为数据透视图。

Excel 2003中，可以先使用图表向导来生成一个空白图表，然后使用数据拖曳方式添加系列，但该方法并不非常可靠，在不确定的状况下，该图表依然会被Excel自动设置为数据透视图。在Excel 2007和2010中则完全无望，拖曳功能也不再被支持。但并非没有办法来实现这样的操作，借助定义名称的方法，传统Excel图表和数据透视表可以被有机地结合在一起。图14.3-8所示的案例，就是图14.3-7的常规图表演绎，该图仅使用了一个图表来展示“数量”和“金额”两项求和项。

数据透视表和定义名称结合的案例