基于先进先出(FIFO)计算售出物料总价与单价的Excel实现需求

我手头有多个物料编号（MaterialNo），这里先以其中一个为例说明需求：我的数据包含Type列，用于标记特定日期下物料的采购（Buy）或销售（Sold）状态，且已经按MaterialNo和Date完成排序。目前我手动计算售出物料的TotalPrice（总价）和UnitPrice（单价），效率很低，希望能在Excel里通过公式实现**FIFO（先进先出）**逻辑自动计算。

示例数据 #

具体实现方案 #

以下公式假设表头在第1行，数据从第2行开始，你可以根据实际数据范围调整单元格引用：

1. 计算销售记录的TotalPrice（总价）

这个公式会自动匹配最早的采购批次，按先进先出原则累计销售成本。Excel 365及以上版本直接回车即可，旧版需按Ctrl+Shift+Enter触发数组计算：

=SUM(IF(($A$2:$A2=A2)*($B$2:$B2="Buy")*($D$2:$D2>0), MIN($D$2:$D2, $D2-SUMIF($A$2:$A1, A2, $D$2:$D1)+SUMIF($A$2:$A1, A2, IF($B$2:$B1="Sold", $D$2:$D1, 0)))*$E$2:$E2))

公式逻辑：先筛选出当前物料的所有未消耗采购批次，再计算当前销售需要从这些批次中扣减的数量，最后乘以对应批次的单价求和得到总价。

2. 计算销售记录的UnitPrice（单价）

直接用总价除以销售数量即可，仅对销售行生效：

=IF(B2="Sold", G2/D2, "")

3. 更新Balance-Quantity（剩余数量）

实时计算当前物料的剩余库存，采购加数量、销售减数量：

=SUMIF($A$2:A2, A2, IF($B$2:B2="Buy", $D$2:D2, -$D$2:D2))

关键注意点 #

• 必须保证数据严格按MaterialNo分组、Date升序排序，否则FIFO逻辑会出错
• 若有多个物料，公式会自动通过$A$2:$A2=A2条件匹配当前物料，无需额外拆分数据
• 旧版Excel使用数组公式时，一定要按Ctrl+Shift+Enter完成输入，不能直接回车

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