Excel 2016教程: 在数据透视表中计算值

Excel 2016教程: 在数据透视表中计算值

在数据透视表中计算值
Microsoft 365 专属 Excel Excel 网页版 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007
在数据透视表中，可在值字段中使用汇总函数合并基础源数据中的值。 如果汇总函数和自定义计算无法提供所需结果，可在计算字段和计算项中创建自己的公式。 例如，可为计算项添加计算销售佣金的公式，销售佣金在每个地区可能有所不同。 然后，数据透视表?自动将佣金包含在分类汇总和总计中。

详细了解如何在数据透视表??中计算值

数据透视表提供了一些计算数据的方法。 详细了解可用的计算方法、源数据类型对计算产生的影响，以及如何在数据透视表和数据透视图中使用公式。
可用计算方法
要在数据透视表??中计算值，可使用以下任一或所有类型的计算方法：

值字段中的汇总函数    值区域中的数据可将数据透视表??的中基础源数据汇总。 例如，下列源数据：

生成以下数据透视表和数据透视图。 如果通过数据透视表中的数据创建数据透视图，则该数据透视图中的值会反映关联的数据透视表中的计算。

                                             

在数据透视表??中，“月份”列字段提供的项为“三月”和“四月”。 “地区”行字段提供的项为“北部”、“南部”、“东部”和“西部”。 “四月”列和“北部”行交叉处的值为来自源数据的记录中的总销售收入（“月份”值为“四月”，“地区”值为“北部”）。 在数据透视图中，“地区”字段可能是一个分类字段，将“北部”、“南部”、“东部”和“西部”显示为类别。 “月份”字段可以是一个系列字段，将“三月”、“四月”和“五月”作为系列显示在图例中。 名为“销售总额”的“值”字段可包含数据标记，用于显示各地区的每月总收入。 例如，一个数据标记可通过其在纵轴（值）上的位置表示“北部”地区“四月”的销售总额。 要计算值字段，可所有类型的源数据（联机分析处理 (OLAP) 源数据除外）使用以下汇总函数。

函数

概述

Sum

值的总和。 这是用于数值数据的默认函数。

Count

数据值的数量。 Count 汇总函数的作用与 COUNTA 函数相同。 Count 是数字以外数据的默认函数。

Average

值的平均值。

Max

最大值。

Min

最小值。

Product

值的乘积。

Count Nums

数字型数据值的数量。 Count Nums 汇总函数的作用与 COUNT 函数相同。

StDev

估算总体的标准偏差，其中样本是整个总体计算的子集。

StDevp

总体的标准偏差，其中总体是要汇总的所有数据。

Var

估算总体的方差，其中样本是整个总体计算的子集。

Varp

总体的方差，其中总体是要汇总的所有数据。

自定义计算    自定义计算 根据数据区域中的其他项或单元格来显示值。 例如，可将“销售总额”数据字段中的值显示为“三月”销售额的某个百分比，或显示为“月份”字段中各项的汇总值。

下列函数可用于值字段中的自定义计算。

函数

结果

无计算

显示在该字段中输入的值。

总计的百分比

将值显示为报表中所有值或数据点的总计百分比。

列汇总百分比

将每个列或系列中的所有值显示为列或系列的汇总百分比。

行汇总百分比

将每个行或类别中的值显示为行或类别中的汇总百分比。

某一字段中某项的百分比

将值显示为“基本字段”中“基本项”值的百分比。

父行汇总百分比

按如下方式计算值：
（该项的值）/（行上父项的值）

父列汇总百分比

按如下方式计算值：
（该项的值）/（列上父项的值）

父级汇总的百分比

按如下方式计算值：
（该项的值）/（所选“基本字段”中父项的值）

差异

将值显示为与“基本字段”中“基本项”值的差异。

差异百分比

将值显示为与“基本字段”中“基本项”值的差异百分比。

按某一字段汇总

将“基本字段”中连续项的值显示为汇总。

按某一字段汇总的百分比

计算“基本字段”中连续项的值，并将该值显示为汇总百分比。

按升序排名

显示某一特定字段中所选值的排位，其中将该字段中的最小项列为 1，而每个较大的值将具有较高的排位值。

按降序排名

显示某一特定字段中所选值的排位，其中将该字段中的最大项列为 1，而每个较小的值将具有较高的排位值。

索引

按如下方式计算值：
(（单元格中的值）x（总计/整体总计）)/(（行总计）x（列总计）)

公式    如果汇总函数和自定义计算无法提供所需结果，可在计算字段和计算项中创建自己的公式。 例如，可为计算项添加计算销售佣金的公式，销售佣金在每个地区可能有所不同。 然后，报表自动将佣金包含在分类汇总和总计中。

源数据类型如何影响计算
报表中可用的计算和选项取决于源数据是来自 OLAP 数据库，还是非 OLAP 数据源。

基于 OLAP 源数据的计算    对于创建自 OLAP 多维数据集的数据透视表，会在 OLAP 服务器上预先计算汇总值，然后在 Excel 中显示结果。 不能更改这些预计算值在数据透视表??中的计算方式。 例如，不能更改用于计算数据字段或分类汇总的汇总函数，也不能添加计算字段或计算项。

此外，如果 OLAP 服务器提供计算字段（称为计算成员），可在数据透视表字段列表中看到这些字段。 还可看到通过宏（在 Visual Basic for Applications (VBA) 中编写并存储在工作簿中）创建的所有计算字段和计算项，但不能更改这些字段或项。 如需其他计算类型，请联系 OLAP 数据库管理员。
对于 OLAP 源数据，可在计算分类汇总和总计时包括或排除隐藏项的值。

基于非 OLAP 源数据的计算    在基于其他类型的外部数据或基于工作表数据的数据透视表中，Excel 使用 Sum 汇总函数来计算包含数值数据的值字段，并使用 Count 汇总函数来计算包含文本的数据字段。 可选择不同的汇总函数（例如，Average、Max 或 Min）以进一步分析和自定义数据。 此外，还可通过创建计算字段或在字段内创建计算项，创建使用报表元素或其他工作表数据的自定义公式。