如何用ARRAYFORMULA实现匹配ID+日期区间的求和（按半月分段）

需求背景 #

需将一年划分为24个分段（每月1-15日为上半月，16日至月末为下半月），通过ARRAYFORMULA批量计算指定ID在对应日期区间内的数值求和，替代低效的MAP嵌套写法以提升计算效率。

优化后的公式 #

=ARRAYFORMULA(
  LET(
    ids, $A$2:$A,
    intervals, BZ1:CW1,
    data_ids, 'Ref4'!$G$2:$G,
    data_dates, 'Ref4'!$P$2:$P,
    data_values, 'Ref4'!$Q$2:$Q,
    IFERROR(
      MMULT(
        --(TRANSPOSE(data_ids)=ids),
        MMULT(
          --(data_dates>=TRANSPOSE(intervals))*(data_dates<IF(DAY(TRANSPOSE(intervals))=1, TRANSPOSE(intervals)+15, EOMONTH(TRANSPOSE(intervals),0)+1)),
          data_values
        )
      ),
      ""
    )
  )
)

公式逻辑说明 #

1. 变量封装：用LET统一定义所有引用区域，简化公式结构，提升可读性。
2. ID匹配矩阵：通过TRANSPOSE(data_ids)=ids生成二维匹配矩阵，用--将布尔值转换为0/1，标记每行数据是否对应目标ID。
3. 日期区间判断：
   • 若区间起始日为1号（上半月），结束日设为起始日+15，确保统计1-15日的数据
   • 若区间起始日为16号（下半月），结束日设为EOMONTH(起始日,0)+1（次月1号），确保统计16日至月末的数据
   • 通过--将日期判断的布尔结果转为0/1矩阵，标记数据是否落在对应区间内
4. 二维批量求和：两次使用MMULT矩阵乘法，先按区间汇总每个ID的数值，再按ID匹配输出最终结果，一次性完成所有ID、所有区间的求和计算。
5. 空值优化：用IFERROR将计算结果中的0和错误值替换为空字符串，让表格展示更整洁。

优势对比 #

相比原有的MAP嵌套写法，该公式采用数组化运算逻辑，避免了循环迭代，在处理大数据集时计算效率显著提升，完全符合ARRAYFORMULA的批量计算需求。

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