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í.
Hola Jorge;
ResponderBorrarUna curiosidad creo que te haz confundido con el cuaderno con el ejemplo a descargar no coincide con el del tutorial.
Gracias
Atentamente:
Isabel
Hola Isabel, efectivamente, he corregido el enlace.
ResponderBorrarGracias por la observación.
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.
ResponderBorrarPD: El excel que manejo es el 2007.
Enhorabuena por el blog!
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!
ResponderBorrarEstimado,
ResponderBorraren 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.
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?
ResponderBorrarSin 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).
ResponderBorrarPuedes ver lo que pongo esta nota.
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:
ResponderBorrarFecha 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?
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:
ResponderBorrar1 - 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)
Buenas Estimado
ResponderBorrarExcelente 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
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).
ResponderBorrarGracias por tu respuesta Jorge, y pido disculpas por el off-topic
ResponderBorrarGracias 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".
ResponderBorrarsaludos y gracias nuevamente.
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.
ResponderBorrarGracias
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.
ResponderBorrar¿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).
ResponderBorrarEn el rango de filtro pongo la fecha en sistema americano >=MM/DD/AA y funciona
ResponderBorrarla seleccion de fechas. Saludos
Francisco
ResponderBorrarMuy 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.
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.
ResponderBorrarSupongo 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.
Estimado Jorge, gracias por la información, me ha sido muy útil.
ResponderBorrar¿Hay alguna manera de que los datos se actualicen sin necesidad de aplicar el proceso completo de elaboración del Filtro Avanzado? (Algo como el botón "Actualizar" en tablas dinámicas).
De antemano, muchas gracias!
Si, se podría hacer usando un evento (macro). Si no estás familiarizado con el tema de macros/eventos, te sugiero que veas las notas sobre el tema apretando la etiqueta "Eventos" en la nube etiquetas (en la columna derecha del blog).
ResponderBorrar