Excel SUMIFS取第n个值返回位置及库存对应采购订单溯源

看起来你需要处理一个典型的库存溯源场景——从最新（最右侧）的采购订单开始倒推，找到当前库存对应的入库批次（采购订单），同时结合SUMIFS实现位置定位。我会分步骤给你拆解解决方案，用Excel函数落地需求。

先明确数据结构（假设） #

为了方便公式演示，先统一表格结构定义：

• A列：商品SKU（A2:A100）
• 第1行（B1:Z1）：采购订单编号（从左到右为旧订单到新订单，右侧是最新批次）
• B2:Z100：对应SKU在各订单的入库数量
• AA列（AA2:AA100）：当前库存数量

核心需求：从右往左定位库存对应的采购订单 #

我们需要从最右侧的订单开始累加入库量，直到累加值≥当前库存，此时对应的订单就是目标批次。这里用INDEX+MATCH+BYCOL（适配Excel 365/2021版本）实现：

=INDEX($B$1:$Z$1,MATCH(TRUE,BYCOL($B2:$Z2,LAMBDA(col,SUM(OFFSET(col,0,0,1,COLUMN($Z2)-COLUMN(col)+1))))>=AA2,0))

公式拆解： #

1. BYCOL($B2:$Z2,LAMBDA(col,SUM(...)))：遍历当前SKU的每一列入库数，计算从当前列到最右侧列的累计入库量（即从当前订单到最新订单的总入库）
2. >=AA2：判断每个累计值是否≥当前库存
3. MATCH(TRUE,...,0)：找到第一个满足条件的列位置——因为越往右的列累计和越小，第一个符合的就是最右侧能覆盖库存的订单
4. INDEX($B$1:$Z$1,...)：根据位置返回对应的采购订单编号

如果你的Excel版本不支持BYCOL，可以用数组公式（输入后按Ctrl+Shift+Enter确认）：

=INDEX($B$1:$Z$1,MATCH(TRUE,MMULT(--(COLUMN($B2:$Z2)>=TRANSPOSE(COLUMN($B2:$Z2))),$B2:$Z2)>=AA2,0))

结合SUMIFS获取第n个累计值的位置 #

如果需要单独定位从右往左数第n个订单的位置，或获取其累计入库量，可以用SUMIFS结合列号判断：

1. 获取从右往左第n个订单的入库量 #

=SUMIFS($B2:$Z2,$B$1:$Z$1,INDEX($B$1:$Z$1,COLUMNS($B$1:$Z$1)-n+1))

这里的n是从右往左的序号（比如n=1就是最新订单），公式会返回该订单的入库数量。

2. 定位该订单的列位置 #

=MATCH(INDEX($B$1:$Z$1,COLUMNS($B$1:$Z$1)-n+1),$B$1:$Z$1,0)

也可以直接结合SUMIFS的结果反向匹配位置：

=MATCH(SUMIFS($B2:$Z2,$B$1:$Z$1,INDEX($B$1:$Z$1,COLUMNS($B$1:$Z$1)-n+1)),$B2:$Z2,0)

示例验证（你的场景） #

假设某SKU当前库存122，过去7个订单（B2:H2）的入库数为50、60、70、80、40、30、22（累计352）。从右往左累加：

• 第1个（H2）：22 < 122 → 继续
• 第2个（G2+H2）：30+22=52 < 122 → 继续
• 第3个（F2+G2+H2）：40+52=92 < 122 → 继续
• 第4个（E2+F2+G2+H2）：80+92=172 ≥ 122 → 停止

此时对应的订单是E列的采购订单，用上面的公式会自动返回E1的订单编号。

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