Excel, Média de uma coluna específica dentro da tabela

Question

Estou tentando calcular uma média apenas para uma coluna dentro de uma tabela de valores.

É assim:

Hi   Hello  Hey
1  |  2   | 3
1  |  2   | 3
1  |  2   | 3

E eu estou tentando obter a média para uma dessas colunas, mas qual depende de outra célula que vai me dizer Hi, Hello ou Hey. Vamos dizer que tal célula é K5

Então, pensei em algo como averageif (A1: C1; "=" & K5; A2: C4). Com o K5 sendo Hi a fórmula retornou o valor 1. Mas quando mudei um valor no Hi collumnn, tendo então:

Hi   Hello  Hey
1  |  2   | 3
7  |  2   | 3
1  |  2   | 3

A fórmula ainda me mostra 1 (deve ser 3), então é só levar em consideração a segunda linha.

Como posso calcular a média de A2: A4 se K5 for Hi, B2: B4 se K5 for Hello ou C2: C4 se K5 for Hey?

Obrigado.

    

Answer 1

AverageIf não foi criado para avaliar uma tabela inteira e retornar apenas uma coluna. Para alcançar o resultado descrito, você poderia usar algo como

=AVERAGE(IF(A1:C1=K5,A2:C4,""))

Esta é uma fórmula de matriz e deve ser confirmada com Ctrl + Deslocar + Enter .

    

Answer 2

Se você usar a função de tabela no Excel para criar os dados desejados como uma tabela
( Destaque tudo e pressione CTRL + T ) então você pode calcular a média usando:

=AVERAGE([Hi]) 

Supondo que o cabeçalho da tabela é chamado Hi .

Você pode usar isso em uma função maior, como

=100 - ([@hi]) / ( MAX([hi]) + AVERAGE([hi]) ) 

ou mais, se você quiser expandir isso ainda mais.

O =([@hi]) daria o valor atual de hi para essa linha. O resto é bastante auto-explicativo.

    

Answer 3

Eu darei as duas primeiras formas que você teve em mente.

=average(offset(A2:C2,0,match(K5,A1:C1,0)-1))

offset desloca seu range, match encontrará K5 nas células A1: C1 e retornará qual célula é (1,2,3) subtrai uma para tornar a linha correta.

Como alternativa, se você tiver os dados armazenados como uma tabela, encontrará a média de qualquer coluna com o nome do cabeçalho em K5.

=average(INDIRECT("Table1["&K5&"]"))

Indireto basicamente permite usar variáveis para definir referências de células.