Cree un informe de estilo de tabla dinámica dinámica con una fórmula

Históricamente, hemos tenido dos formas básicas de crear informes en Excel. Podríamos ingresar las etiquetas del informe y usar fórmulas para calcular los valores del informe o podríamos usar una tabla dinámica. Ambas opciones tenían pros y contras. Tendríamos que elegir el tipo de informe según el contexto de nuestro libro de trabajo. En esta publicación, te mostraré una nueva opción que usa una matriz dinámica (DA). Específicamente, escribiremos una fórmula DA con las nuevas funciones VSTACK y HSTACK para crear un informe dinámico estilo tabla dinámica que actualiza automáticamente los valores y se expande para incluir nuevos elementos. ¡No es necesario actualizar manualmente!

Descripción general basada en fórmulas frente a tabla dinámica

Antes de comenzar, retrocedamos un segundo y recapitulemos los pros y los contras clave de los informes tradicionales basados ​​en fórmulas y las tablas dinámicas. Necesitamos comprender estos detalles para poder apreciar plenamente la belleza de la nueva solución que permite VSTACK/HSTACK.

Informes basados ​​en fórmulas

Un método para crear informes es ingresar las etiquetas del informe en celdas y luego escribir fórmulas que calculen los valores del informe. Dado que los valores del informe se calculan con fórmulas, se recalculan automáticamente cada vez que cambian los valores de las celdas dependientes. Pero el problema es que no se expanden automáticamente para incluir ningún elemento nuevo que se encuentre en la fuente de datos actualizada. Es decir, si se necesita una nueva etiqueta de informe (que se encuentra en los datos subyacentes), deberá insertar manualmente una nueva fila de la hoja de trabajo en su informe, ingresar la nueva etiqueta y luego escribir o completar la fórmula para calcular el valor. La imposibilidad de expandirse automáticamente para incluir nuevos elementos (nuevas filas de etiquetas de informe) ha sido una gran decepción. Es por eso que las tablas dinámicas han sido una alternativa tan sorprendente a los informes basados ​​en fórmulas.

Informes de tabla dinámica

Los informes de tabla dinámica ajustan automáticamente su tamaño para adaptarse a cualquier elemento nuevo encontrado. Es decir, una tabla dinámica insertará automáticamente nuevas filas de informe para cualquier elemento nuevo que se encuentre en los datos subyacentes. Esto es asombroso y una de las muchas razones por las que los usuarios de Excel adoran las tablas dinámicas. El único pequeño problema es que los usuarios deben recordar hacer clic manualmente en Actualizar después de cambiar los datos de origen en el libro.

Opciones

Y estas han sido nuestras dos opciones básicas durante literalmente décadas. Tendríamos que elegir entre estas dos opciones según nuestro libro de trabajo. Por un lado, podríamos crear un informe basado en fórmulas que recalcule los valores con las manos libres… pero… necesitaríamos insertar manualmente nuevas filas del informe y completar fórmulas. Por otro lado, podríamos crear una tabla dinámica que incluya automáticamente nuevos elementos… pero… actualizaríamos manualmente.

Durante años he deseado lo mejor de ambos mundos. Un informe basado en fórmulas que recalcularía automáticamente los valores y se expandiría dinámicamente para incluir nuevas etiquetas (elementos) de informe.

Bueno amigos, ¡este deseo finalmente se ha hecho realidad!

Con la introducción de VSTACK y HSTACK, podemos crear dicho informe. Probablemente, el reportaje más bonito de todos. Y repasaré todos los detalles en esta publicación. Vamos.

Descargo de responsabilidad: no estoy sugiriendo que dejemos de usar tablas dinámicas o que las funciones DA puedan reemplazar todas las tablas dinámicas. Para que conste, me encantan las tablas dinámicas. Tampoco estoy diciendo que el enfoque presentado no tenga desventajas. Lo que digo es que evita dos de las desventajas mencionadas anteriormente, principalmente, los informes de fórmulas no incluyen elementos nuevos y es necesario actualizar manualmente un PT después de cambiar los valores de la tabla. Sin embargo, quería hablar sobre VSTACK/HSTACK con esta configuración para que podamos ver una forma realmente interesante de aplicarlos. Es solo una opción más que puede ayudar en algunos de nuestros informes

Objetivo

A continuación se muestra una captura de pantalla de un informe tradicional basado en fórmulas.

Para crearlo, ingresé manualmente las etiquetas del informe y luego usé la función SUMIFS para crear los valores. La ventaja es que cualquier transacción nueva en mi tabla de datos se incluye automáticamente en el informe siempre que su etiqueta se encuentre en el informe. La desventaja es que cuando hay un elemento nuevo, como ZZ400, necesitamos insertar una nueva fila de la hoja de trabajo, escribir la nueva etiqueta y luego escribir o completar la fórmula para calcular su valor.

Por otro lado, aquí está la versión de tabla dinámica de ese informe:

Para crearlo, utilicé Insertar Tabla dinámica y agregué los campos de elementos a las filas y el campo de cantidad a los valores. La ventaja es que todas las transacciones nuevas se reflejan en este informe cuando lo actualizo, incluso si se encuentra una nueva etiqueta de informe (elemento de fila). La desventaja es que debo recordar actualizar manualmente el informe. Esto no lleva mucho tiempo, pero es un paso que debo recordar y, si lo olvido, el informe será inexacto.

Ahora bien, aquí hay un informe del DA que combina estas dos ventajas sin heredar las dos desventajas.

Incluirá automáticamente nuevas etiquetas de fila como una tabla dinámica y actualizará sus valores con las manos libres (sin actualización manual) como un informe basado en fórmulas. De hecho, es un informe basado en fórmulas, pero utiliza matrices dinámicas y las funciones VSTACK/HSTACK.

Video

Narrativo

¿Listo para comenzar? Hay algunas funciones que usaremos:

1. HSTACK
2. VSTACK
3. DEJAR

Haremos que estos funcionen juntos para crear nuestro hermoso informe. ¡Vamos a hacerlo!

Nota: no todas las versiones de Excel incluyen estas funciones. La forma más rápida de determinar si su versión de Excel los admite es escribir =VS en cualquier celda en blanco. Si VSTACK aparece en la lista de autocompletar, ¡entonces los tienes! Actualmente está disponible solo a través de una suscripción a Office 365 y las mejoras se implementan según su canal de actualización (el canal Insiders recibe las actualizaciones primero). Puede controlar el canal de actualización yendo a Archivo Cuenta Opciones de actualización. Si está en una red corporativa, es posible que su equipo de TI pueda ayudarlo.

HSTACK

El propósito de HSTACK es combinar columnas de datos una al lado de la otra. En el caso de nuestro informe Beautiful, primero lo usaremos para crear los tres componentes principales de nuestro informe. Pensaremos en escribir fórmulas individuales para crear la fila del encabezado, el cuerpo y la fila total.

Comenzando con la fila del encabezado, combinamos las etiquetas del informe ItemNum y Amount de esta manera:

=HSTACK("Núm de artículo", "Cantidad")

Pasando al cuerpo del informe, completamos la columna de etiqueta del informe utilizando las funciones ORDENAR y ÚNICA. Estos ordenarán los valores únicos que se encuentran en la columna ItemNum de nuestra tabla de datos.

= ORDENAR (ÚNICO (tbl_data [Núm de artículo]))

La segunda columna del cuerpo completará los valores de nuestra columna Monto. Para hacer eso, usaremos la función SUMIFS para sumar las cantidades en la columna Amt.

=SUMIFS(tbl_data[Amt],tbl_data[ItemNum],E8#)

A continuación, creamos la fila Gran Total usando la función HSTACK nuevamente.

=HSTACK("Total general",SUM(tbl_data(Amt)))

El resultado de estas cuatro fórmulas individuales nos acercará al informe deseado:

Estamos cerca, pero necesitamos encontrar una manera de unirlos. De lo contrario, nuestras tablas no se expandirán automáticamente porque nuestras fórmulas existentes estarán en el camino.

Ahí es cuando pasamos a nuestra segunda función, VSTACK.

VSTACK

Mientras que HSTACK combina columnas de datos una al lado de la otra, VSTACK combina tablas verticalmente. Lo usaremos para apilar los encabezados de nuestras filas (E7#), los cuerpos de ambas columnas del informe (E8#,F8#) y luego nuestra fila total (E18#).

=VPILA(E7#,HPILA(E8#,F8#),E18#)

Puede ver cómo los argumentos de la fórmula corresponden a los rangos calculados anteriormente aquí:

Ahora que hemos descubierto nuestro enfoque general (calcular las regiones del informe separadas y luego VSTACK/HSTACK juntas), queremos eliminar las referencias auxiliares y escribir una fórmula única que se mantenga por sí sola.

Una opción es reemplazar las referencias de derrame en nuestra fórmula (E7#, E18#, etc.) con la función subyacente. Es decir, podemos copiar las fórmulas individuales de las celdas auxiliares y pegarlas en nuestra nueva fórmula VSTACK. Este es el resultado final:

=VSTACK(HSTACK("ItemNum","Cantidad"),HSTACK(ORDENAR(ÚNICO(tbl_data[ItemNum])),SUMIFS(tbl_data[Amt],tbl_data[ItemNum]),ORDENAR(ÚNICO(tbl_data[ItemNum])) ,HSTACK("Total general",SUM(tbl_data[Amt]))

Ahora que nuestra fórmula ya no necesita las referencias auxiliares, podemos eliminar el informe temporal que hicimos y bam:

Sin embargo, como puede ver, todavía nos queda una fórmula bastante larga (y difícil de mantener).

Podemos usar LET para agruparlo todo en un contenedor y mantener todo organizado (y más fácil de actualizar y mantener con el tiempo).

Poniéndolo junto con LET

La función LET nos permite asignar nombres y luego hacer referencia a los nombres en el argumento de cálculo final. En nuestro caso, podemos asignar la letra “r” a la expresión que devuelve las etiquetas de nuestras filas, “v” a la expresión que calcula los valores de nuestro informe, “encabezados” a la función que crea los encabezados del informe, “cuerpo” a la Función HSTACK(r,v) que proporciona el cuerpo de nuestro informe y pie de página de la función que devuelve la fila total. El argumento final utiliza la función VSTACK para combinar los encabezados, el cuerpo y el pie de página. La fórmula completa se ve así:

=LET(r,ordenar(ÚNICO(tbl_data[ItemNum])),v,SUMIFS(tbl_data[Amt],tbl_data[ItemNum],r),encabezados, HSTACK("ItemNum","Cantidad"),cuerpo, HSTACK (r, v),pie de página, HSTACK("Total general",SUM(tbl_data[Amt])),VSTACK (encabezados, cuerpo, pie de página))

Le damos enter y bam:

Formato condicional

El último paso en la creación de nuestro hermoso informe es usar formato condicional para crear un borde entre los valores en el cuerpo de nuestra tabla y la sección Gran Total.

En este caso, estableceremos una regla que dice: aplique un borde de celda superior si el valor en la columna B es igual a la palabra “Total general”. Esto permitirá que el borde de la celda cambie dinámicamente junto con los valores de la celda a medida que actualizamos el informe.

Conclusión

Con HSTACK y VSTACK, hemos podido solucionar los dos inconvenientes identificados de los informes tradicionales basados ​​en fórmulas y las tablas dinámicas. Ahora tenemos lo mejor de ambos mundos: un hermoso informe manos libres que recalcula automáticamente los valores y se expande dinámicamente para incluir nuevos elementos. ¡No más pasos adicionales ni tener que hacer clic en actualizar!

Esta es una aplicación bastante interesante de matrices dinámicas y ayuda a superar dos problemas clave con los informes tradicionales. Déjame saber lo que piensas al publicar un comentario a continuación… ¡Gracias!

Archivo de muestra

Hermosa.xlsxDescargar