Como Calcular Média Ponderada no Excel

26
51800
calculo média ponderada
Planilhas Prontas em Excel

O conceito de média está sempre relacionado à soma de certos valores divididos pelo total dessa soma, não é mesmo? E está correto. Este é procedimento para o cálculo da média aritmética. Contudo, há também o que chamamos de média ponderada. No post de hoje iremos aprender o seu conceito e como calcula utilizando o Excel. Vamos lá?

O que é a média ponderada?

Utilizando a média simples, ou média aritmética, somamos todos os valores e divididos pela quantidade de termos adicionados. A média ponderada, contudo, é calculada através da soma de todos esses valores multiplicados pelos seus pesos e divididos pela soma dos pesos. Um pouco complexo? Vamos exemplificar em seguida!

Exemplos em que a média ponderada é utilizada

A média ponderada é muito utilizada para o cálculo das notas de alunos. Por exemplo, quando queremos saber a média anual de um determinado aluno em questão. Observe:

O aluno Gustavo obteve as seguintes notas durante os quatro bimestres:

1º Bimestre: 9,0

2º Bimestre: 8,5

3º Bimestre: 7,0

4° Bimestre: 6,0

Curso de Excel - Formatação Condicional

Agora, vamos supor que cada bimestre tem um peso diferente na nota total.

1º Bimestre: 1

2º Bimestre: 2

3º Bimestre: 3

4° Bimestre: 4

Então, como saberemos a média anual dele? Simples! Basta aplicar o conceito da média ponderada:

Como Calcular Média Ponderada no Excel

Média Ponderada = (9 x 1)+(8,5 x 2)+(7 x 3)+(6 x 4) / 1 + 2 + 3 + 4

Média Ponderada = 9 + 17 + 21 + 24 / 10

Média Ponderada = 71 / 10 = 7,1

A média anual de Gustavo, portanto, é 7,1.

Agora que você compreendeu a noção básica do cálculo da média ponderada, vamos aplica-la a uma situação do seu dia-a-dia e utilizando o Excel. Acompanhe!

Curso de Excel - Formatação Condicional

Demonstração do cálculo de média ponderada

Vamos supor que você deseja saber a média salarial mensal de todos da empresa, incluindo Presidente, Diretores e Gerentes. Para isso, suponha o seguinte quadro:

CargoQuantidade Salário
Presidente1 R$     15.000,00
Diretor3 R$       8.500,00
Gerente3 R$       6.000,00
Secretária3 R$       2.500,00
Estagiário6 R$       1.500,00

 

No Excel, temos:

Como Calcular Média Ponderada no Excel

Figura 1 – Quadro de colaboradores

A seguir, você aplicará a função SUMPRODUCT (ou SOMARPRODUTO), que retorna o valor da soma dos intervalos e a função SOMA, que retorna a soma dos valores selecionados. Sendo assim, temos:

Como Calcular Média Ponderada no Excel

Figura 2 – Cálculo da Média Ponderada

Curso de Excel - Formatação Condicional

=SOMARPRODUTO(C2:C6;B2:B6)/SOMA(B2:B6)

Como Calcular Média Ponderada no Excel

Desse modo, temos o valor R$4.687,50.

Com a função SOMARPRODUTO temos a soma dos salários multiplicados pelos pesos, ou seja, quantidade de colaboradores em cada função. Em seguida, com a função soma, temos apenas a soma da quantidade de colaboradores. E o símbolo de “/” significa que estamos dividindo ambos os valores, aplicando assim, a fórmula da média ponderada.

Como você pôde perceber, esse cálculo é muito útil quando queremos fazer um cálculo rápido sem muito esforço, especialmente para pesos diferentes. Portanto, lembre-se de sempre ficar atento aos pesos de cada medida e também de utilizar os parênteses corretamente no Excel para evitar erro do valor. Além disso, fique ligado em qual função deve ser utilizada primeiro! Fora isso, é só fazer o cálculo!

Este post foi útil para você? Está com dúvidas? Entre no Fórum de Excel da LUZ e envie a sua pergunta!

Planilhas Prontas em Excel

26 COMENTÁRIOS

  1. Olá Martins!

    Trabalho em uma instituição financeira de grande porte e lido com uma carteira de ativos muito grande.
    Também efetuo esse tipo de cálculo: temos diversos títulos com rentabilidades semelhantes e, no fim, precisamos saber qual foi a rentabilidade média dos títulos.

    Existem várias formas de se fazer esse cálculo, mas a mais utilizada é a fórmula de cotas. No caso da média ponderada, você já pode multiplicar o percentual do DI pela taxa para saber o valor e fazer a fórmula:

    ((Valor da aplicação 1 * taxa) + (Valor da aplicação 2 * taxa) + (Valor da plicação 3 * taxa)…)/(Valor da aplicação 1 + Valor da aplicação 2 + Valor da aplicação 3…)

    Ou seja, pelo que entendi, seu raciocínio está correto. No Excel, você irá utilizar a função SOMAPRODUTO e somar a coluna de valores aplicados, inserir um “;” e somar a coluna de taxas (já calculadas – por exemplo, se 99% do DI então 0,4906).

    Já no caso do sistema de cotas, leva um pouco mais de tempo para elaborar. Ele é recomendado nos casos em que há investimento e desinvestimento (ou aportes e resgates, se preferir).

    Multiplicar o va

  2. a fórmula é criada SOMANDO os valores da tabela auxiliar e dividindo pelo total de itens. Por exemplo, se você tem 1 item que custa 100, 2 que custam 200 e 3 que custam 300 cada, a média ponderada é a soma deles, (100 + 400 + 900) / 6

  3. queremos a taxa media ponderada.
    tenho 70 aplicações uma a 100% outra a 99% Do DI e assim por diante.
    foi apurado que o DI acumulado de Fev foi 0,4906.
    eu acho a taxa de cada aplicação 99%*0,4906 e depois multiplico pelo saldo liquido da aplicação; no final somo os resultados encontrados e divido pelo total liquido me dando uma taxa media que acredito seja a media ponderada para as taxas aplicadas. Isto que queria entender se é o correto de se fazer, ou seria de outra forma.

    grato

  4. Oi Martins, tudo vai depender de que indicadores seu gerente quer calcular. Vale a pena inicialmente entender quais informações vocês possuem e o que querem para depois usar o Excel para encontrar o melhor caminho

  5. tenho umas 50 aplicações financeiras na empresa
    cada uma aplicada a 1 percentual do CDi( de 99 a 102%)
    fiz uma planilha em que pego o valor liquido * percentual do CDi(aqui cdi do mes * o percentual que a aplicação foi efetuada e o valor total dividir pelo total liquido das aplicações , achando assim a media poderada(taxas) das aplicaçóes.
    O gerente da área não acha que este calculo esteja correto. Tem alguma sugestão?

  6. vc explica como é a media ponderada, mas nao explica como criar a formula

  7. Oi Murielli, sem ver como a sua tabela de dados está formatada fica um pouco mais difícil de dar uma resposta. Mas a média ponderada precisa ser ponderada por um peso (ou similar) no seu caso, você só tem uma variável do carbono. Se você tiver outra, é só fazer uma multiplicação dessa variável encontrada por profundidade que você chegará na média poderada

  8. Ola Rafael. Muito bom seu post, mas estou com uma duvida com meu conjunto de dados. Preciso fazer a média ponderada de Carbono em diferentes profundidades. Tenho os valores de Mg/ha porem não entendi como eu multiplicaria pelo peso, como na formula. Por exemplo a profundidade de 0-10cm tenho 3,98Mg/ha, em sequencia mais 5 profundidades com dados parecidos. Como vou gerar a media ponderada?

  9. Não sei se entendi bem, mas a principio imagino que fazer a média ponderada seja o melhor caminho mesmo, como você tem os valores das respostas na primeira linha, pode multiplicar a quantidade de respostas de cada valor (1,2,3,4,5) por esse valor e depois dividir o somatório total pela quantidade de respondentes para chegar nessa média

  10. Fala Rafael, beleza? Cara realizei uma pesquisa de satisfação em minha panificadora (utilizei o método SERVQUAL) onde 22 perguntas para Expectativa e 22 perguntas para Percepção no grau de 1 a 5, coloquei os dados no Excel por questões e quais foram as respostas, por exemplo: a 1ª questão teve 10 votos no grau 1, 4 votos no grau 2, 7 votos no grau 3 e 16 votos no grau 5. Como faço para calcular a media/variância com esses dados já tabulados. Estava vendo aqui de fazer por tabela dinâmica, mas teria que tabular (retrabalho) novamente, conforme cada respondente do questionário. Teria alguma solução?

    https://uploads.disquscdn.com/images/b31f00153a9e614cecf8effaee20f45ea4cc4a0513f73d55a5d547218521d6af.png

  11. Oi Charles, as formas que eu conheço são as que estão explicadas aqui no post. Uma um pouco mais longa, quando você pega o peso e multiplica pelo valor em questão. Esse procedimento precisa ser repetido para todos os valores e seus pesos respectivamente. Depois basta dividir pela soma dos pesos. Uma maneira mais simples é usar a função SOMARPRODUTO.

    Para criar um gráfico depois, basta usar a guia INSERIR e escolher o gráfico que quiser criar. Se você estiver com as células selecionadas, esse procedimento já vai gerar o gráfico com os dados em questão.

  12. Como vai, Rafael? São muito úteis os seus posts. Vez ou outra venho aqui para tirar dúvidas.
    Você poderia ensinar como calcular a média ponderada no excel dentro de uma tabela e depois transpor todos os dados desta tabela (incluindo o cálculo da média ponderada) para um gráfico?

  13. Desculpe, teria como me mostrar uma fórmula, não tô conseguindo. Na planilha Base de Dados, Separei os valores, ,na Coluna A, um em cada linha e na coluna C, da frente, coloquei os valores em reais referente a eles para multiplicar.
    Na planilha Mão de Obra tenho a B6 pra digitar numero de pontos e na C6 é o valor total. Eu me perdi nisso tudo, mas quero aprender kkk

  14. Oi Sérgio, de maneira geral faz sim, mas quase sempre fica mais fácil separar as coisas ao invés de fazer tudo junto.

    Por exemplo, sua coluna A (da planilha de base de dados) está mal formatada para buscas com a PROCV, o ideal seria ela ter apenas o número do limite inferior (0, 1501, 3001, etc).

    Depois disso, na planilha de mão de obra, você poderia utilizar um PROCV usando o argumento VERDADEIRO para buscar os valores nessa tabela. Depois é só fazer a multiplicação.

  15. Olá, tudo bem? Trabalho com Bordados Eletrônicos e tentei montar uma tabela para calcular a quantidade de pontos de bordados. Tenho 2 planilhas.. a 1ª se chama BASE DE DADOS e tem os numeros de pontos de bordados em 1 coluna, e, na 2ª coluna os valores respectivos aos intervalos de pontos de bordados. Por ex: A1= até 1500 pontos de bordados, e B1= 5,00 (este é valor unitário e fixo) os demais são multiplicados os pontos e valores…A2=1500 a 3000 pontos de bordados, e B2=0,0030…A3= de 3001 a 4500 pontos de bordados, e, C3=0,0028…
    Na segunda planilha se chama MÃO DE OBRA e nela tenho a célula pra digitar o numero de pontos de bordados (C6) e na (C7) tem que aparecer o valor final. Então assim, o excel tem localizar na 1ª planilha o valor que vou digitar em (C6), irá também multiplicar pelo valor respectivo aos pontos de bordados e me dará o valor total. O excel faz isso, de pesquisar, encontrar, multiplicar e retornar o resultado, tudo junto?

  16. De nada Pedro, ficamos felizes de ter ajudado. Vamos nos atentar para as próximas imagens

  17. Olá!
    Muito bom o conteúdo. Ajudou muito.

    Só uma dúvida, é possível acrescentar um “SE” nessa fórmula? Ou seja, realizar a média ponderada com critérios?

  18. olá!
    foi bem útil para mim. Mas sugiro que as imagens tenham melhor resolução, sejam “maiores”, pois quase precisei de lupa pra conseguir enxergá-las 😉
    isso facilitaria bastante 🙂

    de resto, muitíssimo obrigado!

    abs!

  19. Oi Diógenes, realmente ocorreu esse erro bobo. Felizmente isso não altera a lógica que ensinamos, só o resultado está errado mesmo. Já corrigi de toda forma. Grande abraços e obrigado pelo elogio e correção =]

  20. Parabéns excelente este site de vocês, me ajudou muito. Porém tem um uma multiplicação equivocada; no caso da nota por bimestre do aluno, o calculo de 6×4 que é igual a 24. Lá está 36 e por isso se obteve um resultado contraditório. Só estou repassando pois o site de vocês é muito legal, e, para que possam fazer a correção. Abraços!

Comments are closed.