Excel Pivot Tables 简明教程

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 −

  1. ROWS.

  2. COLUMNS.

  3. FILTERS.

  4. ∑ VALUES (Read as Summarizing Values).

pivottable areas

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

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

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

With PivotTable Areas, you can choose −

  1. What fields to display as rows (ROWS area).

  2. What fields to display as columns (COLUMNS area).

  3. How to summarize your data (∑ VALUES area).

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

  1. Drag the field Salesperson to ROWS area.

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

rows

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.

column

  1. Drag the field Month from ROWS to COLUMNS.

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

columns row

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

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.

  1. Drag the field Order Amount to ∑ VALUES.

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

sigma values

正如您看到的，数据已按区域、销售人员和月份汇总。您会看到按月划分的每个区域的子计。您还可以在 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.

filters

正如您看到的，(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.

  1. Click on the arrow to the right of filter.

  2. Check the box – Select Multiple Items.

all

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

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

  1. Check the boxes – North and South.

  2. Clear the other boxes. Click OK.

check boxes

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

The PivotTable gets changed to reflect the filtered data.

reflect

你可以观察到，筛选器显示 (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.