如何将Google Sheets中的SUMIFS公式转换为ARRAYFORMULA或QUERY？

原公式逻辑回顾 #

原公式针对每一行实现以下逻辑：

• 若对应需求单元格（C列）为空，返回空值
• 计算当前行及以上、同客户（A列）同产品（B列）的批次数量（E列）总和，减去当前行的需求数量
• 若计算结果为负，返回0；否则返回该结果

ARRAYFORMULA方案（高效版） #

直接在结果列的起始单元格（如F3）输入以下公式，即可自动填充整列：

=ARRAYFORMULA(
  LET(
    key, A3:A & B3:A,
    batch_vals, E3:E,
    demand_vals, C3:C,
    // 按客户-产品分组生成累积批次总和
    cumulative_batches, REDUCE("", UNIQUE(key),
      LAMBDA(acc, k,
        VSTACK(acc, SCAN(0, FILTER(batch_vals, key=k), LAMBDA(a, v, a + v)))
      )
    ),
    // 将累积值匹配到对应行
    matched_cumulative, XLOOKUP(key, UNIQUE(key), cumulative_batches),
    // 计算余额并处理空值和负数
    IF(demand_vals="", "", MAX(0, matched_cumulative - demand_vals))
  )
)

简化版ARRAYFORMULA（易理解） #

如果需要更直观的写法，可使用MAP函数逐行计算：

=ARRAYFORMULA(
  IF(C3:C="", "",
    MAX(0,
      MAP(A3:A, B3:B, C3:C, ROW(A3:A),
        LAMBDA(client, product, demand, row_num,
          SUMIFS(E$3:Erow_num, A$3:Arow_num, client, B$3:Brow_num, product) - demand
        )
      )
    )
  )
)

关于QUERY的说明 #

QUERY函数更适合做一次性聚合统计，难以直接实现按分组逐行累积计算的逻辑，因此不推荐用QUERY替代原公式。

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