Excel Pivot Tables 简明教程

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 −

  1. Expanding and Collapsing a Field.

  2. Grouping and Ungrouping Field Values.

  3. Active Field Settings.

  4. PivotTable Options.

commands

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.

expanding

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

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

collapsing

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

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

  1. Click any of the items of the field – Region.

  2. Click the ANALYZE tab on the Ribbon.

  3. Click Collapse Field in the Active Field group.

collapse all items

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

All the items of the field Region will be collapsed.

collapsed

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

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

  1. Click on any of the items of the field – Region.

  2. Click the ANALYZE tab on the Ribbon.

  3. Click Expand Field in the Active Field group.

expand all items

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

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.

  1. Select the East and North items of the Region field in the PivotTable, along with the nested Salesperson field items.

  2. Click the ANALYZE tab on the Ribbon.

  3. Click Group Selection in the group – Group.

grouping

项目——“东部”和“北部”将被分组在一个名为“组 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.

group1

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

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

  1. Select the South and West items of the Region2 field in the PivotTable, along with the nested Region and Salesperson field items.

  2. Click the ANALYZE tab on the Ribbon.

  3. Click Group Selection in the group – Group.

group selection

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

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

group2

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

To ungroup a group, do the following −

  1. Click on the Group Name.

  2. Click the ANALYZE tab.

  3. Click Ungroup in the group – Group.

ungroup

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.

title wise

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

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

  1. Click on a Date item in the PivotTable.

  2. Click the ANALYZE tab on the Ribbon.

  3. Click Group Field in the group – Group.

quarters

将出现“分组”对话框。

The Grouping dialog box appears.

  1. Set the dates for – Starting at and Ending at.

  2. Select Quarters and Years in the box under By. To select / deselect multiple items, keep the Ctrl-key pressed.

  3. Click OK.

grouping dialog box

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

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

grouped into quarters

如果您想取消此分组，可以通过单击组中的 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.

active value

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

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

  1. Click on East. On the Ribbon, in the Active Field group, in the Active Field box, Region will be displayed.

  2. Click on Field Settings. The Field Settings dialog box appears.

field settings

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

You can set your preferences for the field – Region.

PivotTable Options

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

You can set the PivotTable Options according to your preferences.

  1. Click on the PivotTable.

  2. Click the ANALYZE tab.

  3. Click Options in the PivotTable group.

analyze

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

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

options dialog box