Como classificar valores por grupo no Excel?
Trabalhar com dados agrupados no Excel exige frequentemente comparar valores dentro de cada grupo — como classificar vendas por região, pontuações de testes por turma ou montantes de transações por categoria. Embora o Excel ofereça ferramentas robustas para ordenação de dados, a classificação dentro de grupos (também conhecida como «classificação por grupo» ou «classificação condicional») requer uma abordagem específica. Esta técnica revela-se especialmente útil quando precisa avaliar desempenhos ou identificar os registos mais e menos relevantes em diferentes categorias, sem misturar resultados entre grupos. Os métodos seguintes apresentam soluções práticas para classificar valores por grupo, facilitando uma interpretação clara e uma análise rigorosa dos seus dados nas tarefas do dia a dia.
Classificar valores por grupo
Código VBA – Utilize uma macro para automatizar a classificação de valores dentro de cada grupo
Classificar valores por grupo
Em situações em que precisa classificar valores dentro de grupos distintos — como pontuar alunos por turma ou listar vendas por região —, o Excel não oferece uma funcionalidade direta de «classificação por grupo». Contudo, com uma fórmula bem elaborada, é possível obter essa classificação de forma eficiente e sem qualquer manipulação adicional dos dados.
Para tal, pode utilizar uma fórmula matricial que combine testes lógicos com funções de agregação, permitindo comparar cada valor exclusivamente dentro do seu grupo designado e gerando assim a classificação necessária para cada ponto de dados.
Siga estes passos:
- Prepare os seus dados organizados em colunas, como Grupo (A2:A11) e Valor (B2:B11).
- Selecione uma célula vazia junto aos seus dados — normalmente na primeira linha ao lado dos seus valores, por exemplo, a célula C2.
- Introduza a seguinte fórmula:
=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1Esta fórmula funciona contando quantos valores do mesmo grupo são inferiores ao Valor Atual. Eis o que cada parâmetro significa:
- ($A$2:$A$11=A2)
→ Verifica se cada célula no intervalo A2:A11 é igual ao valor em A2.
→ Devolve um conjunto de valores VERDADEIRO/FALSO (ou 1/0), indicando se cada linha pertence ao mesmo grupo que A2. - (B2<$B$2:$B$11)
→ Isto verifica quantos valores em B2:B11 são superiores a B2.
→ Devolve VERDADEIRO (1) se B2 for inferior a um determinado valor e FALSO (0) caso contrário. - * (Multiplicação)
→ Combina as duas condições: - Correspondência do grupo (A2)
Valor em B2 é inferior aos restantes
→ Assim, apenas as linhas que pertencem ao mesmo grupo e cujo valor seja inferior serão contabilizadas. - SOMAPRODUTO(...)
→ Soma o número de linhas que satisfazem ambas as condições. - +1
→ As classificações começam em 1 (em vez de 0), por isso adicionamos 1 à contagem de valores inferiores.
Assim que introduzir a fórmula em C2, arraste a alça de autorreenchimento para baixo para aplicar automaticamente essa fórmula a todas as linhas relevantes do seu conjunto de dados. A fórmula ajustar-se-á dinamicamente ao grupo e ao valor de cada linha, devolvendo a respetiva classificação dentro desse grupo.
Dicas e precauções:
- Se o seu intervalo for extenso, não se esqueça de atualizar as referências das células em conformidade.
- Para obter classificações em ordem decrescente (por exemplo, em que o valor mais alto corresponde à posição 1), altere a comparação na fórmula de
B2<$B$2:$B$11paraB2>$B$2:$B$11. - Para lidar com valores duplicados, esta fórmula atribui a mesma classificação a valores idênticos dentro do mesmo grupo. Caso necessite de classificações sequenciais únicas, considere utilizar colunas auxiliares adicionais.
Este método baseado em fórmulas é flexível e adapta-se facilmente à maioria das estruturas de tabelas agrupadas no Excel. Contudo, em conjuntos de dados muito grandes, o desempenho do cálculo pode diminuir devido à dependência da lógica matricial.
Código VBA – Utilize uma macro para automatizar a classificação de valores dentro de cada grupo
Para utilizadores que pretendam automatizar o processo de classificação ou trabalhar com conjuntos de dados maiores de forma mais eficiente, criar uma macro VBA pode ser uma solução extremamente valiosa. As macros automatizam tarefas repetitivas, oferecem um elevado grau de personalização e processam dados com rapidez superior à das fórmulas complexas — ideal para cenários como a geração programada de relatórios, operações recorrentes de ordenação ou sempre que desejar manter a sua folha de cálculo livre de fórmulas adicionais.
Antes de prosseguir, certifique-se de guardar o seu trabalho e de ativar as macros nas definições do Excel. Eis como pode criar e executar esta solução:
- Prima Alt + F11 para abrir o Editor do VBA. Na janela do Microsoft Visual Basic for Applications que surge, clique em Inserir > Módulo e, de seguida, cole o seguinte código no módulo aberto:
Sub RankValuesByGroup()
Dim DataRange As Range
Dim GroupRng As Range
Dim ValueRng As Range
Dim OutCol As Range
Dim dictGroups As Object
Dim arrValues, arrRanks
Dim i As Long, j As Long
Dim GroupKey As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data table range (including group and value columns)", xTitleId, Selection.Address, Type:=8)
If DataRange Is Nothing Then Exit Sub
Set GroupRng = Application.InputBox("Select the group column within your range", xTitleId, DataRange.Columns(1).Address, Type:=8)
Set ValueRng = Application.InputBox("Select the value column to rank within your range", xTitleId, DataRange.Columns(2).Address, Type:=8)
Set OutCol = DataRange.Offset(0, DataRange.Columns.Count).Resize(DataRange.Rows.Count, 1)
OutCol.Cells(1).Value = "RankByGroup"
Set dictGroups = CreateObject("Scripting.Dictionary")
arrValues = ValueRng.Value
arrRanks = ValueRng.Value
' Build group dictionaries for ranking
For i = 2 To UBound(arrValues, 1)
GroupKey = GroupRng.Cells(i, 1).Value
If Not dictGroups.Exists(GroupKey) Then
dictGroups.Add GroupKey, CreateObject("System.Collections.ArrayList")
End If
dictGroups(GroupKey).Add arrValues(i, 1)
Next i
' Rank within each group
For i = 2 To UBound(arrValues, 1)
GroupKey = GroupRng.Cells(i, 1).Value
Dim countLower As Long
countLower = 0
For j = 0 To dictGroups(GroupKey).Count - 1
If dictGroups(GroupKey)(j) < arrValues(i, 1) Then
countLower = countLower + 1
End If
Next j
arrRanks(i, 1) = countLower + 1
Next i
' Output results
For i = 2 To UBound(arrRanks, 1)
OutCol.Cells(i, 1).Value = arrRanks(i, 1)
Next i
MsgBox "Ranking by group completed.", vbInformation, xTitleId
End Sub- Clique em Executar. Uma caixa de diálogo irá pedir-lhe para selecionar o seu intervalo de dados completo, a coluna de grupo e a coluna de valor. A macro irá então criar uma nova coluna com as classificações de cada valor dentro do respetivo grupo.
Notas e resolução de problemas:
- Certifique-se de que as colunas selecionadas correspondem aos seus dados: as colunas de grupo e valor devem estar corretamente alinhadas.
- Se incluir o cabeçalho dos dados, ajuste o índice inicial do ciclo no código para obter a classificação correta, de acordo com a estrutura dos seus dados.
- Para classificar em ordem decrescente, modifique a comparação
If dictGroups(GroupKey)(j) < arrValues(i,1)em conformidade. - Se encontrar avisos de permissão ou segurança relacionados com macros, verifique as definições de Segurança de Macros do Excel em Ficheiro > Opções > Centro de Confiança.
Este método VBA oferece flexibilidade e desempenho robusto para aplicações mais avançadas ou de grande escala, especialmente quando integrado em fluxos de trabalho automatizados de relatórios.
Desbloqueie a Magia do Excel com KUTOOLS AI
- Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
- Programação VBA: Escreva e implemente código VBA com facilidade.
- Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
- Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
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