Calcular costes por tramos escalonados con SUMAPRODUCTO y REDUCE

Un miembro de la comunidad desde Chile plantea un problema frecuente en facturación y logística: calcular el coste total cuando el precio varía por tramos (las primeras 10.000 unidades a un precio, las siguientes 4.000 a otro, etc.). La dificultad está en que cada tramo tiene un tope y un coste diferente.

Con SUMAPRODUCTO + LET (John): la solución matricial. Usa N(+rango) para coercer los límites de tramo en un array, y SUMAPRODUCTO para calcular el coste acumulado:

``
=LET(
h; N(+B1:B5);
SUMAPRODUCTO(--(F1 > h); F1 - h; C2:C6 - N(+C1:C5))
)
`

Sin LET (compatible con versiones anteriores):

`
=SUMAPRODUCTO(--(F1 > N(+B1:B5)); F1 - N(+B1:B5); C2:C6 - N(+C1:C5))
`

Con REDUCE + LAMBDA (John): enfoque iterativo que recorre cada tramo acumulando el coste con MIN para respetar los topes:

`
=SUMA(
REDUCE(0; B2:B6; LAMBDA(a; v;
APILARV(a; MIN(v; F1) - SUMA(a))
)) * N(+C1:C6)
)
`

John además explica un detalle técnico poco conocido: N(+rango) es necesario porque N(rango) sin el + solo devuelve el primer valor de un rango. El operador + fuerza la evaluación como array, y luego N() convierte el resultado a números.

Hugo conecta el problema con BUSCARV con coincidencia aproximada (VERDADERO como último argumento), que es la forma clásica de resolver escalones en Excel — aunque las soluciones con SUMAPRODUCTO y REDUCE` son mucho más flexibles y transparentes.

El problema: precios por tramos escalonados

Un miembro de la comunidad, desde Chile, planteó un cálculo muy habitual en facturación y logística: el precio total cuando el coste cambia por tramos. Las primeras 10.000 unidades a un precio, las siguientes 4.000 a otro, y así sucesivamente. Cada tramo tiene un tope y un coste distinto, y hay que acumular el gasto respetando esos escalones.

Solución 1: SUMAPRODUCTO + LET

John aporta la solución matricial. La clave es coercer los límites de cada tramo en un array y calcular, para cada escalón, cuántas unidades caen dentro y a qué coste:

=LET(
    h; N(+B1:B5);
    SUMAPRODUCTO(N(F1 > h); F1 - h; C2:C6 - N(+C1:C5))
)

N(F1 > h) genera un array de 1 y 0 según qué tramos supera la cantidad F1; F1 - h mide cuántas unidades entran en cada tramo, y C2:C6 - N(+C1:C5) es el coste incremental de cada escalón respecto al anterior. SUMAPRODUCTO multiplica y suma todo en una pasada.

Para versiones de Excel sin LET, la misma lógica cabe en una línea:

=SUMAPRODUCTO(N(F1 > N(+B1:B5)); F1 - N(+B1:B5); C2:C6 - N(+C1:C5))

Solución 2: REDUCE + LAMBDA

Un enfoque iterativo que recorre cada tramo acumulando el coste, usando MIN para no pasarse del tope:

=SUMA(
    REDUCE(0; B2:B6; LAMBDA(a; v;
        APILARV(a; MIN(v; F1) - SUMA(a))
    )) * N(+C1:C6)
)

En cada paso, MIN(v; F1) recorta la cantidad al tope del tramo y le resta lo ya acumulado, obteniendo las unidades que caen exactamente en ese escalón. Al final se multiplica por los costes y se suma.

El detalle técnico: por qué N(+rango)

John explica un matiz poco conocido que hace funcionar todo esto. N(rango) a secas solo devuelve el primer valor de un rango. Para que actúe sobre todo el array hay que anteponer el operador +: N(+rango). El + fuerza la evaluación como matriz y luego N() convierte el resultado en números. Sin ese +, las fórmulas de arriba darían un único valor en lugar del array completo.

La alternativa clásica: BUSCARV aproximado

Hugo recordó la vía tradicional: BUSCARV con coincidencia aproximada (el último argumento a VERDADERO), que localiza el escalón correspondiente en una tabla de tramos. Funciona y es familiar, pero las soluciones con SUMAPRODUCTO y REDUCE son más flexibles y transparentes, sobre todo cuando el número de tramos cambia.

Funciones clave

  • SUMAPRODUCTO: multiplica y suma los tres arrays (dentro de tramo, unidades y coste incremental) en una operación.
  • N con el operador +: coacciona un rango entero a números como array.
  • REDUCE + APILARV: recorren los topes acumulando unidades por tramo.
  • MIN: respeta el tope de cada escalón.

Conclusión

El cálculo por tramos es de esos que todo el mundo resuelve con una tabla y un BUSCARV, hasta que descubre que con SUMAPRODUCTO cabe en una celda y se adapta sola a cualquier número de escalones. El truco del N(+rango) para forzar la evaluación como array es, además, una de esas perlas técnicas que solo aprendes viendo trabajar a alguien que domina Excel. En la comunidad de Influexcel salen cada semana.

Más casos con estas funciones

Más contenido de Excel en InflueXcel