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í.
