Otra técnica para "aplanar" tablas de datos

Hace un poco más de dos años atrás publiqué una nota sobre cómo crear una tabla sumario en Excel. La idea es "aplanar" una matriz de datos de manera que pueda ser usada como base de datos para tablas dinámicas.

¿Qué significa aplanar en este caso? Veamos este ejemplo de un cuadro de ventas de productos por meses

Para poder aprovechar eficientemente las posibilidades de las tablas dinámicas, tendríamos que organizar estos mismos datos de esta manera:

Si usamos Excel 2010 o 2013 podemos usar, además de la técnica que mostré en la nota anterior, la nueva herramienta Power Query (anteriormente, Data Explorer).

El primer paso consiste en descargar e instalar el complemento.

Luego convertimos nuestra matriz de datos en Tabla

Seleccionamos la tabla y en el menú de Power Query usamos la opción "From Table"

para introducirla en la ventana de Power Query

Ahora viene la parte menos evidente del proceso (esperemos que Microsoft lo mejore en el futuro): en la ventana del Query seleccionamos las columnas (clic al encabezamiento de la columna manteniendo el botón Ctrl apretado) y abrimos el menú contextual con un clic del botón derecho; en el menú hacemos un clic en la opción "Unpivot Columns"

con este resultado

Ahora apretamos el botón "Done" en el editor del Query. Excel crea una nueva hoja con el resultado de Query

Todo los que nos queda por hacer es reemplazar los encabezamientos de las columnas que el Query creó ("Mes" en lugar de "Attribute" y "Ventas" en lugar de "Value", en nuestro ejemplo).

La gran ventaja de este método sobre el anterior, es que podemos actualizar el Query en caso de hacer cambios en la tabla de origen. Por ejemplo, si agregamos una línea para el nuevo Producto 11 a la tabla de datos, todo lo que tenemos que hacer es usar la opción "Refresh" en la opción Query de la cinta de opciones

El único inconveniente es que debemos volver a reemplazar los encabezamientos ya que Excel vuelve a poner los valores por defecto (Attribute y Value).

Si agregamos una nueva columna a la tabla, digamos el mes de Mayo, tendremos que rehacer el Query, ya que en este caso la nueva columna aparecerá como tal también en el Query (no ha sido incluida en el proceso Unpivot Column).

Informes con Filtro Avanzado de Excel

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

Por qué y cómo usar la nueva función AGREGAR

Estaba leyendo una discusión en uno de los foros de Excel sobre el uso de la combinación de funciones =SI(ESERROR(fórmula1),fórmula2,fórmula1) que opuesto a la nueva función (desde Excel 2007) SI.ERROR(fórmula1,fórmula2).

La primera forma es muy, pero muy, ineficiente ya que primero tiene que calcular fórmula1, luego evaluar si da error y si no da error volver a calcularla.
SI.ERROR resuelve este problema, pero sólo para quien haya avanzado a Excel 2007 posteriores.

Personalmente trato de evitar el uso de SI.ERROR ya que en ciertas ocasiones puede ocultar errores que si quisiéramos detectar (por ejemplo: #REF!).

Una situación similar es cuando queremos hacer operaciones con un rango de valores que contiene errores. En el pasado he mostrado algunas técnicas para enfrentarse con estas situaciones, pero a partir de Excel 2010 tenemos una nueva herramienta: la función AGREGAR.

Esta nueva función es una especie de SUBTOTALES potenciada. AGREGAR viene provista con 19 funciones (como puede verse en la ayuda en línea de Excel)

siete opciones (qué omitir y qué no)

Esta función viene en dos formas: forma de referencia y forma matricial. Un detalle a tener en cuenta es que esta función no está diseñada para agregar rangos horizontales.