从单个单元格引用多个字段和项目 - GETPIVOTDATA

从单个单元格引用多个字段和项目 - GETPIVOTDATA

我已经在 StackOverflow 上问过这个问题,但意识到在这里问可能更合适。

是否可以将单个单元格中的多个字段和项目引用到 GETPIVOTDATA 公式中?

我有一个仪表板,它根据多个用户控件显示来自数据透视表的数据。根据所选内容,我可能需要使用一个或多个字段和项目来引用正确的数据。我能够在单元格中构建所需字段和项目的字符串,但似乎无法将其插入到 GETPIVOTDATA 公式中。它似乎将单元格视为引号内的单个项目。有什么办法可以解决这个问题吗?

Example code:
=GetPivotData("Time", PTSchedule, $A$1)
Cell $A$1 contains "ID", $A$5, "Team", "Team 1"

理想情况下,这将评估为

=GetPivotData("Time", PTSchedule, "ID", $A$5, "Team", "Team 1")

然而,它似乎评估

=GetPivotData("Time", PTSchedule, ""ID", $A$5, "Team", "Team 1"")

有任何想法吗?

我处理的数据有几个层次:经理 > 团队 > 代理 根据用户的选择,我想提取其中任何一个级别的数据。这意味着 GETPIVOTDATA 语句可能需要 1 到 3 个字段/项目配对才能识别正确的信息。

我想我可以使用三个 GETPIVOTDATA 并用 CHOOSE 函数包装它们:

=CHOOSE(Selection, GETPIVOTDATA("Sales", PTSALES, "Manager", Manager),
                   GETPIVOTDATA("Sales", PTSALES, "Manager", Manager, "Team", Team),
                   GETPIVOTDATA("Sales", PTSALES, "Manager", Manager, "Team", Team, "Agent", Agent))

然而,我希望有一个更简洁的解决方案,将所有“变量”值移动到一个更容易维护的位置,而不是分布在我从数据透视表中提取数据的每个单元格中。

答案1

Excel 官方文档指出的语法GETPIVOTDATA是:

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

但是,还有一种替代的、更灵活的使用方法,GETPIVOTDATA但尚未记录:

GETPIVOTDATA(pivot_table,"'Sum of " & data_field & "' '" & item1 & 
     "' '" & item2 & "' '" & ... & "'")

可以Sum of用其他聚合类型替代。

因此,解决您的问题的方法是:

GETPIVOTDATA(
  PTSALES,
  "'Sum of Sales' " &
  "'" & Manager & "' " &
  if(len(Team>0),"'" & Team & "' ","") &
  if(len(Agent>0),"'" & Agent & '","")
)

并确保命名范围TeamAgent为空,以防您需要对它们进行聚合。为了清晰起见,您可能需要将 if 语句移至单独的单元格

答案2

不。

但是既然您有“许多用户控件”,为什么不直接从它们的输出构建您的 GETPIVOTDATA 例如:

=GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7)

因此,对于每个控制,将输出放入其自己的单元格(当然在此过程中进行一些错误检查)并在公式中使用它们。

(另外:为了稳健性,我总是使用单元格来获取字段标签,而不是对其进行硬编码,例如引用原始列标题。这样,如果有人在原始源表中将“Team”重命名为“Group”或将“ID”重命名为“SSN”,因此在 PT 中,您的公式不会中断。)

不幸的是,这并没有提供一种简单的方法来忽略空项(例如未指定 IDis 的情况),但是当然您可以将整个内容包装到 IF 语句中来检查这一点:

=IF($A$5="",GETPIVOTDATA("Time", PTSchedule, "Team", $B$7),GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7))

这仅当您的 PT 层次结构为 Team > Id 并且 Team 具有可见的小计时才有效(GetPivotData 将仅返回已在 PT 中计算和显示的值)。

或者简单地告诉用户,如果用户没有填写所有内容,就会出现问题:

=IFERROR(GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7),"Please choose all parameters")

相关内容