在数据透视表中添加对数据透视表单元格的引用的计算字段 [英] Add a calculated field in a Pivot table with a reference to a cell of the Pivot Table

问题描述

我一直试图在数据透视表中添加一个计算字段.到目前为止，整个数据透视表均已完美创建.我要添加id的最后一列是计算的.不幸的是，到目前为止，我什至无法手动完成.但是，如果我只是将下面提供的公式作为公式插入到数据透视表的旁边，那么它可以完美地工作.

I have been trying to add a calculated field in the pivot table. The entire pivot table is created perfectly so far. The last column that I want to add id the calculated one. Unfortunately, so far I cannot even do it manually. However, if I just insert as a formula right next to the pivot table the below provided formula, then it works perfectly.

=GETPIVOTDATA('Monthly $/SU Forecast',$A$15,'T-Lane','Athens to GREECE')-GETPIVOTDATA('Monthly $/SU Actual',$A$15,'T-Lane','Athens to GREECE')

当我在宏中键入以下内容时，它会在行中显示无法获取PivotTable类的PivotFields属性"

When I type the below inside the macro then it says 'Unable to get the PivotFields property of the PivotTable class' for the line

.PivotFields("Diff").Orientation = xlDataField

代码的整个部分是这样的:

The entire part of the code is this :

.CalculatedFields.Add "Diff", "=GETPIVOTDATA('Monthly $/SU Forecast',$A$15,'T-Lane','Athens to GREECE')-GETPIVOTDATA('Monthly $/SU Actual',$A$15,'T-Lane','Athens to GREECE')"
.PivotFields("Diff").Orientation = xlDataField

我的数据透视表

推荐答案

您计算出的字段公式无效，这就是为什么您不能手动添加它.根据 http://www.contextures.com/excel上的文章-pivot-table-calculated-field.html

Your calculated field formula is invalid, which is why you can't add it manually. As per the article at http://www.contextures.com/excel-pivot-table-calculated-field.html

• 计算的字段公式不能引用数据透视表总计或 小计
• 计算的字段公式不能按地址引用工作表单元格 或按名称.
• 求和是唯一可用于计算字段的函数.
• 计算的字段在基于OLAP的数据透视表中不可用.

• 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.

以下是显示如何设置计算字段的画面:

Here's a screeshot showing how to set up a calculated field:

...结果如下:

请注意，以这种方式创建计算字段已变得多余，例如在Excel 2013及更高版本中，您可以使用数据模型执行此操作，并且可以执行更多操作(即使您的Excel版本不是PowerPivot插件随附的高级" SKU之一).

Note that creating Calculated Fields in this manner has pretty much been made redundant, as in Excel 2013 and later you can use the Data Model to do this and a heck of a lot more (even if your version of Excel isn't one of the 'premium' SKUs that ship with the PowerPivot addin).

以下是显示该方法的示例:

Here's an example showing that approach:

首先，选择您的数据源，将其转换为Excel表，然后创建一个新的数据透视表，但是这次选中对话框底部的框将数据添加到数据模型" :

First, select your data source, turn it into an Excel Table, and then create a new PivotTable but this time check the box at the bottom of the dialog "Add this data to the Data Model":

单击生成的数据透视表时，您会看到数据透视表字段"对话框看起来略有不同:它将显示表名称，然后在该表下方将列出每个字段:

When you click on the PivotTable this produces, you'll see that the PivotTable Fields dialog looks slightly different: It will show the Table name, and then under that table it will list each field:

右键单击该对话框中的表名"，然后单击添加度量"选项:

Right-click on the Table name in that dialog, and then click the Add Measure option:

给Measure一个名称，然后在对话框中键入所需的公式. (实际上，您不必键入总和" ...只需键入预测"，您会看到一个自动完成框，您可以在其中选择所需的字段)

Give the Measure a name, and type the formula you want in the dialog box. (You don't actually have to type that 'Sum of' stuff...just type 'Forecast' and you'll see an autocomplete box come up where you can choose the field you want)

Bam:您在数据透视表中有了一个新的计算字段:

Bam: You've got a new calculated field in the PivotTable:

这篇关于在数据透视表中添加对数据透视表单元格的引用的计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！