Reestructurar datos apilados con LAMBDA y PIVOTARPOR

Un miembro de la comunidad comparte un archivo de revisión de Seguridad Social con una tabla amplia (34 columnas x 1540 filas) en formato "ancho" y necesita reestructurarla: extraer grupos de columnas específicos, apilarlos verticalmente y generar un resumen pivotado con subtotales.

Nacho propone una fórmula que combina una LAMBDA reutilizable con PIVOTARPOR para transformar y resumir los datos en un solo paso:

``
=LET(E;ELEGIRCOLS;
base;'datos pila modif'!A1:AH1540;s;SECUENCIA(FILAS(base)-1);
F; LAMBDA(Rcab;LET(_filas;EXCLUIR(ELEGIRCOLS(base;COINCIDIRX(EXCLUIR(Rcab;;1);TOMAR(base;1)));1);APILARH(SECUENCIA(FILAS(_filas);;TOMAR(Rcab;;1);0);EXCLUIR(ELEGIRCOLS(base;COINCIDIRX(EXCLUIR(Rcab;;1);TOMAR(base;1)));1))));
a;APILARV(F(B2:G2);F(B3:G3);F(B4:G4);F(B5:G5));b;a;
res;PIVOTARPOR(E(b;{1;5});E(b;2);E(b;6);SUMA;0;2;1;0;;E(b;6)>0);res)
`

La fórmula funciona así: E es un alias de ELEGIRCOLS para acortar la expresión. F es una LAMBDA que recibe una fila de cabeceras, localiza las columnas correspondientes con COINCIDIRX, las extrae con ELEGIRCOLS y les antepone un identificador de grupo con SECUENCIA + APILARH. Se aplica F a 4 grupos de cabeceras y se apilan con APILARV. El resultado se resume con PIVOTARPOR, agrupando por las columnas clave y sumando valores positivos.

Leo comparte su versión alternativa del archivo con subtotales ya calculados.

Funciones utilizadas: LET, LAMBDA, ELEGIRCOLS, COINCIDIRX, EXCLUIR, TOMAR, APILARH, APILARV, SECUENCIA, FILAS, PIVOTARPOR`.

Más contenido de Excel en InflueXcel