Filtro automático em tabela dinâmica Excel

Neste artigo é demonstrado como criar um filtro automático em tabelas dinâmicas do Excel.

A tabela dinâmica é uma ferramenta muito útil do Excel. Com ela podemos sumarizar os dados e apresentá-los de diversas maneiras, de forma automática, atualizada, formatada e com muitos recursos. Um destes recursos é o filtro de dados da tabela.

Para utilizar o filtro na tabela dinâmica é necessário selecionar a coluna que deseja filtrar e utilizar uma das diversas opções de filtro.

Neste artigo utilizamos o VBA para fazer com que a filtragem de dados seja realizada de forma automática quando ao digitar o valor o filtro seja atualizado automaticamente, como no gif abaixo:

No exemplo de filtro utilizamos os filtros de “contém o texto” e de “data entre”, veja abaixo como implementar este código.

1. • Crie uma tabela dinâmica conforme o nosso exemplo:

     

   • Habilite a guia Desenvolvedor, caso ela já não esteja habilitada. Veja neste artigo como habilitar a guia http://guiadoexcel.com.br/habilitando-a-guia-desenvolvedor-e-copiando-procedimentos-vba-sub-da-internet.
   • Com a tabela dinâmica já criada, clique em Gravar macro e altere o filtro manualmente da tabela dinâmica, depois identifique no código criado o nome da tabela dinâmica. Este nome será utilizado no código de filtro.

     

   • Volte para a planilha com a tabela dinâmica e na guia Desenvolvedor, selecione Inserir->Caixa de texto.

     

   • Desenhe a caixa de texto conforme o nosso exemplo, uma para o campo de nome, uma para data inicial e outra para data final. Se quiser pode incrementar inserindo uma Caixa de grupo no grupo do filtro de tabela dinâmica, conforme abaixo.

     

   • Agora que já temos o filtro da tabela dinâmica e a tabela dinâmica, vamos implementar o código.
   • Abra o Visual Basic clicando em Desenvolvedor->Visual Basic ou pressionando ALT+F11.
   • Selecione na pasta Módulos um dos módulos da Pasta de trabalho que está trabalhando. Se não houver insira, e cole o código abaixo. Esta função é geral e nunca será alterada, ela faz o filtro da tabela dinâmica para filtros com até dois parâmetros, como por exemplo o filtro de texto exato ou data entre.

'Procedimento de filtro automático de tabela dinâmica
'PARÂMETROS
'lTipo: tipo de filtro que será aplicado na função, aqui os parâmetros que podem ser utilizados em filtros de TD,
'       embora nem todos tenham sido testados nesta função.
'xlBefore, xlBeforeOrEqualTo, xlAfter, xlAfterOrEqualTo, xlAllDatesInPeriodJanuary, xlAllDatesInPeriodFebruary,
'xlAllDatesInPeriodMarch , xlAllDatesInPeriodApril, xlAllDatesInPeriodMay, xlAllDatesInPeriodJune , xlAllDatesInPeriodJuly,
'xlAllDatesInPeriodAugust, xlAllDatesInPeriodSeptember, xlAllDatesInPeriodOctober,xlAllDatesInPeriodNovember,
'xlAllDatesInPeriodDecember, xlAllDatesInPeriodQuarter1, xlAllDatesInPeriodQuarter2, xlAllDatesInPeriodQuarter3,
'xlAllDatesInPeriodQuarter4, xlBottomCount, xlBottomPercent, xlBottomSum, xlCaptionBeginsWith, xlCaptionContains,
'xlCaptionDoesNotBeginWith, xlCaptionDoesNotContain, xlCaptionDoesNotEndWith, xlCaptionDoesNotEqual, xlCaptionEndsWith,
'xlCaptionEquals, xlCaptionIsBetween, xlCaptionIsGreaterThan, xlCaptionIsGreaterThanOrEqualTo, xlCaptionIsLessThan,
'xlCaptionIsLessThanOrEqualTo, xlCaptionIsNotBetween, xlDateBetween, xlDateLastMonth, xlDateLastQuarter, xlDateLastWeek,
'xlDateLastYear, xlDateNextMonth, xlDateNextQuarter, xlDateNextWeek, xlDateNextYear, xlDateThisMonth,
'xlDateThisQuarter, xlDateThisWeek, xlDateThisYear, xlDateToday, xlDateTomorrow, xlDateYesterday, xlNotSpecificDate,
'xlSpecificDate, xlTopCount, xlTopPercent, xlTopSum, xlValueDoesNotEqual, xlValueEquals, xlValueIsBetween,
'xlValueIsGreaterThan, xlValueIsGreaterThanOrEqualTo, xlValueIsLessThan, xlValueIsLessThanOrEqualTo, xlValueIsNotBetween,
'xlYearToDate.
'lValor: valores que serão passados ao filtro, podem ser até 2 na função criada, mas pode ser alterada para incluir vários
'lCampo: nome do campo em que será aplicado o filtro na tabela dinâmica
'lTD: nome da tabela dinâmica
'lPlanilha: nome da planilha
Public Sub lsAutoFiltroTD(ByVal lTipo As String, ByRef lValor() As Variant, ByVal lCampo As String, ByVal lTD As String, _
                          ByVal lPlanilha As String)
    On Error Resume Next

    Dim vTabDin As PivotTable
    Set vTabDin = Worksheets(lPlanilha).PivotTables(lTD)

    vTabDin.ClearAllFilters
    
    If (lTipo  xlDateBetween Or (IsDate(lValor(1)) And IsDate(lValor(2)))) Then
        vTabDin.PivotFields(lCampo).PivotFilters.Add Type:=lTipo, Value1:=lValor(1), Value2:=lValor(2)
    End If
End Sub

• Na árvore do projeto clique duas vezes sobre a planilha que tem a tabela dinâmica e os componentes. No nosso caso Planilha1.
• Selecione nos componentes o objeto TextBox1, equivalente á primeira caixa de texto e selecione o evento KeyUp, o sistema irá gerar automaticamente a chamada do evento.
• O evento KeyUp do TextBox fará com que ao digitar na caixa de texto o evento seja disparado chamando a função de filtro. Insira o código abaixo:

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim lValor(2) As Variant
    
    lValor(1) = TextBox1.Value
    lValor(2) = ""

    lsAutoFiltroTD xlCaptionContains, lValor, "Nome", "Tabela dinâmica1", ActiveSheet.Name
End Sub

• Perceba que o evento envia dois parâmetros, o lValor(1) que é o texto da caixa de texto de nome e lValor(2) que não recebe valor, pois neste caso não é utilizada.
• Na passagem de dados para a função lsAutoFiltroTD temos ainda o tipo xlCaptionsContains, que é o parâmetro que define que o filtro será “contém texto” da tabela dinâmica.
• O próximo campo é o nome do campo em que será aplicado o filtro, no caso “Nome”.
• O último parâmetro é o nome da planilha, no caso passamos a ActiveSheet, ou seja planilha ativa, como parâmetro.
• Agora a planilha já está funcionando para o filtro de nome. Teste voltando á planilha e tirando do modo de Design, se estiver, e digite valores no campo de texto do nome. A tabela dinâmica deverá filtrar automaticamente os dados conforme o que é digitado.
• Abra novamente o Editor do Visual Basic pressionando ALT+F11 e cole o código abaixo.

Private Sub TextBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim lValor(2) As Variant

    lValor(1) = TextBox2.Value
    lValor(2) = TextBox3.Value
    
    lsAutoFiltroTD xlDateBetween, lValor, "Data", "Tabela dinâmica1", ActiveSheet.Name
End Sub

Private Sub TextBox3_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim lValor(2) As Variant

    lValor(1) = TextBox2.Value
    lValor(2) = TextBox3.Value
    
    lsAutoFiltroTD xlDateBetween, lValor, "Data", "Tabela dinâmica1", ActiveSheet.Name
End Sub

• Perceba que o código é muito parecido com o do primeiro caso, exceto o campo tipo que agora é xlDateBetween, ou data entre e os valores do filtro lValor(1) e lValor(2) que agora recebem o valor dos campos de filtro de Data, TextBox2 e TextBox3.
• Voltando ao ambiente do Excel e desmarcando o botão Modo Design, teste a digitação dos filtros de data. É necessário que a data digitada seja válida e estejam digitados os dois campos para que o filtro ocorra.

Então é isso, clique no botão abaixo para realizar o download do nosso exemplo com o código fonte.

Abraço

Marcos Rieper
Referências: GlobalIConnect

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: