数据透视表和数据交叉表_使用数据透视表小计节省空间

数据透视表和数据交叉表

When you summarize data in a pivot table, it usually shows a sum of the values. (If there are blanks or text values in the field, usually the pivot table shows a count instead.)

当您汇总数据透视表中的数据时，通常会显示值的总和。 (如果该字段中有空格或文本值，则通常数据透视表会显示一个计数。)

In this pivot table, you can see the total labour cost for each Service Type.

在此数据透视表中，您可以查看每种服务类型的总人工成本。

If there are two or more fields in the Row or Column area, subtotals are automatically created for all the fields except the last one.

如果“行”或“列”区域中有两个或多个字段，则会自动为除最后一个字段以外的所有字段创建小计。

In the screenshot below, the District field was added below the Service Type field. Subtotals were automatically added to Service Type.

在下面的屏幕快照中，在“服务类型”字段下方添加了“地区”字段。 小计已自动添加到服务类型。

更改摘要功能 (Change the Summary Function)

Instead of seeing the Sum of the data, you can change the summary function, and show the Average, or any of the other options. You could even put the same field in the Values area of a pivot table multiple times, and use different summary functions in each column.

您可以更改摘要功能 ，并显示“平均值”或任何其他选项，而不是查看数据的总和。 您甚至可以多次将同一个字段放在数据透视表的“值”区域中，并在每列中使用不同的汇总函数。

In this example, the sum of the labour cost is shown in column C, and the Max function is used in column D.

在此示例中，人工成本之和显示在C列中，而Max函数用于D列。

更改小计汇总功能 (Change the Subtotal Summary Function)

If your pivot table already has lots of columns, you might not want to make it wider, by adding another copy of one of the Value fields. Instead, you can change the summary function for the Subtotal, so it uses a different function than the Value fields.

如果数据透视表已经有很多列，则可能不希望通过添加“值”字段之一的另一个副本来使其更宽。 相反，您可以更改小计的汇总功能，因此它使用与“值”字段不同的功能。

Now the Value fields show the sum of labour costs, and the subtotal for each service type shows the highest labour cost that was charged for that service. You can see the maximums, without adding extra columns to the pivot table.

现在，“值”字段显示了人工成本的总和，每种服务类型的小计显示了对该服务收取的最高人工成本。 您可以看到最大值，而无需在数据透视表中添加额外的列。

数据透视表小计的更多信息 (More Info on Pivot Table Subtotals)

You can read more about pivot table subtotals, and the steps for changing them, on the Contextures website: Excel Pivot Table Subtotals.

您可以在Contextures网站上阅读有关数据透视表小计及其更改步骤的更多信息： Excel数据透视表小计 。

观看数据透视表小计视频 (Watch the Pivot Table Subtotals Video)

To see the steps for changing the pivot table subtotals, and creating multiple subtotals, you can watch this short video.

要查看更改数据透视表小计并创建多个小计的步骤，您可以观看此简短视频。

演示地址

翻译自: https://contexturesblog.com/archives/2010/04/19/save-space-with-pivot-table-subtotals/

数据透视表和数据交叉表