Regularización trimestral con AGRUPARPOR y ARCHIVOMAKEARRAY: del caos a una fórmula

Caso fresquito de la comunidad. Juan plantea un problema contable: tiene una tabla de movimientos (Nombre, Concepto, Importe, Cuenta, Fecha) y necesita generar automáticamente los asientos de regularización trimestral: cada trimestre, anular los importes del periodo con fecha del último día del trimestre y añadir una fila de "Regularización" que cuadre todo contra la cuenta 4709 o 4759 según el saldo del trimestre. Al final del año, una regularización anual con la misma lógica.

Su primer intento era una fórmula gigantesca con varios FILTRAR repetidos por trimestre que se volvía inmanejable cada vez que aparecía un nuevo año o un nuevo trimestre.

La idea clave de la solución es separar el problema en dos pasos dentro de un único LET:

1. Agrupar los importes por año y trimestre con AGRUPARPOR, con la clave APILARH(AÑO(fecha); REDONDEAR.MAS(MES(fecha)/3; 0)). Sin columnas auxiliares: el trimestre sale directo de la fecha.
2. Reconstruir el rango con ARCHIVOMAKEARRAY invirtiendo el signo del importe y empujando la fecha al fin de trimestre con FIN.MES(FECHA(AÑO; 3·trimestre; 1); 0).
3. Generar las filas de regularización con otro ARCHIVOMAKEARRAY sobre el resultado de AGRUPARPOR, eligiendo 4709 o 4759 según el signo del saldo trimestral y fechando cada fila con el año y el trimestre de la clave.
4. Apilar ambos bloques con APILARV y ordenar por fecha y cuenta con ORDENAR.

La fórmula completa, derramada desde una sola celda:

``
=LET(
rango; B4:F10;
f; ELEGIRCOLS(rango; 5);
saldos; AGRUPARPOR(APILARH(AÑO(f); REDONDEAR.MAS(MES(f)/3; 0)); ELEGIRCOLS(rango; 3); SUMA; 0; 0);
rango_nuevo; ARCHIVOMAKEARRAY(FILAS(rango); 5;
LAMBDA(r;c;
SI.CONJUNTO(
c=3; INDICE(rango;r;3)-1;
c=5; FIN.MES(FECHA(AÑO(INDICE(rango;r;5)); 3REDONDEAR.MAS(MES(INDICE(rango;r;5))/3;0); 1); 0);
VERDADERO; INDICE(rango;r;c)
)
)
);
regularizacion; ARCHIVOMAKEARRAY(FILAS(saldos); 5;
LAMBDA(r;c;
SI.CONJUNTO(
c=3; INDICE(saldos;r;3);
c=4; SI(INDICE(saldos;r;3)>0; 4709; 4759);
c=5; FIN.MES(FECHA(INDICE(saldos;r;1); INDICE(saldos;r;2)3; 1); 0);
VERDADERO; "Regularización"
)
)
);
ORDENAR(APILARV(rango_nuevo; regularizacion); {5\4})
)
`

El resultado: una única celda que devuelve el rango regularizado completo, listo para el cierre contable. Si el año que viene aparecen nuevos movimientos o un nuevo trimestre, no hay que tocar nada: basta con extender el rango de origen.

Juan respondió "es increíble, es perfecta". El hilo terminó con una pequeña master class sobre abreviar funciones nativas con alias dentro de LET (poner I como alias de INDICE o F como alias de FECHA), porque al probarlo la fórmula devolvía #¡VALOR!. El alias en sí funciona: LET(I; INDICE; I(A2:A4; 2)) devuelve el segundo valor, y F como alias de FECHA no da ningún problema. La trampa está en que INDICE devuelve una referencia, y un alias que devuelve una referencia falla en cuanto su resultado entra directo en una operación: I(rango;r;3)-1, la columna del importe, da #¡VALOR!. La salida es definir el alias como una LAMBDA propia, I; LAMBDA(m; x; y; INDICE(m; x; y)), que devuelve el valor y no la referencia; así la fórmula de arriba funciona entera con los alias. Un detalle más: LET no distingue mayúsculas, y como la fórmula ya tiene una f, el alias de FECHA tiene que llamarse de otra forma (FE, por ejemplo).

Nota (corregido el 26/9/2026): la fórmula del hilo agrupaba solo por trimestre y escribía el año a mano, FECHA(2026; …). Con movimientos de dos años, el primer trimestre de 2027 se sumaba al de 2026 y 2027 se quedaba sin regularizar. Ahora la clave lleva el año y la fecha de cierre sale de ella. Comprobado en Excel con movimientos de 2026 y 2027. La regularización anual que pedía el enunciado no está en la fórmula: sería otro AGRUPARPOR, con el año como única clave. Y el párrafo de los alias decía que LET no admite funciones nativas como alias; sí las admite, y lo que fallaba era el alias de INDICE` dentro de una operación. Comprobado también en Excel.

El problema: asientos de regularización que no escalan

Juan trajo a la comunidad un problema contable clásico. Tiene una tabla de movimientos (Nombre, Concepto, Importe, Cuenta, Fecha) y necesita generar automáticamente los asientos de regularización trimestral: cada trimestre hay que anular los importes del periodo con fecha del último día del trimestre y añadir una fila de "Regularización" que cuadre el saldo contra la cuenta 4709 o 4759, según sea acreedor o deudor. Al cierre del año, una regularización anual con la misma lógica.

Su primer intento fue una fórmula enorme con varios FILTRAR repetidos, uno por trimestre. Funcionaba, pero se volvía inmanejable en cuanto aparecía un año nuevo o un trimestre más.

La idea: partir el problema en pasos dentro de un LET

La solución elegante no ataca todo a la vez. Divide el trabajo en piezas dentro de un único LET:

  1. Agrupar los importes por año y trimestre con AGRUPARPOR: la clave junta AÑO(fecha) y REDONDEAR.MAS(MES(fecha)/3;0). El trimestre sale directo de la fecha, sin columnas auxiliares, y el año evita que se mezclen trimestres de años distintos.
  2. Reconstruir el rango original con ARCHIVOMAKEARRAY, invirtiendo el signo del importe y empujando la fecha al fin de trimestre con FIN.MES.
  3. Generar las filas de regularización con otro ARCHIVOMAKEARRAY sobre el resultado de AGRUPARPOR, eligiendo la cuenta según el signo del saldo.
  4. Apilar ambos bloques con APILARV y ordenar con ORDENAR.

La fórmula

=LET(
    rango; B4:F10;
    f; ELEGIRCOLS(rango; 5);
    saldos; AGRUPARPOR(APILARH(AÑO(f); REDONDEAR.MAS(MES(f)/3; 0)); ELEGIRCOLS(rango; 3); SUMA; 0; 0);
    rango_nuevo; ARCHIVOMAKEARRAY(FILAS(rango); 5;
        LAMBDA(r;c;
            SI.CONJUNTO(
                c=3; INDICE(rango;r;3)*-1;
                c=5; FIN.MES(FECHA(AÑO(INDICE(rango;r;5)); 3*REDONDEAR.MAS(MES(INDICE(rango;r;5))/3;0); 1); 0);
                VERDADERO; INDICE(rango;r;c)
            )
        )
    );
    regularizacion; ARCHIVOMAKEARRAY(FILAS(saldos); 5;
        LAMBDA(r;c;
            SI.CONJUNTO(
                c=3; INDICE(saldos;r;3);
                c=4; SI(INDICE(saldos;r;3)>0; 4709; 4759);
                c=5; FIN.MES(FECHA(INDICE(saldos;r;1); INDICE(saldos;r;2)*3; 1); 0);
                VERDADERO; "Regularización"
            )
        )
    );
    ORDENAR(APILARV(rango_nuevo; regularizacion); APILARH(5; 4))
)

El último argumento de ORDENAR, APILARH(5; 4), es una matriz de columnas que ordena primero por la columna 5 (la fecha) y después por la columna 4 (la cuenta), dejando el bloque listo para el cierre.

Por qué es robusto

Lo potente de este enfoque es que no hay nada codificado a mano por trimestre. La clave de agrupación se calcula desde la fecha, así que si el año que viene aparecen movimientos nuevos o un trimestre más, basta con extender el rango de origen y la fórmula se recalcula sola. Para eso la clave lleva el año: sin él, el primer trimestre de dos años distintos acabaría sumado en uno. Juan lo resumió en tres palabras: "es increíble, es perfecta".

Un apunte sobre alias en LET

El hilo terminó con una mini master class. Alguien intentó abreviar funciones nativas dentro del LET (poner I como alias de INDICE o F como alias de FECHA) y obtuvo #¡VALOR!. El alias en sí funciona: LET(I; INDICE; I(A2:A4; 2)) devuelve el segundo valor. La trampa está en que INDICE devuelve una referencia, y un alias que devuelve una referencia falla en cuanto su resultado entra directo en una operación, como el *-1 que invierte el importe. La salida es definir el alias como una LAMBDA propia, I; LAMBDA(m; x; y; INDICE(m; x; y)), que devuelve el valor y no la referencia. Y como LET no distingue mayúsculas, el alias de FECHA no puede llamarse F si la fórmula ya tiene una f.

Funciones clave

  • AGRUPARPOR: agrupa y suma por año y trimestre usando una clave calculada, sin columnas auxiliares.
  • ARCHIVOMAKEARRAY: construye un rango celda a celda con una LAMBDA(fila; columna).
  • SI.CONJUNTO: decide qué poner en cada columna según su posición.
  • FIN.MES y FECHA: empujan cada importe al último día de su trimestre.
  • APILARV y ORDENAR: juntan y ordenan los dos bloques.

Conclusión

Cuando una fórmula "de fuerza bruta" se te va de las manos, casi siempre hay una versión que calcula la estructura en vez de escribirla a mano. Este caso, salido de un problema real de cierre contable, es un buen ejemplo de cómo AGRUPARPOR y ARCHIVOMAKEARRAY convierten un monstruo de FILTRAR repetidos en una sola celda que se mantiene sola. Ese tipo de ideas es lo que sale cada semana en la comunidad de Influexcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel