Desglose de asientos contables con unpivot y generación automática de contrapartidas

Interesante problema planteado por Juan en el grupo: tiene una tabla de asientos contables en formato horizontal (cada fila contiene cuenta, concepto y varias columnas de importes por tipo de asiento) y necesita convertirla en un listado vertical tipo libro diario, generando además las contrapartidas automáticas (la cuenta 572, caja, con el importe en negativo).

El reto tiene dos partes. La primera: dado un rango con columnas Cuenta, Concepto, Fecha e Importe, generar una fila adicional por cada asiento con la cuenta de contrapartida (572) y el importe invertido, intercalándola justo debajo del asiento original. La segunda: cuando la tabla tiene múltiples columnas de importes (varios asientos por fila) con cabeceras de tipo de asiento y conceptos en filas superiores, hacer el unpivot completo y asignar cada concepto a su asiento correspondiente.

Primera parte: contrapartidas con ENCOL + AJUSTARFILAS

Nacho propone una solución con LET que apila horizontalmente los datos originales junto con las contrapartidas generadas, y luego usa la técnica de entrelazar matrices con ENCOL + AJUSTARFILAS para intercalar las filas:

``
=LET(
_datos; B5:E17;
AJUSTARFILAS(
ENCOL(
APILARH(
_datos;
SECUENCIA(FILAS(_datos); ; 572; 0);
ELEGIRCOLS(_datos; 2; 3);
TOMAR(_datos; ; -1) * -1
)
);
4
)
)
`

La clave es APILARH para poner lado a lado los datos originales y las contrapartidas (4 columnas + 4 columnas), luego ENCOL los convierte en una sola columna vertical, y AJUSTARFILAS(...; 4) los redistribuye en filas de 4 columnas. Como los datos originales y las contrapartidas estaban lado a lado, al "enrollar y desenrollar" quedan perfectamente intercalados.

Miki aporta dos variantes con EXPANDIR y ELEGIRCOLS:

`
=LET(
a; B5:E17;
b; APILARH(
EXPANDIR(572; FILAS(a); 1; 572);
ELEGIRCOLS(a; 2; 3);
-ELEGIRCOLS(a; -1)
);
AJUSTARFILAS(ENCOL(APILARH(a; b)); COLUMNAS(a))
)
`

Y una segunda versión que filtra las filas donde la cuenta ya es 572 (para no duplicar contrapartidas):

`
=LET(
a; B5:E17;
b; SI(
ELEGIRCOLS(a; 1) <> "572";
APILARH(
EXPANDIR(572; FILAS(a); 1; 572);
ELEGIRCOLS(a; 2; 3);
-ELEGIRCOLS(a; -1)
)
);
AJUSTARFILAS(ENCOL(APILARH(a; b)); COLUMNAS(a))
)
`

John lo resuelve en una sola línea sin LET, usando una constante matricial {1\0\0} para seleccionar qué columnas replicar:

`
=AJUSTARFILAS(ENCOL(APILARH(B5:E17; APILARH(SI({1\0\0}; 572; B5:D17); -E5:E17))); 4)
`

Muy compacta: SI({1\0\0}; 572; B5:D17) sustituye la primera columna por 572 y mantiene las otras dos, luego le concatena el importe negado. Mismo resultado, sin variables intermedias.

Segunda parte: unpivot con conceptos y tipos de asiento

Juan plantea una extensión más compleja: la tabla tiene múltiples columnas de importes agrupadas bajo cabeceras de tipo de asiento (fila 1) y conceptos (fila 2), con celdas combinadas. Necesita hacer el unpivot completo asignando cada concepto a su columna.

Miki construye una fórmula con SCAN para propagar las cabeceras combinadas, y SI.CONJUNTO para extraer el concepto correcto según el tipo de asiento:

`
=LET(
_c1; SCAN(""; D1:N1; LAMBDA(a; v; SI(v = ""; a; v)));
_c2; D2:N2;
m; D3:N14;
F; LAMBDA(x; ENCOL(SI.CONJUNTO(m <> ""; x); 2));
a; APILARH(F(A3:A14); F(B3:B14); F(C3:C14); F(_c1); F(_c2); F(m));
SI.CONJUNTO(
ELEGIRCOLS(a; 4) = "Tipo Asiento 3";
APILARH(ELEGIRCOLS(a; 1; 2); ESPACIOS(TEXTODESPUES(ELEGIRCOLS(a; 3); "-")); ELEGIRCOLS(a; -2; -1));
ELEGIRCOLS(a; 4) = "Tipo Asiento 4";
APILARH(ELEGIRCOLS(a; 1; 2); ESPACIOS(TEXTODESPUES(ELEGIRCOLS(a; 3); "-")); ELEGIRCOLS(a; -2; -1));
ELEGIRCOLS(a; 4) <> "Tipo Asiento 3";
APILARH(ELEGIRCOLS(a; 1; 2); ESPACIOS(TEXTOANTES(ELEGIRCOLS(a; 3); "-"; ; ; ; ELEGIRCOLS(a; 3))); ELEGIRCOLS(a; -2; -1))
)
)
`

Destaca el uso de SCAN para "rellenar" las cabeceras de celdas combinadas y la función F definida como LAMBDA reutilizable para hacer el unpivot de cada columna auxiliar.

Lourdes propone un enfoque diferente con REDUCE + APILARV para iterar sobre las filas de asientos y construir el resultado fila a fila:

`
=LET(
m; REDUCE(
{"Fecha"; "CONCEPTO"; "Cuenta"; "Importe"};
C3:C4;
LAMBDA(i; x;
APILARV(i;
SI({1; 0; 0; 0};
@+x:B4;
TRANSPONER(
APILARV(0;
INDICE(DIVIDIRTEXTO(x; ; "/");
SCAN(0; SI.ERROR(--DERECHA(D1:R1); ); MAX)
);
D2:R2;
TOMAR(x:R4; 1; -15)
)
)
)
)
)
);
FILTRAR(m; DROP(m; ; 3) <> 0)
)
`

Usa DIVIDIRTEXTO con "/" como separador para extraer los conceptos, y SCAN + MAX para identificar los grupos de columnas.

La técnica clave: entrelazar matrices

Todas las soluciones de la primera parte comparten el mismo patrón: ENCOL + AJUSTARFILAS. ENCOL aplana dos matrices apiladas horizontalmente en una sola columna, y AJUSTARFILAS` las redistribuye con el ancho original. El efecto es que las filas de ambas matrices quedan intercaladas, como barajar dos mazos de cartas. Es una técnica muy potente para cualquier situación donde necesites "insertar" filas calculadas entre las originales.