Como calcular a média de uma coluna com base em critérios definidos noutra coluna no Excel?
Em muitos cenários práticos no Excel, é frequentemente necessário calcular a média dos valores numa coluna, agrupados ou filtrados com base em entradas correspondentes noutra coluna. Por exemplo, poderá querer determinar as vendas médias por vendedor ou por região, como ilustrado na imagem seguinte. Este tipo de cálculo é comum em relatórios resumo, análises de desempenho e revisões de dados. Neste artigo, apresentamos vários métodos eficazes para obter este resultado, garantindo que pode escolher a abordagem mais adequada às suas necessidades e ao seu nível de competência.
Calcular a média numa coluna com base no mesmo valor noutra coluna com fórmulas
Calcular a média numa coluna com base no mesmo valor noutra coluna com Kutools para Excel
Calcular a média por grupo utilizando Tabela Dinâmica
Automatizar o cálculo da média agrupada com macro VBA
Calcular a média numa coluna com base no mesmo valor noutra coluna com fórmulas
Um dos métodos mais diretos para calcular a média de um grupo com base noutra coluna no Excel é utilizar fórmulas condicionais, como MÉDIA.SE ou MÉDIA.SE.S. Esta abordagem revela-se especialmente útil quando precisa de obter resultados específicos de acordo com determinados critérios — por exemplo, calcular as vendas médias de uma cidade ou vendedor específico.
1. Selecione uma célula vazia onde pretende apresentar o resultado, introduza a seguinte fórmula e prima Enter:
=AVERAGEIF(B2:B13,E2,C2:C13)
Explicação dos parâmetros: Na fórmula acima, B2:B13 é o intervalo que contém os critérios a verificar (por exemplo, a cidade ou o vendedor), E2 é o valor específico com o qual pretende comparar (como «Owenton») e C2:C13 é o intervalo que contém os valores numéricos cuja média pretende calcular.
Após premir Enter, obterá imediatamente a média do grupo especificado em E2 (por exemplo, as vendas médias de «Owenton»).
Se precisar de calcular a média para cada valor único na coluna de critérios, basta ajustar o valor na célula de critérios (E2) em conformidade ou copiar a fórmula para baixo, caso tenha uma lista de entradas únicas.
Dica prática: Em conjuntos de dados maiores ou com muitos grupos únicos, combine esta fórmula com uma lista de valores únicos (utilizando ferramentas como «Remover duplicatas» ou a função ÚNICO do Excel no Office 365 e Excel 2021) para acelerar o cálculo de todas as médias de grupo de uma só vez. Verifique cuidadosamente se os intervalos na sua fórmula abrangem todos os dados pretendidos e permanecem alinhados ao copiar a fórmula.
Erros comuns e resolução de problemas:
- Se obtiver um erro #DIV/0!, verifique se o valor dos critérios aparece realmente no intervalo selecionado.
- Certifique-se de que o seu intervalo numérico inclui apenas números válidos — células com texto ou vazias podem comprometer o cálculo.
Calcular a média numa coluna com base no mesmo valor noutra coluna com Kutools para Excel
Se pretender calcular automaticamente a média de todos os valores únicos numa coluna — sem ter de inserir repetidamente fórmulas nem aplicar filtros manualmente — o Kutools para Excel oferece uma solução simplificada e eficiente. Esta funcionalidade revela-se especialmente vantajosa ao trabalhar com listas extensas ou conjuntos de dados complexos.
1. Selecione todo o intervalo de dados que inclui tanto a coluna de grupo como a coluna numérica cuja média pretende calcular. Em seguida, aceda a Kutools > Mesclar e Dividir > Mesclar Linhas Avançado.
2. Na caixa de diálogo Combinar Linhas Com Base na Coluna, proceda da seguinte forma:
- Selecione a coluna pela qual pretende agrupar (por exemplo, Cidade ou Vendedor) e clique no botão Chave Primária para defini-la como campo de agrupamento.
- Selecione a coluna numérica para a qual deseja calcular a média e, em seguida, clique em Calcular > Média.
Dica: Para quaisquer outras colunas (como datas), pode especificar como combinar os respetivos valores (por exemplo, juntá-los com uma vírgula). - Clique em OK para processar a operação.
O Kutools agrupará instantaneamente os dados com base na chave selecionada e apresentará a média de cada grupo na coluna numérica.
O processamento em lote do Kutools é ideal para utilizadores que analisam regularmente estatísticas agrupadas, como relatórios mensais, resumos departamentais ou outros cálculos envolvendo múltiplos grupos. Como complemento, o Kutools preserva a estrutura original dos seus dados, e a sua função de pré-visualização permite rever os agrupamentos antes de aplicar quaisquer alterações.
Notas e dicas:
- Certifique-se de que não há linhas vazias no intervalo selecionado antes de executar a ferramenta.
- Se precisar das médias agrupadas noutro local, utilize Copiar e Colar para transferir os resultados após o cálculo.
- Em conjuntos de dados muito grandes, certifique-se de que as colunas de agrupamento e numéricas estão corretamente atribuídas para evitar confusões.
Kutools para Excel– Potencie o Excel com mais de 300 ferramentas essenciais, tornando o seu trabalho mais rápido e fácil, e aproveite as funcionalidades de IA para um processamento de dados mais inteligente e uma maior produtividade.Obtenha Já
Calcular a média por grupo utilizando Tabela Dinâmica
As Tabelas Dinâmicas oferecem uma forma poderosa e integrada de resumir, agrupar e analisar dados — incluindo o cálculo de médias por grupo — sem necessidade de fórmulas nem de extras de terceiros. Esta técnica é ideal quando pretende uma vista interativa das médias e totais por diferentes categorias, sendo adequada tanto para conjuntos de dados pequenos como para conjuntos muito grandes.
Como configurar uma Tabela Dinâmica para calcular médias por grupo:
- Selecione qualquer célula do seu conjunto de dados e, em seguida, aceda a Inserir > Tabela Dinâmica. Na caixa de diálogo, escolha onde pretende que a Tabela Dinâmica apareça (numa nova folha ou numa folha existente) e clique em OK.
- No painel Campos da Tabela Dinâmica, arraste a coluna pela qual pretende agrupar para a área Linhas (por exemplo, «Cidade» ou «Vendedor»).
- Arraste a coluna numérica cuja média pretende calcular (por exemplo, «Vendas») para a área Valores. Por predefinição, o Excel poderá calcular a soma; para alterar isto, clique no campo de valor, selecione Definições de Campo de Valor e escolha Média.
A sua Tabela Dinâmica apresentará imediatamente o valor médio de cada grupo. Pode filtrar, ordenar e formatar o relatório rapidamente, conforme necessário. Este método é intuitivo e elimina o risco de erros em fórmulas.
Vantagens: Interativo, lida elegantemente com grandes volumes de dados e consegue resumir várias estatísticas em simultâneo.
Desvantagens: O resultado é apresentado num formato de Tabela Dinâmica, e não numa lista simples; além disso, requer atualização ocasional sempre que os Dados de Origem forem alterados.
Dica: Ao clicar duas vezes em qualquer célula de resumo na Tabela Dinâmica, abre-se uma nova folha com os dados subjacentes desse grupo, facilitando a auditoria detalhada ou a resolução de discrepâncias.
Problemas comuns:
- Se não conseguir obter a média, confirme se o campo de valor está definido como «Média» nas Definições do Campo de Valor.
- Verifique os seus Dados de Origem em busca de linhas em branco ou colunas adicionais que possam comprometer a disposição da Tabela Dinâmica.
Automatizar o cálculo da média agrupada com macro VBA
Para utilizadores que frequentemente precisam de calcular médias para múltiplos grupos ou que pretendem gerar automaticamente um resumo, criar uma macro VBA pode poupar um esforço manual considerável. O VBA revela-se especialmente útil quando a estrutura dos seus dados se mantém consistente ou quando pretende produzir relatórios resumo repetidos com apenas um clique.
Antes de começar, certifique-se de guardar a sua pasta de trabalho e de ativar as macros. Eis como dar o primeiro passo:
1. Clique em Ferramentas de Programador > Visual Basic para abrir o editor do VBA. No editor, clique em Inserir > Módulo para criar um novo módulo de código. Cole o código abaixo no módulo:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Após inserir o código, feche o editor do VBA. Regresse ao Excel, prima Alt+F8, selecione GroupAverageSummary na lista e clique em Executar. A macro irá pedir-lhe para selecionar a coluna do seu grupo (critério) e a coluna dos valores (numéricos). Assim que fizer as suas seleções, será gerada automaticamente uma nova folha de cálculo com o nome «Group Average Summary», apresentando cada grupo único e os respetivos valores médios.
Notas sobre parâmetros e operação:
- Certifique-se de que as suas colunas de grupo e de valor têm o mesmo comprimento e contêm dados válidos — evite seleções parciais.
- Esta macro pode ser adaptada para agrupamentos mais avançados ou estatísticas resumo adicionais, conforme as suas necessidades.
- Se a sua folha já incluir um resumo «Média por Grupo» com o nome «Nome da Planilha», a macro criará uma nova folha de cálculo com o nome predefinido «Nome da Planilha».
Resolução de problemas:
- Se encontrar uma mensagem como «subscrito fora do intervalo» ou semelhante, verifique se os seus intervalos selecionados estão corretamente alinhados e na mesma folha de cálculo.
- Para obter os melhores resultados, certifique-se de que a coluna de valores contenha apenas dados numéricos — células com texto ou em branco dentro do intervalo serão ignoradas pela macro.
Utilizar esta macro é ideal para processamento em lote, relatórios automatizados ou situações em que necessita frequentemente de resumir conjuntos de dados novos ou atualizados.
Demonstração: Calcule a média numa coluna com base num valor idêntico noutra coluna com Kutools para Excel
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos