Como classificar dados no Excel usando VBA (um guia passo a passo)

O Excel já possui algumas maneiras de classificar os dados rapidamente.

Você pode classificar facilmente um conjunto de dados usando os ícones de classificação na faixa de opções ou na caixa de diálogo de classificação.

Então, por que você precisa saber como fazer isso usando o VBA?

Saber como classificar dados usando o VBA pode ser útil quando incluído como parte de seu código. Por exemplo, suponha que você obtenha um conjunto de dados diário / semanal que precisa formatar e classificar em uma ordem específica.

Você pode criar uma macro para fazer tudo isso por você com um único clique. Isso vai lhe poupar muito tempo e esforço toda vez que você fizer isso.

Além disso, se você criar painéis do Excel, poderá levar a capacidade de classificação do Excel a um novo nível, permitindo que o usuário classifique os dados apenas clicando duas vezes no cabeçalho (conforme mostrado abaixo).

Abordarei como criar isso mais tarde neste tutorial. Vamos primeiro esclarecer o básico rapidamente.

Compreendendo o método Range.Sort no Excel VBA

Ao classificar usando VBA, você precisa usar o método Range.Sort em seu código.

O ‘intervalo’ são os dados que você está tentando classificar. Por exemplo, se você está classificando os dados em A1: A10, 'Intervalo' seria Intervalo (“A1: A10”).

Você também pode criar um intervalo nomeado e usá-lo em vez das referências de célula. Por exemplo, se eu criar um intervalo nomeado 'DataRange' para as células A1: A10, também posso usar Range (“DataRange”)

Com o método de classificação, você precisa fornecer algumas informações adicionais por meio de parâmetros. Abaixo estão os principais parâmetros que você precisa saber:

• Chave - aqui você precisa especificar a coluna que deseja classificar. Por exemplo, se você deseja classificar a coluna A, você precisa usar a chave: = Range (“A1”)
• Pedido - aqui você especifica se deseja a classificação em ordem crescente ou decrescente. Por exemplo, se você deseja a classificação em ordem crescente, você usará Ordem: = xlAscending
• Cabeçalho - aqui você especifica se seu conjunto de dados tem cabeçalhos ou não. Se tiver cabeçalhos, a classificação começa na segunda linha do conjunto de dados, caso contrário, começa na primeira linha. Para especificar que seus dados têm cabeçalhos, você usará Cabeçalho: = xlSim

Embora esses três sejam suficientes na maioria dos casos, você pode ler mais sobre os parâmetros neste artigo.

Agora vamos ver como usar o método Range.Sort em VBA para classificar dados no Excel.

Classificando uma única coluna sem cabeçalho

Suponha que você tenha uma única coluna sem cabeçalho (conforme mostrado abaixo).

Você pode usar o código abaixo para classificá-lo em ordem crescente.

Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Observe que eu especifiquei o intervalo de dados manualmente como Intervalo (“A1: A12”).

No caso de haver mudanças nos dados e os valores podem ser adicionados / excluídos, você pode usar o código abaixo que se ajusta automaticamente com base nas células preenchidas no conjunto de dados.

Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Observe que, em vez de Range (“A1: A12”), usei Range (“A1”, Range (“A1”). End (xlDown)).

Isso verificará a última célula preenchida consecutivamente na coluna e a incluirá na classificação. Caso haja espaços em branco, serão considerados dados apenas até a primeira célula em branco.

Você também pode criar um intervalo nomeado e usar esse intervalo nomeado em vez das referências de célula. Por exemplo, se o intervalo nomeado for DataSet, seu código agora seria como mostrado abaixo.

Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Agora, deixe-me explicar rapidamente os parâmetros usados ​​nos exemplos acima:

• Key1: = Range (“A1”) - A1 especificado para que o código saiba qual coluna classificar.
• Pedido1: = xlAscending - Especificado o pedido como xlAscending. Se você quiser que seja em ordem decrescente, use xlDescending.
• Cabeçalho: = xlNo - Especificado que não há cabeçalhos. Este também é o valor padrão. Portanto, mesmo se você omitir isso, seus dados serão classificados considerando que não têm cabeçalhos.

Quer saber onde colocar esse código VBA e como executar a macro? Leia este tutorial!

Classificando uma única coluna com cabeçalho

No exemplo anterior, o conjunto de dados não tinha cabeçalho.

Quando seus dados têm cabeçalhos, você precisa especificar isso no código para que a classificação possa começar a partir da segunda linha do conjunto de dados.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Abaixo está o código que irá classificar os dados em ordem decrescente com base nas vendas das lojas.

Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub

Observe que criei um intervalo nomeado - ‘DataRange’ e usei esse intervalo nomeado no código.

Classificação de várias colunas com cabeçalhos

Até agora neste tutorial, vimos como classificar uma única coluna (com e sem cabeçalhos).

Agora, e se você quiser classificar com base em várias colunas.

Por exemplo, no conjunto de dados abaixo, e se eu quiser primeiro classificar pelo código de estado e, em seguida, pela loja.

Aqui está o código que classificará várias colunas de uma vez.

Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlSim. Aplicar End With End Sub

Abaixo está o resultado que você obterá.

No exemplo acima, os dados são classificados primeiro pelo código de estado (coluna A). Então, dentro dos dados do código de estado, eles são novamente classificados pela Loja (Coluna B). Esta ordem é determinada pelo código em que você a mencionou.

Classificando dados usando duplo clique no cabeçalho

Se você estiver criando um painel ou quiser mais facilidade de uso em seus relatórios, pode escrever um código VBA que classificará os dados quando você clicar duas vezes nos cabeçalhos.

Algo conforme mostrado abaixo:

Abaixo está o código que permitirá que você faça isso:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancelar = True Definir KeyRange = Range (Target.Address) Range ("DataRange"). Classificar Key1: = KeyRange, Header: = xlYes End If End Sub

Observe que criei um intervalo nomeado (“DataRange”) e o usei no código em vez de usar as referências de célula.

Assim que você clica duas vezes em qualquer um dos cabeçalhos, o código desativa a funcionalidade usual de clique duplo (que é entrar no modo de edição) e usa essa célula como a chave enquanto classifica os dados.

Observe também que, a partir de agora, esse código classificará todas as colunas apenas em ordem crescente.

Observe que o clique duplo é um gatilho que permite que o Excel execute o código especificado. Esses gatilhos, como clique duplo, abrir uma pasta de trabalho, adicionar uma nova planilha, alterar uma célula, etc., são chamados de eventos e podem ser usados ​​para executar macros no Excel. Você pode ler mais sobre os eventos do Excel VBA aqui.

Onde colocar esse código?

Você precisa colar esse código na janela de código da planilha na qual deseja essa funcionalidade de classificação com duplo clique.

Para fazer isso:

• Clique com o botão direito na guia da folha.
• Clique em Exibir código.
• Cole o código na janela de código da planilha em que seus dados residem.

E se você quiser classificar as duas primeiras colunas ('Estado' e 'Loja') em ordem crescente, mas a coluna 'Vendas' em ordem decrescente.

Aqui está o código que fará isso:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Definir KeyRange = Range (Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub

No código acima, ele verifica se a célula que é clicada duas vezes é o cabeçalho de Vendas ou não. Se sim, ele atribui o valor xlDescending à variável SortOrder, caso contrário, torna xlAscending.

Agora, vamos avançar um pouco mais e mostrar um marcador visual (seta e célula colorida) no cabeçalho quando ele for classificado.

Algo conforme mostrado abaixo:

Para conseguir isso, adicionei uma nova planilha e fiz as seguintes alterações (você pode baixar o arquivo de exemplo e acompanhar):

• Alterou o nome da nova planilha para ‘BackEnd’.
• Na célula B2, insira um símbolo de seta (para fazer isso, vá em Inserir e clique na opção ‘Símbolo’).
• Copie e cole os cabeçalhos do conjunto de dados na célula A3: C3 na planilha ‘Backend’.
• Use a seguinte função na célula A4: AC4:

  = SE (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)

• O restante das células será preenchido automaticamente pelo código VBA quando você clicar duas vezes nos cabeçalhos para classificar a coluna.

Sua folha de back-end seria algo como mostrado abaixo:

Agora você pode usar o código abaixo para classificar os dados clicando duas vezes nos cabeçalhos. Ao clicar duas vezes em um cabeçalho, a seta será exibida automaticamente no texto do cabeçalho. Observe que também usei a formatação condicional para destacar a célula.

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes Worksheets ("BackEnd ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i) .Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1) .Value Next i End If End Sub

Observe que este código funciona bem para a forma como meus dados e pasta de trabalho são construídos. Se você alterar a estrutura dos dados, terá que modificar o código de acordo.

Baixe o arquivo de exemplo