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

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. Y si además tienes que generar las contrapartidas automáticas, la cosa se complica. Juan planteó exactamente este reto en la comunidad y las soluciones que salieron enseñan una técnica que sirve para muchísimo más que contabilidad.

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);
                TOMAR(_datos; ; -1) * -1
            )
        );
        4
    )
)

El razonamiento es elegante. 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 negado con TOMAR sobre la última columna.

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. Sin bucles, sin columnas auxiliares.

Variantes de la comunidad

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

=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))
)

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.

John 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 concatena 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.

Funciones clave

• ENCOL — aplana una matriz en una sola columna, leyendo por filas
• AJUSTARFILAS — repliega una columna en una matriz del ancho que le indiques
• APILARH — pega matrices lado a lado
• EXPANDIR y SECUENCIA — dos formas de generar una columna con un valor repetido
• ELEGIRCOLS — selecciona columnas por posición, admite índices negativos desde el final
• SCAN — ideal para rellenar huecos de cabeceras combinadas

Conclusión

El patrón ENCOL mas AJUSTARFILAS no va de contabilidad: sirve siempre que necesites insertar filas calculadas entre las originales. Duplicar registros, generar líneas de detalle, alternar dos tablas... la mecánica es la misma.

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

• LAMBDA
• LET
• FILTRAR
• APILARH
• SECUENCIA
• APILARV
• REDUCE
• ELEGIRCOLS
• SCAN
• DIVIDIRTEXTO
• TRANSPONER

Más contenido de Excel en InflueXcel

• Cálculos iterativos con REDUCE y APILARV en Excel Caso
  La comunidad aborda un caso sobre cómo trabajar con bucles iterativos en Excel usando funciones modernas. Un miembro necesita realizar cálcu
• Repetir valores N veces: tres enfoques con REDUCE, SCAN y ARCHIVOMAKEARRAY Caso
  Joan Recasens plantea un problema clásico: tiene una lista de categorías (A, B, C) con el número de repeticiones de cada una (4, 2, 3) y qui
• Descomposición de Cholesky con matrices dinámicas: de VBA a LAMBDA+REDUCE Caso
  Juan Pablo lanza un reto al grupo: tiene una descomposición de Cholesky resuelta con VBA y quiere saber si se puede hacer con matrices dinám
• Error #CALC! con MAP y AGRUPARPOR: solución con REDUCE+APILARV Caso
  Nuevo reto de Excel resuelto por la comunidad: un usuario necesita aplicar AGRUPARPOR de forma iterativa sobre un rango de códigos de cuenta
• Filtro multicriteria dinámico con LET, FILTRAR y LAMBDA Caso
  Héctor comparte con la comunidad una fórmula avanzada para filtrar una tabla de productos/servicios por múltiples criterios opcionales (clav
• Funciones personalizadas en Excel: LET, LAMBDA y recursividad Tutorial
  Cómo pasar de una fórmula escrita a mano a una función propia que puedes llamar por su nombre en cualquier libro. Los tres vídeos de esta pá
• Distribución equitativa con LAMBDA, REDUCE y ALEATORIO Caso
  Héctor plantea la necesidad de repartir un conjunto de líneas (tareas, actividades) entre varios grupos de forma equitativa y aleatoria. Ide
• SUMAR.SI.CONJUNTO en Excel: las 5 dudas más buscadas, resueltas (y por qué no acepta ELEGIRCOLS) Tutorial
  Le pregunté a Copilot cuáles son las 5 dudas que más se buscan sobre la función SUMAR.SI.CONJUNTO y las resuelvo una a una en Excel, cada un
• Reto de agrupación y pivotado: AGRUPARPOR vs ARCHIVOMAKEARRAY vs MAP Caso
  Leandro trae un reto de internet al grupo: a partir de una tabla con claves repetidas y valores, generar una tabla pivotada donde cada clave
• Automatizar Excel con VBA y ChatGPT: de pedirle el código a integrar la API Tutorial
  Tres niveles de automatización con VBA, ordenados de menos a más, y los dos primeros no exigen saber programar. 1. Pedirle el código a ChatG