Office 365 Excel：按优先级计算可足额备货的订单

问题背景 #

现有订单数据（A3:F8，表头A2:F2），需按优先级从高到低（数值越小优先级越高）判断哪些订单的所有订单项都有足够库存发货：

• 库存按「部件+类别」汇总，初始值为对应行的「可用数量」
• 若某订单所有订单项库存充足，则后续订单的可用库存需扣减该订单的订购数量
• 现有公式仅判断初始库存是否满足，未考虑高优先级订单扣减库存后对后续订单的影响，且大数据量下MMULT方案因资源不足报错

原始数据 #

现有公式局限 #

以下公式仅按优先级排序后判断初始库存是否满足，未处理库存扣减的迭代逻辑：

=LET(data,SORT(A3:F8,{6;2;3}),
     a,INDEX(data,,1),
     d,INDEX(data,,4),
     e,INDEX(data,,5),
BYROW(a,LAMBDA(x,SUM(N(FILTER(d,a=x)>=FILTER(e,a=x)))=COUNTA(FILTER(a,a=x))))

解决方案：用REDUCE实现迭代库存扣减 #

通过Office 365的REDUCE函数累积库存状态，按优先级顺序处理每个订单，同时记录可发货状态，适配大数据量场景：

=LET(
    // 定义原始数据范围
    rawData, A3:F8,
    // 按优先级升序（1最高）排序数据，确保高优先级订单先处理
    sortedData, SORT(rawData, 6, 1),
    // 获取排序后的唯一订单列表
    uniqueOrders, UNIQUE(INDEX(sortedData,,1)),
    // 初始化库存：按「部件+类别」汇总初始可用数量
    initStock, GROUPBY(CHOOSECOLS(sortedData, 2, 3), INDEX(sortedData,,4), SUM, 0, 0),
    // 用REDUCE迭代处理每个订单，累积库存状态和订单可发货状态
    iterResult, REDUCE(
        // 初始累积值：库存表 + 空的订单状态表
        HSTACK(initStock, MAKEARRAY(ROWS(initStock), 1, LAMBDA(x,y,""))),
        uniqueOrders,
        LAMBDA(acc, currOrd,
            LET(
                // 提取当前订单的所有行数据
                ordRows, FILTER(sortedData, INDEX(sortedData,,1)=currOrd),
                // 当前订单的「部件+类别」组合和对应的订购数量
                ordPartCat, CHOOSECOLS(ordRows, 2, 3),
                ordQty, INDEX(ordRows,,5),
                // 匹配当前库存中对应「部件+类别」的剩余数量
                currStockVals, XLOOKUP(
                    ordPartCat[部件]&"|"&ordPartCat[类别],
                    INDEX(acc,,1)&"|"&INDEX(acc,,2),
                    INDEX(acc,,3),
                    0
                ),
                // 判断当前订单所有订单项是否库存充足
                canFulfill, AND(currStockVals >= ordQty),
                // 计算更新后的库存：若可发货则扣减对应订购数量，否则保持原库存
                updatedStock, IF(
                    NOT(canFulfill),
                    acc,
                    LET(
                        // 生成需要扣减的库存值，匹配库存表的「部件+类别」
                        deductVals, XLOOKUP(
                            INDEX(acc,,1)&"|"&INDEX(acc,,2),
                            ordPartCat[部件]&"|"&ordPartCat[类别],
                            ordQty,
                            0
                        ),
                        HSTACK(INDEX(acc,,1), INDEX(acc,,2), INDEX(acc,,3)-deductVals)
                    )
                ),
                // 记录当前订单的可发货状态
                ordStatus, HSTACK(currOrd, canFulfill),
                // 更新累积值：新库存表 + 追加当前订单状态
                HSTACK(updatedStock, IF(INDEX(acc,,4)="", ordStatus, VSTACK(INDEX(acc,,4), ordStatus)))
            )
        )
    ),
    // 提取最终的订单可发货状态结果（去除空行）
    finalStatus, FILTER(INDEX(iterResult,,4):INDEX(iterResult,,COLUMNS(iterResult)), INDEX(iterResult,,4)<>"")
)

逻辑说明 #

1. 排序与初始化：先按优先级升序排序数据，确保高优先级订单优先处理；同时按「部件+类别」汇总初始库存
2. 迭代处理：用REDUCE遍历每个唯一订单：
   • 提取当前订单的所有订单项，匹配当前剩余库存
   • 判断所有项是否满足库存≥订购数量
   • 若满足则扣减对应库存，否则库存保持不变
   • 记录该订单的可发货状态
3. 结果提取：从迭代结果中提取所有订单的可发货状态，得到最终判断结果

该方案避免了MMULT的高资源消耗，利用GROUPBY、XLOOKUP等高效函数处理大数据量，适配数千行的数据集。

内容的提问来源于stack exchange，提问作者P.b