☰

使用数据透视表中的平均值（计算字段）

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table
使用数据透视表中的平均值（计算字段）

我有以下数据：

customer_id customer_name   id          date        contract_value  costs   profit_extra_work
200027      Company A       00.161.559  08/07/2015   499.5          50.55   0
200027      Company A       00.161.566  08/07/2015   499.5          30      824.9
200027      Company A       00.188.852  04/04/2016   499.5          0       69.39
200027      Company A       00.190.078  30/05/2016   499.5          98.1    158.29
200027      Company A       00.190.291  14/04/2016   499.5          15      346.06
200027      Company A       00.222.221  12/05/2017   499.5          15      5.1
200027      Company A       00.222.229  12/05/2017   499.5          100.35  400.81
200027      Company A       00.161.561  08/07/2015   499.5          45      269.97
200027      Company A       00.185.058  18/02/2016   499.5          15      77.41
200027      Company A       00.190.074  30/05/2016   499.5          37.5    95.92
200027      Company A       00.190.084  30/05/2016   499.5          45      420.89
200027      Company A       00.194.050  01/06/2016   499.5          139.65  0
200027      Company A       00.222.222  12/05/2017   499.5          60      274.88

我的数据透视表如下所示：

Row Labels  Average of contract_value   Sum of costs    Sum of profit_extra_work
Company A   499.5                       651.15          2943.62

我想要做的是添加一个名为“合同利润”的计算字段。该值可以计算为Average of contract_value - Sum of costs。因此，我尝试使用以下公式添加一个新的计算字段“合同利润”：

= contract_value - costs

但这返回了错误的结果 (5842.35)。原因是它contract_value取总和而不是平均值。我试过使用，AVERAGE(contract_value) - costs但这返回了相同的错误结果。

如何使用计算字段中 contract_value 的平均值来获取正确的值 -151.56？

答案1

微软的帮助页面有些晦涩，指出“自定义公式针对总和进行操作，而不是单个记录。”（“计算数据透视表中的值”这对你的问题有两个含义。（1）公式中对 contract_value 的引用将是和contract_value，即使该字段显示为平均值，（2）诸如“AVERAGE”和“COUNT”之类的函数也不会执行任何操作，因为它们只对单个值（总和）进行操作。

要计算您自己的平均值，您需要在数据表中创建一个新变量，每个条目都有一个“1”。我们将其称为“helper”。自定义公式中的“helper”将是行数的计数。（在您的示例中为 13）。

所以你要找的公式是

=(contract_value /helper )-costs

（请注意，这将针对每一行单独计算，因此，例如，如果您添加“公司 B”，您将获得每家公司的“正确”结果。）

相关内容