Como classificar valores por grupo no Excel?

Trabalhar com dados agrupados no Excel muitas vezes exige 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") requer uma abordagem específica. Isso é especialmente útil quando você precisa avaliar o desempenho ou identificar registros superiores e inferiores entre diferentes categorias sem misturar os resultados entre os grupos. Os métodos a seguir exploram soluções práticas para classificar valores por grupo, facilitando interpretar e analisar seus dados com precisão nas tarefas diárias.

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

Classificar valores por grupo

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

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

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 significado de cada parâmetro:

• ($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.

Depois de inserir a fórmula em C2, arraste a alça de preenchimento automático para baixo para preencher essa fórmula para todas as linhas relevantes no conjunto de dados. A fórmula 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 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), altere 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 de 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, fornecer 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ê quer evitar a sobrecarga de fórmulas em 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 seu intervalo de dados completo, 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 (de acordo com a estrutura dos seus dados).
• Para classificar em ordem decrescente, modifique a comparação If dictGroups(GroupKey)(j) < arrValues(i,1) conforme necessário.
• 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 aplicações mais avançadas ou ampliadas, 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!