Filtro automático com data no Excel matricial

Cálculo de índices de liquidez Excel
Cálculo de índices de liquidez Excel
30 de abril de 2015
Lista de validação de dados Excel com autocompletar VBA
Lista de validação de dados Excel com autocompletar VBA
2 de maio de 2015

Objetivo: Demonstrar como utilizar o filtro automático com data no Excel utilizando fórmulas Índice, Corresp, Menor, SE e matricial.

Filtro automático com data Excel

O problema que este exemplo busca solucionar é a criação de uma lista filtrada a partir do seu vencimento.

Temos uma lista completa com todos os dados, por exemplo dados de vendas:

Filtro automático com data Excel 2

Note que a coluna Vencimento não está em ordem, o que é normal e não tem problemas para o nosso caso.

Precisaremos apenas que tenhamos uma coluna com uma chave única e numérica, no nosso caso esta chave é a coluna Nota fiscal.

Sendo assim criamos uma estrutura com as mesmas colunas da lista mestre, aonde serão filtrados os dados e incluímos o campo de filtro, no caso Vencimento.

A primeira função que colocamos é no campo Nota fiscal:

{=SEERRO(MENOR(SE(Plan1!$F$2:$F$831=Plan2!$B$1;Plan1!$D$2:$D$831);LIN()-3);””)}

Perceba que a função possui {}, o que significa que é uma função matricial *.

* Funções matriciais: São funções que realizam filtros em listas de dados antes de executar as funções, exemplo, temos uma lista com várias datas, como é o nosso exemplo, e queremos que seja executado determinado conjunto de funções do Excel com base nesta lista.

No nosso exemplo o filtro de dados da lista matricial é: SE(Plan1!$F$2:$F$831=Plan2!$B$1;Plan1!$D$2:$D$831).

A parte SE(Plan1!$F$2:$F$831=Plan2!$B$1;Plan1!$D$2:$D$831) filtra a lista de dados de F2 á F831 somente quando o valor for igual á B1, que é a data definida para o filtro, o retorno será a lista D2 á D831 correspondente, ou seja, se na célula F5 houver o mesmo valor que em B1, então será retornado D5 e assim por diante.

Entendido isto, utilizamos a função MENOR, que retorna o menor X valor de uma lista.

Exemplo: =MENOR(A1:A5; 3), trará o 3.º menor valor da lista de A1 á A5, no nosso caso utilizamos a expressão LIN()-3, que retorna o número da linha -3, exemplo na linha 4 retorna 1, ou seja o primeiro menor, na linha 5 retorna 2, ou seja, o segundo menor, e assim por diante.

E por último utilizamos a função SEERRO, que se o valor não for encontrado na lista do Excel, ele realiza o retorno de um valor default, no caso “”.

Por último, quando entrar a função você deve pressionar CTRL+ENTER, para que o Excel entenda que se trata de uma função matricial.

Para as outras colunas é mais simples, foi utilizado a função ÍNDICE, CORRESP, veja neste artigo uma explicação completa de como utilizar: http://guiadoexcel.com.br/indice-corresp.

Veja aqui como ficou o nosso relatório com filtro automático. Assim que for digitada uma nova data no campo de filtro de data, todos os campos são alterados automaticamente e em ordem de número de nota fiscal.

Apenas um último aviso, não utilize a função matricial em larga escala, pois ela,  assim como índice, corresp, procv e proch, são muito pesadas, então use com consciência, e depois teste se a sua planilha não ficou muito lenta.

Faça o download do nosso exemplo clicando no botão abaixo da imagem.

Filtro automático com data Excel matricial 3

GUT PPT

Abraço

Marcos Rieper


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/

7 Comentários

  1. Pablo disse:

    Muito bem explicado seus trabalhos, admiro muito sua pratica com a ferramenta mais cobiçada do mundo.

  2. Luiz Kroetz disse:

    Muito bom o seu trabalho. Gostaria de saber se existe um limite de linhas para o filtro, pois uso uma planilha e a partir de uma determinada linha não está filtrando mais.
    Abraços

    • Marcos Rieper disse:

      Bom dia Luiz,

      É estranho, deveria funcionar para qualquer tamanho de lista de dados, dado que é o próprio filtro do Excel que é utilizado.

      Você pode enviar um exemplo da sua planilha no nosso fórum?

      Abraço

      Marcos Rieper

  3. é possível fazer esta formula retornar todos os vencimento de um mês inteiro?

  4. Guerra disse:

    Como faço para filtrar um intervalo de datas usando esse critério?

    Obrigado,

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.