Google Sheets使用QUERY函数实现日期范围月度回执金额统计求助

前置说明 #

核心需求可通过「单条回执拆分为覆盖月份明细+聚合统计」实现，无需Google脚本，纯公式即可兼容跨年度日期范围，输出的列转置格式可直接对接后续查询逻辑。

实现步骤 #

步骤1：确认原始数据结构 #

你的原始表列顺序固定为：

• A列：客户
• B列：银行回执编号
• C列：起始日期
• D列：结束日期
• E列：月额度金额

步骤2：部署聚合公式 #

将以下公式粘贴到你要输出结果的左上角单元格即可，可根据实际数据行数调整范围：

=LET(
  // 过滤有效原始数据
  raw,FILTER(A2:E,ISDATE(C2:C),ISDATE(D2:D),A2:A<>""),
  // 提取字段
  clients,INDEX(raw,,1),
  start_d,INDEX(raw,,3),
  end_d,INDEX(raw,,4),
  amt,INDEX(raw,,5),
  // 单条回执拆分到所有覆盖月份
  month_list,MAP(start_d,end_d,amt,clients,LAMBDA(s,e,a,c,
    IFERROR(
      HSTACK(
        SEQUENCE(DATEDIF(s,e,"M")+1,1,EOMONTH(s,-1)+1,30),
        WRAPROWS(c,DATEDIF(s,e,"M")+1,c),
        WRAPROWS(a,DATEDIF(s,e,"M")+1,a)
      ),
    {"","",""})
  )),
  // 合并所有拆分后的数据
  flat,TOCOL(month_list,1),
  arr,WRAPROWS(flat,3),
  // QUERY聚合并转置为月份列格式
  QUERY(arr,"select Col2, sum(Col3) where Col2 is not null group by Col2 pivot Col1 format Col1 'yyyy-mm'",1)
)

逻辑说明 #

• 用DATEDIF(s,e,"M")计算单条回执覆盖总月数，彻底解决跨年度日期计算异常的问题，2021/12/15到2022/03/31的范围可正确识别为覆盖4个月
• 取每个月第一天作为分组标识，保证同月份数据可被正确聚合
• 用QUERY的pivot参数直接把月份转为列标题，输出格式完全符合后续查询要求

效果验证 #

用你给出的John示例数据运行公式后，输出结果如下：

和预期结果完全一致。

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