Calcula valores adicionais com base nos resultados da Tabela Dinâmica

Question

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 envolvendo tudo isso. 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, tenho uma Tabela Dinâmica contendo a média e o desvio padrão de cada localidade, bem como a média e o desvio padrão de todos os resultados combinados. Eu também gostaria de ter average + 3 * standard deviation Daar para cada local, bem como para todos os resultados combinados.

As informações encontradas manipulam o cálculo de parâmetros adicionais com base nas colunas ORIGINAIS dos dados, mas não combinam os dados RESULTING na tabela dinâmica. Alguma ajuda?

O que 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, parece não funcionar para o total.

    

Answer 1

A menos que você faça algum trabalho VBA, acho que você vai ficar preso com GETPIVOTDATA. Abaixo, mostrarei que funciona e como fazê-lo funcionar quando você está mexendo com a filtragem.

As pessoas são tentadas a tentar usar os cálculos de campo. Eles funcionam em uma base "linha por linha" e você não tem acesso ao valor correto para N ou para STDEV. Mais uma vez, vou demonstrar abaixo.

Começando com esses dados, eu reuni sua pergunta ...

...estatabeladinâmicafoicriada...

Issonãofunciona

Foifeitaumatentativadecriarumcálculodecampo...

...comosseguintesresultados(errôneos)...

Issofunciona

ParaimplementaroscálculosdaplanilhaquenãoserãoafetadospelaalteraçãodafiltragemnaTabelaDinâmica,insiralinhasacimadaTabelaDinâmicaatéterespaçosuficienteparaoscálculosnecessários.

Paraoseuexemplo,oseguintefoicriado...

...UmaequaçãotípicaparaascélulasB2aB4é...

=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 "Local 1". Se isso não acontecer, nada será exibido. Em caso afirmativo, adiciona 3 vezes o desvio padrão do Local 1 à média do Local 1.

As células C2 a C4 são as mesmas, 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 estiverem, então a média geral total + 3 vezes o desvio padrão total total é calculada.

Quando a filtragem é alterada ...

...quandoototalgeraléremovidodatabeladinâmica...