Objetivo: Demonstrar como utilizar o filtro automático com data no Excel utilizando fórmulas Índice, Corresp, Menor, SE e matricial.
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:
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.
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:
