Estou tentando usar AVERAGEIF, ou uma função similar, para encontrar a média de valores em uma coluna se um nome aparecer em outro lugar na linha. Normalmente, esse é um uso direto de AVERAGEIF, mas o nome pode aparecer em algumas colunas diferentes dentro da linha.
Em anexo segue um exemplo de brinquedo.
| Pontuação | Jogadores | ||
|---|---|---|---|
| 3 | Kylie | Ana | |
| 4 | Ana | Lois | Michelle |
| 5 | Michelle |
Aqui, eu quero que a coluna de pontuação média seja a média das pontuações nas linhas em que os jogadores aparecem. Por exemplo, Anna aparece nas linhas 2 e 3, então eu quero que ela calcule a média das pontuações nas linhas 2 e 3.
Isto é o que eu quero que a planilha mostre:
| Nome | Contar | Pontuação média |
|---|---|---|
| Ana | 2 | 3.5 |
| Kylie | 1 | 3 |
| Lois | 1 | 4 |
| Michelle | 2 | 4.5 |
Caso seja importante, a planilha real tem mais colunas que podem conter os nomes, mas eles não devem ser contabilizados fora das colunas especificadas. Então, preciso restringi-la a certas colunas, não apenas a qualquer lugar da linha.
Eu obtive a parte dos resultados pretendidos para automatizar os nomes fazendo =sort(UNIQUE(FLATTEN(B2:D4)))(onde B2:D4 é o intervalo dos jogadores) em A7 e a contagem de aparências =countif(B$2:D$4,A7)em B7. A pontuação média é onde estou tendo problemas.
No C7, tentei fazer isso =averageif(B$2:D$4,A7,A$2:A$4)e estendê-lo para baixo, mas em vez do que eu queria, obtive o seguinte.
| Nome | Contar | Pontuação média |
|---|---|---|
| Ana | 2 | 4 |
| Kylie | 1 | 3 |
| Lois | 1 | #DIV/0! |
| Michelle | 2 | 5 |
Parece que para Anna e Michelle ele só pegou a segunda pontuação que se aplica a elas em vez da média, mas não consigo entender por que ele só me deu um erro para Lois.
Você não pode usar Averageif porque as dimensões do intervalo de critérios (B2:D4) devem corresponder às dimensões do intervalo de números a serem calculados como média (A2:A4). Sua fórmula está usando apenas a primeira coluna de B2:D4 como critério: Lois não aparece na primeira coluna, então seu resultado é #DIV/0!
Em princípio, você poderia duplicar A2:A4 três vezes assim
{A2:A4,A2:A4,A2:A4}para corresponder às dimensões de B2:D4, mas isso não funciona porque seria uma matriz e o Averageifs espera um intervalo.Uma maneira simples de contornar isso (desde que cada nome só possa ocorrer uma vez por linha) é usar a multiplicação de matrizes como esta para obter a soma e dividir pela contagem:
Você pode tentar:
Outra abordagem para obter toda a output_table de uma só vez: