Estou tentando fazer um resumo de alguns resultados de limpeza no Excel com o auxílio de uma Tabela Dinâmica, mas estou tendo alguns problemas para finalizar tudo. Eu tenho os seguintes dados:
Cleaning 1 Location 1 Result 1
Cleaning 1 Location 2 Result 2
Cleaning 1 Location 3 Result 3
Cleaning 2 Location 1 Result 4
Cleaning 2 Location 2 Result 5
Cleaning 2 Location 3 Result 6
Cleaning 3 Location 1 Result 7
Cleaning 3 Location 2 Result 8
Cleaning 3 Location 3 Result 9
Atualmente possuo uma Tabela Dinâmica contendo a média e o desvio padrão de cada local, bem como a média e o desvio padrão de todos os resultados combinados. Gostaria também de ter average + 3 * standard deviationDaar para cada local, bem como para todos os resultados combinados.
As informações que encontro tratam do cálculo de parâmetros adicionais com base nas colunas ORIGINAIS dos dados, mas não combinando os dados RESULTANTES na Tabela Dinâmica. Qualquer ajuda?
O que eu tentei até agora:
GETPIVOTDATAé uma boa fórmula, mas não funciona idealmente para mim, pois usarei as opções de filtro da tabela dinâmica. Além disso, não parece funcionar para o total.
Responder1
A menos que você faça algum trabalho de VBA, acho que ficará preso ao GETPIVOTDATA. Abaixo, vou mostrar que funciona e como fazer funcionar quando você está mexendo na filtragem.
As pessoas ficam tentadas a tentar usar cálculos de campo. Eles funcionam "linha por linha" e você não tem acesso ao valor correto para N ou STDEV. Mais uma vez, demonstrarei abaixo.
Começando por esses dados, juntei a partir da sua pergunta...
...esta Tabela Dinâmica foi construída...
Isso não funciona
Foi feita uma tentativa de criar um Cálculo de Campo...
... com os seguintes resultados (errôneos) ...
Isso funciona
Para implementar cálculos de planilha que não serão afetados pela alteração da filtragem na Tabela Dinâmica, insira linhas acima da Tabela Dinâmica até ter espaço suficiente para os cálculos necessários.
Para o seu exemplo, o seguinte foi construído ...
... Uma equação típica para as células B2 a B4 é ...
=IF(ISERROR(GETPIVOTDATA("Average of Result",$A$7,$A$1,$A2)),"",GETPIVOTDATA("Average of Result",$A$7,$A$1,$A2)+3*GETPIVOTDATA("StdDev of Result",$A$7,$A$1,$A2))
... Esta equação verifica se a Tabela Dinâmica contém informações para o "Local 1". Caso contrário, nada será exibido. Se isso acontecer, ele adiciona 3 vezes o desvio padrão do Local 1 à média do Local 1.
As células C2 a C4 são iguais, exceto pela subtração. A célula B5 contém ...
=IF(ISERROR(GETPIVOTDATA("Average of Result",$A$7)),"",GETPIVOTDATA("Average of Result",$A$7)+3*GETPIVOTDATA("StdDev of Result",$A$7))
Esta equação verifica se os resultados do Total Geral estão presentes. Se não estiverem, nada será exibido. Se forem, então a média total geral + 3 vezes o desvio padrão total geral é calculada.
Quando a filtragem é alterada ...
... quando os totais gerais são removidos da tabela dinâmica ...
