Excel 365中按后进先出法计算当前库存价值的问题

核心思路 #

按采购订单（PO）的时间倒序排列，依次从最新PO中提取数量扣减库存，直到覆盖当前总库存，最后将每个PO实际计入库存的数量乘以对应单价求和。

假设数据结构 #

• A列：PO编号（如PO1、PO2、PO4）
• B列：采购数量
• C列：采购单价
• D列：采购日期（用于确定PO先后顺序）
• E1单元格：当前库存数量（示例中为7）

实现公式（动态数组） #

使用LET函数整合变量，让公式更清晰易维护：

=LET(
    current_stock, E1,
    po_qty, B2:B4,
    po_price, C2:C4,
    po_date, D2:D4,
    sorted_qty, SORT(po_qty, po_date, -1),
    sorted_price, SORT(po_price, po_date, -1),
    remaining_stock, SCAN(current_stock, sorted_qty, LAMBDA(r, q, MAX(r - q, 0))),
    used_qty, TAKE(remaining_stock, ROWS(remaining_stock)-1) - DROP(remaining_stock, 1),
    SUM(used_qty * sorted_price)
)

公式拆解 #

1. 排序采购数据：SORT函数按采购日期降序排列，确保最新PO排在最前面。
2. 跟踪剩余库存：SCAN函数迭代计算每次扣减PO数量后的剩余库存，初始值为当前总库存，每次扣减后结果不小于0。
3. 计算实际使用量：通过TAKE和DROP提取相邻剩余库存的差值，得到每个PO实际计入库存的数量。
4. 求和总价值：将实际使用量与对应单价相乘后求和，得到最终库存价值。

适配无采购日期的场景 #

如果没有采购日期，可按PO编号倒序排序（假设PO编号数字越大越新），将公式中的sorted_qty和sorted_price改为：

sorted_qty, SORT(po_qty, A2:A4, -1),
sorted_price, SORT(po_price, A2:A4, -1),

为什么INDEX/VLOOKUP不适用 #

INDEX和VLOOKUP仅能实现单值匹配或静态区间提取，无法动态迭代计算累计扣减后的库存剩余量，而LIFO逻辑需要逐次扣减、动态判断每个PO的使用数量，动态数组函数（SCAN、LET等）更适配这类场景。

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