Excel Pivot Tables 简明教程

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 −

creating pivottable

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

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

  1. Ensure that the first row has headers. You need headers because they will be the field names in your PivotTable.

  2. Name the data range as SalesData_Range.

  3. Click on the data range – SalesData_Range.

  4. Click the INSERT tab on the Ribbon.

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

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

tables group

在“创建数据透视表”对话框中，在 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 −

  1. Select Select a table or range.

  2. In the Table/Range box, type the range name – SalesData_Range.

  3. 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 name

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

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

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 −

  1. Click on the field Salesperson in the PivotTable Fields list and drag it to the ROWS area.

  2. Click the field Month in the PivotTable Fields list and drag that also to ROWS area.

  3. Click on Order Amount and drag it to ∑ VALUES area.

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

Your first PivotTable is ready as shown below

first pivottable

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

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 table

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 −

  1. Click on the table – SalesData_Table.

  2. Click the INSERT tab on the Ribbon.

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

insert tab

  1. Click Select a table or range.

  2. In the Table/Range box, type the table name – SalesData_Table.

  3. Select New Worksheet under Choose where you want the PivotTable report to be placed. Click OK.

sales data table

新的工作表插入到工作簿中。新的工作表包含一个空数据透视表。命名工作表 - 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 −

  1. Click on the table SalesData-Table.

  2. Click the INSERT tab.

  3. Click Recommended PivotTables in the Tables group. The Recommended PivotTables Dialog Box appears.

recommended pivottables

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

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

  1. Click on each of the PivotTable options to see the preview on the right side.

  2. 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.

preview

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

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

select pivottable

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

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.