Los filtros avanzados de Excel al desnudo

María pregunta:

Hola Jose Enrique, lo primero, muchas gracias por tu blog, me resulta de gran ayuda.
Tengo una duda respecto al filtro avanzado en Excel 2007; estoy haciendo el ejercicio de excel nº 11, en el cual tenemos que filtrar una serie de datos, y no consigo hacer el segundo ejercicio (“Productos Reducidos”). Cuando intento usar el filtro avanzado no consigo elegir sólo las columnas que quiero, y al final, pego toda la tabla entera. ¿Podrías mostrarnos cómo hacerlo? He estado mirando por aquí a ver si lo habéis publicado, pero no he visto nada. Muchas gracias por tu ayuda,
Saludos, María

José Enrique responde:

Aprovechando la pregunta he pensado hacer una respuesta más extensa a modo de tutorial. En esta ocasión te quiero hablar sobre los filtros en Excel, sobre todo de los filtros avanzados y los secretos que encierran.

En algún momento de este tutorial usaré un listado a modo de ejemplo. Ese listado te lo puedes descargar del archivo “Lista de productos” del Ejercicio 11 de Excel propuesto en el “Especial Oposiciones”, te ayudará a seguir este tutorial.

Ya sabes que la acción de filtrar consiste en quedarte con la parte de la información que te interesa quedando oculto el resto de los datos. Para ello necesitas tener una tabla en Excel a modo de “base de datos”. Esa tabla no sería más que unos nombres de campos en forma de cabecera y unos registros debajo. Para que Excel trabaje bien y pueda aplicar todas las herramientas de datos con normalidad la tabla no debería tener filas o columnas totalmente vacías, puede ser que algunas celdas estén en blanco, pero no filas o columnas enteras.

Buenas y malas condiciones para aplicar un filtro

Para filtrar de forma rápida y sencilla existen los Autofiltros o filtros automáticos. Son esas flechitas que aparecen en cada nombre de campo, en la cabecera de la tabla y que necesitan muy pocas explicaciones para poder hacer uso de ellas.

En Excel 2003 basta con que selecciones una celda cualquiera de la tabla y vayas al menú Datos, Filtro, Autofiltro. Desde ese momento podrás utilizar los filtros a tu antojo.

Autofiltro en Excel 2003

En la misma flechita tienes la opción “Todas” para quitar el filtro aplicado o también puedes ir al menú Datos, Filtro, Mostrar todo para quitar todos los filtros que pudiera tener y asegurarte que la tabla que tienes delante está completa. Si te das cuenta desde la flechas de filtros también puedes ordenar ascendente o descendente por ese campo.

En Excel 2007 o 2010 sería parecido. Debes colocarte igualmente en una celda de la tabla e ir a la ficha Datos, Ordenar y filtrar y pulsar en el comando Filtro. De esta forma tendrás las famosas flechitas.

Ordenar y filtrar: Filtro

Las versiones 2007 y 2010 aportan varias cosas nuevas respecto a Excel 2003: la posibilidad de filtrar por colores, poder elegir varios elementos a la vez y opciones adicionales para filtrar textos, números o fechas.

mejoras filtros Excel 2007 y 2010

Filtros avanzados: tres ventajas importantes

Como te decía anteriormente los autofiltros (las flechitas) no necesitan demasiadas explicaciones, son rápidos y directos, sin complicaciones.

Sin embargo los filtros avanzados no son tan directos y tan cómodos de utilizar. Entonces, ¿qué razones hay para usarlos? Fundamentalmente hay tres ventajas de los filtros avanzados frente a los autofiltros.

Una es la variedad de criterios, es decir, la posibilidad de filtrar una tabla combinando varios criterios de varios campos y usando a su vez condiciones “Y” con condiciones “O”.

Por ejemplo, necesito una lista de los alumnos que han sacado más de un 5 en matemáticas y más de un 7 en conocimiento del medio, pero solo en el tercer trimestre. Aquí hay que jugar con tres campos (asignatura, nota y trimestre) y mezclando condiciones Y con condiciones O. Esto no lo pueden hacer los “autofiltros”, al menos en la versión 2003 de Excel.

La segunda ventaja de los filtros avanzados es la posibilidad de sacar el resultado del filtro en otro lugar, es decir, no “estropear” la tabla original. De esta manera la tabla se mantiene intacta mientras que el resultado se refleja en otra zona, incluso en otra hoja aunque a priori parezca que no se puede. Al sacar el resultado en otra zona también se pueden elegir los campos (columnas) que se quieren visualizar en la tabla filtrada, esta sería la tercera ventaja a mi modo de verlo.

Para poder hacer un filtro avanzado Excel necesita saber tres cosas: dónde está la tabla original, dónde está el “rango de criterios” y donde quieres que saque el resultado del filtro (si es que quieres que se copie a otro lugar).

¿Qué es un rango de criterios? Es un conjunto de celdas donde se pondrán las condiciones para filtrar la tabla. Consiste en los nombres de campos por los que vas a preguntar y debajo las condiciones. Por ejemplo, si necesito sacar los alumnos de Madrid mayor de 30 años deberías tener en algún lugar de la hoja de cálculo algo como lo siguiente:

rango de criterios para filtro avanzado

La palabra “Ciudad” y “Edad” deben estar escritas igual que en la cabecera de la tabla original (mayúsculas o minúsculas no importan), por eso lo mejor es que uses copiar y pegar. En nuestro caso eso sería el rango de criterios (B3:C4).

Los criterios pueden ser muchos y muy enrevesados pero hay una regla que debes tener clara, si los criterios están en la misma fila es un “Y” y si los criterios están en distinta fila es un “O” (Access aplica la misma técnica en los criterios de sus consultas). En la imagen anterior dirías que quieres los alumnos cuya ciudad es Madrid “y” tienen más de 30 años pero muy diferente sería preguntar por los de Madrid “o” que tengan más de 30 años, en este caso el rango de criterio sería (B3:C5):

Rango de criterios OR

En este juego hay que tener cuidado con nuestra forma de hablar que a veces parece que se contradice con estas reglas. Por ejemplo si quiero ver los alumnos de Madrid y Sevilla realmente quiero decir un “O” porque nadie es de Madrid y Sevilla a la vez, esto sería Madrid y debajo Sevilla. ¿Cómo escogerías los alumnos de Madrid y Sevilla mayor de 30 años? De la siguiente forma (B3:C5):

Rango de criterios  con AND y OR

Observa que Madrid y Sevilla están unidos por un “O” (están uno debajo de otro) aunque en mi forma de hablar diga “Y”, y nota igualmente que el “>30” lo pones dos veces aunque en la frase lo nombras una sola vez. Creo que las normas de los rangos de criterios han quedado claras y que jugando con los “Y” y con los “O” puedes hacer cualquier filtro que te propongas.

Además quería comentarte que también puedes jugar con los comodines, ya sabes, el asterisco (*). Sabes que el asterisco suele ser el comodín universal y que puedes usarlo para sustituir cualquier cadena de caracteres de longitud indefinida. Por ejemplo si necesito una lista de los jefes de cualquier departamento que estén en turno de tarde podré hacer lo siguiente:

Rango de criterios con comodín

Esto te sacará tanto los “Jefes de mantenimiento” como los “Jefes de RRHH” que estén de tarde.

Bueno, hasta aquí la teoría, pero ¿cómo se realiza este proceso? Empiezo por Excel 2003.

Supón que tienes la tabla del Ejercicio 11 delante y te piden todos los productos de la categoría “Bebidas” más caros de 15 euros. Está claro que tenemos que fabricar un rango de criterios con algún campo que contenga el precio y con otro que indique la categoría. En este caso ve a la cabecera de la tabla y copia los campos “Categoría” y “PrecioUnidad” y pégalos, por ejemplo, al final de la tabla, en la celda A85. Debajo escribe los criterios, de manera que quede como en la imagen:

Imagen ejercicio 11 paso 1

Por otro lado piensa a partir de que celda quieres que salga el resultado, por ejemplo a partir de la celda A90. Pero, una pregunta, ¿quieres que el resultado contenga todos los campos (columnas) de la tabla original, o sólo algunas columnas que tú elijas? Imagina la segunda posibilidad, que el resultado solo quieres que contenga las columnas “NombreProducto”, “Proveedor”, “Categoría” y “PrecioUnidad”. Pues aquí viene otro truco bastante desconocido; vas a copiar los nombres de los campos que te interesa obtener en la zona a partir de la cual quieres el resultado del filtro, en nuestro caso copia de la tabla original los nombres de campos que acabamos de mencionar y pégalos, por ejemplo, en la celda A90, de manera que quede así:

Ejercicio 11 paso 2

¿Sabes que ocurre? Que ya tienes todos los ingredientes necesarios para realizar el filtro avanzado: Una tabla original (A3:J80), un rango de criterios (A85:B86) y una zona de resultados con una cabecera escogida por ti (A90:D90).

Para mi gusto hay un detalle que, aunque no es imprescindible, te ayudaría a hacer el proceso más rápido sobre todo si tienes que hacer varios filtros. La tabla original es incómoda de seleccionar cada vez que vayas a hacer un filtro avanzado y haya que decirle a Excel el rango exacto donde se encuentra. Lo mejor es dar un nombre al rango de la tabla, de esta manera podrás usar ese nombre cada vez que necesites filtrar y tengas que hacer referencia a la tabla.

Para ello seleccionas toda la tabla (A3:J80) y encima de la columna A verás un hueco que si acercas el ratón dice “cuadro de nombres”. Ahí puedes hacer clic, escribir por ejemplo “productos” y dar a Intro. De esta manera acabas de nombrar un rango:

Usando cuadro de nombres para bautizar un rango

Perfecto, con todo esto vamos a ver el proceso de aplicación del filtro avanzado.

Empiezo con Excel 2003.

Estando en cualquier lugar de la hoja debes ir al menú Datos, Filtro, Filtro avanzado, te aparecerá un cuadro como el siguiente:

Aplicando filtro avanzado paso 1 En él tendrás que indicar los tres elementos mencionadas antes: el lugar donde se encuentra la lista o tabla original, la ubicación del rango de criterios y la zona donde quieres que se copie el resultado (marcando previamente la opción de “Copiar a otro lugar”.

Esto lo puedes hacer ubicándote en cada casilla del cuadro de diálogo y seleccionando el rango con el ratón sin necesidad de escribirlo.

filtro avanzado paso 2

El hecho de que aparezcan las referencias con dólares o sin ellos no tiene importancia, pero observa que para indicar el rango de la lista no he tenido que seleccionar nada, me ha bastado con escribir el nombre de rango que dimos: “productos”.  Al pulsar en Aceptar tendrás el resultado donde querías.

¿Y si donde querías es otra hoja distinta a donde se encuentra la tabla original? Es decir, ¿y si tienes la tabla en una hoja pero copias la cabecera de los campos que quieres obtener en otra hoja diferente (el rango de criterios es indiferente donde se encuentre)? Pues muchos me diréis que no se puede porque lo habéis intentado y sale el siguiente aviso:

Sólo puede copiar los datos filtrados a la hoja activa.

Efectivamente, los datos de destino, es decir, los datos resultantes de hacer el filtro sólo pueden aparecer en la hoja activa…. Pues hagamos que la hoja de destino sea la hoja activa. Es decir, empieza el proceso desde la hoja de destino. Tienes la tabla o lista original en una hoja, y quieres que el resultado vaya a otra hoja, pues bien, colócate en cualquier celda de esa otra hoja antes de entrar en el menú Datos, filtro, filtro avanzado. De esta manera haces todo el proceso desde la hoja de destino. Pruébalo y verás.

Por cierto, ¿qué ocurre si en el apartado de rango de criterios no pones nada? Pues que estarás haciendo un filtro avanzado sin criterios, es decir, obtendrás un resultado de todos los registros (filas) que hay en la tabla original y de los campos (columnas) que hayas elegido en “Copiar a”:

Filtro avanzado paso 3

¿Y si no creas una cabecera con los campos que quieres obtener como resultado? Tendrás un filtro con los registros que cumplan el rango de criterios pero con todos los campos (columnas) de la tabla original como resultado a partir de la celda que indiques:

Filtro avanzado paso 4

Todo este proceso que acabas de ver sería muy parecido si lo realizas en Excel 2007 o 2010, cambiaría únicamente la manera de acceder al comando de filtro avanzado.

En ambos casos se encuentra en la ficha Datos, grupo Ordenar y Filtrar. Ahí podrás ver el botón de “Avanzadas”.

Filtro, Avanzadas

Y con esto terminamos este gran mini-tutorial sobre los filtros avanzados de Excel. Si has llegado hasta aquí sin pegar ninguna cabezada sobre el ordenador seguro que has aprendido algunas cosas nuevas. Recuerda que lo más importante es practicar, así que si tienes alguna hoja de Excel a mano aprovecha y “pregunta a los datos”, sus respuestas te dirán si vas por el camino correcto.

Esta entrada fue publicada en Dudas resueltas, Tutoriales y etiquetada , , , . Guarda el enlace permanente.

16 respuestas a Los filtros avanzados de Excel al desnudo

  1. Emilia Domingo Sanz dijo:

    Me ha parecido fabulosa la explicación de filtros avanzados. Muchas gracias.

  2. Silvia Ríos Vera dijo:

    Me ha sido de gran ayuda la explicación. Muchas gracias!!

  3. Jona dijo:

    Excelente! muchas gracias!

  4. Gustavo Salguero dijo:

    Gracias por el aporte me saco de muchas dudas, eres muy claro y preciso en explicar lo que a uno le puede dar mucha dificultad.

  5. luis dijo:

    me gusto muchogracias

  6. Rogelio dijo:

    Sabía de los filtros avanzados, pero no se me había explicado tan bien como se ha hacho acá y he aprendido algo nuevo.

  7. Fernando dijo:

    Desde Argentina… muchas gracias por la explicación, muy buena

  8. Rossmery Serrano dijo:

    GRACIAS!!! muy buena explicación, me ayudo a resolver mi examen…saludos

  9. Cindy dijo:

    gracias por tu aporte :) me sirvió mucho

  10. Luis dijo:

    Se ha pasado de fabuloso en la explicación:
    Felicidades

  11. Antonio dijo:

    Fantástico. Asi es como se deben explicar los temas. Además he aprendido lo de “hagamos que la hoja de destino sea la hoja activa”, ¡Buen truco!

  12. Antoni dijo:

    Gracias por la aclaración.
    Saludos,

  13. Nieves dijo:

    Muchas gracias por la explicación, muy claro todo.

  14. Vielka dijo:

    Esoy fascinada con lo que se puede hacer en excel. Gracias por su clara explicación de cómo usar los filtros avanzadas.
    Que Dios lo bendiga!

  15. Gracia dijo:

    Muy buena tu explicación!!!! gracias por ayudarnos a resolver problemas

  16. Sonia dijo:

    Muy buena explicación, la verdad es que llevo días peleándome con el tema de los filtros avanzados y al leer el mini tutorial, muchas de las dudas que tenía se me han solventado, así que MUCHAS GRACIAS!!!!!!!!!!!!!!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>