Como classificar valores por grupo no Excel?

Trabalhar com dados agrupados no Excel geralmente requer comparar valores dentro de cada grupo, como classificar números de vendas por região, notas de testes por turma ou valores de transações por categoria. Embora o Excel ofereça ferramentas robustas para classificar dados, a classificação dentro de grupos (também conhecida como "classificação por grupo" ou "classificação condicional") exige uma abordagem específica. Isso é especialmente útil quando você precisa avaliar o desempenho ou identificar os melhores e piores registros entre diferentes categorias sem misturar resultados entre os grupos. Os métodos a seguir exploram soluções práticas para classificar valores por grupo, facilitando a interpretação e análise precisa dos seus dados em tarefas diárias.

Classificar valores por grupo
Código VBA - Use uma macro para automatizar a classificação de valores dentro de cada grupo

Classificar valores por grupo

Para situações em que você precisa classificar valores dentro de grupos distintos, como pontuar alunos por turma ou listar vendas por diferentes regiões, o Excel não possui um recurso direto de "classificação por grupo". No entanto, uma fórmula bem elaborada pode alcançar a classificação por grupo de forma eficiente e sem manipulação extra de dados.

Para fazer isso, você pode usar uma fórmula de matriz que combina testes lógicos com funções de agregação. Essa abordagem permite que você compare cada valor apenas dentro de seu grupo designado, produzindo a classificação necessária para cada ponto de dado.

Siga estes passos:

• Prepare seus dados agrupados em colunas, como Grupo (A2:A11) e Valor (B2:B11).
• Selecione uma célula em branco adjacente aos seus dados — geralmente, na primeira linha ao lado dos seus valores, por exemplo, a célula C2.
• Insira a seguinte fórmula:

=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1

Essa fórmula funciona contando quantos valores dentro do mesmo grupo são menores que o valor atual. Aqui está o que cada parâmetro significa:

• ($A$2:$A$11=A2)
  → Isso verifica se cada célula no intervalo A2:A11 é igual ao valor em A2.
  → Retorna uma matriz de VERDADEIRO/FALSO (ou 1/0) representando se cada linha pertence ao mesmo grupo que A2.
• (B2<$B$2:$B$11)
  → Isso verifica quantos valores em B2:B11 são maiores que B2.
  → Retorna VERDADEIRO (1) se B2 for menor que um determinado valor, FALSO (0) caso contrário.
• * (Multiplicação)
  → Isso combina as duas condições:
• Correspondência de grupo (A2)
  O valor em B2 é menor que outros
  → Então, somente as linhas que estão no mesmo grupo e têm um valor menor serão contadas.
• SOMARPRODUTO(...)
  → Soma o número de linhas que atendem ambas as condições.
• +1
  → As classificações começam em 1 (em vez de 0), então adicionamos 1 à contagem de valores menores.

Uma vez que a fórmula seja inserida em C2, arraste a alça de preenchimento automático para baixo para preencher essa fórmula para todas as linhas relevantes no seu conjunto de dados. A fórmula se ajustará automaticamente para usar o grupo e o valor de cada linha, retornando a classificação dentro desse grupo.

Dicas e precauções:

• Se o seu intervalo for grande, lembre-se de atualizar as referências de células adequadamente.
• Para classificações em ordem decrescente (por exemplo, o maior valor é 1), modifique a comparação da fórmula de B2<$B$2:$B$11 para B2>$B$2:$B$11.
• Para lidar com valores duplicados, essa fórmula atribui a mesma classificação para valores iguais dentro do mesmo grupo. Se você precisar de classificações únicas sequenciais, considere usar colunas auxiliares adicionais.

Esse método baseado em fórmulas é flexível e se aplica facilmente à maioria das estruturas de tabelas agrupadas no Excel. No entanto, para conjuntos de dados muito grandes, o desempenho do cálculo pode diminuir devido à dependência da lógica de matriz.

Código VBA - Use uma macro para automatizar a classificação de valores dentro de cada grupo

Para usuários que desejam automatizar o processo de classificação ou lidar com conjuntos de dados maiores de forma mais eficiente, escrever uma macro VBA pode ser uma abordagem valiosa. Macros podem automatizar etapas repetitivas, oferecer mais personalização e processar dados rapidamente em comparação com fórmulas complexas. Isso é ideal para cenários como geração de relatórios agendados, tarefas de classificação repetidas ou quando você deseja evitar a sobrecarga de fórmulas na sua planilha.

Antes de prosseguir, certifique-se de salvar seu trabalho e habilitar macros nas configurações do Excel. Aqui está como você pode criar e executar essa solução:

1. Pressione Alt + F11 para habilitar o editor VBA. Na janela Microsoft Visual Basic for Applications que aparece, clique em Inserir > Módulo, depois 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

2. Clique em Executar. Uma caixa de diálogo solicitará que você selecione o intervalo completo de dados, a coluna de grupo e a coluna de valor. A macro então gerará uma nova coluna com as classificações de cada valor dentro de seu grupo.

Notas e solução de problemas:

• Certifique-se de que as seleções de colunas correspondam aos seus dados: as colunas de grupo e valor devem estar alinhadas corretamente.
• Se o cabeçalho de dados estiver incluído, ajuste o índice inicial do loop no código para classificação correta (conforme a estrutura dos seus dados).
• Para classificar em ordem decrescente, modifique a comparação Se dictGroups(GroupKey)(j) < arrValues(i,1) de acordo.
• Se você encontrar avisos de permissão ou segurança de macro, verifique as configurações de Segurança de Macro do Excel em Arquivo > Opções > Central de Confiabilidade.

Esse método VBA oferece flexibilidade e desempenho robusto para aplicativos mais avançados ou ampliados, especialmente quando integrado a fluxos de trabalho de relatórios automatizados.

Desbloqueie a Magia do Excel com o Kutools AI

• Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
• Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
• Codificação VBA: Escreva e implemente código VBA sem esforço.
• Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
• Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.

Potencialize suas capacidades no Excel com ferramentas alimentadas por IA. Baixe Agora e experimente uma eficiência como nunca antes!