Planilha com Filtro Automático

Neste passo-a-passo vamos ver como criar uma planilha com filtro automático de dados, sem a necessidade de clicar para abrir o filtro.
Esta planilha terá campos do tipo Caixa de Texto utilizando VBA para executar o filtro para cada campo. O filtro será aplicado a cada letra que você digitar, ocasionando uma planilha com um filtro extremamente rápida e perfeita para planilhas de preços por exemplo.



Vamos lá:

1. Faça o download da lista de dados aqui.

Lista de dados

Lista de dados

2. Note que o filtro já está aplicado no cabeçalho, caso a sua planilha não tivesse, você deve aplicar o filtro para continuar.

3.Clique na guia Desenvolvedor e no botão Modo de Design, o que colocará o Excel no modo de desenvolvimento para poder colocar componentes de formulário na planilha.

3. Clique na guia Desenvolvedor, no botão Inserir e selecione Caixa de Texto.

Caixa de texto

Caixa de texto

4.Coloque as caixas de texto acima dos nomes de cada uma das colunas conforme a figura.

Inserção das caixas de texto

Inserção das caixas de texto

5.Clique sobre a caixa de texto da coluna código e nomeie-a como Codigo na caixa de nome, repita a operação para as caixas de texto seguintes, como Loja, Produto, Estoque e Preco.

Nomear caixas de texto

Nomear caixas de texto

6.Clique na guia Desenvolvedor e desmarque o botão Modo de Design.

7.Ainda na guia Desenvolvedor clique no botão Visual Basic.

8.Clique no botão Project Explorer, selecione o projeto VBAProject (Lista de dados.xlsm), clique em Plan1 neste projeto conforme na figura.

Projeto

Projeto

9.No lado direito clique na guia  de código fonte digite o código abaixo:

Private Sub Codigo_Change()
  If Codigo.Text <> "" Then
    Selection.AutoFilter Field:=1, Criteria1:="=" & Codigo.Text
  Else
    Selection.AutoFilter Field:=1
  End If
End Sub
 
Private Sub Estoque_Change()
  If Estoque.Text <> "" Then
    Selection.AutoFilter Field:=4, Criteria1:="=" & Estoque.Text
  Else
    Selection.AutoFilter Field:=4
  End If
End Sub
 
Private Sub Loja_Change()
    Selection.AutoFilter Field:=2, Criteria1:=CStr("*" + Loja.Text) + "*"
End Sub
 
Private Sub Preco_Change()
  If Preco.Text <> "" Then
    Selection.AutoFilter Field:=5, Criteria1:=">=" & Replace(Preco.Text, ",", "."), Criteria2:="<=" & Replace(Preco.Text + 0.0099999, ",", ".")
  Else
    Selection.AutoFilter Field:=5
  End If
End Sub
 
Private Sub Produto_Change()
    Selection.AutoFilter Field:=3, Criteria1:=CStr("*" + Produto.Text) + "*"
End Sub
 
Private Sub Comissao_Change()
  If Comissao.Text <> "" Then
    Selection.AutoFilter Field:=6, Criteria1:=">=" & Replace(Comissao.Text / 100, ",", "."), Criteria2:="<=" & Replace(Comissao.Text / 100 + 0.0099999, ",", ".")
  Else
    Selection.AutoFilter Field:=6
  End If
End Sub
 
 
Private Sub txtAte_Change()
  If txtData.Text <> "" And IsDate(txtData.Text) And IsDate(txtAte.Text) Then
    Selection.AutoFilter Field:=7, Criteria1:=">=" & Format(txtData.Text, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(txtAte.Text, "mm/dd/yyyy")
  Else
    Selection.AutoFilter Field:=7
  End If
End Sub
 
Private Sub txtData_Change()
  If txtData.Text <> "" And IsDate(txtData.Text) And IsDate(txtAte.Text) Then
    Selection.AutoFilter Field:=7, Criteria1:=">=" & Format(txtData.Text, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(txtAte.Text, "mm/dd/yyyy")
  Else
    Selection.AutoFilter Field:=7
  End If
End Sub

 

Pronto! Agora sempre que você digitar os dados nos campos TextBox você terá os filtros sendo aplicados diretamente nestas colunas.

botao_download

excel vba

SISTEMA DE GESTÃO CONTA AZUL

O Excel é uma plataforma de trabalho muito aberta para servir ao gerenciamento de dados de uma empresa, dado a complexidade e a integração entre as diversas áreas que a compõe. Mas é a melhor para analisar suas informações.

Com a intenção de atender as micro e pequenas empresas a Conta Azul criou um software de gestão aonde você tudo o que você precisa para gerir a sua empresa: Estoque, Vendas, Financeiro e ainda emite Nota Fiscal Eletrônica, tudo isso em um sistema simples de trabalhar, que você acessa pela internet, não há a necessidade de comprar servidores e preocupar-se com backup e atendimento através de 0800.

O preço da ferramenta também chama a atenção, sendo um preço mais do que justo para se ter o controle dos dados e a geração de informações para a análise do seu negócio.

ContaAzul é um software de gestão financeira e fiscal para micro e pequenas empresas. Ele funciona 100% em plataforma web, não necessitando nenhuma instalação ou atualização. Você pode acessar de qualquer lugar e não tem necessidade de comprar nova licença caso troque de computador.

  O Guia do Excel recomenda o Conta Azul no gerenciamento de sua empresa.
Use QR-Code to get this permaking using your Smartphone. QR Code for Planilha com Filtro Automático

40 Comentários

  1. Susana Ferreira disse:

    Bom dia. Seria muito útil disponibilizarem a folha de dados excel e também este pequeno tutorial em formato pdf.
    Muito obrigada e sem mais nada de momento,
    Susana Ferreira

    • Rieper disse:

      Bom dia Susana, eu havia colocado um link para o download, mas vou adotar a utilização de um botão para o download dos exemplos.

      Com relação ao PDF assim que tiver disponibilidade eu farei é que demoraria um pouco para fazer a formatação do arquivo.

      Obrigado pela sua visita.

  2. Fábio disse:

    Rieper, blz?

    A parte do preço, pelo menos aqui, não está funcionando corretamente.
    Ele não reconhece “,” ai para procura de um valor igual a 19,00 preciso colocar 19.00. e valores acima de mil eu não consigo localizar.

    Mas bem legal esse esquema que vc fez, as vz é um saco ficar procurando os elementos que desejo. Parece que no excel 2010 eles fizeram uma box de procura no filtro , vc ja viu ?

    abs
    Fábio

  3. [...] neste link para o artigo anterior e veja o passo a passo http://guiadoexcel.com.br/planilha-com-filtro-automatico e faça o download da nova planilha pronta com os exemplos de diferentes filtros no botão [...]

  4. Marcos disse:

    meus amigos, infelizmente nao deu certo, vejam abaixo a planilha, se puderem me dar um help agradeço.
    http://www.4shared.com/file/bNDRQU9K/PRAZO_DE_ENTREGA.html
    Abraço a todos!!!

  5. Marcos disse:

    Parabéns, show mesmo, esta funcionando corretamente, mas indico que criem a tabela, simples porém com um detalhe, deverá estar na primeira linha o cabeçalho, ou seja linha 1

  6. Paulo Rangel disse:

    Prezado Marcos Rieper, Bom dia!

    Primeiramente obrigado por sua atençao, e meus parabéns pelo site. Estou à 14 dias perdendo noites de sono e sem dúvida cansado mentalmente por tanto tentar de forma desordenada, fazer algo que não sei como fazer. estou feliz neste momento por ter, entre essas buscas desesperadas que fiz junto ao google, encontrado você, e ACREDITO que estou iniciando nesse momento, o progresso para a solução de minha problemática!

    Exemplefiquemos…

    Em uma fábrica de camisas, eu e outros cólegas, trabalhamos pregando os botões de tais camisas…
    sendo cada uma delas enumeradas por um n° de fabricação, e todas contém 8 botões.

    Essas camisas precisam estar sempre em rotatividade, de forma que nós pregamos um botão, e as repassamos ao nossos companheiros, é possível que uma mesma camisa passe por minha mão duas vezes no mesmo dia, ou é possível que eu pregue os 8 botões sozinho, porém em 8 dias diferentes.

    A questão caro Marcos Rieper, é que para contabilizar a produtividade de cada um de nós pregadores de botões, a cada botão pregado nós precisamos registrar num pedaço de papel, o n° de registro da camisa a data, e que botão foi pregado… 1°, 2°, 3° ou 8°.

    Então minha tabela é assim…

    Camisa n° 1° Botão 2° Botão 3° Botão 4° Botão 5° Botão 6° Botão 7° Botão 8° Botão

    123 20/12
    142 20/12 20/12
    155 24/12 28/12
    167 24/12
    174 28/12
    189 28/12

    Então, podemos verificar que no dia 20/12 eu preguei três botões em camisas, sendo na camisa n°123 o 2° botão, e na camisa n°142, o 1° e o 8° botão. Enfim… é possível se pregar muitos botões em 6hs de trabalho!

    PRECISO que um relatório baseado nos dados de minha planilha, retorne ao final do dia (sempre a data atual) todas as camisas que eu tratei…

    Suponhemos que hoje seja dia 20/12, meu relatório deveria retornar esses valores em tela…

    Camisa n° 1° Botão 2° Botão 3° Botão 4° Botão 5° Botão 6° Botão 7° Botão 8° Botão

    123 20/12
    142 20/12 20/12

    Desculpe-me não não fui claro, mas a questão é que preciso jogar esta data atual… em todos esses campos (colunas) ao mesmo tempo, porém, não sei como faze-lo, pois se eu por um acaso inicia-se meu filtro por aquela seta ao lado do nome da coluna… com esta data 20/12, automaticamente o registro da camisa 123 já não apareceria, devido ao fato de que esta camisa não teve seu primeiro botão pregado neste dia, o excel somente me retornaria até então, todas as camisa que tiveram os 1°s botões pregados neste dia.

    E se eu fosse adiantew, e inserisse o mesmo filtro (20/12) na coluna seguinte(2° Botão), o registro da camisa 142 desapareceria, porque esta camisa não teve seu 2° botão pregado neste dia, e o meu filtro até o momento, somente estará retornando registros de camisas que tiveram no dia 20/12 o “1°Botão” “e” o “2°Botão” pregados.

    O meu problema parece ser este “e”, o que parece que eu preciso é “ou”! Preciso que retorne a tela todas as camisas que tiveram quaisquer um de seus botões pregados no dia 20/12, em uma única tabela!

    E é necessário que seja em uma única tabela, porque na verdade meu trabalho não se trata de pregar botões, procurei uma maneira exemplificada de passar meu problema, por isso digo que deve ser em uma única tabela, a exemplo do registro da camisa n°142, que teve seu 1° e seu 8° botão pregados no mesmo dia, é exatamente desta forma, deve ser mostrado na mesma linha os dois registros, e não, retornar registro da camisa n°142 para 1° botão em uma linha, e abaixo mais um registro para a camisa n°142, agora porém, para o 8° botão.

    Caso não seja possível elaborar um relatório atendendo a essas necessiades/exigências, de nada me servirá…

    Espero ter conseguido passar o que preciso, grato por toda sua atenção.
    Paulo Rangel.

  7. Paulo Rangel disse:

    Segue tabela reconstruida.

    Então minha tabela é assim…

    Camisa n°—–1° Botão–2° Botão–3° Botão–4° Botão–5° Botão–6° Botão–7° Botão–8° Botão
    ——————————————————————————————–
    123———————20/12—————————————————————
    142———–20/12—————————————————————–20/12—
    155——————————-24/12—————28/12———————————
    167———–24/12————————————————————————-
    174—————————————–28/12——————————————-
    189— ———————————————————28/12———————–

    Então, podemos verificar que no dia 20/12 eu preguei três botões em camisas, sendo na camisa n°123 o 2° botão, e na camisa n°142, o 1° e o 8° botão. Enfim… é possível se pregar muitos botões em 6hs de trabalho!

    PRECISO que um relatório baseado nos dados de minha planilha, retorne ao final do dia (sempre a data atual) todas as camisas que eu tratei…

    Suponhemos que hoje seja dia 20/12, meu relatório deveria retornar esses valores em tela…

    Camisa n°—–1° Botão–2° Botão–3° Botão–4° Botão–5° Botão–6° Botão–7° Botão–8° Botão
    ——————————————————————————————–
    123———————20/12—————————————————————
    142———–20/12—————————————————————–20/12—

    Desculpe-me não não fui claro, mas a questão é que preciso jogar esta data atual… em todos esses campos (colunas) ao mesmo tempo, porém, não sei como faze-lo, pois se eu por um acaso inicia-se meu filtro por aquela seta ao lado do nome da coluna… com esta data 20/12, automaticamente o registro da camisa 123 já não apareceria, devido ao fato de que esta camisa não teve seu primeiro botão pregado neste dia, o excel somente me retornaria até então, todas as camisa que tiveram os 1°s botões pregados neste dia.

    E se eu fosse adiantew, e inserisse o mesmo filtro (20/12) na coluna seguinte(2° Botão), o registro da camisa 142 desapareceria, porque esta camisa não teve seu 2° botão pregado neste dia, e o meu filtro até o momento, somente estará retornando registros de camisas que tiveram no dia 20/12 o “1°Botão” “e” o “2°Botão” pregados.

    O meu problema parece ser este “e”, o que parece que eu preciso é “ou”! Preciso que retorne a tela todas as camisas que tiveram quaisquer um de seus botões pregados no dia 20/12, em uma única tabela!

    E é necessário que seja em uma única tabela, porque na verdade meu trabalho não se trata de pregar botões, procurei uma maneira exemplificada de passar meu problema, por isso digo que deve ser em uma única tabela, a exemplo do registro da camisa n°142, que teve seu 1° e seu 8° botão pregados no mesmo dia, é exatamente desta forma, deve ser mostrado na mesma linha os dois registros, e não, retornar registro da camisa n°142 para 1° botão em uma linha, e abaixo mais um registro para a camisa n°142, agora porém, para o 8° botão.

    Caso não seja possível elaborar um relatório atendendo a essas necessiades/exigências, de nada me servirá…

    Espero ter conseguido passar o que preciso, grato por toda sua atenção.
    Paulo Rangel.

  8. Paulo Rangel disse:

    Tentei corrigir os espeços da tabela, porém, sem sucesso!

  9. Paulo Rangel disse:

    Boa tarde Marcos,

    Ficarei grato, pois tentei (de forma simples) justamente criando uma tabela dinâmica onde utilizei os filtros da própria tabela (aquela setinha ao lado rótulos das colunas) onde eu precisaria na verdade inserir em todas as oito colunas (datas iguais), e não funcionava, acredito que porque o excel entende (da forma que fiz) que eu quero localizar um registro onde “todos os oitos botões tivessem sido pregados no mesmo dia”, quando na verdade, eu quero que me retorne quaisquer aplicações que tivessem sido feitas nesta determinada data.

    Desde já agredecido por atenção.
    Grande abraço, Paulo.

  10. Paulo Rangel disse:

    Marcos, boa tarde!

    Obrigado por sua atenção, mas o que me encaminhou, não é exatamente o que preciso, reformulei minha planilha, e nesse momento, o que estou precisando é…

    Em uma tabela com fonte de dados externos, em determinado campo de data, atribuir um filtro personalizado, onde o resultado é diferente de “ontem”.

    Quando jogo a fórmula “=HOJE()-1″, no filtro personalizado, todas as datas aparecem, isso quer dizer que o excel não está entendendo “=HOJE()-1″, como fórmula, mas sim como texto.

    Não posso utilizar um filtro avançado devido ao fato de utilizar fonte de dados externos, então não posso definir o intervalo.

    Preciso mesmo é conseguir inserir no filtro personalizado essa fórmula… DIFERENTE DE ONTEM!!!

    Abraço, Paulo Rangel.
    Poderia me ajudar com isso

  11. edivandro disse:

    bom dia, marcos !

    antes de mais nada, perfeito o que vc passou acima ! me foi muito esclarecedor, pois busco algo próximo do que vc postou e não tava conseguindo nem a pau ,,,

    é o seguinte: tenho uma lista com mil nomes e gostaria de utilizá-la como banco de dados para, em uma outra planilha, digitar diretamente na célula e conforme digitar “aparecer” as opções de nomes conforme aquela lista base – isso aos moldes do preenchimento automático do excel (deixe-me adiantar: não funcionou bem com uma lista grande como essa com muitas opções parecidas nos nomes…)

    poderia me ajudar ?

  12. Nepow disse:

    Excelente dica! Trabalho com diversos filtros e vai ajudar bastante! parabéns e obrigado!

  13. Marcelo disse:

    Olá, primeiro quero agradecer as ferramentas e conhecimentos compartilhados, realmente o mundo seria bem melhor se todos contribuíssem.

    Preciso de uma ajuda, minha planilha contém 29 colunas e quando faço o VB dá erro, por favor, pode ajudar?

    VLW!

  14. Maicon disse:

    OLA…
    COMO FAÇO, PARA APLICAR ESSE FILTRO AUTOMÁTICO EM UMA PLANILHA PROTEGIDA???

    VLW

  15. Felipe disse:

    Cara de tempos que procuro aprender VBA pela internet e este seu código foi o mais simples e util possível. Não querendo pedir demais, más, existe a possibilidade do filtro funcionar com as iniciais da palavra, tipo, não precisa ser exatamente o texto certilho, com as iniciais ele já vai filtrando, tem como? E outra fora do assunto, você da cursos? Obrigado e boa sorte, você é demais cara.

  16. Toninho disse:

    Bom dia Marcos Rieper

    Gosteira da sua planilha, e estou adaptado ela pro meu uso, so queria tira um duvida, no campo Loja e possivel ler numeros e textos?

    Grato

  17. Medleyne disse:

    Boa tarde,
    To precisando montar uma planilha, do seguinte jeito, controle de entrega.
    Quando a entrega da mercadoria chegar lanço a data e quero que este item vai para outra aba da planilha.
    Como devo fazer?
    Obrigada.

  18. Maurilio disse:

    Show, Show, Show de bola. Muito Obrigado

  19. Roberto disse:

    Muito bom este recurso. Entretanto, quando a planilha está protegida o textbox não fica ativo e dá um erro de programação..como usar este filtro automático em uma planilha protegida com senha?

  20. Danilo disse:

    Marcos,

    como devo fazer para que os dados que são procurados estejam em outro arquivo protegido com senha? tentei proteger a planilha porem o código não funciona.

    Estou tentado montar uma planilha de estoque onde determinadas pessoas só podem visualizar onde o produto está mais não podem inserir nenhuma informação.

  21. Boa tarde Marcos!
    Primeiramente, meu parabéns!! ótima planilha
    bom.. eu tentei fazer o auto filtro em tabela dinâmica. funcionou em partes.
    quanto coloco uma letra ou numero, filtra. mais se coloco mais uma letra ou numero, não filtra.

    gostaria de ajuda

  22. Corrigindo Marcos.
    não é auto filtro.
    é filtro automático

  23. Bom dia Marcos!

    Tentei criar o filtro automatico numa tabela dinamica.
    funcionou da seguinte forma:

    na caixa de texto se eu coloco uma letra ou numero, funciona.
    exemplo: “b” ou “1″

    mais se eu coloco mais de uma letra ou numero da erro de execução.
    exemplo: “bola” ou “123″

    qualquer ajuda é bem vinda
    obrigado

    • Marcos Rieper disse:

      Boa tarde Heryson,

      No exemplo que você tem na planilha você tem que seguir a sequência de caracteres, ele só vai filtrar se existir esta sequência. Por exemplo bola escrito na coluna.

      Abraço

      Marcos Rieper

  24. Boa tarde marcos!

    Obrigado pela força.

    olha só o código que eu criei. na verdade é quase que uma cópia do código da sua planilha.

    na minha planilha: quando eu coloco uma letra ou um numero começa a filtrar. quando coloco a segunda letra ou numero, *erro*

    If código.Value “” Then
    ActiveSheet.PivotTables(“Tabela dinâmica1″).PivotFields(“Código”).PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:=” ” & código.Value

    obrigado Marcos

  25. JOSE MENDES disse:

    BOM, PRECISO QUE, APEREÇA E UMA DETERMINADA CELULAR A DIFERENÇA ENTRE DATAS EM PERCENTUAL EXE:

    SENDO HOJE DIA 18/01/2014

    DATA PEDID.DIAS P/ENTREGA % DATA ENTREGA DIAS REST. STATUS
    B1=16/01 B4=5DIAS B9=? B14= 21/01 B16=3DIAS B19=ENTR
    =AGUAR.
    =CANC.

    PRECISO QUE NA CELULA SEJA B9=ME APAREÇA O PERCENTUAL DA DIFERENÇA ENTRE AS DUAS DATAS EM RELAÇAO AOS DIAS QUE FALTAM PRA CONCLUSÃO DO PEDIDO E TAMEBEM QUE A PLANILHA CONGELE A LINHA QUANDO O ESTATUS ESTIVER “ENTREGUE” OBS QUANDO O STATUS ESTIVER ENTREGUE O DIA SERÁ O MESMO QUE O DA ENTREGA POR TANTO A CELULA B16=0, PRECISO QUE AO ZERAR ALINHA INTEIRA SE CONGELE POIS SE NÃO AS FORMULAS FICARAM COM ERRO!!! DESDE JÁ AGRADEÇO SE ALGUEM PODER ME AJUDAR…..

  26. hENRIQUE disse:

    Ao invés de eu fazer este filtro através do código, é possível eu fazer a mesma coisa através do Nome do produto por exemplo ?
    Sendo que neste caso, é possível filtrar no modo de “Contém” ?
    Exemplo:
    Tenho os produtos: ABC, AAABC, WD, XAZ.
    Se eu Escrever a letra A no meu filtro ele irá me mostrar os itens: ABC, AAABC, XAZ, (POIS TODOS CONTÉM A LETRA A)
    Se eu escrever a letra B no filtro, ele irá me mostrar os itens:
    ABC, AAABC, ( e assim por diante ? )

  27. Marlene disse:

    Bom dia Marcos,
    Muito boa a explicação. Parabéns!
    Por favor, qual código uso em uma coluna onde algumas linhas possuem numeros inteiros e outras, número acompanhados de letras
    Ex.
    1° linha – 350
    2° linha – 95333D/350
    Mais uma dúvida, a coluna de códigos sempre deve existir? No meu caso posso substitui-la pelo número de pedidos que utilizo?

    Aguardo/ obrigada
    Marlene

Deixe o seu comentário


 
%d blogueiros gostam disto: