Como calcular a média de várias correspondências do vlookup no Excel?

Quando você precisa encontrar e calcular a média de vários valores associados ao mesmo item de pesquisa, usar uma fórmula direta é uma das maneiras mais rápidas e flexíveis. A função MÉDIA.SE ou uma fórmula de matriz lida com isso facilmente sem criar colunas extras.

Insira a seguinte fórmula em uma célula em branco (por exemplo, F2):

=AVERAGEIF(A1:A24,E2,C1:C24)

Pressione a tecla Enter após digitar a fórmula. Isso imediatamente lhe dará a média de todos os valores na coluna C onde o valor correspondente na coluna A corresponde ao seu valor de pesquisa localizado na célula E2. Veja a ilustração abaixo:

Explicação dos parâmetros e dicas:

• A1:A24: O intervalo contendo seus valores de pesquisa.
• E2: O valor específico que você deseja procurar.
• C1:C24: O intervalo do qual você deseja calcular a média dos valores correspondentes.

Abordagem alternativa (para usuários confortáveis com fórmulas de matriz):

Insira a seguinte fórmula em uma célula em branco e use Ctrl + Shift + Enter para confirmar:

=AVERAGE(IF(A1:A24=E2,C1:C24))

As fórmulas de matriz processam cada comparação individualmente, o que é útil em versões do Excel que não suportam matrizes dinâmicas. Certifique-se cuidadosamente de que os intervalos tenham exatamente o mesmo tamanho para evitar erros.

Cenários práticos e observações:
- Ideal para conjuntos de dados que não estão filtrados e têm necessidades de pesquisa simples.
- Se qualquer intervalo incluir células vazias, elas serão ignoradas no cálculo da média.
- Em tabelas dinâmicas ou ao adicionar dados, considere usar referências de tabela para fórmulas mais robustas.
- Cuidado com combinações acidentais de intervalos de células, que são uma fonte comum de médias incorretas ou erros.

Se você frequentemente precisa resumir e agregar dados com base em duplicatas, o Kutools para Excel oferece uma solução prática por meio de sua utilidade Mesclar Linhas Avançado. Essa ferramenta pode combinar ou calcular rapidamente valores como média, soma ou contagem para registros correspondentes em uma etapa, tornando-a altamente adequada para grandes conjuntos de dados ou relatórios regulares.

1. Destaque o intervalo de sua tabela de dados, incluindo tanto a coluna de pesquisa quanto os valores para calcular a média. Depois vá para Kutools > Texto > Mesclar Linhas Avançado. Veja a captura de tela:

2. Na caixa de diálogo que aparece:

• Selecione a coluna com seus valores de pesquisa e clique em Chave Primária.
• Escolha a coluna com seus valores-alvo, depois clique em Calcular > Média.
• Defina regras de combinação ou cálculo para outras colunas conforme necessário — como combinar texto com vírgulas ou aplicar soma, máximo ou mínimo.

3. Clique em Ok para aplicar as configurações.

As linhas com valores de pesquisa duplicados agora estão mescladas, e os valores na coluna designada são automaticamente calculados como média para cada valor de pesquisa único. Isso é particularmente útil para preparar relatórios resumidos ou condensar dados.

Dica prática: Usar Mesclar Linhas Avançado minimiza cálculos manuais e a possibilidade de erro. A ferramenta é melhor para usuários que processam regularmente dados com valores de pesquisa recorrentes e querem resumos acionáveis rapidamente. Sempre verifique se as colunas corretas estão atribuídas antes de combinar, especialmente se a estrutura de dados mudar.

Demonstração: calcular a média de várias correspondências do vlookup com o Kutools para Excel

As Tabelas Dinâmicas oferecem uma abordagem dinâmica e visual para resumir e analisar dados. Usando uma Tabela Dinâmica, você pode agrupar automaticamente entradas por seus valores de pesquisa e exibir a média de uma coluna-alvo para cada grupo, fornecendo um resumo interativo que se atualiza conforme seus dados mudam.

Cenários mais eficazes: Essa abordagem é bem adequada quando você precisa de um resumo geral para todos os valores de pesquisa de uma só vez, em vez de focar em um único valor de pesquisa. As Tabelas Dinâmicas também são excelentes para exploração rápida de dados, geração de relatórios e quando você deseja apresentar seus resultados em um formato ordenável e expansível.

Instruções:

• Selecione todo o seu conjunto de dados, incluindo cabeçalhos.
• Vá para Inserir > Tabela Dinâmica > A partir de Tabela ou Intervalo. Escolha colocar a Tabela Dinâmica em uma nova planilha ou em uma existente, conforme necessário.
• No painel Campos da Tabela Dinâmica, arraste a coluna contendo seus valores de pesquisa para a área Linhas.
• Arraste a coluna que você deseja calcular a média para a área Valores. Clique no campo de valor, selecione Configurações do Campo de Valor e defina o tipo de cálculo como Média.

Isso resulta em uma tabela resumida listando cada valor de pesquisa único com sua média calculada para os dados associados. Você pode alterar facilmente o agrupamento, filtrar ou detalhar conforme necessário.

Prós: Não são necessárias fórmulas, suporte para atualizações dinâmicas, adequado para relatórios e exploração de dados.

Contras: Passos extras necessários para atualizar após mudanças nos dados, menos adequado para extrair um único valor diretamente em outras fórmulas, e a configuração inicial requer familiaridade básica com Tabelas Dinâmicas.

Dicas de solução de problemas: Se os valores aparecerem como contagens ou somas em vez de médias, verifique a configuração de cálculo do campo. Para melhores resultados, certifique-se de que as colunas tenham cabeçalhos apropriados e clarifique quaisquer nomes de colunas duplicados antes de criar a Tabela Dinâmica.

Para usuários avançados e aqueles que gerenciam dados que se atualizam regularmente, usar uma macro VBA permite automatizar o processo de cálculo da média em todas as entradas que correspondem a um valor de pesquisa. Este método percorre seus dados para encontrar cada correspondência e calcular a média, sendo adequado para grandes conjuntos de dados ou quando você precisa de um fluxo de trabalho repetível.

Cenários aplicáveis e observações: O VBA é ideal quando você frequentemente precisa realizar o cálculo da média, deseja automatizar relatórios ou requer uma abordagem flexível que pode ser adaptada a layouts de dados incomuns. As macros VBA funcionam melhor quando você está confortável habilitando macros em sua pasta de trabalho e requer saídas personalizadas.

1. Vá para a guia Desenvolvedor, escolha Visual Basic ou pressione Alt + F11 para abrir o editor VBA, depois clique em Inserir > Módulo. Copie e cole o código abaixo no novo módulo:

Sub AverageVlookupMatches()
    Dim lookupCol As Range
    Dim avgCol As Range
    Dim lookupValue As Variant
    Dim total As Double
    Dim count As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set lookupCol = Application.InputBox("Select the lookup column", xTitleId, Selection.Address, Type:=8)
    Set avgCol = Application.InputBox("Select the column to average", xTitleId, , Type:=8)
    lookupValue = Application.InputBox("Enter lookup value", xTitleId, , Type:=2)
    
    Application.ScreenUpdating = False
    total = 0
    count = 0
    
    For i = 1 To lookupCol.Rows.Count
        If lookupCol.Cells(i, 1).Value = lookupValue Then
            If IsNumeric(avgCol.Cells(i, 1).Value) Then
                total = total + avgCol.Cells(i, 1).Value
                count = count + 1
            End If
        End If
    Next i
    
    If count > 0 Then
        MsgBox "Average of all matches: " & total / count, vbInformation, "Result"
    Else
        MsgBox "No matches found.", vbExclamation, "Result"
    End If
    
    Application.ScreenUpdating = True
End Sub

2. Após colar o código, feche o editor VBA. Para executar a macro, retorne ao Excel, pressione a tecla F5 ou clique em Executar. Quando solicitado, selecione a coluna de pesquisa, a coluna de valor a ser calculada e insira o valor de pesquisa. A macro exibirá a média calculada em uma caixa de mensagem.

Dicas práticas e precauções: Certifique-se de que suas colunas de pesquisa e valor tenham o mesmo número de linhas e que não haja linhas em branco nas áreas selecionadas. As entradas com valores não numéricos na coluna-alvo serão ignoradas. Para a melhor automação, ajuste intervalos nomeados ou a lógica da macro conforme necessário para o layout da sua planilha.

Solução de problemas: Se você encontrar "Nenhuma correspondência encontrada", verifique se há espaços iniciais/finais ou inconsistências de tipo de dados na sua coluna de pesquisa. Certifique-se de que as macros estão habilitadas para execução.

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

Calcular média móvel/rolante no Excel

Média por dia/mês/trimestre/hora com tabela dinâmica no Excel