Consolidar datos repetidos con REDUCE, APILARV y PIVOTARPOR

La comunidad aborda un caso de consolidación de datos donde hay tareas con encabezados repetidos que necesitan combinarse dinámicamente. El reto es que pueden venir más tareas o encabezados repetidos, así que la solución debe ser flexible.

Hugo propone una primera aproximación estática con APILARV y APILARH:

``
=APILARV(APILARH(B4:B10;C4:E10);APILARH(B5:B10;F5:H10))
`

Funciona, pero no escala bien cuando los datos crecen.

Leo sugiere el enfoque de UNPIVOT + PIVOTARPOR con función SUMA para obtener un acumulado por cada tarea, aunque advierte que para lo que se pide exactamente hay que hacer "algunas maniobras más".

Nava aporta la solución dinámica completa usando REDUCE + APILARV:

`
=LET(
_data; B5:H10;
REDUCE(J4:M4;
SECUENCIA(FILAS(_data));
LAMBDA(a;v;LET(
r; INDICE(_data;v;);
t; @+r;
p; AJUSTARFILAS(EXCLUIR(r;;1);3);
APILARV(a;SI.ND(APILARH(t;p);t))
))
)
)
`

Nava también advierte que la combinación REDUCE + APILARV` puede verse afectada e incluso quedar inutilizable con datasets grandes, pero para volúmenes moderados funciona perfectamente.

El problema: bloques de columnas que se repiten a lo ancho

La comunidad se enfrenta a una hoja con una estructura muy común y muy incómoda: una columna de tareas y, a su derecha, varios bloques de columnas con los mismos encabezados repetidos. Un bloque por periodo, o por responsable, o por lo que sea que haya ido creciendo hacia la derecha.

Lo que se quiere es consolidar todo eso en una tabla normal, con una fila por combinación. Y con una condición que lo complica: pueden llegar más tareas por abajo y más bloques por la derecha, así que la solución no puede depender de cuántos hay ahora mismo.

Ese requisito es el que descarta la mitad de las soluciones evidentes.

Primer intento: apilar a mano

Hugo arranca con la aproximación directa:

=APILARV(APILARH(B4:B10;C4:E10);APILARH(B5:B10;F5:H10))

Se lee de un vistazo: coge la columna de tareas con el primer bloque, coge la columna de tareas con el segundo bloque, y pon uno debajo del otro.

Y funciona. El problema es que cada bloque nuevo obliga a editar la fórmula, añadiendo otro APILARH con sus rangos exactos. Con dos bloques es asumible; con doce, es una fórmula que nadie querrá tocar y que fallará en silencio el día que alguien inserte una columna.

Aun así, merece la pena empezar por aquí. Escribir la versión estática es la mejor forma de entender qué forma tiene que tener el resultado antes de automatizarlo.

La vía de las tablas dinámicas por fórmula

Leo apunta a otro camino: despivotar los datos y pasarlos por PIVOTARPOR con SUMA, obteniendo un acumulado por tarea.

Es un enfoque sólido y, cuando lo que buscas es un resumen agregado, casi siempre el mejor: PIVOTARPOR está pensada para esto y es rápida.

El propio Leo advierte del matiz: para lo que se pide exactamente aquí, que no es un agregado sino la reestructuración completa fila a fila, "hay que hacer algunas maniobras más". Es una distinción que conviene tener presente en general: agregar y reestructurar no son el mismo problema, aunque a menudo se confundan.

La solución dinámica: REDUCE con APILARV

Nava aporta la versión que cumple el requisito de escalar sola:

=LET(
    _data; B5:H10;
    REDUCE(J4:M4;
        SECUENCIA(FILAS(_data));
        LAMBDA(a;v;LET(
            r; INDICE(_data;v;);
            t; @+r;
            p; AJUSTARFILAS(EXCLUIR(r;;1);3);
            APILARV(a;SI.ND(APILARH(t;p);t))
        ))
    )
)

El patrón de fondo es el clásico de acumulación: REDUCE arranca con una cabecera y va recorriendo las filas, y en cada vuelta apila el trozo nuevo debajo de lo acumulado. La variable a es lo que llevas construido y v el índice de la fila que toca.

Dentro, cada vuelta hace tres cosas:

  • INDICE(_data;v;) extrae la fila completa. Ese segundo punto y coma sin nada detrás significa "todas las columnas", y es una forma muy limpia de coger una fila entera sin saber su ancho.
  • AJUSTARFILAS(EXCLUIR(r;;1);3) quita la primera columna, la de la tarea, y reorganiza el resto en bloques de tres. Aquí está la pieza que hace el trabajo: convierte una fila larga de bloques repetidos en varias filas de tres columnas, sin importar cuántos bloques haya.
  • APILARH vuelve a pegar la tarea delante de cada uno de esos bloques ya separados.

AJUSTARFILAS es la clave real del caso. Sin ella tendrías que localizar dónde empieza cada bloque; con ella basta con decir de cuántas columnas es un bloque y ella reparte.

El SI.ND del final rellena los huecos cuando un bloque viene incompleto, para que la tabla salga cuadrada.

La advertencia sobre el rendimiento

Nava añade un aviso que conviene no saltarse: la combinación de REDUCE con APILARV puede volverse muy lenta, e incluso inutilizable, con conjuntos de datos grandes.

El motivo es estructural: en cada vuelta se construye una copia entera del resultado acumulado. Con cien filas ni lo notas. Con decenas de miles, el número de operaciones crece de forma desproporcionada y el libro se arrastra.

Para volúmenes moderados es una herramienta magnífica. Cuando el volumen crece, la respuesta correcta suele ser Power Query: despivotar bloques repetidos es exactamente para lo que existe su transformación de anular dinamización, y procesa cientos de miles de filas sin despeinarse.

Funciones clave

  • REDUCE con LAMBDA — recorre una secuencia acumulando un resultado. El patrón estándar para construir tablas fila a fila.
  • APILARV y APILARH — apilan matrices en vertical y en horizontal.
  • AJUSTARFILAS — reorganiza una matriz en un número fijo de columnas. La pieza que separa los bloques repetidos sin localizarlos a mano.
  • EXCLUIR — descarta filas o columnas del borde. Aquí quita la columna de la tarea.
  • INDICE con el argumento de columna vacío — extrae una fila completa sin saber su ancho.
  • SI.ND — rellena los huecos de los bloques incompletos.
  • PIVOTARPOR — la herramienta correcta cuando lo que quieres es agregar, no reestructurar.

Conclusión

Este caso enseña tres niveles del mismo problema, y los tres son útiles.

La versión estática vale para entender la forma del resultado. La dinámica con REDUCE y AJUSTARFILAS es la que resuelve de verdad el requisito de escalar. Y el aviso de rendimiento marca la frontera a partir de la cual toca cambiar de herramienta.

La idea más reutilizable es AJUSTARFILAS: cuando tengas bloques repetidos a lo ancho, no busques dónde empieza cada uno, dile a Excel de cuántas columnas es un bloque y deja que reparta.

Casos así, donde la primera solución funciona pero no sirve, y la comunidad sigue empujando hasta la que sí escala, son la mejor parte de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel