Crie uma lista suspensa do Excel com sugestões de pesquisa

Todos nós usamos o Google como parte de nossa rotina diária. Uma de suas características é a sugestão de busca, onde o Google age de forma inteligente e nos dá uma lista de sugestões enquanto estamos digitando.

Neste tutorial, você aprenderá a criar uma lista suspensa pesquisável no Excel - ou seja, uma lista suspensa que mostrará os itens correspondentes conforme você digita.

Abaixo está um vídeo deste tutorial (caso você prefira assistir a um vídeo a ler o texto).

Lista suspensa pesquisável no Excel

Para o propósito deste tutorial, estou usando os dados dos 20 principais países por PIB.

A intenção é criar uma lista suspensa do Excel com um mecanismo de sugestão de pesquisa, de forma que mostre uma lista suspensa com as opções correspondentes conforme eu digito na barra de pesquisa.

Algo conforme mostrado abaixo:

Para acompanhar, baixe o arquivo de exemplo aqui

A criação da lista suspensa pesquisável no Excel seria um processo de três partes:

1. Configurando a caixa de pesquisa.
2. Configurando os dados.
3. Escrever um código VBA curto para fazê-lo funcionar.

Etapa 1 - Configurando a caixa de pesquisa

Nesta primeira etapa, vou usar uma caixa de combinação e configurá-la para que, ao digitar, o texto também seja refletido em uma célula em tempo real.

Aqui estão as etapas para fazer isso:

1. Vá para a guia Desenvolvedor -> Inserir -> Controles ActiveX -> Caixa de combinação (Controle ActiveX).
   • Existe a possibilidade de você não encontrar a guia do desenvolvedor na faixa de opções. Por padrão, ele está oculto e precisa ser ativado. Clique aqui para saber como obter a guia do desenvolvedor na faixa de opções do Excel.
2. Mova o cursor para a área da planilha e clique em qualquer lugar. Isso irá inserir uma caixa de combinação.
3. Clique com o botão direito na caixa de combinação e selecione Propriedades.
4. Na caixa de diálogo de propriedades, faça as seguintes alterações:
   • AutoWordSelect: Falso
   • LinkedCell: B3
   • ListFillRange: DropDownList (criaremos um intervalo nomeado com este nome na etapa 2)
   • MatchEntry: 2 - fmMatchEntryNone

(A célula B3 está vinculada à caixa de combinação, o que significa que tudo o que você digitar na caixa de combinação será inserido em B3)

4. Vá para a guia Desenvolvedor e clique em Modo de design. Isso permitirá que você insira texto na caixa de combinação. Além disso, como a célula B3 está vinculada à caixa de combinação, qualquer texto inserido na caixa de combinação também será refletido em B3 em tempo real.

Etapa 2 - Definindo os dados

Agora que a caixa de pesquisa está configurada, precisamos colocar os dados no lugar. A ideia é que, assim que você digitar qualquer coisa na caixa de pesquisa, ela mostre apenas os itens que contenham esse texto.

Para fazer isso, vamos usar

• Três colunas auxiliares.
• Um intervalo denominado dinâmico.

Coluna auxiliar 1

Coloque a seguinte fórmula na célula F3 e arraste-a para a coluna inteira (F3: F22)

= - ISNUMBER (IFERROR (SEARCH ($ B $ 3, E3,1), ""))

Esta fórmula retorna 1 quando o texto na caixa de combinação está no nome do país à esquerda. Por exemplo, se você digitar UNI, apenas os valores para Uniestados ted e United Kingdom são 1 e todos os valores restantes são 0.

Coluna auxiliar 2

Coloque a seguinte fórmula na Célula G3 e arraste-a para a coluna inteira (G3: G22)

= SE (F3 = 1, CONT.SE ($ F $ 3: F3,1), "") 

Esta fórmula retorna 1 para a primeira ocorrência onde o texto da caixa de combinação corresponde ao nome do país, 2 para a segunda ocorrência, 3 para a terceira e assim por diante. Por exemplo, se você digitar UNI, a célula G3 exibirá 1 porque corresponde aos Estados Unidos e G9 exibirá 2 porque corresponde ao Reino Unido. O restante das células ficará em branco.

Coluna Auxiliar 3

Coloque a seguinte fórmula na célula H3 e arraste-a para a coluna inteira (H3: H22)

= IFERROR (INDEX ($ E $ 3: $ E $ 22, CORRESPONDÊNCIA (LINHAS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

Esta fórmula empilha todos os nomes correspondentes sem nenhuma célula em branco entre eles. Por exemplo, se você digitar UNI, esta coluna mostrará 2 e 9 juntos, e todas as células restantes ficarão em branco.

Criando a Faixa Nomeada Dinâmica

Agora que as colunas auxiliares estão no lugar, precisamos criar o intervalo nomeado dinâmico. Este intervalo nomeado apenas se referirá aos valores que correspondem ao texto inserido na caixa de combinação. Usaremos esse intervalo denominado dinâmico para mostrar os valores na caixa suspensa.

Observação: Na etapa 1, inserimos DropDownList na opção ListFillRange. Agora vamos criar o intervalo nomeado com o mesmo nome.

Aqui estão as etapas para criá-lo:

1. Vá para Fórmulas -> Gerenciador de nomes.
2. Na caixa de diálogo do gerenciador de nomes, clique em Novo. Isso abrirá uma caixa de diálogo Novo nome.
3. No campo Nome, insira DropDownList
4. No campo Refere-se a, insira a fórmula: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Etapa 3 - Colocando o código VBA para funcionar

Estamos quase lá.

A parte final é escrever um código VBA curto. Este código torna a lista suspensa dinâmica de modo que mostre os itens / nomes correspondentes conforme você digita na caixa de pesquisa.

Para adicionar este código à sua pasta de trabalho:

1. Clique com o botão direito na guia Planilha e selecione Exibir código.
2. Na janela do VBA, copie e cole o seguinte código:

   Sub ComboBox1_Change privado () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub

É isso!!

Está tudo pronto com sua própria barra de pesquisa de tipo do Google que mostra os itens correspondentes à medida que você digita neles.

Para uma melhor aparência, você pode cobrir a célula B3 com a caixa de combinação e ocultar todas as colunas auxiliares. Agora você pode se exibir um pouco com este incrível truque do Excel.

Para acompanhar, baixe o arquivo aqui

O que você acha? Você poderia usar esta lista suspensa de sugestões de pesquisa em seu trabalho? Deixe-me saber sua opinião, deixando um comentário.

Se você gostou deste tutorial, tenho certeza de que também gostaria dos seguintes tutoriais do Excel:

• Filtro Dinâmico - Extraia dados correspondentes enquanto você digita.
• Extraia dados com base em uma seleção de lista suspensa.
• Criação de listas suspensas dependentes no Excel.
• O guia definitivo para usar a função VLOOKUP do Excel.
• Como fazer várias seleções em uma lista suspensa no Excel.
• Como inserir e usar uma caixa de seleção no Excel.