Ir para o conteúdo principal

O guia definitivo para lista suspensa pesquisável no Excel

Autor: Siluvia Última modificação: 2024-03-26

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.


Vídeo


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, 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.

Notas:
  • A 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.

  1. Selecione a primeira célula D1 na coluna D e insira o cabeçalho da coluna, como "Procurar Resultados" nesse caso.
  2. Insira a seguinte fórmula na célula D2 e ​​pressione Entrar.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
Notas:
  • 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
  1. 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.
  2. No Validação de dados caixa de diálogo, você precisa configurar como segue.
    1. Sob o Configurações guia, clique no botão botão no fonte caixa.
    2. A 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.
    3. 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.
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.

Notas:
  • 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 Tornar a lista suspensa pesquisável e pop-up automáticorecurso. Este recurso está disponível em todas as versões do Excel e permite pesquisar facilmente o item desejado na lista suspensa com uma configuração simples.

Depois de baixando e instalando Kutools para Excel, selecione Kutools > Lista suspensa > Tornar a lista suspensa pesquisável e pop-up automático para ativar esse recurso. No Torne a lista suspensa pesquisável caixa de diálogo, você precisa:

  1. Selecione o intervalo que contém as listas suspensas que precisam ser definidas como listas suspensas pesquisáveis.
  2. Clique OK para completar as configurações.
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 Entrar para adicioná-lo à célula.

Notas:
  • 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 baixe e instale o Kutools para 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.

  1. Se o Developer guia não for exibida na faixa de opções, você pode habilitar a Developer guia da seguinte forma.
    1. 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 Developer e, em seguida, clique no OK botão. Veja a imagem:
    2. 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.
  2. Depois de mostrar o Developer guia, clique em Developer > inserção > Caixa combo.
  3. Desenhe uma caixa de combinação na planilha, clique com o botão direito e selecione Propriedades no menu do botão direito.
  4. No Propriedades caixa de diálogo, você precisa:
    1. Selecionar Falso no AutoWord Select campo;
    2. Especifique uma célula no Célula Vinculada campo. Nesse caso, inserimos A12;
    3. Selecionar 2-fmMatchEntryNenhum no Entrada de partida campo;
    4. Formato DropDownList no ListFillRange campo;
    5. Feche o Propriedades caixa de diálogo. Veja a imagem:
  5. Agora desligue o modo de design clicando em Developer > Modo de design.
  6. 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:
    1. $ A $ 12 é a célula que você especificou como o Célula Vinculada na etapa 4;
    2. 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.
  7. 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),"")
  8. 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)),"")
  9. Agora você precisa criar um intervalo de nomes. Por favor clique Fórmula > Definir o Nome.
  10. No 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)
    
  11. Agora, ative o modo de design clicando em Developer > Modo de design. Em seguida, clique duas vezes na caixa Combo para abrir a Microsoft Visual Basic para Aplicações janela.
  12. 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
  13. 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.

Note: você precisa salvar esta pasta de trabalho como um arquivo de pasta de trabalho habilitada para macro do Excel para manter o código VBA para uso futuro.

As melhores ferramentas de produtividade para escritório

Kutools para Excel - ajuda você a se destacar na multidão

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas  |  Excluir linhas em branco  |  Combine colunas ou células sem perder dados  |  Rodada sem Fórmula ...
Super VLookup: Vários critérios  |  Valor múltiplo  |  Em várias folhas  |  Pesquisa Difusa...
Av. Lista suspensa: Lista suspensa fácil  |  Lista suspensa de dependentes  |  Lista suspensa de seleção múltipla...
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade  |  Vista de Design  |  Grande Barra de Fórmula  |  Gerenciador de pastas de trabalho e planilhas | Biblioteca (Auto texto)  |  Data Picker  |  Combinar planilhas  |  Criptografar/Descriptografar Células  |  Enviar e-mails por lista  |  Super Filtro  |  Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens ...)  |  50+ de cores Tipos (Gráfico de Gantt ...)  |  Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário ...)  |  19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho ...)  |  12 Conversão Ferramentas (Números para Palavras, Conversão de moedas ...)  |  7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células do Excel ...)  |  ... e mais

Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...

Descrição


Guia Office - Habilite 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.
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Perfect idea for me. But I have a problem with the "ROWS" formula.
I mean point 8.
When I use your formula (in the drop-down list I have nothing entered, as you can see in point 8) in the first cell is "INDIA".
And pick up the cells with the "spilled" error. What I need to change for the formula to work properly.

E1 - India
E2 - #SPILL!
E3 -#SPILL!
E4 - #SPILL!
E5 - #SPILL!
E6 -#SPILL!
E7 - #SPILL!
E8 - India
E9 - Brazil
E10 - Italy
E11 - Japan
E12 - United State
E13 - Francy
E14 - Germany

You also see that there are more poems appearing than yours.
This comment was minimized by the moderator on the site
Hi Przamek PL,
Sory, I cannot reproduce the problem you mentioned. Can you provide us with your data for tesing? If you don't mind, upload your sample file here.
This comment was minimized by the moderator on the site
Thank you for your message.
I was able to run your example correctly.
I have a reflection now ...
How to apply your solution to the UseForm form?

I would like to select a person from the list in the form, then I would have information about the age of this person elsewhere in the form. Such a simple example. Difficult?
This comment was minimized by the moderator on the site
Hi Przemek PF,
This method does not work in UserForm. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Somehow excel will not let me fill in the ListFillRange with ANYTHING. so also not the DropDownList. I did all the steps but am not able to get a flashing cursor and when I type no drop down list appears. any solutions?
This comment was minimized by the moderator on the site
Hi Marloes, This problem can't be solved yet. Make sure the ListFillRange is on the same sheet as your list box. 
This comment was minimized by the moderator on the site
I've just purchased kutools to use this function. Is it possible to have two or more different searchable drop down lists (i.e. referncing different lists of valid entries) on the same sheet?
This comment was minimized by the moderator on the site
Hi Marc,The feature does not support two or more different searchable drop down lists on the same sheet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
how to use this dropdown in vba form any konw please reply
This comment was minimized by the moderator on the site
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
This comment was minimized by the moderator on the site
I have around 80000 data while running excel is hang
This comment was minimized by the moderator on the site
Sir How to use this in excel userform combobox....? plz help
This comment was minimized by the moderator on the site
Hi Sourav Singha,
Can't use it in a userform combobox. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Is there a way to make it call up a hyperlink? My email is
This comment was minimized by the moderator on the site
Hi Josh,
Sorry can;t help you with that yet.
This comment was minimized by the moderator on the site
I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
This comment was minimized by the moderator on the site
Hi Vrezh,
Sorry this kind of problem can't be solved yet. Thank you for your comment.
This comment was minimized by the moderator on the site
How can I use this? I have two problem
1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
I need for I can search with typing too, so simple(not active-x) combo is wrong.

2nd how set padding? - my combo text when I search is not see whole because itt has padding.

3th if my source is C column, how drop empty elements from list
This comment was minimized by the moderator on the site
Hi Steve Olah,
Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations