Excel Pivot Tables 简明教程

Excel Pivot Tables - Quick Guide

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

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

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.

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

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

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

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

The PivotTable layout simply depends on what fields you have selected for the report and how you have arranged them in Areas. The selection and arrangement can be done by just dragging the fields. As you drag the fields, the PivotTable layout keeps the changing and it happens in a matter of seconds.

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

You will learn about PivotTable Fields and Areas in the Chapters – PivotTable Fields and PivotTable Areas.

Exploring Data with PivotTable

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

The primary goal of using a PivotTable normally is to explore the data to extract significant and required information. You have several options to do this that include Sorting, Filtering, Nesting, Collapsing and Expanding, Grouping and Ungrouping, etc.

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

You will have an overview of these options in the Chapter - Exploring Data with PivotTable.

Summarizing Values

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

Once you collate the data required by you by the different exploration techniques, the next step that you would like to take is to summarize the data. Excel provides you with a variety of calculation types that you can apply based on suitability and requirement. You can also switch across different calculation types and view the results in a matter of seconds.

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

You will learn how to apply the calculation types on a PivotTable in the Chapter - Summarizing Values by Different Calculation Types.

Updating a PivotTable

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

Once you have explored the data and summarized it, you need not repeat the exercise if and when the source data gets updated. You can refresh the PivotTable so that it reflects the changes in the source data.

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

You will learn the various ways of refreshing data in the Chapter – Updating a PivotTable.

PivotTable Reports

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

After exploring and summarizing the data with a PivotTable, you would be presenting it as a report. PivotTable reports are interactive in nature, with the specialty that even a person not familiar with Excel can use them intuitively. Because of their inherent dynamic nature, they will enable you to change the perspective quickly of the report to show the required level of detail or to focus on the specific items in which the audience expresses interest.

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

Further, you can structure a PivotTable report for standalone presentation or as an integral part of a broad report as the case may be. You will learn the several of reporting with PivotTables in the Chapter – PivotTable Reports.

Excel Pivot Tables - Creation

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

You can create a PivotTable either from a range of data or from an Excel table. In both the cases, the first row of the data should contain the headers for the columns.

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

If you are sure of the fields to be included in the PivotTable and the layout you want to have, you can start with an empty PivotTable and construct the PivotTable.

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

In case you are not sure which PivotTable layout is best suitable for your data, you can make use of Recommended PivotTables command of Excel to view the PivotTables customized to your data and choose the one you like.

Creating a PivotTable from a Data Range

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

Consider the following data range that contains the sales data for each Salesperson, in each Region and in the months of January, February and March −

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

To create a PivotTable from this data range, do the following −

Ensure that the first row has headers. You need headers because they will be the field names in your PivotTable.
Name the data range as SalesData_Range.
Click on the data range – SalesData_Range.
Click the INSERT tab on the Ribbon.

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

Click PivotTable in the Tables group. The Create PivotTable dialog box appears.

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

In Create PivotTable dialog box, under Choose the data that you want to analyze, you can either select a Table or Range from the current workbook or use an external data source.

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

As you are creating a PivotTable from a data range, select the following from the dialog box −

Select Select a table or range.
In the Table/Range box, type the range name – SalesData_Range.
Select New Worksheet under Choose where you want the PivotTable report to be placed and click OK.

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

You can choose to analyze multiple tables, by adding this data range to Data Model. You can learn how to analyze multiple tables, use of Data Model and how to use an external data source to create a PivotTable in the tutorial Excel PowerPivot.

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

A new worksheet is inserted into your workbook. The new worksheet contains an empty PivotTable. Name the worksheet – Range-PivotTable.

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

As you can observe, the PivotTable Fields list appears on the right side of the worksheet, containing the header names of the columns in the data range. Further, on the Ribbon, PivotTable Tools – ANALYZE and DESIGN appear.

Adding Fields to the PivotTable

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

You will understand in detail about PivotTable Fields and Areas in the later chapters in this tutorial. For now, observe the steps to add fields to the PivotTable.

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

Suppose you want to summarize the order amount salesperson-wise for the months January, February, and March. You can do it in few simple steps as follows −

Click on the field Salesperson in the PivotTable Fields list and drag it to the ROWS area.
Click the field Month in the PivotTable Fields list and drag that also to ROWS area.
Click on Order Amount and drag it to ∑ VALUES area.

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

Your first PivotTable is ready as shown below

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

Observe that two columns appear in the PivotTable, one containing the Row Labels that you selected, i.e. Salesperson and Month and a second one containing Sum of Order Amount. In addition to Sum of Order Amount month wise for each Salesperson, you will also get subtotals representing the total sales by that person. If you scroll down the worksheet, you will find the last row as Grand Total representing total sales.

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

You will learn more about producing PivotTables as per the need as you progress through this tutorial.

Creating a PivotTable from a Table

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

Consider the following Excel table that contains the same sales data as in the previous section −

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

An Excel table will inherently have a name and the columns will have headers, which is a requirement to create a PivotTable. Suppose the table name is SalesData_Table.

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

To create a PivotTable from this Excel table, do the following −

Click on the table – SalesData_Table.
Click the INSERT tab on the Ribbon.
Click PivotTable in the Tables group. The Create PivotTable dialog box appears.

Click Select a table or range.
In the Table/Range box, type the table name – SalesData_Table.
Select New Worksheet under Choose where you want the PivotTable report to be placed. Click OK.

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

A new worksheet is inserted into your workbook. The new worksheet contains an empty PivotTable. Name the worksheet – Table-PivotTable. The worksheet – Table-PivotTable looks similar to the one you have got in the data range case in the earlier section.

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

You can add fields to the PivotTable as you have seen in the section – Adding Fields to the PivotTable, earlier in this chapter.

Creating a PivotTable with Recommended PivotTables

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

In case you are not familiar with Excel PivotTables or if you do not know which fields would result in a meaningful report, you can use the Recommended PivotTables command in Excel. Recommended PivotTables gives you all the possible reports with your data along with the associated layout. In other words, the options displayed will be the PivotTables that are customized to your data.

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

To create a PivotTable from the Excel table SalesData-Table using Recommended PivotTables, proceed as follows −

Click on the table SalesData-Table.
Click the INSERT tab.
Click Recommended PivotTables in the Tables group. The Recommended PivotTables Dialog Box appears.

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

In the Recommended PivotTables dialog box, the possible customized PivotTables that suit your data will be displayed.

Click on each of the PivotTable options to see the preview on the right side.
Click on the PivotTable - Sum of Order Amount by Salesperson and Month and click OK.

你将获得右侧预览。

You will be get the preview on the right side.

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

The selected PivotTable appears on a new worksheet in your workbook.

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

You can see that the PivotTable Fields - Salesperson, Region, Order Amount and Month got selected. Of these, Region and Salesperson are in ROWS area, Month is in COLUMNS area, and Sum of Order Amount is in ∑ VALUES area.

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

The PivotTable summarized the data Region-wise, Salesperson-wise and Month-wise. The subtotals are displayed for each Region, each Salesperson, and each Month.

Excel Pivot Tables - Fields

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

PivotTable Fields is a Task Pane associated with a PivotTable. The PivotTable Fields Task Pane comprises of Fields and Areas. By default, the Task Pane appears at the right side of the window with Fields displayed above Areas.

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

Fields represent the columns in your data – range or Excel table, and will have check boxes. The selected fields are displayed in the report. Areas represent the layout of the report and the calculations included in the report.

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

At the bottom of the Task Pane, you will find an option – Defer Layout Update with an UPDATE button next to it.

By default, this is not selected and whatever changes you make in the selection of fields or in the layout options are reflected in the PivotTable instantly.
If you select this, the changes in your selections are not updated until you click on the UPDATE button.

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

In this chapter, you will understand the details about Fields. In the next chapter, you will understand the details about Areas.

PivotTable Fields Task Pane

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

You can find the PivotTable Fields Task Pane on the worksheet where you have a PivotTable. To view the PivotTable Fields Task Pane, click the PivotTable. In case the PivotTable Fields Task Pane is not displayed, check the Ribbon for the following −

Click the ANALYZE tab under PIVOTTABLE TOOLS on the Ribbon.
Check if Fields List is selected (i.e. highlighted) in the Show group.
If Fields List is not selected, then click it.

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

The PivotTable Fields Task Pane will be displayed on the right side of the window, with the title – PivotTable Fields.

Moving PivotTable Fields Task Pane

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

On the right of the title PivotTable Fields of the PivotTable Task Pane, you will find the button . This represents Task Pane Options. Click the button . The Task Pane Options- Move, Size and Close appear in the dropdown list.

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

You can move the PivotTables Task Pane to anywhere you want in the window as follows −

Click Move in the dropdown list. The button appears on the Task Pane.
Click the icon and drag the pane to a position where you want to place it. You can place the Task Pane next to the PivotTable as given below.

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

You can place the Task Pane on the left side of the window as given below.

Resizing PivotTable Fields Task Pane

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

You can resize the PivotTables Task Pane – i.e. increase / decrease the Task Pane length and/or width as follows −

Click on Task Pane Options − that is on the right side of the title - PivotTable Fields.
Click on Size in the dropdown list.
Use the symbol ⇔ to increase / decrease the width of the Task Pane.
Use the symbol ⇕ to increase / decrease the width of the Task Pane.

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

In the ∑ VALUES area, to make Sum of Order Amount visible completely, you can resize the Task Pane as given below.

PivotTable Fields

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

The PivotTable Fields list comprises of all the tables that are associated with your workbook and the corresponding fields. It is by selecting the fields in the PivotTable fields list, you will create the PivotTable.

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

The tables and the corresponding fields with check boxes, reflect your PivotTable data. As you can check / uncheck the fields randomly, you can quickly change the PivotTable, highlighting the summarized data that you want to report or present.

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

As you can observe, if there is only one table, the table name will not be displayed in the PivotTable Fields list. Only the fields will be displayed with check boxes.

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

Above the fields list, you will find the action Choose fields to add to report. To the right, you will find the button − that represents Tools.

Click on the Tools button.

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

In the dropdown list, you will find the following −

Five different layout options for Fields and Areas.
Two options for Sort order of the fields in the Fields list − Sort A to Z. Sort in Data Source Order.

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

As you can observe in the above Fields list, the Sort order is by default – i.e. in Data Source Order. This means, it is the order in which the columns in your data table appear.

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

Normally, you can retain the default order. However, at times, you might encounter many fields in a table and might not be acquainted with them. In such a case, you can sort the fields in alphabetical order by clicking on – Sort A to Z in the dropdown list of Tools. Then, the PivotTable Fields list looks as follows −

Excel Pivot Tables - Areas

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

PivotTable areas are a part of PivotTable Fields Task Pane. By arranging the selected fields in the areas, you can arrive at different PivotTable layouts. As you can simply drag the fields across areas, you can quickly switch across the different layouts, summarizing the data, in a way you want.

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

You have already learnt about PivotTable Fields Task Pane in the earlier chapter on PivotTable Fields in this tutorial. In this chapter, you will learn about the PivotTable areas.

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

There are four PivotTable areas available −

ROWS.
COLUMNS.
FILTERS.
∑ VALUES (Read as Summarizing Values).

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

The message - Drag fields between areas below appears above the areas.

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

With PivotTable Areas, you can choose −

What fields to display as rows (ROWS area).
What fields to display as columns (COLUMNS area).
How to summarize your data (∑ VALUES area).
Filters for any of the fields (FILTERS area).

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

You can just drag the fields across these areas and observe how the PivotTable Layout changes.

ROWS

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

If you select the fields in the PivotTable Fields lists by just checking the boxes, all the nonnumeric fields will automatically be added to the ROWS area, in the order you select.

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

You can optionally, drag a field to the ROWS area. The fields that are put in ROWS area appear as rows in the PivotTable, with the Row Labels being the values of the selected fields.

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

For example, consider the Sales data table.

Drag the field Salesperson to ROWS area.
Drag the field Month to ROWS area.

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

Your PivotTable appears with one column containing the Row Labels – Salesperson and Month and a last row as Grand Total, as given below.

COLUMNS

您可以将域拖至 COLUMNS 区域。

You can drag fields to the COLUMNS area.

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

The fields that are put in COLUMNS area appear as columns in the PivotTable, with the Column Labels being the values of the selected fields.

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

Drag the field Region to COLUMNS area. Your PivotTable appears with the first column containing the Row Labels – Salesperson and Month the next four columns containing the Column Labels – Region and a last column Grand Total as given below.

Drag the field Month from ROWS to COLUMNS.
Drag the field Region from COLUMNS to ROWS. Your PivotTable layout changes as given below.

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

You can see that there are only five columns now – the first column with Row Labels, three columns with Column Labels and a last column with Grand Total.

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

The number of Rows and Columns is based on the number of values you have in those fields.

∑ VALUES

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

The primary use of a PivotTable is to summarize values. Hence, by placing the fields by which you want to summarize the data in ∑ VALUES area, you arrive at the summary table.

Drag the field Order Amount to ∑ VALUES.
Drag the field Region to above the field Salesperson in ROWS area. This step is to change the nesting order. You will learn nesting in the chapter – Nesting in the PivotTable in this tutorial.

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

As you can observe, the data is summarized region-wise, salesperson-wise and monthwise. You have subtotals for each region, month wise. You also have grand totals month wise in the Grand Total row grand totals region wise in the Grand Total column.

FILTERS

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

The Filters area is to place filters in PivotTable. Suppose you want to display results separately for the selected regions only.

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

Drag the field Region from ROWS area to FILTERS area. The filter Region will be placed above the PivotTable. In case you do not have empty rows above the PivotTable, the PivotTable is pushed down inserting rows above the PivotTable for the filter.

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

As you can observe, (ALL) appears in the filter by default, and the PivotTable displays data for all the values of the Region.

Click on the arrow to the right of filter.
Check the box – Select Multiple Items.

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

Check boxes will appear for all the options in the dropdown list. By default, all the boxes are checked.

Check the boxes – North and South.
Clear the other boxes. Click OK.

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

The PivotTable gets changed to reflect the filtered data.

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

You can observe that the filter displays (Multiple Items). Therefore, when someone is looking at the PivotTable, it is not immediately obvious of what values are filtered.

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

Excel provides you another tool called Slicers to handle filtering more efficiently. You will understand Filtering Data in a PivotTable in detail in a later chapter in this tutorial.

Excel Pivot Tables - Exploring Data

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

Excel PivotTable allows you to explore and extract significant data from an Excel table or a range of data. There are several ways of doing this and you can choose the ones that are best suited to your data. Further, while you are exploring the data, you can view the different combinations instantly as you change your choices to pick the data values.

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

You can do the following with a PivotTable −

Sort the data.
Filter the data.
Nest the PivotTable fields.
Expand and Collapse the fields.
Group and ungroup field values.

Sorting and Filtering Data

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

You can sort the data in a PivotTable in ascending or descending order of the field values. You can also sort by subtotals from largest to smallest or smallest to largest values. You can also set sort options. You will learn these in detail in the chapter – Sorting Data in a PivotTable in this tutorial.

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

You can filter the data in a PivotTable to focus on some specific data. You have several filtering options in PivotTable that you will learn in the chapter – Filtering Data in a PivotTable in this tutorial. You can use Slicers for filtering, which you will learn in the chapter – Filtering using Slicers in this tutorial.

Nesting, Expanding and Collapsing Fields

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

You can nest fields in a PivotTable to show a hierarchy, if relevant to your data. You will learn this in the chapter - Nesting in a PivotTable in this tutorial.

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

When you have nested fields in your PivotTable, you can expand and collapse the values of those fields. You will learn these in the Chapter – Exploring Data with PivotTable Tools in this tutorial.

Grouping and Ungrouping Field Values

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

You can group and ungroup specific values of a field in a PivotTable. You will learn this in the Chapter – Exploring Data with PivotTable Tools in this tutorial.

Excel Pivot Tables - Sorting Data

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

You can sort the data in a PivotTable so that it will be easy for you to find the items you want to analyze. You can sort the data from lowest to highest values or highest to lowest values or in any other custom order that you choose.

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

Consider the following PivotTable wherein you have the summarized sales data region-wise, salesperson-wise and month-wise.

Sorting on Fields

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

You can sort the data in the above PivotTable on Fields that are in Rows or Columns – Region, Salesperson and Month.

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

To sort the PivotTable with the field Salesperson, proceed as follows −

Click the arrow in the Row Labels.
Select Salesperson in the Select Field box from the dropdown list.

显示以下排序选项 −

The following sorting options are displayed −

Sort A to Z.
Sort Z to A.
More Sort Options.

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

Further, the Salesperson field is sorted in ascending order, by default. Click Sort Z to A. The Salesperson field will be sorted in descending order.

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

In the same way, you can sort the field in column – Month, by clicking on the arrow in the column labels.

Sorting on Subtotals

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

Suppose you want to sort the PivotTable based on total order amount – highest to lowest in every Region. That is, you want to sort the PivotTable on subtotals.

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

You can see that there is no arrow for subtotals. You can still sort the PivotTable on subtotals as follows −

Right-click on the subtotal of any of the Salespersons in the Grand Total column.
Select Sort from the dropdown list.
Another dropdown list appears with the sorting options – Sort Smallest to Largest, Sort Largest to Smallest and More Sort Options. Select Sort Largest to Smallest.

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

The subtotals in the Grand Total column are sorted from highest to lowest values, in every region.

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

Likewise, if you want to sort the PivotTable on subtotals region wise, do the following −

Right click on the subtotal of any of the regions in the Grand Total column.
Click Sort in the dropdown list.
Click Sort Largest to Smallest in the second dropdown list. The PivotTable will get sorted on subtotals region-wise.

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

As you can observe, South has the highest order amount while North has the lowest.

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

You can also sort the PivotTable based on the total amount month wise as follows −

Right click on any of the Subtotals in the Grand Total row.
Select Sort from the dropdown list.
Select Sort Largest to Smallest from the second dropdown list.

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

The PivotTable will be sorted on total amount month wise.

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

You can observe that February has highest order amount while March has the lowest.

More Sort Options

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

Suppose you want to sort the PivotTable on total amount region wise in the month of January.

Click on the arrow in Row Labels.
Select More Sort Options from the dropdown list. The Sort (Region) dialog box appears.

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

As you can observe, under Summary, the current Sort order is given as Sort Region in ascending order. Ascending (A to Z) by is selected under Sort Options. In the box below that, Region is displayed.

Click the box containing Region.
Click Sum of Order Amount.

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

Click the More Options button. The More Sort Options (Region) dialog box appears.

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

As you can observe, under Sort By, Grand Total is selected. Under Summary, the current sort order is given as Sort Region by Sum of Order Amount in ascending order.

Click Values in selected column: under Sort By.
In the box below that, type B5.

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

As you can observe, under Summary, the current sort order is given as follows −

Sort Region by Sum of Order Amount in ascending order using values in this column: January. Click OK.
The Sort (Region) dialog box appears. Select Descending (Z to A) by: under Sort Options.

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

Under Summary, the current sort order is given as follows −

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

Sort Region by Sum of Order Amount in descending order, using values in this column: January. Click OK. The PivotTable will be sorted on region, using values in January.

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

As you can observe, in the month of January, West has the highest order amount while North has the lowest.

Sorting Data Manually

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

In the PivotTable, the data is sorted automatically by the sorting option that you have chosen. This is termed as AutoSort.

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

Place the cursor on the arrow in Row Labels or Column Labels.

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

AutoSort appears, showing the current sort order for each of the fields in the PivotTable. Now, suppose you want to sort the field Region in the order – East, West, North and South. You can do this manually, as follows −

Click the arrow in Row Labels.
Select Region in the Select Field box from the dropdown list.
Click More Sort Options. The Sort (Region) dialog box appears.
Select Manual (you can drag items to rearrange them).
Click OK.

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

Under Summary, the current sort order is given as Drag items of the Region field to display them in any order.

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

Click on the East and drag it to the top. While you are dragging East, a horizontal green bar appears across the entire row moves.

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

Repeat the dragging with other items of the Region field until you get the required arrangement.

您可以观察到以下内容 −

You can observe the following −

The items of the nested field – Salesperson also move along with the corresponding Region field item. Further, the values in the other columns also moved accordingly.
If you place the cursor on the arrow in Row Labels or Column Labels, AutoSort appears showing the current sort order of the fields Salesperson and Month only. As you have sorted Region field manually, it will not show up in AutoSort.

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

Note − You cannot use this manual dragging of items of the field that is in ∑ VALUES area of the PivotTable Fields list. Therefore, you cannot drag the Sum of Order Amount values in this PivotTable.

Setting Sort Options

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

In the previous section, you have learnt how to set the sorting option for a field to manual. You have some more sort options that you can set as follows −

Click the arrow in Row Labels.
Select Region in the Select Field box.
Click More Sort Options. The Sort (Region) dialog box appears.
Click the More Options button.

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

More Sort Options (Region) dialog box appears. You can set more sort options in this dialog box.

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

Under AutoSort, you can check or uncheck the box - Sort automatically every time the report is updated, to allow or stop automatic sorting whenever the PivotTable data is updated.

Uncheck the box – Sort automatically every time the report is updated.

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

Now, First key sort order option becomes available. You can use this option to select the custom order you want to use.

Click the box under First key sort order.

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

As you can observe, day-of-the-week and month-of-the year custom lists are provided in the dropdown list. You can use any of these, or you can use your own custom list such as High, Medium, Low or the sizes list S, M, L, XL that are not in alphabetical order.

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

You can create your custom lists from the FILE tab on the Ribbon. FILE → Options. In the Excel Options dialog box, click on advanced and browse to General. You will find the Edit Custom Lists button next to Create lists for use in sort and fill sequences.

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

Note that a custom list sort order is not retained when you update (refresh) data in your PivotTable.

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

Under Sort By, you can click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.

Points to consider while sorting PivotTables

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

When you sort data in a PivotTable, remember the following −

Data that has leading spaces will affect the sort results. Remove any leading spaces before you sort the data.
You cannot sort case-sensitive text entries.
You cannot sort data by a specific format such as cell or font color.
You cannot sort data by conditional formatting indicators, such as icon sets.

Excel Pivot Tables - Filtering Data

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

You might have to do in-depth analysis on a subset of your PivotTable data. This might be because you have large data and your focus is required on a smaller portion of the data or irrespective of the size of the data, your focus is required on certain specific data. You can filter the data in the PivotTable based on a subset of the values of one or more fields. There are several ways to do that as follows −

Filtering using Slicers.
Filtering using Report Filters.
Filtering data manually.
Filtering using Label Filters.
Filtering using Value Filters.
Filtering using Date Filters.
Filtering using Top 10 Filter.
Filtering using Timeline.

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

You will learn filtering data using Slicers in the next chapter. You will understand filtering by the other methods mentioned above in this chapter.

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

Consider the following PivotTable wherein you have the summarized sales data region wise, salesperson wise and month wise.

Report Filters

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

You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.

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

Drag Region from Rows to Filters in the PivotTable Areas.

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

The Filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter.

你会观察到

You will observe that

Salesperson values appear in rows.
Month values appear in columns.
Region Filter appears on the top with default selected as ALL.
Summarizing value is Sum of Order Amount. Sum of Order Amount Salesperson-wise appears in the column Grand Total. Sum of Order Amount Month-wise appears in the row Grand Total.
Click on the arrow in the box to the right of the Filter Region.

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

A drop-down list with the values of the field Region appears. Check the box Select Multiple Items.

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

By default, all the boxes are checked. Uncheck the box (All). All the boxes will be unchecked.

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

Then check the boxes - South and West and click OK.

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

The data pertaining to South and West regions only will get summarized.

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

In the cell next to the Filter Region - (Multiple Items) is displayed, indicating that you have selected more than one item. However, how many items and / or which items is not known from the report that is displayed. In such a case, using Slicers is a better option for filtering.

Manual Filtering

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

You can also filter the PivotTable by picking the values of a field manually. You can do this by clicking on the arrow in the Row Labels or Column Labels cell.

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

Suppose you want to analyze only February data. You need to filter the values by the field Month. As you can observe, Month is part of Column Labels.

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

Click on the arrow in the Column Labels cell.

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

As you can observe, there is a Search box in the dropdown list and below the box, you have the list of the values of the selected field, i.e. Month. The boxes of all the values are checked, showing that all the values of that field are selected.

Uncheck the (Select All) box at the top of the list of values.
Check the boxes of the values you want to show in your PivotTable, in this case February and click OK.

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

The PivotTable displays only those values that are related to the selected Month field value – February. You can observe that the filtering arrow changes to the icon to indicate that a filter is applied. Place the cursor on the icon.

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

You can observe that is displayed indicating that the Manual Filter is applied on the field- Month.

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

If you want to change the filter selection value, do the following −

Click the icon.
Check / uncheck the boxes of the values.

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

If all the values of the field are not visible in the list, drag the handle in the bottom-right corner of the dropdown to enlarge it. Alternatively, if you know the value, type it in the Search box.

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

Suppose you want to apply another filter on the above filtered PivotTable. For example, you want to display the data of that of Walters, Chris for the month February. You need to refine your filtering by adding another filter for the field Salesperson. As you can observe, Salesperson is part of Row Labels.

Click on the arrow in the Row Labels cell.

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

The list of the values of the field – Region is displayed. This is because, Region is at outer level of Salesperson in the nesting order. You also have an additional option – Select Field. Click on the Select Field box.

Click Salesperson from the dropdown list. The list of the values of the field – Salesperson will be displayed.
Uncheck (Select All) and check Walters, Chris.
Click OK.

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

The PivotTable displays only those values that are related to the selected Month field value – February and Salesperson field value - Walters, Chris.

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

The filtering arrow for Row Labels also changes to the icon to indicate that a filter is applied. Place the cursor on the icon on either Row Labels or Column Labels.

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

A text box is displayed indicating that the Manual Filter is applied on the fields – Month, and Salesperson.

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

You can thus filter the PivotTable manually based on any number of fields and on any number of values.

Filtering by Text

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

If you have fields that contain text, you can filter the PivotTable by Text, provided the corresponding field label is text-based. For example, consider the following Employee data.

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

The data has the details of the employees – EmployeeID, Title, BirthDate, MaritalStatus, Gender and HireDate. Additionally, the data also has the manager level of the employee (levels 0 – 4).

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

Suppose you have to do some analysis on the number of employees reporting to a given employee by title. You can create a PivotTable as given below.

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

You might want to know how many employees with ‘Manager’ in their title have employees reporting to them. As the Label Title is text-based, you can apply the Label Filter on the Title field as follows −

Click on the arrow in the Row Labels cell.
Select Title in the Select Field box from the drop down list.
Click on Label Filters.
Click on Contains in the second dropdown list.

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

Label Filter (Title) dialog box appears. Type Manager in the box next to Contains. Click OK.

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

The PivotTable will be filtered to the Title values containing ‘Manager’.

Click the icon.

您可以看到显示，指示：

You can see that is displayed indicating the following −

The Label Filter is applied on the field – Title, and
What the applied Label Filter is.

Filtering by Values

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

You might want to know the titles of the employees who have more than 25 employees reporting to them. For this, you can apply the Value Filter on the Title field as follows −

Click on the arrow in the Row Labels cell.
Select Title in the Select Field box from the drop down list.
Click on Value Filters.
Select Greater than or equal to from the second dropdown list.

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

The Value Filter (Title) dialog box appears. Type 25 in the right side box.

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

The PivotTable will be filtered to display the employee titles who have more than 25 employees reporting to them.

Filtering by Dates

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

You might want to display the data of all the employees who were hired in the fiscal year 2015-15. You can use Data Filters for the same as follows −

Include the HireDate field in the PivotTable. Now, you do not require manager data and so remove ManagerLevel field from the PivotTable.

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

Now that you have a Date field in the PivotTable, you can use Date Filters.

Click the arrow in the Row Labels cell.
Select HireDate in the Select Field box from the drop down list.
Click Date Filters.
Seelct Between from the second dropdown list.

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

The Date Filter (HireDate) dialog box appears. Type 4/1/2014 and 3/31/2015 in the two Date boxes. Click OK.

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

The PivotTable will be filtered to display only the data with HireDate between 1st April 2014 and 31st March 2015.

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

You can group the dates into Quarters as follows −

Right click on any of the dates. The Grouping dialog box appears.
Type 4/1/2014 in the box Starting at. Check the box.
Type 3/31/2015 in the box Ending at. Check the box.
Click Quarters in the box under By.

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

The dates will be grouped into quarters in the PivotTable. You can make the table look compact by dragging the field HireDate from ROWS area to COLUMNS area.

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

You will be able to know how many employees were hired during the fiscal year, quarter wise.

Filtering Using Top 10 Filter

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

You can use the Top 10 Filter to display the top few or bottom few values of a field in the PivotTable.

Click the arrow in the Row Labels cell.
Click Value Filters.
Click Top 10 in the second dropdown list.

将出现前 10 个筛选器（职称）对话框。

Top 10 Filter (Title) dialog box appears.

In the first box, click on Top (You can choose Bottom also).
In the second box, enter a number, say, 7.
In the third box, you have three options by which you can filter. Click on Items to filter by number of items. Click on Percent to filter by percentage. Click on Sum to filter by sum.
As you have count of EmployeeID, click Items.
In the fourth box, click on the field Count of EmployeeID.
Click OK.

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

The top seven values by count of EmployeeID will be displayed in the PivotTable.

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

As you can observe, the highest number of hires in the fiscal year is that of Production Technicians and a predominant number of these are in Qtr1.

Filtering Using Timeline

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

If your PivotTable has a date field, you can filter the PivotTable using Timeline.

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

Create a PivotTable from the Employee Data that you used earlier and add the data to the Data Model in the Create PivotTable dialog box.

Drag the field Title to ROWS area.
Drag the field EmployeeID to ∑ VALUES area and choose Count for calculation.

Click on the PivotTable.
Click the INSERT tab.
Click Timeline in the Filters group. The Insert Timelines dialog box appears.

Check the box HireDate.
Click OK. The Timeline appears in the worksheet.
Timeline Tools appear on the Ribbon.

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

As you can observe, All Periods – in Months are displayed on the Timeline.

Click on the arrow next to - MONTHS.
Select QUARTERS from the drop-down list. The The Timeline display changes to All Periods – in Quarters.

Click on 2014 Q1.
Keep the Shift key pressed and drag to 2014 Q4. The Timeline Period is selected to Q1 – Q4 2014.
PivotTable is filtered to this Timeline Period.

Clearing the Filters

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

You might have to clear the filters you have set from time to time to switch across different combinations and projections of your data. You can do this in several ways as follows −

Clearing all the filters in a PivotTable

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

You can clear all the filters set in a PivotTable at one go as follows −

Click the HOME tab on the Ribbon.
Click Sort & Filter in the Editing group.
Select Clear from the dropdown list.

Clearing a Label, Date or Value Filter

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

To clear a Label, Date, or Value Filter do the following −

Click on the icon in the Row Labels or Column Labels.
Click on the <field name> from which you want to clear the filter in the Select Field box in the dropdown list.
Click on Clear Filter From <Filed Name> that appears in the dropdown list.
Click OK. The specific filter will be cleared.

Filtering data using Slicers

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

Using one or more slicers is a quick and effective way to filter your data. Slicers can be inserted for each of the fields that you want to filter. Slicer will have buttons denoting the values of the field that it represents. You can click on the buttons of a slicer to select/ unselect the values in the field.

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

Slicers stay visible with the PivotTable and so you will always know what fields are used for filtering and what values in those fields are shown or hidden in the filtered PivotTable.

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

To understand the usage of slicers, consider the example of sales data region-wise, month wise and salesperson-wise. Assume you have the following PivotTable with this data.

Inserting Slicers

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

Suppose you want to filter this PivotTable based on the fields – Region and Month.

Click on ANALYZE under PIVOTTABLE TOOLS on the Ribbon.
Click on Insert Slicer in the Filter group. The Insert Slicers dialog box appears. It contains all the fields from your data table.
Check the boxes Region and Month.
Click OK.

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

Slicers for each of the selected fields appear with all the values selected by default. Slicer Tools appear on the Ribbon to work on the Slicer settings, look and feel.

Filtering with Slicers

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

As you can observe, each slicer has all the values of the field that it represents and the values are displayed as buttons. By default, all the values of a field are selected and hence all the buttons are highlighted.

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

Suppose you want to display the PivotTable only for the regions South and West and for the Months February and March.

Click on South in the Slicer for Region. Only South will be highlighted in the Slicer – Region.
Keep Ctrl key pressed and click on West in the Slicer for Region.
Click on February in the Slicer for Month.
Keep Ctrl key pressed and click on March in the Slicer for Month.

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

Selected items in the Slicers are highlighted. PivotTable with summarized values for the selected items will be displayed.

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

To add/remove values of a field from the filter, keep the Ctrl key pressed and click on those buttons in the slicer of the field.

Clearing the Filter in a Slicer

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

To clear the filter in a slicer, click on at the top-right corner of the slicer.

Removing a Slicer

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

Suppose you want to remove the slicer for the Region field.

Right click on the Slicer – Region.
Click on Remove “Region” in the dropdown list.

Slicer Tools

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

Once you insert a slicer, Slicer Tools appear on the Ribbon with OPTIONS tab. To view Slicer Tools, click on a slicer.

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

As you can observe, under the Slicer Tools – OPTION tab, you have several options to change the look and feel of the slicer that include −

Slicer Caption
Slicer Settings
Report Connections
Selection Pane

Slicer Caption

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

You can find the Slicer Caption box in the Slicer group. The Slicer Caption is the header that is displayed on the slicer. By default, Slicer Caption is the name of the field that it represents.

Click on the Slicer for Region.
Click the OPTIONS tab on the Ribbon.

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

The Slicer group on the Ribbon, in the Slicer Caption box, Region is displayed as the header of the slicer. It is the name of the field for which the slicer is inserted. You can change the Slicer Caption as follows −

Click on the Slicer Caption box in the Slicer group on the Ribbon.
Delete Region. The box is cleared.
Type Location in the box and press Enter. The Slicer Caption changes to Location and the same is reflected as header in the slicer.

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

Note − You have changed only the slicer caption, i.e. the header. The name of the field that the slicer represents – Region remains as it is.

Slicer Settings

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

You can use Slicer Settings to change the name of the slicer, change the slicer caption, choose whether to display the slicer header or not and set the sorting and filtering options for the items −

Click on the slicer - Location.
Click the OPTIONS tab on the Ribbon. You can find the Slicer Settings in the Slicer group on the Ribbon. You can also find Slicer Settings in the dropdown list when you right click on the slicer.
Click the Slicer Settings. The Slicer Settings dialog box appears.

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

As you can observe, the following are fixed for the slicer −

Source Name.
Name to use in formulas.

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

You can change the following for the slicer −

Name.
Header – Caption.
Display header.
Sorting and Filtering options for the items displayed on the slicer.

Report Connections

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

You can connect different PivotTables to a Slicer, provided one of the following holds good −

The PivotTables are created using the same data.
One PivotTable has been copied and pasted as an additional PivotTable.
Multiple PivotTables are created on separate sheets with Show Report Filter Pages.

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

Consider the following PivotTables that are created from the same data −

Name the top PivotTable as PivotTable-Top and the bottom one as PivotTable-Bottom.
Click on the top PivotTable.
Insert a Slicer for the field Region.
Select East and North on the Slicer.

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

Observe that the filtering is applied only to the top PivotTable and not to the bottom PivotTable. You can use the same slicer for both the PivotTables by connecting it to the bottom PivotTable also as follows −

Click on the slicer - Region. The SLICER TOOLS appear on the Ribbon.
Click the OPTIONS tab on the Ribbon.

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

You will find Report Connections in the Slicer group on the Ribbon. You can also find Report Connections in the dropdown list when you right click on the slicer.

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

Click Report Connections in the Slicer group.

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

The Report Connections dialog box appears. The box PivotTable-Top is checked and other boxes are unchecked. Check the box PivotTable-Bottom also and click OK.

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

The bottom PivotTable will be filtered to the selected items – East and North.

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

This became possible because both the PivotTables are now connected to the slicer. If you make changes in the selections in the slicer, the same filtering will appear in both the PivotTables.

Selection Pane

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

You can switch the display of the slicers on the worksheet off and on using the Selection Pane.

Click on the slicer - Location.
Click the OPTIONS tab on the Ribbon.
Click the Selection Pane in the Arrange group on the Ribbon. The Selection Pane appears on the right side of the window.

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

As you can observe, the names of all the slicers are listed in the Selection pane. On the right side of the names, you can find the visibility symbol - indicating the slicer is visible on the worksheet.

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

Click the symbol for Month. The symbol changes to the symbol , indicating that the slicer is hidden (not visible).

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

As you can observe, the slicer – Month is not shown on the worksheet. However, remember that you did not remove the slicer for Month, but you have just hidden it.

Click on the symbol for Month.
The symbol changes to the symbol , indicating that the slicer is now visible.

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

When you switch the visibility of a slicer on / off, the selection of the items in that slicer for filtering remain unaltered. You can also change the order of the slicers in the Selection pane by dragging them up/down.

Excel Pivot Tables - Nesting

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

If you have more than one field in any of the PivotTable areas, then the PivotTable layout depends on the order you place the fields in that area. This is called the Nesting Order.

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

If you know how your data is structured, you can place the fields in the required order. If you are not sure about the structure of the data, you can change the order of the fields that instantly changes the layout of the PivotTable.

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

In this chapter, you will understand the nesting order of the fields and how you can change the nesting order.

Nesting Order of the Fields

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

Consider the sales data example, where you have placed the fields in the following order −

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

As you can see, in the rows area there are two fields – salesperson and region in that order. This order of the fields is called nesting order i.e. Salesperson first and Region next.

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

In the PivotTable, the values in the rows will be displayed based on this order, as given below.

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

As you can observe, the values of the second field in the nesting order are embedded under each of the values of the first field.

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

In your data, each salesperson is associated with only one region, whereas most of the regions are associated with more than one salesperson. Hence, there is a possibility that if you reverse the nesting order, your PivotTable will look more meaningful.

Changing the Nesting Order

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

To change the nesting order of the fields in an area, just click the field and drag it to the position you want.

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

Click on the field Salesperson in the ROWS area, and drag it to below the field Region. Thus, you have changed the nesting order to – Region first and Salesperson next, as follows −

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

The resulting PivotTable will be as given below −

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

You can clearly observe that the Layout with the nesting order – Region and then Salesperson yields a better and compact report than the one with the nesting order – Salesperson and then Region.

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

In case a Salesperson represents more than one area and you need to summarize the sales by Salesperson, then the previous Layout would have been a better option.

Excel Pivot Tables - Tools

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

In the worksheet containing a PivotTable, the Ribbon will contain the PivotTable Tools, with ANALYZE and DESIGN Tabs. The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable. The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.

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

You will learn the ANALYZE commands in this chapter. You will learn the DESIGN commands in the Chapter - Aesthetic Reports with PivotTables.

ANALYZE Commands

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

The commands on the Ribbon of ANALYZE tab include the following −

Expanding and Collapsing a Field.
Grouping and Ungrouping Field Values.
Active Field Settings.
PivotTable Options.

Expanding and Collapsing a Field

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

If you have nested fields in your PivotTable, you can expand and collapse a single item or you can expand and collapse all the items of the active field.

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

Consider the following PivotTable, wherein you have Salesperson field nested under Region field.

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

Click the symbol to the left of East. The item East of the field Region will collapse.

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

As you can observe, the other items - North, South and West of the field Region are not collapsed. If you want to collapse any of them, repeat the steps that you have done for East.

Click on the symbol to the left of East. The item East of the field Region will expand.

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

If you want to collapse all the items of a field at once, do the following −

Click any of the items of the field – Region.
Click the ANALYZE tab on the Ribbon.
Click Collapse Field in the Active Field group.

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

All the items of the field Region will be collapsed.

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

If you want to expand all the items of a field at once, do the following −

Click on any of the items of the field – Region.
Click the ANALYZE tab on the Ribbon.
Click Expand Field in the Active Field group.

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

All the items of the field Region will be expanded.

Grouping and Ungrouping Field Values

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

You can group and ungroup field values to define your own clustering. For example, you might want to know the data combining East and North regions.

Select the East and North items of the Region field in the PivotTable, along with the nested Salesperson field items.
Click the ANALYZE tab on the Ribbon.
Click Group Selection in the group – Group.

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

The items – East and North will be grouped under the name Group1. In addition, a new South is created under which South is nested and a new West is created under which West is nested.

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

You can also observe that a new field – Region2 is added in the PivotTable Fields list, which appears in the ROWS area.

Select the South and West items of the Region2 field in the PivotTable, along with the nested Region and Salesperson field items.
Click the ANALYZE tab on the Ribbon.
Click Group Selection in the group – Group.

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

The items – South and West of the field Region will be grouped under the name Group2.

若要取消分组，请执行以下操作：

To ungroup a group, do the following −

Click on the Group Name.
Click the ANALYZE tab.
Click Ungroup in the group – Group.

Grouping by a Date Field

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

Consider the following PivotTable, wherein you have the employee data summarized by Count of EmployeeID, hiredate wise and title wise.

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

Suppose you want to group this data by the HireDate field that is a Date field into years and quarters.

Click on a Date item in the PivotTable.
Click the ANALYZE tab on the Ribbon.
Click Group Field in the group – Group.

将出现“分组”对话框。

The Grouping dialog box appears.

Set the dates for – Starting at and Ending at.
Select Quarters and Years in the box under By. To select / deselect multiple items, keep the Ctrl-key pressed.
Click OK.

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

The HireDate field values will be grouped into Quarters, nested in Years.

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

If you want to ungroup this grouping, you can do as shown earlier, by clicking Ungroup in the group – Group on the Ribbon.

Active Value Field Settings

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

You can set a field options by clicking on a value of that field. Consider the example of sales data that we used earlier in this chapter.

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

Suppose you want to set the options for the Region field.

Click on East. On the Ribbon, in the Active Field group, in the Active Field box, Region will be displayed.
Click on Field Settings. The Field Settings dialog box appears.

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

You can set your preferences for the field – Region.

PivotTable Options

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

You can set the PivotTable Options according to your preferences.

Click on the PivotTable.
Click the ANALYZE tab.
Click Options in the PivotTable group.

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

The PivotTable Options dialog box appears. You can set your preferences in the dialog box.

Excel Pivot Tables - Summarizing Values

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

You can summarize a PivotTable by placing a field in ∑ VALUES area in the PivotTable Fields Task pane. By default, Excel takes the summarization as sum of the values of the field in ∑ VALUES area. However, you have other calculation types, such as, Count, Average, Max, Min, etc.

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

In this chapter, you will learn how to set a calculation type based on how you want to summarize the data in the PivotTable.

Sum

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

Consider the following PivotTable wherein you have the summarized sales data regionwise, salesperson-wise and month-wise.

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

As you can observe, when you drag the field Order Amount to ∑ VALUES area, it is displayed as Sum of Order Amount, indicating the calculation is taken as Sum. In the PivotTable, in the top-left corner, Sum of Order Amount is displayed. Further, Grand Total column and Grand Total row are displayed for subtotals field-wise in rows and columns respectively.

Value Field Settings

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

With Values Field Settings, you can set the calculation type in your PivotTable. You can also decide on how you want to display your values.

Click on Sum of Order Amount in ∑ VALUES area.
Select Value Field Settings from the dropdown list.

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

The Value Field Settings dialog box appears.

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

The Source Name is the field and Custom Name is Sum of field. Calculation Type is Sum. Click the Show Values As tab.

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

In the box Show Values As, No Calculation is displayed. Click the Show Values As box. You can find several ways of showing your total values.

% of Grand Total

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

You can show the values in the PivotTable as % of Grand Total.

In the Custom Name box, type % of Grand Total.
Click on the Show Values As box.
Click on % of Grand Total in the dropdown list. Click OK.

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

The PivotTable summarizes the values as % of the Grand Total.

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

As you can observe, Sum of Order Amount in the top-left corner of the PivotTable and in the ∑ VALUES area in the PivotTable Fields pane is changed to the new Custom Name - % of Grand Total.

Click on the header of the Grand Total column.
Type % of Grand Total in the formula bar. Both the Column and Row headers will change to % of Grand Total.

% of Column Total

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

Suppose you want to summarize the values as % of each month total.

Click on Sum of Order Amount in ∑ VALUES area.
Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears.
In the Custom Name box, type % of Month Total.
Click on the Show values as box.
Select % of Column Total from the dropdown list.
Click OK.

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

The PivotTable summarizes the values as % of the Column Total. In the Month columns, you will find the values as % of the specific month total.

Click on the header of the Grand Total column.
Type % of Column Total in the formula bar. Both the Column and Row headers will change to % of Column Total.

% of Row Total

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

You can summarize the values as % of region totals and % of salesperson totals, by selecting % of Row Total in Show Values As box in the Value Field Settings dialog box.

Count

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

Suppose you want to summarize the values by the number of Accounts region wise, salesperson wise and month wise.

Deselect Order Amount.
Drag Account to ∑ VALUES area. The Sum of Account will be displayed in the ∑ VALUES area.
Click on Sum of Account.
Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears.
In the Summarize value field by box, select Count. The Custom Name changes to Count of Account.
Click OK.

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

The Count of Account will be displayed as shown below −

Average

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

Suppose you want to summarize the PivotTable by average values of Order Amount region wise, salesperson wise and month wise.

Deselect Account.
Drag Order Amount to ∑ VALUES area. The Sum of Order Amount will be displayed in the ∑ VALUES area.
Click on Sum of Order Amount.
Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.
In the Summarize value field by box, click on Average. The Custom Name changes to Average of Order Amount.
Click OK.

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

The average will be displayed as shown below −

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

You have to set the number format of the values in the PivotTable to make it more presentable.

Click on Average of Order Amount in ∑ VALUES area.
Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.
Click on the Number Format button.

格式单元格对话框出现。

The Format Cells dialog box appears.

Click on Number under Category.
Type 2 in the Decimal places box and click OK.

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

The PivotTable values will be formatted to numbers with two decimal places.

Click on the header of the Grand Total column.
Type Average Order Amount in the formula bar. Both the Column and Row headers will change to Average Order Amount.

Max

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

Suppose you want to summarize the PivotTable by the maximum values of Order Amount region-wise, salesperson-wise and month-wise.

Click on Sum of Order Amount.
Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears.
In the Summarize value field by box, click Max. The Custom Name changes to Max of Order Amount.

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

The PivotTable will display the maximum values region wise, salesperson wise and month wise.

Click on the header the Grand Total column.
Type Max Order Amount in the formula bar. Both the Column and Row headers will change to Max Order Amount.

Min

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

Suppose you want to summarize the PivotTable by the minimum values of Order Amount region wise, salesperson wise and month wise.

Click on Sum of Order Amount.
Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.
In the Summarize value field by box, click Min. The Custom Name changes to Min of Order Amount.

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

The PivotTable will display the minimum values region wise, salesperson wise and month wise.

Click on the header of the Grand Total column.
Type Min Order Amount in the formula bar. Both the Column and Row headers will change to Min Order Amount.

Excel Pivot Tables - Updating Data

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

You have learnt how to summarize data with a PivotTable. The data on which the PivotTable is based might be updated either periodically or on occurrence of an event. Further, you also might require to change the PivotTable Layout for different reports.

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

In this chapter, you will learn the different ways of updating the Layout and / or refreshing the data in a PivotTable.

Updating PivotTable Layout

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

You can decide whether your PivotTable is to be updated whenever you make changes to the layout or it is to be updated by a separate trigger.

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

As you have learnt earlier, in the PivotTable Fields task pane, on the bottom side, you will find a check box for Defer Layout Update. By default, it is unchecked, which means the PivotTable Layout gets updated as soon as you make changes in the PivotTable areas.

檢查選項− Defer Layout Update 。

Check the option − Defer Layout Update.

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

The UPDATE button next to it will be enabled. If you make any changes to the PivotTable areas, the changes will be reflected only after you click on the UPDATE button.

Refreshing PivotTable Data

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

When the data of a PivotTable is changed in its source, the same can be reflected in the PivotTable by refreshing it.

Click on the PivotTable.
Click the ANALYZE tab on the Ribbon.
Click Refresh in the Data group.

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

There are different options to refresh the data in the dropdown list −

Refresh − To get the latest data from the source connected to the active cell.
Refresh All − To get the latest data by refreshing all sources in the workbook.
Connection Properties − To set the refresh properties for the workbook connections.

Changing the Source Data of a PivotTable

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

You can change the range of the source data of a PivotTable. For e.g., you can expand the source data to include more number of rows of data.

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

However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable.

Click on the PivotTable. PIVOTTABLE TOOLS appear on the Ribbon.
Click the ANALYZE tab.
Click Change Data Source in the Data group.

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

Select Change Data Source from the dropdown list.

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

Change PivotTable Data Source dialog box appears and the current Data Source will be highlighted.

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

Select the Table or the Range you want to include in the Table/Range Box under Select a Table or Range. Click OK.

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

The data source for the PivotTable will be changed to the selected Table/Range of data.

Changing to External Data Source

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

If you want to change the data source for your PivotTable that is an external one, it might be best to create a new PivotTable. However, if the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current data connection to reflect the same.

Click on the PivotTable.
Click the ANALYZE tab on the Ribbon.
Click Change Data Source in the Data group. The Change PivotTable Data Source dialog box appears.
Click the Choose Connection button.

Existing Connections 对话框随即显示。

The Existing Connections dialog box appears.

Select All Connections in the Show box. All the Connections in your Workbook will be displayed.
Click the Browse for More button.

Select Data Source 窗口随即显示。

The Select Data Source window appears.

Click on the New Source button.
Go through the Data Connection Wizard Steps.

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

If your data source is in another Excel workbook, do the following −

Click on the File name box.
Select the workbook file name.

Deleting a PivotTable

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

You can delete a PivotTable as follows −

Click on the PivotTable.
Click the ANALYZE tab on the Ribbon.
Click Select in the Actions group.

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

Select Entire PivotTable from the dropdown list. The entire PivotTable will be selected.

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

Press the Delete Key. The PivotTable will be deleted.

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

If the PivotTable is on a separate worksheet, you can also delete the PivotTable by deleting the entire worksheet.

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

Right-click on the worksheet tab and select Delete from the dropdown list.

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

The entire worksheet along with the PivotTable is deleted.

Excel Pivot Tables - Reports

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

Major use of PivotTable is reporting. Once you have created a PivotTable, explored the data by arranging and rearranging the fields in its rows and columns, you will be ready to present the data to a wide range of audience. With filters, different summarizations, focusing on specific data, you will be able to generate several required reports based on a single PivotTable.

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

As a PivotTable report is interactive, you can quickly make the necessary changes to highlight the specific results, such as data trends, data summarizations, etc. while presenting it. You can also provide visual cues such as report filters, slicers, timeline, PivotCharts, etc. to the recipients so that they can visualize the details they want.

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

In this chapter, you will learn the different ways of making your PivotTable reports appealing with visual cues that enable quick exploration of the data.

Hierarchies

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

You have learnt how to nest fields to form a hierarchy, in the Chapter – Nesting in a PivotTable in this tutorial. You have also learnt how to group / ungroup data in a PivotTable in the Chapter – Using PivotTable Tools. We will take few examples to show you how to produce interactive PivotTable reports with hierarchies.

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

If you have an in-built structure for the fields in your data, such as, Year-Quarter-Month, nesting the fields to form a hierarchy will enable you to quickly expand/collapse fields to view the summarized values at the required level.

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

For example, suppose you have the sales data for the fiscal year 2015-16 for the regions – East, North, South and West, as given below.

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

Create a PivotTable as shown below.

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

As you can observe, this is a comprehensive way of reporting the data using the nested fields as a hierarchy. If you want to display the results only at the level of Quarters, you can quickly collapse the Quarter field.

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

Suppose you have a Date field in your data as shown below.

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

In such a case, you can group the data by the Date field as follows −

创建数据透视表。

Create a PivotTable.

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

As you can observe, this PivotTable is not convenient to highlight significant data.

Group the PivotTable by Date field. (You have learnt grouping in the Chapter – Exploring Data with PivotTable Tools in this tutorial).
Place the Salesperson field in Filters area.
Filter the Column labels to East Region.

Report Filter

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

Suppose you want a report for each Salesperson separately. You can do it as follows −

Ensure that you have Salesperson field in Filters area.
Click on the PivotTable.
Click the ANALYZE tab on the Ribbon.
Click the arrow next to Options in the PivotTable group.
Select Show Report Filter Pages from the dropdown list.

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

The Show Report Filter Pages dialog box appears. Select the field Salesperson and click OK.

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

A separate worksheet for each of the values of the Salesperson field is created, with the PivotTable filtered to that value.

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

The worksheet will be named by the value of the field, which is visible on the tab of the worksheet.

Slicers

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

Another sophisticated feature that you have in PivotTables is Slicer that can be used to filter the fields visually.

Click on the PivotTable.
Click the ANALYZE tab.
Click Insert Slicer in the Filter group.
Click Order Date, Quarters and Years in the Insert Slicers dialog box. Three Slicers –Order Date, Quarters and Year will get created.
Adjust the sizes of the slicers, adding more columns for the buttons on the slicers.
Create Slicers for Salesperson and Region fields also.
Choose the Slicer Styles so that date fields are grouped to one color and the other two fields get different colors.
Deselect Gridlines.

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

As you can see, you have not only an interactive report, but also an appealing one, that can be understood easily.

Timeline in PivotTable

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

When you have a Date field in your PivotTable, inserting a Timeline also is an option to produce an aesthetic report.

Create a PivotTable with Salesperson in ROWS area and Region in COLUMNS area.
Insert a Timeline for the field Order Date.
Filter the Timeline to show 5 months data, from November 2015 to March 2016.

DESIGN Commands

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

The PIVOTTABLE TOOLS - DESIGN commands on the Ribbon provide you with the options to format a PivotTable, including the following −

Layout
PivotTable Style Options
PivotTable Styles

Layout

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

You can have PivotTable Layout based on your preferences for the following −

Subtotals
Grand Totals
Report Layout
Blank Rows

PivotTable Layout – Subtotals

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

You have an option whether to display Subtotals or not. By default, Subtotals are displayed, at the top of the group.

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

As you can observe the highlighted group – East, the subtotals are at the top of the group. You can change the position of subtotals as follows −

Click on the PivotTable.
Click the DESIGN tab on the Ribbon.
Click Subtotals in the Layout Options group.
Click Show all Subtotals at Bottom of Group.

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

The Subtotals will now appear at the bottom of each group.

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

If you do not have to report the Subtotals, you can select - Do Not Show Subtotals.

Grand Totals

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

You can choose to either display Grand Totals or not. You have four possible combinations −

Off for Rows and Columns
On for Rows and Columns
On for Rows Only
On for Columns Only

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

By default, it is the second combination – On for Rows and Columns.

Report Layout

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

You can choose from the several Report Layouts, the one that best suits your data.

Compact Form.
Outline Form.
Tabular Form.

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

You can also choose whether to repeat all the item labels or not, in case of multiple occurrences.

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

The default Report Layout is the Compact form that you are familiar with.

Compact Form

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

The Compact form optimizes the PivotTable for readability. The other two forms display the field headers also.

单击大纲格式中的 Show 。

Click on Show in Outline Form.

单击表格格式中的 Show 。

Click Show in Tabular Form.

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

Consider the following PivotTable Layout, wherein the field Month is nested under the field Region −

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

As you can observe, the Month labels are repeated and this is the default.

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

Click Do Not Repeat Item Labels. The Month labels will be displayed only once and the PivotTable looks clear.

Blank Rows

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

To make your PivotTable Report more distinct, you can insert a blank line after each item. You can remove these Blank Lines anytime later.

单击 Insert Blank Line after Each Item 。

Click Insert Blank Line after Each Item.

PivotTable Style Options

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

You have the following PivotTable Style Options −

Row Headers
Column Headers
Banded Rows
Banded Columns

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

By default, the boxes for Row Headers and Column Headers are checked. These options are for displaying special formatting for the first row and the first column respectively. Check the box Banded Rows.

勾选带状列框。

Check the box Banded Columns.

PivotTable Styles

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

You can choose several PivotTable Styles. Select the one that suits your report. For example, if you choose Pivot Style Dark 5, you will get the following style for the PivotTable.