如何在Google Sheets中无需给NULL字段填0即可获得QUERY正确计算结果

问题根因 #

Google Sheets的QUERY函数执行SUM聚合时会自动忽略纯空单元格，当某一列所有匹配行均为空时，SUM返回NULL值，NULL参与算术运算结果仍为NULL，因此foo=D行的balance计算结果为空。 #

方案1：改造QUERY数据源实现COALESCE效果（无需修改原表） #

不需要手动给Sheet1的空单元格补0，直接在构造QUERY的输入数据源时，通过ARRAYFORMULA批量将空值替换为0，等效SQL中的COALESCE(字段, 0)逻辑。
单个行的balance计算公式示例（适用于Sheet2的B2单元格，下拉即可适配所有行）：

=INDEX(QUERY(ARRAYFORMULA({Sheet1!$A$2:A, IF(Sheet1!$B$2:C="", 0, Sheet1!$B$2:C)}), "SELECT SUM(Col3) - SUM(Col2) WHERE Col1 = '"&A2&"'"), 2)

如果需要整列自动计算无需下拉，可使用BYROW+LAMBDA的一键公式，放在B2单元格即可自动匹配所有foo行：

=BYROW(A2:A, LAMBDA(f, IF(f="",, INDEX(QUERY(ARRAYFORMULA({Sheet1!$A$2:A, IF(Sheet1!$B$2:C="", 0, Sheet1!$B$2:C)}), "SELECT SUM(Col3) - SUM(Col2) WHERE Col1 = '"&f&"'"), 2))))

方案2：使用SUMIFS简化计算（天然支持空值按0处理） #

该场景逻辑简单，不需要使用QUERY函数，直接用SUMIFS计算更简洁，且SUMIFS默认将空单元格按0计算，无需额外处理空值：
单个行计算公式（下拉适配所有行）：

=SUMIFS(Sheet1!C:C, Sheet1!A:A, A2) - SUMIFS(Sheet1!B:B, Sheet1!A:A, A2)

整列自动计算公式：

=BYROW(A2:A, LAMBDA(f, IF(f="",, SUMIFS(Sheet1!C:C, Sheet1!A:A, f) - SUMIFS(Sheet1!B:B, Sheet1!A:A, f))))

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