计算数据透视表中的值

在数据透视表报表中,您可以使用值字段中汇总函数来组合来自基础源数据值。如果汇总函数和自定义计算不提供结果的需要,您可以创建自己的公式中的计算的字段和计算的项。例如,您可以添加包含销售佣金,可以为每个地区不同的公式计算的项。数据透视表报表然后将自动包含佣金中分类汇总和总计。

数据透视表报表提供多种方式计算的数据。了解有关计算可用的方法,源数据的类型如何影响计算以及要在数据透视表和数据透视图报表中使用公式。

可用的计算方法

若要计算数据透视表中的值,可以使用任何或所有以下类型的计算方法︰

  • 值字段中的汇总函数   在值区域中的数据汇总基础源数据透视表中。例如,下面的源数据︰

    数据透视表源数据示例

  • 将产生以下数据透视表和数据透视图报表。如果您的数据透视表中创建数据透视图,该数据透视图报表中的值反映关联的数据透视表中的计算。

    数据透视表示例

    数据透视图示例

  • 在数据透视表报表中,月份列字段提供3 月年 4 月的项目。地区行字段提供北美南部East,和西部的项目。交叉处的四月列,北美行的值是有北美四月地区值的月份值的源数据中的记录的总销售额。

  • 在数据透视图报表中,地区字段可能显示为分类北美南部East,和西部category 字段。Month字段可能系列字段作为系列图例中显示4 月,和可能的项目。一个名为总销售额字段包含代表每月每个区域中的总收入的数据标记。例如,其的一个数据标记将在垂直 (数值) 轴上表示北美地区年 4 月的总销售额。

  • 若要计算值字段,下面的汇总函数可用于所有除联机分析处理 (OLAP) 源数据的源数据的类型。

函数

总结

Sum

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

Count

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

Average

值的平均值。

Max

最大值。

Min

最小值。

Product

值的乘积。

计数

为数字的数据值的数目。计数汇总函数的工作方式相同COUNT 函数

StDev

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

StDevp

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

Var

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

Varp

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

  • 自定义计算   自定义计算显示基于其他项目或数据区域中的单元格的值。例如,可以销售总额数据字段中显示值, 3 月销售额的百分比或作为Month字段中的项目的运行汇总。

    以下功能可用于自定义计算中值字段。

函数

结果

无计算

显示字段中输入的值。

总计的百分比

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

列汇总百分比

以列或系列的汇总的百分比形式显示每个列或系列中的所有值。

行汇总百分比

以百分比形式的汇总行或类别的每个行或类别中显示的值。

% Of

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

父行汇总百分比

计算值,如下所示︰

(该项的值) / (行上父项的值)

父列汇总百分比

计算值,如下所示︰

(该项的值) / (列上父项的值)

父级汇总的百分比

计算值,如下所示︰

(该项的值) / (所选基本字段的父项的值)

差异

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

差异百分比

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

按某一字段汇总

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

按某一字段汇总的百分比

计算的值为基本字段中连续项的汇总百分比形式显示。

按升序排名

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

按降序排名

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

索引

计算值,如下所示︰

((value in cell) x (总计总的总计)) / ((Grand Row Total) x (列总计))

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

源数据的类型如何影响计算

计算和报表中可用的选项取决于源数据是否是从 OLAP 数据库或非 OLAP 数据源。

  • 计算基于 OLAP 源数据   对于从 OLAP 多维数据集创建的数据透视表报表,汇总的值是预先计算 OLAP 服务器上之前,Excel 将显示结果。不能更改这些预计算的值在数据透视表中的计算方式。例如,不能更改用于计算数据字段或分类汇总的汇总函数,或添加计算的字段或计算的项。

    此外,如果 OLAP 服务器提供计算的字段,称为计算成员,您将看到这些字段在数据透视表字段列表中。您还将看到的任何计算的字段和创建的宏编写在 Visual Basic for Applications (VBA) 并存储在您的工作簿中的计算的项,但不能更改这些字段或项。如果您需要其他类型的计算,与 OLAP 数据库管理员联系。

    对于 OLAP 源数据,您可以包括或排除隐藏项的值计算分类汇总和总计时。

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

在数据透视表报表中使用公式

您可以仅在基于非 OLAP 源数据的报表中创建公式。您不能在基于 OLAP 数据库的报表中使用公式。在数据透视表报表中使用公式时,您应了解以下公式的语法规则和公式的行为︰

  • 数据透视表公式元素   在公式中创建计算的字段和的计算的项,可以在其他工作表公式中使用运算符和表达式。您可以使用常量,并从报表中,引用的数据,但不能使用单元格引用或定义的名称。不能使用工作表函数需要单元格引用或定义的名称作为参数,而且您无法使用数组函数。

  • 字段和项的名称   Excel 使用字段和项的名称来标识报表在公式中的这些元素。在下面的示例,字段名称牛奶使用c3: c9区域中的数据。估算基于牛奶销售新产品的销售类型域中的计算的项公式将如= 奶制品 * 115%

    数据透视表示例

    注意: 在数据透视图报表中,在数据透视表字段列表中,显示域的名称,然后项目名称可以看到每个域下拉列表中。不要混淆这些名称与您看到在图表提示,反映系列和数据点的名称。

  • 公式操作的总和,不是单个记录   计算字段公式操作的基础数据的公式中的任何字段的总和。例如,该计算的字段公式= 销售额 * 1.2乘以 1.2; 每个类型和地区的销售额之和它不乘以 1.2 单个销售,然后求和乘以的金额。

    公式的计算项操作的单个记录。例如,计算的项公式= 奶制品 * 115%相乘牛奶时间 115%,在其后乘以的金额汇总一起在值区域中的每个单独销售。

  • 空格、 数字和名称中的符号   在包含多个字段的名称,字段可按任何顺序。在上面的示例中,四月或北美四月,也可以是单元格c6: d6 。使用单引号将多个单词的名称,或包含数字或符号。

  • 总计   公式不能引用汇总 (如三月总计4 月汇总总计的示例中)。

  • 项目引用中的字段名称   您可以为某个项目引用中包括的字段名称。项目名称必须用方括号 — 例如, [North] 区域。使用此格式来避免 #NAME?在报表中的两个不同字段中的两个项目具有相同名称的错误。例如,如果有一项名为类型域中的肉类和另一个项名为类别字段中的肉类报表,您可以防止 #NAME?通过引用的项目类型 [肉类]类别 [肉类]为错误。

  • 按位置引用数据项   根据当前排序和显示,可以引用的项目,通过在报表中的位置。键入 [1]牛奶,并键入 [2]数据项。显示或隐藏项目更改或其他项目的位置时,可以更改这种方式引用的项目。隐藏的项目不包含在索引中。

    您可以使用相对位置引用的项。确定包含公式的计算项相对位置。南部为当前区域,地区 [-1]北美;北美为当前区域,地区 [-1]南部。例如,计算的项公式将= 地区 [-1] * 3%。如果您提供的位置的第一项之前或之后的字段中的最后一个项目,公式将导致 # #REF !错误。

在数据透视图报表中使用公式

若要在数据透视图报表中使用公式,请位置数据,可以看到进行的单个值,然后您可以查看结果以图形方式中的数据透视图相关联数据透视表报表中创建公式。

例如,以下数据透视图报表显示每个地区各个推销员销售额的︰

显示每个地区各个推销员销售额的数据透视图

若要查看哪些销售额增长百分之十的外观,可以创建计算的字段中使用公式,如相关联的数据透视表报表= 销售额 * 110%

结果立即显示在数据透视图报表中,在下图中所示︰

显示每个地区销售额增长百分之十后的数据透视图

若要查看交通成本为 8%减去北美地区的销售额的独立数据标记,您可以创建计算的项在地区字段中使用公式如North-= (北美 * 8%)

生成的图表将如下所示︰

具有计算项的数据透视图。

但是,销售人员字段中创建的计算的项将显示为一系列图例中和在图表中显示为每个类别中的数据点。

在数据透视表中创建公式

重要: 不能连接到联机分析处理 (OLAP) 数据源的数据透视表报表中创建公式。

在开始之前,决定是否希望计算的字段或字段中的计算的项。当您想要从另一个字段在公式中使用的数据,请使用计算的字段。当您想在公式中使用字段中的一个或多个特定项目中的数据,请使用计算的项。

对于计算项,您可以输入不同的公式的单元格。例如,如果计算的项名为OrangeCounty包含公式的= 橙子 *.25跨所有月份,您可以更改公式为= 橙子 *.5年 6 月、 7 月,和年 8 月。

如果您有多个计算的项或公式时,您可以调整计算的顺序。

添加计算字段

  1. 单击数据透视表。

    这将显示数据透视表工具,其中包含分析设计选项卡。

  2. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击计算字段

    Excel 功能区图像

  3. 名称框中,键入字段的名称。

  4. 公式框中,输入字段的公式。

    若要从另一个字段在公式中使用的数据,在字段框中,单击该字段,然后单击插入域。例如,若要计算 15%的佣金销售额字段中每个值,您可以输入= 销售额 * 15%

  5. 单击“添加”。

向字段中添加计算的项

  1. 单击数据透视表。

    这将显示数据透视表工具,其中包含分析设计选项卡。

  2. 如果该字段中的项目进行分组,在分析选项卡,组中,单击取消组合

    Excel 功能区图像

  3. 单击您要在其中添加计算的项的字段。

  4. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击计算项

    Excel 功能区图像

  5. 名称框中,键入计算项的名称。

  6. 公式框中输入该项目的公式。

    若要在公式中使用从某项的数据,请单击项目列表中的项目,然后单击插入项目(从计算项的同一字段必须是项目)。

  7. 单击“添加”。

输入不同的公式的单元格的计算项

  1. 单击您要更改公式的单元格。

    若要更改多个单元格的公式,请按住 CTRL 并单击其他单元格。

  2. 在编辑栏中,键入对公式进行的更改。

调整多个计算的项或公式的计算顺序

  1. 单击数据透视表。

    这将显示数据透视表工具,其中包含分析设计选项卡。

  2. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击解决顺序

    Excel 功能区图像

  3. 单击公式,,然后单击上移下移

  4. 继续直到达到所需要计算的顺序。

查看数据透视表报表中使用的所有公式

当前数据透视表中,您可以显示所使用的所有公式的列表。

  1. 单击数据透视表。

    这将显示数据透视表工具,其中包含分析设计选项卡。

  2. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击列出公式

    Excel 功能区图像

编辑数据透视表公式

编辑公式之前,确定是否该公式中的计算的字段或计算的项。计算项公式时,还可以确定是否是唯一一个计算项公式。

对于计算项,您可以编辑单个公式的计算项特定单元格。例如,如果计算的项名为OrangeCalc包含公式的= 橙子 *.25跨所有月份,您可以更改公式为= 橙子 *.5年 6 月、 7 月,和年 8 月。

确定是否在计算的字段或计算的项公式

  1. 单击数据透视表。

    这将显示数据透视表工具,其中包含分析设计选项卡。

  2. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击列出公式

    Excel 功能区图像

  3. 在公式中的列表中,找到您要更改的公式列出在计算字段或计算项下。

    项目已创建时输入默认公式存在多个计算项公式时,在列 B 中具有计算的项名称计算项的其他公式,列 B 包含计算的项名称和相交项的名称。

    例如,您可能有默认公式的计算项名为MyItem和另一个公式标识MyItem 年 1 月的销售额为此项。在数据透视表报表中,您将此公式查找销售单元格中,MyItem 行和列 1 月。

  4. 继续使用编辑下列方法之一。

编辑计算的字段公式

  1. 单击数据透视表。

    这将显示数据透视表工具,其中包含分析设计选项卡。

  2. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击计算字段

    Excel 功能区图像

  3. 名称框中,选择您要更改公式的计算的字段。

  4. 公式框中,编辑公式。

  5. 单击“修改”

编辑单个公式的计算项

  1. 单击包含计算的项的字段。

  2. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击计算项

    Excel 功能区图像

  3. 名称框中,选择计算的项。

  4. 公式框中,编辑公式。

  5. 单击“修改”

编辑计算项的特定单元格的单个公式

  1. 单击您要更改公式的单元格。

    若要更改多个单元格的公式,请按住 CTRL 并单击其他单元格。

  2. 在编辑栏中,键入对公式进行的更改。

    提示: 如果您有多个计算的项或公式时,您可以调整计算的顺序。有关详细信息,请参阅调整多个计算的项或公式的计算的顺序

删除数据透视表公式

注意: 删除数据透视表公式中永久删除。如果不想永久删除公式,可以隐藏字段或项改为数据透视表报表利用通过拖动。

  1. 确定公式中的计算的字段或计算的项。

    计算的字段将显示在数据透视表字段列表中。计算的项目显示在其他字段中的项目。

  2. 执行下列操作之一:

    • 要删除的计算的字段,请单击数据透视表中的任意位置。

    • 若要删除的计算的项,在数据透视表中,单击包含要删除的项目的字段。

      这将显示数据透视表工具,其中包含分析设计选项卡。

  3. 分析选项卡上计算组中,单击字段、 项目、 & 集,,然后单击计算字段计算项

    Excel 功能区图像

  4. 名称框中,选择要删除的项目的字段。

  5. 单击“删除”

返回页首

查看数据透视表报表中使用的所有公式

若要显示当前数据透视表中使用的所有公式的列表,请执行下列操作︰

  1. 单击数据透视表。

  2. 选项选项卡上的工具组中,单击公式,然后单击列出公式

编辑数据透视表公式

  1. 确定公式中的计算的字段或计算的项。计算项公式时,确定是否是公式计算项只有一个通过执行下列操作︰

    1. 单击数据透视表。

    2. 选项选项卡上的工具组中,单击公式,然后单击列出公式

    3. 在公式中的列表中,找到您要更改的公式列出在计算字段或计算项下。

      已创建项目时输入默认公式存在多个计算项公式时,在列 B 中具有计算的项名称计算项的其他公式,列 B 包含计算的项名称和相交项的名称。

      例如,您可能有默认公式的计算项名为MyItem和另一个公式标识MyItem 年 1 月的销售额为此项。在数据透视表报表中,您将此公式查找销售单元格中,MyItem 行和列 1 月。

  2. 执行下列操作之一:

    编辑计算的字段公式   

    1. 单击数据透视表。

    2. 选项选项卡上的工具组中,单击公式,然后单击计算字段

    3. 名称框中,选择您要更改公式的计算的字段。

    4. 公式框中,编辑公式。

    5. 单击“修改”

      编辑单个公式的计算项   

    6. 单击包含计算的项的字段。

    7. 选项选项卡上的工具组中,单击公式,然后单击计算项

    8. 名称框中,选择计算的项。

    9. 公式框中,编辑公式。

    10. 单击“修改”

      编辑单个公式的计算项特定单元格   

      例如,如果计算的项名为OrangeCalc包含公式的= 橙子 *.25跨所有月份,您可以更改公式为= 橙子 *.5年 6 月、 7 月,和年 8 月。

    11. 单击您要更改公式的单元格。

      若要更改多个单元格的公式,请按住 CTRL 并单击其他单元格。

    12. 在编辑栏中,键入对公式进行的更改。

  3. 如果您有多个计算的项或公式,请通过执行下列调整计算的顺序︰

    1. 单击数据透视表。

    2. 选项选项卡上的工具组中,单击公式,然后单击解决顺序

    3. 单击公式,,然后单击上移下移

    4. 继续直到达到所需要计算的顺序。

删除数据透视表公式

提示: 如果不想永久删除公式,可以隐藏字段或项。若要隐藏字段,请将其拖出报表。

  1. 确定公式中的计算的字段或计算的项。

    计算的字段将显示在数据透视表字段列表中。计算的项目显示在其他字段中的项目。

  2. 执行下列操作之一:

    删除计算的字段   

    1. 单击数据透视表。

    2. 选项选项卡上的工具组中,单击公式,然后单击计算字段

    3. 名称框中,选择要删除的字段。

    4. 单击“删除”

      删除计算的项   

    5. 单击包含要删除的项目的字段。

    6. 选项选项卡上的工具组中,单击公式,然后单击计算项

    7. 名称框中,选择要删除的项目。

    8. 单击“删除”

返回页首

注释: 

  • 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

  • 请在 此处 中查找本文的英文版本以便参考。你和 Microsoft 社区的其他成员可以使用 Community Translation Framework (CTF) 来改进本文。只需将鼠标悬停在文章中的句子上,然后在 CTF 小组件中单击“IMPROVE TRANSLATION”即可。 单击此处了解有关 CTF 的详细信息。 使用 CTF 即表示你同意服务条款

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×