Excel Pivot Tables 简明教程

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.

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

  2. If you select this, the changes in your selections are not updated until you click on the UPDATE button.

update

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

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 −

  1. Click the ANALYZE tab under PIVOTTABLE TOOLS on the Ribbon.

  2. Check if Fields List is selected (i.e. highlighted) in the Show group.

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

pivottables task pane

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.

moving pivottables task pane

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

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

  1. Click Move in the dropdown list. The button appears on the Task Pane.

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

labels

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

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

taskpane

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 −

  1. Click on Task Pane Options − that is on the right side of the title - PivotTable Fields.

  2. Click on Size in the dropdown list.

  3. Use the symbol ⇔ to increase / decrease the width of the Task Pane.

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

resizing

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.

pivottable fields

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

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.

  1. Click on the Tools button.

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

In the dropdown list, you will find the following −

  1. Five different layout options for Fields and Areas.

  2. Two options for Sort order of the fields in the Fields list − Sort A to Z. Sort in Data Source Order.

tools

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

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 −

data source order