O guia definitivo para lista suspensa pesquisável no Excel

Criar listas suspensas no Excel simplifica a entrada de dados e minimiza erros. No entanto, com conjuntos de dados maiores, rolar por listas longas pode se tornar complicado. Não seria mais fácil apenas digitar e localizar rapidamente o item desejado? Uma "lista suspensa pesquisável" oferece essa conveniência. Este guia mostrará quatro métodos para configurar tal lista no Excel.

• Lista suspensa pesquisável no Excel 365
• Criar lista suspensa pesquisável (para Excel 2019 e posterior)
• Criar lista suspensa pesquisável facilmente (para todas as versões do Excel)
• Criar lista suspensa pesquisável com Caixa de Combinação e VBA (mais complexo)

Vídeo: Criar lista suspensa pesquisável

Lista suspensa pesquisável no Excel 365

O Excel 365 introduziu um recurso muito aguardado em suas listas suspensas de validação de dados: a capacidade de pesquisar dentro da lista. Com a funcionalidade de pesquisa, os usuários podem localizar e selecionar itens de forma mais eficiente. Após inserir a lista suspensa como de costume, 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, digito San na célula e a lista suspensa filtra cidades que começam com o termo de pesquisa San, como San Francisco e San Diego. Depois, você pode selecionar um resultado com o mouse ou usar as setas direcionais e pressionar Enter.

Criar lista suspensa pesquisável (para Excel 2019 e posterior)

Se você estiver usando o Excel 2019 ou versões posteriores, o método desta seção também pode ser usado para tornar uma lista suspensa pesquisável no Excel.

Assumindo que você criou 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.

Passo 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 na coluna D da Planilha1.

1. Selecione a primeira célula D1 na coluna D e insira o cabeçalho da coluna, como "Resultados da pesquisa" neste caso.
2. Insira a seguinte fórmula na célula D2 e pressione Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Planilha2!A2,A2:A8)),"Não Encontrado")

   

Passo 2: Reconfigure a lista suspensa

1. Selecione a célula da lista suspensa (neste caso, selecione a célula A2 da Planilha2), depois vá para selecionar Dados > Validação de Dados > Validação de Dados.
   
2. Na Validação de Dados caixa de diálogo, você precisa configurar conforme segue.
   1. Sob a aba Configurações clique no botão na Fonte caixa.
      
   2. A caixa de diálogo Validação de Dados redirecionará para a Planilha1, selecione a célula (por exemplo, D2) com a fórmula do Passo 1, adicione um símbolo # e clique no botão Fechar .
      
   3. Vá para a aba Alerta de Erro, desmarque a caixa Exibir alerta de erro após a entrada de dados inválidos e finalmente clique no botão OK para salvar as alterações.
      

Resultado

A lista suspensa na célula A2 da Planilha2 agora é pesquisável. Digite 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.

Criar 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 Lista Suspensa Pesquisável, Auto-popup. Este recurso está disponível em todas as versões do Excel e permite que você pesquise facilmente o item desejado na lista suspensa com uma configuração simples.

Após baixar e instalar o Kutools for Excel, selecione Kutools > Lista Suspensa > Tornar Lista Suspensa Pesquisável, Auto-popup para habilitar este recurso. Na caixa de diálogo Tornar a Lista Suspensa Pesquisável, você precisa:

1. Selecione o intervalo contendo as listas suspensas que precisam ser definidas como listas suspensas pesquisáveis.
2. Clique OK para concluir as configurações.
   

Resultado

Ao clicar em uma célula de lista suspensa no intervalo especificado, uma caixa de lista aparece à direita. Digite texto para filtrar a lista instantaneamente, depois selecione um item ou use as teclas de seta e pressione Enter para adicioná-lo à célula.

Criar 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 particular 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 fonte de dados das listas suspensas pesquisáveis, você pode fazer o seguinte para concluí-la.

Você precisa inserir uma Caixa de Combinação em vez de uma lista suspensa de validação de dados na sua planilha.

1. Se a Desenvolvedor guia não estiver visível na faixa de opções, você pode habilitar a Desenvolvedor guia conforme segue.
   1. No Excel 2010 ou versões posteriores, clique em Arquivo > Opções. E na caixa de diálogo Opções do Excel, clique em Personalizar Faixa de Opções no painel esquerdo. Vá para a caixa de listagem Personalizar a Faixa de Opções, marque a caixa Desenvolvedor e depois clique no botão OK. Veja a captura de tela:
      
   2. No Excel 2007, clique no botão Office > Opções do Excel. Na caixa de diálogo Opções do Excel, clique em Popular no painel esquerdo, marque a caixa Mostrar guia Desenvolvedor na Faixa de Opções e finalmente clique no botão OK.
      
2. Após mostrar a guia Desenvolvedor, clique em Desenvolvedor > Inserir > Caixa de Combinação.
   
3. Desenhe uma Caixa de Combinação na planilha, clique com o botão direito nela e selecione Propriedades no menu de contexto.
   
4. Na Propriedades caixa de diálogo, você precisa:
   1. Selecione Falso no campo AutoWordSelect;
   2. Especifique uma célula no campo LinkedCell. Neste caso, inserimos A12;
   3. Selecione 2-fmMatchEntryNone no campo MatchEntry;
   4. Digite DropDownList no campo ListFillRange;
   5. Feche a caixa de diálogo Propriedades. Veja a captura de tela:
      
5. Agora desative 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 Enter. Em seguida, arraste sua Alça de Preenchimento Automático até a célula C9 para preencher automaticamente as células com a mesma fórmula. Veja a captura de tela:

   =--É.NÚM(SEERRO(PROCURAR($A$12,A2,1),""))

   
   Notas:

   1. $A$12 é a célula que você especificou como LinkedCell na etapa 4;
   2. Após concluir as etapas acima, você pode testar agora: digite a letra C na caixa de combinação, e então você verá que as células de fórmula que referenciam as células contendo o caractere C são preenchidas com o número 1.
7. Selecione a célula D2, insira a fórmula abaixo e pressione Enter. Em seguida, arraste sua Alça de Preenchimento Automático até a célula D9.

   =SE(C2=1,CONT.SE($C$2:C2,1),"")

   
8. Selecione a célula E2, insira a fórmula abaixo e pressione Enter. Em seguida, arraste sua Alça de Preenchimento Automático até E9 para aplicar a mesma fórmula.

   =SEERRO(ÍNDICE($A$2:$A$9,CORRESP(LINHAS($D$2:D2),$D$2:$D$9,0)),"")

   
9. Agora você precisa criar um intervalo nomeado. Por favor, clique em Fórmulas > Definir Nome.
   
10. Na Novo Nome caixa de diálogo, digite DropDownList na Nome caixa, insira a fórmula abaixo na Refere-se a caixa, e depois clique no OK botão.

    =$E$2:ÍNDICE($E$2:$E$9,MÁXIMO($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 de Combinação para abrir a janela Microsoft Visual Basic for Applications.
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 as teclas Alt + Q para fechar a janela Microsoft Visual Basic for Applications.

A partir de agora, quando um caractere for inserido na caixa de combinação, ela fará uma pesquisa difusa e, em seguida, listará os valores relevantes na lista.

Artigos relacionados: