Excel Pivot Tables 简明教程
Excel Pivot Tables - Overview
A PivotTable is an extremely powerful tool that you can use to slice and dice data. You can track and analyze hundreds of thousands of data points with a compact table that can be changed dynamically to enable you to find the different perspectives of the data. It is a simple tool to use, yet powerful.
The major features of a PivotTable are as follows −
-
Creating a PivotTable is extremely simple and fast
-
Enabling churning of data instantly by simple dragging of fields, sorting and filtering and different calculations on the data.
-
Arriving at the suitable representation for your data as you gain insights into it.
-
Ability to create reports on the fly.
-
Producing multiple reports from the same PivotTable in a matter of seconds.
-
Providing interactive reports to synchronize with the audience.
In this tutorial, you will understand these PivotTable features in detail along with examples. By the time you complete this tutorial, you will have sufficient knowledge on PivotTable features that can get you started with exploring, analyzing, and reporting data based on the requirements.
Creating a PivotTable
You can create a PivotTable from a range of data or an Excel table. You can start with an empty PivotTable to fill in the details, if you are aware of what you are looking for. You can also make use of Excel Recommended PivotTables that can give you heads up on the PivotTable layouts that are best suited for summarizing your data.
You will learn how to create a PivotTable from a data range or Excel table in the Chapter - Creating a PivotTable from a Table or Range.
Excel gives you a more powerful way of creating a PivotTable from multiple tables, different data sources, and external data sources. It is named as PowerPivot that works on its database known as Data Model. You will learn these Excel power tools in other tutorials in this Tutorials Library.
You need to first know about the normal PivotTable as explained in this tutorial, before you venture into the power tools.
PivotTable Layout - Fields and Areas
数据透视表布局取决于为报表选择的字段以及如何将它们排列在区域中。只需拖动字段即可进行选择和排列。拖动字段时,数据透视表布局将保持更改,并且在几秒钟内发生。
您将在章节数据透视表字段和数据透视表区域中了解数据透视表字段和区域。
Exploring Data with PivotTable
使用数据透视表的首要目标通常是浏览数据以提取重要且必需的信息。您可以执行一些操作,包括排序、筛选、嵌套、折叠和展开、分组和取消分组等。
您将在“使用数据透视表浏览数据”一章中概述这些选项。
Summarizing Values
一旦通过不同的浏览技术整理了所需数据,您要采取的下一步就是汇总数据。Excel 为您提供了各种计算类型,您可以根据适用性和要求应用这些类型。您还可以切换不同的计算类型并在几秒钟内查看结果。
您将在“按不同计算类型汇总值”一章中学习如何在数据透视表上应用计算类型。
Excel Pivot Tables - Creation
您可以创建数据透视表,方法是从数据范围或 Excel 表中创建。在这两种情况下,数据的首行应包含列标题。
如果您确定要包含在数据透视表中的字段和所需的布局,则可以使用空数据透视表开始并构建数据透视表。
如果您不确定哪个数据透视表布局最适合您的数据,您可以使用 Excel 中的“推荐数据透视表”命令查看根据您的数据定制的数据透视表,并选择您喜欢的数据透视表。
Creating a PivotTable from a Data Range
考虑以下包含每个销售人员在每个地区和 1 月、2 月和 3 月的销售数据的数据范围 −
要从这个数据范围创建数据透视表,请执行以下操作 −
-
确保首行包含标题。您需要标题,因为它们将成为数据透视表中的字段名。
-
将数据范围命名为 SalesData_Range。
-
单击数据范围-SalesData_Range。
-
单击功能区上的插入选项卡。
单击“表格”组中的“数据透视表”。出现 Create PivotTable 对话框。
在“创建数据透视表”对话框中,在 Choose the data that you want to analyze 下,您可以从当前工作簿中选择“表”或“范围”,也可以使用外部数据源。
当您从数据范围创建一个数据透视表时,请从对话框中选择以下内容-
-
选择 Select a table or range 。
-
在“表/范围”框中,键入范围名称-SalesData_Range。
-
在“选择放置数据透视表报表的位置”下选择“新建工作表”,然后单击“确定”。
通过将此数据范围添加到数据模型,您可以选择分析多个表。您可以在 Excel PowerPivot 教程中了解如何分析多个表、使用数据模型以及如何使用外部数据源创建数据透视表。
新的工作表插入到工作簿中。新的工作表包含一个空数据透视表。命名工作表 - Range-PivotTable。
正如您所观察到的, PivotTable Fields 列表出现在工作表的右侧,其中包含数据范围中列的标题名称。此外,在功能区上,数据透视表工具 - 分析和设计出现。
Adding Fields to the PivotTable
您将在本教程后面的章节中详细了解数据透视表字段和区域。现在,请观察将字段添加到数据透视表的步骤。
假设您想按销售人员和 1 月、2 月和 3 月总结订单金额。您可以按照以下几个简单的步骤进行操作 -
-
单击数据透视表字段列表中的销售人员字段,并将其拖动到行区域。
-
单击数据透视表字段列表中的月份字段,并将其也拖动到行区域。
-
单击“订单金额”,并将其拖动到“∑ 值”区域。
如下所示,您的第一个数据透视表已准备就绪
请注意,数据透视表中显示两列,一列包含您选择的行标签,即销售人员和月份,另一列包含订单金额的总和。除了每位销售人员按月份计算的订单金额总和外,您还将获得代表该人员总销售额的子总计。如果您向下滚动工作表,您会发现最后一行是大总计,代表总销售额。
随着您本教程的进展,您将了解有关根据需要制作数据透视表的更多信息。
Creating a PivotTable from a Table
考虑以下包含与上一部分中相同的销售数据的 Excel 表 -
Excel 表本质上将具有一个名称,并且列将具有标题,这是创建数据透视表的必要条件。假设表名称为 SalesData_Table。
要从此 Excel 表创建数据透视表,请执行以下操作 -
-
单击表格 - SalesData_Table。
-
单击功能区上的插入选项卡。
-
单击“表格”组中的“数据透视表”。出现 Create PivotTable 对话框。
-
单击选择表或范围。
-
在表/范围框中,键入表名 - SalesData_Table。
-
在 Choose where you want the PivotTable report to be placed 下选择新建工作表。单击确定。
新的工作表插入到工作簿中。新的工作表包含一个空数据透视表。命名工作表 - Table-PivotTable。工作表 - Table-PivotTable 类似于您在较早部分的数据范围案例中获得的工作表。
正如您在本章前面 - 将字段添加到数据透视表的部分中看到的那样,您可以将字段添加到数据透视表。
Creating a PivotTable with Recommended PivotTables
如果你不熟悉 Excel 数据透视表,或者不知道哪些字段将生成有意义的报表,可使用 Excel 中的“推荐数据透视表”命令。推荐数据透视表提供所有可能的数据报表及相关布局。换句话说,显示的选项是根据你的数据自定义的数据透视表。
要使用“推荐数据透视表”从 Excel 表格 SalesData-Table 创建数据透视表,请按以下步骤操作:
-
单击表格 SalesData-Table。
-
Click the INSERT tab.
-
单击“表格”组中的“推荐数据透视表”。“推荐数据透视表”对话框随即出现。
“推荐数据透视表”对话框中将显示适用于你数据的可能自定义数据透视表。
-
单击每个数据透视表选项,以在右侧查看预览。
-
单击数据透视表“按销售人员和月份汇总订购金额”,然后单击“确定”。
你将获得右侧预览。
选定的数据透视表将显示在工作簿的新工作表中。
你可以看到数据透视表字段——销售人员、地区、订单金额和月份已选定。其中,区域和销售人员在行区域中,月份在列区域中,订单金额和在值区域中。
数据透视表按地区、按销售人员和按月份汇总数据。子总数显示在每个地区、每个销售人员和每个月中。
Excel Pivot Tables - Fields
数据透视表字段是一个与数据透视表关联的任务窗格。数据透视表字段任务窗格包括字段和区域。默认情况下,任务窗格显示在窗口右侧,其中字段显示在区域上方。
字段表示你的数据(范围或 Excel 表格)中的列,并会有复选框。选定的字段显示在报表中。区域代表报表的布局和报表中包含的计算。
在任务窗格底部,你会找到一个选项——延迟布局更新,旁边有一个更新按钮。
-
默认情况下,此选项未选定,并且你在字段选择或布局选项中进行的任何更改都会立即反映在数据透视表中。
-
如果你选中此选项,则在单击按钮之前不会更新你的选择中的更改。
在本章中,你将了解有关字段的详细信息。在下一章中,你将了解有关区域的详细信息。
PivotTable Fields Task Pane
你可以在包含数据透视表的工作表中找到数据透视表字段任务窗格。要查看数据透视表字段任务窗格,请单击数据透视表。如果未显示数据透视表字段任务窗格,请检查“功能区”中的以下内容:
-
单击功能区上数据透视表工具下的“分析”选项卡。
-
在“显示”组中检查“字段列表”是否已被选中(即突出显示)。
-
如果“字段列表”未被选中,请单击它。
透视表字段任务窗格将显示在窗口右侧,标题为“透视表字段”。
Moving PivotTable Fields Task Pane
在透视表任务窗格的“透视表字段”标题的右侧,您将找到按钮 。该按钮表示“任务窗格选项”。单击按钮 。“任务窗格选项:移动、大小和关闭”将显示在下拉列表中。
您可以如下所示将透视表任务窗格移动到窗口中的任意位置:
-
在下拉列表中单击“移动”。按钮将出现在任务窗格中。
-
单击图标并将窗格拖动到想要放置的位置。您可以将任务窗格放置在透视表旁边,如下所示。
您可以将任务窗格放置在窗口的左侧,如下所示。
Resizing PivotTable Fields Task Pane
您可以调整透视表任务窗格的大小,即增加/减少任务窗格的长度和/或宽度,如下所示:
-
单击“任务窗格选项”,即标题“透视表字段”的右侧。
-
在下拉列表中单击“大小”。
-
使用符号 ⇔ 增加/减少任务窗格的宽度。
-
使用符号 ⇕ 增加/减少任务窗格的宽度。
在 ∑ 值区域中,为了使订单金额的总和完全可见,您可以如下所示调整任务窗格的大小。
PivotTable Fields
透视表字段列表包括与您的工作簿关联的所有表格和对应的字段。通过在透视表字段列表中选择字段,您可以创建透视表。
包含复选框的表格和对应的字段反映了您的透视表数据。由于您可以随机选中/取消选中字段,您可以快速更改透视表,突出显示想要报告或显示的摘要数据。
您可以观察到,如果只有一个表,表名将不会显示在透视表字段列表中。只显示带有复选框的字段。
您将在字段列表上方找到操作“选择要添加到报告中的字段”。在右侧,您将找到按钮 代表工具。
-
单击“工具”按钮。
在下拉列表中,您会找到以下内容 -
-
字段和区域有五个不同的布局选项。
-
字段列表中有两个为字段排序的选项 - 升序排列。按照数据源顺序排序。
正如您在上面的字段列表中观察到的,排序方式是默认的 - 例如,按照数据源顺序。也就是说,这是表中列出现的顺序。
通常,您可以保留默认顺序。然而,有时候,您可能会遇到表中有很多字段并且可能不了解它们。在这种情况下,您可以按字母顺序对字段进行排序,方法是单击工具下拉列表中的 - 升序排列。然后,数据透视表字段列表如下所示 -
Excel Pivot Tables - Areas
数据透视表区域是数据透视表字段任务窗格的一部分。通过排列区域的所选字段,您会得到不同的数据透视表布局。既然您可以简单地在各个区域之间拖动字段,这样您就能快速切换跨不同布局,总结数据,而且是以您想要的方式。
您已经在本教程较早的数据透视表字段章节中了解了数据透视表字段任务窗格。在本章节,您将学习到数据透视表区域。
有四个可用的数据透视表区域 -
-
ROWS.
-
COLUMNS.
-
FILTERS.
-
∑ VALUES(解读为求和)。
消息 Drag fields between areas below 显示在区域上方。
使用数据透视表区域,您可以选择 -
-
哪些字段显示为行(行区域)。
-
哪些字段显示为列(列区域)。
-
如何总结您的数据(求和区域)。
-
任何字段的过滤器(过滤器区域)。
您只要在这些区域之间拖动字段,观察数据透视表布局的变化即可。
ROWS
如果您通过选中复选框在数据透视表字段列表中选择字段,所有非数字字段将自动添加到行区域,以您选择的顺序。
您可以选择将字段拖动到行区域。放在行区域的字段将显示在数据透视表中,而行标签则是所选字段的值。
例如,请考虑销售数据表。
-
将字段销售人员拖动到“行”区域。
-
将 Month 域拖到 ROWS 区域。
您的数据透视表会随一行包括行标签——销售人员和月份以及一行作为总计,如下所示。
COLUMNS
您可以将域拖至 COLUMNS 区域。
放置在 COLUMNS 区域中的域在数据透视表中显示为列,其中列标签是已选域的值。
将 Region 域拖到 COLUMNS 区域。您的数据透视表会显示第一行包含行标签——销售人员和月份的下一列包括列标签——区域及最后一行总计,如下所示。
-
将 Month 域从 ROWS 拖到 COLUMNS。
-
将 Region 域从 COLUMNS 拖到 ROWS。您的数据透视布局将发生变化,如下所示。
您能看到现在只有五列——第一列带行标签,三列带列标签及最后一列带总计。
行和列的数量基于您在这些域中拥有的值的数量。
∑ VALUES
数据透视表的主要用途是汇总值。因此,通过将您要按照这些值汇总数据放置在 ∑ VALUES 区域中,您将得到汇总表。
-
将 Order Amount 域拖到 ∑ VALUES 。
-
将 Region 域拖到 ROWS 中 Salesperson 域上方。此步骤是为了更改嵌套顺序。您将在本教程中的“数据透视表中的嵌套”章节中学习嵌套。
正如您看到的,数据已按区域、销售人员和月份汇总。您会看到按月划分的每个区域的子计。您还可以在 Grand Total 行中看到按月划分的总计,在 Grand Total 列中看到按区域划分的总计。
FILTERS
Filters 区域用于将筛选器放置到数据透视表中。假设您仅想对选定区域分别显示结果。
将 Region 域从 ROWS 区域拖到 FILTERS 区域。筛选器 Region 将放置到数据透视表上。如果您没有数据透视表上方的空行,则数据透视表会向下推入,以便插入筛选器上方的行为。
正如您看到的,(ALL) 默认情况下出现在筛选器中,并且数据透视表显示所有区域值的 data。
-
点击筛选器右边的箭头。
-
选中框——选择多个项目。
复选框将会出现在下拉列表中的所有选项中。默认情况下,所有框全部选中。
-
选择框 - 北方和南方。
-
清除其他框。单击确定。
数据透视表会发生更改来反映筛选出的数据。
你可以观察到,筛选器显示 (Multiple Items)。因此,当某人看数据透视表时,看不出来哪些值是筛选过的。
Excel 提供了你另一个工具,称作切片器,用来更有效地进行筛选。你将在教程的后面章节详细了解如何在数据透视表中筛选数据。
Excel Pivot Tables - Exploring Data
Excel 数据透视表允许你探索和提取 Excel 表格或数据范围内的重要数据。有几种方法去做,你可以选择最适合你数据的。此外,在你探索数据时,你可以立即看到不同组合,当改变你的选择去挑选数据值时。
你可以在数据透视表中进行以下操作:
-
Sort the data.
-
Filter the data.
-
Nest the PivotTable fields.
-
展开或折叠字段。
-
给字段值分组或取消分组。
Sorting and Filtering Data
你可以按照字段升序或降序对数据透视表中的数据进行排序。你还可以按照小计从最大到最小的值或者从最小到最大的值进行排序。你还可以设定排序选项。你将在此教程的章节中详细学习这些内容 - 在数据透视表中通过排序数据。
你可以筛选数据透视表中的数据来专注于一些特定的数据。你在数据透视表中有几个筛选选项,你将在教程中的章节 - 在数据透视表中筛选数据中学习这些选项。你可以用切片器筛选,你将在教程中的章节 - 用切片器筛选了解这些知识。
Excel Pivot Tables - Sorting Data
你可以对数据透视表中的数据进行排序,这样可以让你更容易找到你想分析的项。你可以按照从最小值到最大值或从最大值到最小值或者你选择的任何其它自定义顺序来对数据进行排序。
思考以下数据透视表,你在其中区域明智、销售人员明智和月份明智地总结了销售数据。
Sorting on Fields
你可以在上面的数据透视表中对位于行或列中的字段进行排序 - 区域、销售人员和月份。
若要使用字段销售人员对数据透视表进行排序,请按下列步骤进行:
-
单击行标签中的箭头。
-
从下拉列表中从“选择字段”框中选择“销售人员”。
显示以下排序选项 −
-
Sort A to Z.
-
Sort Z to A.
-
More Sort Options.
此外,默认情况下,按照升序对销售人员字段进行排序。单击 Sort Z to A 。销售人员字段将按降序排序。
同样,您可以单击列标签中的箭头来按字段排序 – 月份。
Sorting on Subtotals
假设要根据总订单数量对数据透视表进行排序 – 每个区域从最高到最低。也就是说,你要根据子总数对数据透视表进行排序。
您可以看到,子总数没有箭头。您仍然可以按如下方式对子总数对数据透视表进行排序 −
-
右键单击“总计”列中任何一个销售人员的子总数。
-
从下拉列表中选择 Sort 。
-
另一个下拉列表将显示排序选项 – 从最小到最大排序、从最大到最小排序和更多排序选项。选择从最大到最小排序。
在每个区域中,按从最高值到最低值对“总计”列中的子总数进行排序。
同样,如果您要按区域对子总数对数据透视表进行排序,请执行以下操作 −
-
右键单击“总计”列中任何一个区域的子总数。
-
单击下拉列表中的“排序”。
-
在第二个下拉列表中单击“从最大到最小排序”。数据透视表将按区域对子总数进行排序。
正如您所观察到的,南方拥有最高的订单金额,而北方拥有最低的订单金额。
您还可以按月总金额对数据透视表进行排序,如下所示 −
-
右键单击“总计”行中的任何一个子总数。
-
从下拉列表中选择“排序”。
-
从第二个下拉列表中选择“从最大到最小排序”。
数据透视表按总金额按月排序。
您可以观察到,2 月的订单金额最高,而 3 月的订单金额最低。
More Sort Options
假设您想按 1 月的总金额按区域对数据透视表进行排序。
-
单击“行标签”中的箭头。
-
从下拉列表中选择“更多排序选项”。将显示 Sort (Region) 对话框。
如您所见,在“摘要”下,当前的排序顺序为升序按区域排序。升序 (A 到 Z) 按排序选项选中。在该框下方,显示 Region 。
-
单击包含区域的框。
-
单击订单金额之和。
单击 More Options 按钮。将出现 More Sort Options (Region) 对话框。
如您所见,“排序依据”下选择总计。在“摘要”下,当前的排序顺序为 Sort Region by Sum of Order Amount 升序。
-
单击“排序依据”下的 Values in selected column: 。
-
在该框下方,键入 B5。
如您所见,“摘要”下,当前的排序顺序如下所述 −
-
使用此列中的值按 Sum of Order Amount 升序按区域排序:1 月份。单击“确定”。
-
将出现“排序(区域)”对话框。在排序选项下选择降序 (Z 到 A) 按:。
在“摘要”下,当前的排序顺序如下所述 −
按订单金额之和降序按区域排序,使用此列中的值:1 月份。单击“确定”。将按 1 月份的值对数据透视表按区域进行排序。
如您所见,在 1 月份,西部地区的订单金额最高,而北部的订单金额最低。
Sorting Data Manually
在数据透视表中,数据将根据您选择的排序选项自动排序。这称为自动排序。
将光标放置在行标签或列标签中的箭头处。
AutoSort 出现,显示数据透视表中每个字段的当前排序顺序。现在,假设你要按区域字段对字段进行排序——东、西、北和南。你可以按照以下方式手动完成:
-
单击行标签中的箭头。
-
从下拉列表中在“选择字段”框中选择“区域”。
-
单击 More Sort Options 。将出现“排序(区域)”对话框。
-
选择“手动”(你可以拖动项目以重新排列它们)。
-
Click OK.
在“摘要”下,当前排序顺序以“将“区域”字段的项目拖动到此处以按任意顺序显示它们”给出。
单击“东”,然后将其拖动到顶部。在你拖动“东”时,一系列横向绿线会出现在整行中。
用“区域”字段的其他项目重复拖动,直到获得所需排列。
您可以观察到以下内容 −
-
嵌套字段的项目——销售人员也会与相应的区域字段项目一起移动。此外,其他列中的值也会相应移动。
-
如果你将光标放置在行标签或列标签中的箭头,AutoSort 会出现,仅显示销售人员和月份字段的当前排序顺序。由于你已手动对区域字段进行排序,因此它不会显示在 AutoSort 中。
Note ——你无法对数据透视表字段列表中位于“∑ VALUES”区域的字段项目进行此手动拖动。因此,你无法拖动此数据透视表中的“订单金额总和”值。
Setting Sort Options
在上一节中,你学习了如何将字段的排序选项设置为手动。你可以设置如下所示的更多排序选项:
-
单击行标签中的箭头。
-
在“选择字段”框中选择“区域”。
-
单击“更多排序选项”。将出现“排序(区域)”对话框。
-
单击“更多选项”按钮。
将出现“更多排序选项(区域)”对话框。你可以在此对话框中设置更多排序选项。
在“自动排序”下,你可以选中或取消选中该框——每次更新报表时自动排序,以便在数据透视表数据更新时允许或停止自动排序。
-
取消选中该框——每次更新报表时 Sort automatically 。
如今,“第一个键的排序顺序”选项变得可用。你可以使用此选项选择你想要使用的自定义的顺序。
-
点击“第一个键的排序顺序”下面的框。
正如你能观察到的,在一周中某天和一年的某月在下拉列表中提供了自定义列表。你可以使用任何这些,或者你可以使用你自己的自定义列表,例如高、中、低或按字母顺序排列的 S、M、L、XL 尺寸列表。
你可以在功能区的“文件”选项卡中创建自定义列表。文件 → 选项。在 Excel 选项对话框中,单击高级,并浏览至常规。你会在“创建用于排序和填充序列的列表”旁边找到“编辑自定义列表”按钮。
要注意的是,当你在数据透视表中更新(刷新)数据时,不会保留自定义列表的排序顺序。
在“排序依据”下方,你可以单击选定列中的 Grand Total or Values 来按这些值排序。当你将排序设置为“手动”时,此选项不可用。
Excel Pivot Tables - Filtering Data
你可能必须对数据透视表数据的子集进行深入分析。这可能是因为你拥有大量数据,并且你的重点是放在更少部分的数据上,或者不管数据的规模大小,你的重点是放在某些特定数据上。你可以基于一个或多个字段的子集的值来筛选数据透视表中的数据。以下是如何做到这一点的几种方法 −
-
Filtering using Slicers.
-
Filtering using Report Filters.
-
Filtering data manually.
-
Filtering using Label Filters.
-
Filtering using Value Filters.
-
Filtering using Date Filters.
-
使用前 10 名筛选器进行筛选。
-
Filtering using Timeline.
你将在下一章学习如何使用切片器进行筛选数据。你将了解在本章中提到的其他筛选方法。
考虑以下数据透视表,其中按地区、销售人员和月份汇总了销售数据。
Report Filters
您可以为某个字段分配筛选器,以便您可以根据该字段的值动态更改数据透视表。
将区域从行拖动到数据透视表区域中的筛选器。
带有“区域”标签的筛选器显示在数据透视表的上方(如果你没有将空行放在数据透视表上方,数据透视表将被下推以腾出空间供筛选器使用)。
你会观察到
-
销售人员值显示在行中。
-
月值显示在列中。
-
区域筛选器显示在顶部,默认选择为全部。
-
汇总值为订单金额之和。以销售人员为基础的订单金额之和显示在“总计”列中。以月份为基础的订单金额之和显示在“总计”行中。
-
单击“筛选器区域”右侧框中的箭头。
显示带“区域”字段值的下拉列表。选中 Select Multiple Items 框。
默认情况下,所有框都处于选中状态。取消选中 All 框。所有框都将取消选中。
然后选中框 - 南部和西部,然后单击确定。
仅会汇总与南部和西部区域相关的数据。
“筛选区域 - (多项)”旁边的单元格中会显示已选择多项,这表示您已选择多项。但根据显示的报表无法得知选择了几项或所选择的是哪些项。在这种情况下,使用切片器是更好的筛选选项。
Manual Filtering
您还可以手动筛选数据透视表来挑选字段的值。这可以通过单击行标签或列标签单元格中的箭头来实现。
假设您只希望分析 2 月份的数据。您需要根据月份字段筛选值。正如您所观察到的,月份是列标签的一部分。
单击列标签单元格中的箭头。
正如您所观察到的,下拉列表中有一个搜索框,而该框下方是所选字段值(即月份)的列表。所有值的框都被选中,这表明已选择该字段的所有值。
-
取消选中值列表顶部的(全部选择)框。
-
选中您要显示在数据透视表中的值(在本例中为 2 月)的框,然后单击确定。
数据透视表仅显示与所选月份字段值(即 2 月)相关的值。您会观察到,筛选箭头变为图标,以表示已应用筛选器。将光标置于图标上。
您会观察到,显示的是手动筛选器已应用到字段“月份”。
如果您想更改筛选器选择值,请执行以下操作:
-
Click the icon.
-
选中/取消选中值得框。
如果列表中没有显示字段的所有值,则拖动下拉列表右下角的控制手柄将其扩大。或者,如果您知道该值,则在搜索框中输入它。
假设您希望对上述经过筛选的数据透视表应用另一个筛选器。例如,您想显示沃尔特斯·克里斯在 2 月份的数据。您需要通过为销售人员字段添加另一个筛选器来优化筛选操作。正如您所观察到的,销售人员是行标签的一部分。
-
单击行标签单元格中的箭头。
将显示字段值(区域)的列表。这是因为区域在嵌套顺序中位于销售人员的外层。您还有另一个选项 - 选择字段。单击“选择字段”框。
-
从下拉列表中单击“销售人员”。将显示字段值(销售人员)的列表。
-
取消选中(全部选择),然后选中“沃尔特斯·克里斯”。
-
Click OK.
数据透视表仅显示与已选择的“月份”字段值(二月)和“销售人员”字段值(Walters, Chris)相关的值。
行标签旁边的筛选箭头也会更改为图标,以指示已经应用了筛选。将光标放在行标签或列标签上的图标上。
会显示一个文本框,指示已经在“月份”和“销售人员”字段上应用了手动筛选。
因此,您可以根据任意数量的字段和任意数量的值手动筛选数据透视表。
Filtering by Text
如果字段中包含文本内容,则可以按文本筛选数据透视表,如果相应字段标签是基于文本的。例如,考虑以下员工数据。
数据包含员工的详细信息(员工 ID、头衔、出生日期、婚姻状况、性别和雇用日期)。此外,数据还包含员工的经理级别(级别 0-4)。
假设您必须对向给定员工报告的员工数进行分析,按头衔划分。您可以创建如下所示的数据透视表。
您可能想知道有多少个头衔中带有“经理”的员工有向自己报告的员工。因标签“头衔”是基于文本的,因此您可以按如下方式对“头衔”字段应用标签筛选:
-
单击行标签单元格中的箭头。
-
从下拉列表中,在“选择字段”框中选择“头衔”。
-
Click on Label Filters.
-
在第二个下拉列表中,单击“包含”。
“标签筛选(头衔)”对话框会出现。在“包含”旁边框中输入“经理”。单击“确定”。
数据透视表将筛选到包含“经理”的头衔值。
-
Click the icon.
您可以看到显示,指示:
-
标签筛选已经应用于字段“头衔”,且
-
已应用的标签筛选是什么。
Filtering by Values
您可能想知道向他们报告的员工超过 25 人的员工的头衔。为此,您可以按如下方式对“头衔”字段应用数值筛选:
-
单击行标签单元格中的箭头。
-
从下拉列表中,在“选择字段”框中选择 Title 。
-
Click on Value Filters.
-
从第二个下拉列表中,选择“大于或等于”。
“值筛选(头衔)”对话框会出现。在右侧框中输入“25”。
将筛选数据透视表,显示汇报给他们的员工超过 25 名的员工职称。
Filtering by Dates
您可能需要显示 2015-15 财年雇佣的所有员工的数据。您可以使用数据筛选器,方法如下:
-
在数据透视表中包含 HireDate 字段。现在,您不需要管理员数据,所以请从数据透视表中删除 ManagerLevel 字段。
现在,您在数据透视表中有了一个日期字段,可以用户日期筛选器。
-
单击行标签单元格中的箭头。
-
从下拉列表中的选择字段框中选择 HireDate。
-
Click Date Filters.
-
从第二个下拉列表中选择 Between 。
日期筛选器(HireDate)对话框出现。在两个日期框中输入 4/1/2014 和 3/31/2015。单击确定。
将筛选数据透视表,仅显示 HireDate 在 2014 年 4 月 1 日至 2015 年 3 月 31 日之间的日期。
您可以按如下方式将日期分组为季度:
-
右键单击任何日期。 Grouping 对话框出现。
-
在从这里开始框中输入 4/1/2014。选中框。
-
在结束于框中输入 3/31/2015。选中框。
-
在 By 框下方单击季度。
日期将在数据透视表中分组为季度。您可以通过将字段 HireDate 从行区域拖到列区域,使表格看起来更紧凑。
您将能够按季度了解在该财政年度雇佣了多少员工。
Filtering Using Top 10 Filter
您可以使用前 10 个筛选器在数据透视表中显示字段前几个或后几个值。
-
单击行标签单元格中的箭头。
-
Click Value Filters.
-
在第二个下拉列表中单击前 10 个。
将出现前 10 个筛选器(职称)对话框。
-
在第一个框中,单击“顶部”(你也可以选择“底部”)。
-
在第二个框中,输入一个数字,比如 7。
-
在第三个框中,你有三个可以进行筛选的选项。单击“项目”按项目数筛选。单击“百分比”按百分比筛选。单击“总和”按总和筛选。
-
由于你已按 EmployeeID 计数,因此单击“项目”。
-
在第四个框中,单击字段“EmployeeID 计数”。
-
Click OK.
按 EmployeeID 计数的前七个值将显示在数据透视表中。
正如你可以看到,在该财政年度中最多雇员的职务是“生产技术人员”,而且其中多数发生在第一季度。
Filtering Using Timeline
如果你的数据透视表有一个日期字段,你就可以使用时间线筛选数据透视表。
根据先前使用的 Employee Data 创建一个数据透视表,并在“创建数据透视表”对话框中将数据添加到数据模型中。
-
将字段“名称”拖动到“行”区域。
-
将字段“EmployeeID”拖动到“∑ 值”区域,并选择“计数”进行计算。
-
Click on the PivotTable.
-
Click the INSERT tab.
-
在“筛选器”组中,单击“时间线”。“插入时间线”对话框便会弹出。
-
Check the box HireDate.
-
单击“确定”。时间线随即出现在工作表中。
-
时间线工具出现在功能区中。
正如你所看到的,“全部期间 - 月”显示在时间线中。
-
单击“月”旁边的箭头。
-
从下拉列表中选择“季度”。时间线显示便会更改为“全部期间 - 季度”。
-
Click on 2014 Q1.
-
按住“Shift”键不放,然后拖到“2014 Q4”。时间线期间已选为“2014 Q1 - Q4”。
-
会按此时间线期间筛选数据透视表。
Filtering data using Slicers
使用一个或多个切片器是筛选数据快速有效的方法。可以为想要筛选的每个字段插入切片器。切片器将显示它所代表字段值的按钮。您可以单击切片器的按钮以选择/取消选择字段中的值。
切片器与枢纽表保持可见,因此您将始终了解用于筛选的字段以及在筛选后的枢纽表中显示或隐藏了这些字段中的哪些值。
要了解切片器的用法,请考虑按区域、按月份和按销售人员划分的销售数据示例。假设您拥有包含此数据的以下枢纽表。
Inserting Slicers
假设您想基于字段 - 区域和月份对该枢纽表进行筛选。
-
单击功能区中【PIVOTTABLE 工具】下的【分析】。
-
单击“筛选”组中的“插入切片器”。此时将显示“插入切片器”对话框。其中包含数据表中的所有字段。
-
勾选区域和月份。
-
Click OK.
所有选定字段的切片器都默认选中所有值后显示。切片器工具随即显示在功能区上,用于设定切片器设置、外观和风格。
Filtering with Slicers
您可以看到,每个切片器都包含它所代表字段的所有值,并且这些值以按钮的形式显示。默认情况下,字段的所有值都被选中,因此所有按钮都高亮显示。
假设您只想显示南部和西部区域的枢纽表,以及 2 月和 3 月份。
-
单击“区域”切片器中的“南部”。在“区域”切片器中,只有“南部”将高亮显示。
-
按住 Ctrl 键并单击区域切片器中的西部。
-
单击月切片器中的 2 月。
-
按住 Ctrl 键并单击月切片器中的 3 月。
切片器中选定的项目将突出显示。将显示选定项目的汇总值透视表。
要添加/删除过滤器中的字段值,请按住 Ctrl 键并单击字段切片器中的那些按钮。
Slicer Tools
一旦插入切片器,带选项选项卡的切片器工具就会出现在功能区。若要查看切片器工具,请单击切片器。
正如你所看到的,在切片器工具——选项选项卡下,你可以更改切片器的外观和感觉,包括:
-
Slicer Caption
-
Slicer Settings
-
Report Connections
-
Selection Pane
Slicer Caption
你可以在切片器组中找到切片器标题框。切片器标题是显示在切片器上的页眉。默认情况下, Slicer Caption 是它所代表的字段的名称。
-
单击区域切片器。
-
单击功能区上的选项选项卡。
功能区上的切片器组,在切片器标题框,区域显示为切片器的页眉。它是插入切片器的字段的名称。你可以按照如下方式更改切片器标题:
-
单击功能区切片器组中的切片器标题框。
-
删除区域。框被清空。
-
在框中输入位置并按回车。切片器标题更改为位置,同一位置反映在切片器的页眉中。
Note - 你只更改了切片器标题,即页眉。切片器所代表的字段的名称——区域保持原样。
Slicer Settings
你可以使用切片器设置更改切片器的名称、切换切片器标题、选择是否显示切片器页眉以及设置列表的排序和筛选选项——
-
单击切片器 - 位置。
-
单击功能区的选项卡选项。您可以在功能区的切片器组中找到切片器设置。您也可以在右击切片器时在下拉列表中找到切片器设置。
-
单击切片器设置。您便会看到切片器设置对话框。
正如您所看到的,以下内容已针对切片器修复:
-
Source Name.
-
公式中要使用的名称。
您可以针对切片器更改以下项:
-
Name.
-
Header – Caption.
-
Display header.
-
切片器上显示的项目的排序和筛选选项。
Report Connections
您可以将不同的数据透视表连接到切片器,只要满足以下条件之一:
-
使用相同数据创建了数据透视表。
-
一个数据透视表已被复制并粘贴为一个附加的数据透视表。
-
使用显示报表筛选器页在不同的工作表上创建了多个数据透视表。
考虑使用相同数据创建的以下数据透视表:
-
将顶部数据透视表命名为数据透视表 - 顶部,将底部数据透视表命名为数据透视表 - 底部。
-
单击顶部数据透视表。
-
为区域字段插入一个切片器。
-
在切片器中选择东和北。
观察到只对顶部数据透视表应用了筛选,并没有针对底部数据透视表应用。您可以使用相同的切片器用于两个数据透视表,方法是将其连接到底部数据透视表,如下所示:
-
单击切片器 - 区域。切片器工具将出现在功能区上。
-
单击功能区上的选项选项卡。
您将在功能区的切片器组中找到报表连接。您也可以在右击切片器时在下拉列表中找到报表连接。
在“切片器”组中,单击 Report Connections 。
“ Report Connections ”对话框随即将出现。已选中“枢纽表格-顶部”复选框,而其他复选框未被选中。同时选中“枢纽表格-底部”复选框,然后单击“确定”。
底部枢纽表格将筛选到选定项(东部和北部)。
之所以能这样,是因为现在这两个枢纽表格都已连接至切片器。如果您对切片器中的选定内容进行更改,则相同筛选将在两个枢纽表格中显示。
Selection Pane
您可以使用“选择窗格”开启和关闭工作表上切片器的显示。
-
单击切片器 - 位置。
-
单击功能区上的选项选项卡。
-
单击功能区的“排列”组中的“选择窗格”。“选择窗格”随即将出现在窗口右侧。
正如您所观察到的,所有切片器的名称均已列在“选择窗格”中。在名称右侧,您可以找到一个可见性符号,表示切片器在工作表上可见。
单击“月份”符号。该符号会更改为符号,表示切片器已隐藏(不可见)。
正如您所观察到的,切片器(月份)未显示在工作表上。但是,请记住您尚未移除“月份”切片器,而只是将其隐藏。
-
单击“月份”符号。
-
该符号会更改为符号,表示切片器现在已可见。
当您关闭或开启切片器的可见性时,该切片器中用于筛选的项的选定内容将保持不变。您还可通过向上/向下拖动的方式更改“选择窗格”中切片器的顺序。
Excel Pivot Tables - Nesting
如果您在任何枢纽表格区域中有多个字段,则该枢纽表格布局取决于您将字段放置在该区域中的顺序,称为嵌套顺序。
如果您了解您的数据如何构建,则可按所需顺序放置这些字段。如果您不确定数据的构建,您可以更改字段顺序,此操作会立即更改枢纽表格的布局。
在本章中,您将了解字段的嵌套顺序,以及如何更改嵌套顺序。
Nesting Order of the Fields
考虑销售数据示例,其中您已按以下顺序放置这些字段:
正如您所看到的,在行区域中有两个字段——销售人员和区域,它们按此顺序排列。此字段顺序称为嵌套顺序,即首先是销售人员,然后是区域。
在枢纽表格中,行中的值将基于以下顺序显示,如下所示。
正如您所观察到的,嵌套顺序中第二个字段的值嵌入在第一个字段的各个值之下。
在您的数据中,每位销售人员仅与一个区域相关联,但是大多数区域与多个销售人员相关联。因此,如果您反转嵌套顺序,您的数据透视表会看起来更有意义。
Changing the Nesting Order
若要更改某个区域中的字段嵌套顺序,只需单击该字段并将其拖动到您想要的位置即可。
单击 ROW 区域中的 Salesperson 字段,并将其拖动到 Region 字段下方。这样,您就将嵌套顺序更改为 - 首先是 Region,然后是 Salesperson,如下所示:
由此产生的数据透视表如下所示:
您可以清楚地看到,使用嵌套顺序(Region,然后是 Salesperson)的布局会产生比嵌套顺序(Salesperson,然后是 Region)更好的紧凑型报表。
如果销售人员代表多个区域,而您需要按销售人员汇总销售额,则先前的布局将是更好的选择。
Excel Pivot Tables - Tools
在包含数据透视表的工作表中,Ribbon 将包含数据透视表工具以及 ANALYZE 和 DESIGN 选项卡。ANALYZE 选项卡包含多项命令,使您能够浏览数据透视表中的数据。DESIGN 选项卡命令将有助于使用各种报表选项和样式选项对数据透视表进行构造。
您将在本章学习 ANALYZE 命令。您将在“使用数据透视表美化报表”一章中学习 DESIGN 命令。
ANALYZE Commands
ANALYZE 选项卡功能区上的命令包括以下内容:
-
展开和折叠字段。
-
对字段值进行分组和取消分组。
-
Active Field Settings.
-
PivotTable Options.
Expanding and Collapsing a Field
如果您在数据透视表中嵌套了字段,则可以展开和折叠单个项目,也可以展开和折叠活动字段的所有项目。
考虑以下数据透视表,其中将 Salesperson 字段嵌套在 Region 字段下。
单击 East 左侧的 符号。字段 Region 的项目 East 将折叠。
正如您所看到的,字段 Region 的其他项目(North、South 和 West)不会折叠。如果您要折叠其中任何一个,请对 East 所做的步骤进行重复。
-
单击 East 左侧的 符号。字段 Region 的项目 East 将展开。
如果您要一次折叠字段的所有项目,请做以下操作:
-
单击该字段的任何项目(Region)。
-
单击功能区中的“分析”选项卡。
-
单击“活动字段”组中的“折叠字段”。
字段 Region 的所有项目都将折叠。
如果您希望立即展开所有字段的项目,请执行以下操作:
-
单击该字段的任意项目——“区域”。
-
单击功能区中的“分析”选项卡。
-
单击“活动字段”组中的“展开字段”。
字段“区域”的所有项目都将展开。
Grouping and Ungrouping Field Values
您可以对字段值进行分组和取消分组以定义您自己的聚类。例如,您可能想要了解合并了东部和北部区域的数据。
-
在数据透视表中选择“区域”字段的“东部”和“北部”项目,以及嵌套的“销售人员”字段项目。
-
单击功能区中的“分析”选项卡。
-
在组——“组”中单击“分组选择”。
项目——“东部”和“北部”将被分组在一个名为“组 1”的名称下。此外,将会创建一个新的“南部”,南部将被嵌套其中,并将创建一个新的“西部”,西部将被嵌套其中。
您还可以看到,数据透视表字段列表中添加了新字段——“区域 2”,它出现在“行”区域中。
-
在数据透视表中选择“区域 2”字段的“南部”和“西部”项目,以及嵌套的“区域”和“销售人员”字段项目。
-
单击功能区中的“分析”选项卡。
-
在组——“组”中单击“分组选择”。
该字段“区域”的项目——“南部”和“西部”将被分组在一个名为“组 2”的名称下。
若要取消分组,请执行以下操作:
-
单击“组名称”。
-
Click the ANALYZE tab.
-
在组——“组”中单击“取消分组”。
Grouping by a Date Field
查看以下数据透视表,其中您按“员工编号”计数、雇佣日期和职务总结了员工数据。
假设您要按雇佣日期字段(这是一个日期字段)将此数据分组为几年和几个季度。
-
单击数据透视表中的日期项目。
-
单击功能区中的“分析”选项卡。
-
在组——“组”中单击“分组字段”。
将出现“分组”对话框。
-
设置以下日期——“开始于”和“结束于”。
-
在“按”下面的方框中选择季度和年份。要选择/取消选择多项,请按住 Ctrl 键。
-
Click OK.
雇佣日期字段值将被分组到季度,嵌套在年份中。
如果您想取消此分组,可以通过单击组中的 Ungroup 来执行,具体如早前面板所示 - 功能区中的“按组”。
Excel Pivot Tables - Summarizing Values
您可以通过将字段放在数据透视表字段任务窗格的∑ VALUES区域中来汇总数据透视表。默认情况下,Excel 会将汇总当作∑ VALUES区域中字段的值的总和。但是,您还有其他计算类型,如计数、平均、最大值、最小值等。
在本指南中,您将学习如何根据您希望汇总数据透视表中数据的方式设置计算类型。
Sum
考虑以下数据透视表,其中汇总了按区域、销售人员和月份划分的销售数据。
如您所见,当您将“订单金额”字段拖动到∑ VALUES区域时,它会显示为“订单金额的总和”,这表示计算是以总和形式执行的。在数据透视表中,在左上角显示“订单金额的总和”。此外,“总计”列和“总计”行分别以子总计字段的形式显示在行和列中。
Value Field Settings
使用“值字段设置”,可以在数据透视表中设置计算类型。您还可以决定希望如何显示您的值。
-
单击∑ VALUES区域中的“订单金额的总和”。
-
从下拉列表中选择“值字段设置”。
将显示“值字段设置”对话框。
源名称是字段,自定义名称是字段的总和。计算类型是求和。单击 Show Values As 选项卡。
在“显示值”框中,显示 No Calculation 。单击 Show Values As 框。你可以找到几种显示总价值的方式。
% of Grand Total
你可以按大计总额的百分比显示数据透视表中的值。
-
在“自定义名称”框中,键入大计总额的百分比。
-
单击“显示值”框。
-
在下拉列表中单击“大计总额的百分比”。单击“确定”。
数据透视表将值汇总为大计总额的百分比。
正如你所能观察到的,数据透视表左上角的数据透视表字段窗格中的“订单金额总和”和“∑ VALUES”区域已变为新的自定义名称 - 大计总额的百分比。
-
单击大计总额列的标题。
-
在公式栏中键入“大计总额的百分比”。列和行标题都将变为“大计总额的百分比”。
% of Column Total
假设你想按每个月总额的百分比汇总值。
-
单击∑ VALUES区域中的“订单金额的总和”。
-
从下拉列表中选择“值字段设置”。将出现“值字段设置”对话框。
-
在“自定义名称”框中,键入“月总额的百分比”。
-
单击“显示值”框。
-
从下拉列表中选择“列总额的百分比”。
-
Click OK.
数据透视表将值汇总为列总额的百分比。在“月份”列中,你将找到特定月份总额的百分比值。
-
单击大计总额列的标题。
-
在公式栏中键入“列总额的百分比”。列和行标题都将变为“列总额的百分比”。
Count
假设你想按帐号按区域、按销售人员和按月汇总值。
-
Deselect Order Amount.
-
将账户拖动至∑ VALUES区域。账户总和将显示在 ∑ VALUES 区域中。
-
点击账户总和。
-
从下拉列表中选择“值字段设置”。将出现“值字段设置”对话框。
-
在按字段汇总值字段框中,选择计数。自定义名称变更为账户计数。
-
Click OK.
账户计数将按如下所示显示 -
Average
假设你希望通过订单金额的平均值,按区域、销售人员和月份总结数据透视表。
-
Deselect Account.
-
将订单金额拖动至 ∑ VALUES 区域。订单金额的总和将显示在 ∑ VALUES 区域中。
-
点击订单金额总和。
-
点击下拉列表中的值字段设置。值字段设置对话框会出现。
-
在按字段汇总值字段框中,点击平均值。自定义名称变更为订单金额的平均值。
-
Click OK.
平均值将按如下所示显示 -
您必须设置数据透视表中值的数字格式以使其更具可读性。
-
点击 ∑ VALUES 中的订单金额平均值。
-
点击下拉列表中的值字段设置。值字段设置对话框会出现。
-
点击数字格式按钮。
格式单元格对话框出现。
-
点击类别下的数字。
-
在小数位框中输入 2 并点击确定。
数据透视表值将被格式化为带有两位小数的数字。
-
单击大计总额列的标题。
-
在公式栏中输入平均订单金额。列标题和行标题都将变更为平均订单金额。
Excel Pivot Tables - Updating Data
您已經瞭解了如何使用樞紐分析表總結數據。樞紐分析表所依據的數據可能會定期更新或在事件發生時更新。此外,您還可能需要更改不同的報表的樞紐分析表佈局。
在本章中,您將瞭解更新佈局和/或刷新樞紐分析表中數據的不同方法。
Updating PivotTable Layout
您可以決定是否在您對佈局進行更改時更新您的樞紐分析表,或者是否由單獨的觸發器更新。
正如您之前瞭解的那樣,在樞紐分析表欄位工作窗格中,在底部,您將找到一個用於延遲佈局更新的核取方塊。默認情況下,它未選中,這表示在您對樞紐分析表區域進行更改後,樞紐分析表佈局會立即更新。
檢查選項− Defer Layout Update 。
它旁邊的更新按鈕將啟用。如果您對樞紐分析表區域進行任何更改,則只有在您單擊更新按鈕後才會反映這些更改。
Refreshing PivotTable Data
當樞紐分析表中的數據在其源中發生更改時,通過刷新樞紐分析表可以在樞紐分析表中反映相同的內容。
-
Click on the PivotTable.
-
单击功能区中的“分析”选项卡。
-
單擊數據組中的刷新。
在下拉列表中有不同的選項來刷新數據−
-
Refresh − 從連接到活動單元的源獲取最新數據。
-
Refresh All − 通過刷新工作簿中的所有來源來獲取最新數據。
-
Connection Properties − 设置工作簿连接的刷新属性。
Changing the Source Data of a PivotTable
您可以更改数据透视表的源数据范围。例如,您可以扩展源数据以包含更多行数据。
但是,如果源数据已大幅更改,例如有更多或更少的列,请考虑创建一个新的数据透视表。
-
单击数据透视表。 PIVOTTABLE TOOLS 随即显示在功能区中。
-
Click the ANALYZE tab.
-
单击“数据”组中的“更改数据源”。
从下拉列表中选择“更改数据源”。
“更改数据透视表数据源”对话框随即显示,当前数据源将突出显示。
在“选择表或范围”下,选择要包含在表/范围框中的表或范围。单击“确定”。
数据透视表的数据源将更改为所选表/数据范围。
Changing to External Data Source
如果要更改外部数据透视表的数据源,最好创建一个新的数据透视表。但是,如果外部数据源的位置发生更改,例如,SQL Server 数据库名称相同,但它已移至另一个服务器,或者 Access 数据库已移至另一个网络共享,则可以更改当前数据连接以反映同样的情况。
-
Click on the PivotTable.
-
单击功能区中的“分析”选项卡。
-
单击“数据”组中的 Change Data Source 。 Change PivotTable Data Source 对话框随即显示。
-
单击 Choose Connection 按钮。
Existing Connections 对话框随即显示。
-
在“显示”框中选择“所有连接”。工作簿中的所有连接都将显示出来。
-
单击 Browse for More 按钮。
Select Data Source 窗口随即显示。
-
单击“新建数据源”按钮。
-
按照数据连接向导步骤进行操作。
如果数据源位于另一个 Excel 工作簿中,请执行以下操作:
-
单击“文件名”框。
-
选择工作簿文件名。
Excel Pivot Tables - Reports
数据透视表的主要作用是报告。在创建数据透视表,通过整理其行和列中的字段来探索数据后,即可向广泛受众展示数据。通过筛选器、不同的汇总和重点关注特定数据,可以根据单个数据透视表生成多个所需报告。
由于数据透视表报告具有交互性,因此可快速进行必要的更改,以在报告时突出显示具体结果,例如数据趋势、数据汇总等。还可以向收件人提供报表筛选器、切片器、时间轴、数据透视图表等可视提示,以便他们可视化所需详情。
在此章节中,将学习不同方法,以通过可视提示美化数据透视表报告,以便快速探索数据。
Hierarchies
已了解如何嵌套字段以形成层次结构,在本教程的“在数据透视表中嵌套”章节中。还学习了如何在“使用数据透视表工具”章节中对数据透视表中的数据进行分组/取消分组。将示范几个示例,展示如何利用层次结构生成交互式数据透视表报告。
如果数据中的字段具有内置结构,例如:年-季度-月,则嵌套字段以形成层次结构将使你能够快速展开/折叠字段,以查看所需级别的汇总值。
例如,假设有 2015-16 财年的销售数据,地区包括东部、北部、南部和西部,如下所示。
创建如下所示的数据透视表。
正如所见,这是一种使用嵌套字段作为层次结构报告数据的全面方法。如果仅想在季度级别显示结果,则可以快速折叠“季度”字段。
假设数据中存在“日期”字段,如下所示。
在这种情况下,可通过以下步骤按“日期”字段对数据分组:
创建数据透视表。
如你所见,此数据透视表不利于突出重要数据。
-
按日期字段对数据透视表分组。(在本指南的“使用数据透视表工具探索数据”一章中,你已经学习了分组功能。)
-
将销售人员字段放入筛选器区域。
-
将列标签筛选为东区。
Report Filter
假设你要分别针对每个销售人员生成报告。可按以下步骤操作:
-
确保筛选器区域中有销售人员字段。
-
Click on the PivotTable.
-
单击功能区中的“分析”选项卡。
-
单击数据透视表组中选项旁边的箭头。
-
从下拉列表中选择显示报告筛选器页面。
Show Report Filter Pages 对话框随即显示。选择字段销售人员,然后单击确定。
将为销售人员字段的每个值创建一个独立的工作表,其中数据透视表筛选至该值。
工作表将以字段值命名,值显示在工作表的选项卡上。
Slicers
数据透视表中的另一复杂功能是切片器,可用作可视化筛选字段。
-
Click on the PivotTable.
-
Click the ANALYZE tab.
-
单击筛选组中的插入切片器。
-
在插入切片器对话框中单击 Order Date, Quarters and Years 。将创建三个切片器:订单日期、季度和年份。
-
调整切片器大小,为切片器上的按钮添加更多列。
-
还要为销售人员和地区字段创建切片器。
-
选择切片器样式,以便将日期字段组合为一种颜色,另外两个字段获取不同的颜色。
-
Deselect Gridlines.
如你所见,你不仅有一个交互式报告,还拥有一个吸引人的报告,可以轻松理解。
Timeline in PivotTable
如果透视表中包含“日期”字段,插入“时间轴”也是生成美观报表的一种选择。
-
在“行”区域创建包含业务员的透视表,在“列”区域创建包含区域的透视表。
-
插入“订单日期”字段的时间轴。
-
筛选时间轴以显示 2015 年 11 月至 2016 年 3 月的 5 个月数据。
DESIGN Commands
功能区上的 PIVOTTABLE TOOLS - DESIGN 命令提供格式化透视表的选项,包括以下内容:
-
Layout
-
PivotTable Style Options
-
PivotTable Styles
Layout
根据以下内容,透视表布局可以使用您的首选项:
-
Subtotals
-
Grand Totals
-
Report Layout
-
Blank Rows
PivotTable Layout – Subtotals
可以选择是否显示 Subtotals 。默认情况下,子总数显示在组的顶部。
当您观察到高亮显示的组——“东部”时,子总数位于组的顶部。您可以按照以下步骤更改子总数的位置:
-
Click on the PivotTable.
-
单击功能区上的设计选项卡。
-
单击“布局选项”组中的“子总数”。
-
单击“在组底部显示所有子总数”。
子总数现在将显示在每个组的底部。
如果您不必报告子总数,则可以选择 - 不显示子总数。
Grand Totals
可以选择显示或不显示“合计”。有四种可能的组合:
-
关闭行和列
-
打开行和列
-
On for Rows Only
-
On for Columns Only
默认情况下,是第二个组合——打开行和列。
Report Layout
您可以从若干报表布局中选择最适合您数据的布局。
-
Compact Form.
-
Outline Form.
-
Tabular Form.
如果发生多次出现,还可以选择是否重复所有项目标签。
默认的报表布局是你熟悉的紧凑格式。
Compact Form
紧凑格式优化了数据透视表的可读性。其他两个格式也会显示字段标题。
单击大纲格式中的 Show 。
单击表格格式中的 Show 。
考虑以下数据透视表布局,其中字段“月份”嵌套在字段“区域”下 −
正如你所观察到的,“月份”标签是重复的,这是默认设置。
单击“不重复项目标签”。“月份”标签将仅显示一次,数据透视表看起来很清晰。