Excel中如何编写可下拉填充的分段阶梯计算通用公式？

场景梳理 #

• 需实现阶梯分段累加计算逻辑，规则参考：
• 已完成数值≤100区间的计算，所用公式为=IFS(H6<E7,H6*F6)，无法覆盖数值跨多个分段的累加场景
• 规则验证：待计算值为600时，手动计算逻辑为100*2 + 400*4 + 100*6，结果为2400

实现方案 #

这类超额累进计算不需要嵌套多层IF，用SUMPRODUCT函数即可写出支持下拉填充的通用公式，后续调整分段单价、新增分段都不需要大幅改公式。

表格前置调整 #

先把分段规则表整理为规范结构：

• E列存储各分段上限：E7填第一档上限100，E8填第二档上限500，最后一档上限填一个远大于业务可能最大值的数（比如1000000，避免数值超过最高档时计算错误）
• F列存储对应分段单价：F7填第一档单价2，F8填第二档单价4，F9填第三档单价6
• E6单元格填0，作为第一档的计算起点

通用可扩展公式 #

在第一行结果单元格输入以下公式，直接下拉即可批量计算所有行的结果：

=SUMPRODUCT(
  (H6>E$6:OFFSET(E$6,COUNT(E:E)-6,0))*
  (H6<OFFSET(E$6,1,0,COUNT(E:E)-6,1))*
  (H6-E$6:OFFSET(E$6,COUNT(E:E)-6,0))*
  F$7:OFFSET(F$7,COUNT(E:E)-7,0)
)
+
SUMPRODUCT(
  (H6>=OFFSET(E$6,1,0,COUNT(E:E)-6,1))*
  (OFFSET(E$6,1,0,COUNT(E:E)-6,1)-E$6:OFFSET(E$6,COUNT(E:E)-6,0))*
  F$7:OFFSET(F$7,COUNT(E:E)-7,0)
)

公式特性：

• 分段规则区域的行号加$锁死，下拉时规则引用不会偏移
• 待计算值H6用相对引用，下拉时自动匹配当前行的待计算数值
• 自动识别E、F列已填写的分段规则，后续新增分段只要在E、F列往下补填上限和单价，不需要修改公式

固定3档简化公式 #

如果你的分段固定为3档，可以直接用更短的公式，计算结果完全一致：

=IF(H6<=E7,H6*F7,E7*F7+IF(H6<=E8,(H6-E7)*F8,(E8-E7)*F8+(H6-E8)*F9))

代入测试值600计算，结果为2400，和手动计算逻辑完全匹配。

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