Ссылка на несколько полей и элементов из одной ячейки - GETPIVOTDATA

Question

Я уже спрашивал об этом в 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))

Однако я надеялся на более точное решение, которое позволило бы переместить все «переменные» значения в одно место, где их было бы легче поддерживать, а не распределять по каждой ячейке, где я извлекаю данные из сводной таблицы.

Answer 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 & '","")
)

И убедитесь, что именованный диапазон Team и Agent пусты на тот случай, если вам нужно их объединить. Для ясности вы можете переместить оператор if в отдельную ячейку.

Answer 2

Нет.

Но поскольку у вас есть "несколько пользовательских элементов управления", почему бы просто не сконструировать ваши GETPIVOTDATA непосредственно из их вывода, например:

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

Таким образом, для каждого элемента управления поместите выходные данные в его собственную ячейку (с некоторой проверкой ошибок по ходу процесса) и используйте их в своей формуле.

(Помимо: для надежности я всегда использовал бы ячейку, чтобы получить метку поля, а не жестко ее кодировать, например, ссылаться на исходный заголовок столбца. Таким образом, если кто-то переименует "Team" в "Group" или "ID" в "SSN" в исходной исходной таблице и, следовательно, в PT, ваша формула не будет нарушена.)

К сожалению, это не обеспечивает простой способ игнорирования пустого элемента, например, если ID не указан, но, конечно, вы можете обернуть все это в оператор IF, чтобы проверить это:

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

Это будет работать только в том случае, если ваша иерархия PT имеет Team> Id, а команда имеет промежуточные итоги, видимые (GetPivotData будет возвращать только значения, уже рассчитанные и отображенные в PT).

Или просто скажите пользователю, что есть проблема, если он не заполняет все:

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