Tablas de resumen con matrices dinámicas

Una tabla de resumen condensa una lista grande de filas sin procesar en un bloque pequeño y fácil de leer: una fila por categoría con los totales a su lado. Imagine un registro de ventas con cientos de filas que se convierte en una tabla ordenada donde se muestra cada región y sus ingresos totales.

La forma antigua consistía en una tabla dinámica manual que tenía que actualizar. La forma moderna utiliza fórmulas de matriz dinámica que se actualizan por sí solas en cuanto cambian los datos. Sin botones ni actualizaciones manuales.

En esta lección combinará tres herramientas potentes: UNIQUE para enumerar las categorías, SUMIFS para obtener el total de cada una y FILTER para extraer las filas coincidentes. Juntas crean un resumen actualizado en tiempo real.

Imagine una hoja llamada Sales con tres columnas: Región en la columna A, Producto en la columna B e Importe en la columna C, que ocupan las filas 2 a 200.

Nuestro objetivo es crear un resumen que muestre cada región sin duplicados y sus ventas totales. El primer desafío consiste en obtener una lista limpia de regiones sin escribirlas manualmente, ya que más adelante podrían aparecer regiones nuevas.

• A2:A200 contiene muchos nombres de regiones repetidos, como Este, Oeste, Este y Norte.
• Solo queremos: Este, Oeste y Norte, cada uno una vez.

Esta lista sin duplicados es la base de todo el resumen.

La función UNIQUE toma un rango y devuelve cada valor una sola vez. El resultado se desborda, es decir, una fórmula rellena tantas celdas como valores distintos haya.

Introduzca esto en la celda E2 y la lista de regiones aparecerá automáticamente debajo:

Si más adelante se añade una región nueva a los datos, la lista desbordada crecerá por sí sola. Nunca tendrá que editar la fórmula.

Ahora necesitamos el importe total de cada región en la columna E. SUMIFS suma los valores de un rango únicamente cuando otro rango coincide con una condición.

La estructura es SUMIFS(sum_range, criteria_range, criteria). Coloque esto en F2, junto a la primera región:

La referencia E2# es la clave. El signo # representa todo el rango desbordado desde E2. Por eso, esta única fórmula obtiene el total de todas las regiones que generó UNIQUE.

La referencia de desbordamiento E2# siempre apunta al bloque completo que produjo una fórmula, independientemente de cuánto crezca. Esto es lo que hace dinámico al resumen.

Cuando UNIQUE encuentra 3 regiones, E2# tiene una altura de 3 celdas y SUMIFS devuelve 3 totales. Cuando los datos crecen hasta incluir 5 regiones, ambos rangos se amplían juntos sin editar nada.

• E2 = solo la celda superior.
• E2# = toda la matriz desbordada que comienza en E2.

Familiarícese con el signo #; es el elemento central de las fórmulas de los paneles.

Un resumen se lee mejor cuando los totales están ordenados. Envuelva la lista de regiones en SORT para que las categorías aparezcan alfabéticamente, o bien ordene toda la tabla por total.

Para enumerar las regiones alfabéticamente en E2:

Como los totales de F siguen haciendo referencia a E2#, al ordenar las regiones los totales se realinean automáticamente. Las dos columnas permanecen sincronizadas.

A veces necesita las filas originales de una categoría, no solo un total. FILTER devuelve todas las filas que cumplen una condición y las desborda.

Para mostrar todas las filas de ventas en las que Región es igual al valor de la celda H1:

Si H1 contiene Este, obtendrá todas las filas de Este. Cambie H1 a Oeste y el bloque se reescribirá al instante. Esta es la base de una vista de desglose detallado en un panel.

FILTER produce un error #CALC! cuando no hay coincidencias. Para mantener un resultado limpio, proporcione el tercer argumento opcional como mensaje alternativo.

El tercer argumento se muestra cuando no hay ninguna coincidencia:

Ahora, una región sin ventas muestra una nota clara en lugar de un error. Añada siempre este mensaje alternativo en los paneles para que una selección inesperada nunca rompa el diseño.

Un resumen suele mostrar cuántos pedidos tuvo cada región, no solo el importe. COUNTIFS cuenta las filas que cumplen una condición, igual que SUMIFS, pero sin un rango de suma.

Coloque esto en la columna G, junto a los totales:

Ahora su resumen de tres columnas muestra Región, Ventas totales y Número de pedidos, todo controlado por la única lista de regiones desbordada en E2#. Todo se actualiza al mismo tiempo.

A continuación se muestra la receta completa, una fórmula junto a la otra:

• E2: =SORT(UNIQUE(Sales!A2:A200)) enumera las regiones.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) obtiene el total de cada una.
• G2: =COUNTIFS(Sales!A2:A200, E2#) cuenta cada una.

Solo se introduce la fórmula de E2; F y G se desbordan a partir de la referencia #. Añada una venta nueva en cualquier parte de Sales y las tres columnas se actualizarán sin hacer clic en nada.

Un resumen controlado por fórmulas ofrece ventajas reales frente a escribir valores o actualizar una tabla dinámica:

• En tiempo real: se recalcula en cuanto cambian los datos.
• Tamaño automático: las categorías nuevas aparecen automáticamente mediante UNIQUE y la referencia #.
• Transparente: cualquiera puede leer la lógica en la celda.

La desventaja es que los rangos desbordados necesitan espacio vacío para crecer; veremos los desbordamientos bloqueados en una lección posterior. Por ahora, deje espacio debajo de las fórmulas.

Ponga a prueba lo que ha aprendido sobre la creación de una tabla de resumen que se actualiza sola.

Ha creado una tabla de resumen que se mantiene actualizada por sí sola:

• UNIQUE enumera cada categoría una vez y desborda el resultado.
• SORT ordena esa lista para facilitar su lectura.
• SUMIFS y COUNTIFS obtienen el total y el recuento de cada categoría mediante la referencia de desbordamiento E2#.
• FILTER extrae las filas coincidentes para un desglose detallado, con un mensaje alternativo cuando no hay coincidencias.

Como todas las fórmulas se basan en la lista desbordada, añadir datos nuevos actualiza todo el resumen sin pasos manuales. A continuación recreará informes completos con el estilo de una tabla dinámica utilizando únicamente fórmulas.