Unpivot de asientos contables con conceptos múltiples

Juan plantea un problema de contabilidad con una estructura de datos compleja: tiene una tabla de asientos contables donde cada fila contiene múltiples tipos de asiento (cuentas contables repartidas en columnas) y un campo Concepto que agrupa varios conceptos separados por "/".

Por ejemplo, una fila puede tener:
- Concepto: "Salario Enero/Outsourcing/Ventas Enero//Amortización Enero"
- Columnas D-R: cuentas contables (640, 642, 476, 465, 607, 472, 400, 705, 477, 430, 628, 681, 216...) con sus importes

El objetivo es transformar esta tabla ancha en formato largo: Fecha | Concepto individual | Cuenta | Importe, donde cada concepto separado por "/" se asigna correctamente a su grupo de cuentas.

La dificultad es doble: hay que hacer el unpivot de las columnas (pasar de ancho a largo) y a la vez dividir el concepto por "/" para asignar cada parte a las cuentas que le corresponden. Las celdas vacías separan los grupos de cuentas que pertenecen a cada concepto.

John resuelve el problema con una fórmula que combina LET, APILARH, ENCOL, SI, TEXTODESPUES 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); ); MAX)); "/";;;1)); F(D2:R2); F(D3:R4))
)
)
`

La clave de esta solución 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. SCAN con MAX genera un contador incremental que, combinado con TEXTODESPUES/TEXTOANTES`, extrae el concepto correcto para cada cuenta.

Miki aporta una solución alternativa visible en el fichero, que desglosa los asientos de forma más directa, separando por tipo de asiento y concepto.

El fichero adjunto incluye la tabla original con el problema, la solución esperada y ambas soluciones.

El problema: un unpivot que además reparte conceptos

Juan trajo a la comunidad un problema de contabilidad con una estructura de datos endiablada. Tiene una tabla ancha donde cada fila mezcla varios tipos de asiento —las cuentas contables (640, 642, 476, 607, 400, 705...) repartidas en muchas columnas con sus importes— y un campo Concepto que agrupa varios conceptos separados por barras, por ejemplo: "Salario Enero / Outsourcing / Ventas Enero / Amortización Enero".

El objetivo es pasar esa tabla ancha a formato largo, con la forma Fecha | Concepto individual | Cuenta | Importe, de modo que cada concepto separado por barra quede asignado a su grupo de cuentas correcto. La dificultad es doble: hay que hacer el unpivot de las columnas (de ancho a largo) y, a la vez, partir el concepto y repartir cada trozo entre las cuentas que le tocan. Las celdas vacías de la fila de importes son las que marcan dónde termina un grupo de cuentas y empieza el siguiente.

La solución de John: un contador que identifica cada grupo

John lo resolvió con una única fórmula apoyada en LET, APILARH, ENCOL, SI, TEXTODESPUES, TEXTOANTES y, sobre todo, SCAN. La clave conceptual está en cómo detecta a qué grupo pertenece cada cuenta.

La fila de cabeceras del origen numera los bloques ("Tipo asiento 1", "Tipo asiento 2"...). John extrae la cifra final de cada cabecera y construye con SCAN y MAX un contador incremental 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 (el 1 coacciona el texto a número); SI.ERROR deja hueco donde no hay número, y SCAN con MAX arrastra hacia adelante el máximo visto, generando algo como 1, 1, 1, 2, 2, 3... — es decir, una etiqueta de grupo para cada columna de cuenta.

Con ese contador, TEXTODESPUES y TEXTOANTES recortan del campo Concepto justamente el trozo que corresponde a cada grupo: el contador dice "para esta cuenta, quiero el 2.º concepto de la lista separada por barras", y las funciones de texto lo extraen.

Cómo se apila todo junto

El resto es puro montaje. John define una función auxiliar que, con ENCOL(SI(...=""; ...); 2), aplana cada bloque en columna ignorando los vacíos (el segundo argumento de ENCOL igual a 2 descarta errores; combinado con SI sobre celdas vacías, salta los huecos). Aplica esa misma función a las fechas, a los conceptos ya recortados, a los nombres de cuenta y a los importes, y con APILARH los pone en cuatro columnas paralelas. Un APILARV remata añadiendo la fila de cabeceras ("Cuenta", "Importe"...) encima.

El resultado: la tabla ancha convertida en larga, con cada concepto correctamente emparejado a su grupo de cuentas, en una sola fórmula derramada.

La aportación de Miki

Miki ofreció una solución alternativa, más directa, que desglosa los asientos separando explícitamente por tipo de asiento y concepto. Llega al mismo formato largo por un camino menos denso, y sirve de contraste didáctico frente a la fórmula-navaja de John. El fichero del caso incluye la tabla original, el resultado esperado y las dos soluciones.

Funciones clave

  • SCAN + MAX: crean el contador incremental que etiqueta cada columna con su número de grupo.
  • TEXTOANTES / TEXTODESPUES: recortan del concepto múltiple el trozo que corresponde a cada grupo.
  • ENCOL con segundo argumento 2: aplana cada bloque en columna descartando huecos.
  • APILARH / APILARV: ensamblan las cuatro columnas y añaden las cabeceras.

Conclusión

Este es un unpivot "con inteligencia": no solo estira la tabla, sino que reparte un texto múltiple entre grupos de columnas usando un contador construido con SCAN. Es de esos casos que parecen imposibles sin VBA y que las matrices dinámicas resuelven en una fórmula. Retos contables de este calibre aparecen una y otra vez en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel