Como calcular a média ponderada no Excel?

Suponha que tem uma lista de compras, tal como apresentado na captura de ecrã abaixo. Embora a função MÉDIA do Excel lhe dê o preço médio sem ter em conta o peso ou a quantidade, uma abordagem mais rigorosa nestes casos é calcular a média ponderada. Isto reflete melhor o custo real por unidade, atribuindo aos artigos com pesos ou frequências superiores uma influência maior no resultado final.

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

Selecione uma célula vazia, como F2, e introduza a seguinte fórmula:

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

e prima a tecla Enter para obter o resultado.

Nota: nesta fórmula, C2:C18 refere-se à coluna Peso e D2:D18 refere-se à coluna Preço. Ajuste estes intervalos conforme a disposição dos seus dados. A função SOMARPRODUTO multiplica cada peso pelo respetivo preço e soma os resultados, enquanto a função SOMA totaliza os pesos — obtendo assim a média ponderada correta. Certifique-se de que utiliza intervalos com o mesmo comprimento e de que não existem valores em falta nem células vazias nos seus dados, pois isso poderá originar erros de cálculo.

Se a média ponderada calculada apresentar demasiadas ou poucas casas decimais de acordo com as suas preferências, selecione a célula e clique no botão Aumentar Casas Decimais ou Diminuir Casas Decimais no separador Base para ajustar o número de casas decimais apresentadas conforme necessário.

Se encontrar um erro como #VALOR!, verifique se todas as células referenciadas contêm valores numéricos e se os intervalos são consistentes. Evite incluir linhas de cabeçalho no seu intervalo de cálculo para garantir resultados precisos. Ao trabalhar com conjuntos de dados maiores, opte por intervalos nomeados — uma solução que traz mais clareza e facilita a manutenção.

A fórmula anterior calcula o preço médio ponderado para todos os artigos. Na prática, porém, poderá pretender calcular a média ponderada apenas para categorias específicas — por exemplo, determinar o preço médio ponderado exclusivamente para Maçãs. Nestes casos, pode aprimorar a fórmula incluindo uma condição com base nos seus critérios.

Para tal, selecione uma célula vazia, como F8, e introduza a seguinte fórmula:

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

Em seguida, prima a tecla Enter para calcular a média ponderada que corresponde aos seus critérios específicos. Esta fórmula multiplica cada par peso-preço apenas se o artigo corresponder à condição («Maçã», neste caso), soma esses produtos e divide o resultado pela soma dos pesos desse artigo.

Nota: aqui, B2:B18 é a coluna Fruta, C2:C18 é o Peso e D2:D18 é o Preço. Substitua “Maçã” por outro artigo, conforme necessário. Este método funciona perfeitamente para filtrar com base numa única condição; caso precise de aplicar múltiplos critérios (por exemplo, tipo de fruta e fornecedor), poderá ter de recorrer a uma coluna auxiliar ou a uma fórmula mais avançada.

Após aplicar a fórmula, poderá querer ajustar as casas decimais para maior clareza. Selecione a célula com o resultado e utilize os botões Aumentar Casas Decimais ou Diminuir Casas Decimais no separador Base para alterar o número de casas decimais apresentadas.

Se a fórmula devolver um resultado inesperado, confirme que os critérios têm correspondências no seu intervalo-alvo e verifique se há células vazias ou entradas de texto nas colunas que deveriam conter valores numéricos.

Em determinadas situações, poderá precisar frequentemente de calcular médias ponderadas em intervalos com tamanhos variáveis, que contenham valores em falta ou que exijam filtragem flexível — como a aplicação simultânea de múltiplos critérios. Em vez de atualizar manualmente fórmulas ou intervalos, automatizar esse cálculo com uma macro VBA poupa tempo e reduz significativamente o risco de erros, sendo especialmente vantajoso ao trabalhar com conjuntos de dados extensos ou sujeitos a atualizações regulares.

Eis como criar e utilizar uma macro VBA para médias ponderadas:

1. Clique em Programador > Visual Basic(ou prima)Alt + F11) para abrir a janela do editor Microsoft Visual Basic para Aplicações. Em seguida, clique em Inserir > Módulo e cole o seguinte código na nova janela do 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. Prima F5(ou clique no) botão Executar) para executar.
Ser-lhe-á pedido que selecione os intervalos passo a passo (intervalo de critérios — que pode ignorar se não for necessário, intervalo de pesos e intervalo de valores). Pode ainda introduzir critérios específicos para filtrar o seu cálculo ou deixá-los em branco para incluir todos os dados. A macro suporta intervalos dinâmicos, tornando-a ideal se a sua tabela crescer ou mudar regularmente.

Por fim, receberá uma caixa de mensagem com o resultado da média ponderada.

Dicas:

• Esta abordagem automatiza a análise repetitiva de médias ponderadas e pode ainda ser expandida para incluir opções adicionais de filtragem ou saída.
• Certifique-se de que os intervalos selecionados têm o mesmo comprimento e de que os tipos de dados são consistentes.
• Inclua um tratamento básico de erros, como ilustrado (por exemplo, para situações em que não sejam encontrados pesos válidos ou a soma dos pesos seja zero).
• Se pretender aplicar apenas às linhas filtradas ou visíveis, pode aprimorar ainda mais o código com uma enumeração especial de células.

Se encontrar problemas relacionados com permissões ou segurança de macros, certifique-se de que as macros estão ativadas nas definições do Excel antes de executar o código.

Média de intervalo com arredondamento no Excel

Taxa média de variação no Excel

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

Média móvel/rolante no Excel

Total acumulado/média móvel no Excel