Excel Pivot Tables 简明教程

Excel Pivot Tables - Quick Guide

Excel Pivot Tables - Overview

数据透视表是一个强大的工具,你可以使用它来切片和处理数据。你可以使用动态更改的紧凑型表格来追踪和分析数以千计的数据点,以便帮助你发现数据的不同视角。它是一个简单易用,却又强大的工具。

数据透视表的主要功能如下 −

  1. 创建数据透视表极其简单快捷

  2. 通过简单地拖动字段、对数据进行排序和筛选以及不同的计算,可以立即启用数据的轮换。

  3. 随着你对数据的深入了解,获得适合的数据表示形式。

  4. 能够快速创建报表。

  5. 在几秒钟内从同一个数据透视表生成多个报表。

  6. 提供交互式报表以与受众同步。

在本教程中,你将详细了解这些数据透视表功能以及示例。当你完成本教程时,你将对数据透视表功能有足够的了解,可以开始探索、分析和根据要求报告数据。

Creating a PivotTable

你可以从数据范围或 Excel 表格中创建数据透视表。如果你知道自己要找什么,则可以从一个空白的数据透视表开始填写详细信息。你还可以利用 Excel 推荐的数据透视表,它可以为你提供最适合汇总数据的透视表布局。

你将在章节 - 从表格或范围创建数据透视表中学习如何从数据范围或 Excel 表中创建数据透视表。

Excel 为你提供了一种更强大的方式,可以从多个表格、不同的数据源和外部数据源创建数据透视表。它被命名为 PowerPivot,它在其被称为数据模型的数据库上工作。你将在本教程库中的其他教程中学习这些 Excel 强大工具。

在你冒险使用强大工具之前,你需要先了解本教程中解释的普通数据透视表。

PivotTable Layout - Fields and Areas

数据透视表布局取决于为报表选择的字段以及如何将它们排列在区域中。只需拖动字段即可进行选择和排列。拖动字段时,数据透视表布局将保持更改,并且在几秒钟内发生。

您将在章节数据透视表字段和数据透视表区域中了解数据透视表字段和区域。

Exploring Data with PivotTable

使用数据透视表的首要目标通常是浏览数据以提取重要且必需的信息。您可以执行一些操作,包括排序、筛选、嵌套、折叠和展开、分组和取消分组等。

您将在“使用数据透视表浏览数据”一章中概述这些选项。

Summarizing Values

一旦通过不同的浏览技术整理了所需数据,您要采取的下一步就是汇总数据。Excel 为您提供了各种计算类型,您可以根据适用性和要求应用这些类型。您还可以切换不同的计算类型并在几秒钟内查看结果。

您将在“按不同计算类型汇总值”一章中学习如何在数据透视表上应用计算类型。

Updating a PivotTable

浏览并汇总数据后,如果源数据得到更新,您无需重复此练习。您可以刷新数据透视表,使其反映源数据中的更改。

您将在“更新数据透视表”一章中学习刷新数据的各种方式。

PivotTable Reports

使用数据透视表探索并汇总数据之后,您会将其作为报表显示。数据透视表报表本质上是交互式的,即使是不熟悉 Excel 的人也能够直观地使用它。由于其固有的动态特性,它们将使您能够快速更改报表透视图,以显示所需の詳細级别或关注听众感兴趣的具体项目。

此外,您可以根据具体情况将数据透视表报表构建为独立演示文稿或作为广泛报表的一个组成部分。您将在“数据透视表报表”一章中学习使用数据透视表进行报表的多种方法。

Excel Pivot Tables - Creation

您可以创建数据透视表,方法是从数据范围或 Excel 表中创建。在这两种情况下,数据的首行应包含列标题。

如果您确定要包含在数据透视表中的字段和所需的布局,则可以使用空数据透视表开始并构建数据透视表。

如果您不确定哪个数据透视表布局最适合您的数据,您可以使用 Excel 中的“推荐数据透视表”命令查看根据您的数据定制的数据透视表,并选择您喜欢的数据透视表。

Creating a PivotTable from a Data Range

考虑以下包含每个销售人员在每个地区和 1 月、2 月和 3 月的销售数据的数据范围 −

creating pivottable

要从这个数据范围创建数据透视表,请执行以下操作 −

  1. 确保首行包含标题。您需要标题,因为它们将成为数据透视表中的字段名。

  2. 将数据范围命名为 SalesData_Range。

  3. 单击数据范围-SalesData_Range。

  4. 单击功能区上的插入选项卡。

单击“表格”组中的“数据透视表”。出现 Create PivotTable 对话框。

tables group

在“创建数据透视表”对话框中,在 Choose the data that you want to analyze 下,您可以从当前工作簿中选择“表”或“范围”,也可以使用外部数据源。

当您从数据范围创建一个数据透视表时,请从对话框中选择以下内容-

  1. 选择 Select a table or range

  2. 在“表/范围”框中,键入范围名称-SalesData_Range。

  3. 在“选择放置数据透视表报表的位置”下选择“新建工作表”,然后单击“确定”。

通过将此数据范围添加到数据模型,您可以选择分析多个表。您可以在 Excel PowerPivot 教程中了解如何分析多个表、使用数据模型以及如何使用外部数据源创建数据透视表。

range name

新的工作表插入到工作簿中。新的工作表包含一个空数据透视表。命名工作表 - Range-PivotTable。

range pivottable

正如您所观察到的, PivotTable Fields 列表出现在工作表的右侧,其中包含数据范围中列的标题名称。此外,在功能区上,数据透视表工具 - 分析和设计出现。

Adding Fields to the PivotTable

您将在本教程后面的章节中详细了解数据透视表字段和区域。现在,请观察将字段添加到数据透视表的步骤。

假设您想按销售人员和 1 月、2 月和 3 月总结订单金额。您可以按照以下几个简单的步骤进行操作 -

  1. 单击数据透视表字段列表中的销售人员字段,并将其拖动到行区域。

  2. 单击数据透视表字段列表中的月份字段,并将其也拖动到行区域。

  3. 单击“订单金额”,并将其拖动到“∑ 值”区域。

如下所示,您的第一个数据透视表已准备就绪

first pivottable

请注意,数据透视表中显示两列,一列包含您选择的行标签,即销售人员和月份,另一列包含订单金额的总和。除了每位销售人员按月份计算的订单金额总和外,您还将获得代表该人员总销售额的子总计。如果您向下滚动工作表,您会发现最后一行是大总计,代表总销售额。

随着您本教程的进展,您将了解有关根据需要制作数据透视表的更多信息。

Creating a PivotTable from a Table

考虑以下包含与上一部分中相同的销售数据的 Excel 表 -

excel table

Excel 表本质上将具有一个名称,并且列将具有标题,这是创建数据透视表的必要条件。假设表名称为 SalesData_Table。

要从此 Excel 表创建数据透视表,请执行以下操作 -

  1. 单击表格 - SalesData_Table。

  2. 单击功能区上的插入选项卡。

  3. 单击“表格”组中的“数据透视表”。出现 Create PivotTable 对话框。

insert tab
  1. 单击选择表或范围。

  2. 在表/范围框中,键入表名 - SalesData_Table。

  3. Choose where you want the PivotTable report to be placed 下选择新建工作表。单击确定。

sales data table

新的工作表插入到工作簿中。新的工作表包含一个空数据透视表。命名工作表 - Table-PivotTable。工作表 - Table-PivotTable 类似于您在较早部分的数据范围案例中获得的工作表。

正如您在本章前面 - 将字段添加到数据透视表的部分中看到的那样,您可以将字段添加到数据透视表。

如果你不熟悉 Excel 数据透视表,或者不知道哪些字段将生成有意义的报表,可使用 Excel 中的“推荐数据透视表”命令。推荐数据透视表提供所有可能的数据报表及相关布局。换句话说,显示的选项是根据你的数据自定义的数据透视表。

要使用“推荐数据透视表”从 Excel 表格 SalesData-Table 创建数据透视表,请按以下步骤操作:

  1. 单击表格 SalesData-Table。

  2. Click the INSERT tab.

  3. 单击“表格”组中的“推荐数据透视表”。“推荐数据透视表”对话框随即出现。

recommended pivottables

“推荐数据透视表”对话框中将显示适用于你数据的可能自定义数据透视表。

  1. 单击每个数据透视表选项,以在右侧查看预览。

  2. 单击数据透视表“按销售人员和月份汇总订购金额”,然后单击“确定”。

你将获得右侧预览。

preview

选定的数据透视表将显示在工作簿的新工作表中。

select pivottable

你可以看到数据透视表字段——销售人员、地区、订单金额和月份已选定。其中,区域和销售人员在行区域中,月份在列区域中,订单金额和在值区域中。

数据透视表按地区、按销售人员和按月份汇总数据。子总数显示在每个地区、每个销售人员和每个月中。

Excel Pivot Tables - Fields

数据透视表字段是一个与数据透视表关联的任务窗格。数据透视表字段任务窗格包括字段和区域。默认情况下,任务窗格显示在窗口右侧,其中字段显示在区域上方。

字段表示你的数据(范围或 Excel 表格)中的列,并会有复选框。选定的字段显示在报表中。区域代表报表的布局和报表中包含的计算。

在任务窗格底部,你会找到一个选项——延迟布局更新,旁边有一个更新按钮。

  1. 默认情况下,此选项未选定,并且你在字段选择或布局选项中进行的任何更改都会立即反映在数据透视表中。

  2. 如果你选中此选项,则在单击按钮之前不会更新你的选择中的更改。

update

在本章中,你将了解有关字段的详细信息。在下一章中,你将了解有关区域的详细信息。

PivotTable Fields Task Pane

你可以在包含数据透视表的工作表中找到数据透视表字段任务窗格。要查看数据透视表字段任务窗格,请单击数据透视表。如果未显示数据透视表字段任务窗格,请检查“功能区”中的以下内容:

  1. 单击功能区上数据透视表工具下的“分析”选项卡。

  2. 在“显示”组中检查“字段列表”是否已被选中(即突出显示)。

  3. 如果“字段列表”未被选中,请单击它。

透视表字段任务窗格将显示在窗口右侧,标题为“透视表字段”。

pivottables task pane

Moving PivotTable Fields Task Pane

在透视表任务窗格的“透视表字段”标题的右侧,您将找到按钮 。该按钮表示“任务窗格选项”。单击按钮 。“任务窗格选项:移动、大小和关闭”将显示在下拉列表中。

moving pivottables task pane

您可以如下所示将透视表任务窗格移动到窗口中的任意位置:

  1. 在下拉列表中单击“移动”。按钮将出现在任务窗格中。

  2. 单击图标并将窗格拖动到想要放置的位置。您可以将任务窗格放置在透视表旁边,如下所示。

labels

您可以将任务窗格放置在窗口的左侧,如下所示。

taskpane

Resizing PivotTable Fields Task Pane

您可以调整透视表任务窗格的大小,即增加/减少任务窗格的长度和/或宽度,如下所示:

  1. 单击“任务窗格选项”,即标题“透视表字段”的右侧。

  2. 在下拉列表中单击“大小”。

  3. 使用符号 增加/减少任务窗格的宽度。

  4. 使用符号 增加/减少任务窗格的宽度。

在 ∑ 值区域中,为了使订单金额的总和完全可见,您可以如下所示调整任务窗格的大小。

resizing

PivotTable Fields

透视表字段列表包括与您的工作簿关联的所有表格和对应的字段。通过在透视表字段列表中选择字段,您可以创建透视表。

包含复选框的表格和对应的字段反映了您的透视表数据。由于您可以随机选中/取消选中字段,您可以快速更改透视表,突出显示想要报告或显示的摘要数据。

pivottable fields

您可以观察到,如果只有一个表,表名将不会显示在透视表字段列表中。只显示带有复选框的字段。

您将在字段列表上方找到操作“选择要添加到报告中的字段”。在右侧,您将找到按钮 代表工具。

  1. 单击“工具”按钮。

在下拉列表中,您会找到以下内容 -

  1. 字段和区域有五个不同的布局选项。

  2. 字段列表中有两个为字段排序的选项 - 升序排列。按照数据源顺序排序。

tools

正如您在上面的字段列表中观察到的,排序方式是默认的 - 例如,按照数据源顺序。也就是说,这是表中列出现的顺序。

通常,您可以保留默认顺序。然而,有时候,您可能会遇到表中有很多字段并且可能不了解它们。在这种情况下,您可以按字母顺序对字段进行排序,方法是单击工具下拉列表中的 - 升序排列。然后,数据透视表字段列表如下所示 -

data source order

Excel Pivot Tables - Areas

数据透视表区域是数据透视表字段任务窗格的一部分。通过排列区域的所选字段,您会得到不同的数据透视表布局。既然您可以简单地在各个区域之间拖动字段,这样您就能快速切换跨不同布局,总结数据,而且是以您想要的方式。

您已经在本教程较早的数据透视表字段章节中了解了数据透视表字段任务窗格。在本章节,您将学习到数据透视表区域。

有四个可用的数据透视表区域 -

  1. ROWS.

  2. COLUMNS.

  3. FILTERS.

  4. ∑ VALUES(解读为求和)。

pivottable areas

消息 Drag fields between areas below 显示在区域上方。

使用数据透视表区域,您可以选择 -

  1. 哪些字段显示为行(行区域)。

  2. 哪些字段显示为列(列区域)。

  3. 如何总结您的数据(求和区域)。

  4. 任何字段的过滤器(过滤器区域)。

您只要在这些区域之间拖动字段,观察数据透视表布局的变化即可。

ROWS

如果您通过选中复选框在数据透视表字段列表中选择字段,所有非数字字段将自动添加到行区域,以您选择的顺序。

您可以选择将字段拖动到行区域。放在行区域的字段将显示在数据透视表中,而行标签则是所选字段的值。

例如,请考虑销售数据表。

  1. 将字段销售人员拖动到“行”区域。

  2. 将 Month 域拖到 ROWS 区域。

您的数据透视表会随一行包括行标签——销售人员和月份以及一行作为总计,如下所示。

rows

COLUMNS

您可以将域拖至 COLUMNS 区域。

放置在 COLUMNS 区域中的域在数据透视表中显示为列,其中列标签是已选域的值。

将 Region 域拖到 COLUMNS 区域。您的数据透视表会显示第一行包含行标签——销售人员和月份的下一列包括列标签——区域及最后一行总计,如下所示。

column
  1. 将 Month 域从 ROWS 拖到 COLUMNS。

  2. 将 Region 域从 COLUMNS 拖到 ROWS。您的数据透视布局将发生变化,如下所示。

columns row

您能看到现在只有五列——第一列带行标签,三列带列标签及最后一列带总计。

行和列的数量基于您在这些域中拥有的值的数量。

∑ VALUES

数据透视表的主要用途是汇总值。因此,通过将您要按照这些值汇总数据放置在 ∑ VALUES 区域中,您将得到汇总表。

  1. 将 Order Amount 域拖到 ∑ VALUES

  2. 将 Region 域拖到 ROWS 中 Salesperson 域上方。此步骤是为了更改嵌套顺序。您将在本教程中的“数据透视表中的嵌套”章节中学习嵌套。

sigma values

正如您看到的,数据已按区域、销售人员和月份汇总。您会看到按月划分的每个区域的子计。您还可以在 Grand Total 行中看到按月划分的总计,在 Grand Total 列中看到按区域划分的总计。

FILTERS

Filters 区域用于将筛选器放置到数据透视表中。假设您仅想对选定区域分别显示结果。

将 Region 域从 ROWS 区域拖到 FILTERS 区域。筛选器 Region 将放置到数据透视表上。如果您没有数据透视表上方的空行,则数据透视表会向下推入,以便插入筛选器上方的行为。

filters

正如您看到的,(ALL) 默认情况下出现在筛选器中,并且数据透视表显示所有区域值的 data。

  1. 点击筛选器右边的箭头。

  2. 选中框——选择多个项目。

all

复选框将会出现在下拉列表中的所有选项中。默认情况下,所有框全部选中。

  1. 选择框 - 北方和南方。

  2. 清除其他框。单击确定。

check boxes

数据透视表会发生更改来反映筛选出的数据。

reflect

你可以观察到,筛选器显示 (Multiple Items)。因此,当某人看数据透视表时,看不出来哪些值是筛选过的。

Excel 提供了你另一个工具,称作切片器,用来更有效地进行筛选。你将在教程的后面章节详细了解如何在数据透视表中筛选数据。

Excel Pivot Tables - Exploring Data

Excel 数据透视表允许你探索和提取 Excel 表格或数据范围内的重要数据。有几种方法去做,你可以选择最适合你数据的。此外,在你探索数据时,你可以立即看到不同组合,当改变你的选择去挑选数据值时。

你可以在数据透视表中进行以下操作:

  1. Sort the data.

  2. Filter the data.

  3. Nest the PivotTable fields.

  4. 展开或折叠字段。

  5. 给字段值分组或取消分组。

Sorting and Filtering Data

你可以按照字段升序或降序对数据透视表中的数据进行排序。你还可以按照小计从最大到最小的值或者从最小到最大的值进行排序。你还可以设定排序选项。你将在此教程的章节中详细学习这些内容 - 在数据透视表中通过排序数据。

你可以筛选数据透视表中的数据来专注于一些特定的数据。你在数据透视表中有几个筛选选项,你将在教程中的章节 - 在数据透视表中筛选数据中学习这些选项。你可以用切片器筛选,你将在教程中的章节 - 用切片器筛选了解这些知识。

Nesting, Expanding and Collapsing Fields

你可以将字段嵌套在数据透视表中以显示层次结构(如果和你的数据相关)。你将在教程的章节 - 在数据透视表中嵌套中学习这个知识。

在数据透视表中有嵌套字段时,你可以展开或折叠字段的值。你将在教程的章节 - 用数据透视表工具探索数据中学习这些知识。

Grouping and Ungrouping Field Values

你可以在数据透视表中对特定字段的值分组或取消分组。你将在教程的章节 - 用数据透视表工具探索数据中学习这个知识。

Excel Pivot Tables - Sorting Data

你可以对数据透视表中的数据进行排序,这样可以让你更容易找到你想分析的项。你可以按照从最小值到最大值或从最大值到最小值或者你选择的任何其它自定义顺序来对数据进行排序。

思考以下数据透视表,你在其中区域明智、销售人员明智和月份明智地总结了销售数据。

sum of order amount

Sorting on Fields

你可以在上面的数据透视表中对位于行或列中的字段进行排序 - 区域、销售人员和月份。

若要使用字段销售人员对数据透视表进行排序,请按下列步骤进行:

  1. 单击行标签中的箭头。

  2. 从下拉列表中从“选择字段”框中选择“销售人员”。

sorting

显示以下排序选项 −

  1. Sort A to Z.

  2. Sort Z to A.

  3. More Sort Options.

此外,默认情况下,按照升序对销售人员字段进行排序。单击 Sort Z to A 。销售人员字段将按降序排序。

sort z to a

同样,您可以单击列标签中的箭头来按字段排序 – 月份。

Sorting on Subtotals

假设要根据总订单数量对数据透视表进行排序 – 每个区域从最高到最低。也就是说,你要根据子总数对数据透视表进行排序。

sorting on subtotals

您可以看到,子总数没有箭头。您仍然可以按如下方式对子总数对数据透视表进行排序 −

  1. 右键单击“总计”列中任何一个销售人员的子总数。

  2. 从下拉列表中选择 Sort

  3. 另一个下拉列表将显示排序选项 – 从最小到最大排序、从最大到最小排序和更多排序选项。选择从最大到最小排序。

grand total

在每个区域中,按从最高值到最低值对“总计”列中的子总数进行排序。

click sort

同样,如果您要按区域对子总数对数据透视表进行排序,请执行以下操作 −

  1. 右键单击“总计”列中任何一个区域的子总数。

  2. 单击下拉列表中的“排序”。

  3. 在第二个下拉列表中单击“从最大到最小排序”。数据透视表将按区域对子总数进行排序。

total amount

正如您所观察到的,南方拥有最高的订单金额,而北方拥有最低的订单金额。

您还可以按月总金额对数据透视表进行排序,如下所示 −

  1. 右键单击“总计”行中的任何一个子总数。

  2. 从下拉列表中选择“排序”。

  3. 从第二个下拉列表中选择“从最大到最小排序”。

数据透视表按总金额按月排序。

more sort options

您可以观察到,2 月的订单金额最高,而 3 月的订单金额最低。

More Sort Options

假设您想按 1 月的总金额按区域对数据透视表进行排序。

  1. 单击“行标签”中的箭头。

  2. 从下拉列表中选择“更多排序选项”。将显示 Sort (Region) 对话框。

region

如您所见,在“摘要”下,当前的排序顺序为升序按区域排序。升序 (A 到 Z) 按排序选项选中。在该框下方,显示 Region

  1. 单击包含区域的框。

  2. 单击订单金额之和。

more options

单击 More Options 按钮。将出现 More Sort Options (Region) 对话框。

values in selected column

如您所见,“排序依据”下选择总计。在“摘要”下,当前的排序顺序为 Sort Region by Sum of Order Amount 升序。

  1. 单击“排序依据”下的 Values in selected column:

  2. 在该框下方,键入 B5。

ascending order

如您所见,“摘要”下,当前的排序顺序如下所述 −

  1. 使用此列中的值按 Sum of Order Amount 升序按区域排序:1 月份。单击“确定”。

  2. 将出现“排序(区域)”对话框。在排序选项下选择降序 (Z 到 A) 按:。

under summary

在“摘要”下,当前的排序顺序如下所述 −

按订单金额之和降序按区域排序,使用此列中的值:1 月份。单击“确定”。将按 1 月份的值对数据透视表按区域进行排序。

sorting data manually

如您所见,在 1 月份,西部地区的订单金额最高,而北部的订单金额最低。

Sorting Data Manually

在数据透视表中,数据将根据您选择的排序选项自动排序。这称为自动排序。

将光标放置在行标签或列标签中的箭头处。

select manual

AutoSort 出现,显示数据透视表中每个字段的当前排序顺序。现在,假设你要按区域字段对字段进行排序——东、西、北和南。你可以按照以下方式手动完成:

  1. 单击行标签中的箭头。

  2. 从下拉列表中在“选择字段”框中选择“区域”。

  3. 单击 More Sort Options 。将出现“排序(区域)”对话框。

  4. 选择“手动”(你可以拖动项目以重新排列它们)。

  5. Click OK.

select region

在“摘要”下,当前排序顺序以“将“区域”字段的项目拖动到此处以按任意顺序显示它们”给出。

单击“东”,然后将其拖动到顶部。在你拖动“东”时,一系列横向绿线会出现在整行中。

click on east

用“区域”字段的其他项目重复拖动,直到获得所需排列。

repeat

您可以观察到以下内容 −

  1. 嵌套字段的项目——销售人员也会与相应的区域字段项目一起移动。此外,其他列中的值也会相应移动。

  2. 如果你将光标放置在行标签或列标签中的箭头,AutoSort 会出现,仅显示销售人员和月份字段的当前排序顺序。由于你已手动对区域字段进行排序,因此它不会显示在 AutoSort 中。

Note ——你无法对数据透视表字段列表中位于“∑ VALUES”区域的字段项目进行此手动拖动。因此,你无法拖动此数据透视表中的“订单金额总和”值。

Setting Sort Options

在上一节中,你学习了如何将字段的排序选项设置为手动。你可以设置如下所示的更多排序选项:

  1. 单击行标签中的箭头。

  2. 在“选择字段”框中选择“区域”。

  3. 单击“更多排序选项”。将出现“排序(区域)”对话框。

  4. 单击“更多选项”按钮。

将出现“更多排序选项(区域)”对话框。你可以在此对话框中设置更多排序选项。

click ok

在“自动排序”下,你可以选中或取消选中该框——每次更新报表时自动排序,以便在数据透视表数据更新时允许或停止自动排序。

  1. 取消选中该框——每次更新报表时 Sort automatically

如今,“第一个键的排序顺序”选项变得可用。你可以使用此选项选择你想要使用的自定义的顺序。

  1. 点击“第一个键的排序顺序”下面的框。

click the box

正如你能观察到的,在一周中某天和一年的某月在下拉列表中提供了自定义列表。你可以使用任何这些,或者你可以使用你自己的自定义列表,例如高、中、低或按字母顺序排列的 S、M、L、XL 尺寸列表。

你可以在功能区的“文件”选项卡中创建自定义列表。文件 → 选项。在 Excel 选项对话框中,单击高级,并浏览至常规。你会在“创建用于排序和填充序列的列表”旁边找到“编辑自定义列表”按钮。

advanced

要注意的是,当你在数据透视表中更新(刷新)数据时,不会保留自定义列表的排序顺序。

在“排序依据”下方,你可以单击选定列中的 Grand Total or Values 来按这些值排序。当你将排序设置为“手动”时,此选项不可用。

Points to consider while sorting PivotTables

当你对数据透视表中的数据进行排序时,记住以下几点 −

  1. 带前导空格的数据将影响排序结果。在对数据进行排序之前,请删除所有前导空格。

  2. 你无法对区分大小写的文本项进行排序。

  3. 你无法按特定格式对数据进行排序,例如单元格或字体颜色。

  4. 你无法按条件格式指示符(例如图标集)对数据进行排序。

Excel Pivot Tables - Filtering Data

你可能必须对数据透视表数据的子集进行深入分析。这可能是因为你拥有大量数据,并且你的重点是放在更少部分的数据上,或者不管数据的规模大小,你的重点是放在某些特定数据上。你可以基于一个或多个字段的子集的值来筛选数据透视表中的数据。以下是如何做到这一点的几种方法 −

  1. Filtering using Slicers.

  2. Filtering using Report Filters.

  3. Filtering data manually.

  4. Filtering using Label Filters.

  5. Filtering using Value Filters.

  6. Filtering using Date Filters.

  7. 使用前 10 名筛选器进行筛选。

  8. Filtering using Timeline.

你将在下一章学习如何使用切片器进行筛选数据。你将了解在本章中提到的其他筛选方法。

考虑以下数据透视表,其中按地区、销售人员和月份汇总了销售数据。

slicers

Report Filters

您可以为某个字段分配筛选器,以便您可以根据该字段的值动态更改数据透视表。

将区域从行拖动到数据透视表区域中的筛选器。

report filters

带有“区域”标签的筛选器显示在数据透视表的上方(如果你没有将空行放在数据透视表上方,数据透视表将被下推以腾出空间供筛选器使用)。

space filter

你会观察到

  1. 销售人员值显示在行中。

  2. 月值显示在列中。

  3. 区域筛选器显示在顶部,默认选择为全部。

  4. 汇总值为订单金额之和。以销售人员为基础的订单金额之和显示在“总计”列中。以月份为基础的订单金额之和显示在“总计”行中。

  5. 单击“筛选器区域”右侧框中的箭头。

显示带“区域”字段值的下拉列表。选中 Select Multiple Items 框。

select multiple items

默认情况下,所有框都处于选中状态。取消选中 All 框。所有框都将取消选中。

然后选中框 - 南部和西部,然后单击确定。

uncheck box

仅会汇总与南部和西部区域相关的数据。

data pertaining

“筛选区域 - (多项)”旁边的单元格中会显示已选择多项,这表示您已选择多项。但根据显示的报表无法得知选择了几项或所选择的是哪些项。在这种情况下,使用切片器是更好的筛选选项。

Manual Filtering

您还可以手动筛选数据透视表来挑选字段的值。这可以通过单击行标签或列标签单元格中的箭头来实现。

manual filtering

假设您只希望分析 2 月份的数据。您需要根据月份字段筛选值。正如您所观察到的,月份是列标签的一部分。

单击列标签单元格中的箭头。

正如您所观察到的,下拉列表中有一个搜索框,而该框下方是所选字段值(即月份)的列表。所有值的框都被选中,这表明已选择该字段的所有值。

search box
  1. 取消选中值列表顶部的(全部选择)框。

  2. 选中您要显示在数据透视表中的值(在本例中为 2 月)的框,然后单击确定。

check box

数据透视表仅显示与所选月份字段值(即 2 月)相关的值。您会观察到,筛选箭头变为图标,以表示已应用筛选器。将光标置于图标上。

month field value

您会观察到,显示的是手动筛选器已应用到字段“月份”。

如果您想更改筛选器选择值,请执行以下操作:

  1. Click the icon.

  2. 选中/取消选中值得框。

如果列表中没有显示字段的所有值,则拖动下拉列表右下角的控制手柄将其扩大。或者,如果您知道该值,则在搜索框中输入它。

假设您希望对上述经过筛选的数据透视表应用另一个筛选器。例如,您想显示沃尔特斯·克里斯在 2 月份的数据。您需要通过为销售人员字段添加另一个筛选器来优化筛选操作。正如您所观察到的,销售人员是行标签的一部分。

  1. 单击行标签单元格中的箭头。

row labels cell

将显示字段值(区域)的列表。这是因为区域在嵌套顺序中位于销售人员的外层。您还有另一个选项 - 选择字段。单击“选择字段”框。

  1. 从下拉列表中单击“销售人员”。将显示字段值(销售人员)的列表。

  2. 取消选中(全部选择),然后选中“沃尔特斯·克里斯”。

  3. Click OK.

check walters

数据透视表仅显示与已选择的“月份”字段值(二月)和“销售人员”字段值(Walters, Chris)相关的值。

行标签旁边的筛选箭头也会更改为图标,以指示已经应用了筛选。将光标放在行标签或列标签上的图标上。

column labels

会显示一个文本框,指示已经在“月份”和“销售人员”字段上应用了手动筛选。

因此,您可以根据任意数量的字段和任意数量的值手动筛选数据透视表。

Filtering by Text

如果字段中包含文本内容,则可以按文本筛选数据透视表,如果相应字段标签是基于文本的。例如,考虑以下员工数据。

employee data

数据包含员工的详细信息(员工 ID、头衔、出生日期、婚姻状况、性别和雇用日期)。此外,数据还包含员工的经理级别(级别 0-4)。

假设您必须对向给定员工报告的员工数进行分析,按头衔划分。您可以创建如下所示的数据透视表。

hiredate

您可能想知道有多少个头衔中带有“经理”的员工有向自己报告的员工。因标签“头衔”是基于文本的,因此您可以按如下方式对“头衔”字段应用标签筛选:

  1. 单击行标签单元格中的箭头。

  2. 从下拉列表中,在“选择字段”框中选择“头衔”。

  3. Click on Label Filters.

  4. 在第二个下拉列表中,单击“包含”。

manager

“标签筛选(头衔)”对话框会出现。在“包含”旁边框中输入“经理”。单击“确定”。

title

数据透视表将筛选到包含“经理”的头衔值。

  1. Click the icon.

您可以看到显示,指示:

  1. 标签筛选已经应用于字段“头衔”,且

  2. 已应用的标签筛选是什么。

applied label filter

Filtering by Values

您可能想知道向他们报告的员工超过 25 人的员工的头衔。为此,您可以按如下方式对“头衔”字段应用数值筛选:

  1. 单击行标签单元格中的箭头。

  2. 从下拉列表中,在“选择字段”框中选择 Title

  3. Click on Value Filters.

  4. 从第二个下拉列表中,选择“大于或等于”。

select greater

“值筛选(头衔)”对话框会出现。在右侧框中输入“25”。

将筛选数据透视表,显示汇报给他们的员工超过 25 名的员工职称。

employee titles

Filtering by Dates

您可能需要显示 2015-15 财年雇佣的所有员工的数据。您可以使用数据筛选器,方法如下:

  1. 在数据透视表中包含 HireDate 字段。现在,您不需要管理员数据,所以请从数据透视表中删除 ManagerLevel 字段。

managerlevel

现在,您在数据透视表中有了一个日期字段,可以用户日期筛选器。

  1. 单击行标签单元格中的箭头。

  2. 从下拉列表中的选择字段框中选择 HireDate。

  3. Click Date Filters.

  4. 从第二个下拉列表中选择 Between

select between

日期筛选器(HireDate)对话框出现。在两个日期框中输入 4/1/2014 和 3/31/2015。单击确定。

data filter

将筛选数据透视表,仅显示 HireDate 在 2014 年 4 月 1 日至 2015 年 3 月 31 日之间的日期。

display

您可以按如下方式将日期分组为季度:

  1. 右键单击任何日期。 Grouping 对话框出现。

  2. 在从这里开始框中输入 4/1/2014。选中框。

  3. 在结束于框中输入 3/31/2015。选中框。

  4. By 框下方单击季度。

by

日期将在数据透视表中分组为季度。您可以通过将字段 HireDate 从行区域拖到列区域,使表格看起来更紧凑。

您将能够按季度了解在该财政年度雇佣了多少员工。

fiscal year

Filtering Using Top 10 Filter

您可以使用前 10 个筛选器在数据透视表中显示字段前几个或后几个值。

  1. 单击行标签单元格中的箭头。

  2. Click Value Filters.

  3. 在第二个下拉列表中单击前 10 个。

top filter

将出现前 10 个筛选器(职称)对话框。

  1. 在第一个框中,单击“顶部”(你也可以选择“底部”)。

  2. 在第二个框中,输入一个数字,比如 7。

  3. 在第三个框中,你有三个可以进行筛选的选项。单击“项目”按项目数筛选。单击“百分比”按百分比筛选。单击“总和”按总和筛选。

  4. 由于你已按 EmployeeID 计数,因此单击“项目”。

  5. 在第四个框中,单击字段“EmployeeID 计数”。

  6. Click OK.

field count

按 EmployeeID 计数的前七个值将显示在数据透视表中。

seven values

正如你可以看到,在该财政年度中最多雇员的职务是“生产技术人员”,而且其中多数发生在第一季度。

Filtering Using Timeline

如果你的数据透视表有一个日期字段,你就可以使用时间线筛选数据透视表。

根据先前使用的 Employee Data 创建一个数据透视表,并在“创建数据透视表”对话框中将数据添加到数据模型中。

  1. 将字段“名称”拖动到“行”区域。

  2. 将字段“EmployeeID”拖动到“∑ 值”区域,并选择“计数”进行计算。

employeeid
  1. Click on the PivotTable.

  2. Click the INSERT tab.

  3. 在“筛选器”组中,单击“时间线”。“插入时间线”对话框便会弹出。

insert timelines
  1. Check the box HireDate.

  2. 单击“确定”。时间线随即出现在工作表中。

  3. 时间线工具出现在功能区中。

timeline appears

正如你所看到的,“全部期间 - 月”显示在时间线中。

  1. 单击“月”旁边的箭头。

  2. 从下拉列表中选择“季度”。时间线显示便会更改为“全部期间 - 季度”。

select quarters
  1. Click on 2014 Q1.

  2. 按住“Shift”键不放,然后拖到“2014 Q4”。时间线期间已选为“2014 Q1 - Q4”。

  3. 会按此时间线期间筛选数据透视表。

q1

Clearing the Filters

您可能需要不时清除您设置的筛选器,以便在不同组合和数据预测之间切换。您可以通过如下几种方式来实现: -

Clearing all the filters in a PivotTable

您可以一次性清除枢纽表中设置的所有筛选器,如下所示: -

  1. 单击功能区中的【开始】选项卡。

  2. 单击“编辑”组中的“排序和筛选”。

  3. 从下拉列表中选择“清除”。

clearing filters

Clearing a Label, Date or Value Filter

要清除标签、日期或值筛选器,请执行以下操作: -

  1. 单击行标签或列标签中的图标。

  2. 在下拉列表中,从“选择字段”框中单击您想要清除筛选器的 <字段名称>。

  3. 单击下拉列表中出现的“从 <字段名称> 清除筛选器”。

  4. 单击“确定”。将清除特定的筛选器。

select field box

Filtering data using Slicers

使用一个或多个切片器是筛选数据快速有效的方法。可以为想要筛选的每个字段插入切片器。切片器将显示它所代表字段值的按钮。您可以单击切片器的按钮以选择/取消选择字段中的值。

切片器与枢纽表保持可见,因此您将始终了解用于筛选的字段以及在筛选后的枢纽表中显示或隐藏了这些字段中的哪些值。

要了解切片器的用法,请考虑按区域、按月份和按销售人员划分的销售数据示例。假设您拥有包含此数据的以下枢纽表。

usage of slicers

Inserting Slicers

假设您想基于字段 - 区域和月份对该枢纽表进行筛选。

  1. 单击功能区中【PIVOTTABLE 工具】下的【分析】。

  2. 单击“筛选”组中的“插入切片器”。此时将显示“插入切片器”对话框。其中包含数据表中的所有字段。

  3. 勾选区域和月份。

  4. Click OK.

inserting slicers

所有选定字段的切片器都默认选中所有值后显示。切片器工具随即显示在功能区上,用于设定切片器设置、外观和风格。

slicer tools

Filtering with Slicers

您可以看到,每个切片器都包含它所代表字段的所有值,并且这些值以按钮的形式显示。默认情况下,字段的所有值都被选中,因此所有按钮都高亮显示。

假设您只想显示南部和西部区域的枢纽表,以及 2 月和 3 月份。

  1. 单击“区域”切片器中的“南部”。在“区域”切片器中,只有“南部”将高亮显示。

  2. 按住 Ctrl 键并单击区域切片器中的西部。

  3. 单击月切片器中的 2 月。

  4. 按住 Ctrl 键并单击月切片器中的 3 月。

切片器中选定的项目将突出显示。将显示选定项目的汇总值透视表。

filtering with slicers

要添加/删除过滤器中的字段值,请按住 Ctrl 键并单击字段切片器中的那些按钮。

Clearing the Filter in a Slicer

要清除切片器中的筛选器,请单击切片器右上角的 。

clearing filter in slicer

Removing a Slicer

假设你要删除区域字段的切片器。

  1. 右键单击切片器——区域。

  2. 在下拉列表中单击删除“区域”。

removing slicer

Slicer Tools

一旦插入切片器,带选项选项卡的切片器工具就会出现在功能区。若要查看切片器工具,请单击切片器。

tab options

正如你所看到的,在切片器工具——选项选项卡下,你可以更改切片器的外观和感觉,包括:

  1. Slicer Caption

  2. Slicer Settings

  3. Report Connections

  4. Selection Pane

Slicer Caption

你可以在切片器组中找到切片器标题框。切片器标题是显示在切片器上的页眉。默认情况下, Slicer Caption 是它所代表的字段的名称。

  1. 单击区域切片器。

  2. 单击功能区上的选项选项卡。

slicer caption

功能区上的切片器组,在切片器标题框,区域显示为切片器的页眉。它是插入切片器的字段的名称。你可以按照如下方式更改切片器标题:

  1. 单击功能区切片器组中的切片器标题框。

  2. 删除区域。框被清空。

  3. 在框中输入位置并按回车。切片器标题更改为位置,同一位置反映在切片器的页眉中。

slicer group

Note - 你只更改了切片器标题,即页眉。切片器所代表的字段的名称——区域保持原样。

Slicer Settings

你可以使用切片器设置更改切片器的名称、切换切片器标题、选择是否显示切片器页眉以及设置列表的排序和筛选选项——

  1. 单击切片器 - 位置。

  2. 单击功能区的选项卡选项。您可以在功能区的切片器组中找到切片器设置。您也可以在右击切片器时在下拉列表中找到切片器设置。

  3. 单击切片器设置。您便会看到切片器设置对话框。

slicer settings

正如您所看到的,以下内容已针对切片器修复:

  1. Source Name.

  2. 公式中要使用的名称。

您可以针对切片器更改以下项:

  1. Name.

  2. Header – Caption.

  3. Display header.

  4. 切片器上显示的项目的排序和筛选选项。

Report Connections

您可以将不同的数据透视表连接到切片器,只要满足以下条件之一:

  1. 使用相同数据创建了数据透视表。

  2. 一个数据透视表已被复制并粘贴为一个附加的数据透视表。

  3. 使用显示报表筛选器页在不同的工作表上创建了多个数据透视表。

考虑使用相同数据创建的以下数据透视表:

same data
  1. 将顶部数据透视表命名为数据透视表 - 顶部,将底部数据透视表命名为数据透视表 - 底部。

  2. 单击顶部数据透视表。

  3. 为区域字段插入一个切片器。

  4. 在切片器中选择东和北。

top pivottable

观察到只对顶部数据透视表应用了筛选,并没有针对底部数据透视表应用。您可以使用相同的切片器用于两个数据透视表,方法是将其连接到底部数据透视表,如下所示:

  1. 单击切片器 - 区域。切片器工具将出现在功能区上。

  2. 单击功能区上的选项选项卡。

您将在功能区的切片器组中找到报表连接。您也可以在右击切片器时在下拉列表中找到报表连接。

在“切片器”组中,单击 Report Connections

report connections

Report Connections ”对话框随即将出现。已选中“枢纽表格-顶部”复选框,而其他复选框未被选中。同时选中“枢纽表格-底部”复选框,然后单击“确定”。

checked

底部枢纽表格将筛选到选定项(东部和北部)。

selected items

之所以能这样,是因为现在这两个枢纽表格都已连接至切片器。如果您对切片器中的选定内容进行更改,则相同筛选将在两个枢纽表格中显示。

Selection Pane

您可以使用“选择窗格”开启和关闭工作表上切片器的显示。

  1. 单击切片器 - 位置。

  2. 单击功能区上的选项选项卡。

  3. 单击功能区的“排列”组中的“选择窗格”。“选择窗格”随即将出现在窗口右侧。

selection pane

正如您所观察到的,所有切片器的名称均已列在“选择窗格”中。在名称右侧,您可以找到一个可见性符号,表示切片器在工作表上可见。

单击“月份”符号。该符号会更改为符号,表示切片器已隐藏(不可见)。

month

正如您所观察到的,切片器(月份)未显示在工作表上。但是,请记住您尚未移除“月份”切片器,而只是将其隐藏。

  1. 单击“月份”符号。

  2. 该符号会更改为符号,表示切片器现在已可见。

当您关闭或开启切片器的可见性时,该切片器中用于筛选的项的选定内容将保持不变。您还可通过向上/向下拖动的方式更改“选择窗格”中切片器的顺序。

Excel Pivot Tables - Nesting

如果您在任何枢纽表格区域中有多个字段,则该枢纽表格布局取决于您将字段放置在该区域中的顺序,称为嵌套顺序。

如果您了解您的数据如何构建,则可按所需顺序放置这些字段。如果您不确定数据的构建,您可以更改字段顺序,此操作会立即更改枢纽表格的布局。

在本章中,您将了解字段的嵌套顺序,以及如何更改嵌套顺序。

Nesting Order of the Fields

考虑销售数据示例,其中您已按以下顺序放置这些字段:

nesting

正如您所看到的,在行区域中有两个字段——销售人员和区域,它们按此顺序排列。此字段顺序称为嵌套顺序,即首先是销售人员,然后是区域。

在枢纽表格中,行中的值将基于以下顺序显示,如下所示。

displayed

正如您所观察到的,嵌套顺序中第二个字段的值嵌入在第一个字段的各个值之下。

在您的数据中,每位销售人员仅与一个区域相关联,但是大多数区域与多个销售人员相关联。因此,如果您反转嵌套顺序,您的数据透视表会看起来更有意义。

Changing the Nesting Order

若要更改某个区域中的字段嵌套顺序,只需单击该字段并将其拖动到您想要的位置即可。

单击 ROW 区域中的 Salesperson 字段,并将其拖动到 Region 字段下方。这样,您就将嵌套顺序更改为 - 首先是 Region,然后是 Salesperson,如下所示:

changing nesting order

由此产生的数据透视表如下所示:

resulting pivottable

您可以清楚地看到,使用嵌套顺序(Region,然后是 Salesperson)的布局会产生比嵌套顺序(Salesperson,然后是 Region)更好的紧凑型报表。

如果销售人员代表多个区域,而您需要按销售人员汇总销售额,则先前的布局将是更好的选择。

Excel Pivot Tables - Tools

在包含数据透视表的工作表中,Ribbon 将包含数据透视表工具以及 ANALYZEDESIGN 选项卡。ANALYZE 选项卡包含多项命令,使您能够浏览数据透视表中的数据。DESIGN 选项卡命令将有助于使用各种报表选项和样式选项对数据透视表进行构造。

您将在本章学习 ANALYZE 命令。您将在“使用数据透视表美化报表”一章中学习 DESIGN 命令。

ANALYZE Commands

ANALYZE 选项卡功能区上的命令包括以下内容:

  1. 展开和折叠字段。

  2. 对字段值进行分组和取消分组。

  3. Active Field Settings.

  4. PivotTable Options.

commands

Expanding and Collapsing a Field

如果您在数据透视表中嵌套了字段,则可以展开和折叠单个项目,也可以展开和折叠活动字段的所有项目。

考虑以下数据透视表,其中将 Salesperson 字段嵌套在 Region 字段下。

expanding

单击 East 左侧的 符号。字段 Region 的项目 East 将折叠。

collapsing

正如您所看到的,字段 Region 的其他项目(North、South 和 West)不会折叠。如果您要折叠其中任何一个,请对 East 所做的步骤进行重复。

  1. 单击 East 左侧的 符号。字段 Region 的项目 East 将展开。

如果您要一次折叠字段的所有项目,请做以下操作:

  1. 单击该字段的任何项目(Region)。

  2. 单击功能区中的“分析”选项卡。

  3. 单击“活动字段”组中的“折叠字段”。

collapse all items

字段 Region 的所有项目都将折叠。

collapsed

如果您希望立即展开所有字段的项目,请执行以下操作:

  1. 单击该字段的任意项目——“区域”。

  2. 单击功能区中的“分析”选项卡。

  3. 单击“活动字段”组中的“展开字段”。

expand all items

字段“区域”的所有项目都将展开。

Grouping and Ungrouping Field Values

您可以对字段值进行分组和取消分组以定义您自己的聚类。例如,您可能想要了解合并了东部和北部区域的数据。

  1. 在数据透视表中选择“区域”字段的“东部”和“北部”项目,以及嵌套的“销售人员”字段项目。

  2. 单击功能区中的“分析”选项卡。

  3. 在组——“组”中单击“分组选择”。

grouping

项目——“东部”和“北部”将被分组在一个名为“组 1”的名称下。此外,将会创建一个新的“南部”,南部将被嵌套其中,并将创建一个新的“西部”,西部将被嵌套其中。

group1

您还可以看到,数据透视表字段列表中添加了新字段——“区域 2”,它出现在“行”区域中。

  1. 在数据透视表中选择“区域 2”字段的“南部”和“西部”项目,以及嵌套的“区域”和“销售人员”字段项目。

  2. 单击功能区中的“分析”选项卡。

  3. 在组——“组”中单击“分组选择”。

group selection

该字段“区域”的项目——“南部”和“西部”将被分组在一个名为“组 2”的名称下。

group2

若要取消分组,请执行以下操作:

  1. 单击“组名称”。

  2. Click the ANALYZE tab.

  3. 在组——“组”中单击“取消分组”。

ungroup

Grouping by a Date Field

查看以下数据透视表,其中您按“员工编号”计数、雇佣日期和职务总结了员工数据。

title wise

假设您要按雇佣日期字段(这是一个日期字段)将此数据分组为几年和几个季度。

  1. 单击数据透视表中的日期项目。

  2. 单击功能区中的“分析”选项卡。

  3. 在组——“组”中单击“分组字段”。

quarters

将出现“分组”对话框。

  1. 设置以下日期——“开始于”和“结束于”。

  2. 在“按”下面的方框中选择季度和年份。要选择/取消选择多项,请按住 Ctrl 键。

  3. Click OK.

grouping dialog box

雇佣日期字段值将被分组到季度,嵌套在年份中。

grouped into quarters

如果您想取消此分组,可以通过单击组中的 Ungroup 来执行,具体如早前面板所示 - 功能区中的“按组”。

Active Value Field Settings

您可以通过单击该字段的一个值来设置字段选项。考虑我们在本章前面使用的销售数据示例。

active value

假设您要设置“区域”字段的选项。

  1. 单击“东部”。在功能区中的“活动字段”组的“活动字段”框中,将显示“区域”。

  2. 单击 Field Settings 。将显示字段设置对话框。

field settings

您可以设置“区域”字段的首选项。

PivotTable Options

您可以根据自己的首选项设置数据透视表选项。

  1. Click on the PivotTable.

  2. Click the ANALYZE tab.

  3. 单击数据透视表组中的“选项”。

analyze

将显示 PivotTable Options 对话框。您可以在此对话框中设置首选项。

options dialog box

Excel Pivot Tables - Summarizing Values

您可以通过将字段放在数据透视表字段任务窗格的∑ VALUES区域中来汇总数据透视表。默认情况下,Excel 会将汇总当作∑ VALUES区域中字段的值的总和。但是,您还有其他计算类型,如计数、平均、最大值、最小值等。

在本指南中,您将学习如何根据您希望汇总数据透视表中数据的方式设置计算类型。

Sum

考虑以下数据透视表,其中汇总了按区域、销售人员和月份划分的销售数据。

sum

如您所见,当您将“订单金额”字段拖动到∑ VALUES区域时,它会显示为“订单金额的总和”,这表示计算是以总和形式执行的。在数据透视表中,在左上角显示“订单金额的总和”。此外,“总计”列和“总计”行分别以子总计字段的形式显示在行和列中。

Value Field Settings

使用“值字段设置”,可以在数据透视表中设置计算类型。您还可以决定希望如何显示您的值。

  1. 单击∑ VALUES区域中的“订单金额的总和”。

  2. 从下拉列表中选择“值字段设置”。

将显示“值字段设置”对话框。

value field settings

源名称是字段,自定义名称是字段的总和。计算类型是求和。单击 Show Values As 选项卡。

show values as

在“显示值”框中,显示 No Calculation 。单击 Show Values As 框。你可以找到几种显示总价值的方式。

no calculations

% of Grand Total

你可以按大计总额的百分比显示数据透视表中的值。

  1. 在“自定义名称”框中,键入大计总额的百分比。

  2. 单击“显示值”框。

  3. 在下拉列表中单击“大计总额的百分比”。单击“确定”。

percentage grand total

数据透视表将值汇总为大计总额的百分比。

values as percentage

正如你所能观察到的,数据透视表左上角的数据透视表字段窗格中的“订单金额总和”和“∑ VALUES”区域已变为新的自定义名称 - 大计总额的百分比。

  1. 单击大计总额列的标题。

  2. 在公式栏中键入“大计总额的百分比”。列和行标题都将变为“大计总额的百分比”。

sum values

% of Column Total

假设你想按每个月总额的百分比汇总值。

  1. 单击∑ VALUES区域中的“订单金额的总和”。

  2. 从下拉列表中选择“值字段设置”。将出现“值字段设置”对话框。

  3. 在“自定义名称”框中,键入“月总额的百分比”。

  4. 单击“显示值”框。

  5. 从下拉列表中选择“列总额的百分比”。

  6. Click OK.

percentage column

数据透视表将值汇总为列总额的百分比。在“月份”列中,你将找到特定月份总额的百分比值。

  1. 单击大计总额列的标题。

  2. 在公式栏中键入“列总额的百分比”。列和行标题都将变为“列总额的百分比”。

total month

% of Row Total

你可以通过在“值字段设置”对话框的“显示值”框中选择“行总额的百分比”来按区域总额和销售人员总额的百分比汇总值。

total row

Count

假设你想按帐号按区域、按销售人员和按月汇总值。

  1. Deselect Order Amount.

  2. 将账户拖动至∑ VALUES区域。账户总和将显示在 ∑ VALUES 区域中。

  3. 点击账户总和。

  4. 从下拉列表中选择“值字段设置”。将出现“值字段设置”对话框。

  5. 在按字段汇总值字段框中,选择计数。自定义名称变更为账户计数。

  6. Click OK.

count

账户计数将按如下所示显示 -

count of account

Average

假设你希望通过订单金额的平均值,按区域、销售人员和月份总结数据透视表。

  1. Deselect Account.

  2. 将订单金额拖动至 ∑ VALUES 区域。订单金额的总和将显示在 ∑ VALUES 区域中。

  3. 点击订单金额总和。

  4. 点击下拉列表中的值字段设置。值字段设置对话框会出现。

  5. 在按字段汇总值字段框中,点击平均值。自定义名称变更为订单金额的平均值。

  6. Click OK.

average

平均值将按如下所示显示 -

display average

您必须设置数据透视表中值的数字格式以使其更具可读性。

  1. 点击 ∑ VALUES 中的订单金额平均值。

  2. 点击下拉列表中的值字段设置。值字段设置对话框会出现。

  3. 点击数字格式按钮。

number format

格式单元格对话框出现。

  1. 点击类别下的数字。

  2. 在小数位框中输入 2 并点击确定。

format cells

数据透视表值将被格式化为带有两位小数的数字。

formatted
  1. 单击大计总额列的标题。

  2. 在公式栏中输入平均订单金额。列标题和行标题都将变更为平均订单金额。

grand total column

Max

假设你希望通过订单金额的最大值,按区域、销售人员和月份总结数据透视表。

  1. 点击订单金额总和。

  2. 从下拉列表中选择“值字段设置”。将出现“值字段设置”对话框。

  3. 在按方框汇总值字段中,单击最大值。自定义名称更改为订单金额的最大值。

max order

樞紐分析表將以區域、業務員和月份為基準顯示最大值。

  1. 單擊總計欄的標題。

  2. 在公式列中輸入最大訂單金額。列和行標題都會變為最大訂單金額。

max

Min

假設您想按區域、業務員和月份按訂單金額的最小值來總結樞紐分析表。

  1. 点击订单金额总和。

  2. 点击下拉列表中的值字段设置。值字段设置对话框会出现。

  3. 在按方框汇总值字段中,单击 Min 。自定义名称更改为订单金额的最小值。

min

樞紐分析表將以區域、業務員和月份為基準顯示最小值。

  1. 单击大计总额列的标题。

  2. 在公式列中輸入最小訂單金額。列和行標題都會變為最小訂單金額。

header

Excel Pivot Tables - Updating Data

您已經瞭解了如何使用樞紐分析表總結數據。樞紐分析表所依據的數據可能會定期更新或在事件發生時更新。此外,您還可能需要更改不同的報表的樞紐分析表佈局。

在本章中,您將瞭解更新佈局和/或刷新樞紐分析表中數據的不同方法。

Updating PivotTable Layout

您可以決定是否在您對佈局進行更改時更新您的樞紐分析表,或者是否由單獨的觸發器更新。

正如您之前瞭解的那樣,在樞紐分析表欄位工作窗格中,在底部,您將找到一個用於延遲佈局更新的核取方塊。默認情況下,它未選中,這表示在您對樞紐分析表區域進行更改後,樞紐分析表佈局會立即更新。

updating

檢查選項− Defer Layout Update

它旁邊的更新按鈕將啟用。如果您對樞紐分析表區域進行任何更改,則只有在您單擊更新按鈕後才會反映這些更改。

defer layout update

Refreshing PivotTable Data

當樞紐分析表中的數據在其源中發生更改時,通過刷新樞紐分析表可以在樞紐分析表中反映相同的內容。

  1. Click on the PivotTable.

  2. 单击功能区中的“分析”选项卡。

  3. 單擊數據組中的刷新。

refreshing

在下拉列表中有不同的選項來刷新數據−

  1. Refresh − 從連接到活動單元的源獲取最新數據。

  2. Refresh All − 通過刷新工作簿中的所有來源來獲取最新數據。

  3. Connection Properties − 设置工作簿连接的刷新属性。

Changing the Source Data of a PivotTable

您可以更改数据透视表的源数据范围。例如,您可以扩展源数据以包含更多行数据。

但是,如果源数据已大幅更改,例如有更多或更少的列,请考虑创建一个新的数据透视表。

  1. 单击数据透视表。 PIVOTTABLE TOOLS 随即显示在功能区中。

  2. Click the ANALYZE tab.

  3. 单击“数据”组中的“更改数据源”。

data group

从下拉列表中选择“更改数据源”。

“更改数据透视表数据源”对话框随即显示,当前数据源将突出显示。

change data source

在“选择表或范围”下,选择要包含在表/范围框中的表或范围。单击“确定”。

range

数据透视表的数据源将更改为所选表/数据范围。

Changing to External Data Source

如果要更改外部数据透视表的数据源,最好创建一个新的数据透视表。但是,如果外部数据源的位置发生更改,例如,SQL Server 数据库名称相同,但它已移至另一个服务器,或者 Access 数据库已移至另一个网络共享,则可以更改当前数据连接以反映同样的情况。

  1. Click on the PivotTable.

  2. 单击功能区中的“分析”选项卡。

  3. 单击“数据”组中的 Change Data SourceChange PivotTable Data Source 对话框随即显示。

  4. 单击 Choose Connection 按钮。

changing

Existing Connections 对话框随即显示。

  1. 在“显示”框中选择“所有连接”。工作簿中的所有连接都将显示出来。

  2. 单击 Browse for More 按钮。

browse

Select Data Source 窗口随即显示。

  1. 单击“新建数据源”按钮。

  2. 按照数据连接向导步骤进行操作。

source button

如果数据源位于另一个 Excel 工作簿中,请执行以下操作:

  1. 单击“文件名”框。

  2. 选择工作簿文件名。

workbook

Deleting a PivotTable

可通过以下步骤删除数据透视表:

  1. Click on the PivotTable.

  2. 单击功能区中的“分析”选项卡。

  3. 单击“操作”组中的“选择”。

deleting

从下拉列表中选择 Entire PivotTable 。整个数据透视表将被选中。

entire pivottable

按下 Delete 键。数据透视表将被删除。

delete key

如果数据透视表位于单独的工作表上,可通过删除整个工作表删除数据透视表。

右键单击工作表选项卡,然后从下拉列表中选择 Delete

select delete

整个工作表连同数据透视表将被删除。

Excel Pivot Tables - Reports

数据透视表的主要作用是报告。在创建数据透视表,通过整理其行和列中的字段来探索数据后,即可向广泛受众展示数据。通过筛选器、不同的汇总和重点关注特定数据,可以根据单个数据透视表生成多个所需报告。

由于数据透视表报告具有交互性,因此可快速进行必要的更改,以在报告时突出显示具体结果,例如数据趋势、数据汇总等。还可以向收件人提供报表筛选器、切片器、时间轴、数据透视图表等可视提示,以便他们可视化所需详情。

在此章节中,将学习不同方法,以通过可视提示美化数据透视表报告,以便快速探索数据。

Hierarchies

已了解如何嵌套字段以形成层次结构,在本教程的“在数据透视表中嵌套”章节中。还学习了如何在“使用数据透视表工具”章节中对数据透视表中的数据进行分组/取消分组。将示范几个示例,展示如何利用层次结构生成交互式数据透视表报告。

如果数据中的字段具有内置结构,例如:年-季度-月,则嵌套字段以形成层次结构将使你能够快速展开/折叠字段,以查看所需级别的汇总值。

例如,假设有 2015-16 财年的销售数据,地区包括东部、北部、南部和西部,如下所示。

hierarchies

创建如下所示的数据透视表。

create

正如所见,这是一种使用嵌套字段作为层次结构报告数据的全面方法。如果仅想在季度级别显示结果,则可以快速折叠“季度”字段。

quater field

假设数据中存在“日期”字段,如下所示。

your data

在这种情况下,可通过以下步骤按“日期”字段对数据分组:

创建数据透视表。

group

如你所见,此数据透视表不利于突出重要数据。

  1. 按日期字段对数据透视表分组。(在本指南的“使用数据透视表工具探索数据”一章中,你已经学习了分组功能。)

  2. 将销售人员字段放入筛选器区域。

  3. 将列标签筛选为东区。

east region

Report Filter

假设你要分别针对每个销售人员生成报告。可按以下步骤操作:

  1. 确保筛选器区域中有销售人员字段。

  2. Click on the PivotTable.

  3. 单击功能区中的“分析”选项卡。

  4. 单击数据透视表组中选项旁边的箭头。

  5. 从下拉列表中选择显示报告筛选器页面。

report filter

Show Report Filter Pages 对话框随即显示。选择字段销售人员,然后单击确定。

filter pages

将为销售人员字段的每个值创建一个独立的工作表,其中数据透视表筛选至该值。

separate worksheet

工作表将以字段值命名,值显示在工作表的选项卡上。

Slicers

数据透视表中的另一复杂功能是切片器,可用作可视化筛选字段。

  1. Click on the PivotTable.

  2. Click the ANALYZE tab.

  3. 单击筛选组中的插入切片器。

  4. 在插入切片器对话框中单击 Order Date, Quarters and Years 。将创建三个切片器:订单日期、季度和年份。

  5. 调整切片器大小,为切片器上的按钮添加更多列。

  6. 还要为销售人员和地区字段创建切片器。

  7. 选择切片器样式,以便将日期字段组合为一种颜色,另外两个字段获取不同的颜色。

  8. Deselect Gridlines.

slicer

如你所见,你不仅有一个交互式报告,还拥有一个吸引人的报告,可以轻松理解。

Timeline in PivotTable

如果透视表中包含“日期”字段,插入“时间轴”也是生成美观报表的一种选择。

  1. 在“行”区域创建包含业务员的透视表,在“列”区域创建包含区域的透视表。

  2. 插入“订单日期”字段的时间轴。

  3. 筛选时间轴以显示 2015 年 11 月至 2016 年 3 月的 5 个月数据。

timeline

DESIGN Commands

功能区上的 PIVOTTABLE TOOLS - DESIGN 命令提供格式化透视表的选项,包括以下内容:

  1. Layout

  2. PivotTable Style Options

  3. PivotTable Styles

Layout

根据以下内容,透视表布局可以使用您的首选项:

  1. Subtotals

  2. Grand Totals

  3. Report Layout

  4. Blank Rows

layout

PivotTable Layout – Subtotals

可以选择是否显示 Subtotals 。默认情况下,子总数显示在组的顶部。

subtotals

当您观察到高亮显示的组——“东部”时,子总数位于组的顶部。您可以按照以下步骤更改子总数的位置:

  1. Click on the PivotTable.

  2. 单击功能区上的设计选项卡。

  3. 单击“布局选项”组中的“子总数”。

  4. 单击“在组底部显示所有子总数”。

click subtotals

子总数现在将显示在每个组的底部。

bottom

如果您不必报告子总数,则可以选择 - 不显示子总数。

show subtotals

Grand Totals

可以选择显示或不显示“合计”。有四种可能的组合:

  1. 关闭行和列

  2. 打开行和列

  3. On for Rows Only

  4. On for Columns Only

默认情况下,是第二个组合——打开行和列。

Report Layout

您可以从若干报表布局中选择最适合您数据的布局。

  1. Compact Form.

  2. Outline Form.

  3. Tabular Form.

如果发生多次出现,还可以选择是否重复所有项目标签。

report layout

默认的报表布局是你熟悉的紧凑格式。

Compact Form

compact form

紧凑格式优化了数据透视表的可读性。其他两个格式也会显示字段标题。

单击大纲格式中的 Show

click show

单击表格格式中的 Show

tabular form

考虑以下数据透视表布局,其中字段“月份”嵌套在字段“区域”下 −

nested

正如你所观察到的,“月份”标签是重复的,这是默认设置。

单击“不重复项目标签”。“月份”标签将仅显示一次,数据透视表看起来很清晰。

not repeat

Blank Rows

为了使数据透视表报表更清晰,可以在每个项目后插入一个空白行。你稍后可以随时删除这些空白行。

blank rows

单击 Insert Blank Line after Each Item

insert blank line

PivotTable Style Options

你有以下数据透视表样式选项 −

  1. Row Headers

  2. Column Headers

  3. Banded Rows

  4. Banded Columns

style option

默认情况下,行标题和列标题的框被选中。这些选项用于分别显示第一行和第一列的特殊格式。勾选框 Banded Rows

banded rows

勾选带状列框。

banded columns

PivotTable Styles

你可以选择几种数据透视表样式。选择适合你报告的样式。例如,如果你选择“数据透视表样式深色5”,你将获得以下数据透视表样式。

pivottable styles

Conditional Formatting in PivotTable

你可以通过值设置数据透视表单元格的条件格式。

condition formatting

PivotCharts

数据透视图表在你的数据透视表报表中加入了视觉重点。你可以通过以下方式插入与数据透视表数据关联的数据透视图表 −

  1. Click on the PivotTable.

  2. 单击功能区中的“分析”选项卡。

  3. Click PivotChart.

pivotcharts

“插入图表”对话框会出现。

单击左侧窗格中的“柱形图”并选择“堆积柱形图”。单击“确定”。

click column

堆积柱形图显示出来。

stacked column
  1. 单击数据透视表的“月份”。

  2. 筛选到“2 月”,然后单击“确定”。

february

您会观察到,数据透视表也会按照数据透视表进行筛选。