Classificar tabela dinâmica Excel automaticamente

Lista de validação de dados Excel com autocompletar VBA
Lista de validação de dados Excel com autocompletar VBA
2 de maio de 2015
Plano de ação Excel - Radar de projetos
Plano de ação Excel – Radar de projetos
17 de maio de 2015

Objetivo: Como classificar uma tabela dinâmica automaticamente no Excel.

Classificar automaticamente tabela dinâmica

A tabela dinâmica no Excel é uma poderosa ferramenta de análise.

Com ela o usuário pode unir grandes massas de dados, criando os seus próprios relatórios com os campos desta base, inclusive criando cálculos. Tudo muito rápido e prático.

No entanto uma das funcionalidades desta ferramenta não é muito prática. A classificação de dados nas tabelas dinâmicas podem ser bastante trabalhosas, principalmente quando se trata de valores.

Para se classificar os dados em uma tabela dinâmica, basta você clicar no botão sobre a coluna e clicar em classificar:

Classificar automaticamente tabela dinâmica Excel

O problema consiste em quando queremos que a tabela dinâmica seja organizada por um determinado campo e este não é o primeiro campo, sendo necessário que todos os campos sejam classificados por este. Para isto deve-se clicar em uma coluna e clicar no botão de classificação e na opção Mais Opções de Classificação, selecionar se será classificado de forma crescente ou decrescente e o campo.

Classificar automaticamente tabela dinâmica Excel 1

Esta operação tem que ser repetida para todos os campos da esquerda para a direita até o campo definido. O que gera um grande trabalho.

Os códigos VBA Excel criados abaixo realizam a classificação de forma crescente ou de forma decrescente, bastando clicar no título da coluna da tabela Excel e clicar no botão Classificar.

 insta

Para utilizar o código siga o passo-a-passo deste artigo: Habilitar a guia desenvolvedor no Excel e copiar códigos VBA da internet.

Sub lsClassificarTabelaDinamicaMaiorMenor()

'Formata a tabela dinâmica com sua formatação preferida, formatação default

'Configurações realizadas
'   1.  Mostrar de forma tabulada os dados
'   2.  Ligar a opção de repetir linhas
'   3.  Desliga os subtotais
'   4.  Liga o total final de coluna
'   5.  Desliga a opção de ajustar a tabela dinâmica
'   7.  Desliga a opção de salvar os dados da tabela dinâmica como arquivo
'   8.  Formata os campos de soma de valor no formato decimal separado por ponto
'   9.  Ajusta as colunas da tabela dinâmica
'   10. Retira do nome dos valores as descrições Contagem, Soma

    Dim pc As PivotCache
    Dim pf As PivotField
    Dim pt As PivotTable
    Dim lo As ListObject
    Dim rng As Range
    Dim strLabel As String
    Dim strFormat As String
    Dim i As Long
    Dim wksSource As Worksheet
    Dim lRange As String
 
 
    'Verifica se estamos lidando com uma versão do Excel que suporta ListObjects
    'Versões superiores ao Excel 2007
    If Application.Version >= 14 Then
 
 
        On Error Resume Next
        Set pt = ActiveCell.PivotTable
        On Error GoTo errhandler

        If pt Is Nothing Then
            MsgBox "Selecione o campo da tabela dinâmica pelo qual quer classificar!"
            GoTo errhandler
        End If
        
        pt.PivotCache.Refresh
        
        lCampo = pt.PivotFields(ActiveCell.Value).Name

        lRange = ActiveCell.Address

        'Limpa a classificação da tabela
        With pt
            If ActiveCell.CurrentRegion.Cells.Count > 1 Then
                For i = 1 To .PivotFields.Count - .DataFields.Count
                    Set pf = .PivotFields(i)
                    With pf
                        .AutoSort xlManual, pf.SourceName
                        On Error Resume Next
                        On Error GoTo errhandler
                    End With
                Next i
            End If
        End With

        'Classifica a tabela pelo campo desejado
        With pt
            If ActiveCell.CurrentRegion.Cells.Count > 1 Then
                For i = .PivotFields.Count - .DataFields.Count - 1 To 1 Step -1
                    Set pf = .PivotFields(i)
                    With pf
                        If pf.Name  "Values" Then
                            .AutoSort xlDescending, lCampo
                            On Error Resume Next
                            On Error GoTo errhandler
                        End If
                    End With
                Next i
            End If
        End With
 
        'Calcula e atualiza a tela
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlAutomatic
        End With
 
        With pt
            .ManualUpdate = False
            .TableRange2.Select
        End With
 
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
 
        ActiveCell.CurrentRegion.EntireColumn.AutoFit
 
        Range(lRange).Select
 
    'Tratamento de erros
    Err.Clear
errhandler:
            If Err.Number > 0 Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                    .Calculation = xlAutomatic
                End With
                If Err.Number = 1004 Then
                    MsgBox "Selecione o cabeçalho do campo da tabela dinâmica que quer classificar!"
                Else
                    MsgBox "Atenção, ocorreu um erro: Error#" & Err.Number & vbCrLf & Err.Description _
                             , vbCritical, "Error", Err.HelpFile, Err.HelpContext
                End If
            End If
    End If
 
End Sub

Sub lsClassificarTabelaDinamicaMenorMaior()
'Formata a tabela dinâmica com sua formatação preferida, formatação default

'Configurações realizadas
'   1.  Mostrar de forma tabulada os dados
'   2.  Ligar a opção de repetir linhas
'   3.  Desliga os subtotais
'   4.  Liga o total final de coluna
'   5.  Desliga a opção de ajustar a tabela dinâmica
'   7.  Desliga a opção de salvar os dados da tabela dinâmica como arquivo
'   8.  Formata os campos de soma de valor no formato decimal separado por ponto
'   9.  Ajusta as colunas da tabela dinâmica
'   10. Retira do nome dos valores as descrições Contagem, Soma

    Dim pc As PivotCache
    Dim pf As PivotField
    Dim pt As PivotTable
    Dim lo As ListObject
    Dim rng As Range
    Dim strLabel As String
    Dim strFormat As String
    Dim i As Long
    Dim wksSource As Worksheet
    Dim lRange As String
 
 
    'Verifica se estamos lidando com uma versão do Excel que suporta ListObjects
    'Versões superiores ao Excel 2007
    If Application.Version >= 14 Then
 
 
        On Error Resume Next
        Set pt = ActiveCell.PivotTable
        On Error GoTo errhandler

        If pt Is Nothing Then
            MsgBox "Selecione o campo da tabela dinâmica pelo qual quer classificar!"
            GoTo errhandler
        End If
        
        pt.PivotCache.Refresh
        
        lCampo = pt.PivotFields(ActiveCell.Value).Name
        
        lRange = ActiveCell.Address

        'Limpa a classificação da tabela
        With pt
            If ActiveCell.CurrentRegion.Cells.Count > 1 Then
                For i = 1 To .PivotFields.Count - .DataFields.Count
                    Set pf = .PivotFields(i)
                    With pf
                        .AutoSort xlManual, pf.SourceName
                        On Error Resume Next
                        On Error GoTo errhandler
                    End With
                Next i
            End If
        End With

        'Classifica a tabela pelo campo desejado
        With pt
            If ActiveCell.CurrentRegion.Cells.Count > 1 Then
                For i = .PivotFields.Count - .DataFields.Count - 1 To 1 Step -1
                    Set pf = .PivotFields(i)
                    With pf
                        If pf.Name  "Values" Then
                            .AutoSort xlAscending, lCampo
                            On Error Resume Next
                            On Error GoTo errhandler
                        End If
                    End With
                Next i
            End If
        End With
 
        'Calcula e atualiza a tela
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlAutomatic
        End With
 
        With pt
            .ManualUpdate = False
            .TableRange2.Select
        End With
 
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
 
        ActiveCell.CurrentRegion.EntireColumn.AutoFit
 
        Range(lRange).Select
 
    'Tratamento de erros
    Err.Clear
errhandler:
            If Err.Number > 0 Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                    .Calculation = xlAutomatic
                End With
                If Err.Number = 1004 Then
                    MsgBox "Selecione o cabeçalho do campo da tabela dinâmica que quer classificar!"
                Else
                    MsgBox "Atenção, ocorreu um erro: Error#" & Err.Number & vbCrLf & Err.Description _
                             , vbCritical, "Error", Err.HelpFile, Err.HelpContext
                End If
            End If
    End If
End Sub

Download-button

Abraço

Marcos Rieper

2 Comentários

  1. Viviane disse:

    Oi Marcos, tudo bem?

    Sempre entro no seu blog atrás de uma solução mágica!! Rs

    Estava com um problema de classificação de tabela dinamica hoje, e por coincidência encontrei esse tópico.
    Mas infelizmente não resolveu meu problema. Você pode me ajudar a pensar em uma solução?
    Olha só:

    Eu tenho um gráfico dinâmico que é gerado por uma tabela que contem os seguintes campos:
    TIPO | MÊS | DIA | VALOR

    A explicação que você deu ai em cima é para classificar o valor independente do TIPO, mas só funciona se o campo tipo não estiver expandido.
    O que eu precisava fazer é que o campo MÊS fosse o determinante. Independente da ordem alfabética de tipo, é importante que a tabela respeite o mês (1, 2, 3, 4, 5, 6)… Mas não consigo fazer!
    E se eu só inverter para mês ser a primeira coluna, o gráfico fica feio, porque o campo tipo fica todo embolorado no mês.
    Deu para entender, né? :p

    Se você souber alguma forma, pode me ajudar?

    Obrigada!!!!

    Att
    Viviane

    • Marcos Rieper disse:

      Boa tarde Viviane,

      Obrigado pelo seu contato.

      Pode entrar no nosso fórum e enviar a sua planilha para eu analisar melhor o problema? Em teoria deveria funcionar a solução para qualquer classificação.

      Abraço

      Marcos Rieper

  2. Raphael disse:

    Olá,

    É possível fazer isto sem ser numa tabela dinâmica?

    • Marcos Rieper disse:

      Bom dia,

      Sim, e neste caso nem precisa de macros. Selecione a coluna que quer classificar, vá na guia Dados e depois em Classificar e Filtrar clique nos botões menores, eles irão classificar automaticamente por este campo. Obrigado pelo seu comentário e contribuição.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.