如何基于收货日期计算SKU的库存结余成本？

数据场景 #

• 绿色表格：记录采购信息（包含SKU、收货日期、采购数量、单位成本）
• 红色表格：库存汇总表，需计算Balance Stock Cost（库存结余成本）

计算规则 #

库存结余成本需仅汇总最新收货日期对应的未售出库存的成本，采用先进先出（FIFO）逆向取值逻辑：从最新采购批次开始，依次往前取货，直到覆盖结余数量，再汇总对应批次的成本。

示例说明 #

以SKU A为例：

• 库存结余数量：31件
• 该SKU采购记录按收货日期倒序排列后：

  收货日期	采购数量	单位成本
  1月10日	5件	6
  1月7日	25件	4
  1月5日	10件	5.5

计算时，先取最新批次的5件（5×6=30），再取次新的25件（25×4=100），最后从更早批次取1件（1×5.5=5.5），总计成本为135.5。

计算公式（Google Sheets） #

假设：

• 采购数据范围：采购!A:D（A列=SKU，B列=收货日期，C列=采购数量，D列=单位成本）
• 库存汇总表当前行的SKU为A2，结余数量为B2

使用以下数组公式（新版Google Sheets直接回车即可生效，旧版需按Ctrl+Shift+Enter触发）：

=SUMPRODUCT(
  --(采购!A:A=A2),
  IF(
    SUMIFS(采购!C:C,采购!A:A,A2,采购!B:B,">="&采购!B:B) <= B2,
    采购!C:C,
    MAX(0, B2 - SUMIFS(采购!C:C,采购!A:A,A2,采购!B:B,">"&采购!B:B))
  ),
  采购!D:D
)

公式逻辑 #

1. --(采购!A:A=A2)：筛选出当前SKU的所有采购记录
2. SUMIFS(采购!C:C,采购!A:A,A2,采购!B:B,">="&采购!B:B)：计算当前批次及更晚批次的总采购量
3. 若该总量≤结余数量，则取当前批次全部数量；否则取结余数量减去更晚批次总量的差值（即当前批次需取用的数量）
4. 最后将各批次需取用的数量乘以单位成本，汇总得到总成本

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