Despivotar una tabla cruzada con fórmulas: SCAN+GROUPBY vs TEXTSPLIT+TOROW

Un miembro comparte un fichero con datos en formato tabla cruzada: 4 proyectos en filas, 12 meses en columnas, cada mes con dos sub-columnas (Presupuesto y Real). Necesita transformarlo a formato plano (una fila por cada combinación proyecto-mes-tipo) para poder trabajar con los datos en tablas dinámicas o gráficos. El clásico "unpivot" que en Power Query se resuelve con un clic, pero ¿se puede hacer solo con fórmulas?

La comunidad aporta dos enfoques muy diferentes, incluidos en los dos ficheros adjuntos.

Gerson usa SCAN + TOCOL + AGRUPARPOR. El primer reto es que las cabeceras de mes están en celdas combinadas: "Enero" ocupa dos columnas (Pto y Real), y las celdas intermedias están vacías. SCAN resuelve esto propagando el último valor no vacío:

``
=SCAN(""; B2:Z2; LAMBDA(a; v;
SI(v = ""; a; v)
))
`

Con las cabeceras reconstruidas, se usa TOCOL para aplanar la matriz eliminando vacíos, y una función LAMBDA reutilizable para aplicar la misma transformación a cada columna:

`
=LET(
v; SCAN(""; B2:Z2; LAMBDA(a; v; SI(v = ""; a; v)));
L; LAMBDA(x; TOCOL(SI(v; x; z); 2));
APILARH(
L(A3:A6);
L(C2:Z2);
L(v);
L(C3:Z6)
)
)
`

Después AGRUPARPOR puede re-agrupar el resultado según necesidad.

El segundo enfoque genera todas las combinaciones "a la fuerza" usando operaciones de texto con UNIRCADENAS + DIVIDIRTEXTO + TOCOL + ENFILA:

`
=APILARH(
DIVIDIRTEXTO(
UNIRCADENAS(","; ;
TOCOL(B4:B7 & "|" & ENFILA({"Pto";"Real"} & SECUENCIA(12)))
);
"|"; ","
);
TOCOL(C4:Z7)
)
`

El truco: TOCOL(B4:B7 & "|" & ENFILA(...)) genera el producto cartesiano de proyectos × tipos-mes como texto concatenado. UNIRCADENAS los une y DIVIDIRTEXTO los separa de nuevo en columnas. TOCOL(C4:Z7)` aplana los valores en el mismo orden. Resultado: una tabla despivotada de 96 filas (4 proyectos × 12 meses × 2 tipos).

Miki también sugiere Power Query como alternativa cuando los datos son más complejos o voluminosos.

El problema: unpivot sin Power Query

Un miembro llegó con un fichero en formato tabla cruzada: 4 proyectos en las filas, 12 meses en las columnas, y cada mes partido en dos sub-columnas (Presupuesto y Real). Para poder explotarlo con tablas dinámicas o gráficos necesitaba lo contrario: formato plano, una fila por cada combinación de proyecto, mes y tipo.

Es el clásico unpivot (anular dinamización), que en Power Query se resuelve con un par de clics. La pregunta era otra: ¿se puede hacer solo con fórmulas? La comunidad respondió con dos enfoques radicalmente distintos.

El obstáculo previo: las cabeceras combinadas

Antes de despivotar nada hay que resolver un problema de partida. Las cabeceras de mes están en celdas combinadas: "Enero" ocupa dos columnas (Pto y Real) y las celdas intermedias están vacías. Sin arreglar eso, cualquier intento de cruzar mes con valor falla.

Gerson lo resuelve propagando el último valor no vacío con SCAN:

=SCAN(""; B2:Z2; LAMBDA(a; v;
    SI(v = ""; a; v)
))

Se lee muy bien: recorre la fila de cabeceras arrastrando un acumulador a. Si la celda actual v está vacía, repite el acumulador; si tiene contenido, lo actualiza. Es el equivalente exacto del "Rellenar hacia abajo" de Power Query, en una fórmula.

Enfoque 1: SCAN + TOCOL + AGRUPARPOR

Con las cabeceras reconstruidas, Gerson aplana la matriz:

=LET(
    v; SCAN(""; B2:Z2; LAMBDA(a; v; SI(v = ""; a; v)));
    L; LAMBDA(x; TOCOL(SI(v; x; z); 2));
    APILARH(
        L(A3:A6);
        L(C2:Z2);
        L(v);
        L(C3:Z6)
    )
)

Lo elegante aquí es la variable L: dentro de un LET se define una función reutilizable con LAMBDA, y luego se aplica cuatro veces, una por cada columna del resultado (proyecto, tipo, mes, valor). TOCOL aplana cada bloque a una sola columna y APILARH las pega en horizontal. Después AGRUPARPOR puede re-agrupar el resultado si hace falta.

Enfoque 2: el producto cartesiano por texto

El segundo enfoque genera todas las combinaciones "a la fuerza", tirando de operaciones de texto:

=APILARH(
    DIVIDIRTEXTO(
        UNIRCADENAS(","; ;
            TOCOL(B4:B7 & "|" & ENFILA({"Pto";"Real"} & SECUENCIA(12)))
        );
        "|"; ","
    );
    TOCOL(C4:Z7)
)

El truco está en TOCOL(B4:B7 & "|" & ENFILA(...)): concatenar un rango vertical (los proyectos) con uno horizontal (los tipos-mes) genera el producto cartesiano de ambos, con la barra vertical como separador. UNIRCADENAS lo une todo en un único texto gigante y DIVIDIRTEXTO lo vuelve a separar en columnas, usando la barra para las columnas y la coma para las filas. TOCOL(C4:Z7) aplana los valores en ese mismo orden.

Resultado: una tabla despivotada de 96 filas (4 proyectos por 12 meses por 2 tipos).

Funciones clave

  • SCAN: recorre un rango arrastrando un acumulador. La forma de rellenar hacia abajo las cabeceras combinadas con una fórmula.
  • TOCOL (ENCOL): aplana una matriz en una sola columna. Su segundo argumento controla qué ignorar: los vacíos, los errores o ambos.
  • LET con LAMBDA: define una función local reutilizable dentro de la propia fórmula, sin ensuciar el administrador de nombres.
  • ENFILA y SECUENCIA: generan el vector horizontal que produce el cruce cartesiano.
  • UNIRCADENAS y DIVIDIRTEXTO: el par que permite construir y deshacer combinaciones vía texto.
  • APILARH: pega las columnas resultantes en la tabla final.

Cuál usar

El enfoque de SCAN es el más mantenible: cada pieza hace una cosa y se entiende leyéndola. El del producto cartesiano es más ingenioso, pero pasar los datos por texto y volver a partirlos tiene un límite: DIVIDIRTEXTO no procesa cadenas indefinidamente largas, así que con volúmenes grandes acabará fallando.

Y aquí conviene ser honesto, como apuntó Miki en el hilo: cuando los datos son voluminosos o la estructura se complica, Power Query sigue siendo la herramienta adecuada. Estas fórmulas brillan cuando quieres que el resultado se actualice solo, sin refrescar una consulta.

Conclusión

Despivotar con fórmulas es perfectamente posible, y por dos caminos muy distintos: uno reconstruyendo las cabeceras y aplanando por bloques, otro fabricando todas las combinaciones por texto. Comparar ambos enseña más sobre matrices dinámicas que cualquier tutorial suelto, y es lo que sale cada semana en la comunidad de Influexcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel