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.

En las semanas siguientes Juan volvió con tres variantes del mismo problema, que tienes al final de la página: el unpivot con conceptos múltiples separados por «/», el unpivot con separación de cuentas contables concatenadas en una celda y las contrapartidas cuando las columnas no están en el orden esperado.

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);
ELEGIRCOLS(_datos; COLUMNAS(_datos)) -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. El importe de la contrapartida es la última columna de _datos multiplicada por menos uno.

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; COLUMNAS(a))
);
AJUSTARFILAS(ENCOL(APILARH(a; b)); COLUMNAS(a))
)
`

Y una segunda versión que solo genera contrapartida en las filas cuya cuenta no es ya la 572 (para no duplicarlas):

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

En las dos, Miki saca el importe con ELEGIRCOLS y un índice negativo (el menos uno es la última columna); aquí va escrito como COLUMNAS(a), que en esta tabla es lo mismo.

Hugo lo resuelve en una sola línea sin LET. Apila con APILARH el rango B5:E17 y, a su lado, un bloque de contrapartidas que construye así: un SI con una constante matricial horizontal de tres elementos (un uno y dos ceros) que, donde hay uno, pone el 572 y, donde hay ceros, deja las columnas de B5:D17; a ese bloque le pega -E5:E17, el importe negado. Todo eso va dentro del mismo ENCOL y AJUSTARFILAS(...; 4). 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(NO(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; 5; 6));
ELEGIRCOLS(a; 4) = "Tipo Asiento 4";
APILARH(ELEGIRCOLS(a; 1; 2); ESPACIOS(TEXTODESPUES(ELEGIRCOLS(a; 3); "-")); ELEGIRCOLS(a; 5; 6));
NO(ELEGIRCOLS(a; 4) = "Tipo Asiento 3");
APILARH(ELEGIRCOLS(a; 1; 2); ESPACIOS(TEXTOANTES(ELEGIRCOLS(a; 3); "-"; ; ; ; ELEGIRCOLS(a; 3))); ELEGIRCOLS(a; 5; 6))
)
)
`

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. Las dos últimas columnas de a (la cuenta y el importe) van como ELEGIRCOLS(a; 5; 6); en el fichero, Miki las pide con índices negativos, contando desde el final.

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) 1; ); MAX)
);
D2:R2;
TOMAR(x:R4; 1; -15)
)
)
)
)
)
);
FILTRAR(m; NO(EXCLUIR(m; ; 3) = 0))
)
`

Usa DIVIDIRTEXTO con «/» como separador para extraer los conceptos, y SCAN + MAX para identificar los grupos de columnas. Al final, FILTRAR quita las filas cuyo importe (la cuarta columna) es cero.

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.

Variante 1: unpivot de asientos contables con conceptos múltiples

Juan concretó la segunda parte con su tabla real. Cada fila tiene varios tipos de asiento (cuentas contables repartidas en columnas: 640, 642, 476, 465, 607, 472, 400, 705, 477, 430, 628, 681, 216...) con sus importes, y un único campo Concepto que agrupa varios conceptos separados por «/», por ejemplo:

- Concepto: «Salario Enero/Outsourcing/Ventas Enero//Amortización Enero»
- Columnas D-R: las cuentas con sus importes

El objetivo es pasar a formato largo, Fecha | Concepto individual | Cuenta | Importe, de modo que cada concepto separado por «/» quede asignado a su grupo de cuentas. La dificultad es doble: el unpivot de las columnas y, a la vez, partir el concepto y repartir cada trozo entre las cuentas que le tocan. Un concepto vacío entre dos barras significa que ese tipo de asiento no lleva concepto.

John lo resuelve con una fórmula que combina LET, APILARH, ENCOL, SI, TEXTODESPUES, TEXTOANTES y SCAN:

`
=LET(
H; APILARH;
F; LAMBDA(x; ENCOL(SI(D3:R4 = ""; E; x); 2));
APILARV(
H(B2:C2; "Cuenta"; "Importe");
H(
F(B3:B4);
F(TEXTOANTES(TEXTODESPUES("/" & C3:C4; "/"; SCAN(; SI.ERROR(DERECHA(D1:R1) 1; ); MAX)); "/"; ; ; 1));
F(D2:R2);
F(D3:R4)
)
)
)
`

La clave está en cómo identifica los grupos de cuentas: usa la fila de cabeceras («Tipo asiento 1», «Tipo Asiento 2»...) para detectar dónde empieza cada grupo. DERECHA(...) 1 convierte en número el dígito final de cada cabecera, SI.ERROR deja hueco donde no lo hay, y SCAN con MAX arrastra el máximo visto: un contador que dice a qué grupo pertenece cada columna. Con ese número, TEXTODESPUES y TEXTOANTES recortan del Concepto justo el trozo de ese grupo. La LAMBDA F aplana cada bloque con ENCOL saltándose las celdas vacías de importes.

Miki aporta además la solución que tienes en la segunda parte, que separa por tipo de asiento y concepto de forma más directa. El fichero incluye la tabla original, la solución esperada, la de Miki y otra versión de John, a la que llama «método recomendadísimo».

Variante 2: unpivot con separación de cuentas contables concatenadas

Más adelante, Juan trajo otra tabla: las cuentas vienen concatenadas con «/» en una sola celda (por ejemplo, 621/472/410), y cada cuenta tiene su importe en una columna aparte. El reto es un unpivot que no solo despliegue los importes, sino que también separe las cuentas y las asocie a cada importe.

Los datos de partida tienen esta estructura:

`
Fecha | Cuentas | Importe | Importe | Importe | Concepto
2026-03-31 | 621/472/410 | 100 | 21 | -410 | Inventado
2026-04-30 | 623/472/410 | 3000 | 21 | -3021 | Prueba
`

Y el resultado esperado es una fila por cada cuenta individual con su importe:

`
Fecha | Cuenta | Importe | Valor | Concepto
2026-03-31 | 621 | Importe | 100 | Inventado
2026-03-31 | 472 | Importe | 21 | Inventado
2026-03-31 | 410 | Importe | -410 | Inventado
2026-04-30 | 623 | Importe | 3000 | Prueba
...
`

Juan ya tenía una LAMBDA heredada de otro ejercicio que casi lo resolvía, pero la columna de cuentas se repetía en vez de dividirse. La comunidad propuso tres enfoques.

Solución de Nacho, con LET y una LAMBDA auxiliar para vectorizar la operación de columnas:

`
=LET(
H; APILARH;
F; LAMBDA(x; ENCOL(SI(F5:H6 = ""; NOD(); x); 2));
c; ENCOL(DIVIDIRTEXTO(UNIRCADENAS(""; 0; E5:E6 & "/"); "/"));
APILARV(
H(
F(D5:D6);
H(
TOMAR(c; FILAS(c) - 1);
F(F4:H4);
F(F5:H6);
F(I5:I6)
)
)
)
)
`

La clave está en unir todas las cuentas en una sola cadena, con una «/» detrás de cada celda, y separarlas de golpe con DIVIDIRTEXTO. A ese vector le sobra un elemento vacío al final (el de la última barra), y por eso se toman todos menos el último. En la fórmula original la unión se hace con CONCAT y el último elemento se quita con EXCLUIR y un índice negativo; aquí va con UNIRCADENAS y TOMAR, que hacen lo mismo. El SI contra F5:H6 le da a cada columna la forma de la tabla de importes (cada fecha y cada concepto, una vez por importe) y ENCOL la aplana. Su 2 le dice que tire los errores: los NOD() que marcan los importes vacíos, para que un asiento con dos cuentas no deje una fila de más.

Solución con REDUCE y EXPANDIR, que construye el resultado fila a fila:

`
=LET(
_f; D5:D6;
_c; E5:E6;
_t; I5:I6;
_d; F5:H6;
_R; LAMBDA(x; EXCLUIR(REDUCE(0; x; LAMBDA(a; v; APILARV(a; EXPANDIR(v; 3; ; v)))); 1));
APILARH(
_R(_f);
EXCLUIR(REDUCE(0; _c; LAMBDA(a; v; APILARV(a; ENCOL(DIVIDIRTEXTO(v; "/") 1)))); 1);
_R(EXPANDIR(F4; FILAS(D5:D6); ; F4));
EXCLUIR(REDUCE(0; _d; LAMBDA(a; v; APILARV(a; v))); 1);
_R(_t)
)
)
`

La LAMBDA _R repite cada valor tantas veces como cuentas hay (3 en este caso) usando EXPANDIR, y REDUCE va apilando cada bloque verticalmente; el EXCLUIR(...; 1) quita el cero inicial del acumulador. Tal como está, vale cuando todos los asientos traen tres cuentas: ese 3 va fijo.

Solución de Gerson, la más compacta, con un truco elegante: unir todas las cuentas con dos separadores ("/" entre cuentas y "|" al final de cada fila) para aplanarlo todo en un solo paso:

`
=LET(
a; DIVIDIRTEXTO(UNIRCADENAS(""; 0; E5:E6 & "|"); "/"; "|"; 1) 1;
F; LAMBDA(x; ENCOL(SI(a; x); 2));
APILARH(F(D5:D6); F(a); F(F4); F(F5:H6); F(I5:I6))
)
`

El DIVIDIRTEXTO con dos delimitadores ("/" para las columnas y "|" para las filas) genera directamente una matriz con la forma correcta, que luego sirve como máscara para ENCOL. Si un asiento trae menos cuentas que otro, DIVIDIRTEXTO rellena el hueco con #N/D, y el 2 de ENCOL los tira. Como en la de Nacho, Gerson une las cuentas con CONCAT; aquí va con UNIRCADENAS.

Miki también aportó su versión con una imagen que completaba el enfoque. Las reacciones de Juan lo dicen todo: «Solución increíble», «Perfecta», «Buenísima también».

Nota (corregido el 26/9/2026): las fórmulas de Nacho y de Gerson funcionaban con el ejemplo, en el que todos los asientos traen tres cuentas, pero con uno de dos cuentas añadían una fila de más, llena de #N/D o de vacíos. A la de Gerson le faltaba el ; 2 de ENCOL, que es el que tira esos #N/D; en la de Nacho, los huecos van ahora con NOD() en vez de "", para que ese mismo 2 los pueda tirar. La explicación decía además que el 2 de ENCOL agrupa en bloques de 3 filas: es el argumento que ignora los errores. Comprobado en Excel.

Variante 3: contrapartidas cuando las columnas no están en el orden esperado

La fórmula de la primera parte funciona siempre que las columnas de la tabla estén en el orden previsto, pero si alguien cambia el orden (por ejemplo, mueve la columna de importe antes que la de cuenta), la contrapartida generada se descompensa. Y lo hace en silencio: no da error, simplemente cuadra mal.

Juan dio con la clave: dentro del LET, al construir la contrapartida, fuerza el orden de columnas con ELEGIRCOLS. Así la lógica trabaja siempre sobre el layout correcto, sea cual sea el orden en la tabla original:

`
=LET(
_datos; A1:D6;
AJUSTARFILAS(
ENCOL(
APILARH(
_datos;
ELEGIRCOLS(_datos; 1);
SECUENCIA(FILAS(_datos); ; 572; 0);
ELEGIRCOLS(_datos; 3);
ELEGIRCOLS(_datos; COLUMNAS(_datos)) * -1
)
);
4
)
)
`

Compárala con la de la primera parte: allí el 572 sustituía a la primera columna y se copiaban la segunda y la tercera; aquí se copian la primera y la tercera, y el 572 va en la segunda. En la fórmula original de Juan, el importe se toma con TOMAR y un índice negativo, que es lo mismo que pedir la última columna.

Nacho confirmó el enfoque: no se trata solo de elegir qué columnas tomar, sino de reordenarlas dentro del APILARH para que la contrapartida quede blindada frente a cambios en la tabla. Un recordatorio útil de que ELEGIRCOLS no sirve únicamente para seleccionar, también para reubicar.

En el fichero de esta variante va además una solución de John (así se llama el fichero) para otro paso del mismo libro diario. De cada apunte de la 601 saca dos líneas más: la de la 472, con el 21 % del importe, y la de la 410, con el total cambiado de signo (el importe por 1,21 en negativo). Lo hace con el mismo ENCOL + AJUSTARFILAS y una LAMBDA F(cuenta; factor), de modo que cada línea nueva es una llamada más a F. Juan pidió después poder fechar el pago unos días más tarde, y el fichero recoge también esa versión: F` acepta un tercer argumento opcional con los días, y con 60 añade el pago (410 en positivo y 572 en negativo) 60 días después del apunte.

Convertir una tabla de asientos contables en formato horizontal a un listado vertical tipo libro diario es una de esas tareas que parecen simples hasta que te sientas a hacerlas. Si además tienes que generar las contrapartidas automáticas, la cosa se complica. Juan planteó este reto en la comunidad y volvió varias veces con nuevas versiones de su tabla: conceptos múltiples en una sola celda, cuentas concatenadas, columnas que cambian de sitio.

Esta página reúne el caso y sus tres variantes. Todas comparten la misma idea de fondo: pasar de ancho a largo con matrices dinámicas, sin bucles ni columnas auxiliares.

El problema

La tabla de partida tiene una fila por asiento con las columnas Cuenta, Concepto, Fecha e Importe. Lo que se necesita es un listado donde, justo debajo de cada asiento, aparezca su contrapartida: la cuenta 572 (caja) con el mismo concepto y fecha, pero con el importe invertido.

Es decir, no basta con añadir filas al final. Hay que intercalarlas entre las originales, alternando una y una. Y ahí es donde la mayoría se atasca.

La técnica clave: entrelazar matrices

La solución de Nacho se apoya en un patrón que conviene memorizar: ENCOL seguido de AJUSTARFILAS.

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

Primero APILARH coloca lado a lado los datos originales (4 columnas) y las contrapartidas construidas (otras 4 columnas): la cuenta 572 repetida con SECUENCIA de paso 0, el concepto y la fecha copiados con ELEGIRCOLS, y el importe de la última columna multiplicado por menos uno.

Después ENCOL aplana ese bloque de 8 columnas en una única columna vertical, leyendo fila a fila. Y AJUSTARFILAS(...; 4) lo vuelve a plegar en bloques de 4 columnas.

El efecto es el de barajar dos mazos de cartas: como original y contrapartida iban pegados en la misma fila, al enrollar y desenrollar quedan alternados automáticamente.

Variantes de la comunidad para las contrapartidas

Miki propone la misma idea usando EXPANDIR para generar la columna de cuentas 572 y ELEGIRCOLS con índice negativo para quedarse con el importe, la última columna:

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

Fíjate en el detalle de COLUMNAS(a) como segundo argumento de AJUSTARFILAS: así la fórmula se adapta sola si la tabla cambia de ancho.

Miki añade además una segunda versión que evita duplicar contrapartidas cuando la cuenta ya es la 572, envolviendo el bloque b en un SI que comprueba NO(ELEGIRCOLS(a; 1) = "572"). Solo genera contrapartida para las filas que la necesitan.

Hugo lo condensa en una sola línea sin LET, apoyándose en una constante matricial horizontal de tres elementos (un uno seguido de dos ceros) dentro de un SI: donde hay uno, coloca el 572; donde hay cero, mantiene la columna original. Después le pega el importe negado y aplica el mismo ENCOL y AJUSTARFILAS de 4 columnas. Mismo resultado, cero variables intermedias.

Segunda parte: el unpivot completo

Juan sube la apuesta: la tabla real tiene varias columnas de importes agrupadas bajo cabeceras de tipo de asiento, con celdas combinadas y una fila de conceptos.

Miki lo resuelve propagando las cabeceras combinadas con SCAN, que arrastra el último valor no vacío hacia la derecha, y define una LAMBDA reutilizable dentro de LET para hacer el unpivot de cada columna auxiliar. Después usa SI.CONJUNTO para extraer el concepto correcto según el tipo de asiento, con TEXTOANTES y TEXTODESPUES sobre el guion separador.

Lourdes ataca por otro lado: REDUCE con APILARV para construir el resultado fila a fila, usando DIVIDIRTEXTO con la barra como separador para trocear los conceptos y SCAN con MAX para identificar a qué grupo de columnas pertenece cada importe. Un FILTRAR final quita las filas con importe cero.

Variante 1: unpivot de asientos contables con conceptos múltiples

En la tabla real de Juan, cada fila mezcla varios tipos de asiento: las cuentas (640, 642, 476, 607, 400, 705...) repartidas en muchas columnas con sus importes, y un único campo Concepto que agrupa varios conceptos separados por barras, como «Salario Enero/Outsourcing/Ventas Enero//Amortización Enero».

El objetivo es la forma Fecha | Concepto individual | Cuenta | Importe, con cada concepto asignado a su grupo de cuentas. Hay que hacer el unpivot y, a la vez, partir el concepto y repartir cada trozo entre las cuentas que le tocan.

John lo resuelve con una única fórmula. La clave está en cómo detecta a qué grupo pertenece cada cuenta. La fila de cabeceras numera los bloques («Tipo asiento 1», «Tipo asiento 2»...), y John construye con SCAN y MAX un contador que avanza cada vez que empieza un grupo nuevo:

SCAN(; SI.ERROR(DERECHA(D1:R1) * 1; ); MAX)

DERECHA(...) * 1 convierte en número el dígito final de cada cabecera; SI.ERROR deja hueco donde no hay número, y SCAN con MAX arrastra hacia delante el máximo visto. Sale algo como 1, 1, 1, 2, 2, 3...: una etiqueta de grupo para cada columna de cuenta.

Con ese contador, TEXTODESPUES y TEXTOANTES recortan del Concepto el trozo que corresponde a cada grupo: «para esta cuenta, quiero el segundo concepto de la lista». El resto es montaje: una LAMBDA que aplana cada bloque con ENCOL saltándose los huecos, aplicada a fechas, conceptos, cuentas e importes, y un APILARH que los pone en cuatro columnas, con APILARV para añadir las cabeceras.

Es un unpivot «con inteligencia»: no solo estira la tabla, sino que reparte un texto múltiple entre grupos de columnas.

Variante 2: unpivot con separación de cuentas contables concatenadas

Más adelante, Juan trajo otra tabla. Las cuentas venían pegadas con una barra en una sola celda (la 621, la 472 y la 410 juntas) y, al lado, tres columnas de importes, uno por cuenta. Si haces solo el unpivot, la cadena de cuentas se repite entera en las tres filas. Hay que partir la cadena y emparejar cada cuenta con su importe, arrastrando fecha y concepto.

Juan ya tenía una LAMBDA heredada que casi lo resolvía: los importes se desplegaban bien, pero la cuenta se repetía. La comunidad respondió con tres enfoques.

Aplanar y repartir con ENCOL (Nacho). Se une toda la columna de cuentas en una sola cadena, con una barra detrás de cada celda, y se parte de golpe con DIVIDIRTEXTO. Sale un vector con todas las cuentas seguidas, en el orden correcto; solo hay que quitarle el elemento vacío que deja la última barra. Una LAMBDA auxiliar hace el resto: un SI contra el bloque de importes le da a cada columna (fecha, cabecera, concepto) la forma de ese bloque, y ENCOL la aplana por filas. Los huecos van marcados con NOD() y el 2 de ENCOL los tira, de modo que un asiento con dos cuentas no deja una fila de más.

Construir fila a fila con REDUCE y EXPANDIR. Una LAMBDA auxiliar recibe una columna y repite cada valor tres veces con EXPANDIR, apilando los bloques con APILARV dentro de un REDUCE. Se aplica a la fecha, al concepto y a la etiqueta de columna; las cuentas se parten con DIVIDIRTEXTO dentro del mismo patrón, y al final todo se pega con APILARH. Como REDUCE arranca con un valor inicial que aparece como primera fila, un EXCLUIR se lo quita. Es la más legible, pero el 3 de EXPANDIR va fijo: vale cuando todos los asientos traen tres cuentas.

Dos separadores en un paso (Gerson). La más compacta. DIVIDIRTEXTO acepta dos delimitadores, uno para columnas y otro para filas. Gerson une las cuentas marcando el final de cada fila con una barra vertical y deja la barra inclinada entre cuentas: una sola llamada devuelve ya una matriz con una fila por asiento y una columna por cuenta. Esa misma matriz se reutiliza como máscara para que ENCOL apile solo las posiciones donde hay cuenta. Si un asiento trae menos cuentas, el hueco sale como #N/D y el 2 de ENCOL lo tira.

Las reacciones de Juan en el grupo lo resumen: «Solución increíble», «Perfecta», «Buenísima también».

Variante 3: contrapartidas cuando las columnas no están en el orden esperado

La fórmula de las contrapartidas funciona de maravilla... siempre que las columnas estén en el orden previsto. Si alguien reordena la tabla (por ejemplo, mueve el importe antes que la cuenta), la contrapartida se descompensa. Es un fallo silencioso y peligroso: no da error, simplemente cuadra mal.

Juan dio con la solución: dentro del LET, al construir la contrapartida, fuerza el orden de las columnas con ELEGIRCOLS:

=LET(
    _datos; A1:D6;
    AJUSTARFILAS(
        ENCOL(
            APILARH(
                _datos;
                ELEGIRCOLS(_datos; 1);
                SECUENCIA(FILAS(_datos); ; 572; 0);
                ELEGIRCOLS(_datos; 3);
                ELEGIRCOLS(_datos; COLUMNAS(_datos)) * -1
            )
        );
        4
    )
)

El APILARH construye la contrapartida columna a columna, eligiendo explícitamente qué va en cada posición: la primera columna tal cual, la cuenta 572 en la segunda, la tercera columna y el importe invertido. Al reconstruir el orden a mano, la fórmula deja de depender de cómo estén colocadas las columnas en la tabla de entrada. ENCOL y AJUSTARFILAS(...; 4) hacen el entrelazado de siempre.

Nacho confirmó el enfoque con un matiz: no se trata solo de elegir qué columnas tomar, sino de reubicarlas dentro del APILARH. ELEGIRCOLS no se limita a filtrar columnas: también las coloca en el orden que le pidas. Es una forma sencilla de normalizar el layout de una tabla antes de operar con ella, y de protegerte de que un compañero mueva una columna y te rompa el modelo sin que te enteres.

El fichero de esta variante lleva además una extensión del mismo patrón: de cada apunte saca la línea del IVA (472, el 21 %) y la del proveedor (410), y una LAMBDA reutilizable permite añadir más líneas, incluida la del pago 60 días después.

Funciones clave

  • ENCOL: aplana una matriz en una sola columna, leyendo por filas; con un 2 como segundo argumento, ignora los errores.
  • AJUSTARFILAS: repliega una columna en una matriz del ancho que le indiques.
  • APILARH y APILARV: pegan matrices lado a lado o una encima de otra.
  • EXPANDIR y SECUENCIA: dos formas de generar una columna con un valor repetido.
  • ELEGIRCOLS: selecciona y reordena columnas por posición; admite índices negativos desde el final.
  • SCAN: rellena huecos de cabeceras combinadas y, con MAX, numera grupos de columnas.
  • DIVIDIRTEXTO: parte texto por uno o dos delimitadores; con dos, devuelve una matriz bidimensional.
  • TEXTOANTES y TEXTODESPUES: recortan de un texto múltiple el trozo que toca.
  • REDUCE: acumula bloques para construir el resultado paso a paso.

Conclusión

Varias tablas de asientos distintas y una misma caja de herramientas. El patrón ENCOL más AJUSTARFILAS sirve siempre que necesites insertar filas calculadas entre las originales: contrapartidas, líneas de IVA, pagos. El unpivot con ENCOL y una LAMBDA auxiliar resuelve tanto los conceptos múltiples como las cuentas concatenadas. Y ELEGIRCOLS es el seguro barato para que todo eso no se descuadre cuando alguien mueve una columna.

Casos como este nacen de problemas reales de trabajo, y en la comunidad de InflueXcel suelen aparecer tres o cuatro enfoques distintos para el mismo reto. Comparar las soluciones enseña más que memorizar una.

Más casos con estas funciones

Más contenido de Excel en InflueXcel