通常,一旦您创建了一个数据透视表,您就需要扩展您的分析并在其中包含更多数据/计算。
如果您需要一个可以使用数据透视表中现有数据点获取的新数据点,则无需返回并将其添加到源数据中。相反,您可以使用数据透视表计算字段来执行此操作。
什么是数据透视表计算字段?
让我们从数据透视表的基本示例开始。
假设您有一个零售商数据集,并且您创建了一个数据透视表,如下所示:
上面的数据透视表总结了零售商的销售额和利润值。
现在,如果您还想知道这些零售商的利润率是多少(利润率是“利润”除以“销售额”),该怎么办?
有几种方法可以做到这一点:
- 返回原始数据集并添加这个新数据点。因此,您可以在源数据中插入一个新列并计算其中的利润率。完成此操作后,您需要更新数据透视表的源数据以获取此新列作为其中的一部分。
- 虽然这种方法是可行的,但您需要手动返回数据集并进行计算。例如,您可能需要添加另一列来计算每单位的平均销售额(销售额/数量)。同样,您必须将此列添加到源数据中,然后更新数据透视表。
- 当您向其中添加新数据时,此方法还会使您的数据透视表膨胀。
- 在数据透视表之外添加计算。如果您的数据透视表结构不太可能更改,则可以选择此选项。但是,如果您更改数据透视表,则计算可能不会相应更新,并且可能会给您错误的结果或错误。如下所示,我计算了当行中有零售商时的利润率。但是当我将它从客户更改为区域时,公式出错了。
- 使用数据透视表计算字段。这是使用现有数据透视表数据和计算所需指标的最有效方式。将计算字段视为您使用数据透视表中的现有列添加的虚拟列。使用数据透视表计算字段有很多好处(稍后我们将看到):
- 它不需要您处理公式或更新源数据。
- 它是可扩展的,因为它会自动考虑您可能添加到数据透视表中的任何新数据。添加计算字段后,您可以像使用数据透视表中的任何其他字段一样使用它。
- 它易于更新和管理。例如,如果指标发生变化或您需要更改计算,您可以从数据透视表本身轻松完成。
将计算字段添加到数据透视表
让我们看看如何在现有数据透视表中添加数据透视表计算字段。
假设您有一个如下所示的数据透视表,并且您想要计算每个零售商的利润率:
以下是添加数据透视表计算字段的步骤:
添加计算字段后,它将显示为数据透视表字段列表中的字段之一。
现在您可以将此计算字段用作任何其他数据透视表字段(请注意,您不能将数据透视表计算字段用作报表过滤器或切片器)。
正如我之前提到的,使用数据透视表计算字段的好处是您可以更改数据透视表的结构,它会自动调整。
例如,如果我将区域拖放到行区域中,您将得到如下所示的结果,其中报告了零售商和区域的利润率值。
在上面的示例中,我使用了一个简单的公式(=Profit/Sales) 来插入计算字段。但是,您也可以使用一些高级公式。
在我向您展示使用高级公式创建数据透视表计算字段的示例之前,您必须了解以下几点:
- 在创建数据透视表计算字段时,您不能使用引用或命名范围。这将排除很多公式,例如VLOOKUP、INDEX、OFFSET 等。但是,您可以使用无需引用即可工作的公式(例如SUM、IF、COUNT 等..)。
- 您可以在公式中使用常数。例如,如果您想知道预计增长10% 的预测销售额,您可以使用公式=Sales*1.1(其中1.1 是常数)。
- 在生成计算字段的公式中遵循优先顺序。作为最佳实践,使用括号确保您不必记住优先顺序。
现在,让我们看一个使用高级公式创建计算字段的示例。
假设您有如下所示的数据集,并且您需要在数据透视表中显示预测的销售额。
对于预测值,您需要对大型零售商(销售额超过300 万)使用5% 的销售额增长,对中小型零售商(销售额低于300 万)使用10% 的销售额增长。
注意:此处的销售数字是假的,已用于说明本教程中的示例。
以下是如何做到这一点:
这会在数据透视表中添加一个包含销售预测值的新列。
数据透视表计算字段的问题
计算字段是一项了不起的功能,它通过字段计算真正提高了数据透视表的价值,同时仍然保持一切可扩展和可管理。
但是,数据透视表计算字段存在一个问题,您必须在使用它之前了解它。
假设,我有一个如下所示的数据透视表,我使用计算字段来获取预测销售数字。
请注意,小计和总计不正确。
虽然这些应该为每个零售商添加单独的销售预测值,但实际上,它遵循我们创建的相同计算字段公式。
因此,对于South Total,虽然该值应为22,824,000,但South Total 错误地将其报告为22,287,000。发生这种情况是因为它使用公式21,225,800*1.05 来获取值。
不幸的是,您无法纠正此问题。
处理此问题的最佳方法是从数据透视表中删除小计和总计。
您还可以通过 Debra 展示的一些创新解决方法来处理此问题。
如何修改或删除数据透视表计算字段?
创建数据透视表计算字段后,您可以使用以下步骤修改公式或将其删除:
如何获取所有计算字段公式的列表?
如果您创建了很多数据透视表计算字段,则不必担心跟踪每个字段中使用的公式。
Excel 允许您快速创建用于创建计算字段的所有公式的列表。
以下是快速获取所有计算字段公式列表的步骤:
- 选择数据透视表中的任何单元格。
- 转到数据透视表工具-> 分析-> 字段、项和集-> 列出公式。
一旦您单击列表公式,Excel 将自动插入一个新工作表,其中包含您在数据透视表中使用的所有计算字段/项目的详细信息。
如果您必须将工作发送给客户或与您的团队共享,这可能是一个非常有用的工具。