如何让基于数据模型的Excel透视表GETPIVOTDATA函数更具动态性

核心调整逻辑 #

数据模型作为源的透视表GETPIVOTDATA函数，维度参数需要传入符合MDX规范的完整成员表达式，不能直接传入维度值，需要把左侧单元格的内容拼接到成员表达式的固定结构中。

最终可用的VBA公式字符串 #

你可以直接使用以下拼接完成的公式，和你之前旧版本的逻辑一致，RC[-1]自动引用当前行左侧相邻单元格的内容：

"=GETPIVOTDATA(""[Measures].[Sum of QTY invoiced]"",Pivot!R3C2,""[Dim_Item_warehouse].[Product Family]"",""[Dim_Item_warehouse].[Product Family].&[""&RC[-1]&""]"")"

注意事项 #

• 需保证左侧列的单元格值和Dim_Item_warehouse维度中Product Family的成员值完全匹配，无多余空格、大小写一致，否则会返回#REF!错误
• 如果维度成员包含方括号、&等特殊字符，需要先对单元格值做转义处理，普通文本场景下上述公式可直接使用
• VBA中批量填充时，直接将上述字符串赋值给目标区域的FormulaR1C1属性即可，示例：

' 示例：给B列2到100行批量填充公式，引用左侧A列的产品族值
Range("B2:B100").FormulaR1C1 = "=GETPIVOTDATA(""[Measures].[Sum of QTY invoiced]"",Pivot!R3C2,""[Dim_Item_warehouse].[Product Family]"",""[Dim_Item_warehouse].[Product Family].&[""&RC[-1]&""]"")"

内容的提问来源于stack exchange，提问作者leonhnoel