Excel按客户Rank、Min/Max阈值分配物料数量的实现方法

背景 #

此前尝试在SQL Server中通过查询语句实现对应数量分配逻辑，未达到预期效果，需在Excel内完成Distribute（分配量）列的自动计算。

基础表结构 #

数据表共7个字段，字段定义如下：

• Item：物料编码，同编码物料对应固定的总可用库存
• Qty：对应物料的总可分配数量
• Customer：客户编码
• Rank：分配优先级排名，按物料维度分组，同物料下数据已按Rank升序排序（数值越小优先级越高）；同一物料下支持多个客户持有相同Rank，同Rank客户按表中现有行顺序依次分配
• Min：对应客户的保底分配量
• Max：对应客户的最高可分配量
• Distribute：待计算的实际分配量列

分配规则 #

• 第一阶段（保底分配）：不考虑客户排名，所有客户优先获得Min列标注的保底分配量
• 第二阶段（优先级分配）：扣除全量客户保底量后的剩余物料，按客户Rank优先级从高到低依次分配，单客户最终分配总量不得超过Max列标注的上限值
• 剩余库存处理：若所有客户均已分配至Max上限后仍有剩余物料，无需强制分配完毕，直接留存剩余量即可

落地操作步骤 #

前置数据校验 #

分配前先确认同物料所有客户的保底量总和不超过对应物料总可分配量，可在空白列第二行输入以下公式下拉，快速核对异常数据：

=SUMIFS($E:$E,$A:$A,$A2)>XLOOKUP($A2,$A:$A,$B:$B)

公式返回TRUE即代表当前物料保底需求总和超出总库存，需先人工调整基础数据后再执行分配计算。

分配量计算 #

假设表头位于第1行，数据从第2行开始，Distribute列为G列，点击G2单元格输入以下公式：

• 若使用Excel 365/2021及以上版本，直接回车后下拉填充全列即可
• 若使用旧版Excel，输入公式后按Ctrl+Shift+Enter三键结束数组公式编辑，再下拉填充

=LET(
cur_item,A2,
total_qty,XLOOKUP(cur_item,A:A,B:B),
sum_min_all,SUMIFS(E:E,A:A,cur_item),
remain_stage2,total_qty-sum_min_all,
sum_high_rank_alloc,SUMIFS(G:G,A:A,cur_item,C:C,"<"&C2),
sum_samerank_prev_alloc,SUMIFS(G:G,A:A,cur_item,C:C,C2,ROW(A:A),"<"&ROW()),
add_alloc,MAX(0,MIN(F2-E2,remain_stage2-sum_high_rank_alloc-sum_samerank_prev_alloc)),
base_alloc,MIN(E2,MAX(0,total_qty-SUMIFS(E:E,A:A,cur_item,ROW(A:A),"<"&ROW()))),
base_alloc+add_alloc
)

公式逻辑说明：

1. 自动匹配当前行对应物料的总库存、全量保底总和，计算第二阶段可分配的剩余库存
2. 累计同物料下优先级更高（Rank值更小）的客户已分配的追加额度，以及同Rank下排在当前行之前的客户已分配的追加额度
3. 计算当前客户可获得的追加分配量：取「客户可追加上限（Max-Min）」和「当前剩余可分配追加库存」的较小值，结果为负时取0
4. 兜底处理保底总和超库存的极端场景：按行顺序依次分配保底量直到库存耗尽，避免出现负分配值

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