首页 > Excel专区 > Excel教程 >

在数据透视表中计算值

Excel教程 2022-02-03 17:56:55

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

详细了解如何在数据透视表中计算值
数据透视表提供了一些计算数据的方法。详细了解可用的计算方法、源数据类型对计算产生的影响，以及如何在数据透视表和数据透视图中使用公式。
可用计算方法
要在数据透视表中计算值，可使用以下任一或所有类型的计算方法：
值字段中的汇总函数    值区域中的数据可将数据透视表的中基础源数据汇总。例如，下列源数据：
生成以下数据透视表和数据透视图。如果通过数据透视表中的数据创建数据透视图，则该数据透视图中的值会反映关联的数据透视表中的计算。
在数据透视表中，“月份”列字段提供的项为“三月”和“四月”。 “地区”行字段提供的项为“北部”、“南部”、“东部”和“西部”。 “四月”列和“北部”行交叉处的值为来自源数据的记录中的总销售收入（“月份”值为“四月”，“地区”值为“北部”）。
在数据透视图中，“地区”字段可能是一个分类字段，将“北部”、“南部”、“东部”和“西部”显示为类别。 “月份”字段可以是一个系列字段，将“三月”、“四月”和“五月”作为系列显示在图例中。名为“销售总额”的“值”字段可包含数据标记，用于显示各地区的每月总收入。例如，一个数据标记可通过其在纵轴（值）上的位置表示“北部”地区“四月”的销售总额。
要计算值字段，可所有类型的源数据（联机分析处理 (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）以进一步分析和自定义数据。此外，还可通过创建计算字段或在字段内创建计算项，创建使用报表元素或其他工作表数据的自定义公式。
在数据透视表中使用公式
仅可在基于非 OLAP 源数据的报表中创建公式。不能在基于 OLAP 数据库的报表中使用公式。在数据透视表中使用公式时，应了解以下公式语法规则和公式行为：
数据透视表公式元素    在为计算字段和计算项创建的公式中，可像在其他工作表公式中一样使用运算符和表达式。可使用常量，也可引用报表中的数据，但不能使用单元格引用或定义的名称。不能使用需要将单元格引用或定义的名称作为参数的工作表函数，也不能使用数组函数。
字段和项名称    Excel 使用字段和项名称来标识公式中的报表元素。在以下示例中，C3:C9 区域中的数据使用字段名称“奶制品”。 “类型”字段中的计算项（根据乳制品销售额估算新产品的销售额）可使用诸如 =奶制品 * 115% 等公式。
注意: 在数据透视图中，字段名称显示在数据透视表字段列表中，而项名称显示在每个字段的下拉列表中。不要将这些名称与图表信息中显示的名称混淆，图表信息中的名称反映系列和数据点名称。
公式针对总数（而不是单个记录）进行运算    计算字段公式针对公式中任何字段的基础数据总和进行运算。例如，计算字段公式 =销售额 * 1.2 会将每个类型和地区的销售总额乘以 1.2；而不是将单个销售额乘以 1.2，然后对相乘得到的数进行求和。
计算项公式针对单个记录进行运算。例如，计算项公式 =奶制品 * 115% 会将每个乳制品销售额乘以 115%，然后再将相乘所得的数汇总到“值”区域。
名称中的空格、数字和符号    在包括多个字段的名称中，这些字段可按任意顺序排列。在上述示例中，单元格 C6:D6 可以是“‘四月北部’”，也可以是“‘北部四月’”。如果名称包含多个单词，或者包含数字或符号，请在该名称两边加上单引号。
总计    公式不能引用总计（如示例中的“三月总计”、“四月总计”和“总计”）。
项引用中的字段名称    可在对项的引用中包括字段名称。项名称必须放入方括号中 – 例如地区[北部]。如果某报表中两个不同字段中的两个项具有相同名称，使用此格式可避免 #NAME? 错误。例如，如果报表的“类型”字段中有名为“肉类”的项，而“分类”字段中也有名为“肉类”的项，可将这两个项分别引用为类型[肉类] 和分类[肉类]，以防止 #NAME? 错误。
按位置引用项    可根据当前排序和显示，按照项在报表中的位置引用项。类型[1] 是“奶制品”，类型[2] 是“海鲜”。每当项的位置发生更改，或者显示或隐藏其他项时，通过这种方式引用的项也会随之更改。隐藏项不会计入此索引。
可使用相对位置引用项。相对于包含公式的计算项来确定位置。如果“南部”是当前地区，那么地区[-1] 是“北部”；如果“北部”是当前地区，那么地区[+1] 是“南部”。例如，计算项可使用公式 =地区[-1] * 3%。如果提供的位置在字段中第一个项之前或最后一个项之后，公式会引发 #REF! 错误。
在数据透视图中使用公式
要在数据透视图中使用公式，可在关联的数据透视表（可在其中看到组成数据的各值）中创建公式，然后在数据透视图中以图形方式查看结果。
例如，下面的数据透视图显示每个地区各销售员的销售额：
要了解销售额增长 10% 后的情况，可在关联的数据透视表中创建一个计算字段，使用诸如 =销售额 * 110% 等公式。
结果立即在数据透视图中显示，如下图所示：
要查看表示北部地区销售额减去 8% 的运输成本的数据标记，可使用诸如 =北部 – (北部 * 8%) 等公式在“地区”字段中创建计算项。
生成的图表如下所示：
但是，在“销售员”字段中创建的计算项在图例中显示为系列，在每个图表中显示为各类别的数据点。
较新版本 Office 2007 Web 
在数据透视表中创建公式
重要: 不能在连接到联机分析处理 (OLAP) 数据源的数据透视表中创建公式。
开始之前，请确定是需要计算字段，还是字段内的计算项。如果要在公式中使用其他字段中的数据，请使用计算字段。如果希望公式使用一个字段内的一个或多个特定项中的数据，请使用计算项。
对于计算项，可逐单元格输入不同公式。例如，如果名为 OrangeCounty 的计算项将公式 =Oranges * .25 用于所有月份，那么可针对六月、七月和八月将该公式更改为 =Oranges .5。
如果具有多个计算项或公式，可调整计算顺序。
添加计算字段
单击数据透视表。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“计算字段”。
在“名称”框中，键入字段名称。
在“公式”框中，输入字段公式。
要在公式中使用另一字段中的数据，请在“字段”框中单击该字段，然后单击“插入字段”。例如，要针对“销售额”字段中各值计算 15% 的佣金，可输入 = 销售额 15%。
单击“添加”。
向字段添加计算项
单击数据透视表。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
如果字段中的项已分组，请在“分析”选项卡的“分组”组中，单击“取消组合”。
单击要向其中添加计算项的字段。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“计算项”。
在“名称”框中，键入计算项的名称。
在“公式”框中，输入项公式。
要在公式中使用某个项中的数据，请在“项”列表中单击该项，然后单击“插入项”（该项必须来自与计算项相同的字段）。
单击“添加”。
逐单元格为计算项输入不同公式
单击要更改其公式的单元格。
要更改多个单元格的公式，请按住 Ctrl，然后单击其他单元格。
在编辑栏中，键入对公式的更改。
调整多个计算项或公式的计算顺序
单击数据透视表。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“求解次序”。
单击公式，然后单击“上移”或“下移”。
继续操作，直到公式按所需计算顺序排列。
查看数据透视表中使用的所有公式
可显示当前数据透视表中使用的所有公式列表。
单击数据透视表。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“列出公式”。
编辑数据透视表公式
编辑公式前，请确定该公式位于计算字段内，还是计算项内。如果公式位于计算项内，还需确定该公式是否是计算项的唯一公式。
对于计算项，可编辑计算项特定单元格的单个公式。例如，如果名为 OrangeCalc 的计算项将公式 =Oranges * .25 用于所有月份，那么可针对六月、七月和八月将该公式更改为 =Oranges .5。
确定公式位于计算字段内，还是计算项内
单击数据透视表。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“列出公式”。
在公式列表中，在“计算字段”或“计算项”下列出的公式中找到要更改的公式。如果某个计算项有多个公式，那么在创建项目时输入的默认公式在 B 列中具有计算项名称。对于该计算项的其他公式，B 列包含计算项名称和交叉项名称。
例如，某个计算项的默认公式名为 MyItem，此项的另一个公式被标记为 MyItem 一月销售额。在数据透视表中，可在“MyItem”行和“一月”列的“销售额”单元格中找到此公式。
使用以下编辑方法之一继续操作。
编辑计算字段公式
单击数据透视表。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“计算字段”。
在“名称”框中，选择要为其更改公式的计算字段。
在“公式”框中，编辑公式。
单击“修改”。
编辑计算项的单个公式
单击包含该计算项的字段。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“计算项”。
在“名称”框中，选择计算项。
在“公式”框中，编辑公式。
单击“修改”。
编辑计算项特定单元格的单个公式
单击要更改其公式的单元格。
要更改多个单元格的公式，请按住 Ctrl，然后单击其他单元格。
在编辑栏中，键入对公式的更改。
提示: 如果具有多个计算项或公式，可调整计算顺序。有关详细信息，请参阅调整多个计算项或公式的计算顺序。
删除数据透视表公式
注意: 删除数据透视表公式为永久性操作。如果不希望永久性删除公式，可将字段或项拖出数据透视表，以将其隐藏。
确定公式位于计算字段内，还是计算项内。
计算字段显示在数据透视表字段列表中。计算项显示为其他字段内的项。
执行下列操作之一：
要删除计算字段，请单击数据透视表中的任意位置。
要删除计算项，请在数据透视表中单击包含要删除的项的字段。
此操作将显示数据透视表工具，并添加“分析”和“设计”选项卡。
在“分析”选项卡的“计算”组中，单击“字段、项目和集”，然后单击“计算字段”或“计算项”。
在“名称”框中，选择要删除的字段或项。
单击“删除”。
查看数据透视表中使用的所有公式
要显示当前数据透视表中使用的所有公式列表，请执行以下操作：
单击数据透视表。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“列出公式”。
编辑数据透视表公式
确定公式位于计算字段内，还是计算项内。如果公式位于计算项内，请确定该公式是否是计算项的唯一公式，操作方法如下：
单击数据透视表。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“列出公式”。
在公式列表中，在“计算字段”或“计算项”下列出的公式中找到要更改的公式。
如果某个计算项有多个公式，那么在创建项目时输入的默认公式在 B 列中具有计算项名称。对于该计算项的其他公式，B 列包含计算项名称和交叉项名称。
例如，某个计算项的默认公式名为 MyItem，此项的另一个公式被标记为 MyItem 一月销售额。在数据透视表中，可在“MyItem”行和“一月”列的“销售额”单元格中找到此公式。
执行下列操作之一：
编辑计算字段公式
单击数据透视表。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“计算字段”。
在“名称”框中，选择要为其更改公式的计算字段。
在“公式”框中，编辑公式。
单击“修改”。
编辑计算项的单个公式
单击包含该计算项的字段。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“计算项”。
在“名称”框中，选择计算项。
在“公式”框中，编辑公式。
单击“修改”。
编辑计算项特定单元格的单个公式
例如，如果名为 OrangeCalc 的计算项将公式 =Oranges .25 用于所有月份，那么可针对六月、七月和八月将该公式更改为 =Oranges *.5。
单击要更改其公式的单元格。
要更改多个单元格的公式，请按住 Ctrl，然后单击其他单元格。
在编辑栏中，键入对公式的更改。
如果具有多个计算项或公式，可通过执行以下操作来调整计算顺序：
单击数据透视表。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“求解次序”。
单击公式，然后单击“上移”或“下移”。
继续操作，直到公式按所需计算顺序排列。
删除数据透视表公式
提示: 如果不希望永久性删除公式，可隐藏字段或项。要隐藏字段，可将其拖出报表。
确定公式位于计算字段内，还是计算项内。
计算字段显示在数据透视表字段列表中。计算项显示为其他字段内的项。
执行下列操作之一：
删除计算字段
单击数据透视表。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“计算字段”。
在“名称”框中，选择要删除的字段。
单击“删除”。
删除计算项
单击包含要删除的项的字段。
在“选项”选项卡的“工具”组中，单击“公式”，然后单击“计算项”。
在“名称”框中，选择要删除的项。
单击“删除”。
如要在 Excel 网页版的数据透视表中汇总值，您可以使用 Sum、Count 和 Average 等汇总函数。默认情况下，SUM 函数用于值字段中的数值，但下面介绍了如何选择其他汇总函数：
右键单击数据透视表中的任意位置，然后单击“显示字段列表”。
在“数据透视表字段”列表的“值”中，单击值字段旁边的箭头。
单击“值字段设置”。
选择所需的汇总函数，然后单击“确定”。
注意: 汇总函数在基于联机分析处理 (OLAP) 源数据的数据透视表中不可用。
使用此汇总函数
若要计算
Sum
对数值求和。默认情况下对具有数值的值字段使用。
Count
非空值的数目。 Count 汇总函数的作用与 COUNTA 函数相同。默认情况下对具有非数值或空值的值字段使用 Count。
Average
值的平均值。
Max
最大值。
Min
最小值。
Product
值的乘积。
Count Numbers
包含数字的值个数（与 Count 不一样，它包括不能为空的值）。
StDev
估算总体的标准偏差，其中样本是整个总体计算的子集。
StDevp
总体的标准偏差，其中总体是要汇总的所有数据。
Var
估算总体的方差，其中样本是整个总体计算的子集。
Varp
总体的方差，其中总体是要汇总的所有数据。
需要更多帮助吗？
可随时在 Excel 技术社区中咨询专家，在解答社区获得支持，或在 Excel User Voice 上建议新功能或功能改进。

标签：公式字段数据计算透视 excel教程

上一篇：更改数据透视表的源数据下一篇：筛选数据透视表中的数据

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.