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`.

El problema: 34 columnas que en realidad son cuatro bloques

Un miembro de la comunidad llegó al grupo con un fichero de revisión de Seguridad Social: 34 columnas por 1.540 filas, en el formato ancho clásico de los informes que salen de un sistema externo.

El problema de esos ficheros no es el tamaño, es la estructura. Lo que parece una tabla de 34 columnas son en realidad cuatro bloques repetidos: los mismos conceptos, con las mismas cabeceras, pero para cuatro categorías distintas puestas una al lado de la otra. Mientras estén así, no puedes agrupar, no puedes filtrar por categoría y no puedes hacer un resumen decente.

Lo que necesitaba era doble: reestructurar (extraer cada bloque de columnas y apilarlos en vertical, dejando una tabla larga con una columna que identifique de qué bloque viene cada fila) y luego resumir ese resultado con subtotales. Todo sin macros y sin tocar el fichero original.

La solución de Nacho: una LAMBDA que se aplica cuatro veces

La respuesta combina una LAMBDA reutilizable con PIVOTARPOR para hacer las dos cosas en una sola fórmula:

=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); _filas)
     ));
a; APILARV(F(B2:G2); F(B3:G3); F(B4:G4); F(B5:G5));
res; PIVOTARPOR(E(a;{1;5}); E(a;2); E(a;6); SUMA; 0; 2; 1; 0;; E(a;6)>0);
res)

Larga, pero con una estructura muy clara en cuanto la desmontas.

El alias que ahorra media fórmula

E; ELEGIRCOLS es lo primero que hace el LET, y no es cosmético. ELEGIRCOLS aparece cinco veces en la fórmula; con el alias de una letra, la expresión final cabe en una línea en lugar de tres. Es un truco que conviene tener a mano en cualquier fórmula larga: guarda las funciones que más repites en una variable de LET.

La LAMBDA F: el corazón del asunto

F recibe una fila de cabeceras (un rango pequeño donde tú has escrito qué columnas quieres de ese bloque) y devuelve ese bloque ya extraído y etiquetado. Por dentro hace tres cosas:

  1. COINCIDIRX localiza las columnas. Compara los nombres de cabecera que le pasas contra la primera fila de la tabla base y devuelve las posiciones. Esto es lo que hace la fórmula robusta: no dependes de que las columnas estén en la posición 7, 12 y 19; dependes de que se llamen como se llaman. Si mañana el informe cambia el orden, la fórmula sigue funcionando.
  2. ELEGIRCOLS extrae y EXCLUIR quita la cabecera. Con las posiciones ya resueltas, se sacan las columnas y se descarta la primera fila.
  3. SECUENCIA + APILARH añaden el identificador. Se genera una columna del mismo alto que el bloque, rellena con el mismo valor (el nombre del grupo, que sale del primer elemento del rango de cabeceras) usando un paso de 0. Esa columna se pega delante del bloque. Sin este paso, al apilar los cuatro bloques no habría manera de saber a qué categoría pertenece cada fila.

Apilar y resumir

Con F definida, apilar los cuatro bloques es una línea: APILARV(F(B2:G2); F(B3:G3); F(B4:G4); F(B5:G5)). Cada rango de esos es una fila de cabeceras que el usuario ha preparado en la hoja, así que cambiar qué columnas entran en cada bloque no exige tocar la fórmula, solo esos rangos.

El PIVOTARPOR final agrupa por dos columnas (la 1 y la 5), toma la 2 como campo de columnas y la 6 como valores, suma, y aplica un filtro para quedarse solo con los valores positivos. Los argumentos intermedios controlan totales y orden.

Leo aportó además su propia versión del fichero con los subtotales ya calculados, útil para contrastar el resultado.

Funciones clave

  • LET — nombra resultados intermedios; aquí también sirve para crear alias de funciones y acortar la fórmula.
  • LAMBDA — encapsula la lógica de extraer un bloque para poder aplicarla cuatro veces sin copiar y pegar.
  • COINCIDIRX — localiza columnas por nombre en lugar de por posición, que es lo que hace la solución resistente a cambios.
  • ELEGIRCOLS — extrae columnas concretas de una matriz a partir de sus posiciones.
  • SECUENCIA con paso 0 — genera una columna de valor constante del alto que necesites; el truco para etiquetar bloques.
  • APILARH y APILARV — componen el resultado: horizontal para añadir la etiqueta, vertical para juntar los cuatro bloques.
  • PIVOTARPOR — el resumen final con agrupación, totales y filtro, todo en una llamada.

Conclusión

Lo interesante de este caso no es la longitud de la fórmula, es que hace despivotar y pivotar en el mismo paso, sin Power Query y sin columnas auxiliares en la hoja. Y sobre todo, que la parte que cambia (qué columnas forman cada bloque) está fuera de la fórmula, en rangos de cabeceras que cualquiera puede editar.

Ese es el patrón que merece la pena llevarse: cuando tengas que repetir la misma transformación sobre varios bloques de columnas, escribe una LAMBDA que resuelva un bloque y aplícala tantas veces como haga falta. Casos de reestructuración como este aparecen constantemente en la comunidad de InflueXcel, casi siempre con ficheros que vienen de un sistema que nadie controla.

Más casos con estas funciones

Más contenido de Excel en InflueXcel