Han pasado ocho días desde que se anunciaron las fórmulas de matriz dinámica en la conferencia Ignite 2018 en Orlando. Esto es lo que he aprendido:
- Los arreglos modernos se anunciaron en Ignite el 24 de septiembre de 2018 y se denominaron oficialmente Dynamic Arrays.
- Escribí un libro electrónico de 60 páginas con 30 ejemplos de cómo usarlos, y lo ofreceré gratis hasta finales de 2018.
- La implementación va a ser mucho más lenta de lo que todos quieren, lo cual es frustrante. ¿Por qué es tan lenta? El equipo de Excel ha realizado cambios en el código de Calc Engine que se ha mantenido estable durante 30 años. De particular preocupación: con complementos que inyectan fórmulas en Excel que inadvertidamente utilizaron una intersección implícita. Esos complementos se romperán si Excel ahora devuelve un rango de Derrame.
- Hay una nueva forma de referirse al rango devuelto por una matriz:
=E3#pero aún no tiene nombre. El # se llama Operador de fórmula derramada . ¿Qué opinas de un nombre como Spill Ref (sugerido por el MVP de Excel Jon Acampora) o The Spiller (sugerido por MVP Ingeborg Hawighorst)?
Como coautor de Pivot Table Data Crunching, me encanta una buena tabla dinámica. Pero, ¿qué sucede si necesita que sus tablas dinámicas se actualicen y no puede confiar en que el gerente de su gerente haga clic en Actualizar? La técnica descrita hoy ofrece una serie de tres fórmulas para reemplazar una tabla dinámica.
Para obtener una lista ordenada de clientes únicos, utilice =SORT(UNIQUE(E2:E564))en I2.
Para colocar el producto en la parte superior, utilícelo =TRANSPOSE(SORT(UNIQUE(B2:B564)))en J1.
Aquí hay un problema: no sabe qué tan alta será la lista de clientes. No sabe qué tan amplia será la lista de productos. Si se refiere a I2 #, el Spiller se referirá automáticamente al tamaño actual de la matriz devuelta.
La fórmula para devolver la zona de valores de la tabla dinámica es una sola fórmula de matriz en J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).
En inglés, esto dice que desea agregar los ingresos de G2: G564 donde los Clientes en E coinciden con el cliente de la fila actual de la fórmula de matriz I2 y los productos en B coinciden con la columna actual de la fórmula de matriz en J1.
¿Y si cambian los datos subyacentes? Agregué un nuevo cliente y un nuevo producto cambiando estas dos celdas en la fuente.
El informe se actualiza con nuevas filas y nuevas columnas. La referencia de rango de matriz de I2 # y J1 # maneja la fila y columna adicionales.
¿Por qué funciona SUMIFS? Este es un concepto en Excel llamado Difusión. Si tiene una fórmula que se refiere a dos matrices:
- La matriz uno es (27 filas) x (1 columna)
- La matriz dos es (1 fila) x (3 columnas)
- Excel devolverá una matriz resultante que es tan alta y ancha como la parte más alta y ancha de las matrices referenciadas:
- El resultado será (27 filas) x (3 columnas).
- Esto se denomina matrices de difusión.
Ver video
Descargar archivo de Excel
Para descargar el archivo de Excel: reemplace-a-pivot-table-with-3-dynamic-array-formulas.xlsx
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Mantenga sus datos cerca y sus hojas de cálculo más cerca"
Jordan Goldmeier
