Filtro automático com data no Excel matricial

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.