Excel Pivot Tables 简明教程

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.

sum

如您所见，当您将“订单金额”字段拖动到∑ 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.

  1. Click on Sum of Order Amount in ∑ VALUES area.

  2. Select Value Field Settings from the dropdown list.

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

The Value Field Settings dialog box appears.

value field settings

源名称是字段，自定义名称是字段的总和。计算类型是求和。单击 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.

show values as

在“显示值”框中，显示 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.

no calculations

% of Grand Total

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

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

  1. In the Custom Name box, type % of Grand Total.

  2. Click on the Show Values As box.

  3. Click on % of Grand Total in the dropdown list. Click OK.

percentage grand total

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

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

values as percentage

正如你所能观察到的，数据透视表左上角的数据透视表字段窗格中的“订单金额总和”和“∑ 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.

  1. Click on the header of the Grand Total column.

  2. Type % of Grand Total in the formula bar. Both the Column and Row headers will change to % of Grand Total.

sum values

% of Column Total

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

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

  1. Click on Sum of Order Amount in ∑ VALUES area.

  2. Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears.

  3. In the Custom Name box, type % of Month Total.

  4. Click on the Show values as box.

  5. Select % of Column Total from the dropdown list.

  6. Click OK.

percentage column

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

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.

  1. Click on the header of the Grand Total column.

  2. Type % of Column Total in the formula bar. Both the Column and Row headers will change to % of Column Total.

total month

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

total row

Count

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

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

  1. Deselect Order Amount.

  2. Drag Account to ∑ VALUES area. The Sum of Account will be displayed in the ∑ VALUES area.

  3. Click on Sum of Account.

  4. Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears.

  5. In the Summarize value field by box, select Count. The Custom Name changes to Count of Account.

  6. Click OK.

count

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

The Count of Account will be displayed as shown below −

count of account

Average

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

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

  1. Deselect Account.

  2. Drag Order Amount to ∑ VALUES area. The Sum of Order Amount will be displayed in the ∑ VALUES area.

  3. Click on Sum of Order Amount.

  4. Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.

  5. In the Summarize value field by box, click on Average. The Custom Name changes to Average of Order Amount.

  6. Click OK.

average

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

The average will be displayed as shown below −

display average

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

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

  1. Click on Average of Order Amount in ∑ VALUES area.

  2. Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.

  3. Click on the Number Format button.

number format

格式单元格对话框出现。

The Format Cells dialog box appears.

  1. Click on Number under Category.

  2. Type 2 in the Decimal places box and click OK.

format cells

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

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

formatted

  1. Click on the header of the Grand Total column.

  2. Type Average Order Amount in the formula bar. Both the Column and Row headers will change to Average Order Amount.

grand total column

Max

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

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

  1. Click on Sum of Order Amount.

  2. Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears.

  3. In the Summarize value field by box, click Max. The Custom Name changes to Max of Order Amount.

max order

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

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

  1. Click on the header the Grand Total column.

  2. Type Max Order Amount in the formula bar. Both the Column and Row headers will change to Max Order Amount.

max

Min

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

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

  1. Click on Sum of Order Amount.

  2. Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.

  3. In the Summarize value field by box, click Min. The Custom Name changes to Min of Order Amount.

min

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

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

  1. Click on the header of the Grand Total column.

  2. Type Min Order Amount in the formula bar. Both the Column and Row headers will change to Min Order Amount.

header