Excel: Realiza un cálculo con tarifa escalonada

Trabajar con una planilla de cálculos con tarifas escalonadas puede ser un gran inconveniente si no razonamos bien cómo organizar las funciones. Veamos con qué herramientas contamos.
VIE, 29 / MAY / 2015

Este es un problema interesante. Si tenemos una planilla que contiene un tarifario con diferentes valores:

Hasta 100 unidades, 10$ cada una.
Más de 100 unidades y hasta 500, 8$ cada una.
Más de 500 unidades y hasta 1000, 5$ cada una.
Más de 1000 unidades, 3$ cada una.

Según esto, para una cantidad de 1200 unidades, tenemos que separar el cálculo en cuatro partes:

Por las primeras 100, se paga un total de 1000$ (10$ cada una).
Por las siguientes 400 (hasta llegar a 500) se paga un total de 3200$ (8$ cada una).
Por las siguientes 500 (hasta llegar a 1000) se paga un total de 2500$ (5$ cada una).
Por las restantes 200 (hasta completar las 1200) se paga un total de 600$ (3$ cada una).
Todo esto da un gran total de 7300$.

Es decir que, para calcular el importe total, tenemos que ver dentro de qué escalón cae la  cantidad comprada. Calculamos luego los importes correspondientes a los escalones anteriores y sumamos el importe a la porción de escalón actual. En función de esto, preparamos la siguiente tabla auxiliar con tres columnas:

En la primera columna escribimos los topes de los escalones: 0, 100, 500 y 1000.

En la segunda columna escribimos el importe correspondiente a los “escalones completos”: 0, 1000, 4200 (1000+3200) y 6700 (4200+2500).

En la tercera columna escribimos el precio unitario correspondiente a cada escalón: 10, 8, 5 y 3.

Digamos que armamos esta tabla en el rango [D1:F4]. Para una cantidad dada tenemos que hacer todo esto:

  1. Buscamos el escalón que le corresponde y tomamos nota del importe correspondiente a los escalones anteriores (segunda columna).
  2. Restamos el valor del escalón (primera columna) de la cantidad.
  3. Multiplicamos esa diferencia por el precio unitario (tercera columna).
  4. Sumamos los importes de los pasos 2 y 3.

Todo esto lo podemos hacer con funciones

BUSCARV. Vamos a suponer que la cantidad vendida está en [A1]:

=BUSCARV(A1;D1:F4;2) nos da el acumulado de los escalones anteriores.
=BUSCARV(A1;D1:F4;1) nos da el valor inicial del escalón actual.
 =BUSCARV(A1;D1:F4;3) nos da el precio unitario para el escalón actual.

El importe total lo obtenemos con =BUSCARV(A1;D1:F4;2)+(A1-BUSCARV(A1;D1:F4;1))* BUSCARV(A1;D1:F4;3).

Esta fórmula suma el acumulado de los escalones anteriores al producto del precio unitario por el excedente por sobre el último escalón. Es complicado, pero funciona.

¡Comparte esta noticia!

Últimos lanzamientos Ver más