Aprenda a criar uma lista suspensa pesquisável no Excel! Crie uma lista suspensa que se atualiza automaticamente e filtra os resultados para você.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:
Na aula de hoje eu quero te mostrar como criar uma lista suspensa pesquisável no Excel. A ideia é que, à medida que você digita, a lista filtre os resultados com base no que você já escreveu na célula.
Dessa forma você terá uma lista suspensa só que com um filtro para pesquisar de forma rápida e prática, sem precisar percorrer todos os valores presentes na lista.
Para isso veremos como utilizar a validação de dados, a fórmula DESLOC no Excel e a função CONT.VALORES.
Faça o download do material disponível e vamos aprender como criar essa lista suspensa pesquisável no Excel!
Para criar a nossa lista suspensa no Excel, vamos utilizar um recurso chamado de Validação de Dados.
A Validação de Dados é uma ferramenta do Excel que nos permite verificar uma célula ou conjunto de células, definindo e restringindo os tipos de informações aceitas nessas células.
Isso nos dá maior controle sobre os dados inseridos na planilha, evitando que o usuário insira informações não permitidas, o que poderia resultar em erros.
Dentro da Validação de Dados temos diversas opções de restrição e controle, entre elas temos a opção de Lista.
Para criar nossa lista, vamos selecionar toda a coluna de Funcionário na nossa tabela, ir até a guia Dados e selecionar a Validação de Dados.
Na janela que se abrirá, escolheremos o tipo de validação Lista. Em Fonte, definiremos os funcionários cadastrados na tabela ao lado, registrados na coluna Nome.
Dessa forma, na nossa coluna Funcionário, teremos um menu suspenso na célula. Ao clicar nele, será exibida uma lista com os funcionários registrados.
E caso você tente digitar um nome nessa coluna que não esteja na lista, o Excel mostrará uma mensagem de erro.
Para tornar sua lista suspensa pesquisável, ou seja, para que ela filtre automaticamente os nomes que começam com a letra ou sílaba que você digitar, é necessário que você esteja usando a versão mais recente do Excel (o Microsoft 365).
Observe que ao digitar “Ca”, o Excel já filtra os funcionários que comecem o nome com “Ca”, isso facilita bastante a busca em listas muito extensas. Tornando sua pesquisa mais rápida e eficiente. E a partir do Excel mais recente, essa é uma função nativa das listas suspensas.
Uma segunda funcionalidade que podemos adicionar à nossa lista é fazer com que, ao inserir um novo nome na primeira tabela da qual estamos validando os dados, esse nome seja adicionado automaticamente à nossa lista suspensa na segunda tabela.
Existem algumas maneiras de fazer isso, mas a mais prática e comumente usada pela sua conveniência é selecionar toda a coluna Nome ao definir a Fonte na Validação de Dados. No entanto, esse método fará com que a própria palavra ‘Nome’ faça parte da sua lista e que sempre haja uma linha em branco sendo exibida.
Para evitar isso e criar uma lista suspensa automática mais refinada, usaremos as funções DESLOC e CONT.VALORES no Excel.
Para fazer isso, selecione novamente nossa coluna Funcionário e vá para a opção Validação de Dados. No lugar da Fonte, insira a seguinte fórmula:
=DESLOC($A$1;1;0;CONT.VALORES(A:A)-1)Essa fórmula se baseia na função DESLOC do Excel, que desloca uma referência de célula de acordo com os argumentos fornecidos.
Então, estamos passando a célula A1 trancada ($A$1) para garantir que a célula de referência inicial seja sempre a célula A1, que é o início da coluna Nome de onde queremos obter os nomes dos funcionários.
Em seguida, usamos o número 1 como argumento, indicando o deslocamento de linha. Neste caso, movemos a referência uma linha para baixo, ou seja, a referência começa na célula A2.
O terceiro argumento indica o número de colunas a serem deslocadas. Como desejamos pegar os nomes na coluna ‘Nome’, usamos 0 para esse argumento, garantindo que não haja deslocamento horizontal para outras colunas.
Finalmente, o quarto argumento para a função DESLOC é a altura, que especifica quantas linhas devem ser incluídas no intervalo resultante. Por exemplo, se colocássemos o número 5, a fórmula só consideraria como intervalo os nomes de Fred até Marcelle.
Como queremos que nossa lista seja atualizada automaticamente, incluindo os nomes novos que adicionarmos na coluna Nome, a altura precisa ser calculada com base na quantidade de nomes na coluna.
Por isso, usamos a função CONT.VALORES(A:A) como argumento de altura, que conta quantas células na coluna A contêm valores, subtraindo 1 do resultado para excluir o cabeçalho onde está escrito Nome.
Portanto, passando essa fórmula para a Fonte da Validação de Dados teremos uma lista suspensa pesquisável no Excel que atualiza automaticamente.
Dessa forma, se adicionarmos novos nomes na primeira coluna, teremos esses nomes sendo exibidos na nossa lista suspensa.
E se algum funcionário for removido da coluna Nome, ele também não aparecerá mais na nossa lista.
Na aula de hoje, mostrei como criar uma lista suspensa pesquisável no Excel que se atualiza automaticamente. Dessa forma, você terá uma lista que valida apenas os nomes presentes na sua tabela de referência e filtra os resultados para uma pesquisa rápida e prática.
Você também aprendeu como usar a função DESLOC e CONT.VALORES no Excel para construir essa validação de dados mais inteligente e automática.
Apesar da função de pesquisa estar disponível apenas a partir da versão mais recente do Excel (o Microsoft 365), você pode aplicar os conhecimentos desta aula para adaptar as validações de dados em suas planilhas de acordo com a sua necessidade e realidade.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Básico, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador