Filtro automático em tabela dinâmica Excel

Planilha Excel de formação de preço de pacote de viagem
Planilha Excel de custos e formação de preço de pacote de viagem
21 de dezembro de 2015
Planilha NF-e XML Excel
Planilha NFe XML – Importar dados de NF-e no Excel
2 de janeiro de 2016

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:

Filtro automático em tabela dinâmica Excel

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

      • Crie uma tabela dinâmica conforme o nosso exemplo:Filtro automático em tabela dinâmica Excel
      • 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.Filtro automático em tabela dinâmica Excel
      • Volte para a planilha com a tabela dinâmica e na guia Desenvolvedor, selecione Inserir->Caixa de texto.Filtro automático em tabela dinâmica Excel
      • 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.Filtro automático em tabela dinâmica Excel
      • 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

Filtro automático em tabela dinâmica Excel

    • 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


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

7 Comentários

  1. Sandro disse:

    O filtro está retornando o valor errado. O valor de Carolina é 423 e não 185.

  2. Eduardo disse:

    Marcos, boa tarde.

    Teria como usar os mesmos valores de data para duas tabelas? Sendo uma delas em outra planilha dentro da mesma pasta de trabalho?
    Substitui os parâmetros da tabela, mas ele não aceita o objeto Sheets(“plan2”) no último.

    • Marcos Rieper disse:

      Boa tarde Eduardo,

      E se você vincular as duas tabelas dinâmicas? Assim qualquer filtro será aplicado para as duas.

      Crie um slicer(segmentação de dados), clique com o botão direito sobre ele e em Conexões de relatório. Marque as suas tabelas dinâmicas.

      At.
      Marcos Rieper

  3. Heron disse:

    Marcos bom dia,

    Tem como fazer uma rotina para que o VBA copie e cole a informação a ser filtrada na textbox de filtro automático?

    Obrigado pela atenção

  4. Francisco disse:

    Como seria utilizando mais de um nome na lista para filtrar na Tabela Dinâmica? Exemplo: Tenho 5 nomes e quero que a Tabela Dinâmica seja filtrada por eles mas não em text box distintas mas usando uma ListBox por exemplo. É possível?

  5. Eustáquio Mota da Costa disse:

    Tudo me foi útil. Porém, não me permitiu resolver algo que me foi solicitado: vincular A UMA CÉLULA ESPECÍFICA DA PLANILHA o filtro de datas de uma tabela dinâmica, de modo que, ao alterar a data na célula da planilha o filtro da tabela dinâmica fosse atualizado automaticamente.

Deixe uma resposta

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

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

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.