基于日费率、起止日期及费率变更日期计算月度金额

问题根源 #

原公式未正确将业务结束日期（$C7）纳入日期范围判断，导致计算时出现「结束日期早于开始日期」的差值，最终产生负数结果。

优化思路 #

核心是通过MAX和MIN锁定每个费率阶段的有效日期边界，确保所有计算的日期区间都是合法的（起始≤结束），同时覆盖以下约束：

• 区间必须落在当前统计月度内（月度起始日到月度最后一天）
• 区间必须落在业务的起止日期范围内
• 区分Rate1（业务开始至费率变更日前一天）和Rate2（费率变更日至业务结束）的生效区间

利用O365支持的LET函数定义变量，大幅提升公式可读性和维护性。

优化后公式 #

=LET(
    月度起始, EOMONTH(G$6, -1) + 1,
    月度结束, G$6,
    业务起始, $B7,
    业务结束, $C7,
    费率变更日, IF($D7=$G$2, $E$2, $E$3),
    // 计算Rate1阶段的有效天数
    Rate1起始, MAX(月度起始, 业务起始),
    Rate1结束, MIN(月度结束, 费率变更日 - 1, 业务结束),
    Rate1天数, MAX(Rate1结束 - Rate1起始 + 1, 0),
    // 计算Rate2阶段的有效天数
    Rate2起始, MAX(月度起始, 费率变更日, 业务起始),
    Rate2结束, MIN(月度结束, 业务结束),
    Rate2天数, MAX(Rate2结束 - Rate2起始 + 1, 0),
    // 计算月度总金额
    Rate1天数 * $E7 + Rate2天数 * $F7
)

公式逻辑说明 #

1. 变量定义：用LET把重复引用的日期（如月度起始、业务起止）定义为变量，避免重复计算
2. Rate1区间：取「月度起始、业务起始」的较晚值作为起点，取「月度结束、费率变更日前一天、业务结束」的较早值作为终点，最后用MAX(...,0)确保天数不为负
3. Rate2区间：取「月度起始、费率变更日、业务起始」的较晚值作为起点，取「月度结束、业务结束」的较早值作为终点，同样用MAX(...,0)避免负数
4. 金额计算：按两个阶段的天数分别乘以对应日费率，求和得到月度总金额

验证场景覆盖 #

• 业务完全在月度外：两个阶段天数均为0，结果为0
• 业务结束早于费率变更日：Rate2天数为0，仅计算Rate1
• 业务跨月度：自动拆分到对应月度的有效天数
• 费率变更日在月度内：正确拆分月度内两个费率阶段的天数

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