FILTRO AVANÇADO EXCEL COM MACRO

78
Tabela - Cabeçalho
Tabela - Cabeçalho

Neste artigo você aprenderá como utilizar o filtro avançado, e mais, como utilizá-lo com uma macro de Excel para tornar o filtro automático.

Para isso vamos conhecer como funciona o filtro avançado do Excel:

1. Faça download do arquivo base clicando no botão abaixo:

2. Com o arquivo que você baixou copie o cabeçalho da lista a partir da célula H1.

Tabela - Cabeçalho
Tabela – Cabeçalho

3. Para criar um filtro avançado você deve saber que:

  • A planilha deve possuir uma lista com cabeçalhos, esta lista é a fonte aonde serão aplicados os filtros;
  • Deve haver uma parte da planilha com os mesmos cabeçalhos, os cabeçalhos devem ser exatamente iguais, pois é abaixo destes campos que você colocará os critérios, que podem ser fórmulas que retornem valores da lista, pode ser valores utilizando os indicadores >, <, <>, =, como por exemplo >1.

4.Digite na célula H2 a palavra João,  na coluna valor da célula L2 digite >100.

5.Clique na guia Dados e no botão Avançado.

6.Na tela que segue, você terá a parametrização do filtro avançado.

  • No primeiro campo Intervalo da lista,  selecione a lista de dados: $A$1:$F$831
  • No segundo campo, Intervalo de critérios, selecione o intervalo de critérios com o cabeçalho: $H$1:$M$2
  • Como queremos que  a lista seja filtrada em um outro local clique na ação Copiar para outro local
  • No campo Copiar para selecione o intervalo $H$7:$M$7

7.Clique no botão Ok, note que os dados foram filtrados logo abaixo do filtro de dados conforme especificado.

Pronto! Terminamos a a primeira parte do tutorial, entendendo como funciona o Filtro Avançado.

O problema é que sempre que você for filtrar terá que repetir estas ações.

Para resolver este problema vamos fazer uma macro para gravar as ações de filtragem de dados.

Criando uma macro para automatizar o filtro avançado

  • Clique na guia Desenvolvedor
  • Clique no botão Gravar Macro
  • Digite no campo Nome da Macro Filtrar, na tecla de atalho digite A e em Armazenar Macro em: selecione Esta pasta de trabalho.

  • Clique em OK. Cuidado, a partir deste momento todas as operações que você fizer serão gravadas, por isso não clique em qualquer lugar na planilha.
  • Clique em uma célula da lista, por exemplo F1 e clique na guia Dados em Avançado
  • Verifique se o intervalo de dados selecionado corresponde á $A$1:$F$831, senão corrija-o.
  • No intervalo de critérios verifique se está apontando para $H$1:$M$2, senão corrija.
  • Marque novamente a opção Copiar para outro local e verifique se o intervalo é $H$7:$M$7.
  • Clique em OK.
  • Volte na guia Desenvolvedor e clique em Parar Gravação.
  • Agora clique na guia Inserir e selecione o botão Formas e escolha a forma de Retângulo e desenhe-a ao lado dos critérios, mais ou menos á partir da coluna N.
  • Selecione a forma criada e Digite Filtrar.
  • Clique com o botão direito sobre o retângulo e selecione a opção Atribuir Macro.
  • Na janela que segue selecione a macro criada que tem o nome Filtrar.

Pronto! Para testar altere alguma propriedade do filtro, como por exemplo digite Ana no lugar de José e clique no botão Filtrar.

Faça o download do arquivo clicando no botão abaixo:

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

78 COMENTÁRIOS

  1. meu grande!!! bm dia!! esse cédigo é show.. mais… estou com uma necessidade!! estou querendo filtrar dentro de um intervalode datas, nessa mesma sua planilha.. teria como construirmos outro critério para criarmos outro campo de datas? comto com seu apoio!!!!!

  2. MArcos Riper grande tutorial, Otimo mesmo, uma duvida que surgiu e ;

    Consegue colocar doi criterios de data na pesquisa ?

    Ex: >=01/02/2011 até <=10/02/2011

    NOTA FABRICANTE Data
    10267 SIMM DO BRASIL 01/02/2011
    10268 SIMM DO BRASIL 02/02/2011
    10256 SIMM DO BRASIL 03/02/2011
    10263 SIMM DO BRASIL 04/02/2011
    47677 NOKIA DO BRASIL TECNOLOGIA LTDA 05/02/2011
    10292 SIMM DO BRASIL 06/02/2011
    10286 SIMM DO BRASIL 07/02/2011
    10282 SIMM DO BRASIL 08/02/2011
    10283 SIMM DO BRASIL 09/02/2011
    10293 SIMM DO BRASIL 10/02/2011

  3. Rieper obrigado pela resposta, tem alguma forma de colocar esta pesquisa entre datas no formato que utilizamos >= 01/12/2010 ? pois a planilha usam varias pessoas e pode confundir na utilização. e possivel que esta busca seja feita com varias Notas ao mesmo tempo.

    EX. Notas
    12
    13
    14
    e me retornar todas elas?

    E me retornar o intervalo das datas tambem ?

    EX :

    Intervalo entre >01/02/2010 e <10/02/2010 numa unica pesquisa

    • Boa noite Wilson,

      Você pode ocultar a coluna na qual ele efetua o filtro, digitar o valor em outra coluna e utilizar a concatenação pra chegar neste resultado.

      Exemplo: =”>=”&MÊS(A2)&”/”&DIA(A2)&”/”&ANO(A2)

      Abraço

      Marcos Rieper

  4. Boa tarde Marcos,

    por favor, eu gostaria de fazer com que o banco de dados ficasse em uma aba, e a filtragem e resultado em outra, assim o usuario so vai ver o resultado que quer e nao todos os dados juntos. Entende?

    Obrigado!

  5. O questionamento do Neto do dia 23/02/11, sobre o banco de dados ficar em uma aba e a filtragem em outra, seria possível nos mostrar como pode ser feito? Por favor.

    Obrigado!

  6. Marcos,

    Neste filtros, há como colocar um “listbox”….. estilo dos próprios filtros do excel….. mas buscando a informação do banco de dados para os filtros?

  7. Estou com uma que talvez pudessem me auxiliar, estou utilizando filtro em minhas colunas para selecionar fornecedores, acontece que já tenho mais de 40 e meu filtro só está funcionando até a linha 26. Sabe como expardir a abrangência, fazendo assim ir até o final da tabela??

    Grato pelo auxílio.

  8. Marcos boa tarde!
    tem como padronizar para que o filtro selecione apenas dados iguais e não que contenham? Por ex: Quero selecionar apenas o nome Maria das Dores, mas o filtro me traz todos os que contém Maria….

  9. ola pessoal, eu estou precizando d um favor, essa mesma macro que foi feita para auto filtro, eu queria que ela pesqueza-se os nomes mas tino, eu quero acha todos os dados que contenha a letra “na” na coluna “a”, dessa forma que esta eu só conseguigo fazer o filtro com o nome inteiro….
    por favro desde ja agradeço…

  10. já utilizei o filtro avançado e é ótimo. gostaria de saber se a partir destes dados filtrados como posso proceder a alterações, isto é: filtro o nome de uma pessoa por ex: Fernado e verifico que está mal escrito. Há forma de alterar o dado da filtragem e alterar a sua origem?

    Um abraço

  11. Cara, nunca tinha comentado em um “tutorial” eu estava quebrando a cabeça aqui para lembrar como se fazia este tipo de trabalho, você ensinou de uma forma simples, clara e objetiva. PArabens!!!

  12. marcos, boa tarde.
    Neste filtro, eu utilizo bastante na empresa, mas surgiu a necessidade de filtrar entre diferente “planilhas” (abas)
    é possivel utilizano o filtro avançado? seria uma planilha apra cada dia , exemplo 01..02…03…04…05…06…etc…
    E no filtro deveria varrer todas elas….

  13. Caro Marcos; muito útil a apresentação desse tópido, o que eu gostaria de saber se é possível fazer a utilização do filtro com apenas uma parte do texto; ou seja, se digitei “jo”, foi me apresentado “josé” e “joão”; mas se digito “sé” ou “ão”; não é apresentado nenhum resultado, isso seria possível de ser apresentado. Antecipadamente, obrigado!

  14. Boa noite Mestre. Valeu a dica; estou usando essa função com um arquivo Diário onde possuo colunas com datas, valores, históricos, códigos de contas em alfa-numérico. E sua colocação para minha dúvida foi muito útil. Obrigado!

  15. Caro Marcos, primeiro parabéns pelo tutorial, show de bola, me ajudou muito. Estou com uma dúvida, e acho que é simples, não estou conseguindo colocar o parâmetro para filtrar um intervalo, ou seja, quero filtrar entre >1 e 1 na linha superior e <=9 na linha inferior, conforme demonstrou acima com data, porém não funciona. Pode me explicar por favor como eu faria? Obrigado.

  16. Marcos sobre sua resposta abaixo você teria um exemplo para fornecer?

    Bom dia Luciano,

    Acredito que dá pra fazer sem problemas, só alterando o objeto para combobox, alterando o intervalo de entrada e o vínculo da célula e adaptando o código fonte.

    Abraço

    Marcos Rieper

  17. Oi Rieper,

    Parabéns pelo artigo e principalmente por disseminar o conhecimento.

    Uma dúvida… existe como fazer filtro apenas por parte de palavras, por exemplo, na tua planilha tu tens “João” e “José”, seria possível achar todos que contenham “Jo” no vendedor?

    Obrigado, abraço

    Daniel

    • Bom dia Fábia,

      Tem como fazer de forma a ficar conforme o do artigo, mas demandaria programação VBA, no entanto você pode usar o filtro do campo Filtro de relatório da tabela dinâmica um filtro bastante completo.

      Abraço

      Marcos Rieper

  18. estou tentando fazer um filtro avançado onde, a mesma celula contem data e hora ex:
    Manifest Date Manifest Date
    >=2012-04-30 09:25:16 <=2012-05-01 5:00
    se alguem puder me ajudar…..

  19. Olá,pessoal
    estou tentando fazer um filtro automatico em uma planilha com tabela dinamica,na planilha tem uma tabela onde eu quero colocar um campo para você digitar um nome dos relacionados e quando você digitar o nome aparecetodos os dados do mesmo e se eu quiser ver todos é só digitar todos.e agora pessoal como faço???por favor me ajuda…
    No aguardo.

  20. Boa-noite
    Marcos,

    Eu usei modelo que você disse e eu tenho um total de 5 colunas,porém a coluna com os nomes não realiza o filtro e a caixa de texto fica meio riscada e me apresenta um erro 1004.
    Te enviei a planilha pelo seu e-mail do gmail,você pode por favor me ajudar por favor.

    Grata
    No aguardo.

  21. Olá Pessoal, bom dia!

    emcima deste filtro, sera que existe alguma possibilidade de fazer alteração?? Ex. no filtro puxei um determinado produdo com X qnd., e alterar essa qnt, e na proxima consulta q eu fazer esta com a qnt que eu alterei.

    Grato..

  22. Amigo,
    eu to precisando criar uma filtragem do tipo que eu selecione o nome da empresa em cima e automaticamente embaixo em uma caixa de texto traga o nome fantasia da mesma…
    outro exemplo..
    seleciono em cima o nome da pessoa e embaixo ja da a data do aniversario dela… tem como?
    abraçao

  23. Boa noite
    Como faço para criar pu utilizar algum comando, em uma lista de aniversário e preciso somente os aniversarientes do mês de agosto, como exemplo;
    Aguardo resposta
    OBS : sem usar filtro ou tabela dinâmica.

  24. Criei uma planilha com 4 colunas: Classificação, código, quantidade e Descrição. O filtro é apenas para trazer as quantidades maiores que 1. Quando aplico o filtro avançado, ele funciona perfeitamente, mas quando tento gravar a Macro, executando exatamente o mesmo procedimento, ela não funciona.
    Segui as instruções do tópico, e não consegui fazer a macro funcionar. Pode me ajudar? Grato!

  25. Muito boa esta dica. Parabéns Rieper, você mostrou que conhece muito mesmo!. Você por acaso sabe como fazer para incluir no filtro avançado o comentário incluso na célula e aparecer no local indicado?

  26. Consegui fazer e funcionou perfeitamente.
    Porém o problema é outro, na seleção do intervalo de informações, é algo fixo, ou seja, toda vez que for acrescentado um cadastro terei que refazer todo processo?

  27. Faço das palavras do Herbert (14/12/2012 às 13:55) as minhas.
    A sua didática e conhecimento é infinitamente melhor e maior, respectivamente, que as dos “mestres” da Microsoft.
    Cordialmente,

  28. Muito bom, obrigado!!

    No meu caso, um dos parâmetros seria o destino dos dados. Pra resolver isso, bastou abrir o código da macro e mudar o destino para uma variável.

    Me ajudou muito, vou continuar acessando o site sempre!

  29. Primeiro parabéns pelo post! Tenho um “problema” o filtro funciona muito bem. Entretanto, gostaria de editar os valores e colocá-los devolta na mesma posição de linha dentro do banco.
    Ou ainda, identificá-los, “jogá-los”de volta para as células que utilizei para cadastrá-los e por fim salvá-los na mesma posição. Tem alguma sugestão?

    Obrigado!

  30. Estou precisando de extrair dados de uma planilha que some um determinado valor. por exemplo quero extrair vários valores que a soma seja 52.250. Esta informação seria de grande utilidade para conciliação de valores.
    alguem consegue me ajudar ?

  31. Olá!

    A macro para o executar o filtro avançado está rodando,
    porem qdo a base de dados do filtro é muito grande, o filtro é feito mas na sequência qdo a macro copia e tenta colar em outra planilha, a base é colada com todas informações sem o devido filtro… não sei como resolver isso, alguém pode ajudar?

    Grato

  32. Grande dica, Marcos..eu queria saber como fazer para buscar com relação a datas apenas por mês, independente de ano. Ex: uma lista de nomes com datas de nascimento. Eu queria filtrar apenas os que nasceram no mês X. Tem como?

    Obrigado,

    Ueritom

    • Boa noite Herika,

      O filtro avançado somente funciona para uma planilha de cada vez, neste caso você precisaria criar um procedimento VBA específico para o seu caso que realizasse os filtros em cada uma das planilhas e transportasse os resultados dos filtros para esta planilha consolidadora.

      Abraço

      Marcos Rieper

  33. Bom dia Marcos,

    Estou precisando fazer um filtro em uma macro, mas preciso indicar o que não deve filtrar. Ex: Não quero que mostre: “.”, “-“,”0” e mostre apenas informações válidas, como “Jose”, “Maria”.

    Tenho uma planilha com 6 abas e o filtro deve ser feito em todas elas, mas quero um só comando.

    Deu pra entender?

    Obrigada,
    Abçs,

  34. Bom dia,

    Como faço para filtrar linhas, com base no seu conteúdo, usando caracteres globais ???

    Exemplo:

    Critério passando: *lote*
    Linhas Retornadas: Loteamento, Construção de Lote, Novo Lote…

    Obrigado pela força.

  35. Bom dia Marcos!
    gostaria muito de conseguir fazer um filtro como uma opção de busca em uma agenda telefonica, tenho abas de a, b, c, …. é queria criar uma Aba de busca para filtrar somente o nome que procuraria….isso tem como fazer?

  36. Marcos,

    minha questão é outra, eu tenho um conjunto de CNPJs diferentes e aleatorios (em torno de 400), que gostaria de fazer um filtro em uma tabela dinâmica destes 400 CNPJs ao mesmo tempo, tem como fazer isto sem ser manualmente?

    Obrigado!

Faça umcomentário