如何创建引用excel数据透视表中的单元格值的计算字段

I have my data that looks like the following:

我的数据如下所示:

Col1 | Value
A    | 1
B    | 1
A    | 3
C    | 7

and a pivot table that sums up the values for col1. In addition I have an exchange rete in a another cell (in another worksheet) and I would like to create a calculated field in my pivot table that multiplies the Value for the exchange rate in that cell. Is this possible? How? The reason for this is that the users might change the exchange rate in the cell but they would not know how to change it in the calculated field. I know that I can simply add a column in my source data with the calculated value, but I would like to do it as calculated pivot table field if this is at all possible.

和一个数据表,它总结了col1的值。另外,我在另一个单元格(在另一个工作表中)有一个交换rete,我想在我的数据透视表中创建一个计算字段,该字段乘以该单元格中汇率的值。这可能吗?怎么样?这样做的原因是用户可能会更改单元格中的汇率,但他们不知道如何在计算字段中更改它。我知道我可以在我的源数据中添加一个具有计算值的列,但是如果可能的话,我想将其作为计算的数据透视表字段。

1 个解决方案

#1

You can achieve something similar to what you're after by 'slaving' a Table to a PivotTable, with a little VBA. See this answer I posted, that can be adapted to effectively achieve what you want.

通过使用一点VBA将一个Table“slaving”到数据透视表,你可以实现类似于你所追求的东西。看到我发布的这个答案,可以适应有效实现你想要的。

Or alternately, you could multiply the individual totals in the source data by a named range called Exchange_Rate, and have a small bit of VBA that refreshes the Pivot whenever that Exchange_Rate input parameter is changed by users.

或者,您可以将源数据中的各个总计乘以一个名为Exchange_Rate的命名范围,并且只要用户更改了Exchange_Rate输入参数,就会有一小部分VBA刷新数据透视表。

Here's how that looks:

这是看起来如何:

...and if I change the ExchangeRate variable in that orange input cell:

...如果我在该橙色输入单元格中更改了ExchangeRate变量:

To do this, I have a small snippet of VBA in the relevant Sheet Module that simply monitors that input cell for any change, and then refreshes the PivotTable so that the Pivot reflects the updated values in the underlying source data:

为此,我在相关的Sheet Module中有一小段VBA,它只是监视输入单元格是否有任何变化,然后刷新数据透视表,以便Pivot反映底层源数据中的更新值:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ExchangeRate")) Is Nothing Then ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Or you could record a macro while setting up a calculated field at a specific exchange rate, then modify that code so that the rate part gets replaced to match any change to that orange cell:

或者您可以在设置特定汇率的计算字段时记录宏,然后修改该代码以便更换费率部分以匹配对该橙色单元格的任何更改:

...and here's the event handler code that does this:

...这是执行此操作的事件处理程序代码:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ExchangeRate")) Is Nothing Then _
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("Local Cost"). _
        StandardFormula = "='Value ($USD)'/ " & Range("ExchangeRate")
End Sub

Or if you have the ability to use PowerPivot, you could use Rob Collie's Disconnected Slicer trick.

或者如果你有能力使用PowerPivot,你可以使用Rob Collie的Disconnected Slicer技巧。

#1