Pesquisar Texto ao Digitar no Excel em Lista de Validação
Neste artigo você aprenderá pesquisar um texto ao digitar no Excel em uma lista de validação de dados.
O objetivo é ao digitar alguns caracteres a lista encontrar as células que contém a sequência de letras e na você poder selecionar na lista o item desejado.
Como visto acima, você digita algumas letras e pressiona Enter ou Ctrl+Enter, para não sair da célula e são listados os dados que contém aquele texto.
Como Criar uma Validação de Dados Pesquisável no Excel
Há mais de uma forma de fazer esta validação de dados pesquisável.
A forma de pesquisa que utilizamos neste artigo é com o Office 365 e as funções de matrizes dinâmicas, principalmente a função FILTRO.
Para versões mais antigas veja como fazer isso com VBA: https://www.guiadoexcel.com.br/planilha-filtro-automatico-excel-ao-digitar/
Criando a base de dados
A base de dados que usaremos na nossa planilha com filtro automático de dados no Excel ao digitar será a de endereços abaixo.
A lista deve estar no formato tabular ou em formato de tabela, conforme ilustramos abaixo.
O nosso objetivo é listar pelas letras digitadas os nomes da coluna Local em uma outra planilha:
Na outra planilha acima, digite os dados conforme o mesmo cabeçalho da anterior, na linha abaixo dela iremos colocar a validação para digitar os dados.
Na planilha de base de dados na célula L9 digitamos =Tabela!B10 que é o endereço da planilha aonde serão listados os dados.
Na planilha com a base de dados inclua a seguinte fórmula:
=CLASSIFICAR(FILTRO(B10:B27;(ÉNÚM(LOCALIZAR(L9;B10:B27)));”NÃO ENCONTRADO”))
A fórmula funciona da seguinte forma:
A função Localizar identifica se o texto contido na célula L9, que está ligada com a célula aonde foi digitado o texto é encontrada em cada uma das palavras do intervalo em B10:B27.
Caso encontre a posição inicial do texto é retornada em formato de número.
Então usamos a função ÉNÚM para identificar se foi retornado um número retornando então uma lista de VERDADEIRO e FALSO.
A função FILTRO é então aplicada no intervalo de B10:B27 retornando os seus correspondentes apenas quando for VERDADEIRO no filtro aplicado.
E por último é realizada a classificação dos dados utilizando a função CLASSIFICAR e caso não encontrada informação é retornado NÃO ENCONTRADO.
Assim é retornada uma lista com os dados desta planilha automaticamente logo abaixo da célula.
Retorne então para a outra planilha, aonde teremos a pesquisa dos dados.
Nela selecione a célula aonde irá adicionar a validação de dados e clique na guia Dados->Validação de dados e selecione a opção Permitir Lista.
Aponte para a célula L10 da outra planilha e digite # ao final no campo Fonte, conforme abaixo.
O # faz com que os dados sejam entendidos como uma matriz de dados, listando assim todos os itens que estiverem no retorno desta fórmula.
Ainda na Validação de dados desmarque a opção Mostrar alerta de erro após a inserção de dados inválidos no Excel.
Esta opção desmarcada faz com que não sejam permitidos quaisquer dados, e como precisamos digitar textos parciais precisamos desabilitar esta opção.
Então agora na célula digite uma parte do texto que deseja pesquisar para que ele complete a validação de dados e pressione CTRL+ENTER e em seguida CTRL+Seta para baixo, ou então ENTER e clique na lista do canto direito.
Veja que os dados agora são listados conforme a informação fórmula da planilha da base dados.
Função Filtro como Procv no Excel
A função Filtro é muito útil realmente.
Conheça mais sobre esta função em: https://www.guiadoexcel.com.br/relatorio-excel-com-matrizes-dinamicas/
Na nossa planilha de validação de dados pesquisável no Excel, nós iremos usar a função filtro para retornar a linha com a empresa selecionada.
Para isso usamos a seguinte fórmula:
=FILTRO(Origem!C10:I27;(Origem!B10:B27=Tabela!B10);””)
Na função Filtro estamos retornando as informações de C10:I27 da nossa base de dados chamada Origem, quando os dados da coluna de nome da empresa B10:B27 for igual ao conteúdo digitado em B10 na nossa tabela.
O resultado é o seguinte:
Ao selecionar o local, automaticamente são retornadas todas as informações pertinentes aos endereços de uma forma automática para a nossa consulta.
Esta solução está limitada a uma linha, mas em breve posto uma adaptação para que possa ser aplicada em mais células.
Download
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:
