Filtro ao Digitar Excel – Validação de Dados Lista
Como fazer um filtro ao digitar no Excel utilizando validação dados com lista e a função Filtro.
Esta é uma melhoria do artigo: https://www.guiadoexcel.com.br/pesquisar-texto-ao-digitar-no-excel-em-lista-de-validacao/ aonde mostramos como fazer uma lista pesquisável ao digitar.
No entanto ela era limitada a uma célula, reduzindo assim muito a sua aplicação.
Pensando nisso fiz algumas adaptações e com este ajuste conseguimos retornar para qualquer item de uma lista de validação de dados facilmente.
Estrutura da planilha
A estrutura da planilha é igual a planilha anterior ou muito parecida com qualquer outra que você tenha.
Temos uma base de dados:
E um local aonde queremos retornar itens desta base de dados conforme é realizada uma seleção.
Na lista abaixo o objetivo é que na coluna Local, ao digitar uma parte do texto e dar um Enter ou Ctrl+Enter na célula a mesma ter uma lista com a pesquisa de somente os itens que tem aquele texto digitado.
O objetivo é que ao digitar ele apareça como abaixo, mas em uma lista.
Função Cél para Preencher a Lista de Validação
Para preencher a lista de validação com a pesquisa automática, independente da quantidade de itens utilizamos a função CÉL.
A função Cél retorna informações da célula que acabou de ser alterada.
Podemos retornar diversas informações da célula, inclusive o seu conteúdo.
Pensando nisso usamos a função CÉL da seguinte forma: =CÉL(“conteúdo”).
O resultado disso é que ao alterar qualquer célula da planilha o Excel irá retornar nesta fórmula o conteúdo.
No exemplo que temos, digitamos por exemplo ME, em uma célula e o retorno nesta função CÉL será ME automaticamente.
Fórmula com a Pesquisa Digitada
Para aplicar um filtro e retornar somente os dados que contém o texto digitado usamos a função FILTRO.
A função Filtro permite que sejam realizadas consultas dinâmicas e de uma forma muito prática e facilmente adaptável.
No nosso exemplo usamos a seguinte fórmula:
=CLASSIFICAR(FILTRO($B$10:$B$27;(ÉNÚM(LOCALIZAR(L9;$B$10:$B$27;1)));”SEM DADOS”))
nela utilizamos a função FILTRO para retornar apenas se forem retornados números (posições) nos textos que contenham o conjunto de letras.
Depois disso é feito a classificação com a função Classificar e os dados são retornados para uma lista conforme abaixo:
O Excel retorna uma matriz dinâmica de dados com o resultado de todas as palavras que contém o texto e de forma classificada.
Aplicando o Filtro na Validação de Dados do Excel
Agora na validação de dados você deve selecionar os itens que terão a consulta pesquisável.
Clique em Dados->Validação de dados e desmarque a opção em Alerta de Erro chamada Mostrar alerta de erro após a inserção de dados inválidos.
Esta opção deve estar desmarcada para que possa digitar um texto e não acusar um erro.
Em Configurações, nesta mesma tela, altere Permitir para Lista e altere a Fonte para a primeira célula da fórmula FITLRO que fizemos antes. Aonde tem o retorno dos dados da pesquisa no Excel.
IMPORTANTE: Digite # depois da célula para que este intervalo seja identificado como um intervalo dinâmico, pois caso contrário não irá funcionar.
Resultado
O resultado da planilha é que ao digitar um valor em uma célula da lista da pesquisa o Excel altera o conteúdo da célula com a função CÉL e por conseguinte altera também o resultado da função FILTRO e por fim altera a lista de validação de dados.
No topo deste artigo há também um vídeo mostrando passo-a-passo como implementar um filtro ao digitar na lista de validação de dados do Excel.
Download da Planilha de Filtro ao Digitar
Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
Baixe a planilhaCurso 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:
