Incluir elementos nuevos sin una tabla dinámica

Una de las razones por las que nos encantan las tablas dinámicas es que se expanden automáticamente para incluir nuevos elementos. Por ejemplo, tenemos una tabla dinámica que resume las transacciones por cuenta. Luego, el mes que viene, pegamos nuevas transacciones en la tabla de datos y aparece una nueva cuenta. Para incluir esta nueva cuenta en nuestro informe, todo lo que hacemos es actualizar la tabla dinámica. Bam, automáticamente aparece la nueva cuenta. ¡Y nos ENCANTA absolutamente eso de las tablas dinámicas!

Pero ahora tenemos otra opción: matrices dinámicas y rangos de derrame. Ahora podemos escribir una fórmula que haga que cualquier cuenta nueva se incluya en el rango de derrame. Y podemos usar una función como SUMIFS para agregar la cantidad.

Aunque probablemente todavía usaría una tabla dinámica para esto en la práctica, me estoy divirtiendo mucho explorando posibilidades nuevas e interesantes que ofrecen las matrices dinámicas y los rangos de derrames

Objetivo

Antes de llegar demasiado lejos, retrocedamos y analicemos nuestro objetivo. Tenemos un montón de datos, como este:

Queremos resumirlo por cuenta, así:

Cuando agregamos nuevas transacciones a la tabla de datos, si hay una cuenta nueva, como Ferias comerciales , queremos que se incluya automáticamente en el resumen, así:

Históricamente, esta es exactamente la razón por la que AMAMOS las tablas dinámicas. Porque los nuevos elementos se incluyen automáticamente en el resumen. Los arreglos dinámicos y los rangos de derrame ofrecen otra opción, así que profundicemos.

Narrativo

Crearemos el informe siguiendo estos tres pasos:

• Resumen básico
• Clasificar
• Total arriba

Nota: las matrices dinámicas y los rangos de derrame están disponibles de forma limitada en el momento en que escribo esta publicación. Por lo tanto, es posible que su versión de Excel no tenga estas capacidades. En el momento en que escribo esto, están disponibles para los suscriptores de O365 con el canal Insiders Fast. Si es suscriptor de O365, es posible que pueda cambiar al canal de actualización Insiders Fast. Consulte el sitio web de Microsoft para obtener más información.

Resumen básico

Antes de que funcione el resumen básico, echemos otro vistazo a la tabla de datos.

Los datos se almacenan en una tabla denominada Table1 y podemos hacer referencia a la columna AcctName utilizando una referencia de tabla estructurada como esta: Table1[AcctName] . A continuación, la columna AcctName tiene duplicados. Lo cual tiene sentido porque puede haber muchas transacciones para una cuenta determinada. Nuestro resumen debe contener una línea para cada cuenta.

Entonces, para crear nuestro resumen, nos gustaría comenzar obteniendo una lista única de los nombres de las cuentas. A continuación, necesitamos una forma de agregar la columna de monto por cuenta.

Primero, creemos una columna de cuentas. Podemos lograr esto usando la función ÚNICA, así:

=ÚNICO(Tabla1[NombreCuenta])

La fórmula devuelve múltiples resultados y salen de la celda de fórmula (B6), como se muestra a continuación.

A continuación, podemos usar SUMIFS para crear la columna de resumen. Usamos la referencia de derrame (B6#) como se muestra a continuación:

=SUMIFS(Tabla1[Cantidad], Tabla1[NombreCuenta], B6#)

Cuando le damos enter… bam:

Y en este punto hemos cumplido nuestro objetivo porque cuando agregamos una nueva transacción a la tabla de datos, se incluye en el resumen… incluso si la transacción incluye una nueva cuenta.

Por ejemplo, agregamos una nueva transacción para ferias comerciales a la tabla de datos, así:

Y la nueva cuenta se incluye automáticamente en el resumen, así:

Hay un par de mejoras opcionales que podemos realizar si lo deseamos.

Clasificar

Digamos que queremos ordenar el resumen por nombre de cuenta. Podemos lograr esto envolviendo la función ORDENAR alrededor de la función ÚNICA, así:

=ORDENAR(ÚNICO(Tabla1[NombreCuenta]))

Bam:

¿Qué tal un total? Claro, hagámoslo a continuación.

Total arriba

Como queremos dejar muchas celdas vacías debajo del rango de derrames para dar cabida a cuentas futuras, simplemente pondremos el total encima del detalle. Ruidoso, lo sé. Una función SUMA simple que haga referencia al rango de derrame funcionaría. Algo como esto:

=SUMA(C6#)

Dado que utilizamos la referencia de derrame, C6#, cualquier valor nuevo en el rango de derrame se incluirá en el total.

Y creo que lo tenemos… ¡un resumen basado en fórmulas que incluye automáticamente nuevos elementos!

Si tiene otros consejos divertidos sobre matrices dinámicas o rangos de derrame, compártalos publicando un comentario a continuación… ¡gracias!

Archivo de muestra: NewItemsSpill.xlsx