Informes con Filtro Avanzado de Excel

viernes, octubre 18, 2013

Una de las ventajas de Filtro Avanzado, comparado con Autofiltro, es la posibilidad de copiar las filas filtradas a otra ubicación automáticamente.

En el pasado he mostrado como podemos superar la aparente limitación que impide copiar el resultado del filtro a otra hoja.

Ese truco nos permite crear informes respetando la norma de separar entre datos, cálculos e informes. Principio de buena práctica que nunca me cansaré de enfatizar.



En esta nota daremos un paso adelante y mostraré como crear informes usando Filtro Avanzado en forma dinámica (sin macros).

Supongamos esta hoja que contiene las ventas de una empresa desde el 2006 hasta el 31/05/2008



Al rango de la base de datos (bd!A1:M2156) lo asignamos al nombre "rngFacturas"



Creamos una nueva hoja, "Informe 1" que contendrá un informe que muestre los siguientes campos:


  • Cliente
  • País
  • Vendedor
  • Fechas
  • Total Facturado


filtrado por fechas.

En la celda B2 de la hoja "Informe 1" introducimos esta fórmula

=bd!H2>=FECHA(2008,5,1)

y en rango B5:F5 los encabezamientos de los campos (columnas) que queremos que aparezcan en el informe



Es importante notar que la celda del criterio (B2) no tiene encabezado; la celda B1 está vacía.

Ahora seleccionamos una celda de la hoja "Informe 1" tal que todas las celdas contiguas estén vacías (por ejemplo, B7); usamos Datos—Ordenar y Filtras—Avanzadas para abrir el menú de Filtro Avanzado



Hacemos clic en la ventanilla "Rango de la lista", apretamos F3 para abrir el menú "Pegar nombre" y seleccionamos "rngFacturas"; en "Rango de Criterios" seleccionamos B1:B2; marcamos la opción "Copiar a otro lugar" y en la casilla "Copiar a" seleccionamos el rango donde pusimos los encabezados de las columnas que queremos que aparezcan en el informe (en nuestro ejemplo B5:F5)



apretamos "Aceptar" y el informe aparece instantáneamente en la hoja



El inconveniente de esta método es que si queremos cambiar la fecha del informe, tenemos que editar la fórmula. Aparentemente podríamos crear una referencia a una celda que contenga la fecha, por ejemplo usar como criterio en la celda B2 esta fórmula: =bd!H2>=F2



El problema de esta técnica es que en el caso de Filtro Avanzado no funciona. El remedio es asignar un nombre a la celda F2, por ejemplo "fecha1"



Ahora la fórmula del criterio es =bd!H2>=fecha1 y Filtro Avanzado funciona.

Vamos a dar un paso más adelante. Usamos esta fórmula para extraer y copiar las ventas entre dos fechas

=Y(bd!H2>=fecha1,bd!H2<=fecha2)



Hemos definido un segundo nombre (fecha2) para la segunda fecha; las celdas del criterio de filtrado la ponemos en la columna A de manera que luego podamos ocultarla y al rango A1:A2, que contiene la fórmula, le asignamos el nombre "celCriterio".

Ahora podemos crear el informe de esta manera



Si queremos generar un nuevo informe, cambiamos los parámetros en las celdas D2 y D3 y aplicamos nuevamente Filtro Avanzado, seleccionando previamente alguna celda vacía. Excel elimina los datos existentes en el informe y los reemplaza con la nueva selección. El único inconveniente es que Filtro Avanzado copia también los formatos de la base de datos y éstos persisten aún en las filas que no contienen datos.



La solución es quitar todos los formatos en la base de datos.

El cuaderno con el ejemplo se puede descargar aquí.

19 comments:

Anónimo,  19 octubre, 2013 15:15  

Hola Jorge;
Una curiosidad creo que te haz confundido con el cuaderno con el ejemplo a descargar no coincide con el del tutorial.
Gracias
Atentamente:
Isabel

Jorge Dunkelman 19 octubre, 2013 18:54  

Hola Isabel, efectivamente, he corregido el enlace.
Gracias por la observación.

Anónimo,  30 octubre, 2013 13:15  

Hola Jorge, por alguna razón la metodología que expones aquí no funciona, no filtra los valores. He seguido el ejemplo al pie de la letra con la base de datos que has colgado y no funciona. Puede que el error esté en la forma de meter la condicional ya que según otros tutoriales que he visto hay que poner el nombre de la columna y debajo las condiciones de filtro, para que excel interprete qué columna es la que establece el filtrado.

PD: El excel que manejo es el 2007.

Enhorabuena por el blog!

Anónimo,  30 octubre, 2013 13:45  

Ya he descubierto donde cometía el error, en el rango de criterios seleccionaba únicamente la celda con la condición y hay que seleccionar el rango que abarca la celda con la condición y la casilla inmediatamente superior. Espero que le sea útil la aclaración!

Jorge Dunkelman 30 octubre, 2013 17:55  

Estimado,

en el cuerpo de la nota se explica: en "Rango de Criterios" seleccionamos B1:B2, es decir, se seleccionan ambas celdas; también puede verse en la imagen aclaratoria. De todas maneras le agradezco las buenas intenciones. A pesar que la alaración es innecesaria, puede ser que también otros lectores no hayan prestado atención y a ellos su aclaración pudiera ser útil.

Unknown 07 noviembre, 2013 17:31  

Estimado felicitaciones por tu blog, es magnífico. Fijate que trabaje bien con tus instrucciones, agregué un criterio de código contable y funciona bien. Ahora intenté agregar un criterio de nombre de la cuenta, es decir las que contengan "costos" lo hice "Costo???" y no me funcionó. ¿Cómo lo podría solucionar?

Jorge Dunkelman 09 noviembre, 2013 18:25  

Sin ver el cuaderno es un poco difícil hacerme una idea, pero hay que prestar atención a la lógica de los criterios. Si ambos crtierios tiene que cumplirse, deben ir en la misma fila en el área de los criterio (AND); si es sufieciente que se cumpla uno de los criterios, irán en filas separadas (OR).
Puedes ver lo que pongo esta nota.

OP_YODA 21 noviembre, 2013 02:38  

Hola Jorge, tengo una duda (posiblemente trivial, pero me tiene loco) estoy calculando una tabla de amortización manualmente y requiero saber cúanto tiempo ha pasado desde el útlmo pago hasta el siguiente pago. Ejemplo:
Fecha Saldo Ant Cuota Real Capital Interes
31/07/2013 30.000,00 - - -
31/08/2013 30.000,00 - - -
30/09/2013 30.000,00 2.200,00 2.200,00 -
31/10/2013 27.800,00 - - -
30/11/2013 27.800,00 - - -
31/12/2013 27.800,00 2.200,00 2.200,00 -
31/01/2014 25.600,00 - - -
28/02/2014 25.600,00 - - -
31/03/2014 25.600,00 2.200,00 2.200,00 -
30/04/2014 23.400,00 - - -
31/05/2014 23.400,00 - - -
30/06/2014 23.400,00 2.200,00 2.200,00 -

Tengo que hallar una manera de determinar que desde el 30/09/2013 hasta el 31/12/2013 han pasado 3 períodos, que son la cantidad de filas que hay entre un renglón y otro.

¿Me puedes dar luz?

Jorge Dunkelman 21 noviembre, 2013 07:30  

OP_YODA, el objeivo de los comentarios es que estén relacionados con el tema de la nota. Las consultas es preferible dirigirlas a alguno de los muchos y buenos foros que hay en la red. De todas maneras mi respuesta a tu consulta es:

1 - si el cáclulado tiene que estar ligado a la distancia entre las fila, usar la fórmula =FILA(A10)-FILA(A7), donde A10 y A7 son las celdas que contienen las fechas;

- si el cálculo tiene que estar ligado a los meses, =MES(A10)-MES(A7)

Marco Antonio Gonzalez 22 noviembre, 2013 21:27  

Buenas Estimado
Excelente herramienta, he podido reproducir con exito el ejercicio, pero quise ver tambien el archivo que publicas, mas, no lo puedo abrir (tengo excel 2010).
saludos... excelenete blog

Jorge Dunkelman 23 noviembre, 2013 10:14  

Acabo de probar el enlace y veo que funciona. Trata de descargarlo nuevamente. Si no lo lograss, ponte en contacto conmigo por mail privado (la dirección en el enlace Ayuda).

OP_YODA 23 noviembre, 2013 20:11  

Gracias por tu respuesta Jorge, y pido disculpas por el off-topic

Marco Antonio Gonzalez 25 noviembre, 2013 13:52  

Gracias por la respuetsa estimado, se trataba de un problema de seguridad del firewall de windows que hacía que office no me dejara abrir archivos provenientes de fuentes "no seguras".
saludos y gracias nuevamente.

nestor garcia 25 noviembre, 2013 16:05  

Gracias por los tutoriales antes de nada. Tengo una consulta referente a este tema. Al asignar un nombre a la celda con fecha, el filtro avanzado no me funciona. He podido comprobar que es debido al formato, lo coge como texto. No sé como resolverlo.
Gracias

nestor garcia 25 noviembre, 2013 19:19  

Al aplicar el filtro, poniendo dentro de la condicional la fecha, filtra bien la tabla. El problema me viene cuando nombro la celda e incluyo ese nombre en la condicional. En este caso ya no me filtra se queda la tabla vacía.

Jorge Dunkelman 25 noviembre, 2013 20:45  

¿Podrías enviarme el libro para darle un vistazo? La dirección del mail aparece en el enlace Ayuda (en la parte superior de la plantilla).

mizqui 26 marzo, 2014 19:28  

En el rango de filtro pongo la fecha en sistema americano >=MM/DD/AA y funciona
la seleccion de fechas. Saludos

Anónimo,  07 mayo, 2015 04:21  

Francisco
Muy buenas noches señor Jorge. Muy bien explicado. Mi duda es:
Como puedo filtrar unos datos cuyas filas estan identificadas por unos colores. Lo que debe hacer el filtro es generarme los mismos datos pero quitando las fila que el fondo es rojo.
He buscado por filtro automatico, avanzado y no he podido. Hay una opción que es la de color, pero funciona por un solo color y toma es que sea igual a ese color. Lo que necesito es que filtre y muestre las que no sean rojas.
Nota: No se necesita tomar toda la fila. Se puede Tomar solo el Rango "A1:A122"
De antemano muchas gracias por la atencion prestada.
Espero me hallas entendido.

Jorge Dunkelman 09 mayo, 2015 11:54  

Francisco, es muy mala práctica usar colores como datos (o "meta-datos"). Los colores sirven para enfatizar o llamar la atención sobre algunos datos, pero para realizar cálculos u operaciones con nuestros datos debemos usar datos, no colores.
Supongo que los colores que usas representan alguna característica del dato que puede ser identificada por Excel (por ejemplo, números mayores de 1000) y es esta característica la que tienes que usar para realizar el filtrado.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP