O guia definitivo para Tornar lista suspensa pesquisável no Excel

Criar uma lista suspensa no Excel simplifica a introdução de dados e minimiza erros. Mas, com conjuntos de dados maiores, deslocar-se por listas extensas torna-se incómodo. Não seria mais fácil digitar e localizar rapidamente o item pretendido? Uma «Tornar lista suspensa pesquisável» oferece exatamente essa conveniência. Este guia irá orientá-lo através de quatro métodos para configurar essa lista no Excel.

• Tornar lista suspensa pesquisável no Excel 365
• Criar Tornar lista suspensa pesquisável (para Excel 2019 e posteriores)
• Criar Tornar lista suspensa pesquisável facilmente (para todas as versões do Excel)
• Criar Tornar lista suspensa pesquisável com caixa de combinação e VBA (mais complexo)

Vídeo: Criar Tornar lista suspensa pesquisável

Tornar lista suspensa pesquisável no Excel 365

O Excel 365 introduziu uma funcionalidade muito aguardada na validação de dados do tipo Lista suspensa: a possibilidade de pesquisar diretamente dentro da lista. Com esta nova capacidade, os utilizadores conseguem localizar e selecionar itens de forma ainda mais eficiente. Depois de inserir a Lista suspensa como habitualmente, basta clicar numa célula com essa lista e começar a digitar — a lista é imediatamente filtrada para mostrar apenas os itens que correspondem ao texto introduzido.

Neste caso, ao escrever San na célula, a lista suspensa filtra automaticamente as cidades que começam pelo termo de pesquisa San, como San Francisco e San Diego. Depois, pode selecionar um resultado com o rato ou utilizar as teclas de setas e premir Enter.

Criar Tornar lista suspensa pesquisável (para Excel 2019 e versões posteriores)

Se estiver a utilizar o Excel 2019 ou versões posteriores, o método descrito nesta secção também pode ser usado para tornar uma lista suspensa pesquisável no Excel.

Assumindo que criou uma lista suspensa na célula A2 da Folha2 (imagem à direita) com base nos dados do intervalo A2:A8 da Folha1 (imagem à esquerda), siga estes passos para tornar a lista pesquisável.

Passo 1. Crie uma coluna auxiliar com a lista dos Itens de Pesquisa.

Precisamos aqui 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 Folha1.

1. Selecione a primeira célula D1 na coluna D e introduza o cabeçalho da coluna, por exemplo «Resultados da pesquisa» neste caso.
2. Introduza a seguinte fórmula na célula D2 e prima Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

Passo 2: Reconfigure a Lista suspensa

1. Selecione a célula Lista suspensa (neste caso, selecionei a célula A2 da Sheet2) e vá a Dados>Validação de Dados>Validação de Dados.
   
2. Na caixa de diálogo Validação de Dados, deverá configurar da seguinte forma.
   1. No separador Definições, clique no botão na caixa Origem.
      
   2. A caixa de diálogo Validação de Dadosredirecionará para a Folha1; selecione a célula (por exemplo, D2) com a fórmula do Passo 1, adicione o símbolo #e clique no botão Fechar.
      
   3. Aceda ao separador Alerta de Erro, desmarque a caixa de verificação Mostrar alerta de erro após introdução de dados inválidose, por fim, clique no botão OKpara guardar as alterações.
      

Resultado

A lista suspensa na célula A2 da Folha2 é agora pesquisável. Basta digitar texto na célula, clicar na seta suspensa para expandir a lista e verá imediatamente os resultados filtrados com base no que escreveu.

Criar Tornar lista suspensa pesquisável facilmente (para todas as versões do Excel)

Tendo em conta as várias limitações dos métodos acima, apresentamos-lhe uma ferramenta extremamente eficaz – a funcionalidade Kutools para Excel's Tornar Lista suspensa Pesquisável, Pop-up Automático. Disponível em todas as versões do Excel, permite-lhe encontrar facilmente o item pretendido na lista suspensa com uma configuração simples.

Após transferir e instalar o Kutools para Excel, selecione Kutools > Lista suspensa > Tornar Lista suspensa Pesquisável, Pop-up Automático para ativar esta funcionalidade. Na caixa de diálogo Tornar a Lista suspensa Pesquisável, terá de:

1. Selecione o intervalo que contém as listas suspensas que pretende definir como “Tornar lista suspensa pesquisável”.
2. Clique em OKpara concluir as definições.
   

Resultado

Ao clicar numa célula com Lista suspensa no Intervalo limitado, aparece uma caixa de lista à direita. Basta digitar texto para filtrar instantaneamente a lista, depois selecione um item ou use as teclas de setas e prima Enter para adicioná-lo à célula.

Criar Tornar lista suspensa pesquisável com Caixa de Combinação e VBA (mais complexo)

Se pretender criar simplesmente uma lista suspensa pesquisável sem especificar um tipo específico de lista suspensa, esta secção apresenta uma abordagem alternativa: utilizar uma Caixa de Combinação com código VBA para realizar a tarefa.

Suponha que tenha uma lista de nomes de países na coluna A, conforme ilustrado na imagem abaixo, e pretenda utilizá-los como Dados de Origem para a Lista Suspensa Pesquisável. Siga os passos seguintes para alcançar este objetivo.

Terá de inserir uma Caixa de Combinação em vez de utilizar a validação de dados do tipo Lista suspensa na sua folha de cálculo.

1. Se o separador Programadornão estiver visível no Friso, pode ativar o separador Programadorda seguinte forma.
   1. No Excel 2010 ou versões posteriores, clique em Ficheiro > Opções. Na caixa de diálogo Opções do Excel, clique em Personalizar Friso no painel esquerdo. Na lista «Personalizar o Friso», assinale a caixa Programador e, em seguida, clique no botão OK. Veja a imagem:
      
   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, assinale a caixa Mostrar separador Programador na Faixa de Opções e, por fim, clique no botão OK.
      
2. Após exibir o separador Programador, clique em Programador>Inserir>Caixa de Combinação.
   
3. Desenhe uma Caixa de Combinação na folha de cálculo, clique com o botão direito do rato nela e selecione Propriedadesno menu de contexto.
   
4. Na caixa de diálogo Propriedades, deverá:
   1. Selecione Falsono campo AutoSeleçãoPalavra;
   2. Especifique uma célula no campo CélulaLigada. Neste caso, introduzimos A12;
   3. Selecione 2-fmMatchEntryNoneno campo EntradaCorrespondência;
   4. Escreva ListaDropdownno campo IntervaloPreenchimentoLista;
   5. Feche a caixa de diálogo Propriedades. Veja a imagem:
      
5. Agora, desative o modo de estrutura clicando em Programador > Modo de Estrutura.
6. Selecione uma célula vazia, como C2, introduza a fórmula abaixo e prima Enter. Em seguida, arraste a Alça de Preenimento Automático até à célula C9 para preencher automaticamente as células com a mesma fórmula. Veja a captura de ecrã:

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   Notas:

   1. $A$12é a célula que especificou como CélulaLigadano passo 4;
   2. Após concluir os passos acima, pode agora testar: introduza a letra C na caixa de combinação e verá que as células com fórmulas que referenciam células contendo o carácter C são preenchidas com o número 1.
7. Selecione a célula D2, introduza a fórmula abaixo e prima Enter. Em seguida, arraste a Alça de Preenimento Automático até à célula D9.

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. Selecione a célula E2, introduza a fórmula abaixo e prima Enter. Em seguida, arraste a Alça de Preenimento Automático até 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, precisa de criar um intervalo com nome. Clique em Fórmulas > Definir Nome.
   
10. Na caixa de diálogo Novo Nome, digite DropDownListna caixa Nome, introduza a fórmula abaixo na caixa Refere-se ae clique no botão OK.

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. Agora, ative o modo de estrutura clicando em Programador > Modo de Estrutura. Em seguida, faça duplo clique na Caixa de Combinação para abrir a janela do Microsoft Visual Basic para Aplicações.
12. Copie e cole o código VBA abaixo no editor de código.
    
    Código VBA: tornar a lista pendente pesquisável

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Prima as teclas Alt + Q para fechar a janela do Microsoft Visual Basic para Aplicações.

A partir de agora, sempre que introduzir um carácter na caixa de combinação, será efetuada uma pesquisa aproximada e os valores relevantes serão listados.

Artigos relacionados: