我的书签
添加书签
移除书签8.4 在数据透视表中使用计算字段和计算项
数据透视表创建完成后，不允许手工更改或者移动数据透视表中的任何区域，也不能在数据透视表中插入单元格或者添加公式进行计算。如果需要在数据透视表中执行自定义计算，必须使用“添加计算字段”或“添加计算项”功能。在创建了自定义的字段或项之后，Excel就允许在数据透视表中使用它们，这些自定义的字段或项就像是在数据源中真实存在的数据一样。
计算字段是通过对数据透视表中现有的字段执行计算后得到的新字段。
计算项是在数据透视表的现有字段中插入新的项，通过对该字段的其他项执行计算后得到该项的值。
计算字段和计算项可以对数据透视表中的现有数据（包括其他的计算字段和计算项生成的数据）进行运算，但无法引用数据透视表之外的工作表数据。
8.4.1 创建计算字段
1．在计算字段中对现有字段执行除运算
示例8.15 使用计算字段计算销售平均单价
图8-69所示展示了一张根据现有数据列表所创建的数据透视表，在这张数据透视表的数值区域中，包含“销售数量”和“销售额”字段，但是没有“单价”字段。如果希望得到平均销售单价，可以通过添加计算字段的方法来完成，而无需对数据源做出调整后再重新创建数据透视表。
图8-69 需要创建计算字段的数据透视表
步 骤1
单击数据透视表中的列字段项单元格（如C4），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算字段】命令，打开【插入计算字段】对话框，如图8-70所示。
图8-70 打开【插入计算字段】对话框
在【插入计算字段】对话框的【名称】框内输入“销售单价”，将光标定位到【公式】框中，清除原有的数据“=0”；在【字段】列表框中双击“销售额”字段，输入“／”（除号），再双击“销售数量”字段，得到计算“销售单价”的公式，如图8-71所示。
图8-71 编辑插入的计算字段
步 骤3
单击【添加】按钮，将定义好的计算字段添加到数据透视表中，单击【确定】按钮完成设置，此时数据透视表中新增了一个字段“求和项：销售单价”，如图8-72所示。
图8-72 添加“销售单价”计算字段的数据透视表
新增的计算字段“求和项：销售单价”被添加到数据透视表以后，也会相应地出现在【数据透视表字段列表】对话框的窗口之中，就像真实地存在于数据源表中其他字段一样，如图8-73所示。
图8-73 添加的计算字段出现在【数据透视表字段列表】对话框中
示例8.16 使用计算字段计算奖金提成
图8-74所示展示了一张根据销售订单数据列表所创建的数据透视表，如果希望根据销售人员业绩进行奖金提成的计算，可以通过添加计算字段的方法来完成，而无需对数据源做出调整后再重新创建数据透视表。
图8-74 需要创建计算字段的数据透视表
步 骤1
单击数据透视表中的列字段项单元格（如B3），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算字段】命令，打开【插入计算字段】对话框，如图8-75所示。
图8-75 打开【插入计算字段】对话框
步 骤2
在【插入计算字段】对话框的【名称】框内输入“销售人员提成”，将光标定位到【公式】框中，清除原有的数据“=0”，在【字段】列表框中双击“订单金额”字段，然后输入“*0.015”（销售人员的提成按1.5%计算），得到计算“销售人员提成”的计算公式，如图8-76所示。
图8-76 将现有的字段乘上参数得到新字段
步 骤3
单击【添加】按钮，最后单击【确定】按钮关闭对话框。此时，数据透视表中新增了一个“销售人员提成”字段，如图8-77所示。
图8-77 添加“销售人员提成”计算字段后的数据透视表
3．在计算字段中执行四则混和运算
示例8.17 使用计算字段计算主营业务毛利率
图8-78中展示了一张根据主营业务收入及成本的数据列表所创建的数据透视表，在这张数据透视表的数值区域中，包含“销售数量”、“主营业务收入”和“主营业务成本”字段，但是没有“主营业务利润率”字段。如果希望得到主营业务利润率，可以通过添加计算字段的方法来完成，而无需对数据源做出调整后再重新创建数据透视表。
图8-78 销售、成本及利润报表
步 骤1
单击数据透视表中的列字段项单元格（如B3），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算字段】命令，打开【插入计算字段】对话框，如图8-79所示。
图8-79 打开【插入计算字段】对话框
步 骤2
在【插入计算字段】对话框的【名称】框内输入“主营业务利润率％”，将光标定位到【公式】框中，清除原有的数据“=0”，然后输入“=（主营业务收入-主营业务成本)／主营业务收入”，得到计算“主营业务利润率％”字段的公式，如图8-80所示。
图8-80 编辑插入的计算字段
单击【添加】按钮，最后单击【确定】按钮关闭对话框，此时数据透视表中新增一个“主营业务利润率％”字段。将新增字段的数字格式设置为“百分比”，如图8-81所示。
图8-81 添加“主营业务利润率％”计算字段后的数据透视表
数据透视表字段数字格式设置的具体应用请参阅4.1.4小节。
4．在计算字段中使用Excel函数来运算
在数据透视表中插入计算字段不仅可以进行加、减、乘和除等简单运算，还可以使用函数来进行更复杂的计算。但是，计算字段中使用Excel函数会有很多限制，因为在数据透视表内添加计算字段的公式计算实际上是利用了数据透视表缓存中存在的数据，公式中不能使用单元格引用或定义名称作为变量的工作表函数，只能使用SUM、IF、AND、NOT、OR、COUNT、AVERAGE和TEXT等函数。
示例8.18 使用计算字段进行应收账款账龄分析
图8-82所示展示了一张在2011年9月1日根据应收账款余额数据列表所创建的数据透视表，在这张数据透视表的数值区域中只包含“应收账款余额”的汇总字段，如果希望对应收账款余额进行账龄分析，依次划分为“欠款0~30天”、“欠款31~60天”、“欠款61~90天”和“欠款90天以上”不同的账龄区间，可以通过添加计算字段的方法来完成，具体方法请参照以下步骤进行。
图8-82 应收账款余额统计表
单击数据透视表中的列字段项单元格（如D3），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算字段】命令，打开【插入计算字段】对话框，如图8-83所示。
图8-83 打开【插入计算字段】对话框
步 骤2
在【插入计算字段】对话框的【名称】框内输入“账龄0〜30天”，将光标定位到【公式】框中，清除原有的数据“=0”，然后输入“=IF（AND（TEXT（"2011-9-1","#"）-应收款日期>0,TEXT("2011-9-1","#"）-应收款日期<=30），应收账款余额，0）”，单击【添加】按钮得到计算“账龄0〜30天”的公式。
将【名称】框内的“账龄0~30天”更改为“账龄31~60天”，清除【公式】框中原有的公式，然后输入“= IF(AND(TEXT("2011-9-1","#")-应收款日期>30,TEXT（"2011-9-1","#")-应收款日期<=60），应收账款余额，0）”，单击【添加】按钮得到计算“账龄31〜60天”的公式。
按相同方法将【名称】框内的“账龄31〜60天”，更改为“账龄61〜90天”，清除【公式】框中原有的公式，然后输入“=IF(AND(TEXT("2011-9-1","#")-应收款日期>60，TEXT("2011-9-1","#")-应收款日期<=90),应收账款余额，0）”，单击【添加】按钮得到计算“账龄61〜90天”的公式。
最后将【名称】框内的“账龄61〜90天”更改为“账龄大于90天”，清除【公式】框中原有的公式，然后输入“=IF(TEXT("2011-9-1","#")-应收款日期>90,应收账款余额，0））”，单击【添加】按钮得到计算“账龄大于90天”的公式。
此时，新创建的计算字段都出现在【名称】和【字段】的下拉列表中，如图8-84所示。
图8-84 编辑插入的计算字段
单击【确定】按钮关闭对话框，完成后的报表如图8-85所示。
图8-85 应收账款账龄分析表
5．使数据源中的空数据不参与数据透视表计算字段的计算
示例8.19 合理地进行目标完成率指标统计
图8-86所示展示了某公司在一定时期内各地区销售目标完成情况的数据列表，其中数据列表中的“完成”列中有很多尚未实施的空白项，如果这些数据参与数据透视表计算字段的计算就会造成目标完成率指标统计上的不合理，要解决这个问题，请参照以下步骤进行。
图8-86 某公司目标完成明细表
步 骤1
根据如图8-86所示的数据列表创建如图8-87所示的数据透视表。
步 骤2
添加计算字段“完成率％”，计算字段公式为“=完成／目标”，如图8-88所示。
图8-87 创建数据透视表
图8-88 添加计算字段
单击报表筛选字段“完成”的下拉按钮，在弹出的下拉菜单中勾选【选择多项】复选框，同时取消勾选“（空白）”复选框，单击【确定】按钮，如图8-89所示。
图8-89 无效数据不参与完成率统计的数据透视表
8.4.2 修改数据透视表中的计算字段
对于数据透视表中已经添加的计算字段，用户还可以进行修改以满足变化的分析要求。以图8-77所示的数据透视表为例，要将销售人员提成比例提高为2%，请参照以下步骤进行。
步 骤1
单击数据透视表中的任意单元格（如B4），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算字段】命令，打开【插入计算字段】对话框，如图8-90所示。
图8-90 打开【插入计算字段】对话框
步 骤2
单击【名称】框的下拉按钮，选择“销售人员提成”选项，如图8-91所示。
图8-91 编辑已经插入的计算字段
步 骤3
在【公式】框中，将原有公式“=订单金额0.015”，修改为“=订单金额0.02”（销售人员的提成按2%计算），单击【修改】按钮，最后单击【确定】按钮，如图8-92所示。
图8-92 修改计算字段后的数据透视表
8.4.3 删除数据透视表中的计算字段
对于数据透视表已经添加好的计算字段，如果不再有分析价值，用户可以对计算字段进行删除，仍以图8-77所示的数据透视表为例，如果需要删除“销售人员提成”字段，请参照以下步骤进行。
步 骤1
调出【插入计算字段】对话框。
步 骤2
单击【名称】框的下拉按钮，选择“销售人员提成”选项，单击【删除】按钮，如图8-93所示。
图8-93 删除计算字段
步 骤3
单击【确定】按钮完成设置，如图8-94所示。
图8-94 删除计算字段后的数据透视表
8.4.4 计算字段的局限性
数据透视表的计算字段，不是按照数值字段在数据透视表中所显示的数值进行计算，而是依据各个数值之和来计算。也就是说，数据透视表是使用各个数值字段分类求和的结果来应用计算字段。即使数值字段的汇总方式被设置为“平均值”，计算字段也会将其看作是“求和”。
图8-95 计算字段与手工计算对比
例如，在图8-95所示的数据透视表中，“求和项：销售金额”是一个计算字段，其公式为“数量单价”。但是，它并未按照数据透视表内所显示的数值进行直接相乘，而是按照“求和项：数量”与“求和项：单价”相乘，即数量之总和与单价之总和的乘积。数据透视表右侧区域中（F列）用作对比显示的数据，则是按照数据透视表内显示的“求和项：数量平均值项：单价”而得来。因此，以“按摩椅”为例，计算字段的结果为3547200=2 548 800，而不是354800=283 200。
此外，添加计算字段后的数据透视表“总计”的结果有时也会出现错误。
如图8-95所示的数据透视表，添加“销售金额”计算字段后，总计统计出的结果并不是各个品名的销售金额总计，而是按照“求和项：数量”总计乘以“求和项：单价”总计得来的，这个结果显然是不正确的，如图8-96所示。
图8-96 添加计算字段后“总计”出现错误
解决这个问题的方法如下。
步 骤1
在数据透视表的“总计”单元格上单击鼠标右键，在弹出的快捷菜单中单击【删除总计】命令，如图8-97所示。
图8-97 去掉数据透视表无意义的总计行
步 骤2
在数据透视表下方手工添加总计行，如图8-98所示。
图8-98 对数据透视表手工添加总计行
8.4.5 创建计算项
1．使用计算项进行差额计算
示例8.21 公司费用预算与实际支出的差额分析
图8-99所示展示了一张由费用预算额与实际发生额明细表创建的数据透视表，在这张数据透视表的数值区域中，只包含“实际发生额”和“预算额”字段。如果希望得到各个科目费用的“实际发生额”与“预算额”之间的差异，可以通过添加计算项的方法来完成。
图8-99 需要创建自定义计算项的数据透视表
步 骤1
单击数据透视表中的列字段单元格（如C2），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算项】命令，打开【在“费用属性”中插入计算字段】对话框，如图8-100所示。
图8-100 添加“计算项”功能
注意
事实上，此处用于设置“计算项”的对话框名称并不是【在某字段中插入计算项】，而是如图所示的【在某字段中插入计算字段】，这是Excel 2010简体中文版中的一个已知错误。
在弹出的【在“费用属性”中插入计算字段】对话框内的【名称】框中输入“差额”把光标定位到【公式】框中，清除原有的数据“=0”，单击【字段】列表框中的“费用属性”选项，接着双击右侧【项】列表框中出现的“实际发生额”选项，然后输入减号“-”，再双击【项】列表框中的“预算额”选项，得到“差额”的计算公式，如图8-101所示。
步 骤3
单击【添加】按钮，最后单击【确定】按钮关闭对话框。此时数据透视表的列字段区域中已经插入了一个新的项目“差额”，其数值就是“实际发生额”项的数据与“预算额”项的数据的差值，如图8-102所示。
图8-101 添加“差额”计算项
图8-102 添加“差额”计算项后的数据透视表
提示
但是这里会出现一个问题，数据透视表中的行“总计”将汇总所有的行项目，包括新添加的“差额”项，因此其结果不再具有实际意义，所以需要通过修改相应设置去掉“总计”列。
步 骤4
在数据透视表“总计”标题上（如F2）单击鼠标右键，在弹出的快捷菜单中选择【删除总计】命令，如图8-103所示。
图8-103 实现费用差额分析的数据透视表
示例8.22 统计各个零售商店不同时期的销售增长率
图8-104所示展示了一张根据商店销售额数据列表创建的数据透视表，在这张数据透视表的数值区域中，包含“2011”和“2012”年份字段，如果希望得到2012年销售增长率，可以通过添加计算项的方法来完成。
图8-104 需要创建自定义计算项的数据透视表
步 骤1
弹出【在“年份”中插入计算字段】对话框，如图8-105所示。
步 骤2
在弹出的【在“年份”中插入计算字段】对话框内的【名称】框中输入“2012年增长率％”，把光标定位到【公式】框中，清除原有的数据“=0”，输入“=（'2012'-'2011'）／'2011'”，得到计算“2012年增长率％”的公式，如图8-106所示。
图8-105 添加【计算项】功能
图8-106 添加“2012年增长率％”计算项
步 骤3
单击【添加】按钮，最后单击【确定】按钮关闭对话框。此时数据透视表中新增了一个字段“2012年增长率％”，对“2012年增长率％”字段设置“百分比”样式，并删除“总计”列，完成的数据透视表，如图8-107所示。
图8-107 添加“2012年增长率％”计算项后的数据透视表
3．使用计算项进行企业盈利能力分析
示例8.23 反映企业盈利能力的财务指标分析
图8-108所示的数据透视表是某公司的2011年度的损益表，下面通过添加计算项进行企业的盈利能力指标分析，如果希望向数据透视表中添加主营业务利润率、营业利润率、利润率和净利润率等财务分析指标，请参照以下步骤。
图8-108 损益表
步 骤1
弹出【在“项目”中插入计算字段】对话框，如图8-109所示。
步 骤2
在【名称】文本框中输入“主营业务利润率％”，把光标定位到【公式】文本框中，清除原有的数据“=0”，单击【字段】列表框中的“项目”选项，接着双击右侧【项】列表框中出现的“二、主营业务利润”选项，然后输入除号“／”，再双击【项】列表框中的“一、主营业务收入”选项，得到计算“主营业务利润率%”的计算公式，如图8-110所示。
图8-109 【在“项目”中插入计算字段】对话框
图8-110 添加“主营业务利润率%”计算项
步 骤3
重复步骤2依次添加“营业利润率%”（=三、营业利润（亏损以“-”号填列）／一、主营业务收入）、“利润率%”（=四、利润总额（亏损以“-”号填列）／一、主营业务收入）和“净利润率％”（=五、净利润／一、主营业务收入）等计算项。
步 骤4
添加完成反映盈利能力指标的计算项后，数据透视表如图8-111所示。
步 骤5
将添加的计算项指标移动到数据透视表中的相关位置，完成反映企业盈利能力的财务分析，如图8-112所示。
图8-111 添加计算项后的数据透视表
图8-112 最终完成的数据透视表
4．隐藏数据透视表计算项为零的行
示例8.24 企业产成品进销存管理
在数据透视表中添加计算项后有时会出现很多数值为“0”的数据，如图8-113所示，为了使数据透视表更具可读性和易于操作，可以运用Excel的自动筛选功能将数值为“0”的数据项隐藏。
具体操作步骤请参阅5.2.5小节，完成后如图8-114所示。
图8-113 数据透视表中的“0”值计算项
图8-114 隐藏“0”值计算项后的数据透视表
5．在数据透视表中同时使用计算字段和计算项
根据不同的数据分析要求，在数据透视表中，计算字段或计算项既可以单独使用也可以同时使用。
示例8.25 比较分析费用控制属性的占比和各年差异
图8-115所示的数据列表是某公司2010年和2011年的制造费用明细账，如果希望根据明细账创建数据透视表并同时添加计算字段和计算项进行制造费用分析并计算出2010年与2011年发生费用的差额和可控费用与不可控费用分别占费用发生总额的占比，请参照以下步骤进行。
图8-115 费用明细账
步 骤1
创建如图8-116所示的数据透视表。
图8-116 创建数据透视表
单击数据透视表中“2010年”字段的标题单元格（如C3），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算字段】命令，打开【插入计算字段】对话框，如图8-117所示。
步 骤3
在【插入计算字段】对话框的【名称】框内输入“差异”，将光标定位到【公式】框中，清除原有的数据“=0”，然后输入“='2011年'-'2010年'”，得到“差异”的计算公式，如图8-118所示。
图8-117 添加“计算字段”功能
图8-118 添加“差异”计算字段
步 骤4
单击【添加】按钮，最后单击【确定】按钮关闭对话框。此时，数据透视表中已经新增了一个“差异”字段，如图8-119所示。
步 骤5
单击数据透视表中“不可控费用”项的单元格（如A4），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算项】命令，打开【在“费用属性”中插入计算字段】对话框，如图8-120所示。
图8-119 添加“差异”计算字段后的数据透视表
图8-120 添加“计算项”功能
步 骤6
在弹出的【在“费用属性”中插入计算字段】对话框内的【名称】文本框中输入“可控费用占比”，把光标定位到【公式】文本框中，清除原有的数据“=0”，输入“=可控费用／（可控费用+不可控费用）”，得到“可控费用占比”的计算公式，如图8-121所示。
步 骤7
重复步骤6，依次添加“不可控费用占比=不可控费用／（可控费用+不可控费用）”、“费用总计=可控费用+不可控费用”。
步 骤8
将添加的计算项指标移动到数据透视表中的相关位置，去掉总计行，完成费用比较分析，如图8-122所示。
图8-121 添加“可控费用占比”计算项
图8-122 比较分析费用控制属性占比和各年差异的数据透视表
6．改变数据透视表中的计算项
对于数据透视表已经添加好的计算项，用户还可以进行修改以满足分析要求的变化，以图8-102所示的数据透视表为例，如果希望将实际发生额与预算额的“差额”计算项更改为“差额率%”，请参照以下步骤进行。
步 骤1
单击数据透视表中的列字段单元格（如C2），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算项】命令，打开【在“费用属性”中插入计算字段】对话框，如图8-123所示。
图8-123 【在“费用属性”中插入计算字段】对话框
步 骤2
单击【名称】框的下拉按钮，选择“差额”选项，如图8-124所示。
图8-124 编辑已经插入的计算项
在【公式】框中将原有公式“=实际发生额-预算额”，修改为“=（实际发生额-预算额）／实际发生额”，如图8-125所示。
步 骤4
单击【修改】按钮，最后单击【确定】按钮完成设置，将“差额”字段名称更改为“差额率％”，数据列设置为“百分比”单元格样式，如图8-126所示。
图8-125 编辑已经插入的计算项
图8-126 修改计算项后的数据透视表
7．删除数据透视表中的计算项
对于数据透视表已经创建的计算项，如果不再有分析价值，用户可以将计算项进行删除。仍以图8-102所示的数据透视表为例，要删除“差额”计算项，请参照以下步骤进行。
步 骤1
单击数据透视表中列字段的单元格（如C2），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【计算项】命令，打开【在“费用属性”中插入计算字段】对话框，如图8-127所示。
步 骤2
单击【名称】框的下拉按钮，选择“差异”选项，单击【删除】按钮，如图8-93所示。
图8-127 【在“费用属性”中插入计算字段】对话框
图8-128 删除计算项
步 骤3
单击【确定】按钮完成设置，如图8-129所示。
图8-129 删除计算项后的数据透视表
8.4.6 改变计算项的求解次序
如果数据透视表存在两个或两个以上的计算项，并且不同计算项的公式中存在相互引用，各个计算项的计算顺序会带来不同的计算结果，为了满足不同的数据分析要求，可以通过数据透视表工具栏中的“求解次序”选项来改变各个计算项的计算次序。
单击数据透视表内的计算项单元格（如B8），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，在弹出的下拉菜单中选择【求解次序】命令，打开【计算求解次序】对话框，如图8-130所示。
图8-130 【求解次序】对话框
【求解次序】对话框中列示出数据透视表存在的所有计算项，用户在确定了正在处理的计算项后可以通过对话框中的【上移】或者【下移】按钮改变计算项的求解次序，也可以单击【删除】按钮将该计算项删除。
8.4.7 列示数据透视表计算字段和计算项的公式
在数据透视表中添加完成的计算字段和计算项公式还可以通过报表的形式反映出来，以图8-122所示的数据透视表为例，首先单击数据透视表内的任意单元格（如B8），在【数据透视表工具】的【选项】选项卡中单击【域、项目和集】的下拉按钮，然后在弹出的下拉菜单中选择【列出公式】命令，Excel会自动生成一张新的工作表，列示出在数据透视表中添加的所有计算字段和计算项的公式，如图8-131所示。
图8-131 列示数据透视表计算字段和计算项公式
