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)

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

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")

   

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. Na série 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. 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.
      
   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.

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:

1. 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.
2. 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.

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 Desenvolvedor guia não for exibida na faixa de opções, você pode habilitar a Desenvolvedor 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 Desenvolvedor 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 Desenvolvedor guia, clique em Desenvolvedor > inserção > Caixa combo.
   
3. Desenhe uma caixa de combinação na planilha, clique com o botão direito e selecione Properties no menu do botão direito.
   
4. Na série Properties 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. Tipo DropDownList no ListFillRange campo;
   5. Feche o Properties caixa de diálogo. Veja a imagem:
      
5. Agora desligue o modo de design clicando em Desenvolvedor > 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. 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)
    

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

Artigos relacionados: