求Excel中按日期汇总多营销活动每日营收的公式

嘿，这个需求我太熟悉了——要把每个活动的每日营收对应到全年日历上并累加，用SUMPRODUCT搭配INDEX就能完美搞定，不用复杂的VBA或者辅助列。

核心公式（以O2单元格为例） #

假设你的活动数据从第2行到第100行（可根据实际行数调整），全年日期在N列，那么O2的公式如下：

=SUMPRODUCT(($B$2:$B$100<=N2)*($C$2:$C$100>=N2)*INDEX($F$2:$L$100,0,N2-$B$2:$B$100+1))

输入后直接下拉填充到O列所有单元格即可。

公式拆解，帮你理解每一步 #

咱们把公式拆成3个关键部分来看：

• 筛选符合日期范围的活动
  ($B$2:$B$100<=N2)*($C$2:$C$100>=N2)
  这部分会遍历所有活动，判断当前N列的日期是否在活动的start（B列）和end（C列）之间。符合条件的活动返回1，不符合的返回0，相当于给活动做了“筛选标记”。

• 定位活动对应日期的营收值
  INDEX($F$2:$L$100,0,N2-$B$2:$B$100+1)

  • INDEX的第二个参数0表示返回整列；
  • N2-$B$2:$B$100+1计算当前日期是该活动的第几天（比如活动1月1日开始，1月2日就是第2天）；
  • 结合起来，这部分会精准取出每个符合条件的活动在对应日期的营收值（从F:L列里找）。

• 累加所有符合条件的营收
  SUMPRODUCT会把前面两个数组相乘（筛选标记×对应营收），然后求和——这样就自动忽略了不符合日期的活动，只累加有效营收。

注意事项 #

• 日期格式统一：确保B、C、N列的日期都是Excel标准日期格式，不是文本格式（可以选中列→设置单元格格式→日期）；
• 活动天数匹配：F:L列的每日营收要和活动的实际天数对应（比如活动持续3天，就填F、G、H列，后面列留空即可，SUMPRODUCT会自动忽略空值）；
• 旧版Excel兼容：如果用的是Excel 2019及更早版本，输入公式后需要按Ctrl+Shift+Enter作为数组公式提交（Excel 365/2021无需此操作）。

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