数据透视表和数据透视图提供了多种计算方式。数据字段用汇总函数来合并基本源数据中的数值。还可用自定义计算比较数据值或添加使用报表元素或其他工作表数据的公式。

数据透视表和数据透视图如何汇总数据

源数据

数据区域中的数值可对报表中的基本源数据进行汇总。

根据上面的源数据创建的数据透视表

“月份”列字段提供了“三月”和“四月”两个项。“地区”行字段提供了“东部”、“南部”、“西部”和“北部”四个项。“四月”所在列和“北部”所在行交汇处的数值就是“月份”值为“四月”而“地区”值为“北部”的源数据中记录的总销售额。

根据上面的数据透视表示例创建的数据透视图

在数据透视图报表中，“地区”字段是分类字段，它将“北部”、“南部”、“东部”和“西部”项目显示为分类。而“月份”字段是系列字段，它将“三月”、“四月”和“五月”显示为图例中的系列。“求和项：销售额”字段包含代表每个地区每月总收入的数据标志。例如，相应的数据标志将在数值轴上标示出北部地区四月份的总销售额。

数据透视图中的值和计算将影响其相关联的数据透视表中的值和计算，反之亦然。

自定义计算      自定义计算显示根据数据区域的其他项或单元格得到的数值。例如，可将“求和项：销售额”数据字段中的数值作为“三月”销售额的百分数显示，或者作为“月份”字段中的项的运行汇总显示。

公式      如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段和计算项中创建所需公式。例如，可以对销售年金添加一个带公式的计算项。然后该报表将自动在分类汇总和总计中包含年金值。

源数据类型对计算的影响方式

报表中可用的计算和选项依赖于源数据是来自 OLAP 数据库还是其他类型的数据库。

OLAP 源数据      对于通过 OLAP 多维数据集创建的报表而言，OLAP 服务器会在 Microsoft Excel 显示结果之前预先对汇总数值进行计算。因此，不能更改这些值在报表内部的计算方式。也不能更改用来计算数据字段或分类汇总的汇总函数，或添加计算字段或计算项。 如果 OLAP 服务器可提供计算字段（称作计算成员），则可在数据透视表的字段列表中查看这些字段。还可查看任何一个由宏创建的计算字段和计算项（该宏在 Visual Basic for Applications 中编辑，并存储在工作簿中），但是不能更改这些字段或项。如果需要其他计算类型，请与 OLAP 数据库管理员联系。

源数据的其他类型      在根据其他类型的外部数据或工作表数据创建的报表中，Microsoft Excel 使用 Sum 汇总函数计算含有数字数据的数据字段，使用 Count 汇总函数计算含有文本的数据字段。您可以选择其他的汇总函数来进一步分析和自定义数据，如 Average、Max 或 Min 等。通过在字段中创建计算字段或计算项，还可以创建使用报表或其他工作表数据的自定义公式。

汇总中的隐藏项      对 OLAP 源数据而言，当计算分类汇总和总计时，可包含或排除隐藏项的数值。对于其他类型的源数据，在默认情况下，已排除了隐藏项的数值，但是可选择是否包括页字段的隐藏项。

公式语法

只能在不是基于 OLAP 源数据的报表中创建公式。

数据透视图可以使用公式，且其语法与数据透视表中相同。为了在数据透视图中获得最佳效果，请在与其相关联的数据透视表中创建并编辑公式（在该数据透视表中可查看构成数据的单个数值），然后在数据透视图中查看结果。

公式元素       像其他工作表公式一样，在为计算字段和计算项创建的公式中，也可以使用运算符和表达式。还可以使用常量并引用报表中的数据，但不能使用单元格引用和定义的名称，也不能使用以单元格引用或定义的名称为参数的工作表函数，还不能使用数组函数。

报表中的名称      Microsoft Excel 可提供名称来识别公式中的报表元素。名称由字段和项的名称组成。在下面的例子中，区域 C3:C9 中的数据被命名为“奶制品”。

在数据透视图中，字段名称显示在字段按钮中，而项目名称则在每一字段的下拉列表中。不要将这些名称与图表提示中的信息相混淆，它们只用于反映系列和数据点的名称。

示例      名为“预测”的计算字段可用如下公式预测未来的订货情况：

=销售额 * 1.2

在“种类”字段中，可以使用如下公式根据“奶制品”的销售情况估计新产品的销售情况：

=奶制品 * 115%

公式对求和汇总进行操作，而不是针对单个记录      计算字段中公式的操作对象是公式中任意字段的源数据的和。例如，公式 =销售额 * 1.2 是指将每类产品和区域的销售额的和乘以 1.2，并不是将每笔独立的销售额都乘以 1.2，然后再将所得的乘积结果相加。然而，计算项中公式的操作对象则是独立的记录，例如，计算项公式 =奶制品 * 115% 将“奶制品”的每笔销售额都乘以 115%，然后再将所得乘积在数据区域中相加。

名称中的空格、数字和符号      在包含多个字段的名称中，字段可按任何顺序排列。在上面的例子中，单元格区域 C6:D6 可以是“北部四月”或“四月北部”。在包含多个词、数字或符号的名称中可使用单引号。

汇总      公式中不能引用汇总（如本例中的“三月份汇总”、“四月份汇总”和“总计”）。

数据项引用中的字段名称      可以在对数据项的引用中包含字段名称。数据项的名称必须用中括号括起来，例如“地区[北部]”。当报表中两个不同字段中的两个数据项有同样的名称时，使用这种格式可以避免 #NAME 错误。例如，如果报表中的“种类”字段中有一个叫“肉类”的数据项，“分类”字段中也有一个叫“肉类”的数据项，用“类别[肉类]”和“分类[肉类]”的形式引用数据项可以防止 #NAME 错误。

按位置引用数据项      在报表中，可以根据当前排序和数据项显示的位置来引用数据项。“种类[1]”是“奶制品”，而“种类[2]”是“海产品”。当数据项的位置发生变化，或者显示或隐藏不同的数据项时，用这种方式引用的数据项可随之更改。

可用相对位置来引用数据项。该位置是由与其相关的包含公式的计算项决定的。如果“南部”是当前地区，则“地区[-1]”就是“北部”；如果“北部”是当前地区，则“地区[+1]”就是“南部”。例如，计算项可使用公式 =地区[-1] * 3%。如果所给的位置在字段的第一项之前或最后一项之后，则公式将产生 #REF! 错误。

在计算数据项公式中，如果根据绝对或相对位置引用数据项，那么“数据透视表‘排序并列出前 10 个’”或“数据透视表字段高级选项”对话框中的“自动显示前 10 项”和“自动排序选项”下的任何选项都将被重置成“关闭”或“手动”，从而这些选项都将不可用。

使用数据透视图中的公式

在数据透视图中创建公式的方法和规则与在数据透视表中创建公式的方法相同。当您在数据透视图中创建计算字段或计算项时，这些计算会反映在相关联的数据透视表上，反之亦然。为了获得最佳效果，请在相关联的数据透视表（在其中可查看构成数据的单个数值）中为数据透视图创建公式，然后在数据透视图中查看图形化的结果。

例如，下面的数据透视图显示了每个地区各个推销员的销售额：

若要了解销售额增长百分之十后的情况，您可以创建使用下面公式的计算字段：

=销售额 * 110%

计算结果将反映在图表中：

若要查看北部地区的销售额减去百分之八的运输费后所得到的独立数据标志，可在“地区”字段中使用以下公式创建一个计算项：

=北部 – (北部 * 8%)

结果如下所示：

但是，在“销售人员”字段中所创建的计算项将显示为图例中所代表的数据系列，同时在图表中显示为每个分类中的数据点。