El MEJOR FILTRO Avanzado de Excel
¿Qué es lo que vamos a realizar en el día de hoy de nuestro curso de Excel online? Vamos a configurar un buscador tipo Google, de estos que tú escribes en una celda en una casilla, y automáticamente te van a ir apareciendo aquellos registros que contengan los caracteres que tú has ido escribiendo previamente en esas celdas.
Podríamos decir que es un ejemplo de filtros avanzados en Excel, ya que se comportará como un filtro rápido y automático de información.
En la parte izquierda tenemos los datos, y en la parte central, las columnas auxiliares y en la derecha vamos a copiar-pegar los rótulos de las columnas de nuestros registros. Justo en la parte derecha, van a ir apareciendo todos aquellos registros que coincidan con lo que nosotros escribamos en el buscador.
Es importante hacer una numeración de los diferentes registros que vamos a ir realizando.
más del Seminario del Profesor
CONFIGURAR FILTRO TIPO GOOGLE EN EXCEL
Vamos a comenzar configurando la primera de las columnas auxiliares: igual hallar abro paréntesis y, qué es lo que quiero encontrar, pues quiero buscar los caracteres que yo escriba en la celda de color amarillo a1.
Como siempre voy a escribir los caracteres en esta celda de color amarillo voy a fijar esa celda, le doy F4 una vez y se incorporan los símbolos de anclaje de los dólares, punto y coma.
Dónde voy a buscar yo este texto, lo voy a buscar dentro de la casilla del país correspondiente, justo la casilla a5 le daríamos al enter.
Hacemos doble clic en la pestañita inferior derecha de la celda para copiar la fórmula en todos los registros hacia abajo.
Como veis si encuentra los caracteres asociados dentro de la columna de los países te devuelve la posición, te devuelve un número, y si no, te devuelve un error.
La fórmula quedaría de la siguiente manera: =HALLAR($A$1;A5)
Qué es lo que vamos a hacer en la siguiente columna: vamos a aprovechar estos valores y aprovecharemos la fórmula esnúmero: abrimos paréntesis y seleccionamos el valor de la columna auxiliar número 1 abrimos y cerramos el paréntesis y le damos al enter.
Doble clic para abajo y observamos que al aplicar la fórmula es número lo que nos devuelve la función es si es un número nos dice que es verdadero, y si nos da un error, la fórmula hallar nos dice que no es un número, es decir, es falso.
Quedando la fórmula: =ESNUMERO(D5)
más del Seminario del Profesor
Ahora con la tercera de las columnas auxiliares, lo que vamos a configurar es el número de registros que van a aparecer: igual SI, abro paréntesis, la celda de la columna auxiliar número 2, es igual a verdadero, escribo verdadero abro y cierro paréntesis, punto y coma, el Excel quiero que me cuente, contar punto sí, cuántas veces aparece, dentro del rango hago clic en la propia celda de la columna auxiliar número 2, escribo dos puntos y automáticamente me rellena con la propia celda.
Aquí vamos a tener que hacer un pequeño ajuste y vamos a poner como referencia absoluta como anclaje o fijar la primera de las celdas E5, pulsamos F4, punto y coma, y ahora finalmente, cuál es el criterio el criterio que yo quiero que me cuente, las veces que aparece la palabra verdadero, cierro el paréntesis punto y coma y si no quiero que me escriba un cero, cierro el paréntesis le doy al enter.
La función sería: =SI(E5=VERDADERO();CONTAR.SI($E$5:E5;”verdadero”);0)
Voy a comprobar si funciona, y fijaros qué es lo que ha ocurrido aquellos registros cuyo país contiene los caracteres que hemos escrito en la casilla de color amarillo, en este caso hemos escrito m a n, y m a n solo aparecen en los países que contienen esos caracteres, es decir, en la palabra Alemania, pues aquellos registros que estoy marcando ahora mismo, sí que contienen los caracteres que hemos escrito en nuestro buscador particular.
Cualquier duda me lo escribís abajo en los comentarios del vídeo de YouTube.
más del Seminario del Profesor
CÓMO DAR ASPECTO DE BUSCADOR AL FILTRO AVANZADO
Muy bien, ya tenemos configurada la parte difícil, ya vais a ver qué chulo lo que vamos a realizar ahora.
En primer lugar, empiezo escribiendo la función índice: igual índice como estoy en la columna de los países, quiero extraer aquí el país, qué país o qué columna de país, pues este, y pues hasta abajo y hago la selección.
Una vez que ya he seleccionado la columna de los países, porque de ahí me tiene que extraer el Excel un determinado país, punto y coma, tengo que decirle al Excel que utilice la fórmula coincidir.
Escribo coincidir: abro paréntesis y le digo al Excel, a ver qué vas a buscar, pues voy a buscar el valor número uno, que es este de aquí, punto y coma, dónde lo vas a buscar, lo vas a buscar en la columna auxiliar número 3, de acuerdo, seleccionaría todos los datos hacia abajo, punto y coma, y además quiero escribir un cero, porque quiero que sea una coincidencia exacta, cerraría el paréntesis.
Voy a utilizar como referencia absoluta o fijar toda esa selección. Esto, por un lado.
más del Seminario del Profesor
Después lo mismo voy a hacer con la columna auxiliar número 3 para ello le doy a F4 una vez y listo, cerraría el último de los paréntesis le doy al enter, hacer doble clic hacia abajo.
Como veis, me aparecen en los cuatro primeros lugares, cuatro “Alemanias”, por qué me aparecen cuatro, porque en la columna auxiliar, solo hemos encontrado cuatro registros que contienen los caracteres m a n que hemos escrito en nuestro buscador, de acuerdo.
Nos queda utilizar sí punto error escribo después del igual sí punto error, abro el paréntesis, y me sitúo al final de toda la fórmula que he escrito anteriormente.
Escribo punto y coma, y después comillas y comillas, cierro el paréntesis, le doy al enter doble clic para abajo para actualizar el resto de fórmulas de la parte inferior.
La fórmula completa sería: =SI.ERROR(INDICE($A$5:$A$13;COINCIDIR($H5;$F$5:$F$13;0));””)
Como veis, al hacer este ajuste, ya me aparecen correctamente los valores del buscador y en la parte inferior, donde había errores, se me queda en blanco, y queda mucho más atractivo.
Vamos a hacer la misma configuración que hemos hecho para países, lo tenemos que hacer para destino y para estado, de acuerdo, por favor si os está gustando, pues darle like a este vídeo, que me gusta ver cariño en todas estas cosas que hacemos [Música].
Tenemos configuradas ya las columnas auxiliares y tenemos configurada la tabla de los resultados de búsqueda muy importante.
Por supuesto, le podéis dar formato a la tabla, que eso tiene muchas ventajas como ya comentamos en un vídeo anterior.
Para ir terminando, vamos con la parte final que es la que va a transformar toda esta configuración en un auténtico buscador tipo Google.
Para ello, hacemos clic en la pestaña desarrollador, hacemos clic en el botón insertar, nos aparecerá un desplegable y nos vamos a la parte inferior derecha donde pone controles activex.
Vamos a centrarnos en el botoncito cuadro de texto control active x, hacemos clic en él, y hacemos clic y arrastramos creando un rectángulo. Este rectángulo va a ser nuestro buscador tipo Google.
Para dejar esto un poquito más bonito, estas configuraciones de las columnas auxiliares las voy a ocultar botón derecho encima de una de ellas, y le doy a ocultar.
más del Seminario del Profesor
Hacemos clic derecho encima de este cuadro de texto, y nos vamos a la opción que pone propiedades.
En todas estas propiedades que están en inglés, nos vamos a la opción que pone link cell qué celda vamos a vincular, pues vamos a vincular esa celda de color amarillo donde habíamos escrito los caracteres, en nuestro ejemplo ahora es la celda c1 en mi caso.
Cerraríamos lo que son las propiedades y hacemos bonito lo que nos queda.
Por último, nos vamos a la pestaña desarrollador y, veis que está activado el botón modo diseño, gracias a que tenemos activado el modo diseño podemos pinchar y arrastrar cualquier elemento que vayamos incorporando.
Para ver cómo funciona y ver el resultado de nuestro buscador tipo Google, desactivamos ese botón haciendo clic en él.
Y ahora ya podemos escribir lo que nosotros queramos, fijaros qué chulo se ha actualizado el resultado de la búsqueda, ahora escribo por ejemplo la i y la a.
La i y la a de país aparece en los países de Francia y Alemania. Ah y en Italia también. Pues venga y se va actualizando toda la búsqueda.
Venga voy a escribir España. Como veis, conforme escribo esp, automáticamente se ha ido actualizando, conforme voy borrando se van a ir incorporando registros que tengan el carácter en su celda correspondiente, porque estamos trabajando o hemos hecho un buscador exclusivo para los países.
No hace falta escribir el país completo, directamente FR ya sabemos que tenemos esos registros en nuestra base de datos, lo mismo con Italia y ya empiezas a poner la i la t y automáticamente ya te aparecen.
Así que yo espero que os haya servido este tutorial de filtros avanzados en excel y que lo podáis aplicar en cada uno de vuestros proyectos.
Como siempre, si tienes alguna duda coméntame en los vídeos de Youtube, y por supuesto, no dudes en consultar la ayuda de filtros avanzados de Microsoft Excel.
Recuerda SUSCRÍBETE a mi canal de Youtube.
más del Seminario del Profesor
Nos vemos en el siguiente post.
Chao chao.