Cálculos iterativos con REDUCE y APILARV en Excel

La comunidad aborda un caso sobre cómo trabajar con bucles iterativos en Excel usando funciones modernas. Un miembro necesita realizar cálculos acumulativos que dependen de valores anteriores, aplicando coeficientes desde distintas matrices.

Leo propone una solución elegante con REDUCE y APILARH, explicando paso a paso la lógica del bucle:

``
=REDUCE(G4;SECUENCIA(10);LAMBDA(a;v;APILARH(a;TOMAR(a;;-1)-(v-2)INDICE(H2:Q2;v)0,561^(v-1))))
`

La clave del enfoque es entender que REDUCE recorre una SECUENCIA(10) como bucle, y en cada iteración se relacionan elementos de distintas matrices mediante INDICE, usando la posición del valor "v" del bucle. Como REDUCE solo devuelve el último valor acumulado, se intercala un APILARH (o APILARV según la orientación) para ir apilando los resultados intermedios.

Para extraer el último valor del acumulado en cada paso, se usa TOMAR con -1: si se apila horizontalmente, TOMAR(a;;-1) extrae la última columna; si se apila verticalmente, TOMAR(a;-1)` extrae la última fila.

Leo menciona también que existen técnicas alternativas con thunks para este tipo de trabajo, aunque son más difíciles de entender.

Hay cálculos que no se pueden resolver de un tirón porque cada paso depende del anterior: amortizaciones, saldos que arrastran, series que aplican un coeficiente distinto en cada periodo. En Excel clásico eso se hacía con una columna auxiliar y arrastrando la fórmula hacia abajo. Con las funciones modernas se puede hacer en una sola celda, y la herramienta es REDUCE. El caso que planteó la comunidad tiene además el matiz que más cuesta al principio: cómo guardar todos los resultados intermedios y no solo el último.

REDUCE como bucle

REDUCE recibe tres cosas: un valor inicial, un rango que recorrer y una LAMBDA que se ejecuta en cada vuelta. La clave está en el segundo argumento: si le pasas SECUENCIA(10), no estás recorriendo datos, estás contando iteraciones. Es literalmente un bucle de diez pasos.

Dentro de la LAMBDA tienes dos parámetros:

  • a, el acumulado que traes de la vuelta anterior.
  • v, el número de iteración en la que estás.

Ese v es mucho más útil de lo que parece, porque sirve de índice para ir a buscar el dato que toca en otras matrices. Si tienes los coeficientes en una fila y los pesos en otra, INDICE(rango; v) te da el elemento correspondiente a la iteración actual. Así puedes relacionar varias tablas dentro del mismo bucle sin necesidad de alinearlas previamente.

El problema: REDUCE solo devuelve el último valor

Aquí está el punto que hace tropezar a casi todo el mundo. REDUCE está pensado para reducir: recorre todo y te entrega un único resultado final. Si lo que necesitas es la serie completa —el valor de cada uno de los diez periodos— la salida por defecto no te sirve.

La solución que propuso Leo es un patrón que conviene memorizar: en lugar de guardar un número en el acumulador, guarda una matriz que va creciendo. En cada vuelta apilas el nuevo resultado junto a los anteriores:

=REDUCE(valor_inicial; SECUENCIA(10); LAMBDA(a; v;
    APILARH(a; nuevo_valor)
))

Con APILARH la serie crece hacia la derecha; con APILARV, hacia abajo. Elige según cómo quieras ver el resultado en la hoja.

Cómo leer el último valor calculado

Si el acumulador ya no es un número sino una matriz, la fórmula del paso siguiente no puede usar a directamente: necesita el último elemento de esa matriz, que es el resultado de la iteración anterior.

Eso se consigue con TOMAR y un índice negativo, que significa "cuenta desde el final":

  • Si apilas en horizontal, tomas la última columna dejando el argumento de filas vacío y pidiendo una columna contando desde el final.
  • Si apilas en vertical, tomas la última fila con el mismo planteamiento aplicado al primer argumento.

Con esas dos piezas —APILARH para conservar la historia y TOMAR para recuperar el último valor— ya tienes el esqueleto completo de cualquier cálculo iterativo:

=REDUCE(valor_inicial; SECUENCIA(n); LAMBDA(a; v;
    APILARH(a; ULTIMO(a) - INDICE(coeficientes; v) * factor(v))
))

donde ULTIMO(a) es ese TOMAR de la última columna y la parte de la derecha es tu fórmula de negocio, la que combine el valor anterior con el coeficiente de la iteración v.

Consideraciones de rendimiento

Este patrón es potente pero no gratis: en cada vuelta se construye una matriz nueva un poco más grande que la anterior, así que el coste crece más deprisa que el número de iteraciones. Con diez o veinte pasos ni lo notarás; con miles, la hoja se resiente.

Leo mencionó que existen técnicas alternativas basadas en thunks —almacenar funciones sin evaluar dentro del acumulador— que evitan ese crecimiento. Son considerablemente más difíciles de leer, así que la recomendación práctica es empezar por el apilado y reservar los thunks para cuando el rendimiento sea un problema real y medido.

Funciones clave

  • REDUCE: recorre un rango arrastrando un acumulador y devuelve solo el resultado final.
  • SECUENCIA: genera los números de iteración; convierte REDUCE en un bucle de N pasos.
  • APILARH y APILARV: hacen crecer el acumulador para conservar todos los resultados intermedios.
  • TOMAR: con índice negativo extrae los últimos elementos, que es como se recupera el valor de la vuelta anterior.
  • INDICE: usa el número de iteración para ir a buscar el coeficiente que toca en otra matriz.

Conclusión

REDUCE con SECUENCIA es lo más parecido a un bucle que tiene Excel sin salir de las fórmulas, y el truco de apilar el acumulador convierte ese bucle en una serie completa que se calcula sola. Merece la pena montarlo una vez con calma: el mismo esqueleto sirve para amortizaciones, saldos acumulados, simulaciones y cualquier cálculo donde el paso de hoy dependa del de ayer.

Como casi todo lo que publicamos, este patrón salió de un problema concreto planteado en la comunidad de InflueXcel y de la explicación paso a paso de otro miembro.

Más casos con estas funciones

Más contenido de Excel en InflueXcel