如何用Excel标准公式计算阶梯定价总成本（无需多层IF）

核心思路 #

利用SUMPRODUCT结合区间数组实现可扩展的阶梯计费计算，无需修改公式即可增减阶梯数量，替代繁琐的多层IF或复杂的VLOOKUP方案。

阶梯表设置 #

先将阶梯定价整理为区间表（示例）：

• 区间下限：每段阶梯的起始用量（第一段从0开始）
• 区间上限：每段阶梯的结束用量，最后一段设为远大于最大可能用量的数值（如999999），确保覆盖所有情况
• 费率：对应区间的单位计费费率

单单元格公式 #

假设当日用量存于单元格A1，区间下限在B2:B3，区间上限在C2:C3，费率在D2:D3，公式为：

=SUMPRODUCT(MAX(MIN(A1, C2:C3)-B2:B3, 0), D2:D3)

公式原理拆解 #

1. MIN(A1, C2:C3)-B2:B3：计算每个区间的实际可计费用量
   • 若当日用量超过区间上限，取区间长度（上限-下限）
   • 若当日用量在区间内，取用量与区间下限的差值
   • 若当日用量未达区间下限，结果为负数
2. MAX(..., 0)：将负数结果转为0，避免未覆盖区间产生负费用
3. SUMPRODUCT：将各区间的实际用量与对应费率相乘后求和，得到总费用

示例验证 #

当A1=120时：

• 第一段计算：MIN(120,100)-0=100 → MAX(100,0)=100 → 100*8=800
• 第二段计算：MIN(120,999999)-100=20 → MAX(20,0)=20 → 20*4=80
• 总费用：800+80=880，与示例结果一致

扩展说明 #

如需新增阶梯（如第三阶梯：200以上费率2），只需在区间表中新增一行：

公式无需修改，SUMPRODUCT会自动遍历所有区间计算总和。

内容的提问来源于stack exchange，提问作者Skeeve