Excel 数据透视表 – 快速指南
Excel 数据透视表 – 快速指南
Excel 数据透视表 – 概述
数据透视表是一种非常强大的工具，可用于对数据进行切片和切块。您可以使用可动态更改的紧凑表来跟踪和分析数十万个数据点，从而使您能够找到数据的不同视角。它是一个简单易用但功能强大的工具。
数据透视表的主要特点如下 –
-
创建数据透视表非常简单快捷
-
通过简单的字段拖动、排序和过滤以及对数据的不同计算，立即启用数据搅动。
-
当您深入了解数据时，为您的数据找到合适的表示形式。
-
能够即时创建报告。
-
在几秒钟内从同一个数据透视表生成多个报告。
-
提供交互式报告以与观众同步。
在本教程中，您将通过示例详细了解这些数据透视表功能。当您完成本教程时，您将对数据透视表功能有足够的了解，可以让您开始根据需求探索、分析和报告数据。
创建数据透视表
您可以从一系列数据或 Excel 表格创建数据透视表。如果您知道要查找的内容，您可以从一个空的数据透视表开始填写详细信息。您还可以使用 Excel 推荐的数据透视表，它可以让您了解最适合汇总数据的数据透视表布局。
您将在章节 – 从表或范围创建数据透视表中学习如何从数据区域或 Excel 表创建数据透视表。
Excel 为您提供了一种从多个表、不同数据源和外部数据源创建数据透视表的更强大的方法。它被命名为 PowerPivot，适用于其称为数据模型的数据库。您将在此教程库的其他教程中学习这些 Excel 强大工具。
在您尝试使用电动工具之前，您需要首先了解本教程中所述的普通数据透视表。
数据透视表布局 – 字段和区域
数据透视表布局仅取决于您为报告选择了哪些字段以及您如何在区域中排列它们。只需拖动字段即可完成选择和排列。当您拖动字段时，数据透视表布局会保持变化，并且它会在几秒钟内发生。
您将在章节 – 数据透视表字段和数据透视表区域中了解数据透视表字段和区域。
使用数据透视表探索数据
使用数据透视表的主要目标通常是探索数据以提取重要和必需的信息。您有多种选择来执行此操作，包括排序、过滤、嵌套、折叠和展开、分组和取消分组等。
您将在章节 – 使用数据透视表探索数据中概述这些选项。
总结价值
一旦您整理了不同探索技术所需的数据，您希望采取的下一步就是汇总数据。Excel 为您提供了多种计算类型，您可以根据适用性和要求应用这些计算类型。您还可以切换不同的计算类型并在几秒钟内查看结果。
您将在章节 – 按不同计算类型汇总值中了解如何在数据透视表上应用计算类型。
更新数据透视表
一旦您浏览了数据并对其进行了汇总，您就无需在源数据更新时重复该练习。您可以刷新数据透视表，使其反映源数据中的更改。
您将在“更新数据透视表”一章中了解刷新数据的各种方法。
数据透视表报告
在使用数据透视表探索和总结数据后，您将把它呈现为报告。数据透视表本质上是交互式的，其特点是即使不熟悉 Excel 的人也可以直观地使用它们。由于它们固有的动态特性，它们将使您能够快速改变报告的视角，以显示所需的详细程度或专注于受众表示感兴趣的特定项目。
此外，您可以根据情况构建数据透视表以进行独立演示或作为广泛报告的组成部分。您将在“数据透视表报告”一章中学习使用数据透视表进行报告的几种方法。
Excel 数据透视表 – 创建
您可以从一系列数据或 Excel 表中创建数据透视表。在这两种情况下，数据的第一行都应包含列的标题。
如果您确定要包含在数据透视表中的字段和您想要的布局，您可以从一个空的数据透视表开始并构建数据透视表。
如果您不确定哪种数据透视表布局最适合您的数据，您可以使用 Excel 的推荐数据透视表命令查看为您的数据定制的数据透视表并选择您喜欢的数据透视表。
从数据范围创建数据透视表
考虑以下数据范围，其中包含每个销售人员在每个地区以及 1 月、2 月和 3 月的销售数据 –
要从此数据范围创建数据透视表，请执行以下操作 –
-
确保第一行有标题。您需要标题，因为它们将是数据透视表中的字段名称。
-
将数据范围命名为 SalesData_Range。
-
单击数据范围 – SalesData_Range。
-
单击功能区上的插入选项卡。
单击表组中的数据透视表。在创建数据透视表对话框出现。
在“创建数据透视表”对话框中的“选择要分析的数据”下，您可以从当前工作簿中选择表或范围，也可以使用外部数据源。
当您从数据范围创建数据透视表时，请从对话框中选择以下内容 –
-
选择选择表或范围。
-
在表/范围框中，输入范围名称 – SalesData_Range。
-
在“选择要放置数据透视表的位置”下选择“新建工作表”，然后单击“确定”。
您可以通过将此数据范围添加到数据模型来选择分析多个表。您可以在 Excel PowerPivot 教程中了解如何分析多个表、使用数据模型以及如何使用外部数据源创建数据透视表。
一个新的工作表被插入到您的工作簿中。新工作表包含一个空的数据透视表。将工作表命名为范围数据透视表。
如您所见，数据透视表字段列表出现在工作表的右侧，其中包含数据区域中列的标题名称。此外，在功能区上，数据透视表工具 – 分析和设计出现。
向数据透视表添加字段
您将在本教程后面的章节中详细了解数据透视表字段和区域。现在，请观察向数据透视表添加字段的步骤。
假设您要汇总销售人员一月、二月和三月的订单金额。您可以通过以下几个简单的步骤来完成 –
-
单击 PivotTable Fields 列表中的 Salesperson 字段并将其拖到 ROWS 区域。
-
单击数据透视表字段列表中的字段 Month 并将其拖到 ROWS 区域。
-
单击 Order Amount 并将其拖到 ∑ VALUES 区域。
您的第一个数据透视表已准备就绪，如下所示
观察数据透视表中出现两列，一列包含您选择的行标签，即销售人员和月份，第二列包含订单金额总和。除了每个销售员的每月订单金额总和之外，您还将获得代表该人总销售额的小计。如果向下滚动工作表，您会发现最后一行是表示总销售额的总计。
在学习本教程的过程中，您将了解有关根据需要生成数据透视表的更多信息。
从表创建数据透视表
考虑以下包含与上一节相同的销售数据的 Excel 表格 –
Excel 表将固有地具有名称，并且列将具有标题，这是创建数据透视表的要求。假设表名是 SalesData_Table。
要从此 Excel 表创建数据透视表，请执行以下操作 –
-
单击表 – SalesData_Table。
-
单击功能区上的插入选项卡。
-
单击表组中的数据透视表。在创建数据透视表对话框出现。
-
单击选择表或范围。
-
在表/范围框中，键入表名称 – SalesData_Table。
-
在“选择要放置数据透视表的位置”下选择“新建工作表”。单击确定。
一个新的工作表被插入到您的工作簿中。新工作表包含一个空的数据透视表。将工作表命名为 Table-PivotTable。工作表 – Table-PivotTable 看起来类似于您在上一节中的数据范围案例中获得的工作表。
您可以将字段添加到数据透视表，如本章前面部分 – 将字段添加到数据透视表中所见。
使用推荐的数据透视表创建数据透视表
如果您不熟悉 Excel 数据透视表，或者您不知道哪些字段会生成有意义的报告，您可以使用 Excel 中的推荐数据透视表命令。推荐的数据透视表为您提供所有可能的报告以及您的数据以及相关联的布局。换句话说，显示的选项将是针对您的数据定制的数据透视表。
要使用推荐的数据透视表从 Excel 表 SalesData-Table 创建数据透视表，请执行以下操作 –
-
单击表 SalesData-Table。
-
单击插入选项卡。
-
单击表组中的推荐数据透视表。出现推荐的数据透视表对话框。
在“推荐的数据透视表”对话框中，将显示适合您的数据的可能的自定义数据透视表。
-
单击每个数据透视表选项以查看右侧的预览。
-
单击数据透视表 – 按销售人员和月份划分的订单金额总和，然后单击确定。
您将在右侧获得预览。
选定的数据透视表出现在工作簿中的新工作表上。
您可以看到数据透视表字段 – 销售员、地区、订单金额和月份被选中。其中，Region和Salesperson在ROWS区域，Month在COLUMNS区域，Sum of Order Amount在∑VALUES区域。
数据透视表汇总了区域、销售人员和月份的数据。显示每个地区、每个销售人员和每个月的小计。
Excel 数据透视表 – 字段
数据透视表字段是与数据透视表关联的任务窗格。数据透视表字段任务窗格由字段和区域组成。默认情况下，任务窗格显示在窗口右侧，字段显示在区域上方。
字段代表数据中的列 – 范围或 Excel 表格，并带有复选框。所选字段显示在报告中。区域代表报告的布局和报告中包含的计算。
在任务窗格的底部，您会找到一个选项 – 延迟布局更新，旁边有一个更新按钮。
-
默认情况下，此选项未被选中，您在字段选择或布局选项中所做的任何更改都会立即反映在数据透视表中。
-
如果选择此项，则在单击“更新”按钮之前不会更新选择中的更改。
在本章中，您将了解有关字段的详细信息。在下一章中，您将了解有关区域的详细信息。
数据透视表字段任务窗格
您可以在具有数据透视表的工作表上找到数据透视表字段任务窗格。要查看数据透视表字段任务窗格，请单击数据透视表。如果未显示数据透视表字段任务窗格，请检查功能区中的以下内容 –
- 单击功能区上 PIVOTTABLE TOOLS 下的 ANALYZE 选项卡。
- 检查是否在显示组中选择了字段列表（即突出显示）。
- 如果未选择字段列表，则单击它。
数据透视表字段任务窗格将显示在窗口右侧，标题为“数据透视表字段”。
移动数据透视表字段任务窗格
在数据透视表任务窗格的标题数据透视表字段的右侧，您将找到按钮
。这代表任务窗格选项。单击按钮。任务窗格选项 – 移动、大小和关闭出现在下拉列表中。
您可以将数据透视表任务窗格移动到窗口中您想要的任何位置，如下所示 –
-
单击下拉列表中的移动。该
按钮出现在任务窗格上。 -
单击该
图标并将窗格拖动到要放置它的位置。您可以将任务窗格放在数据透视表旁边，如下所示。
您可以将任务窗格放置在窗口的左侧，如下所示。
调整数据透视表字段任务窗格的大小
您可以调整数据透视表任务窗格的大小 – 即增加/减少任务窗格的长度和/或宽度如下 –
-
单击任务窗格选项 –
在标题右侧 – 数据透视表字段。 -
单击下拉列表中的大小。
-
使用符号⇔增加/减少任务窗格的宽度。
-
使用符号&vArr增加/减少任务窗格的宽度。
在 ∑ VALUES 区域中，要使总订单金额完全可见，您可以按如下所示调整任务窗格的大小。
数据透视表字段
数据透视表字段列表包含与您的工作簿和相应字段关联的所有表。它是通过选择数据透视表字段列表中的字段，您将创建数据透视表。
表格和带有复选框的相应字段反映了您的数据透视表数据。由于您可以随机选中/取消选中字段，因此您可以快速更改数据透视表，突出显示要报告或呈现的汇总数据。
如您所见，如果只有一个表，则表名将不会显示在数据透视表字段列表中。只会显示带有复选框的字段。
在字段列表上方，您将找到“选择要添加到报告的字段”操作。在右侧，您会找到
代表工具的按钮。
- 单击工具按钮。
在下拉列表中，您会发现以下内容 –
-
字段和区域的五种不同布局选项。
-
字段列表中字段的排序顺序的两个选项 –
-
从 A 到 Z 排序。
-
按数据源顺序排序。
-
正如您在上面的字段列表中所观察到的，排序顺序是默认的——即数据源顺序。这意味着，它是数据表中列出现的顺序。
通常，您可以保留默认顺序。但是，有时您可能会遇到表中的许多字段，并且可能不熟悉它们。在这种情况下，您可以通过单击工具下拉列表中的 – 从 A 到 Z 排序，按字母顺序对字段进行排序。然后，数据透视表字段列表如下所示 –
Excel 数据透视表 – 区域
数据透视表区域是数据透视表字段任务窗格的一部分。通过在区域中排列选定的字段，您可以获得不同的数据透视表布局。由于您可以简单地跨区域拖动字段，因此您可以快速切换不同的布局，以您想要的方式汇总数据。
您已经在本教程中有关数据透视表字段的前一章中了解了数据透视表字段任务窗格。在本章中，您将了解数据透视表区域。
有四个数据透视表区域可用 –
- 行。
- 列。
- 过滤器。
- ∑ VALUES（读作汇总值）。
消息 –在下方区域之间拖动字段出现在区域上方。
使用数据透视表区域，您可以选择 –
- 哪些字段显示为行（ROWS 区域）。
- 哪些字段显示为列（COLUMNS 区域）。
- 如何汇总您的数据（∑ VALUES 区域）。
- 任何字段的过滤器（过滤器区域）。
您只需将字段拖过这些区域并观察数据透视表布局如何变化。
行
如果您仅通过选中复选框来选择数据透视表字段列表中的字段，则所有非数字字段都将按照您选择的顺序自动添加到 ROWS 区域。
您可以选择将字段拖到 ROWS 区域。放在 ROWS 区域的字段在数据透视表中显示为行，行标签是所选字段的值。
例如，考虑 Sales 数据表。
- 将字段 Salesperson 拖到 ROWS 区域。
- 将字段 Month 拖动到 ROWS 区域。
您的数据透视表显示有一列包含行标签 – 销售员和月份，最后一行作为总计，如下所示。
列
您可以将字段拖到 COLUMNS 区域。
放在 COLUMNS 区域中的字段在数据透视表中显示为列，列标签是所选字段的值。
将字段 Region 拖到 COLUMNS 区域。您的数据透视表显示的第一列包含行标签 – 销售员和月份，接下来的四列包含列标签 – 区域和最后一列总计，如下所示。
-
将字段 Month 从 ROWS 拖到 COLUMNS。
-
将字段 Region 从 COLUMNS 拖到 ROWS。您的数据透视表布局更改如下。
您可以看到现在只有五列 – 第一列带有行标签，三列带有列标签，最后一列带有总计。
行数和列数取决于您在这些字段中的值数。
∑值
数据透视表的主要用途是汇总值。因此，通过在∑ VALUES区域中放置要用于汇总数据的字段，您就可以到达汇总表。
-
将字段 Order Amount拖到 ∑ VALUES。
-
将字段 Region 拖动到 ROWS 区域中字段 Salesperson 的上方。这一步是改变嵌套顺序。您将在本教程的“在数据透视表中嵌套”一章中学习嵌套。
如您所见，数据按区域、销售人员和月度进行汇总。您有每个地区的小计，按月计算。您还可以在“总计”列中的“总计行总计区域明智”中获得每月总计。
过滤器
过滤器区域用于在数据透视表中放置过滤器。假设您只想单独显示选定区域的结果。
将字段 Region 从 ROWS 区域拖到 FILTERS 区域。过滤器区域将放置在数据透视表上方。如果数据透视表上方没有空行，数据透视表将向下推，在数据透视表上方插入行以供过滤器使用。
如您所见，(ALL) 默认出现在过滤器中，数据透视表显示该区域所有值的数据。
- 单击过滤器右侧的箭头。
- 选中该框 – 选择多个项目。
下拉列表中的所有选项都将出现复选框。默认情况下，所有框都被选中。
- 选中复选框 – 北和南。
- 清除其他框。单击确定。
数据透视表被更改以反映过滤后的数据。
您可以观察到过滤器显示 (Multiple Items)。因此，当有人查看数据透视表时，过滤哪些值并不是很明显。
Excel 为您提供了另一种称为切片器的工具来更有效地处理过滤。您将在本教程后面的章节中详细了解在数据透视表中过滤数据。
Excel 数据透视表 – 探索数据
Excel 数据透视表允许您从 Excel 表或一系列数据中探索和提取重要数据。有几种方法可以做到这一点，您可以选择最适合您的数据的方法。此外，在探索数据时，您可以在更改选择以选择数据值时立即查看不同的组合。
您可以使用数据透视表执行以下操作 –
- 对数据进行排序。
- 过滤数据。
- 嵌套数据透视表字段。
- 展开和折叠字段。
- 分组和取消分组字段值。
排序和过滤数据
您可以按字段值的升序或降序对数据透视表中的数据进行排序。您还可以按从大到小或从小到大的小计排序。您还可以设置排序选项。您将在本教程的“对数据透视表中的数据进行排序”一章中详细了解这些内容。
您可以过滤数据透视表中的数据以关注某些特定数据。数据透视表中有几个过滤选项，您将在本教程的“在数据透视表中过滤数据”一章中了解这些选项。您可以使用切片器进行过滤，您将在本教程的使用切片器过滤一章中学习。
嵌套、扩展和折叠字段
如果与您的数据相关，您可以在数据透视表中嵌套字段以显示层次结构。您将在本教程的“在数据透视表中嵌套”一章中了解这一点。
当数据透视表中有嵌套字段时，您可以展开和折叠这些字段的值。您将在本教程的“使用数据透视表工具探索数据”一章中了解这些内容。
分组和取消分组字段值
您可以对数据透视表中字段的特定值进行分组和取消分组。您将在本教程的“使用数据透视表工具探索数据”一章中了解这一点。
Excel 数据透视表 – 排序数据
您可以对数据透视表中的数据进行排序，以便您轻松找到要分析的项目。您可以按从最低值到最高值或从最高值到最低值或您选择的任何其他自定义顺序对数据进行排序。
考虑以下数据透视表，其中您具有按地区、销售人员和月度汇总的销售数据。
按字段排序
您可以根据行或列中的字段对上述数据透视表中的数据进行排序 – 区域、销售人员和月份。
要使用字段 Salesperson 对数据透视表进行排序，请按以下步骤操作 –
-
单击
行标签中的箭头。 -
从下拉列表中的选择字段框中选择销售人员。
显示以下排序选项 –
- 从 A 到 Z 排序。
- 将 Z 排序为 A。
- 更多排序选项。
此外，默认情况下，销售人员字段按升序排序。单击从Z 到 A 排序。销售人员字段将按降序排序。
同样，您可以通过单击列标签中的箭头对列 – 月份中的字段进行排序。
按小计排序
假设您想根据总订单金额对数据透视表进行排序 – 每个区域的最高到最低。也就是说，您希望按小计对数据透视表进行排序。
您可以看到小计没有箭头。您仍然可以按小计对数据透视表进行排序，如下所示 –
-
右键单击总计列中任何销售人员的小计。
-
从下拉列表中选择排序。
-
出现另一个带有排序选项的下拉列表 – 从小到大排序、从大到小排序和更多排序选项。选择从大到小排序。
“总计”列中的小计按每个地区从最高值到最低值排序。
同样，如果要按区域小计对数据透视表进行排序，请执行以下操作 –
-
右键单击“总计”列中任何区域的小计。
-
单击下拉列表中的排序。
-
单击第二个下拉列表中的从大到小排序。数据透视表将按区域分类汇总。
如您所见，南方的订单量最高，而北方的订单量最低。
您还可以根据每月的总金额对数据透视表进行排序，如下所示 –
- 右键单击总计行中的任何小计。
- 从下拉列表中选择排序。
- 从第二个下拉列表中选择从大到小排序。
数据透视表将按月总额排序。
您可以观察到 2 月的订单量最高，而 3 月的订单量最低。
更多排序选项
假设您想在 1 月份按区域总量对数据透视表进行排序。
-
单击
行标签中的箭头。 -
从下拉列表中选择更多排序选项。出现排序（区域）对话框。
如您所见，在摘要下，当前的排序顺序以升序排序区域给出。在排序选项下选择升序（A 到 Z）。在下面的方框，区域显示出来。
- 单击包含区域的框。
- 单击订单金额总和。
单击更多选项按钮。出现更多排序选项（区域）对话框。
如您所见，在排序依据下，选择了总计。在“摘要”下，当前排序顺序是按“按订单金额总和”升序排列的“排序区域”。
-
单击选定列中的值：在排序方式下。
-
在下面的框中，键入 B5。
如您所见，在摘要下，当前排序顺序如下 –
-
使用此列中的值按订单金额总和升序对区域进行排序：一月。单击确定。
-
出现排序（区域）对话框。在排序选项下选择降序（Z 到 A）：。
在摘要下，当前排序顺序如下 –
使用此列中的值按订单金额总和降序对区域进行排序：一月。单击确定。数据透视表将按地区排序，使用 1 月份的值。
如您所见，在一月份，西部的订单量最高，而北部的订单量最低。
手动排序数据
在数据透视表中，数据按您选择的排序选项自动排序。这称为自动排序。
将光标放在行标签或列标签中的箭头上。
出现自动排序，显示数据透视表中每个字段的当前排序顺序。现在，假设您要按顺序对区域区域进行排序 – 东、西、北和南。您可以手动执行此操作，如下所示 –
-
单击
行标签中的箭头。 -
从下拉列表中的选择字段框中选择区域。
-
单击更多排序选项。出现排序（区域）对话框。
-
选择手动（您可以拖动项目以重新排列它们）。
-
单击确定。
在“摘要”下，当前排序顺序作为“区域”字段的拖动项目给出，以按任意顺序显示它们。
单击 East 并将其拖到顶部。当您向东拖动时，整个行移动中会出现一个水平绿色条。
重复拖动区域字段的其他项目，直到获得所需的排列。
您可以观察以下内容 –
-
嵌套字段的项目 – 销售员也随相应的区域字段项目一起移动。此外，其他列中的值也相应地移动。
-
如果您将光标放在
行标签或列标签中的箭头上，则会出现自动排序，仅显示字段 Salesperson 和 Month 的当前排序顺序。由于您手动对 Region 字段进行了排序，因此它不会显示在 AutoSort 中。
注意– 您不能使用此手动拖动数据透视表字段列表的 ∑ VALUES 区域中的字段项目。因此，您不能拖动此数据透视表中的订单金额总和值。
设置排序选项
在上一节中，您学习了如何将字段的排序选项设置为手动。您可以设置更多排序选项，如下所示 –
-
单击
行标签中的箭头。 -
在选择字段框中选择区域。
-
单击更多排序选项。出现排序（区域）对话框。
-
单击更多选项按钮。
出现更多排序选项（区域）对话框。您可以在此对话框中设置更多排序选项。
在自动排序下，您可以选中或取消选中复选框 – 每次更新报表时自动排序，以在数据透视表数据更新时允许或停止自动排序。
- 取消选中该框 –每次更新报告时自动排序。
现在，First key sort order 选项变得可用。您可以使用此选项来选择要使用的自定义订单。
- 单击第一个键排序顺序下的框。
如您所见，下拉列表中提供了星期几和月份自定义列表。您可以使用其中任何一种，也可以使用您自己的自定义列表，例如高、中、低或不按字母顺序排列的尺寸列表 S、M、L、XL。
您可以从功能区上的“文件”选项卡创建自定义列表。文件 → 选项。在 Excel 选项对话框中，单击高级并浏览到常规。您将在创建用于排序和填充序列的列表旁边找到编辑自定义列表按钮。
请注意，当您更新（刷新）数据透视表中的数据时，不会保留自定义列表排序顺序。
在排序依据下，您可以单击所选列中的总计或值以按这些值排序。当您将排序设置为手动时，此选项不可用。
对数据透视表进行排序时要考虑的要点
当您对数据透视表中的数据进行排序时，请记住以下几点 –
-
有前导空格的数据会影响排序结果。在对数据进行排序之前删除所有前导空格。
-
您不能对区分大小写的文本条目进行排序。
-
您不能按特定格式（例如单元格或字体颜色）对数据进行排序。
-
您不能按条件格式指示符（例如图标集）对数据进行排序。
Excel 数据透视表 – 筛选数据
您可能需要对数据透视表数据的子集进行深入分析。这可能是因为您拥有大量数据并且需要将注意力集中在数据的较小部分，或者无论数据大小如何，都需要将注意力集中在某些特定数据上。您可以根据一个或多个字段的值的子集过滤数据透视表中的数据。有以下几种方法可以做到这一点 –
- 使用切片器过滤。
- 使用报告过滤器过滤。
- 手动过滤数据。
- 使用标签过滤器过滤。
- 使用值过滤器进行过滤。
- 使用日期过滤器进行过滤。
- 使用 Top 10 Filter 进行过滤。
- 使用时间轴过滤。
您将在下一章中学习使用切片器过滤数据。您将在本章中了解上述其他方法的过滤。
考虑以下数据透视表，其中您有汇总的销售数据区域、销售人员和月份。
报告过滤器
您可以为其中一个字段分配过滤器，以便您可以根据该字段的值动态更改数据透视表。
将区域从行拖到数据透视表区域中的过滤器。
带有 Region 标签的过滤器出现在数据透视表上方（如果数据透视表上方没有空行，数据透视表将被向下推以为过滤器腾出空间。
你会观察到
-
销售员值显示在行中。
-
月份值出现在列中。
-
区域过滤器出现在顶部，默认选择为 ALL。
-
汇总值是订单金额总和。
-
销售人员的订单金额总和出现在总计列中。
-
订单金额的总和出现在行总计中。
-
-
单击“过滤区域”右侧框中的箭头。
将出现一个包含字段 Region 值的下拉列表。选中“选择多个项目”框。
默认情况下，所有框都被选中。取消选中该框 ( All )。所有复选框都将取消选中。
然后选中框 – 南和西并单击确定。
将仅汇总与南部和西部地区有关的数据。
在过滤区域旁边的单元格中显示 – (Multiple Items)，表示您选择了多个项目。但是，从显示的报告中不知道有多少项目和/或哪些项目。在这种情况下，使用切片器是更好的过滤选择。
手动过滤
您还可以通过手动选择字段的值来过滤数据透视表。您可以通过单击行标签或列标签单元格中的箭头来执行此操作。
假设您只想分析 2 月份的数据。您需要按字段 Month 过滤值。如您所见，Month 是 Column Labels 的一部分。
单击列标签单元格中的箭头。
如您所见，下拉列表中有一个搜索框，在该框下方，您有所选字段的值列表，即月份。选中所有值的框，表明该字段的所有值都已选中。
-
取消选中值列表顶部的（全选）框。
-
选中要在数据透视表中显示的值的框，在本例中为二月，然后单击确定。
数据透视表仅显示与所选月份字段值 – 二月相关的那些值。您可以观察到过滤箭头变为图标
以指示应用了过滤器。将光标放在图标上。
您可以观察到显示的内容表明手动过滤器应用于字段 – 月。
如果要更改过滤器选择值，请执行以下操作 –
-
单击该
图标。 -
选中/取消选中值的框。
如果该字段的所有值在列表中都不可见，请拖动下拉列表右下角的手柄以将其放大。或者，如果您知道该值，请在“搜索”框中键入它。
假设您想对上面过滤的数据透视表应用另一个过滤器。例如，您想要显示 Walters, Chris 在二月份的数据。您需要通过为字段 Salesperson 添加另一个过滤器来优化过滤。正如您所看到的，Salesperson 是 Row Labels 的一部分。
-
单击
行标签单元格中的箭头。
显示该字段的值列表 – Region。这是因为 Region 在嵌套顺序中位于 Salesperson 的外部级别。您还有一个附加选项 – 选择字段。单击选择字段框。
-
从下拉列表中单击销售人员。将显示字段值的列表 – 销售员。
-
取消选中（全选）并选中 Walters, Chris。
-
单击确定。
数据透视表仅显示与选定的月份字段值（二月）和销售人员字段值（Walters, Chris）相关的值。
行标签的过滤箭头也会变为图标以指示应用了过滤器。将光标放在行标签或列标签上的图标上。
将显示一个文本框，指示手动过滤器应用于字段 – 月份和销售人员。
因此，您可以根据任意数量的字段和任意数量的值手动过滤数据透视表。
按文本过滤
如果您有包含文本的字段，您可以按文本过滤数据透视表，前提是相应的字段标签是基于文本的。例如，考虑以下员工数据。
数据包含员工的详细信息 – EmployeeID、Title、BirthDate、MaritalStatus、Gender 和 HireDate。此外，数据还具有员工的经理级别（级别 0 – 4）。
假设您必须对按职称报告给给定员工的员工数量进行一些分析。您可以创建如下所示的数据透视表。
您可能想知道有多少头衔中带有“经理”的员工向他们报告。由于标签标题是基于文本的，您可以在标题字段上应用标签过滤器，如下所示 –
-
单击
行标签单元格中的箭头。 -
从下拉列表中选择“选择字段”框中的“标题”。
-
单击标签过滤器。
-
单击第二个下拉列表中的包含。
出现标签过滤器（标题）对话框。在包含旁边的框中键入管理器。单击确定。
数据透视表将被过滤到包含“经理”的标题值。
-
单击该
图标。
您可以看到
显示以下内容 –
- 标签过滤器应用于字段 – 标题，和
- 应用的标签过滤器是什么。
按值过滤
您可能想知道向他们报告的员工超过 25 名的员工的职称。为此，您可以在标题字段上应用值过滤器，如下所示 –
-
单击
行标签单元格中的箭头。 -
选择标题从下拉列表中选择字段框。
-
单击值过滤器。
-
从第二个下拉列表中选择大于或等于。
出现值过滤器（标题）对话框。在右侧框中键入 25。
数据透视表将被过滤以显示向他们报告的员工超过 25 名的员工职位。
按日期过滤
您可能想要显示在 2015-15 财年雇用的所有员工的数据。您可以使用数据过滤器，如下所示 –
-
在数据透视表中包括 HireDate 字段。现在，您不需要经理数据，因此从数据透视表中删除 ManagerLevel 字段。
现在您在数据透视表中有一个日期字段，您可以使用日期过滤器。
-
单击
行标签单元格中的箭头。 -
在下拉列表中的选择字段框中选择 HireDate。
-
单击日期过滤器。
-
从第二个下拉列表中选择介于之间。
出现日期过滤器 (HireDate) 对话框。在两个日期框中键入 4/1/2014 和 3/31/2015。单击确定。
数据透视表将被过滤以仅显示 HireDate 介于 1 之间的数据st 2014 年 4 月和 31st 2015 年 3 月。
您可以按如下方式将日期分组为季度 –
-
右键单击任何日期。在分组对话框。
-
在开始于框中键入 4/1/2014。选中该框。
-
在结束于框中键入 3/31/2015。选中该框。
-
单击By下框中的 Quarters 。
日期将在数据透视表中按季度分组。您可以通过将字段 HireDate 从 ROWS 区域拖到 COLUMNS 区域来使表格看起来紧凑。
您将能够知道在本财年（每个季度）雇佣了多少员工。
使用前 10 个过滤器过滤
您可以使用前 10 个过滤器来显示数据透视表中字段的前几个或后几个值。
-
单击
行标签单元格中的箭头。 -
单击值过滤器。
-
单击第二个下拉列表中的前 10 名。
出现前 10 个过滤器（标题）对话框。
-
在第一个框中，单击顶部（您也可以选择底部）。
-
在第二个框中，输入一个数字，例如 7。
-
在第三个框中，您可以使用三个选项进行过滤。
-
单击项目以按项目数量过滤。
-
单击百分比以按百分比过滤。
-
单击总和以按总和过滤。
-
-
当您有 EmployeeID 计数时，单击 Items。
-
在第四个框中，单击 EmployeeID 字段计数。
-
单击确定。
按 EmployeeID 计数的前七个值将显示在数据透视表中。
如您所见，本财年招聘人数最多的是生产技术员，其中主要是在第一季度。
使用时间轴过滤
如果您的数据透视表有一个日期字段，您可以使用时间轴过滤数据透视表。
根据您之前使用的员工数据创建数据透视表，并将数据添加到“创建数据透视表”对话框中的数据模型。
-
将字段标题拖到 ROWS 区域。
-
将字段 EmployeeID 拖到 ∑ VALUES 区域并选择 Count 进行计算。
-
单击数据透视表。
-
单击插入选项卡。
-
单击过滤器组中的时间轴。出现插入时间线对话框。
- 选中 HireDate 框。
- 单击确定。时间轴出现在工作表中。
- 时间轴工具出现在功能区上。
正如您所观察到的，所有期间 – 以月为单位显示在时间轴上。
-
单击旁边的箭头 – MONTHS。
-
从下拉列表中选择 QUARTERS。时间线显示更改为所有期间 – 以季度为单位。
-
单击 2014 年第一季度。
-
按住 Shift 键并拖动到 2014 Q4。时间线周期选择为 2014 年第一季度至第四季度。
-
数据透视表被过滤到这个时间线周期。
清除过滤器
您可能需要不时清除设置的过滤器，才能在数据的不同组合和投影之间切换。您可以通过以下几种方式做到这一点 –
清除数据透视表中的所有过滤器
您可以一次性清除数据透视表中设置的所有过滤器，如下所示 –
- 单击功能区上的主页选项卡。
- 单击编辑组中的排序和筛选。
- 从下拉列表中选择清除。
清除标签、日期或值过滤器
要清除标签、日期或值过滤器，请执行以下操作 –
-
单击行标签或列标签中的图标。
-
在下拉列表中的“选择字段”框中，单击要从中清除过滤器的<字段名称>。
-
单击出现在下拉列表中的从 <Filed Name> 清除过滤器。
-
单击确定。特定过滤器将被清除。
使用切片器过滤数据
使用一个或多个切片器是过滤数据的一种快速有效的方法。可以为要过滤的每个字段插入切片器。切片器将具有表示它所代表的字段值的按钮。您可以单击切片器的按钮来选择/取消选择字段中的值。
切片器在数据透视表中保持可见，因此您将始终知道哪些字段用于过滤以及这些字段中的哪些值在过滤后的数据透视表中显示或隐藏。
要了解切片器的用法，请考虑区域、月份和销售人员的销售数据示例。假设您有以下包含此数据的数据透视表。
插入切片器
假设您想根据字段（地区和月份）过滤此数据透视表。
-
单击功能区上 PIVOTTABLE TOOLS 下的 ANALYZE。
-
单击过滤器组中的插入切片器。出现插入切片器对话框。它包含数据表中的所有字段。
-
选中区域和月份框。
-
单击确定。
默认情况下，每个选定字段的切片器都会显示所有选定的值。切片器工具出现在功能区上，用于处理切片器设置、外观和感觉。
用切片器过滤
如您所见，每个切片器都具有它所代表的字段的所有值，并且这些值显示为按钮。默认情况下，一个字段的所有值都被选中，因此所有按钮都被突出显示。
假设您只想显示南部和西部地区以及二月和三月的数据透视表。
-
单击区域切片器中的南。只有南部将在切片器 – 区域中突出显示。
-
按住 Ctrl 键并单击区域切片器中的 West。
-
在月份的切片器中单击二月。
-
按住 Ctrl 键并在切片器中单击 March for Month。
切片器中的选定项目将突出显示。将显示带有所选项目汇总值的数据透视表。
要从过滤器中添加/删除字段的值，请按住 Ctrl 键并单击字段切片器中的这些按钮。
清除切片器中的过滤器
要清除切片器中的过滤器，请单击切片器
右上角的 。
移除切片器
假设您要删除 Region 字段的切片器。
- 右键单击切片器 – 区域。
- 单击下拉列表中的删除“区域”。
切片工具
插入切片器后，切片器工具会出现在带有选项的功能区选项卡上。要查看切片器工具，请单击切片器。
如您所见，在切片器工具 – 选项选项卡下，您有几个选项可以更改切片器的外观，包括 –
- 切片标题
- 切片器设置
- 报告连接
- 选择窗格
切片标题
您可以在切片器组中找到切片器标题框。切片器标题是显示在切片器上的标题。默认情况下，切片器标题是它所代表的字段的名称。
- 单击区域切片器。
- 单击功能区上的选项选项卡。
功能区上的切片器组，在切片器标题框中，区域显示为切片器的标题。它是插入切片器的字段的名称。您可以按如下方式更改切片器标题 –
-
单击功能区切片器组中的切片器标题框。
-
删除区域。该框已清除。
-
在框中键入位置，然后按 Enter。切片器标题更改为位置，并在切片器中反映为标题。
注意– 您只更改了切片器标题，即标题。切片器表示的字段的名称 – 区域保持原样。
切片器设置
您可以使用切片器设置更改切片器的名称、更改切片器标题、选择是否显示切片器标题以及设置项目的排序和过滤选项 –
-
单击切片器 – 位置。
-
单击功能区上的选项选项卡。您可以在功能区的切片器组中找到切片器设置。当您右键单击切片器时，您还可以在下拉列表中找到切片器设置。
-
单击切片器设置。出现切片器设置对话框。
正如您所观察到的，切片器的以下内容是固定的 –
- 来源名称。
- 要在公式中使用的名称。
您可以为切片器更改以下内容 –
- 名称。
- 标题 – 标题。
- 显示标题。
- 切片器上显示的项目的排序和过滤选项。
报告连接
您可以将不同的数据透视表连接到切片器，前提是以下之一有效 –
-
数据透视表是使用相同的数据创建的。
-
一个数据透视表已被复制并粘贴为另一个数据透视表。
-
多个数据透视表是在单独的工作表上创建的，并带有显示报表过滤器页面。
考虑以下从相同数据创建的数据透视表 –
- 将顶部的数据透视表命名为 PivotTable-Top，将底部的数据透视表命名为 PivotTable-Bottom。
- 单击顶部的数据透视表。
- 为区域区域插入切片器。
- 在切片器上选择东和北。
观察筛选仅应用于顶部数据透视表而不应用于底部数据透视表。您可以通过将其连接到底部数据透视表来为两个数据透视表使用相同的切片器，如下所示 –
- 单击切片器 – 区域。切片器工具出现在功能区上。
- 单击功能区上的选项选项卡。
您将在功能区的切片器组中找到报告连接。当您右键单击切片器时，您还可以在下拉列表中找到报告连接。
单击切片器组中的报告连接。
该报告连接对话框出现对话框。PivotTable-Top 框已选中，其他框未选中。还选中数据透视表底部框，然后单击确定。
底部的数据透视表将被过滤到选定的项目 – 东和北。
这成为可能，因为两个数据透视表现在都连接到切片器。如果您对切片器中的选择进行更改，则两个数据透视表中将出现相同的筛选。
选择窗格
您可以使用选择窗格在工作表上关闭和打开切片器的显示。
-
单击切片器 – 位置。
-
单击功能区上的选项选项卡。
-
单击功能区上排列组中的选择窗格。选择窗格出现在窗口的右侧。
如您所见，所有切片器的名称都列在“选择”窗格中。在名称的右侧，您可以找到可见性符号 –
表示切片器在工作表上可见。
单击月的符号。符号变为 符号
，表示切片器已隐藏（不可见）。
如您所见，切片器 – 月份未显示在工作表上。但是，请记住，您并未删除 Month 的切片器，而只是将其隐藏。
-
单击月的
符号。 -
符号
变为符号，表示切片器现在可见。
当您打开/关闭切片器的可见性时，该切片器中用于过滤的项目选择保持不变。您还可以通过向上/向下拖动来更改“选择”窗格中切片器的顺序。
Excel 数据透视表 – 嵌套
如果任何数据透视表区域中有多个字段，则数据透视表布局取决于您在该区域中放置字段的顺序。这称为嵌套顺序。
如果您知道数据的结构，则可以按所需顺序放置字段。如果您不确定数据的结构，您可以更改字段的顺序，从而立即更改数据透视表的布局。
在本章中，您将了解字段的嵌套顺序以及如何更改嵌套顺序。
字段的嵌套顺序
考虑销售数据示例，您已按以下顺序放置字段 –
如您所见，在行区域中有两个字段——按顺序是销售人员和地区。字段的这种顺序称为嵌套顺序，即首先是销售人员，然后是区域。
在数据透视表中，行中的值将根据此顺序显示，如下所示。
如您所见，嵌套顺序中第二个字段的值嵌入在第一个字段的每个值下。
在您的数据中，每个销售员仅与一个区域相关联，而大多数区域与多个销售员相关联。因此，如果您颠倒嵌套顺序，您的数据透视表可能看起来更有意义。
更改嵌套顺序
要更改区域中字段的嵌套顺序，只需单击该字段并将其拖动到所需位置即可。
单击 ROWS 区域中的 Salesperson 字段，并将其拖到 Region 字段下方。因此，您已将嵌套顺序更改为 – 区域优先，然后是销售人员，如下所示 –
结果数据透视表将如下所示 –
您可以清楚地观察到，与具有嵌套顺序（销售员然后区域）的布局相比，具有嵌套顺序 – 区域然后是销售员的布局生成了更好和紧凑的报告。
如果销售员代表多个区域，并且您需要按销售员汇总销售情况，那么之前的布局将是更好的选择。
Excel 数据透视表 – 工具
在包含数据透视表的工作表中，功能区将包含具有分析和设计选项卡的数据透视表工具。ANALYZE 选项卡有几个命令可以让您浏览数据透视表中的数据。DESIGN 选项卡命令可用于构建具有各种报告选项和样式选项的数据透视表。
您将在本章中学习 ANALYZE 命令。您将在章节 – 带有数据透视表的美学报告中学习设计命令。
分析命令
ANALYZE 选项卡功能区上的命令包括以下内容 –
- 展开和折叠字段。
- 分组和取消分组字段值。
- 活动字段设置。
- 数据透视表选项。
展开和折叠字段
如果数据透视表中有嵌套字段，则可以展开和折叠单个项目，也可以展开和折叠活动字段的所有项目。
考虑以下数据透视表，其中您在 Region 字段下嵌套了 Salesperson 字段。
单击
East 左侧的符号。字段区域东部的项目将折叠。
如您所见，其他项目 – 区域区域的北部、南部和西部并未折叠。如果您想折叠其中任何一个，请重复您为 East 所做的步骤。
-
单击
East 左侧的符号。字段 Region 的 East 项将扩展。
如果您想一次折叠一个字段的所有项目，请执行以下操作 –
- 单击该字段的任何项目 – 区域。
- 单击功能区上的分析选项卡。
- 单击活动字段组中的折叠字段。
字段 Region 的所有项目将被折叠。
如果您想一次扩展一个字段的所有项目，请执行以下操作 –
- 单击该字段的任何项目 – 区域。
- 单击功能区上的分析选项卡。
- 单击活动字段组中的展开字段。
字段 Region 的所有项目都将被展开。
分组和取消分组字段值
您可以对字段值进行分组和取消分组以定义自己的聚类。例如，您可能想知道组合东部和北部地区的数据。
-
选择数据透视表中 Region 字段的 East 和 North 项目，以及嵌套的 Salesperson 字段项目。
-
单击功能区上的分析选项卡。
-
单击组中的组选择 – 组。
项目 – 东和北将在名称 Group1 下分组。此外，创建了一个新的南部，在其下嵌套了南部，并在其下嵌套了一个新的西部。
您还可以观察到，在 ROWS 区域中显示的数据透视表字段列表中添加了一个新字段 – Region2。
-
选择数据透视表中 Region2 字段的 South 和 West 项目，以及嵌套的 Region 和 Salesperson 字段项目。
-
单击功能区上的分析选项卡。
-
单击组中的组选择 – 组。
字段区域南部和西部的项目将在名称 Group2 下分组。
要取消分组，请执行以下操作 –
- 单击组名称。
- 单击分析选项卡。
- 单击组中的取消组合 – 组。
按日期字段分组
考虑以下数据透视表，其中您有按 EmployeeID 计数汇总的员工数据，明智的雇佣和明智的职称。
假设您想通过 HireDate 字段将此数据分组为年和季度的日期字段。
- 单击数据透视表中的日期项。
- 单击功能区上的分析选项卡。
- 单击组中的组字段 – 组。
出现分组对话框。
-
设置日期 – 开始于和结束于。
-
在 By 下的框中选择 Quarters 和 Years。要选择/取消选择多个项目，请按住 Ctrl 键。
-
单击确定。
HireDate 字段值将分组为 Quarters，嵌套在 Years 中。
如果你想取消这个分组，你可以按照前面的方法来做，通过在组中点击取消分组 – 功能区上的分组。
活动值字段设置
您可以通过单击该字段的值来设置字段选项。考虑我们在本章前面使用的销售数据示例。
假设您要为 Region 字段设置选项。
-
点击东。在功能区上，在活动字段组中，在活动字段框中，将显示区域。
-
单击字段设置。出现字段设置对话框。
您可以为该字段设置您的首选项 – 地区。
数据透视表选项
您可以根据自己的喜好设置数据透视表选项。
- 单击数据透视表。
- 单击分析选项卡。
- 单击数据透视表组中的选项。
在数据透视表选项对话框。您可以在对话框中设置您的首选项。
Excel 数据透视表 – 汇总值
您可以通过在数据透视表字段任务窗格的 ∑ VALUES 区域中放置一个字段来汇总数据透视表。默认情况下，Excel 将汇总作为 ∑ VALUES 区域中字段值的总和。但是，您还有其他计算类型，例如 Count、Average、Max、Min 等。
在本章中，您将学习如何根据您希望如何汇总数据透视表中的数据来设置计算类型。
和
考虑以下数据透视表，其中您有区域、销售人员和月份的汇总销售数据。
可以看到，当您将字段Order Amount 拖动到∑ VALUES 区域时，它显示为Sum of Order Amount，表示计算为Sum。在数据透视表的左上角，显示订单金额总和。此外，总计列和总计行分别在行和列中按字段显示小计。
值字段设置
使用值字段设置，您可以在数据透视表中设置计算类型。您还可以决定如何显示您的值。
- 在 ∑ VALUES 区域单击订单金额总和。
- 从下拉列表中选择值字段设置。
值字段设置对话框出现。
源名称是字段，自定义名称是字段的总和。计算类型为总和。单击将值显示为选项卡。
在将值显示为框中，不显示计算。单击将值显示为框。您可以找到多种显示总值的方法。
占总数的百分比
您可以将数据透视表中的值显示为总计的百分比。
- 在自定义名称框中，键入总计的百分比。
- 单击将值显示为框。
- 单击下拉列表中的总计百分比。单击确定。
数据透视表将值汇总为总计的百分比。
如您所见，数据透视表左上角和数据透视表字段窗格中的 ∑ VALUES 区域中的订单金额总和更改为新的自定义名称 – 总计的百分比。
-
单击“总计”列的标题。
-
在公式栏中键入总计的百分比。列和行标题都将更改为总计的百分比。
列总数的百分比
假设您要将这些值汇总为每个月总数的百分比。
-
在 ∑ VALUES 区域单击订单金额总和。
-
从下拉列表中选择值字段设置。值字段设置对话框出现。
-
在自定义名称框中，键入 % of Month Total。
-
单击将值显示为框。
-
从下拉列表中选择 % of Column Total。
-
单击确定。
数据透视表将值汇总为列总计的百分比。在“月份”列中，您会发现值占特定月份总数的百分比。
-
单击“总计”列的标题。
-
在公式栏中键入 % of Column Total。Column 和 Row 标题都将更改为 % of Column Total。
占行总数的百分比
您可以通过在“值字段设置”对话框的“将值显示为”框中选择“行总计百分比”，将值汇总为区域总计百分比和销售人员总计百分比。
数数
假设您要按区域明智、销售人员明智和月份明智的客户数量汇总值。
-
取消选择订单金额。
-
将帐户拖到 ∑ VALUES 区域。账户总和将显示在 ∑ VALUES 区域。
-
单击帐户总和。
-
从下拉列表中选择值字段设置。值字段设置对话框出现。
-
在按框汇总值字段中，选择计数。自定义名称更改为帐户计数。
-
单击确定。
帐户计数将显示如下 –
平均数
假设您想按订单金额区域、销售人员和月份的平均值来汇总数据透视表。
-
取消选择帐户。
-
将订单金额拖到 ∑ VALUES 区域。订单金额总和将显示在 ∑ VALUES 区域。
-
单击订单金额总和。
-
单击下拉列表中的值字段设置。值字段设置对话框出现。
-
在按框汇总值字段中，单击平均值。自定义名称更改为平均订单金额。
-
单击确定。
平均值将显示如下 –
您必须设置数据透视表中值的数字格式以使其更易于展示。
-
在 ∑ VALUES 区域单击平均订单金额。
-
单击下拉列表中的值字段设置。值字段设置对话框出现。
-
单击数字格式按钮。
出现设置单元格格式对话框。
- 单击类别下的数字。
- 在小数位数框中键入 2，然后单击确定。
数据透视表值将被格式化为带有两位小数的数字。
-
单击“总计”列的标题。
-
在公式栏中输入平均订单金额。列和行标题都将更改为平均订单金额。
最大限度
假设您想按地区、销售人员和月度的订单金额最大值来汇总数据透视表。
-
单击订单金额总和。
-
从下拉列表中选择值字段设置。值字段设置对话框出现。
-
在按框汇总值字段中，单击最大值。自定义名称更改为最大订单金额。
数据透视表将显示区域明智、销售人员明智和月份明智的最大值。
-
单击“总计”列的标题。
-
在公式栏中键入最大订单金额。Column 和 Row 标题都将更改为 Max Order Amount。
最小
假设您想通过订单金额区域、销售人员和月份的最小值来汇总数据透视表。
-
单击订单金额总和。
-
单击下拉列表中的值字段设置。值字段设置对话框出现。
-
在按框汇总值字段中，单击最小值。自定义名称更改为 Min of Order Amount。
数据透视表将显示区域明智、销售人员明智和月份明智的最小值。
-
单击“总计”列的标题。
-
在公式栏中键入 Min Order Amount。Column 和 Row 标题都将更改为 Min Order Amount。
Excel 数据透视表 – 更新数据
您已经学习了如何使用数据透视表汇总数据。数据透视表所基于的数据可能会定期更新或在事件发生时更新。此外，您可能还需要更改不同报告的数据透视表布局。
在本章中，您将学习更新布局和/或刷新数据透视表中数据的不同方法。
更新数据透视表布局
您可以决定是在更改布局时更新数据透视表，还是由单独的触发器更新数据透视表。
正如您之前了解到的，在数据透视表字段任务窗格的底部，您将找到延迟布局更新复选框。默认情况下，它是未选中的，这意味着只要您在数据透视表区域中进行更改，数据透视表布局就会更新。
检查选项 –延迟布局更新。
旁边的 UPDATE 按钮将被启用。如果对数据透视表区域进行了任何更改，则只有在单击 UPDATE 按钮后才会反映这些更改。
刷新数据透视表数据
当数据透视表的数据在其源中发生变化时，同样可以通过刷新数据透视表反映在数据透视表中。
- 单击数据透视表。
- 单击功能区上的分析选项卡。
- 单击数据组中的刷新。
有不同的选项可以刷新下拉列表中的数据 –
-
刷新– 从连接到活动单元格的源获取最新数据。
-
全部刷新– 通过刷新工作簿中的所有源来获取最新数据。
-
连接属性– 设置工作簿连接的刷新属性。
更改数据透视表的源数据
您可以更改数据透视表的源数据范围。例如，您可以扩展源数据以包含更多行数据。
但是，如果源数据发生了重大变化，例如列的数量更多或更少，请考虑创建一个新的数据透视表。
-
单击数据透视表。可旋转工具出现在功能区上。
-
单击分析选项卡。
-
单击数据组中的更改数据源。
从下拉列表中选择更改数据源。
出现更改数据透视表数据源对话框，当前数据源将突出显示。
选择要包含在“选择表或范围”下的“表/范围”框中的表或范围。单击确定。
数据透视表的数据源将更改为选定的表/数据范围。
更改为外部数据源
如果要更改外部数据透视表的数据源，最好创建一个新的数据透视表。但是，如果您的外部数据源的位置发生更改，例如，您的 SQL Server 数据库名称相同，但已移动到不同的服务器，或者您的 Access 数据库已移动到另一个网络共享，您可以更改您当前的数据连接反映相同。
-
单击数据透视表。
-
单击功能区上的分析选项卡。
-
单击数据组中的更改数据源。出现“更改数据透视表数据源”对话框。
-
单击选择连接按钮。
在现有连接对话框出现对话框。
-
在显示框中选择所有连接。将显示工作簿中的所有连接。
-
单击浏览更多按钮。
出现“选择数据源”窗口。
- 单击“新建源”按钮。
- 完成数据连接向导步骤。
如果您的数据源在另一个 Excel 工作簿中，请执行以下操作 –
- 单击文件名框。
- 选择工作簿文件名。
删除数据透视表
您可以按如下方式删除数据透视表 –
- 单击数据透视表。
- 单击功能区上的分析选项卡。
- 单击操作组中的选择。
从下拉列表中选择整个数据透视表。将选择整个数据透视表。
按删除键。数据透视表将被删除。
如果数据透视表位于单独的工作表上，您还可以通过删除整个工作表来删除数据透视表。
右键单击工作表选项卡，然后从下拉列表中选择删除。
整个工作表连同数据透视表被删除。
Excel 数据透视表 – 报告
数据透视表的主要用途是报告。创建数据透视表并通过排列和重新排列其行和列中的字段来探索数据后，您就可以将数据呈现给广泛的受众。使用过滤器、不同的摘要，专注于特定数据，您将能够基于单个数据透视表生成多个所需的报告。
由于数据透视表是交互式的，您可以在呈现时快速进行必要的更改以突出显示特定结果，例如数据趋势、数据摘要等。您还可以向收件人提供可视化提示，例如报表过滤器、切片器、时间线、数据透视图等，以便他们可以可视化所需的详细信息。
在本章中，您将学习通过视觉提示使数据透视表具有吸引力的不同方法，以便快速探索数据。
层次结构
在本教程的“在数据透视表中嵌套”一章中，您已经学习了如何嵌套字段以形成层次结构。您还学习了如何在“使用数据透视表工具”一章中对数据透视表中的数据进行分组/取消分组。我们将通过几个示例向您展示如何生成具有层次结构的交互式数据透视表。
如果数据中的字段具有内置结构，例如年-季度-月，嵌套字段以形成层次结构将使您能够快速展开/折叠字段以查看所需级别的汇总值。
例如，假设您拥有 2015-16 财年东部、北部、南部和西部地区的销售数据，如下所示。
创建一个数据透视表，如下所示。
如您所见，这是一种使用嵌套字段作为层次结构报告数据的综合方法。如果您只想在 Quarters 级别显示结果，您可以快速折叠 Quarter 字段。
假设您的数据中有一个 Date 字段，如下所示。
在这种情况下，您可以按日期字段对数据进行分组，如下所示 –
创建数据透视表。
如您所见，此数据透视表不便于突出显示重要数据。
-
按日期字段对数据透视表进行分组。（您已经在本教程的“使用数据透视表工具探索数据”一章中学习了分组）。
-
将销售人员字段放在过滤器区域中。
-
将 Column 标签过滤为 East Region。
报告过滤器
假设您需要为每个销售人员分别生成一份报告。你可以这样做 –
- 确保您在过滤器区域中有销售人员字段。
- 单击数据透视表。
- 单击功能区上的分析选项卡。
- 单击数据透视表组中选项旁边的箭头。
- 从下拉列表中选择显示报告过滤器页面。
该展会报告过滤网页对话框出现对话框。选择字段销售人员并单击确定。
将为 Salesperson 字段的每个值创建一个单独的工作表，并将数据透视表过滤为该值。
工作表将由字段的值命名，该值在工作表的选项卡上可见。
切片机
数据透视表中的另一个复杂功能是切片器，可用于直观地过滤字段。
-
单击数据透视表。
-
单击分析选项卡。
-
单击过滤器组中的插入切片器。
-
在“插入切片器”对话框中单击“订购日期”、“季度”和“年”。三个切片器 – 将创建订单日期、季度和年份。
-
调整切片器的大小，为切片器上的按钮添加更多列。
-
还为 Salesperson 和 Region 字段创建切片器。
-
选择切片器样式，以便将日期字段分组为一种颜色，而其他两个字段具有不同的颜色。
-
取消选择网格线。
如您所见，您不仅拥有交互式报告，而且拥有易于理解的吸引人的报告。
数据透视表中的时间线
当数据透视表中有日期字段时，插入时间轴也是生成美观报告的一个选项。
- 使用 ROWS 区域中的 Salesperson 和 COLUMNS 区域中的 Region 创建数据透视表。
- 为字段订单日期插入时间轴。
- 过滤时间线以显示 5 个月的数据，从 2015 年 11 月到 2016 年 3 月。
设计命令
在数据透视表工具-设计命令功能区为您提供格式化数据透视表的选项，包括以下内容-
- 布局
- 数据透视表样式选项
- 数据透视表样式
布局
您可以根据您的以下偏好设置数据透视表布局 –
- 小计
- 总计
- 报告布局
- 空白行
数据透视表布局 – 小计
您可以选择是否显示小计。默认情况下，小计显示在组的顶部。
正如您可以观察到突出显示的组 – 东，小计位于组的顶部。您可以按如下方式更改小计的位置 –
- 单击数据透视表。
- 单击功能区上的设计选项卡。
- 单击布局选项组中的小计。
- 单击在组底部显示所有小计。
小计现在将出现在每个组的底部。
如果您不必报告小计，您可以选择 – 不显示小计。
总计
您可以选择显示或不显示总计。您有四种可能的组合 –
- 关闭行和列
- 为行和列打开
- 仅用于行
- 仅针对列打开
默认情况下，它是第二个组合 – 为行和列打开。
报告布局
您可以从多种报告布局中进行选择，最适合您的数据的一种。
- 紧凑的形式。
- 大纲表格。
- 表格形式。
如果多次出现，您还可以选择是否重复所有项目标签。
默认的报表布局是您熟悉的 Compact 表单。
紧凑型
紧凑形式优化了数据透视表的可读性。其他两种形式也显示字段标题。
单击以大纲形式显示。
单击以表格形式显示。
考虑以下数据透视表布局，其中字段 Month 嵌套在字段 Region 下 –
如您所见，月标签是重复的，这是默认设置。
单击不重复项目标签。月份标签将只显示一次，数据透视表看起来很清晰。
空白行
为了使您的数据透视表更加清晰，您可以在每个项目后插入一个空行。您可以稍后随时删除这些空行。
单击在每个项目后插入空行。
数据透视表样式选项
您有以下数据透视表样式选项 –
- 行标题
- 列标题
- 带状行
- 带状柱
默认情况下，行标题和列标题框被选中。这些选项分别用于显示第一行和第一列的特殊格式。选中带状行框。
选中带状列框。
数据透视表样式
您可以选择多个数据透视表样式。选择适合您的报告的一项。例如，如果您选择 Pivot Style Dark 5，您将获得以下 PivotTable 样式。
数据透视表中的条件格式
您可以通过值在数据透视表单元格上设置条件格式。
数据透视图
数据透视图为您的数据透视表添加了视觉重点。您可以插入与数据透视表数据相关的数据透视图，如下所示 –
- 单击数据透视表。
- 单击功能区上的分析选项卡。
- 单击数据透视图。
出现插入图表对话框。
单击左窗格中的列并选择堆叠列。单击确定。
显示堆积柱状图。
- 单击数据透视图上的月份。
- 过滤到二月，然后单击确定。
如您所见，数据透视表也根据数据透视图进行过滤。
