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, Cuenta, Importe, 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 trimestre con AGRUPARPOR usando REDONDEAR.MAS(MES(fecha)/3; 0) como clave de agrupación. 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.
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;
saldos; AGRUPARPOR(REDONDEAR.MAS(MES(ELEGIRCOLS(rango;5))/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;2);
c=4; SI(INDICE(saldos;r;2)>0; 4709; 4759);
c=5; FIN.MES(FECHA(2026; INDICE(saldos;r;1)*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 por qué intentar abreviar funciones nativas con alias dentro de LET (poner I como alias de INDICE o F como alias de FECHA) devuelve #¡VALOR!: Excel Labs no permite renombrar funciones nativas como alias dentro de un LET. Para algo así hay que crear LAMBDA` auxiliares en el Administrador de nombres.