Como calcular médias não ponderadas na tabela dinâmica do Excel?

Question

Geralmente, faço Tabelas Dinâmicas nas quais cada linha contém várias medidas médias por pessoa. Então eu quero olhar para a média da coluna não ponderada para cada medida, e geralmente fazer algum tipo de gráfico a partir deles.

Como minhas células individuais geralmente têm uma média de diferentes números de pontos de dados, a linha "Total geral" acaba sendo uma média ponderada, na qual não estou interessado. Então, costumo fazer minha própria linha média algumas linhas acima da tabela para usar nos meus gráficos.

Isso não é muito trabalho, mas há outro problema. Costumo adicionar mais dados de algumas pessoas à fonte de Tabelas Dinâmicas e atualizar as tabelas. Isso significa que minha linha média precisa ser atualizada para abranger mais linhas da Tabela Dinâmica. Não é um grande negócio com uma mesa, mas quando eu tenho muitas delas em várias folhas, eu tenho que encontrar / substituir em um monte de fórmulas.

Portanto: existe uma maneira de obter automaticamente médias de coluna não ponderadas em uma Tabela Dinâmica, de modo que, quando a tabela é atualizada, as médias

1. não mude de local e
2. englobam os dados recém-adicionados (ou removidos)

Obrigado

    

Answer 1

Isso é um pouco desajeitado, mas funciona. Se você tem uma única 'categoria' (por exemplo, pessoa), você pode simplesmente usar AverageIf no exemplo a seguir. Se você tiver vários critérios (por exemplo, pessoa, produto ou sexo, ou cidade natal), você precisa do AverageifS (plural - disponível apenas no Excel 2007).

Você precisa de uma coluna auxiliar ao lado dos seus dados. Aqui vamos encontrar:

• esta é a primeira ocorrência dessa pessoa (mais segundo, terceiro critério)?
• se sim, qual é a média para essa pessoa?
• se não, BLANK (não zero).

Então, se o nome da pessoa está na coluna A, seu valor a ser medido está na coluna B, a linha 1 é cabeçalhos e a linha 2 a 1000 é dados, na célula C2 você precisa de uma fórmula como esta:

=IF(COUNTIF($A$2:A2,A2)=1,AVERAGEIF($A$2:$A$1000,A2,$B$2:$B$1000),"")

Se você tiver dois critérios (por exemplo, pessoa e produto, região ou qualquer outro), estenda isso usando COUNTIFS e AVERAGEIFS (seu valor medido agora está na coluna C e observe que esse se torna o primeiro argumento do averageifS, não o último ):

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,AVERAGEIF($C$1:$C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2),"")

Agora você tem uma coluna onde a média de cada pessoa é exibida exatamente uma vez.

Inclua um título para esta coluna auxiliar extra ("Média pessoal"). Altere a origem de sua tabela dinâmica para incluir esses dados extras e atualizar. Inclua a coluna extra e certifique-se de resumi-la por média, em vez de soma. Agora seus subtotais por categoria ou seus totais gerais (se você não tiver categorias de nível superior) serão precisos, já que em todos os casos a média de cada pessoa é simplesmente a média de um único número que já foi calculado. suprimida.

Eu sei que é um pouco complicado, mas quando feito a fórmula finalizada pode sempre ser copiada à medida que você adiciona dados (ou usa uma Tabela para que o Excel faça isso para você), e o resultado é parte da tabela dinâmica para que você possa facilmente produzir um único relatório.