基于日期区间的数量乘费率求和：最优公式及SUMPRODUCT适用性咨询

嘿，我完全懂你的困惑——要根据不同的费率时间区间，计算指定起始点之后的月度数量加权总和，而且还想知道SUMPRODUCT能不能搞定对吧？答案是完全可以，甚至SUMPRODUCT就是这类问题的绝佳工具，我来一步步给你拆解清楚。

先理清楚数据结构（关键前提） #

首先我们得把你的数据整理成规整的表格，方便公式引用：

• 费率表（假设放在A2:C4单元格）：

  费率开始日期	费率结束日期	单价
  2017/1/1	2017/4/30	5
  2017/5/1	2017/6/30	6
  2017/7/1	2017/12/31	7

• 月度数量表（假设放在E2:F13单元格）：

  月份代表日期	苹果数量
  2017/1/1	10
  2017/2/1	30
  2017/3/1	10
  2017/4/1	50
  2017/5/1	40
  2017/6/1	70
  2017/7/1	80
  2017/8/1	40
  2017/9/1	20
  2017/10/1	10
  2017/11/1	40
  2017/12/1	100

注意：每个月份用当月第一天作为代表日期，这样能准确匹配它所属的费率区间；同时费率区间必须是连续、不重叠且按起始日期升序排列的，这是公式生效的关键。

核心SUMPRODUCT公式实现 #

假设你要指定的起始日期（比如2017/5/9）放在H2单元格，那么直接用下面的公式就能得到你要的结果：

=SUMPRODUCT(
  F2:F13,
  INDEX(B2:B4, MATCH(E2:E13, A2:A4, 1)),
  --(INDEX(A2:A4, MATCH(E2:E13, A2:A4, 1)) >= INDEX(A2:A4, MATCH(H2, A2:A4, 1)))
)

公式拆解（一步步看懂）

1. F2:F13：直接引用月度苹果数量数组，这是我们要计算的基数。
2. INDEX(B2:B4, MATCH(E2:E13, A2:A4, 1))：给每个月份匹配对应的单价。MATCH(E2:E13, A2:A4, 1)会找到每个月份代表日期对应的费率区间位置（利用升序查找，返回小于等于该日期的最大费率起始日期的位置），再用INDEX取出对应单价。
3. --(INDEX(A2:A4, MATCH(E2:E13, A2:A4, 1)) >= INDEX(A2:A4, MATCH(H2, A2:A4, 1)))：这是筛选条件。先找到指定起始日期H2对应的费率区间起始日期，再判断每个月份的费率起始日期是否大于等于这个日期；--把布尔值（TRUE/FALSE）转换成1或0，符合条件的月份乘1保留，不符合的乘0被排除。

测试验证 #

代入你的示例数据：

• 指定H2=2017/5/9，MATCH(H2, A2:A4, 1)返回2，对应费率区间起始日期为2017/5/1；
• 1-4月的费率起始日期是2017/1/1，小于2017/5/1，被排除；
• 5-6月的费率起始日期是2017/5/1，符合条件，数量总和40+70=110，乘单价6得660；
• 7-12月的费率起始日期是2017/7/1，符合条件，数量总和80+40+20+10+40+100=290，乘单价7得2030；
• 最终总和660+2030=2690，和你给出的示例结果完全一致。

有没有更优的方案？ #

如果你的数据量不大，这个SUMPRODUCT公式已经是最优解了——它不需要额外的辅助列，逻辑清晰且计算高效。如果数据量极大，你可以考虑用辅助列先给每个月份标记对应的费率和是否符合日期条件，再用SUMIFS求和，但SUMPRODUCT的简洁性依然是首选。

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