Cree una lista desplegable de Excel con sugerencias de búsqueda

Todos usamos Google como parte de nuestra rutina diaria. Una de sus características es la sugerencia de búsqueda, donde Google actúa de manera inteligente y nos da una lista de sugerencias mientras escribimos.

En este tutorial, aprenderá a crear una lista desplegable de búsqueda en Excel, es decir, una lista desplegable que mostrará los elementos coincidentes a medida que escribe.

A continuación se muestra un video de este tutorial (en caso de que prefiera ver un video en lugar de leer el texto).

Lista desplegable de búsqueda en Excel

Para el propósito de este tutorial, estoy usando los datos de los 20 países principales por PIB.

La intención es crear una lista desplegable de Excel con un mecanismo de sugerencia de búsqueda, de modo que muestre un menú desplegable con las opciones de coincidencia mientras escribo en la barra de búsqueda.

Algo como se muestra a continuación:

Para seguir, descargue el archivo de ejemplo desde aquí

La creación de la lista desplegable de búsqueda en Excel sería un proceso de tres partes:

1. Configurando el cuadro de búsqueda.
2. Configuración de los datos.
3. Escribir un código VBA corto para que funcione.

Paso 1: configuración del cuadro de búsqueda

En este primer paso, usaré un cuadro combinado y lo configuraré para que cuando escribas en él, el texto también se refleje en una celda en tiempo real.

Estos son los pasos para hacer esto:

1. Vaya a la pestaña Desarrollador -> Insertar -> Controles ActiveX -> Cuadro combinado (Control ActiveX).
   • Existe la posibilidad de que no encuentre la pestaña de desarrollador en la cinta. De forma predeterminada, está oculto y debe habilitarse. Haga clic aquí para saber cómo obtener la pestaña de desarrollador en la cinta en Excel.
2. Mueva el cursor al área de la hoja de trabajo y haga clic en cualquier lugar. Insertará un cuadro combinado.
3. Haga clic con el botón derecho en el cuadro combinado y seleccione Propiedades.
4. En el cuadro de diálogo de propiedades, realice los siguientes cambios:
   • AutoWordSelect: Falso
   • LinkedCell: B3
   • ListFillRange: DropDownList (crearemos un rango con nombre con este nombre en el paso 2)
   • MatchEntry: 2 - fmMatchEntryNone

(La celda B3 está vinculada al cuadro combinado, lo que significa que todo lo que escriba en el cuadro combinado se ingresará en B3)

4. Vaya a la pestaña Desarrollador y haga clic en Modo de diseño. Esto le permitirá ingresar texto en el cuadro combinado. Además, dado que la celda B3 está vinculada al cuadro combinado, cualquier texto que ingrese en el cuadro combinado también se reflejará en B3 en tiempo real.

Paso 2: configuración de los datos

Ahora que el cuadro de búsqueda está listo, necesitamos poner los datos en su lugar. La idea es que tan pronto como escriba algo en el cuadro de búsqueda, muestre solo aquellos elementos que tienen ese texto en él.

Para hacer esto, usaremos

• Tres columnas auxiliares.
• Un rango dinámico con nombre.

Columna auxiliar 1

Coloque la siguiente fórmula en la celda F3 y arrástrela para toda la columna (F3: F22)

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

Esta fórmula devuelve 1 cuando el texto del cuadro combinado aparece en el nombre del país de la izquierda. Por ejemplo, si escribe UNI, solo los valores para UniEstados ted y United Kingdom son 1 y todos los valores restantes son 0.

Columna auxiliar 2

Coloque la siguiente fórmula en la celda G3 y arrástrela para toda la columna (G3: G22)

= SI (F3 = 1, CONTAR.SI ($ F $ 3: F3,1), "") 

Esta fórmula devuelve 1 para la primera aparición donde el texto del cuadro combinado coincide con el nombre del país, 2 para la segunda aparición, 3 para la tercera y así sucesivamente. Por ejemplo, si escribe UNI, la celda G3 mostrará 1 ya que coincide con Estados Unidos y G9 mostrará 2 como coincide con Reino Unido. El resto de las celdas estarán en blanco.

Columna auxiliar 3

Coloque la siguiente fórmula en la celda H3 y arrástrela para toda la columna (H3: H22)

= SI.ERROR (ÍNDICE ($ E $ 3: $ E $ 22, COINCIDIR (FILAS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

Esta fórmula apila todos los nombres coincidentes sin celdas en blanco entre ellos. Por ejemplo, si escribe UNI, esta columna mostraría 2 y 9 juntos, y el resto de la celda estaría en blanco.

Creación del rango dinámico con nombre

Ahora que las columnas auxiliares están en su lugar, necesitamos crear el rango dinámico con nombre. Este rango con nombre solo hará referencia a aquellos valores que coincidan con el texto ingresado en el cuadro combinado. Usaremos este rango dinámico con nombre para mostrar los valores en el cuadro desplegable.

Nota: En el paso 1 ingresamos DropDownList en la opción ListFillRange. Ahora crearemos el rango nombrado con el mismo nombre.

Estos son los pasos para crearlo:

1. Vaya a Fórmulas -> Administrador de nombres.
2. En el cuadro de diálogo del administrador de nombres, haga clic en Nuevo. Se abrirá un cuadro de diálogo Nuevo nombre.
3. En el campo de nombre ingrese DropDownList
4. En el campo Se refiere a ingrese la fórmula: = $ H $ 3: INDICE ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Paso 3: poner en funcionamiento el código VBA

Estamos casi alli.

La parte final es escribir un código VBA corto. Este código hace que el menú desplegable sea dinámico de modo que muestre los elementos / nombres coincidentes mientras escribe en el cuadro de búsqueda.

Para agregar este código a su libro de trabajo:

1. Haga clic con el botón derecho en la pestaña Hoja de trabajo y seleccione Ver código.
2. En la ventana de VBA, copie y pegue el siguiente código:

   Private Sub ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Yo.ComboBox1.DropDown End Sub

¡¡Eso es!!

Todo está configurado con su propia barra de búsqueda tipo Google que muestra elementos coincidentes a medida que escribe en ella.

Para una mejor apariencia, puede cubrir la celda B3 con el cuadro combinado y ocultar todas las columnas auxiliares. Ahora puede lucirse un poco con este increíble truco de Excel.

Para seguir, descargue el archivo desde aquí

¿Qué piensas? ¿Podrías utilizar esta lista desplegable de sugerencias de búsqueda en tu trabajo? Déjame saber tus pensamientos dejando un comentario.

Si ha disfrutado de este tutorial, estoy seguro de que también le gustaría los siguientes tutoriales de Excel:

• Filtro dinámico: extraiga datos coincidentes mientras escribe.
• Extraiga datos basados ​​en una selección de lista desplegable.
• Creación de listas desplegables dependientes en Excel.
• La guía definitiva para usar la función BUSCARV de Excel.
• Cómo realizar selecciones múltiples en una lista desplegable en Excel.
• Cómo insertar y usar una casilla de verificación en Excel.