循环盘点表多物料FIFO批量分配问题：MIN/MAX函数条件适配需求

问题概述 #

• Sheet1为用户输入页，用于录入物料编码及对应实盘数量
• Sheet2为库存快照页，存储各物料的分批次库存数据
• 核心需求：按*FIFO原则（从最新批次到最旧批次）*将Sheet1的实盘数量分配至Sheet2对应批次，直到实盘量耗尽
• 现有问题：单个物料场景下用MIN/MAX函数可实现分配逻辑，但添加物料筛选条件后无法正常运行
• 示例验证：物料P9919617的30000实盘量已完成大部分批次分配，仅2278US9602批次剩余10584库存待调整

适配多物料筛选的解决方案 #

针对带物料筛选的FIFO分配场景，可通过SUMIFS与MIN/MAX嵌套实现，核心是先计算当前批次及更新批次的累计库存，再匹配剩余实盘量进行分配：

基础公式（单物料目标） #

假设Sheet2列定义：

• A列：物料编码
• B列：批次号（需按最新到最旧排序）
• C列：批次库存数量
  Sheet1列定义：
• E2：目标物料编码
• F2：对应实盘数量

在Sheet2的D2单元格输入以下公式，下拉填充：

=IF(
    A2=Sheet1!$E$2,
    MAX(
        0,
        MIN(
            C2,
            Sheet1!$F$2 - SUMIFS($C$2:C2, $A$2:A2, Sheet1!$E$2) + C2
        )
    ),
    0
)

公式逻辑拆解 #

1. A2=Sheet1!$E$2：仅对与目标物料匹配的行执行分配计算
2. SUMIFS($C$2:C2, $A$2:A2, Sheet1!$E$2)：计算当前批次及所有更新批次的累计库存
3. Sheet1!$F$2 - SUMIFS(...) + C2：得出当前批次可分配的剩余实盘额度
4. MIN(C2, ...)：取批次库存与剩余额度的较小值，避免超批次分配
5. MAX(0, ...)：确保分配结果不为负数

批量多物料版本 #

若需同时处理多个物料的分配，可结合XLOOKUP动态匹配当前物料的实盘量，公式如下：

=LET(
    target_qty, XLOOKUP(A2, Sheet1!E:E, Sheet1!F:F, 0),
    cum_inv, SUMIFS($C$2:C2, $A$2:A2, A2),
    prev_cum, cum_inv - C2,
    MAX(0, MIN(C2, target_qty - prev_cum))
)

关键注意事项 #

• 必须保证Sheet2的批次数据按最新到最旧排序，否则FIFO分配逻辑会完全错误
• 谷歌表格与Excel函数语法一致，上述公式可直接套用

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