Excel Filter es una de las funcionalidades más utilizadas cuando se trabaja con datos. En esta publicación de blog, le mostraré cómo crear un cuadro de búsqueda de filtro dinámico de Excel, de modo que filtre los datos en función de lo que escriba en el cuadro de búsqueda.
Algo como se muestra a continuación:
Hay una doble funcionalidad para esto: puede seleccionar el nombre de un país de la lista desplegable, o puede ingresar manualmente los datos en el cuadro de búsqueda, y le mostrará todos los registros coincidentes. Por ejemplo, cuando escribe "I", le da todos los nombres de los países con el alfabeto I en él.
Ver video: Creación de un cuadro de búsqueda de filtro dinámico de Excel
Creación de un cuadro de búsqueda de filtro dinámico de Excel
Este filtro de Excel dinámico se puede crear en 3 pasos:
- Obtener una lista única de elementos (países en este caso). Esto se usaría para crear el menú desplegable.
- Creando el cuadro de búsqueda. Aquí he usado un Combo Box (Control ActiveX).
- Configuración de los datos. Aquí usaría tres columnas auxiliares con fórmulas para extraer los datos coincidentes.
Así es como se ven los datos sin procesar:
CONSEJO ÚTIL: Casi siempre es una buena idea convertir sus datos en una tabla de Excel. Puede hacer esto seleccionando cualquier celda en el conjunto de datos y usando el atajo de teclado Control + T.
Paso 1: obtener una lista única de elementos
- Seleccione todos los países y péguelo en una nueva hoja de trabajo.
- Seleccione la lista de países -> Ir a Datos -> Eliminar duplicados.
- En el cuadro de diálogo Eliminar duplicados, seleccione la columna en la que tiene la lista y haga clic en Aceptar. Esto eliminará los duplicados y le dará una lista única como se muestra a continuación:
- Un paso adicional es crear un rango con nombre para esta lista única. Para hacer esto:
- Vaya a la pestaña Fórmula -> Definir nombre
- En el cuadro de diálogo Definir nombre:
- Nombre: CountryList
- Alcance: Libro de trabajo
- Se refiere a: = UniqueList! $ A $ 2: $ A $ 9 (Tengo la lista en una pestaña separada llamada UniqueList en A2: A9. Puede consultar donde reside su lista única)
NOTA: Si utiliza el método "Eliminar duplicados" y expande sus datos para agregar más registros y nuevos países, tendrá que repetir este paso nuevamente. Alternativamente, también puede crear una fórmula para dinamizar este proceso.
Paso 2: creación del cuadro de búsqueda de filtro dinámico de Excel
Para que esta técnica funcione, necesitaríamos crear un "Cuadro de búsqueda" y vincularlo a una celda.
Podemos usar el cuadro combinado en Excel para crear este filtro de cuadro de búsqueda. De esta manera, cada vez que ingrese algo en el cuadro combinado, también se reflejará en una celda en tiempo real (como se muestra a continuación).
Estos son los pasos para hacer esto:
- Vaya a la pestaña Desarrollador -> Controles -> Insertar -> Controles ActiveX -> Cuadro combinado (Controles ActiveX).
- Si no tiene la pestaña Desarrollador visible, estos son los pasos para habilitarla.
- Si no tiene la pestaña Desarrollador visible, estos son los pasos para habilitarla.
- Haga clic en cualquier parte de la hoja de trabajo. Insertará el cuadro combinado.
- Haga clic con el botón derecho en Cuadro combinado y seleccione Propiedades.
- En la ventana Propiedades, realice los siguientes cambios:
- Celda vinculada: K2 (puede elegir cualquier celda donde desee que muestre los valores de entrada. Usaremos esta celda para configurar los datos).
- ListFillRange: CountryList (este es el rango con nombre que creamos en el Paso 1. Esto mostraría todos los países en el menú desplegable).
- MatchEntry: 2-fmMatchEntryNone (esto asegura que una palabra no se complete automáticamente mientras escribe)
- Con el Cuadro combinado seleccionado, vaya a la pestaña Desarrollador -> Controles -> Haga clic en Modo de diseño (esto lo saca del modo de diseño y ahora puede escribir cualquier cosa en el Cuadro combinado. Ahora, todo lo que escriba se reflejará en la celda K2 en tiempo real)
Paso 3: configuración de los datos
Finalmente, vinculamos todo por columnas auxiliares. Utilizo tres columnas auxiliares aquí para filtrar los datos.
Columna auxiliar 1: Introduzca el número de serie de todos los registros (20 en este caso). Puede usar la fórmula ROWS () para hacer esto.
Columna auxiliar 2: En la columna auxiliar 2, verificamos si el texto ingresado en el cuadro de búsqueda coincide con el texto en las celdas de la columna del país.
Esto se puede hacer usando una combinación de funciones SI, ESNÚMERO y BÚSQUEDA.
Aquí está la fórmula:
= SI (ESNÚMERO (BÚSQUEDA ($ K $ 2, D4)), E4, "")
Esta fórmula buscará el contenido en el cuadro de búsqueda (que está vinculado a la celda K2) en la celda que tiene el nombre del país.
Si hay una coincidencia, esta fórmula devuelve el número de fila; de lo contrario, devuelve un espacio en blanco. Por ejemplo, si el cuadro combinado tiene el valor "EE. UU.", Todos los registros con el país como "EE. UU." Tendrían el número de fila y el resto estaría en blanco ("")
Columna auxiliar 3: En la columna auxiliar 3, necesitamos apilar todos los números de fila de la columna auxiliar 2. Para hacer esto, podemos usar una combinación de las fórmulas IFERROR y SMALL. Aquí está la fórmula:
= SI.ERROR (PEQUEÑO ($ F $ 4: $ F $ 23, E4), "")
Esta fórmula apila todos los números de fila coincidentes. Por ejemplo, si el cuadro combinado tiene el valor EE. UU., Todos los números de fila con "EE. UU." Se apilan.
Ahora, cuando tenemos los números de fila apilados juntos, solo necesitamos extraer los datos en estos números de fila. Esto se puede hacer fácilmente usando la fórmula de índice (inserte esta fórmula en el lugar donde desea extraer los datos. Cópiela en la celda superior izquierda donde desea extraer los datos y luego arrástrela hacia abajo y hacia la derecha).
= SIERROR (ÍNDICE ($ B $ 4: $ D $ 23, $ G4, COLUMNAS ($ I $ 3: I3)), "")
Esta fórmula tiene 2 partes:
ÍNDICE - Esto extrae los datos basados en el número de fila.
SI ERROR - Esto vuelve en blanco cuando no hay datos.
Aquí hay una instantánea de lo que finalmente obtienes:
El cuadro combinado es un cuadro desplegable y un cuadro de búsqueda. Puede ocultar los datos originales y las columnas auxiliares para mostrar solo los registros filtrados. También puede tener los datos sin procesar y las columnas auxiliares en otra hoja y crear este filtro de Excel dinámico en otra hoja de trabajo.
¡Se creativo! Prueba algunas variaciones
Puede intentar personalizarlo según sus necesidades. Es posible que desee crear varios filtros de Excel en lugar de uno. Por ejemplo, es posible que desee filtrar los registros en los que el Representante de ventas es Mike y el País es Japón. Esto se puede hacer siguiendo exactamente los mismos pasos con alguna modificación en la fórmula en las columnas auxiliares.
Otra variación podría ser filtrar los datos que comienzan con los caracteres que ingresa en el cuadro combinado. Por ejemplo, cuando ingresa "I", es posible que desee extraer países que comiencen con I (en comparación con la construcción actual, donde también le daría Singapur y Filipinas, ya que contiene el alfabeto I).
Como siempre, la mayoría de mis artículos se inspiran en las preguntas / respuestas de mis lectores. Me encantaría recibir tus comentarios y aprender de ti. Deja tus pensamientos en la sección de comentarios.
Nota: En caso de que esté usando Office 365, puede usar la función FILTRO para filtrar rápidamente los datos mientras escribe. Es más fácil que el método que se muestra en este tutorial.
