Google Sheets：在生成数组中按组计算带初始值的累计库存

需求回顾 #

基于产品+国家的笛卡尔积数组，按这两个字段分组，从独立SOH表获取期初库存，计算累计库存：

• 累计库存公式：累计库存 = 期初库存 + 收货量 - 销量
• 每组首条记录用SOH表的期初值，后续记录沿用前一行的累计结果
• 所有逻辑整合到单个公式，不使用辅助列

假设表结构 #

• SOH表：A列=产品，B列=国家，C列=期初库存
• Sales表：A列=产品，B列=国家，C列=日期，D列=销量
• Receipts表：A列=产品，B列=国家，C列=日期，D列=收货量

完整公式 #

=LET(
  // 生成产品-国家的唯一组合
  product_countries, UNIQUE(FLATTEN(SOH!A2:A & "|" & SOH!B2:B)),
  split_pc, INDEX(SPLIT(product_countries, "|")),
  products, INDEX(split_pc,,1),
  countries, INDEX(split_pc,,2),
  // 提取并排序所有涉及的日期
  all_dates, SORT(UNIQUE({Sales!C2:C; Receipts!C2:C})),
  // 生成产品-国家-日期的笛卡尔积
  cartesian, GENERATE(products, countries, LAMBDA(p,c, HSTACK(p, c, all_dates))),
  // 匹配对应行的销量（无数据则返回0）
  sales_vals, XLOOKUP(
    INDEX(cartesian,,1)&"|"&INDEX(cartesian,,2)&"|"&INDEX(cartesian,,3),
    Sales!A2:A&"|"&Sales!B2:B&"|"&Sales!C2:C, Sales!D2:D, 0
  ),
  // 匹配对应行的收货量（无数据则返回0）
  receipt_vals, XLOOKUP(
    INDEX(cartesian,,1)&"|"&INDEX(cartesian,,2)&"|"&INDEX(cartesian,,3),
    Receipts!A2:A&"|"&Receipts!B2:B&"|"&Receipts!C2:C, Receipts!D2:D, 0
  ),
  // 生成分组标识，用于判断组切换
  group_key, INDEX(cartesian,,1)&"|"&INDEX(cartesian,,2),
  // 获取每组的期初库存
  initial_soh, XLOOKUP(group_key, SOH!A2:A&"|"&SOH!B2:B, SOH!C2:C, 0),
  // 分组计算累计库存：组首行用期初值，后续行沿用前累计
  cumulative_soh, SCAN(
    0,
    SEQUENCE(ROWS(cartesian)),
    LAMBDA(acc, i,
      IF(
        i=1 OR INDEX(group_key,i)<>INDEX(group_key,i-1),
        INDEX(initial_soh,i) + INDEX(receipt_vals,i) - INDEX(sales_vals,i),
        acc + INDEX(receipt_vals,i) - INDEX(sales_vals,i)
      )
    )
  ),
  // 组合最终输出列
  HSTACK(cartesian, sales_vals, receipt_vals, cumulative_soh)
)

核心逻辑拆解 #

1. 笛卡尔积生成：通过UNIQUE+FLATTEN提取SOH表的产品-国家唯一组合，再用GENERATE与所有日期生成完整的三维数组；
2. 数据匹配：用XLOOKUP通过“产品|国家|日期”的复合键匹配销量和收货量，无数据时返回0避免计算错误；
3. 分组累计：SCAN函数逐行遍历数组，通过对比当前行与上一行的分组标识，判断是否需要重置为SOH期初值，否则继续累计计算。

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