Excel 固定金额滑动比例金额公式

Question

我想根据下面需求价格表计算每月费用总额。

价格表* – 每位最终用户的月费

1-100 end users:        $29 each
101 - 250 end users:        $27 each
251 - 500 end users:        $25 each
501 - 750 end users:        $23 each
750 - 1000 end users:   $21 each
1001 - 1500 end users:  $19 each
1501 - 2000 end users:  $15 each
2001 - 2500 end users:  $11 each
2501 - 5000 end users:  $9 each
5001+ end users:        $7 each

*价格适用于各自层级的每位客户。为清楚起见，前 100 位最终用户的费用仍为 29 美元，无论最终用户总数有多少。第 101 位至第 250 位最终用户的费用为 27 美元，依此类推。

输入是最终用户的数量，输出是每月费用总额

例如，有 150 个最终用户，输出将是：

(100*29) + (49*27)
= 4,223

手动计算很容易，但真正需要一个足够智能的公式来计算，只需给出最终用户的数量

Answer 1

我最终采用了这个公式。不确定这是否是最好的方法，但似乎有效

=(MIN(B1,100)*29)+
MAX(0,(MIN(250-100,(B1-100))*27))+
MAX(0,(MIN(500-250,(B1-250))*25))+
MAX(0,(MIN(750-500,(B1-500))*23))+
MAX(0,(MIN(1000-750,(B1-750))*21))+
MAX(0,(MIN(1500-1000,(B1-1000))*19))+
MAX(0,(MIN(2000-1500,(B1-1500))*15))+
MAX(0,(MIN(2500-2000,(B1-2000))*11))+
MAX(0,(MIN(5000-2500,(B1-2500))*9))+
MAX(0,(B1-5000)*7)

Answer 2

此类问题通常最好通过查找表和VLOOKUP函数来解决。

如果将来情况发生变化，它可以使维护变得更加容易。

例如，像这样设置您的monthlyTable：

第一列和第三列是手动输入的。该Base列使用以下公式计算：

H2: 0
H3: =(G3-G2)*I2+H2

然后您可以使用以下公式：

B2: =VLOOKUP(A2,monthlyFees,2)+(A2-VLOOKUP(A2,monthlyFees,1))*VLOOKUP(A2,monthlyFees,3)