O Excel tornou o cálculo da média de várias células uma tarefa muito fácil - basta usar a função MÉDIA (MÉDIA). Mas e se alguns valores tiverem mais peso do que outros? Por exemplo, em muitos cursos, os testes têm mais peso do que as tarefas. Para esses casos, é necessário calcular média ponderada.
O Excel não possui uma função para calcular a média ponderada, mas existe uma função que fará a maior parte do trabalho para você: SUMPRODUCT (SOMA DO PRODUTO). E mesmo que você nunca tenha usado esse recurso antes, no final deste artigo você o usará como um profissional. O método que usamos funciona em qualquer versão do Excel, bem como em outras planilhas, como o Planilhas Google.
Preparamos a mesa
Se você for calcular uma média ponderada, precisará de pelo menos duas colunas. A primeira coluna (coluna B em nosso exemplo) contém as pontuações de cada tarefa ou teste. A segunda coluna (coluna C) contém os pesos. Mais peso significa mais influência da tarefa ou teste na nota final.
Para entender o que é peso, você pode pensar nele como uma porcentagem da sua nota final. Na verdade, este não é o caso, pois neste caso os pesos devem somar 100%. A fórmula que analisaremos nesta lição calculará tudo corretamente e não depende da quantidade que os pesos somam.
Entramos na fórmula
Agora que nossa tabela está pronta, adicionamos a fórmula à célula B10 (qualquer célula vazia serve). Como em qualquer outra fórmula do Excel, começamos com um sinal de igual (=).
A primeira parte da nossa fórmula é a função SUMPRODUCT (SOMA DO PRODUTO). Os argumentos devem ser colocados entre colchetes, então os abrimos:
=СУММПРОИЗВ(
=SUMPRODUCT(
Em seguida, adicione os argumentos da função. SUMPRODUCT (SUMPRODUCT) pode ter vários argumentos, mas geralmente dois são usados. Em nosso exemplo, o primeiro argumento será um intervalo de células. B2:B9A que contém as pontuações.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
O segundo argumento será um intervalo de células C2:C9, que contém os pesos. Esses argumentos devem ser separados por ponto e vírgula (vírgula). Quando tudo estiver pronto, feche os colchetes:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Agora vamos adicionar a segunda parte da nossa fórmula, que dividirá o resultado calculado pela função SUMPRODUCT (SOMAPRODUTO) pela soma dos pesos. Discutiremos mais tarde por que isso é importante.
Para realizar a operação de divisão, continuamos a fórmula já inserida com o símbolo / (barra reta) e, em seguida, escreva a função SOMA (SOMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Para função SOMA (SUM) especificaremos apenas um argumento – um intervalo de células C2:C9. Não se esqueça de fechar os parênteses depois de inserir o argumento:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Preparar! Após pressionar a tecla Entrar, o Excel calculará a média ponderada. No nosso exemplo, o resultado final será 83,6.
Como Funciona
Vamos dividir cada parte da fórmula, começando com a função SUMPRODUCT (SUMPRODUCT) para entender como funciona. Função SUMPRODUCT (SUMPRODUCT) calcula o produto da pontuação de cada item e seu peso e, em seguida, soma todos os produtos resultantes. Em outras palavras, a função encontra a soma dos produtos, daí o nome. Então para Tarefas 1 multiplique 85 por 5, e para O teste multiplique 83 por 25.
Se você está se perguntando por que precisamos multiplicar os valores na primeira parte, imagine que quanto maior o peso da tarefa, mais vezes temos que considerar a nota para ela. Por exemplo, Tarefa 2 contado 5 vezes e Exame final – 45 vezes. É por isso Exame final tem um impacto maior na nota final.
Para efeito de comparação, ao calcular a média aritmética usual, cada valor é levado em consideração apenas uma vez, ou seja, todos os valores têm o mesmo peso.
Se você pudesse olhar sob o capô de uma função SUMPRODUCT (SUMPRODUCT), vimos que de fato ela acredita nisso:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Felizmente, não precisamos escrever uma fórmula tão longa porque SUMPRODUCT (SUMPRODUCT) faz tudo isso automaticamente.
Uma função em si SUMPRODUCT (SUMPRODUCT) nos retorna um número enorme - 10450. Neste ponto, a segunda parte da fórmula entra em jogo: /SOMA(C2:C9) or /SOMA(C2:C9), que retorna o resultado para o intervalo normal de pontuações, dando a resposta 83,6.
A segunda parte da fórmula é muito importante porque permite corrigir cálculos automaticamente. Lembra que os pesos não precisam somar 100%? Tudo isso graças à segunda parte da fórmula. Por exemplo, se aumentarmos um ou mais valores de peso, a segunda parte da fórmula simplesmente dividirá pelo valor maior, resultando novamente na resposta correta. Ou podemos tornar os pesos muito menores, por exemplo, especificando valores como 0,5, 2,5, 3 or 4,5, e a fórmula ainda funcionará corretamente. É ótimo, certo?