Consolidar bloques de datos con cabeceras en una sola tabla

Un miembro de la comunidad comparte un archivo con datos de carteras de cobro organizados en bloques separados. Cada bloque tiene una cabecera con el tipo de cartera (CARTERA_AL_DIA, CARTERA_[01-30], etc.) y debajo una tabla con empresas y sus porcentajes de mora y deuda contable.

El objetivo: unificar todos los bloques en una sola tabla de 3 columnas, concatenando el nombre de la empresa con el tipo de cartera (ej: MANZANA_CARTERA_AL_DIA) y manteniendo los porcentajes asociados.

La estructura original tiene este aspecto:

``
CARTERA_AGRUPADA CARTERA_AL_DIA
EMPRESA %MORA %CONTABLE
MANZANA 0,01 0,02
PERA 0,03 0,04

CARTERA_AGRUPADA CARTERA_[01-30]
EMPRESA %MORA %CONTABLE
DURAZNO 0,05 0,06
MELON 0,07 0,08
SANDIA 0,09 0,10
`

Y el resultado esperado:

`
MANZANA_CARTERA_AL_DIA 0,01 0,02
PERA_CARTERA_AL_DIA 0,03 0,04
DURAZNO_CARTERA_[01-30] 0,05 0,06
MELON_CARTERA_[01-30] 0,07 0,08
SANDIA_CARTERA_[01-30] 0,09 0,10
`

John propuso una primera aproximación directa con APILARV y APILARH, apilando cada bloque manualmente:

`
=APILARV(
APILARH(B5:B6&"_"&C2; H5:I6);
APILARH(B13:B15&"_"&C10; H13:I15)
)
`

Simple y funcional, aunque requiere ajustar los rangos manualmente si se añaden más bloques.

Para hacerlo completamente dinámico, John aportó una segunda solución con LET, FILTRAR, APILARH y BUSCAR:

`
=LET(
e; B2:B15;
f; FILA(e);
FILTRAR(
APILARH(
e&"_"&BUSCAR(f; f/(e=B2); C2:C15);
H2:I15
);
H2:H15<""
)
)
`

El truco está en BUSCAR(f; f/(e=B2); C2:C15): busca hacia atrás la última cabecera de cartera para cada fila, asignando automáticamente el tipo correcto a cada empresa. Luego FILTRAR elimina las filas sin datos.

Gerson aportó una tercera versión usando SCAN y REGEXPRUEBA para detectar las cabeceras de forma automática:

`
=FILTRAR(
APILARH(
B2:B15&"_"&SCAN(; C2:C15; LAMBDA(a; v; SI(REGEXPRUEBA(v; "_"); v; a)));
H2:I15
);
N(+I2:I15)
)
`

Aquí SCAN recorre la columna C y, cada vez que encuentra un valor con guion bajo (patrón de cabecera), lo guarda como referencia. Para las filas de datos, mantiene la última cabecera encontrada. REGEXPRUEBA` identifica las cabeceras por el patrón del nombre de cartera.

Tres enfoques para el mismo problema: desde lo manual y directo hasta lo completamente dinámico con detección automática de cabeceras.

El problema: bloques de datos con cabeceras sueltas

Un miembro de la comunidad tenía un archivo de carteras de cobro organizado en bloques separados. Cada bloque arranca con una cabecera que indica el tipo de cartera (cartera al día, cartera de 1 a 30 días, etc.) y debajo una tabla con empresas y sus porcentajes de mora y deuda.

El objetivo: unificar todos los bloques en una sola tabla de tres columnas, concatenando el nombre de la empresa con el tipo de cartera al que pertenece (por ejemplo, MANZANA con su cartera correspondiente) y arrastrando sus porcentajes. El reto de fondo es asignar a cada fila la cabecera que tiene encima, aunque esa cabecera solo aparezca una vez por bloque.

Solución 1: apilado manual con APILARV y APILARH

John empezó por lo directo: apilar cada bloque a mano, concatenando el nombre de empresa con su tipo de cartera.

=APILARV(
    APILARH(B5:B6&"_"&C2; H5:I6);
    APILARH(B13:B15&"_"&C10; H13:I15)
)

Simple y funcional. El inconveniente: hay que ajustar los rangos a mano cada vez que se añade un bloque. Perfecto para pocos bloques, incómodo si crecen.

Solución 2: totalmente dinámica con BUSCAR

Para no tocar rangos nunca más, John aportó una versión que arrastra la última cabecera vista hasta cada fila. El corazón es una BUSCAR que, para cada fila, localiza hacia atrás la cabecera de cartera más reciente y la asigna a esa empresa. Después, un FILTRAR elimina las filas que no tienen datos (las propias líneas de cabecera y los huecos entre bloques).

La técnica de buscar "el último valor no vacío hacia arriba" es un patrón que reaparece constantemente en Excel cuando trabajas con datos jerárquicos o con cabeceras que no se repiten en cada fila.

Solución 3: detección automática con SCAN y REGEXPRUEBA

Gerson lo llevó un paso más allá detectando las cabeceras por su patrón de texto, no por su posición:

=FILTRAR(
    APILARH(
        B2:B15&"_"&SCAN(; C2:C15; LAMBDA(a; v; SI(REGEXPRUEBA(v; "_"); v; a)));
        H2:I15
    );
    N(+I2:I15)
)

Aquí SCAN recorre la columna de tipos y va memorizando la última cabecera encontrada: cada vez que topa con un valor que encaja con el patrón de cabecera (detectado con REGEXPRUEBA), lo guarda como referencia; para las filas de datos, mantiene la cabecera anterior. Al final, N(+I2:I15) sirve para quedarse solo con las filas que tienen importe real, descartando cabeceras y huecos.

Es el enfoque más robusto: no depende de dónde estén los bloques ni de cuántos haya. Mientras las cabeceras sigan su patrón, la fórmula se adapta sola.

Funciones clave

  • APILARV y APILARH: ensamblan bloques en vertical y horizontal.
  • BUSCAR: recupera hacia atrás la última cabecera vigente para cada fila.
  • SCAN + LAMBDA: recorren la columna acumulando la cabecera actual como estado.
  • REGEXPRUEBA: identifica las cabeceras por su patrón de texto.
  • FILTRAR y N: eliminan las filas sin datos reales.

Conclusión

Consolidar bloques con cabeceras sueltas se reduce a un principio: propagar cada cabecera hacia abajo hasta la siguiente, y luego quedarte solo con las filas de datos. Puedes hacerlo a mano con APILARV, arrastrando la cabecera con BUSCAR, o detectándola por patrón con SCAN y REGEXPRUEBA. De lo manual a lo totalmente automático, tres niveles para el mismo problema. Casos así aparecen cada semana en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel