De tabla plana a árbol con una sola fórmula: ANIDAR y las matrices anidadas de Excel

Con las listas y matrices en celda de la beta de Excel, una LAMBDA recursiva, ANIDAR, convierte una tabla plana en un árbol por niveles, con los totales de cada rama. Te lo explico paso a paso, con las fórmulas probadas y el fichero descargable.

El problema: una tabla plana que quieres ver por niveles

Casi todo lo que exportas de un sistema llega igual: una tabla plana, una fila por movimiento, con las claves repetidas en cada fila.

| Región | Cliente | Mes | Importe | Unidades |
|---|---|---|---|---|
| Norte | Acme | Ene | 120 | 12 |
| Norte | Beta | Ene | 80 | 8 |
| Norte | Acme | Feb | 45 | 5 |
| Sur | Ciro | Ene | 200 | 20 |
| Sur | Acme | Feb | 30 | 3 |
| Norte | Beta | Feb | 10 | 1 |
| Sur | Ciro | Feb | 15 | 2 |
| Norte | Acme | Ene | 5 | 1 |

Y lo que quieres ver es un árbol: cada región con sus clientes dentro, cada cliente con sus meses, y los importes al final de cada rama. Lo que en programación sería un JSON anidado:

``
{"Norte": {"Acme": {"Ene": [120, 5], "Feb": [45]}, "Beta": {...}}, "Sur": {...}}
`

Hasta ahora Excel no podía guardar eso: una celda tenía un valor y una fórmula que devolvía varios los desbordaba. Con las listas y matrices en celda, que Microsoft anunció el 24 de septiembre en la beta de Microsoft 365, una celda ya puede guardar una lista entera, o una tabla, o una tabla de tablas. Y eso cambia lo que se puede hacer con una fórmula.

> Antes de empezar: necesitas la beta de Microsoft 365 y que el libro esté en la versión de compatibilidad 3 (Fórmulas › Opciones de cálculo). Sin eso, todo lo de este artículo da #CALC!.

Un nivel: AGRUPARPOR con llaves

El primer paso es casi un truco. AGRUPARPOR normalmente suma, cuenta o promedia cada grupo. Si en vez de una función de agregación le pasas una LAMBDA que devuelve el grupo entre llaves, cada grupo se queda guardado como una lista en su celda:

`
=AGRUPARPOR(A2:A9; D2:D9; LAMBDA(x; {x}); 0; 0)
`

| Región | Importes |
|---|---|
| Norte | 120; 80; 45; 10; 5 |
| Sur | 200; 30; 15 |

La segunda columna no es texto: es una lista de verdad. CONTARA sobre la celda de Norte devuelve 5 y SUMA devuelve 260. Los dos últimos argumentos (0; 0) quitan la cabecera y la fila de total que AGRUPARPOR añade por defecto.

Las llaves son la clave de todo. En el vocabulario de Microsoft, envuelven el resultado: en lugar de desbordarlo, lo meten en una sola celda.

N niveles: una LAMBDA que se llama a sí misma

Para dos niveles se podría escribir una fórmula a mano. Para tres, cuatro o los que tenga tu tabla, lo limpio es una función con nombre que se llame a sí misma. La llamamos ANIDAR.

Se crea en Fórmulas › Administrador de nombres › Nuevo, con el nombre ANIDAR y esto en Hace referencia a:

`
=LAMBDA(claves; valores;
SI(COLUMNAS(claves)=1;
AGRUPARPOR(claves; valores; LAMBDA(x; {x}); 0; 0);
LET(primera; TOMAR(claves;; 1);
u; UNICOS(primera);
APILARH(u; MAP(u; LAMBDA(k;
LET(m; primera=k;
ANIDAR(FILTRAR(EXCLUIR(claves;; 1); m); FILTRAR(valores; m)))))))))
`

Y se usa así:

`
=ANIDAR(A2:C9; D2:D9)
`

- claves son las columnas que forman los niveles del árbol, de fuera hacia dentro: aquí Región, Cliente y Mes. Tienen que ser columnas seguidas.
- valores es la columna con lo que queda al final de cada rama: el importe.

Cómo funciona

La función hace siempre lo mismo, un nivel cada vez:

1. Si solo queda una columna de claves, ya está en el último nivel: agrupa con AGRUPARPOR y las llaves, como en el apartado anterior, y para.
2. Si quedan más, toma la primera columna (primera), saca sus valores únicos (u) y, para cada uno, con MAP:
- filtra las filas de esa clave (m),
- quita la primera columna de claves con EXCLUIR,
- y se llama a sí misma con lo que queda.
3. APILARH pone cada clave al lado de su rama.

Cada llamada baja un nivel y trabaja con menos columnas, hasta que queda una sola. Es recursividad de libro, la misma idea que en cualquier lenguaje de programación.

El resultado

Desplegado con APLANAR para verlo entero en la hoja:

| Región | Cliente | Mes | Importe |
|---|---|---|---|
| Norte | Acme | Ene | 120 |
| | | | 5 |
| | | Feb | 45 |
| | Beta | Ene | 80 |
| | | Feb | 10 |
| Sur | Ciro | Ene | 200 |
| | | Feb | 15 |
| | Acme | Feb | 30 |

Y sin desplegar, cada celda contiene su rama: la celda de Norte guarda la tabla de sus clientes, y cada cliente guarda la de sus meses.

Tres detalles que muerden

- Por qué MAP y no BYROW. MAP envuelve solo lo que devuelve la LAMBDA cuando es una matriz, y así cada rama se queda en su celda. Con BYROW, la recursión da #¡NUM!. Tampoco hay que poner llaves alrededor del MAP: quedaría envuelto dos veces.
- El nombre de dentro tiene que ser el mismo que el de fuera. La función se llama a sí misma por su nombre. Si la guardas como ARBOL, la llamada de dentro también tiene que decir ARBOL(. Si no, llamará a otra función, o a ninguna.
- No uses nombres de variable que parezcan una celda. Escribí la primera versión con una variable llamada k1 y Excel se negaba a guardar el nombre: la leía como la celda K1. Por eso se llama primera.

Y de vuelta: APLANAR

El camino contrario es una sola función: APLANAR deshace el árbol y lo devuelve a tabla. Es la que he usado arriba para enseñar el resultado:

`
=APLANAR(ANIDAR(A2:C9; D2:D9); ""; 10)
`

El segundo argumento es con qué rellenar los huecos (aquí, vacío) y el tercero cuántos niveles bajar. Y las funciones de agregación atraviesan el árbol sin desplegarlo: =SUMA(ANIDAR(A2:C9; D2:D9)) devuelve 505, el total de la tabla.

Con totales en cada rama

Un árbol sin totales se queda corto para un informe. La versión completa de ANIDAR admite un tercer argumento opcional, total. Con VERDADERO, cada bloque empieza con su fila TOTAL y el último nivel muestra la suma en vez de la lista, con tantas columnas de valores como quieras:

`
=APLANAR(ANIDAR(A2:B9; D2:E9; VERDADERO); ""; 10)
`

| Región | Cliente | Importe | Unidades |
|---|---|---|---|
| Norte | TOTAL | 260 | 27 |
| | Acme | 170 | 18 |
| | Beta | 90 | 9 |
| Sur | TOTAL | 245 | 25 |
| | Acme | 30 | 3 |
| | Ciro | 215 | 22 |

Con tres claves, cada nivel repite el patrón y los totales se alinean en la misma columna que los de abajo:

| Región | Cliente | Mes | Importe |
|---|---|---|---|
| Norte | TOTAL | | 260 |
| | Acme | TOTAL | 170 |
| | | Ene | 125 |
| | | Feb | 45 |
| | Beta | TOTAL | 90 |
| | | Ene | 80 |
| | | Feb | 10 |
| Sur | TOTAL | | 245 |
| | Acme | TOTAL | 30 |
| | | Feb | 30 |
| | Ciro | TOTAL | 215 |
| | | Ene | 200 |
| | | Feb | 15 |

La fórmula completa es bastante más larga, porque cada fila de total tiene que tener la misma profundidad que las filas que tiene debajo para que, al desplegar, caigan en su columna:

`
=LAMBDA(claves; valores; [total];
LET(t; SI(ISOMITTED(total); FALSO; total);
primera; TOMAR(claves;; 1);
hoja; COLUMNAS(claves)=1;
SI(t;
LET(g; AGRUPARPOR(primera; valores; SUMA; 0; 0);
SI(hoja; g;
APILARH(TOMAR(g;; 1); MAP(TOMAR(g;; 1); LAMBDA(k;
LET(m; primera=k;
v; FILTRAR(valores; m);
nr; COLUMNAS(claves)-1;
sumas; BYCOL(v; LAMBDA(c; SUMA(c)));
base; APILARH(""; sumas);
interior; SI(2>=nr; base;
REDUCE(base; SECUENCIA(MAX(nr-2; 1));
LAMBDA(a; q; APILARH(""; MAP(SECUENCIA(1); LAMBDA(z; a))))));
cab; SI(nr=1; APILARH("TOTAL"; sumas);
APILARH("TOTAL"; MAP(SECUENCIA(1); LAMBDA(z; interior))));
sub; ANIDAR(FILTRAR(EXCLUIR(claves;; 1); m); v; t);
w; MAX(COLUMNAS(cab); COLUMNAS(sub));
APILARV(EXPANDIR(cab; 1; w; ""); EXPANDIR(sub; FILAS(sub); w; ""))))))));
SI(hoja;
AGRUPARPOR(claves; valores; LAMBDA(x; {x}); 0; 0);
LET(u; UNICOS(primera);
APILARH(u; MAP(u; LAMBDA(k;
LET(m; primera=k;
ANIDAR(FILTRAR(EXCLUIR(claves;; 1); m); FILTRAR(valores; m)))))))))))
`

Sin el tercer argumento se comporta exactamente como la versión corta. Dos cosas que conviene saber:

- ISOMITTED se escribe igual en español. No es «ESOMITIDO»: si te da #¿NOMBRE?, es eso.
- Por qué MAP(SECUENCIA(1); LAMBDA(z; …)). Es la forma de envolver en una celda una expresión: las llaves solo admiten valores escritos, no una expresión con ; dentro.

Lo que no hace

- No lleva los nombres de los campos. Los arrays de Excel no tienen claves con nombre, tienen posiciones. Es un árbol «tipo JSON» en la forma, no en los nombres.
- Ordena alfabéticamente el último nivel, porque ahí agrupa con AGRUPARPOR. Los niveles de arriba respetan el orden en que aparecen en la tabla.
- No es para producción todavía. Mientras las matrices anidadas estén solo en la beta, quien abra tu libro con un Excel normal verá errores. Es para aprender lo que viene, y para tenerlo listo el día que llegue a todos.

Funciones clave

- AGRUPARPOR: agrupa y, con una LAMBDA entre llaves, guarda cada grupo como lista.
- LAMBDA con nombre: permite la recursión, la función llamándose a sí misma.
- MAP: recorre las claves y deja cada rama en su celda.
- TOMAR, EXCLUIR, FILTRAR y UNICOS: separan la primera clave del resto y las filas de cada rama.
- APLANAR`: deshace el árbol y lo devuelve a tabla.

Conclusión

Hace un año, pasar una tabla plana a una estructura por niveles pedía Power Query, una tabla dinámica o VBA. Ahora cabe en una función con nombre de diez líneas, que además se puede recorrer, sumar y aplanar con fórmulas normales.

Todo lo que hace falta para escribirla (LET, LAMBDA, MAP, recursividad) es exactamente lo que se trabaja en el curso de LAMBDA de InflueXcel. Y si te atascas con alguna fórmula, en el grupo de WhatsApp de la comunidad lo resolvemos entre todos.