如何在Google Sheets中按先进先出逻辑计算SKU仓储期？

需求逻辑 #

• 第1天入库10件：仓储期从第1天开始计算
• 第5天出库5件：累计出库未超过首次入库量，仓储期仍从第1天开始
• 第8天再入库10件：仓储期保持第1天不变
• 第10天出库8件：累计出库（13件）超过首次入库量（10件），剩余出库量从第二次入库扣减，仓储期改为从第8天开始
• 第12天出库1件：累计出库未超过第二次入库量，仓储期仍从第8天开始

测试数据 #

公式实现 #

在E2单元格输入以下公式，下拉填充即可得到每行对应的仓储起始日期：

=LET(
  current_sku, B2,
  all_sku_records, FILTER($A$2:$D$6, $B$2:$B$6=current_sku),
  in_records, FILTER(all_sku_records, INDEX(all_sku_records,,3)="In"),
  in_dates, INDEX(in_records,,1),
  in_qtys, INDEX(in_records,,4),
  cumulative_out, SUMIF($B$2:B2, current_sku, IF($C$2:C2="Out", $D$2:D2, 0)),
  in_cumulative, SCAN(0, in_qtys, LAMBDA(a,b,a+b)),
  matched_in_date, XLOOKUP(cumulative_out, in_cumulative, in_dates, INDEX(in_dates,1), 1),
  IF(C2="In", A2, matched_in_date)
)

公式逻辑拆解 #

1. LET函数：定义变量简化公式结构，避免重复计算

   • current_sku：锁定当前行的SKU，确保只处理同SKU的出入库记录
   • all_sku_records：筛选出当前SKU的所有历史记录
   • in_records、in_dates、in_qtys：提取当前SKU的所有入库日期和数量

2. cumulative_out：计算截至当前行的累计出库总量，只统计同SKU的出库数据

3. in_cumulative：生成入库量的累计序列，比如首次入库10则累计10，第二次入库10则累计20

4. matched_in_date：用XLOOKUP匹配累计出库量对应的入库批次——如果累计出库未超过最早的入库累计量，返回最早入库日期；超过则返回对应后续入库批次的日期

5. 最终判断：入库记录的仓储起始日就是自身入库日期；出库记录则返回匹配到的对应入库批次日期

测试结果验证 #

结果完全符合需求逻辑。

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