Como hacer un filtro avanzado en Excel


Hoy vamos a hablar del filtro avanzado de Excel.

A ver, todos sabemos colocar el embudito en la cabecera de un rango de datos en Excel clicando sobre el icono del filtro que aparece en la pestaña “Inicio” y aplicar filtros a los datos de ese rango.

Sabemos, además, que a través de esos embuditos podemos seleccionar diferentes tipos de filtros.

Si son números, podemos escoger entre “Es igual a…”, “No es igual a…”, “Mayor que…”, “Menor que…”, etc.

Si es texto, tenemos “Es igual a…”, “No es igual a…”, “Empieza por…”, “Termina con…”, “Contiene…” y otras tantas opciones.

Incluso sabemos que podemos realizar filtros por más de un criterio.

Sin embargo, hay cosas que con este el filtro simple de Excel, no se pueden hacer.

Para ellas, tenemos el filtro avanzado.

Hoy vamos a ver cómo funciona y qué podemos llegar a conseguir a través de esta funcionalidad.

¿Empezamos?

Diferencia entre el filtro simple y el filtro avanzado en Excel

Para entender bien la diferencia entre el filtro de Excel y el filtro avanzado, empezaremos haciendo una prueba.

Para hacerlo, he creado una base de datos random en un rango de Excel.

Vamos a suponer que queremos filtrar los pedidos de Jan Benítez Guijarro enviados a través de DHL.

Para hacerlo, solo debemos crear dos filtros, uno en el nombre del cliente y otro en el sistema de envío.

Ahora, supongamos que queremos añadir a Juan Lago Pujol a la ecuación.

De momento, Excel nos muestra la información que le pedimos sin ningún problema.

Pero, ¿qué pasa si necesitamos ver los datos de Jan Benítez Guijarro enviados por DHL y los de Juan Lago Pujol enviados por Correos Express?

Si ahora personalizamos el filtro de la columna del envío…

… ya nos encontramos con datos que no se ajustan a los criterios que nos interesan.

Esto ocurre porque el filtro básico de Excel no es capaz de discriminar entre los criterios que corresponden a un cliente y los que corresponden al otro y realiza el mismo filtrado para ambos.

Para casos como este, y muchos otros, tenemos el filtro avanzado de Excel.

Detalles previos a crear un filtro avanzado de Excel

Como siempre, antes de meternos en materia, vamos a ver un par de detalles importantes para poder de crear filtros avanzados de Excel.

Dónde encontramos el filtro avanzado de Excel

Si vamos a la pestaña de Inicio y clicamos sobre las opciones de “Ordenar y filtrar”, vemos que las opciones que encontramos aquí solo nos permiten aplicar, borrar y volver a aplicar filtros.

Aquí no encontramos el filtro avanzado.

El filtro avanzado de Excel está en la pestaña “Datos”.

En ella tenemos un grupo “Ordenar y filtrar”, que nos ofrece las mismas opciones que están en la pestaña “Inicio” y, además, la opción “Avanzadas”.

Esta es la que usaremos para crear filtros avanzados en Excel.

Criterios de filtrado

Con los filtros simples, filtramos a través del embudito que Excel nos pone en las cabeceras de los datos, pero el filtro avanzado funciona de otra manera.

Para que Excel sepa lo que queremos que filtre, se lo tenemos que decir poniendo, en unas celdas aparte, los criterios por los que vamos a filtrar los datos.

De momento, puede parecer raro, pero lo entenderás perfectamente en cuanto empecemos a trabajar.

Si bien es cierto que Excel no distingue entre mayúsculas y minúsculas, es importante que, en estas celdas, se respeten las tildes.

¿Irreversibilidad del filtro?

Una cosa que debes tener en cuenta cuando crees un filtro avanzado en Excel, es que éste (en principio) parece irreversible.

Es decir, en la tabla quedarán solo los datos que hemos filtrado y los demás desaparecerán.

Evidentemente, siempre podemos deshacer la última acción con la flecha correspondiente…

pero no ese no es el sistema de “desfiltrar” los datos.

La forma de hacerlo es a través del botón “Borrar” que encontramos encima del botón que hemos usado para crear el filtro.

De todas formas, también tenemos la posibilidad de copiar el resultado del filtrado en otra ubicación distinta, pero esto, también tiene sus restricciones.

Pero vayamos paso a paso. Esto lo veremos en detalle más adelante.

Crear un filtro avanzado en Excel

Partiendo de la misma tabla, vamos a intentar crear ese filtro que antes no hemos podido hacer con el filtro simple.

Es decir, los datos de Jan Benítez Guijarro enviados por DHL y de Juan Lago Pujol enviados por Correos Express.

Para ello, lo primero que debemos hacer es crear una “mini tabla” en la que definiremos los criterios por los que queremos filtrar nuestra base de datos.

Empezaremos con un filtro avanzado simple, aquel que podríamos conseguir con los filtros básicos de Excel e iremos complicando la cosa a medida que vayamos avanzando.

Imaginemos que, para empezar, queremos obtener los datos de Jan Benítez Guijarro enviados por DHL.

Seleccionando cualquier celda de la base de datos, si clicamos sobre el icono de “Avanzadas”, nos aparece la siguiente ventana emergente:

Como Excel es muy listo, en la mayoría de los casos, detectará automáticamente el rango de la lista que queremos filtrar. (En caso que no lo haga, tendrás que escogerlo tú, pero clicando CRTL+E lo puedes harás en un plis).

Ahora tenemos que indicarle el rango de criterios de filtrado, seleccionando las celdas en las que lo hemos puesto. En este caso, entre las celdas C1 y D2.

Al clicar en “Aceptar”, vemos como nuestra tabla se ha filtrado automáticamente.

Hasta aquí, nada del otro mundo. Nada que no podamos hacer con un filtro simple de Excel.

Ahora, vamos un paso más allá.

Vamos a añadir los datos que corresponden a Juan Lago Pujol enviados por Correos Express.

Para ello, añadimos esos datos a nuestro rango de criterios de filtro:

Siguiendo los mismos pasos que en el caso anterior (y cambiando el rango de criterios entre las celdas C1 y D3, claro)…

… obtenemos esos datos que, con el filtro básicos de Excel, no podemos conseguir.

Esta, es la parte más sencilla que nos ofrece el filtro avanzado de Excel, pero lo que se puede conseguir con él, va mucho más allá…

Opción “Copiar a otro lugar”

Si queremos mantener la tabla original sin cambios y tener los datos filtrados a la vez, vamos a tener que seleccionar la opción “Copiar a otro lugar”

… indicar en «Copiar a:» la celda en la que queramos que se muestren los resultados…

… y Excel nos hará un filtrado manteniendo los datos de origen intactos.

Sin embargo, esta opción tiene una restricción.

Excel no nos permite crear (repito, crear) filtros avanzados en hojas que no sean la hoja activa.

Y esto, ¿qué quiere decir?

En Excel, la hoja activa siempre es la hoja en la que estamos situados. Si tenemos un libro con varias hojas, y vamos cambiando de una a otra, para Excel estamos activando y desactivando esas hojas a medida que nos movemos entre ellas.

Por tanto, si estamos situados en la hoja en la que tenemos los datos, queremos crear un filtro y que el resultado se muestre en otra hoja, no podremos hacerlo de la forma que te he enseñado hasta ahora.

Para conseguir que el resultado de un filtro avanzado en Excel se muestre en una hoja distinta a la que están los datos, tenemos que crear ese filtro estando situados en la hoja en la que queremos ver el resultado, es decir, en la hoja de destino.

Por tanto, estando situados en la hoja en la que queremos que se muestren los datos filtrados (en mi caso sería la que he llamado “Hoja destino filtro avanzado”), clicamos sobre “Avanzadas” y desde ahí, ya podemos seleccionar el rango de celdas en el que se encuentran los datos:

Filtro avanzado. Copiar a otra hoja

Otras opciones del filtro avanzado de Excel

Hasta ahora, hemos utilizado el filtro avanzado de una forma básica, con solo dos criterios, pero hay algunos detalles más que tenemos que conocer.

Cuando se repite alguno de los criterios

Supongamos ahora que queremos otro tipo de filtro.

Vamos a filtrar las facturas del cliente Jan Benítez Guijarro que se han enviado por DHL y por Correos Express.

Si en la tabla de criterios pusiéramos esto:

Obtendríamos los datos de Jan Benítez Guijarro de DHL y los de los envíos realizados a través de Correos Express de todos los clientes. Es decir, Excel lee los criterios de filtrado por filas.

Por tanto, para obtener lo que necesitamos, deberíamos repetir aquel criterio por el que queramos filtrar los datos, en ambas filas del rango de criterios:

De esta manera, el filtro avanzado de Excel nos devolverá los resultados que esperamos:

Filtrado por importes

Otra situación con la que nos podemos encontrar sería la necesidad de crear un filtro por un rango de importes.

En este caso, tendremos que utilizar los signos de = (igual), <= (mayor o igual), >= (menor o igual), < (mayor) y > (menor).

Y ¿qué pasa si queremos filtrar los importes entre dos cantidades específicas?

En este caso, lo que tendremos hacer es repetir en el rango de criterios la columna «Importe», para que Excel entienda el rango de importes que queremos obtener en el filtrado:

Filtrado por fechas

De la misma manera que con los importes, podemos utilizar las fechas para crear los filtros avanzados en Excel.

Y también, de igual forma, podemos utilizar los operadores = (igual), <= (mayor o igual), >= (menor o igual), < (mayor) y > (menor) para ajustar los filtros a nuestras necesidades.

Evidentemente, si queremos filtrar entre fechas, también tendremos que repetir la columna «Fecha» en los criterios, para indicarle a Excel el rango de fechas del que queremos obtener los resultados:

Filtrado por más de dos criterios

El filtro avanzado de Excel también podemos utilizarlo para filtrar más de dos criterios.

Para ello, lo único que tenemos que hacer es crear una tercera o cuarta columna en el rango de criterios.

Incluso, podríamos filtrar por todos los campos de la tabla de datos:

En este caso, la tabla tiene muy pocos datos y los resultados son un poco ridículos, pero imagínate esto con cientos o miles de filas.

La opción “Solo registros únicos” del filtro avanzado de Excel

Una de las cosas más interesantes que nos ofrece el filtro avanzado de Excel es la opción de registros únicos, pero esto es algo que no se debería utilizar en todos los casos.

Vamos a ver por qué.

Si en la tabla aplicamos el filtro SIN seleccionar la opción de “Solo registros únicos”, el resultado que obtenemos es el siguiente:

Y si realizamos la misma operación de filtrado, esta vez, seleccionando la opción de “Solo registros únicos”, el resultado que vamos a obtener va a ser el mismo:

Esto es así, porque en esta base de datos, no se existe ningún registro en el que se repitan los criterios de filtrado que hemos seleccionado.

Por tanto, para un caso como este, el activar esta opción no tiene ningún sentido.

Pero ahora vamos a imaginarnos otro escenario.

Supongamos que lo que queremos es que este mismo filtro avanzado, con los mismos criterios de filtrado, nos devuelva solo lo que se encuentra en la columna «Sección» a la que pertenecen los registros que cumplen con los criterios que hemos puesto en nuestro filtro.

No queremos la tabla completa, solo queremos los resultados de la columna “Sección”, pero estos deben cumplir con los criterios de filtrado.

Para verlo claro, nos vamos a fijar en el resultado que hemos obtenido antes.

En ambos casos, lo datos que nos ha devuelto el filtro en la columna «Sección» es lo siguiente:

Para conseguir una tabla que contenga únicamente el resultado de esta columna, tenemos que indicárselo a Excel poniendo este encabezado, “Sección”, en la ubicación en la que queramos que aparezcan los resultados e indicarle que esa celda es en la que queremos que aparezcan dichos resultados.

Vamos a hacer una prueba para ver la diferencia que existe ahora entre seleccionar o no seleccionar la casilla de “Solo registros únicos”.

Como puedes ver, en el primer caso, Excel hace un filtrado y nos devuelve los mismos resultados que habíamos conseguido antes en la columna «Sección», obviando si estos están o no repetidos.

Sin embargo, en el segundo caso, lo que hace es devolvernos los resultados, pero eliminando los duplicados.

Y hasta aquí hemos llegado.

A partir de ahora, podrás filtrar tus bases de datos con esta fantástica opción de filtro avanzado que nos ofrece Excel, sin tener que romperte la cabeza.

Nada nuevo que no sepas

Nombre, email, aceptas y... ¡Sorpresita!

Si quieres saber cómo sacarle el máximo partido a tus libros de Excel, únete a los cientos de miles de millones de de personas que lo están descubriendo día a día.


Deja un comentario