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)
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.
- A pesquisa é iniciada a partir da primeira letra de cada palavra na lista suspensa. Se você inserir um caractere que não corresponda à primeira letra de qualquer palavra, a lista não exibirá itens correspondentes.
- Este recurso está disponível apenas na versão mais recente do Excel 365.
- Se sua versão do Excel não suporta este recurso, aqui recomendamos o recurso Lista Suspensa Pesquisável do Kutools for Excel. Não há limitação de versão do Excel, e uma vez ativado, você pode facilmente pesquisar o item desejado na lista suspensa simplesmente digitando o texto relevante. Veja as etapas detalhadas.
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.
- Selecione a primeira célula D1 na coluna D e insira o cabeçalho da coluna, como "Resultados da pesquisa" neste caso.
- Insira a seguinte fórmula na célula D2 e pressione Enter.
=FILTER(A2:A8,ISNUMBER(SEARCH(Planilha2!A2,A2:A8)),"Não Encontrado")
- Nesta fórmula, A2:A8 é o intervalo de dados de origem. Planilha2!A2 é a localização da lista suspensa, o que significa que a lista suspensa está localizada em A2 da Planilha2. Altere-os conforme seus próprios dados.
- Se nenhum item for selecionado na lista suspensa em 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.
Passo 2: Reconfigure a lista suspensa
- 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.
- Na Validação de Dados caixa de diálogo, você precisa configurar conforme segue.
- Sob a aba Configurações clique no
botão na Fonte caixa.
- 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 .
- 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.
- Sob a aba Configurações clique no
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.
- Este método está disponível apenas para o Excel 2019 e versões posteriores.
- Este método funciona apenas em uma célula de lista suspensa de cada vez. Para tornar as listas suspensas pesquisáveis nas células A3 até A8 na Planilha2, as etapas mencionadas 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.
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:
- Selecione o intervalo contendo as listas suspensas que precisam ser definidas como listas suspensas pesquisáveis.
- 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.
- Este recurso suporta pesquisa a partir de qualquer posição dentro das palavras. Isso significa que mesmo que você insira um caractere que esteja no meio ou no final de uma palavra, itens correspondentes ainda serão encontrados e exibidos, oferecendo uma experiência de pesquisa mais abrangente e amigável.
- Para saber mais sobre este recurso, visite esta página.
- Para aplicar este recurso, baixe e instale o Kutools for Excel primeiro.
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.
- Se a Desenvolvedor guia não estiver visível na faixa de opções, você pode habilitar a Desenvolvedor guia conforme segue.
- 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:
- 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.
- 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:
- Após mostrar a guia Desenvolvedor, clique em Desenvolvedor > Inserir > Caixa de Combinação.
- Desenhe uma Caixa de Combinação na planilha, clique com o botão direito nela e selecione Propriedades no menu de contexto.
- Na Propriedades caixa de diálogo, você precisa:
- Selecione Falso no campo AutoWordSelect;
- Especifique uma célula no campo LinkedCell. Neste caso, inserimos A12;
- Selecione 2-fmMatchEntryNone no campo MatchEntry;
- Digite DropDownList no campo ListFillRange;
- Feche a caixa de diálogo Propriedades. Veja a captura de tela:
- Agora desative 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 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:- $A$12 é a célula que você especificou como LinkedCell na etapa 4;
- 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.
- 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),"")
- 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)),"")
- Agora você precisa criar um intervalo nomeado. Por favor, clique em Fórmulas > Definir Nome.
- 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)
- 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.
- 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 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:
Autocompletar ao digitar na lista suspensa do Excel
Se você tem uma lista suspensa de validação de dados com muitos valores, precisa rolar pela lista apenas para encontrar o adequado, ou digitar a palavra inteira diretamente na caixa de listagem. Se houver um método que permita autocompletar ao digitar a primeira letra na lista suspensa, tudo ficará mais fácil. Este tutorial fornece o método para resolver o problema.
Criar lista suspensa de outra pasta de trabalho no Excel
É bastante fácil criar uma lista suspensa de validação de dados entre planilhas dentro de uma pasta de trabalho. Mas se os dados da lista que você precisa para a validação 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.
Criar uma lista suspensa pesquisável no Excel
Para uma lista suspensa com inúmeros valores, encontrar um adequado não é uma tarefa fácil. Anteriormente, introduzimos um método de autocompletar a lista suspensa ao digitar a primeira letra na caixa suspensa. Além da função de autocompletar, você também pode tornar a lista suspensa pesquisável para aumentar a eficiência no trabalho ao encontrar valores adequados na lista suspensa. Para tornar a lista suspensa pesquisável, experimente o método deste tutorial.
Preenchimento automático de outras células ao selecionar valores na lista suspensa do Excel
Digamos que você tenha criado uma lista suspensa com base nos valores no 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 automaticamente preenchidos em uma célula selecionada. Para resolver o problema, os métodos deste tutorial ajudarão.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de 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.