Unpivot con separación de cuentas contables concatenadas

El problema: dos transformaciones a la vez

Juan planteó en el grupo un caso que mezcla dos operaciones que normalmente se hacen por separado. Su diario contable tiene una columna donde las cuentas vienen pegadas con una barra inclinada (por ejemplo la 621, la 472 y la 410 en una sola celda) y, justo al lado, tres columnas de importes: uno por cada una de esas cuentas.

Lo que necesita es un unpivot, sí, pero uno especial: además de desplegar los importes en vertical, hay que partir la cadena de cuentas y emparejar cada cuenta con su importe correspondiente. Si haces solo el unpivot, la columna de cuentas se repite entera en las tres filas y el resultado no sirve para nada.

Partiendo de una fila con fecha, la cadena de tres cuentas, tres importes y un concepto, el resultado que busca es una fila por cuenta: la 621 con su importe, la 472 con el suyo y la 410 con el suyo, arrastrando fecha y concepto en cada una.

Juan ya tenía una LAMBDA heredada de otro ejercicio que casi lo resolvía. Casi: los importes se desplegaban bien, pero la cuenta se repetía. La comunidad respondió con tres enfoques distintos, y merece la pena verlos los tres porque cada uno ataca el problema desde un ángulo diferente.

Enfoque 1: aplanar y repartir con ENCOL

La propuesta de Nacho se apoya en un alias corto y una LAMBDA auxiliar para no repetir la misma operación cuatro veces. La estructura, en pseudocódigo, es esta:

=LET(
    H; APILARH;
    F; LAMBDA(x; ENCOL(SI(esVacio; NOD(); x); 2));
    APILARV(
        H(
            F(columnaFecha);
            H(
                EXCLUIR(ENCOL(DIVIDIRTEXTO(cuentasUnidas; barra)); ultimo);
                F(filaCabeceras);
                F(bloqueImportes);
                F(columnaConcepto)
            )
        )
    )
)

La idea clave está en el par formado por la función de unión de texto y DIVIDIRTEXTO. En vez de partir la cadena fila a fila, se une toda la columna de cuentas en una sola cadena añadiendo una barra al final de cada una, y luego se parte de golpe por esa barra. Sale un vector con todas las cuentas de todas las filas seguidas, en el orden correcto. A ese vector le sobra un elemento vacío al final, el residuo de la última barra, y ahí es donde entra EXCLUIR: en la fórmula real se le pasa un índice negativo para que quite ese elemento contando desde el final en lugar de desde el principio.

El segundo truco es el SI contra el bloque de importes: le da a cada columna (la fecha, las cabeceras, el concepto) la forma de ese bloque, con cada valor repetido una vez por importe, y ENCOL la aplana por filas, que es justo lo que hace falta para que cada bloque de tres importes quede pegado a su fecha. El 2 de ENCOL no agrupa nada: le dice que ignore los errores. Por eso los huecos van marcados con NOD(): si un asiento trae dos cuentas en vez de tres, su importe vacío desaparece al aplanar y no deja una fila de más.

Enfoque 2: construir fila a fila con REDUCE y EXPANDIR

La segunda solución cambia de mentalidad: en vez de aplanar, va acumulando bloques. La pieza central es una LAMBDA auxiliar que recibe una columna y repite cada valor tres veces usando EXPANDIR, apilando cada bloque con APILARV dentro de un REDUCE:

LAMBDA(x;
    REDUCE(
        vacio;
        x;
        LAMBDA(acumulado; valor;
            APILARV(acumulado; EXPANDIR(valor; 3;; valor))
        )
    )
)

Aplicando esa función a la fecha, al concepto y a la etiqueta de columna, los tres quedan replicados tantas veces como cuentas hay en cada fila. Los importes se apilan con un REDUCE sencillo, y las cuentas se parten con DIVIDIRTEXTO dentro del mismo patrón. Al final se pegan todas las columnas con APILARH.

Un detalle práctico: REDUCE necesita un valor inicial para el acumulador, y ese valor aparece como primera fila del resultado. Por eso la fórmula real termina con un EXCLUIR que se lo quita.

Es más largo que el anterior, pero se lee mejor: cada columna del resultado se construye por separado y luego se ensamblan. Eso sí, el 3 de EXPANDIR va fijo, así que vale cuando todos los asientos traen tres cuentas.

Enfoque 3: la versión de Gerson, dos separadores en un paso

La más compacta de las tres, y la más ingeniosa. Gerson aprovecha que DIVIDIRTEXTO acepta dos delimitadores: uno para separar columnas y otro para separar filas.

Une todas las cuentas en una sola cadena marcando el final de cada fila con un carácter poco habitual, una barra vertical que no aparece nunca en un número de cuenta, y dejando la barra inclinada como separador de cuentas. Con esos dos delimitadores, una sola llamada a DIVIDIRTEXTO devuelve ya una matriz con la forma exacta del resultado: una fila por asiento y una columna por cuenta. Una multiplicación por uno convierte el texto resultante en número.

El segundo detalle es el mejor: esa misma matriz se reutiliza como máscara. Una LAMBDA auxiliar aplica un SI sobre ella para dejar pasar solo los valores en las posiciones donde efectivamente hay cuenta, y ENCOL los apila. Si un asiento trae menos cuentas que otro, DIVIDIRTEXTO rellena el hueco con #N/D, y por eso ese ENCOL lleva un 2: tira los errores al aplanar. Una matriz que sirve a la vez de dato y de plantilla, y toda la solución cabe en cinco líneas con un APILARH final.

Funciones clave

• Funciones de unión de texto sobre un rango — devuelven una sola cadena; es la base para aplanar antes de partir.
• DIVIDIRTEXTO — parte texto por uno o dos delimitadores; con dos genera directamente una matriz bidimensional.
• ENCOL — apila una matriz en una columna; su segundo argumento decide qué ignora (con un 2, los errores) y el tercero, si recorre por filas o por columnas.
• EXPANDIR — repite o rellena un valor hasta un tamaño dado, ideal para replicar la fecha en cada cuenta.
• REDUCE junto a APILARV — el patrón estándar para construir un resultado acumulando bloques.
• EXCLUIR — quita filas o columnas sobrantes, tanto el valor inicial de REDUCE como el residuo del separador final.

Conclusión

Tres personas, tres caminos, el mismo resultado. La versión de Nacho es la más directa si ya dominas ENCOL; la de REDUCE es la más legible, aunque lleva fijo el número de cuentas; la de Gerson es la que menos escribe y la que más te enseña sobre lo que DIVIDIRTEXTO puede hacer con dos delimitadores.

Las reacciones de Juan en el grupo resumen bien la sesión: "Solución Increíble", "Perfecta", "Buenísima también". Este es exactamente el tipo de problema que sale a diario en la comunidad de InflueXcel: alguien trae un caso real de contabilidad y, en unas horas, hay tres formas de resolverlo sobre la mesa.

Más casos con estas funciones

• LAMBDA
• LET
• APILARH
• APILARV
• REDUCE
• DIVIDIRTEXTO

Más contenido de Excel en InflueXcel

• 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
• 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
• 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
• 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
• Combinaciones de N elementos tomados de K en K: fórmulas con REDUCE y LAMBDA Caso
  Sebas comparte un archivo con un listado de 11 elementos y necesita generar todas las combinaciones posibles tomados de 2 en 2 (sin repetici
• Consolidar datos repetidos con REDUCE, APILARV y PIVOTARPOR Caso
  La comunidad aborda un caso de consolidación de datos donde hay tareas con encabezados repetidos que necesitan combinarse dinámicamente. El
• 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á
• REDUCE de CERO a PRO Tutorial
  Con REDUCE podrás transformar listas simples en análisis completos y automatizar cálculos que antes parecían imposibles. Si trabajas con dat
• Media móvil dinámica: 6 enfoques con SCAN, MMULT y MAP Caso
  Un miembro de la comunidad tiene un rango con ventas mensuales y necesita generar una columna con la media móvil de los últimos 3 meses usan
• Cruzar albaranes entre hojas con AJUSTARFILAS y BUSCARX Caso
  Un miembro de la comunidad tiene números de albarán en una hoja y, en otra hoja, varias columnas con pares albarán-importe (distribuidos hor