Referenciando vários campos e itens de uma única célula - GETPIVOTDATA

Question

Eu já perguntei isso no StackOverflow, mas percebo que pode ser mais apropriado aqui.

É possível fazer referência a vários campos e itens de uma única célula em uma fórmula GETPIVOTDATA?

Eu tenho um painel que exibe dados de um pivottable com base em vários controles de usuário. Dependendo do que foi selecionado, talvez precise usar um único campo e um item ou vários para fazer referência aos dados corretos. Eu sou capaz de construir uma seqüência de caracteres dos campos e itens necessários em uma célula, mas não consigo inseri-lo em uma fórmula GETPIVOTDATA. Parece tratar a célula como um único item dentro de vírgulas invertidas. Existe alguma coisa em torno disso?

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

O ideal seria avaliar

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

No entanto, parece avaliar para

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

Alguma idéia?

Os dados com os quais estou lidando possuem vários níveis hierárquicos: Gerenciador > Equipa > Agente Dependendo das escolhas do usuário, quero recuperar os dados em qualquer um desses níveis. Isso significa que a instrução GETPIVOTDATA pode precisar de entre 1 e 3 pares de campos / itens para identificar as informações corretas.

Suponho que eu poderia usar três GETPIVOTDATA e envolvê-los com uma função CHOOSE:

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

No entanto, eu esperava uma solução mais simples que movesse todos os valores de 'variáveis' para um único local onde seriam mais fáceis de manter, em vez de distribuídos em todas as células onde eu extraia dados da tabela dinâmica. / p>     

Answer 1

A documentação oficial do Excel indica a sintaxe de GETPIVOTDATA is:

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

No entanto, existe uma maneira alternativa e mais flexível de usar o GETPIVOTDATA que não está documentado:

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

Em que Sum of pode ser substituído por outros tipos de agregação.

A solução para o seu problema é, portanto:

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

E certifique-se de que o intervalo com nome Team e Agent esteja vazio, caso você queira agregá-los. Para maior clareza, você pode querer mover as instruções if para uma célula separada

    

Answer 2

Não.

Mas desde que você tenha "um número de controles de usuário", porque não simplesmente construir seu GETPIVOTDATA diretamente de sua saída, por exemplo:

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

Portanto, para cada controle, coloque a saída em sua própria célula (com alguma verificação de erros ao longo do caminho, é claro) e use-os em sua fórmula.

(Além disso, para a robustez, eu sempre usaria uma célula para obter o rótulo de campo, em vez de codificá-lo, por exemplo, referência ao título da coluna original. Desta forma, se alguém renomeia "Team" para "Group" ou "ID" para "SSN" na tabela de origem original e, portanto, no PT, sua fórmula não será interrompida.

Infelizmente, isso não fornece uma maneira simples de ignorar um item vazio, como se o ID não fosse especificado, mas é claro que você pode envolver tudo em uma instrução IF para verificar isso:

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

Isso só funcionará se sua hierarquia de PT for Equipe > Id e Team têm subtotais visíveis (GetPivotData só retornará valores já calculados e exibidos no PT).

Ou simplesmente diga ao usuário que há um problema se ele não preencher tudo:

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