轻松办公系列进阶课堂-OfficeExcel数据透视表和透视图（十二）

轻松办公系列进阶课堂-OfficeExcel数据透视表和数据透视图

（十二）

计算和总计（1）

一、更改数据透视表中字段的汇总函数或自定义计算

“值”区域中的数据通过以下方法对数据透视表中的基础源数据进行汇总：对于数值使用 SUM 函数，对于文本值使用 COUNT 函数。但是，可以更改汇总函数（汇总函数：是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。汇总函数的例子包括 Sum、Count 和 Average。）。还可以创建自定义计算（自定义计算：用数据透视表的数据区域中的其他单元格值对数据区域中的值进行汇总的方法。使用数据字段的“数据透视表字段”对话中的“数据显示方式”列表可创建自定义计算。）。

在要更改其数据透视表汇总函数的“值”区域中，选择一个字段。
在“选项”选项卡上的“活动字段”组中，单击“活动字段”，然后单击“字段设置”。

将显示“值字段设置”对话框。

“源名称”是数据源中字段的名称。

“自定义名称”显示数据透视表中的当前名称，如果没有自定义名称，则显示源名称。若要更改“自定义名称”，请单击该框中的文本并编辑名称。

单击“汇总方式”选项卡。
在“值字段汇总方式”框中，单击要使用的汇总函数。

可以使用的汇总函数：

函数	功能
Sum	对数值求和。这是数值的默认函数。
Count	求数值的个数。Count 汇总函数的作用与 COUNTA 工作表函数相同。Count 是数字以外的数值的默认函数。
Average	求数值平均值。
Max	求最大值。
Min	求最小值。
Product	求数值的乘积。
Count Nums	求数字型数值的个数。Count Nums 汇总函数的作用与 COUNT 工作表函数相同。
StDev	估算总体的标准偏差，样本为总体的子集。
StDevp	计算总体的标准偏差，其中的总体是指要汇总的所有数值。
Var	估计总体方差，样本为总体的子集。
Varp	计算总体的方差，其中的总体是指要汇总的所有数值。

注释：对于某些类型的源数据（源数据：用于创建数据透视表或数据透视图的数据清单或表。源数据可以来自 Excel 数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。）（如 OLAP 数据），以及对于计算字段 （计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）和包含计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）的字段，不能更改汇总函数。

或者，可以通过执行下列操作来使用自定义计算：
单击“值显示方式”选项卡。
在“值显示方式”框中单击所需的计算。

可以使用的自定义计算

函数	结果
Normal	关闭自定义计算。
Difference From	显示的值为“基本字段”中“基本项”值的差。
% Of	显示的值为“基本字段”中“基本项”值的百分比。
% Difference From	显示的值为“基本字段”中“基本项”值的百分比差值。
Running Total in	显示的值为“基本字段”中连续项（项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）的汇总。
% Of Row	显示每一行或每个类别的值相对于该行或该类别总计的百分比。
% Of Column	显示每列或系列的所有值相对于列或系列的汇总的百分比。
% Of Total	显示的值为报表中所有值或所有数据点的总计的百分比。
Index	按下式计算值： ((单元格中值) x (总计)) / ((行总计) x (列总计))

请选择“基本字段”和“基本项”（如果这些选项对于所选计算可用）。

“基本字段”应当不同于在步骤 1 中选择的字段。

若要更改设置数字格式的方法，请单击“数字格式”，然后在“设置单元格格式”对话框的“数字”选项卡中，选择一种数字格式，然后单击“确定”。
如果报表包含多个数值字段，请针对需要更改的每个数值字段，重复上面的步骤。

注释：若要对同一个字段使用多个汇总函数，请从数据透视表的“字段列表”中再次添加该字段，然后选择所需的其他函数以重复上面的步骤。

二、创建、编辑或删除数据透视表或数据透视图公式

如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段（计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）和计算项（计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）中创建自己的公式。例如，可以添加一个计算项并在其中包含一个计算销售年金的公式（每个区域可能有所不同）。然后数据透视表将自动在分类汇总和总计中包含年金值。

注释：

不能在连接到 OLAP（OLAP：为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。）源数据的数据透视表或数据透视图中创建公式。
若要在数据透视图（数据透视图：提供交互式数据分析的图表，与数据透视表类似。可以更改数据的视图，查看不同级别的明细数据，或通过拖动字段和显示或隐藏字段中的项来重新组织图表的布局。）中获得最佳效果，请使用相关联的数据透视表（相关联的数据透视表：为数据透视图提供源数据的数据透视表。在新建数据透视图时，将自动创建数据透视表。如果更改其中一个报表的布局，另外一个报表也随之更改。），在其中可看到公式计算的单个数据值。

1、创建公式

确定在字段中是需要计算字段（计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）还是需要计算项（计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）。

当您想在公式中使用其他字段的数据时，请使用计算字段。
当您想在公式中使用字段中的一个或多个特定项（项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）的数据时，请使用计算项。

请执行下列操作之一。

添加计算字段：

单击数据透视表。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“计算字段”。
在“名称”框中，键入字段的名称。
在“公式”框中，输入字段的公式。

若要在公式中使用来自另一字段的数据，可在“字段”框中单击该字段，再单击“插入字段”。例如，若要计算“销售额”字段中每个值 15% 的佣金，可输入“= Sales * 15%”。

单击“添加”。

向字段中添加计算项：

如果字段中的项已经分组，请在“选项”选项卡上的“组合”组中，单击“取消组合”。
单击要添加计算项的字段。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“计算项”。
在“名称”框中，键入计算项的名称。
在“公式”框中，输入数据项的公式。

若要在公式中使用数据项的数据，请单击“项”下拉列表中的项，再单击“插入项”（该项目必须和计算项来自同一字段）。

单击“添加”。
假设在第一步中已经取消项组合，如果需要则应重新组合。

对于计算项，可以按单元格逐个输入不同的公式。

例如，如果名称为 OrangeCounty 的计算项有一公式“=Oranges * .25”作用于所有月份，则可将六月、七月和八月的公式更改为“=Oranges *.5”。

请执行下列操作：

单击需更改公式的单元格。

若要更改几个单元格中的公式，请按住 Ctrl 单击其他单元格。

在编辑栏（编辑栏：位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）中，键入对公式的更改。

如果有多个计算项或公式，请通过执行下列操作来调整计算顺序：

单击数据透视表。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“求解次序”。
单击某个公式，再单击“上移”或者“下移”。
继续上一步的操作，直到达到所需的顺序。

2、显示公式列表

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

单击数据透视表。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“列出公式”。

3、编辑公式

确定公式是在计算字段 （计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）还是计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）中。如果公式是在计算项中，请通过执行下列操作确定是否是计算项唯一使用的公式：

单击数据透视表。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“列出公式”。
在公式列表中，在“计算字段”或“计算项”下查找希望更改的公式。

如果一个计算项有多个公式，在该项（项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）创建时输入的默认公式在 B 列拥有计算项名称。对于计算项的附加公式，B 列包含计算项名称和相交项的名称。

例如，名为“MyItem”的计算项有默认的公式以及另一个标识为“MyItem January Sales”的公式。在数据透视表中，这一公式在“MyItem”行和“January”列的“Sales”单元格中。

请执行下列操作之一：

编辑计算字段公式

单击数据透视表。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“计算字段”。
在“名称”框中，选择要更改公式的计算字段。
在“公式”框中，编辑公式。
单击“修改”。

编辑计算项的单个公式

单击含有计算项的字段。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“计算项”。
在“名称”框中，选择计算项。
在“公式”框中，编辑公式。
单击“修改”。

为计算项的特定单元格编辑独立的公式

例如，如果名称为 OrangeCalc 的计算项有一公式 =Oranges * .25 作用于所有月份，则可将六月、七月和八月的公式更改为 =Oranges *.5。

单击需更改公式的单元格。

若要更改几个单元格中的公式，请按住 Ctrl 单击其他单元格。

在编辑栏 （编辑栏：位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）中，键入对公式的更改。

如果有多个计算项或公式，请通过执行下列操作来调整计算顺序：

单击数据透视表。
在“选项”选项卡上的“工具”组中，单击“公式”，然后单击“求解次序”。
单击某个公式，再单击“上移”或者“下移”。
继续上一步的操作，直到达到所需的顺序。

4、删除公式

提示：如果不想永久地删除公式，则可隐藏字段或项。若要隐藏某个字段，请将其拖出报表。

确定公式是在计算字段（计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）还是计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）中。

计算字段显示在“数据透视表字段列表”中。而计算项则在其他字段中以项（项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）的形式出现。