Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Add a calculated field in a Pivot table with a reference to a cell of the Pivot Table

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')

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

My Pivot Table

enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

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

enter image description here

...and here's the result:

enter image description here

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:

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":

enter image description here

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:

enter image description here

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

enter image description here

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)

enter image description here

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

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...