什么是Excel数据透视图_Excel数据透视图如何使用

办公教程导读

收集整理了【什么是Excel数据透视图_Excel数据透视图如何使用】办公软件教程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3663字，纯文字阅读大概需要6分钟。

办公教程内容图文

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另需在“年”字段的设置“布局和打印”中勾选“以表格形式显示项目标签”。

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

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

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

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

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

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

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

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

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

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

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

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

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

小技巧

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

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

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

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

办公教程总结

以上是为您收集整理的【什么是Excel数据透视图_Excel数据透视图如何使用】办公软件教程的全部内容，希望文章能够帮你了解办公软件教程什么是Excel数据透视图_Excel数据透视图如何使用。
如果觉得办公软件教程内容还不错，欢迎将网站推荐给好友。