Quando calculamos uma média simples de um determinado conjunto de valores, a suposição é que todos os valores têm peso ou importância igual.
Por exemplo, se você aparecer para exames e todos os exames tiverem um peso semelhante, a média de suas notas totais também seria a média ponderada de suas pontuações.
No entanto, na vida real, esse dificilmente é o caso.
Algumas tarefas são sempre mais importantes do que outras. Alguns exames são mais importantes do que outros.
E é aí que Média ponderada entra em cena.
Aqui está a definição de média ponderada do livro didático:
Agora vamos ver como calcular a média ponderada no Excel.
Calculando a média ponderada no Excel
Neste tutorial, você aprenderá a calcular a média ponderada no Excel:
- Usando a função SUMPRODUCT.
- Usando a função SUM.
Então vamos começar.
Calculando a média ponderada no Excel - Função SUMPRODUCT
Pode haver vários cenários em que você precisa calcular a média ponderada. Abaixo estão três situações diferentes onde você pode usar a função SUMPRODUCT para calcular a média ponderada no Excel
Abaixo estão três situações diferentes onde você pode usar a função SUMPRODUCT para calcular a média ponderada no Excel
Exemplo 1 - Quando os pesos somam 100%
Suponha que você tenha um conjunto de dados com notas pontuadas por um aluno em exames diferentes, juntamente com os pesos em porcentagens (conforme mostrado abaixo):
Nos dados acima, um aluno obtém notas em diferentes avaliações, mas, ao final, precisa receber uma nota ou nota final. Uma média simples não pode ser calculada aqui, pois a importância das diferentes avaliações varia.
Por exemplo, um questionário com peso de 10% tem o dobro do peso em comparação com uma tarefa, mas um quarto do peso em comparação com o Exame.
Nesse caso, você pode usar a função SUMPRODUCT para obter a média ponderada da pontuação.
Aqui está a fórmula que fornecerá a média ponderada no Excel:
= SUMPRODUTO (B2: B8, C2: C8)
Aqui está como esta fórmula funciona: A função SUMPRODUCT do Excel multiplica o primeiro elemento da primeira matriz pelo primeiro elemento da segunda matriz. Em seguida, ele multiplica o segundo elemento da primeira matriz com o segundo elemento da segunda matriz. E assim por diante…
E, finalmente, adiciona todos esses valores.
Aqui está uma ilustração para deixar isso claro.
Exemplo 2 - Quando os pesos não somam 100%
No caso acima, os pesos foram atribuídos de forma que o total somasse 100%. Mas em cenários da vida real, pode nem sempre ser o caso.
Vamos dar uma olhada no mesmo exemplo com pesos diferentes.
No caso acima, os pesos somam 200%.
Se eu usar a mesma fórmula de SUMPRODUTO, ele me dará o resultado errado.
No resultado acima, dobrei todos os pesos e ele retorna o valor médio ponderado de 153,2. Agora sabemos que um aluno não consegue mais do que 100 em 100, não importa o quão brilhante ele seja.
A razão para isso é que os pesos não somam 100%.
Aqui está a fórmula que resolverá isso:
= SUMPRODUCT (B2: B8, C2: C8) / SUM (C2: C8)
Na fórmula acima, o resultado SUMPRODUCT é dividido pela soma de todos os pesos. Portanto, não importa o que aconteça, os pesos sempre somam 100%.
Exemplo 3 - Quando os pesos precisam ser calculados
No exemplo coberto até agora, os pesos foram especificados. No entanto, pode haver casos em que os pesos não estão diretamente disponíveis e você precisa calcular os pesos primeiro e, em seguida, calcular a média ponderada.
Suponha que você esteja vendendo três tipos diferentes de produtos, conforme mencionado abaixo:
Você pode calcular o preço médio ponderado por produto usando a função SUMPRODUCT. Aqui está a fórmula que você pode usar:
= SUMPRODUTO (B2: B4, C2: C4) / SOMA (B2: B4)
Dividindo o resultado de SUMPRODUCT com a soma das quantidades garante que os pesos (neste caso, as quantidades) somam 100%.
Calculando a média ponderada no Excel - Função SUM
Embora a função SUMPRODUCT seja a melhor maneira de calcular a média ponderada no Excel, você também pode usar a função SUM.
Para calcular a média ponderada usando a função SUM, você precisa multiplicar cada elemento, com sua importância atribuída em porcentagem.
Usando o mesmo conjunto de dados:
Aqui está a fórmula que lhe dará o resultado certo:
= SOMA (B2 * C2, B3 * C3, B4 * C4, B5 * C5, B6 * C6, B7 * C7, B8 * C8)
Este método pode ser usado quando você tem alguns itens. Mas quando você tem muitos itens e pesos, esse método pode ser complicado e sujeito a erros. Existe uma maneira mais curta e melhor de fazer isso usando a função SUM.
Continuando com o mesmo conjunto de dados, aqui está a fórmula curta que fornecerá a média ponderada usando a função SUM:
= SOMA (B2: B8 * C2: C8)
O truque ao usar esta fórmula é usar Control + Shift + Enter, em vez de apenas usar Enter. Como a função SUM não pode lidar com matrizes, você precisa usar Control + Shift + Enter.
Quando você pressiona Control + Shift + Enter, você vê os colchetes aparecerem automaticamente no início e no final da fórmula (veja a barra de fórmulas na imagem acima).
Novamente, certifique-se de que os pesos somam 100%. Caso contrário, você precisará dividir o resultado pela soma dos pesos (conforme mostrado abaixo, tomando o exemplo do produto):
