在数据透视表中的自定义计算字段中使用公式 [英] Use formula in custom calculated field in Pivot Table

问题描述

在Excel数据透视表报表中，可以通过插入计算字段进行用户干预，以便用户可以进一步操作报表。由于许多明显的原因，在数据透视表之外的数据透视表数据中使用公式似乎是最好的方法。

计算字段对话框如下所示： / p>

，而很容易在可用变量之间进行计算（如截图所示）我无法找到如何引用任何可用变量的值范围。

例如，如果由于某些原因，我想将数据居中范围 A1：A100 我会使用 = A1 - AVERAGE（A1：A100）并填写常规Excel表中的所有行。但是对于数据透视表，如果我使用计算字段对话框并添加新变量，其公式如下： ='实际销售' - AVERAGE（'实际销售'）我得到code> 0 作为输出。

所以我的问题是如何在计算字段对话框中引用实际销售变量的整个范围，以便 AVERAGE（）将返回所有目标单元格的平均值？

解决方案

我会将此评论发布为答案，如我很自信，我问的是不可能的。

I）几个类似的问题试图做同样的事情，没有成功：

• 枢轴计算公式：SUM （Field1）/ AVG（Field2）


• Excel数据透视表计算字段

II）本文： Excel数据透视表计算字段，例如列出了许多限制计算字段的小数：

• 对于计算字段，将其他字段中的单个金额相加，然后计算总计


• 计算的字段公式不能引用数据透视表总计或小计


• 计算的字段公式不能引用工作表单元格按地址或名称。


• Sum是唯一可用于计算字段的函数。


• 计算字段在基于OLAP的数据透视表。

III）使用 AVERAGE（）和类似的功能，但只适用于数据透视表没有分组的单元格，这允许列出单元格作为新组中的项目（右上方的Fileds列表框在上面的屏幕截图），然后用户可以计算 AVERAGE（），从Items列表框显式引用每个项目（单元格）作为参数。也许这里有更好的解释：计算数据透视表报告中的值

对于我的数据透视表，由于我的范围不够小，这个选项是理想的选择，因此不适用。

In Excel Pivot table report there is possibility for user intervention by inserting "Calculated Field" so that user can further manipulate the report. This seems like best approach compared to using formula on Pivot table data, outside the Pivot table, for many obvious reasons.

"Calculated Field" dialog, looks like this:

and while it's easy to do calculation between available variables (as shown in screenshot) I can't find how to reference range of values for any of available variables.

For example, if for some reason I want to center the data in range A1:A100 I'd use = A1 - AVERAGE(A1:A100) and fill all rows in regular Excel table. But for Pivot table, if I use "Calculated Field" dialog and add new variable with formula: = 'Actual Sales' - AVERAGE('Actual Sales') I get 0 as output.

So my question is how can I reference whole range for 'Actual Sales' variable in "Calculated Field" dialog, so that AVERAGE() will return the average of all targeted cells ?

解决方案

I'll post this comment as answer, as I'm confident enough that what I asked is not possible.

I) Couple of similar questions trying to do the same, without success:

• Pivot Calculated formula: SUM(Field1)/AVG(Field2)
• Excel Pivot Table Calculated Field

II) This article: Excel Pivot Table Calculated Field for example lists many restrictions of Calculated Field:

• For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
• Calculated field formulas cannot refer to the pivot table totals or subtotals
• Calculated field formulas cannot refer to worksheet cells by address or by name.
• Sum is the only function available for a calculated field.
• Calculated fields are not available in an OLAP-based pivot table.

III) There is tiny limited possibility to use AVERAGE() and similar function for a range of cells, but that applies only if Pivot table doesn't have grouped cells, which allows listing the cells as items in new group (right to "Fileds" listbox in above screenshot) and then user can calculate AVERAGE(), referencing explicitly every item (cell), from Items listbox, as argument. Maybe it's better explained here: Calculate values in a PivotTable report
For my Pivot table it wasn't applicable because my range wasn't small enough, this option to be sane choice.

这篇关于在数据透视表中的自定义计算字段中使用公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！