Planilha Filtro automático Excel ao digitar

Neste artigo você verá como criar uma planilha de filtro automático Excel ao digitar. Neste tutorial você verá como criar um filtro de dados tanto para textos quanto para números inteiros, decimais, percentuais e datas e todos os filtros podem ser usados em conjunto.

Criando a lista para o filtro automático no Excel

A lista de dados deve estar no formato de banco de dados, ou seja, com cabeçalhos e os dados da lista devem ser concisos, cada coluna só pode ter aquele tipo de dado.

No nosso exemplo usaremos uma lista de preços de uma cadeia de lojas, mas pode ser aplicado em inúmeras situações, como por exemplo listas de notas fiscais, listas de vendas, listas de contas contábeis entre outras.

Criando os campos de filtros

1. Aumente a altura do cabeçalho para que você possa inserir os campos aonde serão digitados os filtros.
2. Clique com o botão direito sobre a guia de funcionalidades do Excel e selecione a opção Personalizar a faixa de opções e marque a opção Desenvolvedor no lado direito caso esteja desmarcada.
3. Na guia Desenvolvedor, selecione a opção Inserir e insira o componente Caixa de texto.

   

   
   
4. Desenhe sobre cada uma das colunas esta caixa de texto que será utilizada para o filtro de dados automático no Excel e ajuste o tamanho dos mesmos, coloque dois no campo Intervalo.

   

5. Verifique se o botão Modo de Design na guia Desenvolvedor está marcado e clique com o botão direito sobre o botão acima do campo código e clique em Propriedades. Nesta aba de configurações altere a propriedade Name para Codigo, ou outro nome de acordo. Repita a operação para todos os textbox. Nós colocamos os seguintes nomes para os textBox sequencialmente da esquerda para a direita.
   1. Codigo
   2. Loja
   3. Produto
   4. Estoque
   5. PrecoProduot
   6. Comissao
   7. DataDe
   8. DataAte
6. Clique sobre o cabeçalho da planilha e clique em Dados e marque a opção Filtro.

Agora você já tem os campos todos os campos que usaremos para programar o filtro automático n Excel e também a planilha já tem o filtro marcado sobre o mesmo.

Na próxima etapa iremos programar conforme cada campo como o mesmo irá filtrar os dados.

Programando o filtro automático no Excel

Para realizarmos a programação o botão Modo Design na guia Desenvolvedor deverá estar ativo, pois desta forma você estará editando os objetos de formulários do Visual Basic dentro do Excel.

• Dê um duplo clique sobre o campo criado Codigo, o sistema irá criar automaticamente uma função da seguinte forma.

  

  
  Este evento criado é disparado quando o campo Codigo for alterado, ou seja, quando for digitado no textBox Codigo na planilha, todos os códigos que estiverem neste procedimento serão executados.
• Cole o seguinte código no procedimento. Este código irá realizar o filtro de dados do tipo Inteiro, veja que o código no início verifica se o campo está diferente de vazio e se estiver insere um filtro do primeiro campo (Field), com o critério (Criteria1), no qual o filtro é igual campo digitado no textBox Codigo (“=” & Codigo.Text). Utilize este filtro para campos inteiros e com busca exata, ou seja somente aquele código, lembrando de substituir o Codigo.Text pelo nome do seu textBox. Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

Private Sub Codigo_Change()
  If Codigo.Text  "" Then
    Selection.AutoFilter Field:=1, Criteria1:="=" & Codigo.Text
  Else
    Selection.AutoFilter Field:=1
  End If
End Sub

• Volte para a planilha clicando para fechar o ambiente VBE no canto superior direito, botão X da janela.
• Clique duas vezes sobre o textBox sobre o campo Loja e no campo gerado cole o seguinte código fonte dentro do procedimento.

Private Sub Loja_Change()
    Selection.AutoFilter Field:=2, Criteria1:=CStr("*" + Loja.Text + "*")
End Sub

Este código realiza o filtro de colunas de texto, campos do tipo string, veja que o campo de filtro é o Field:=2, ou seja, segunda coluna da tabela, referente á loja, o critério de filtro (Criteria1) é que seja igual á CStr(“*” + Loja.Text + “*”), o CStr faz a conversão de qualquer valor para Texto, e o + concatena o texto, então se tiver digitado 5 no campo loja, ele ficará “*5*” e assim o filtro buscará na coluna Loja qualquer valor que tenha 5 no seu campo.

• Volte para o Excel e clique duas vezes sobre o textBox Produto, o mesmo irá criar um código automaticamente. Substitua o mesmo pelo código abaixo:

Private Sub Produto_Change()
    Selection.AutoFilter Field:=3, Criteria1:=CStr("*" + Produto.Text + "*")
End Sub

Este código é semelhante ao filtro da coluna Loja, com as mudanças do Field ser a coluna 3, equivalente aos produtos e no critério de filtro utilizar o TextBox Produto.Text ao invés do Loja.Text.  Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

• Da mesma forma clique duas vezes sobre o TextBox equivalente ao campo de Estoque e digite o código a seguir.

Private Sub Estoque_Change()
  If Estoque.Text  "" Then
    Selection.AutoFilter Field:=4, Criteria1:="=" & Estoque.Text
  Else
    Selection.AutoFilter Field:=4
  End If
End Sub

Veja que no código o sistema está verificando se o TextBox Estoque é diferente de vazio e inserindo o filtro neste caso, para a coluna 4 e o critério de que o valor do estoque seja exatamente o digitado, então este filtro automático no Excel é feito por um número inteiro e não aproximado. Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

• Volte para o Excel e dê um duplo clique sobre o TextBox Preco, alterando o código para o seguinte.

Private Sub PrecoProduto_Change()
  If PrecoProduto.Text  "" Then
    Selection.AutoFilter Field:=5, Criteria1:=">=" & Replace(PrecoProduto.Text, ",", "."), Criteria2:="<=" & Replace(PrecoProduto.Text + 0.0099999, ",", ".")
  Else
    Selection.AutoFilter Field:=5
  End If
End Sub

Este filtro também identifica se o campo PrecoProduto está diferente de vazio e se estiver faz uma busca pelo valor. Neste caso para o filtro de Decimal é necessário usar dois critérios Criteria1 e Criteria2, aonde o valor digitado com vírgula é convertido para ponto por causa do formato americano e é utilizado >= no primeiro critério e <= no segundo para que os dados sejam filtrados dentro destes valores.

Veja que no segundo critério é aumentado o valor em 0.00999999, para que o filtro funcione corretamente.

• Retorne ao Excel e clique duas vezes sobre o TextBox Comissao e altere o seu código fonte conforme a seguir.

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

Este filtro é percentual, por isso o filtro é quase igual ao Decimal, anterior a este, porém note que o valor no código é dividido por 100, para converter o mesmo em percentual, de modo que ao digitar 50 o sistema entenda que é 50% e não 5000%, e também não seja necessário digitar 0,5. Lembre sempre que se o nome do seu campo for diferente você deve mudar todos os códigos que tenham .Text. Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

• Voltando ao Excel dê um duplo clique sobre o campo DataDe e digite o seguinte código.

Private Sub DataDe_Change()
  If DataDe.Text  "" And IsDate(DataDe.Text) And IsDate(DataAte.Text) Then
    Selection.AutoFilter Field:=7, Criteria1:=">=" & Format(DataDe.Text, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(DataAte.Text, "mm/dd/yyyy")
  Else
    Selection.AutoFilter Field:=7
  End If
End Sub

Private Sub DataAte_Change()
  If DataDe.Text  "" And IsDate(DataDe.Text) And IsDate(DataAte.Text) Then
    Selection.AutoFilter Field:=7, Criteria1:=">=" & Format(DataDe.Text, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(DataAte.Text, "mm/dd/yyyy")
  Else
    Selection.AutoFilter Field:=7
  End If
End Sub

Este código identifica se o campo DataDe é diferente de vazio e se os campos DataDe e DataAte são datas, para evitar erros de filtro.

Também note que o código é filtrado entre as datas utilizando os operadores >= e <= e também que a data é convertida para o formato americano pela utilização da função Format e da mascará “mm/dd/yyyy”, fazendo com que a data 31/10/2010 seja formatada para 10/31/2010 por exemplo.  Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

• Volte ao Excel e no Campo DataAte digite também o mesmo código, pois o mesmo filtro é aplicado digitando em um e em outro.

Lembre que para utilizar o sistema não deve estar no Modo Design.

Baixe a planilha

Abraço

Marcos Rieper

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: