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

Excel数据透视图-Excel22

传统应用

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

Excel数据透视图-Excel22

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

Excel数据透视图-Excel22

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

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

Excel数据透视图-Excel22

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

Excel数据透视图-Excel22

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

Excel数据透视图-Excel22

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

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

Excel数据透视图-Excel22

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

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

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

Excel数据透视图-Excel22

图14.3-7 最终数据透视图

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

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

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

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

数据的OLAP操作

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

Excel数据透视图-Excel22名词解释


■ OLAP(On-line Analytical Processing,联机分析处理):

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

Excel数据透视图-Excel22

❶数据立方体(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的常规图表演绎,该图仅使用了一个图表来展示“数量”和“金额”两项求和项。

Excel数据透视图-Excel22

图14.3-8 数据透视表和定义名称结合的案例

这个图表的制作过程基于图14.3-7案例数据透视表,通过使用4个数据透视表来实现,这些透视表和图14.3-3的结构基本相同。制作方法说明如下:

1.参照图14.3-6数据透视表的生成过程,生成如图14.3-9所示的4个数据透视表。

Excel数据透视图-Excel22

图14.3-9 图14.3-8案例4个数据透视表

2.新增“类别”、“状态”两个辅助列,相关内容参照图14.3-8设置即可。再依次新增4个触发链接列“状态”、“类别”、“年度”、“分类”,除“分类”为5个单元格外,其余皆为1个单元格。

3.分别添加以下工作表控件:两个组合框、两个分组框、3个选项按钮、5个复选框。分组框并不参与触发作业,仅为视觉分组,排布和外观设定参照图14.3-8设置即可,数据源区域和单元格链接从上到下依次为:

  1. 组合框1中数据源区域为$C$5:$C$6,单元格链接为$F$6;
  2. 组合框2中数据源区域为$D$5:$D$6,单元格链接为$G$6;
  3. 3个选项按钮的单元格链接为$H$6;
  4. 复选框1的单元格链接为$I$6;
  5. 复选框2的单元格链接为$I$7;
  6. 复选框3的单元格链接为$I$8;
  7. 复选框4的单元格链接为$I$9;
  8. 复选框5的单元格链接为$I$10。

4.分别定义“AData”、“BData”、“CData”、“DData”、“EData”、“分类”、“刻度”7个定义名称,公式如下所示:

Excel数据透视图-Excel22

5.生成无数据的簇状柱形图,依次添加6个图表系列,这6个图表系列的SERIES公式依次为:

Excel数据透视图-Excel22

Excel数据透视图-Excel22

Excel数据透视图-Excel22

Excel数据透视图-Excel22

Excel数据透视图-Excel22

Excel数据透视图-Excel22

6.将系列6“刻度”的图表类型改为XY散点图,并将图表按照图14.3-8右侧图表样式美化即可。

图14.3-8案例图表是为了学习如何将数据透视表和定义名称结合,以及如何使用选项按钮和复选框工作表控件。该案例图表仅比直接使用数据透视图的可视化效果好,但并不是一个值得称道的图表。

Excel数据透视图-Excel22

小技巧


交互式图表动态设置数值坐标刻度,使其保持一致。

由于涉及“金额”和“数量”两个不同量纲,图表的数值坐标无法直接手工设定为统一刻度,图14.3-8的系列6“刻度”即是为保证刻度的一致性而设(图表中并未隐去,实际作业中可设置其为无标记点)。

:本章所有的案例,均对图表的数值坐标进行了统一设置。

文档14.3-2是图14.3-8案例使用列表框工作表控件的复选选项实例,文档中获取复选数值使用了xlm4.0宏表函数。该文档仅限在Excel 2003中使用,在Excel 2007/2010中已不可使用,因为该功能已经不被支持,若需使用应借助VBA来处理。