如何在Excel中根据货物量确定最差剩余客户期限？

核心逻辑 #

按剩余客户期限从晚到早的顺序，依次扣除对应批次的货物数量，找到总库存扣减后剩余部分所属的最早过期（即最差）的期限。

动态数组公式（Excel 365/2021适用） #

假设数据位于A2:D9（A=商品编号，B=收货日期，C=剩余客户期限，D=货物数量），总库存值放在F1（示例中为300），使用以下公式直接得到结果：

=LET(
    sorted_terms, SORTBY(C2:C9, C2:C9, -1),
    sorted_qty, SORTBY(D2:D9, C2:C9, -1),
    running_total, SCAN(0, sorted_qty, LAMBDA(a, b, a + b)),
    remaining_stock, F1 - running_total + sorted_qty,
    XLOOKUP(TRUE, remaining_stock > 0, sorted_terms, , 0, 1)
)

公式拆解 #

• sorted_terms & sorted_qty：将剩余期限和对应数量按期限降序排列，确保最晚过期的批次优先被扣除。
• running_total：计算累计扣减的货物数量，从0开始逐批累加。
• remaining_stock：计算当前批次扣减前的剩余库存（总库存减去上一批次的累计扣减量）。
• XLOOKUP：找到第一个剩余库存大于0的批次，对应的期限就是总库存扣减后剩余部分所属的最差（最早过期）期限。

针对你的数据示例验证 #

你的数据总货物量为320件，当总库存为300时：

1. 按期限降序排序后的批次及数量：10.02.2025(30)、10.02.2025(20)、07.02.2025(30)、06.02.2025(100)、06.02.2025(30)、05.02.2025(50)、04.02.2025(30)、03.02.2025(30)
2. 累计扣减到04.02.2025(30)时，总扣减量为30+20+30+100+30+50+30=290，剩余库存300-290=10
3. 下一批次为03.02.2025(30)，剩余库存10≤30，因此最差剩余客户期限为03.02.2025

旧版Excel解决方案（无动态数组） #

如果使用旧版Excel，需通过辅助列实现：

1. 添加辅助列E（排序优先级）：=RANK(C2,$C$2:$C$9,0)，对剩余期限降序排名（数值越大期限越晚）
2. 添加辅助列F（累计扣减）：先按E列升序排序，再输入=F1+D2下拉填充，得到逐批累计的扣减总量
3. 用INDEX+MATCH找到结果：=INDEX($C$2:$C$9,MATCH(F1,$F$2:$F$9,1))

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