如何用Google Sheets Query函数按年月生成跨月累计运行总计

原始数据 #

问题说明 #

需要用Query函数生成运行总计（累计值延续至下月），要求按月末分组、按年份透视。当前使用的公式仅能计算当月范围数值，无法实现累计：

=query({Arrayformula('Cash-Flow'!$A$1:$B), Arrayformula(EOMONTH('Cash-Flow'!$A$1:$B,0)),Arrayformula(MONTH('Cash-Flow'!$A1:$B))}, "SELECT MONTH(Col1)+1, MAX(Col2)  group by MONTH(Col1)+1 PIVOT YEAR(Col1)", 1)

解决方法 #

核心思路 #

先按月末日期汇总每月净收入，再基于汇总结果计算累计总计，最后透视年份展示各月累计值。

公式实现（简洁版，使用LET函数） #

=LET(
  monthly_data, QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1),
  dates, INDEX(monthly_data, 2, 1):INDEX(monthly_data, ROWS(monthly_data), 1),
  monthly_totals, INDEX(monthly_data, 2, 2):INDEX(monthly_data, ROWS(monthly_data), 2),
  running_totals, SCAN(0, monthly_totals, LAMBDA(a,b,a+b)),
  combined, HSTACK(dates, running_totals),
  QUERY(combined, "SELECT MONTH(Col1)+1, Col2 PIVOT YEAR(Col1)", 1)
)

公式解释 #

1. monthly_data：汇总每个月末的净收入，按日期排序。用VALUE(Col2)确保文本格式的收入转成数值，避免SUM计算错误。
2. dates/monthly_totals：提取汇总后的月末日期和对应当月净收入。
3. running_totals：用SCAN函数计算累计总和，从0开始依次累加每个月的净收入，实现累计延续。
4. combined：将日期和累计值合并成新数据范围。
5. 最终QUERY：拆分月份和年份，按年份透视，展示各月的累计总计。

无LET函数兼容版 #

如果使用的Google Sheets版本不支持LET，可使用嵌套公式：

=QUERY(
  HSTACK(
    INDEX(QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1), 2, 1):INDEX(QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1), ROWS(QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1)), 1),
    SCAN(0, INDEX(QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1), 2, 2):INDEX(QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1), ROWS(QUERY('Cash-Flow'!A:B, "SELECT EOMONTH(Col1,0), SUM(VALUE(Col2)) WHERE Col1 IS NOT NULL GROUP BY EOMONTH(Col1,0) ORDER BY EOMONTH(Col1,0)", 1)), 2), LAMBDA(a,b,a+b))
  ),
  "SELECT MONTH(Col1)+1, Col2 PIVOT YEAR(Col1)", 1
)

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