Como calcular a média ponderada no Excel?

Suponha que você tenha uma lista de compras conforme mostrado na captura de tela abaixo. Embora a função MÉDIA do Excel forneça a média de preço sem levar em conta o peso ou quantidade, uma abordagem mais precisa nesses casos é calcular a média ponderada. Isso reflete melhor o custo real por unidade, dando maior influência aos itens com pesos ou frequências maiores no resultado final.

Para calcular o preço médio ponderado, use uma combinação das funções SOMARPRODUTO e SOMA da seguinte forma:

Selecione uma célula em branco, como F2, insira a seguinte fórmula:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

e pressione a tecla Enter para obter o resultado.

Observação: Nesta fórmula, C2:C18 refere-se à coluna Peso, e D2:D18 refere-se à coluna Preço. Ajuste esses intervalos conforme necessário para seu próprio layout de dados. A função SOMARPRODUTO multiplica cada peso pelo preço correspondente e soma os resultados, enquanto SOMA totaliza os pesos — produzindo a média ponderada correta. Certifique-se de usar intervalos de comprimento igual e garantir que não haja células desencontradas ou vazias em seus dados, pois isso poderia levar a erros de cálculo.

Se a média ponderada calculada exibir muitas ou poucas casas decimais para sua preferência, selecione a célula, clique no botão Aumentar Casas Decimais botão ou Diminuir Casas Decimais botão na aba Início para ajustar as casas decimais exibidas conforme necessário.

Se você encontrar um erro como #VALOR!, verifique novamente se cada célula referenciada contém um valor numérico e se os intervalos são consistentes. Além disso, evite incluir qualquer linha de cabeçalho no intervalo de cálculo para garantir resultados precisos. Ao trabalhar com conjuntos de dados maiores, considere usar intervalos nomeados para maior clareza e facilidade de manutenção.

A fórmula anterior calcula o preço médio ponderado para todos os itens. Na análise prática, você pode querer a média ponderada para categorias específicas, como encontrar o preço médio ponderado apenas para Maçãs. Nesses casos, você pode aprimorar a fórmula para incluir uma condição com base nos seus critérios.

Para fazer isso, selecione uma célula em branco, como F8, e insira a seguinte fórmula:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Em seguida, pressione a tecla Enter para calcular a média ponderada que atende seus critérios específicos. Essa fórmula multiplica cada par de peso e preço somente se o item corresponder à condição (“Maçã” neste caso), soma-os e divide pela soma dos pesos apenas para esse item.

Observação: Aqui, B2:B18 é a coluna Fruta, C2:C18 é o Peso, e D2:D18 é o Preço. Substitua “Maçã” por outro item conforme necessário. Esse método funciona bem para filtragem por uma condição; se você precisar filtrar por múltiplos critérios (por exemplo, tipo de fruta e fornecedor), uma coluna auxiliar ou fórmula mais avançada pode ser necessária.

Depois de aplicar a fórmula, você pode querer ajustar as casas decimais para maior clareza. Selecione a célula de resultado e use os botões Aumentar Casas Decimais ou Diminuir Casas Decimais na aba Início para alterar as casas decimais exibidas.

Caso a fórmula retorne um resultado inesperado, confirme que os critérios têm correspondências dentro do intervalo alvo e observe se há células em branco ou entradas de texto em colunas destinadas a serem numéricas.

Em algumas situações, você pode frequentemente precisar calcular médias ponderadas em intervalos que mudam de tamanho, contêm valores ausentes ou exigem filtragem flexível, como aplicar vários critérios de uma só vez. Em vez de atualizar manualmente fórmulas ou intervalos, automatizar o cálculo com uma macro VBA pode economizar tempo e reduzir as chances de erros — especialmente útil ao lidar com grandes conjuntos de dados ou dados atualizados regularmente.

Aqui está como criar e usar uma macro VBA para médias ponderadas:

1. Clique em Desenvolvedor > Visual Basic (ou pressione Alt + F11) para abrir a janela do editor do Microsoft Visual Basic for Applications. Em seguida, clique em Inserir > Módulo, depois cole o código abaixo na nova janela de módulo:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. Pressione F5 (ou clique no botão Executar) para executar.
Você será solicitado a selecionar os intervalos passo a passo (intervalo de critérios — isso pode ser ignorado se não necessário, intervalo de pesos e intervalo de valores). Você também pode inserir critérios específicos para filtrar seu cálculo ou deixá-lo em branco para considerar todos os dados. A macro suporta intervalos de dados dinâmicos, tornando-a prática se sua tabela crescer ou mudar regularmente.

Finalmente, você receberá uma caixa de mensagem listando o resultado da média ponderada.

Dicas:

• Essa abordagem automatiza análises repetitivas de média ponderada e pode ser expandida para lidar com filtros ou opções de saída adicionais.
• Certifique-se de que os intervalos selecionados têm o mesmo comprimento e que os tipos de dados são consistentes.
• Inclua tratamento básico de erros conforme mostrado (por exemplo, em casos onde nenhum peso válido é encontrado ou a soma dos pesos é zero).
• Se você quiser aplicar apenas às linhas visíveis/filtradas, poderá aprimorar ainda mais o código com enumeração de células especiais.

Se você encontrar problemas de permissão ou segurança de macros, certifique-se de que as macros estão habilitadas nas configurações do Excel antes de executar o código.

Intervalo médio com arredondamento no Excel

Taxa média de mudança no Excel

Calcular taxa de crescimento média/composta anual no Excel

Média móvel/rolante no Excel

Total acumulado /média no Excel