基于FIFO规则筛选指定总量库存并计算加权平均

需求说明 #

现有888件库存，需按**FIFO（先进先出）**规则计算加权平均成本：从最近的入库日期向前累加库存数量，直到总和达到888件，筛选出这些符合条件的批次后，结合单价计算加权平均。

原始数据（A1:C11区域，表头行A1:C1） #

解决方案 #

方法一：Arrayformula + SUMPRODUCT 直接计算 #

核心思路：先按日期降序排列批次，计算累计入库量，再确定每个批次需计入库存的有效数量，最后用SUMPRODUCT计算加权平均。

直接输入以下公式即可得到加权平均成本：

=SUMPRODUCT(
  SORT(B2:B11,A2:A11,FALSE)*SORT(C2:C11,A2:A11,FALSE)*
  ARRAYFORMULA(
    IF(
      MMULT(N(ROW(B2:B11)>=TRANSPOSE(ROW(B2:B11))),SORT(B2:B11,A2:A11,FALSE))<=888,
      1,
      IF(
        MMULT(N(ROW(B2:B11)>TRANSPOSE(ROW(B2:B11))),SORT(B2:B11,A2:A11,FALSE))<888,
        (888-MMULT(N(ROW(B2:B11)>TRANSPOSE(ROW(B2:B11))),SORT(B2:B11,A2:A11,FALSE)))/SORT(B2:B11,A2:A11,FALSE),
        0
      )
    )
  )
)/888

公式拆解：

1. SORT(B2:B11,A2:A11,FALSE) 和 SORT(C2:C11,A2:A11,FALSE)：将入库数量、单价按日期降序排列，对应最新到最早的批次。
2. MMULT(...)：计算降序排列后的累计入库量，判断每个批次是否完全计入库存，或仅需取部分数量。
3. SUMPRODUCT(...)：将每个批次的有效数量与单价相乘后求和，再除以总库存888，得到加权平均成本。

方法二：Query分步筛选计算 #

先通过Query筛选出符合条件的批次，再计算加权平均，逻辑更直观。

步骤1：获取降序排列的批次及累计入库量

输入公式生成包含累计数量的数据集：

=QUERY(
  {A2:C11,ARRAYFORMULA(MMULT(N(ROW(A2:A11)>=TRANSPOSE(ROW(A2:A11))),SORT(B2:B11,A2:A11,FALSE)))},
  "select Col1,Col2,Col3,Col4 order by Col1 desc label Col4 '累计数量'"
)

该公式会返回按日期降序排列的批次，并新增“累计数量”列，显示从最新批次到当前批次的总入库量。

步骤2：筛选符合条件的批次

基于步骤1的结果，筛选出累计数量≤888的批次，以及第一个累计数量超过888的批次（仅取补足888的部分）：

=QUERY(
  步骤1的单元格引用,
  "where Col4<=888 or (Col4>888 and Col4-Col2<=888)"
)

步骤3：计算加权平均成本

对筛选后的批次，计算有效数量与单价的乘积和，再除以888：

=SUMPRODUCT(
  ARRAYFORMULA(IF(筛选结果的累计列<=888,筛选结果的数量列,888-(筛选结果的累计列-筛选结果的数量列))),
  筛选结果的单价列
)/888

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