Google Sheets中如何用ArrayFormula+Query实现每日数据批量求和

核心思路 #

避免重复调用IMPORTRANGE和QUERY，先一次性从源数据提取所有中标日期的金额总和，再将全年日期与该结果批量匹配，大幅降低表格运算负载。

最优公式（单公式完成，仅调用一次源数据） #

=ARRAYFORMULA(
  LET(
    win_data, QUERY(IMPORTRANGE('source addresses'!$B$2,"bid tracker!$A$3:$X"),"SELECT Col8, SUM(Col9) WHERE Col8 IS NOT NULL GROUP BY Col8 LABEL SUM(Col9) ''",0),
    year_dates, SEQUENCE(DATEDIF(B5,EDATE(B5,12)-1,"D")+1,1,B5),
    XLOOKUP(year_dates, INDEX(win_data,,1), INDEX(win_data,,2), 0, 0)
  )
)

公式各部分说明 #

• LET函数：定义中间变量，简化逻辑且仅调用一次IMPORTRANGE
  • win_data：从源工作簿提取所有有中标记录的日期（Col8）及对应金额总和（Col9），按日期分组去重
  • year_dates：自动生成从B5（当年起始日期）到年末的所有日期，支持平年/闰年自动适配
• XLOOKUP：批量匹配全年每个日期对应的中标金额，无中标记录的日期返回0（可改为""显示空值）

替代方案（用辅助单元格拆分逻辑） #

如果觉得单公式复杂，可拆分两步：

1. 在任意空白单元格（比如C1）输入以下公式，提取所有中标日期的汇总数据：

=QUERY(IMPORTRANGE('source addresses'!$B$2,"bid tracker!$A$3:$X"),"SELECT Col8, SUM(Col9) WHERE Col8 IS NOT NULL GROUP BY Col8 LABEL SUM(Col9) ''",0)

2. 在需要生成全年数据的单元格输入：

=ARRAYFORMULA(
  XLOOKUP(
    SEQUENCE(DATEDIF(B5,EDATE(B5,12)-1,"D")+1,1,B5),
    INDEX(C:C,2):INDEX(C:C,COUNTA(C:C)),
    INDEX(D:D,2):INDEX(D:D,COUNTA(D:D)),
    0,
    0
  )
)

注意事项 #

• 确保已授权当前工作簿访问源工作簿（首次调用IMPORTRANGE会提示授权，完成后公式才能正常运行）
• 若需调整无中标日期的显示值，将XLOOKUP的第4个参数（当前为0）改为""即可

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