Calcula a pontuação média por categoria no Excel

Question

Suponha que eu tenha as 2 folhas a seguir em uma pasta de trabalho do Excel:

Folha companies :

     A       B       C
1 COMPANY COUNTRY  SCORE
2 Foo     Greece    10
3 Bar     Greece     2
4 Baz     Greece     3
5 Foobar  Japan      8
6 Foobaz  Denmark    6
7 BazBar  Denmark    7

Folha countries :

     A        B
1 COUNTRY AVG_SCORE
2 Denmark    6.5
3 Greece     5.0
4 Japan      8.0

Calculei a pontuação média do país ( AVG_SCORE ) à mão. É calculado somando todos os escores de país e dividindo pelo número de observações. Por exemplo, a Dinamarca tem 2 empresas, totalmente uma pontuação de 6 + 7 , que dividido por 2 é 6.5 .

Como tenho milhares de empresas na primeira planilha, preciso de uma fórmula para a célula countries!B2 (e para baixo) para calcular as pontuações médias dos países.

Tendo em mente que os dados da empresa estão em uma planilha diferente das pontuações médias, qual fórmula pode calcular o AVG_SCORES automaticamente?

    

Answer 1

Se você tiver o Excel 2007+, poderá usar AVERAGEIF . Usando seus dados como modelo, você gostaria de colocar isso na coluna B da guia countries :

=AVERAGEIF(companies!$B$2:$B$7,"=" & A2,companies!$C$1:$C$7)

Se você tem 2003 ou antes, pode usar SUMPRODUCT :

=SUMPRODUCT((companies!$B$2:$B$7=A2)*companies!$C$2:$C$7)/SUMPRODUCT((companies!$B$2:$B$7=A2)*1)

EDIT em resposta ao comentário:

O & é concatenação. ou seja, "a" & "b" == "ab" . Nesse caso, os critérios fornecidos são uma string, portanto, usamos o termo "=" concatenado com o país que queremos procurar. O valor está em uma célula, A2 , então, em vez de digitá-lo, vinculamos o "=" ao valor da célula com "=" & A2 , que é o mesmo que "=Denmark" .

    

Answer 2

Outra opção seria criar uma tabela dinâmica, escolhendo o país no campo de linha e o avg como o campo de valor. Em seguida, você pode vincular sua segunda planilha à tabela dinâmica criada acima.