库存清零后重启加权平均计算的Excel解决方案问询

我有一张包含交易数据的Excel工作表，计算加权平均时遇到问题：当某商品库存完全售罄后再次采购，加权平均计算会包含该商品此前已售库存的旧价格信息。需要一个能在特定商品库存清零后再次采购时重启加权平均计算的方法，当前使用的公式如下：

• H列公式：=SUMIFS($F$2:F2; $D$2:D2; D2)
• I列公式：=IF(B2="Buy";SUMPRODUCT(--($C$2:C2=C2); --($B$2:B2="Buy");$E$2:E2;$F$2:F2)/SUMIFS($F$2:F2;$C$2:C2;C2;$B$2:B2;B2);" ")

核心解决思路：给商品标记批次周期 #

要实现库存清零后重启加权平均，关键是给每个商品的每一轮库存周期（从首次采购到库存售罄为一个完整周期）做专属标记，后续计算只针对当前周期内的交易数据。

步骤1：添加批次标记列（示例用J列） #

在J2单元格输入以下公式，下拉填充至所有行：

=IF(C2<>C1,1,IF(SUMIFS($F$2:F1,$C$2:C1,C2,$J$2:J1,J1)=0,J1+1,J1))

公式逻辑：

• 如果当前行的商品ID（C列）和上一行不同，直接标记为第1批次
• 如果是同一件商品，检查当前批次（上一行的J值）的累计库存是否已经清零，清零则标记为新批次（上一批次号+1），否则沿用当前批次号

步骤2：修改加权平均公式（I列） #

将原来的I列公式替换为以下内容，确保只计算当前批次内的采购数据：

=IF(B2="Buy",SUMPRODUCT(--($C$2:C2=C2),--($B$2:B2="Buy"),--($J$2:J2=J2),$E$2:E2,$F$2:F2)/SUMIFS($F$2:F2,$C$2:C2=C2,$B$2:B2="Buy",$J$2:J2=J2)," ")

改动说明：新增了批次列（J列）的筛选条件，彻底排除之前售罄周期的旧价格数据，只对当前批次的采购金额和数量做加权平均。

步骤3：同步调整库存计算（可选，H列） #

如果需要H列的库存统计也仅针对当前批次，可修改为：

=SUMIFS($F$2:F2,$D$2:D2,D2,$J$2:J2=J2)

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