阶梯式收费计算器公式需求及现有SUMPRODUCT公式问题排查

原公式问题分析 #

你当前使用的=SUMPRODUCT(--(B1>=$A$3:$A$6),(B1-$A$3:$A$6),$D$3:$D$6)/100逻辑存在错误：它会重复计算各档的全额金额。比如投资金额为600k时，公式会把600k分别减去0、50k、500k后再乘以对应费率，等于把前两段金额重复按高费率计算，完全不符合阶梯收费仅收取增量部分的规则，因此金额超过500k时结果必然失效。

正确公式实现 #

假设你的表格结构如下：

• $A$3:$A$6 对应各档起始金额：0、50000、500000、1000000
• $C$3:$C$6 对应各档结束金额：50000、500000、1000000、999999999（用大数替代无上限）
• $D$3:$D$6 对应各档费率：0.35、0.20、0.15、0.10

使用以下公式可准确计算阶梯收费：
=SUMPRODUCT(MAX(0, MIN(B1, $C$3:$C$6) - $A$3:$A$6), $D$3:$D$6)/100

如果不想单独设置结束金额列，也可直接硬编码阈值（适合分档固定的场景）：
=SUMPRODUCT(MAX(0, MIN(B1, {50000,500000,1000000,999999999}) - {0,50000,500000,1000000}), {0.35,0.2,0.15,0.1})/100

公式逻辑说明 #

• MIN(B1, 档结束金额)：确保该档最多计算到实际投资金额，不会超出范围
• 减去档起始金额：得到该档内的实际收费基数
• MAX(0, ...)：若投资金额未达该档起始数，自动取0，避免出现负数计算
• 最后通过SUMPRODUCT将每一档的基数乘以对应费率求和，再除以100转换为百分比费用

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