如何按条件计算累计达指定值时的最后采购日期（Query/Gsheet）

问题背景 #

• 两类核心数据：
  • 库存数据：按批次记录产品入库数量与日期（inbound 1为最早入库批次）
  • 交易数据：按日期记录产品每日交易数量
• 核心需求：根据交易累计消耗的库存，匹配对应入库批次的日期——当最早批次库存耗尽后，自动切换至下一批次，最终得到每笔交易对应的最后采购（入库）日期

实现公式（Google Sheets） #

假设数据范围定义：

• 交易数据：A2:C（A列=产品ID，B列=交易数量，C列=交易日期）
• 库存数据：E2:G（E列=产品ID，F列=入库数量，G列=入库日期，已按入库批次从早到晚排序）

在交易数据的D2单元格输入以下数组公式：

=ARRAYFORMULA(IFERROR(VLOOKUP(
  A2:A&"|"&SCAN(0, B2:B, LAMBDA(acc, curr, acc+curr)),
  QUERY(
    FLATTEN(
      BYROW(UNIQUE(E2:E), LAMBDA(prod,
        LET(
          prod_stock, FILTER(F:G, E:E=prod),
          cum_stock, SCAN(0, INDEX(prod_stock,,1), LAMBDA(a, b, a+b)),
          prev_stock, {0; cum_stock[1:ROWS(cum_stock)-1]},
          dates, INDEX(prod_stock,,2),
          FLATTEN(
            BYROW(SEQUENCE(ROWS(cum_stock)), LAMBDA(n,
              prod&"|"&SEQUENCE(prev_stock[n]+1, 1, prev_stock[n]+1, 1)&"|"&dates[n]
            ))
          )
        )
      )
    ),
    "SELECT Col1, Col3 WHERE Col1 IS NOT NULL"
  ),
  2,
  TRUE
)))

公式逻辑说明 #

1. 库存区间映射：对每个产品的入库批次，将累计库存拆分为连续数值区间，每个区间绑定对应批次的入库日期
2. 交易累计计算：用SCAN函数计算交易数据的累计消耗总量
3. 区间匹配：通过VLOOKUP将累计交易总量匹配到对应的库存区间，返回对应入库日期

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