O guia definitivo para lista suspensa pesquisável no Excel
A criação de listas suspensas no Excel agiliza a entrada de dados e minimiza erros. Mas com conjuntos de dados maiores, percorrer listas extensas torna-se complicado. Não seria mais fácil simplesmente digitar e localizar rapidamente o seu item? A "lista suspensa pesquisável"oferece essa conveniência. Este guia orientará você através de quatro métodos para configurar essa lista no Excel.
- Lista suspensa pesquisável no Excel 365
- Crie uma lista suspensa pesquisável (para Excel 2019 e posterior)
- Crie uma lista suspensa pesquisável facilmente (para todas as versões do Excel)
- Crie uma lista suspensa pesquisável com caixa de combinação e VBA (mais complexo)
Lista suspensa pesquisável no Excel 365
O Excel 365 introduziu um recurso muito esperado em suas listas suspensas de validação de dados: a capacidade de pesquisar na lista. Com a funcionalidade pesquisável, os usuários podem localizar e selecionar itens rapidamente e de maneira mais eficiente. Após inserir a lista suspensa normalmente, basta clicar em uma célula com uma lista suspensa e começar a digitar. A lista será filtrada instantaneamente para corresponder ao texto digitado.
Neste caso, eu digito San na célula e a lista suspensa filtra as cidades que começam com o termo de pesquisa San, Tais como San Francisco e San Diego. Em seguida, você pode selecionar um resultado com o mouse ou usar as teclas de seta e pressionar Enter.
- O a pesquisa é iniciada a partir da primeira letra de cada palavra na lista suspensa. Se você inserir um caractere que não corresponda ao caractere inicial de nenhuma palavra, a lista não exibirá os itens correspondentes.
- Este recurso está disponível apenas na versão mais recente do Excel 365.
- Se a sua versão do Excel não suporta esse recurso, recomendamos aqui o Lista suspensa pesquisável característica de Kutools for Excel. Não há limitação de versão do Excel e, uma vez ativado, você pode pesquisar facilmente o item desejado na lista suspensa simplesmente digitando o texto relevante. Veja as etapas detalhadas.
Crie uma lista suspensa pesquisável (para Excel 2019 e posterior)
Se você estiver usando o Excel 2019 ou versões posteriores, o método nesta seção também pode ser usado para tornar uma lista suspensa pesquisável no Excel.
Supondo que você tenha criado uma lista suspensa na célula A2 da Planilha2 (imagem à direita) usando dados no intervalo A2:A8 da Planilha1 (imagem à esquerda), siga estas etapas para tornar a lista pesquisável.
Etapa 1. Crie uma coluna auxiliar que liste os itens de pesquisa
Aqui precisamos de uma coluna auxiliar para listar os itens que correspondem aos seus dados de origem. Neste caso, criarei a coluna auxiliar em coluna D of Sheet1.
- Selecione a primeira célula D1 na coluna D e insira o cabeçalho da coluna, como "Procurar Resultados" nesse caso.
- Insira a seguinte fórmula na célula D2 e pressione Entrar.
=FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
- Nesta fórmula, A2: A8 é o intervalo de dados de origem. Folha 2!A2 é o local da lista suspensa, o que significa que a lista suspensa está localizada em A2 da Planilha2. Altere-os de acordo com seus próprios dados.
- Se nenhum item for selecionado na lista suspensa A2 da Planilha2, a fórmula exibirá todos os itens dos dados de origem, conforme mostrado na imagem acima. Por outro lado, se um item for selecionado, D2 exibirá esse item como resultado da fórmula.
Etapa 2: reconfigurar a lista suspensa
- Selecione a célula da lista suspensa (neste caso, seleciono a célula A2 da Planilha2) e vá para selecionar Data > Validação de dados > Validação de dados.
- Na série Validação de dados caixa de diálogo, você precisa configurar como segue.
- Sob o Configurações guia, clique no botão
botão no fonte caixa.
- O Validação de dados caixa de diálogo irá redirecionar para Plan1, selecione a célula (por exemplo, D2) com a fórmula da Etapa 1, adicione um # símbolo e clique no Fechar botão.
- Vou ao Alerta de Erro guia, desmarque o Mostrar alerta de erro após dados inválidos serem inseridos caixa de seleção e, por fim, clique no botão OK para salvar as alterações.
- Sob o Configurações guia, clique no botão
Resultado
A lista suspensa na célula A2 da Planilha2 agora pode ser pesquisada. Digite o texto na célula, clique na seta suspensa para expandir a lista suspensa e você verá a lista filtrada instantaneamente para corresponder ao texto digitado.
- Este método está disponível apenas para Excel 2019 e versões posteriores.
- Este método funciona apenas em uma célula da lista suspensa por vez. Para tornar as listas suspensas pesquisáveis nas células A3 a A8 na Planilha2, as etapas mencionadas acima devem ser repetidas para cada célula.
- Quando você digita texto na célula da lista suspensa, a lista suspensa não se expande automaticamente; você precisa clicar na seta suspensa para expandi-la manualmente.
Crie uma lista suspensa pesquisável facilmente (para todas as versões do Excel)
Dadas as várias limitações dos métodos acima, aqui está uma ferramenta muito eficaz para você - Kutools for Excel's Lista suspensa pesquisável recurso. Este recurso está disponível em todas as versões do Excel e funciona não apenas em todas as listas suspensas dentro de um intervalo especificado, mas também em todas as listas suspensas da planilha ou pasta de trabalho atual, permitindo que você pesquise facilmente os itens desejados em listas suspensas.
Depois de baixando e instalando Kutools for Excel, selecione Kutools > Lista suspensa > Lista suspensa pesquisável para ativar esse recurso. No Lista suspensa pesquisável caixa de diálogo, você precisa:
- Especifique uma opção que você precisa no Definir escopo lista suspensa.
Nesse caso, preciso tornar a lista suspensa em um intervalo especificado pesquisável, então seleciono o Especificar intervalo opção e selecione o intervalo.
- Clique OK.
Resultado
Quando você clica em uma célula da lista suspensa no intervalo especificado, uma caixa de listagem aparece à direita. Digite o texto para filtrar a lista instantaneamente e selecione um item ou use as teclas de seta e pressione Enter para adicioná-lo à célula.
- Este recurso oferece três opções úteis:
- Especificar intervalo: se você selecionar esta opção e especificar um intervalo de células, somente as listas suspensas nesse intervalo especificado poderão ser pesquisadas.
- Livro de trabalho atual: se você selecionar esta opção, todas as listas suspensas na pasta de trabalho atual poderão ser pesquisadas.
- Planilha atual: se você selecionar esta opção, somente as listas suspensas na planilha atual poderão ser pesquisadas.
- Este recurso suporta pesquisando em qualquer posição dentro das palavras. Isso significa que mesmo que você insira um caractere no meio ou no final de uma palavra, os itens correspondentes ainda serão encontrados e exibidos, oferecendo uma experiência de pesquisa mais abrangente e fácil de usar.
- Para saber mais sobre esse recurso, por favor visite esta página.
- Para aplicar este recurso, por favor Baixar e instalar Kutools for Excel em primeiro lugar.
Crie uma lista suspensa pesquisável com caixa de combinação e VBA (mais complexo)
Se você simplesmente deseja criar uma lista suspensa pesquisável sem especificar um tipo específico de lista suspensa. Esta seção fornece uma abordagem alternativa: usar uma caixa de combinação com código VBA para realizar a tarefa.
Suponha que você tenha uma lista de nomes de países na coluna A, conforme mostrado na captura de tela abaixo, e agora deseja usá-los como os dados de origem das listas suspensas de pesquisa, você pode fazer o seguinte para fazê-lo.
Você precisa inserir uma caixa de combinação em vez de uma lista suspensa de validação de dados em sua planilha.
- Se o Desenvolvedor guia não for exibida na faixa de opções, você pode habilitar a Desenvolvedor guia da seguinte forma.
- No Excel 2010 ou versões posteriores, clique em Envie o > Opções. E no Opções do Excel caixa de diálogo, clique em Personalizar Faixa de Opções no painel esquerdo. Vá para a caixa de listagem Personalizar a Faixa de Opções, marque a Desenvolvedor e, em seguida, clique no OK botão. Veja a imagem:
- No Excel 2007, clique em Office botão> Opções do Excel. No Opções do Excel caixa de diálogo, clique em Popular no painel esquerdo, verifique o Mostrar guia Desenvolvedor na Faixa de Opções e, finalmente, clique no OK botão.
- No Excel 2010 ou versões posteriores, clique em Envie o > Opções. E no Opções do Excel caixa de diálogo, clique em Personalizar Faixa de Opções no painel esquerdo. Vá para a caixa de listagem Personalizar a Faixa de Opções, marque a Desenvolvedor e, em seguida, clique no OK botão. Veja a imagem:
- Depois de mostrar o Desenvolvedor guia, clique em Desenvolvedor > inserção > Caixa combo.
- Desenhe uma caixa de combinação na planilha, clique com o botão direito e selecione Properties no menu do botão direito.
- Na série Properties caixa de diálogo, você precisa:
- Selecionar Falso no AutoWord Select campo;
- Especifique uma célula no Célula Vinculada campo. Nesse caso, inserimos A12;
- Selecionar 2-fmMatchEntryNenhum no Entrada de partida campo;
- Tipo DropDownList no ListFillRange campo;
- Feche o Properties caixa de diálogo. Veja a imagem:
- Agora desligue o modo de design clicando em Desenvolvedor > Modo de design.
- Selecione uma célula em branco como C2, insira a fórmula abaixo e pressione Entrar. Eles arrastam o identificador de preenchimento automático para a célula C9 para preencher automaticamente as células com a mesma fórmula. Veja captura de tela:
=--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
Notas:- $ A $ 12 é a célula que você especificou como o Célula Vinculada na etapa 4;
- Depois de concluir as etapas acima, agora você pode testar: insira uma letra C na caixa de combinação e verá que as células da fórmula que fazem referência às células que contêm o caractere C são preenchidas com o número 1.
- Selecione a célula D2, insira a fórmula abaixo e pressione Entrar. Em seguida, arraste sua alça de preenchimento automático para a célula D9.
=IF(C2=1,COUNTIF($C$2:C2,1),"")
- Selecione a célula E2, insira a fórmula abaixo e pressione Entrar. Em seguida, arraste sua alça de preenchimento automático para E9 para aplicar a mesma fórmula.
=IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
- Agora você precisa criar um intervalo de nomes. Por favor clique Fórmula > Definir o Nome.
- Na série Novo nome caixa de diálogo, digite DropDownList no Nome caixa, digite a fórmula abaixo na Refere-se a e, em seguida, clique no OK botão.
=$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
- Agora, ative o modo de design clicando em Desenvolvedor > Modo de design. Em seguida, clique duas vezes na caixa Combo para abrir a Microsoft Visual Basic para Aplicações janela.
- Copie e cole o código VBA abaixo no editor de código.
Código VBA: tornar a lista suspensa pesquisável
Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub
- Pressione o botão outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.
A partir de agora, quando um caractere for inserido na caixa de combinação, ele fará uma pesquisa difusa e listará os valores relevantes na lista.
Artigos relacionados:
Preenchimento automático ao digitar na lista suspensa do Excel
Se você tiver uma lista suspensa de validação de dados com valores grandes, precisará rolar para baixo na lista apenas para encontrar o apropriado ou digitar a palavra inteira diretamente na caixa de listagem. Se houver um método para permitir o preenchimento automático ao digitar a primeira letra da lista suspensa, tudo ficará mais fácil. Este tutorial fornece o método para resolver o problema.
Crie uma lista suspensa de outra pasta de trabalho no Excel
É muito fácil criar uma lista suspensa de validação de dados entre as planilhas de uma pasta de trabalho. Mas se os dados da lista de que você precisa para a validação de dados estiverem em outra pasta de trabalho, o que você faria? Neste tutorial, você aprenderá como criar uma lista suspensa de outra pasta de trabalho no Excel em detalhes.
Crie uma lista suspensa pesquisável no Excel
Para uma lista suspensa com vários valores, encontrar um adequado não é um trabalho fácil. Anteriormente, introduzimos um método de preenchimento automático da lista suspensa ao inserir a primeira letra na caixa suspensa. Além da função de preenchimento automático, você também pode tornar a lista suspensa pesquisável para aumentar a eficiência de trabalho ao encontrar os valores adequados na lista suspensa. Para tornar a lista suspensa pesquisável, tente o método neste tutorial.
Preencher automaticamente outras células ao selecionar valores na lista suspensa do Excel
Digamos que você tenha criado uma lista suspensa com base nos valores do intervalo de células B8: B14. Ao selecionar qualquer valor na lista suspensa, você deseja que os valores correspondentes no intervalo de células C8: C14 sejam preenchidos automaticamente em uma célula selecionada. Para resolver o problema, os métodos neste tutorial vão lhe fazer um favor.
As melhores ferramentas de produtividade para escritório
Kutools for Excel - Ajuda você a se destacar da multidão
Kutools for Excel Possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...

Office Tab - Ativar leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.
