Note: The other languages of the website are Google-translated. Back to English

Como criar uma lista suspensa pesquisável no Excel?

Para uma lista suspensa com muitos valores, encontrar um valor adequado na lista não é um trabalho fácil. Anteriormente, introduzimos um método para preencher automaticamente uma lista suspensa quando a primeira letra é inserida. Além da função de preenchimento automático, você também pode tornar a lista suspensa pesquisável para melhorar a eficiência de encontrar o valor apropriado na lista suspensa. Para tornar a lista suspensa pesquisável, você pode seguir os métodos abaixo passo a passo.

Crie uma lista suspensa pesquisável no Excel
Crie facilmente uma lista suspensa pesquisável com uma ferramenta incrível

Mais tutoriais para a lista suspensa ...


Crie uma lista suspensa pesquisável no Excel

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 Populares 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 Propriedades no menu do botão direito.

4. No Propriedades caixa de diálogo, você precisa:

1). Selecione Falso no AutoWord Select campo;
2). Especifique uma célula no Célula Vinculada campo. Nesse caso, inserimos A12;
3). Selecione 2-fmMatchEntryNenhum no MatchEntry campo;
4). Tipo DropDownList no ListFillRange campo;
5). Feche o Propriedades caixa de diálogo. Veja a imagem:

5. Agora desligue o modo de design clicando 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 terminar os passos acima, você pode agora testar: digite uma letra C na caixa de combinação, e então você pode ver que as células da fórmula que referenciam as células contendo o caractere C estã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 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. aperte 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.

Nota: 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.


Crie facilmente uma lista suspensa pesquisável com uma ferramenta incrível

Parece que o método acima não é fácil de manusear. Aqui, recomendo um recurso útil - Lista suspensa pesquisável of Kutools for Excel para ajudar a criar facilmente uma lista suspensa pesquisável no Excel.

Antes de aplicar Kutools for Excel, Por favor baixe e instale primeiro.

Faça o seguinte para criar uma lista suspensa pesquisável com o recurso Lista suspensa pesquisável.

1. Clique Kutools > Lista suspensa > Lista suspensa pesquisável > Habilitar lista suspensa pesquisável.

2. Na abertura Kutools for Excel caixa de diálogo (esta caixa de diálogo só aparece na primeira vez que você aplica este recurso), clique no Sim botão.

3. Em seguida, no Lista suspensa pesquisável caixa de diálogo, você precisa configurar da seguinte maneira.

3.1) No Aplicar a seção, você pode especificar o escopo do intervalo para aplicar este recurso:
A: Faixa (s) especificada (s): Suporta um ou mais intervalos de lista suspensa;
B: Escopo Especificado: Suporte a planilha atual, pasta de trabalho atual ou todas as pastas de trabalho.
3.2) No Opções seção, existem duas opções, você pode escolher uma delas, ambas ou nenhuma delas de acordo com suas necessidades:
A: Corresponde apenas ao início da palavra: Se esta opção estiver marcada, apenas os itens que começam com o caractere digitado são exibidos, entretanto, o primeiro item exibido será preenchido automaticamente; Se esta opção estiver desmarcada, os itens que contêm o caractere digitado serão exibidos;
B: Maiúsculas e minúsculas: Se esta opção estiver marcada, apenas os itens que correspondem às maiúsculas e minúsculas do caractere digitado são exibidos; Se esta opção estiver desmarcada, os itens que contêm o caractere digitado serão exibidos sem distinção entre maiúsculas e minúsculas.
3.3) No Moda seção, escolha o modo para adicionar itens da lista suspensa às células.
A: Acrescentar: Se você escolher esse botão de opção, vários itens pesquisados ​​(incluindo duplicatas) poderão ser adicionados a uma célula. Após adicionar o primeiro item pesquisado a uma célula, se você realizar uma nova pesquisa nessa célula novamente, o novo item pesquisado será adicionado ao final do existente.
Na série separador caixa de texto, insira um separador para separar os itens adicionados;
Na série Direção do texto seção, escolha uma direção para exibir os itens adicionados na célula da lista suspensa.
B: modificar: Se você escolher este botão de opção, o item adicionado posteriormente substituirá o existente. Apenas um item pode ser exibido na célula por vez.
3.3) Clique OK.

4. Em seguida, clique Kutools > Lista suspensa > Lista suspensa pesquisável > Habilitar lista suspensa pesquisável para ativar esse recurso.

Agora a lista suspensa pesquisável foi criada. Ao clicar em uma célula da lista suspensa, uma caixa de listagem aparecerá com todos os itens suspensos listados. Digite um caractere e os itens correspondentes serão pesquisados ​​de uma só vez, e você pode clicar no item necessário para inseri-lo nessa célula.

Se você escolheu Horizontalmente no Direção do texto seção: todos os itens adicionados serão exibidos horizontalmente em uma célula. Veja a demonstração abaixo:

Se você escolheu Verticalmente no Direção do texto seção: todos os itens adicionados serão exibidos verticalmente em uma célula. Veja a demonstração abaixo:

Se você escolheu o modificar botão de opção, apenas um item pode ser exibido em uma célula da lista suspensa por vez. Veja a demonstração abaixo:

Clique para saber mais sobre este recurso.

  Se você quiser ter uma avaliação gratuita (30-dia) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.


Artigos relacionados:

Preenchimento automático ao digitar na lista suspensa do Excel
Se você tiver uma lista suspensa de validação de dados com valores grandes, precisará rolar para baixo na lista apenas para encontrar o apropriado ou digitar a palavra inteira diretamente na caixa de listagem. Se houver um método para permitir o preenchimento automático ao digitar a primeira letra da lista suspensa, tudo ficará mais fácil. Este tutorial fornece o método para resolver o problema.

Crie uma lista suspensa de outra pasta de trabalho no Excel
É muito fácil criar uma lista suspensa de validação de dados entre as planilhas de uma pasta de trabalho. Mas se os dados da lista de que você precisa para a validação de dados 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.

Crie uma lista suspensa pesquisável no Excel
Para uma lista suspensa com vários valores, encontrar um adequado não é um trabalho fácil. Anteriormente, introduzimos um método de preenchimento automático da lista suspensa ao inserir a primeira letra na caixa suspensa. Além da função de preenchimento automático, você também pode tornar a lista suspensa pesquisável para aumentar a eficiência de trabalho ao encontrar os valores adequados na lista suspensa. Para tornar a lista suspensa pesquisável, tente o método neste tutorial.

Preencher automaticamente outras células ao selecionar valores na lista suspensa do Excel
Digamos que você tenha criado uma lista suspensa com base nos valores do 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 preenchidos automaticamente em uma célula selecionada. Para resolver o problema, os métodos neste tutorial vão lhe fazer um favor.

Mais tutorial para lista suspensa ...


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (67)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
muito bem explicado. Amei. Obrigada !!
Este comentário foi feito pelo moderador no site
Excelente postagem. Você poderia explicar como você copia a mesma lista suspensa para várias células. Desejo criar um relatório de despesas e poder selecionar uma despesa diferente em cada linha da mesma lista suspensa. Obrigada.
Este comentário foi feito pelo moderador no site
eu tenho a mesma necessidade
Este comentário foi feito pelo moderador no site
Por algum motivo, quando clico em uma seleção da lista suspensa depois de digitar alguns caracteres, o valor principal suspenso fica em branco ... alguma ideia de por que isso aconteceria e como fazê-lo parar? Eu tenho um botão de comando no qual quero clicar para colocar a seleção na próxima célula disponível em um determinado intervalo, mas novamente o valor fica em branco quando clico nele.
Este comentário foi feito pelo moderador no site
Eu tenho exatamente o mesmo problema. Fiz tudo certo, mas o rótulo da lista suspensa fica em branco toda vez que pressiono enter. Se você descobriu, por favor, compartilhe!
Este comentário foi feito pelo moderador no site
O meu não está funcionando. Meu rótulo de lista suspensa não estava funcionando nas "propriedades" da caixa de combinação. Toda vez que eu entrava, ele desaparecia. Então eu usei "teste" em vez disso. Ajustei a macro com a palavra test em vez de dropdowmlist. Deixe-me saber se há algo mais que eu possa fazer? A pesquisa não está funcionando.
Este comentário foi feito pelo moderador no site
[quote]O meu não está funcionando. Meu rótulo de lista suspensa não estava funcionando nas "propriedades" da caixa de combinação. Toda vez que eu entrava, ele desaparecia. Então eu usei "teste" em vez disso. Ajustei a macro com a palavra test em vez de dropdowmlist. Deixe-me saber se há algo mais que eu possa fazer? A pesquisa não está funcionando.Por imad[/quote] Eu vi isso "como fazer um preenchimento automático / sugestão automática de DDL / caixa de combinação" em alguns sites diferentes e TODOS eles querem que você coloque "algo" no campo ListFillRange ANTES de criar um intervalo de nomes por clicando em Fórmula > Definir nome e ListFillRange sempre ficará em branco na janela Propriedades ATÉ que você defina o nome (Fórmula > Definir Nome) É por isso que acho que o IMAD, acima e o MAARTEN abaixo estava tendo o problema - não 100% de certeza.
Este comentário foi feito pelo moderador no site
Então eu finalmente consegui que funcionasse! Anexei o linkedcell a um vlookup e obtive todas as informações em uma linha. Eu queria saber se poderia haver alguma extensão no vba para realmente filtrar a tabela enquanto digitamos?
Este comentário foi feito pelo moderador no site
Olá, não consigo preencher 'DropDownList' no 'ListFillRange'.... Qual é o problema? Não entendi a solução do imad. Obrigado.
Este comentário foi feito pelo moderador no site
tente colocar isto =--ISNUMBER(SEERRO(SEARCH($A$12,$A$2,1),"")) em vez disso =--ISNUMBER(SEERRO(SEARCH($A$12,A2,1),"") ) na etapa 6
Este comentário foi feito pelo moderador no site
[quote]Oi, não consigo preencher 'DropDownList' no 'ListFillRange'.... Qual é o problema? Não entendi a solução do imad. Obrigado.Por Maarten[/quote] Eu postei esta resposta acima para o IMAD e vi esta postagem aqui para o MAARTEN, então pensei em postar isso para ele também. Eu vi isso "como fazer um preenchimento automático / sugestão automática de DDL / caixa de combinação" em alguns sites diferentes e TODOS eles querem que você coloque "algo" no campo Propriedades de ListFillRange ANTES eles têm você criar um intervalo nomeado clicando em Fórmula > Definir nome ....... e o ListFillRange sempre ficará em branco na janela Propriedades ATÉ que você defina o nome (Fórmula > Definir Nome) É por isso que eu acho que o IMAD, acima e o MAARTEN abaixo (aqui) estava tendo o problema - não 100% de certeza.
Este comentário foi feito pelo moderador no site
Olá, muito obrigado pela sua solução. Já desisti, mas vou tentar de novo.
Este comentário foi feito pelo moderador no site
Obrigado.. Muito útil.. Deus te abençoe
Este comentário foi feito pelo moderador no site
Eu, como Cristina acima, também gostaria de saber como fazer várias caixas de combinação para uma folha. Eu tentei, mas quando começo a digitar na segunda combobox duas coisas acontecem: 1. nenhuma lista suspensa aparece e 2. o simples ato de digitar combobox2 ativa a seleção da minha combobox1 original e a destaca na lista suspensa da combobox1. Verifiquei se toda a minha codificação diz combobox2 para combobox2 etc. para as outras caixas, mas há uma desconexão que não consigo descobrir.
Este comentário foi feito pelo moderador no site
Estou com o mesmo problema, você já conseguiu uma solução??
Este comentário foi feito pelo moderador no site
Oi Herb, E se eu criasse uma lista suspensa de outra planilha? a fórmula " =--ISNUMBER(IFERROR(SEARCH($A$2,H2,1),""))" tem referência errada e quando eu edito não permite colocar a célula certa. o que você sugere? obrigada
Este comentário foi feito pelo moderador no site
HI, Como fazer o mesmo programa pesquisável para contnious rwo, eu tentei e está funcionando apenas uma linha, eu quero fazer o mesmo para a linha abaixo também para um nome diferente
Este comentário foi feito pelo moderador no site
plz me ajude, eu não consigo inserir a fórmula na barra de fórmulas quando colo esta fórmula e colo isso =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),"")) me dê error.type :(
Este comentário foi feito pelo moderador no site
Obrigado, eu usei acima e funciona perfeitamente.... Até que você tenha duas caixas de combinação em uma folha.. Quando você quer digitar na segunda caixa de combinação, ele destaca o texto na primeira caixa de combinação e não quer pesquisar Se eu deixar a primeira caixa em branco, a segunda caixa funciona bem Por favor me ajude
Este comentário foi feito pelo moderador no site
Oi, seu guia é muito útil, mas ainda encontro um último problema. Estou tentando fazer uma fatura simples e fazer a lista suspensa para a célula de nome do meu cliente, minha listagem de clientes deve estar na mesma planilha que minha planilha de fatura? É possível que eu tenha duas planilhas, "fatura" e "nome do cliente", e faça a lista suspensa para o nome do cliente na planilha "fatura"? Obrigada
Este comentário foi feito pelo moderador no site
Obrigado por este detalhamento para tornar a caixa de combinação pesquisável. Eu até consegui três deles trabalhando na mesma página. Meu problema que encontrei é quando começo a digitar as informações de pesquisa e as informações se restringem, se eu pressionar a tecla de seta para baixo para selecionar o item na lista, o Excel trava em mim. Alguém já passou por isso e, em caso afirmativo, você encontrou uma maneira de resolver esse problema.
Este comentário foi feito pelo moderador no site
Oi,
O problema que você mencionou não aparece no meu caso. Você poderia fornecer sua versão do Office?
Este comentário foi feito pelo moderador no site
Oi Como no fórum, eu preciso ter este menu suspenso pesquisável para as colunas 2 a 500. Por favor, deixe-me saber como eu posso, pois o segundo combo replica o mesmo no primeiro que eu não quero
Este comentário foi feito pelo moderador no site
Caro Jelbin,
Não posso lidar com isso. Desculpe por isso.
Este comentário foi feito pelo moderador no site
4. Na caixa de diálogo Propriedades, você precisa: 1). Selecione Falso no campo AutoWordSelect; 2). Especifique uma célula no campo LinkedCell. Neste caso, entramos em A12; Por que A12? obrigado
Este comentário foi feito pelo moderador no site
Oi,
Esta célula é selecionada opcionalmente, o que pode ajudar a concluir toda a operação. Você pode escolher qualquer um como você precisa.
Este comentário foi feito pelo moderador no site
Eu tive um problema contínuo com todos os documentos em que usei esse método. Uma sombra da caixa suspensa reaparece abaixo dela cada vez que clico em outra célula da planilha e começo a digitar. Está além de apenas um incômodo porque quando a sombra cai, ela impede o uso de quaisquer caixas suspensas pesquisáveis ​​adicionais. Por favor ajude!!! Isso está afetando vários documentos que usamos em toda a nossa organização.
Este comentário foi feito pelo moderador no site
Dia bom,
Desculpe por responder tão tarde. O problema que você resolveu não aparece no meu caso. Seria bom se você pudesse fornecer sua versão do Office. Obrigada!
Este comentário foi feito pelo moderador no site
existe uma maneira de fazer com que a caixa de pesquisa coloque o resultado superior se for deixado em branco? no caso deste exemplo, colocaria automaticamente a china se fosse deixado em branco
Este comentário foi feito pelo moderador no site
Caro Davi,
Você poderia fornecer uma captura de tela da sua planilha mostrando exatamente o que você está tentando fazer?
Este comentário foi feito pelo moderador no site
Olá, obrigado pelo tutorial! Estou tendo um problema em que toda vez que digito na caixa de combinação, "DropDownList1" desaparece da propriedade "ListFillRange". Contanto que eu não digite na caixa, se eu redigitar "DropDownList1" na propriedade, a caixa mostra sugestões. Já revisei tudo e não encontrei nenhum erro. Este é um problema comum e existe uma maneira de corrigi-lo? Obrigado pelo seu tempo!
Este comentário foi feito pelo moderador no site
Caro Ben,
Também estou confuso sobre o desaparecimento do "DripDownList" da propriedade "ListFillRange"
Mas isso não influencia o resultado final de tornar a lista suspensa pesquisável.
Este comentário foi feito pelo moderador no site
Eu me sinto burro, mas imediatamente após postar, percebi que provavelmente não havia adicionado o 1 ao DropDownList1 no VBA, e com certeza esse era o problema! Obrigado de qualquer maneira!
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL