使用条件公式 VBA 计算数据透视表中的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10549786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculated Field in Pivot Table using Conditional Formula VBA
提问by brokenp87
I have this problem that I can't solve, I'm writing an Excel Macro that build up a pivot table taking the data from a table. When a field in the table is 0 I don't want to perform the calculation of the calculated field and display a blank cell in the pivot table.
我有一个无法解决的问题，我正在编写一个 Excel 宏来构建一个从表中获取数据的数据透视表。当表中的字段为 0 时，我不想执行计算字段的计算并在数据透视表中显示一个空白单元格。
my conditional formula is this one:
我的条件公式是这样的：
=IF((field1=0),"",(field1/field2))
Putting this formula into VBA as follows
将此公式放入VBA如下
pt.CalculatedFields.Add Name:="ptfieldname", Formula:="=IF((field1=0),"",(field1/field2))"
display an empty pivot table, when I put the same formula in excel using the "Fields, Items & Set" button in the pivot table option tab goes well... so I think that the formula is right, but what I have to do to get it permorf well also within the VBA macro?!
显示一个空的数据透视表，当我使用数据透视表选项选项卡中的“字段、项目和设置”按钮将相同的公式放入 excel 时效果很好......所以我认为该公式是正确的，但我必须做什么在 VBA 宏中也能很好地获得 permorf 吗？！
thanks in advance
提前致谢
采纳答案by Scott Holtzman
Welcome to SO. I am pretty new here myself, but have found it very useful lately.
欢迎来到 SO。我自己是新来的，但最近发现它非常有用。
Anyway, here is your answer. I found it by using the Macro Recorder to see the exact syntax it uses when creating a calculated field in the pivot table. Hopefully the method of my finding the answer helps you find more answers on your own in the future.
无论如何，这就是你的答案。我通过使用宏记录器查看它在数据透视表中创建计算字段时使用的确切语法找到了它。希望我找到答案的方法可以帮助您将来自己找到更多答案。
The problem was in the way you referenced the formula in VBA. VBA needs to have actual quotes inside of quotes, so the
问题在于您在 VBA 中引用公式的方式。VBA 需要在引号内有实际的引号，所以
=IF((field1=0),"",(field1/field2))
in
在
pt.CalculatedFields.Add Name:="ptfieldname", Formula:="=IF((field1=0),"",(field1/field2))"
needs to be
需要是
=If(field1=0,"""",(field1/field2))
