📊 Excel公式详解：动态任务分配系统
📋 数据概览
订单数据（A1:C7）
| 订单号 | 零件 | 数量 |
| PO-1 | C | 2 |
| PO-2 | C | 3 |
| PO-3 | A | 5 |
| PO-4 | C | 7 |
| PO-5 | A | 2 |
| PO-6 | A | 4 |
任务数据（E1:G4）
| 任务号 | 零件 | 数量 |
| WK-001 | C | 3 |
| WK-002 | C | 25 |
| WK-003 | A | 20 |
🔧 公式结构图
- flowchart TD
- A[开始] –> B[REDUCE函数迭代]
- B –> C[遍历订单B2:B7]
- C –> D[LET函数处理每个零件]
- D –> E[定义变量]
- E –> F[“Y: 当前零件”]
- E –> G[“G: 任务数量G2:G4”]
- E –> H[“D: 累计已分配量”]
- E –> I[“T: 当前订单数量”]
- H –> J[“SUMIF计算历史用量”]
- I –> K[“OFFSET获取订单数量”]
- D –> L[核心分配逻辑]
- L –> M[“FILTER筛选匹配任务”]
- M –> N[“IF重复任务号”]
- N –> O[“TOCOL转换为一维数组”]
- O –> P[“DROP跳过已分配部分”]
- P –> Q[“TAKE取当前需求数量”]
- Q –> R[UNIQUE去重<br>实际未使用]
- R –> S[返回分配结果]
- S –> T[VSTACK堆叠结果]
- T –> U{是否遍历完成?}
- U — 否 –> C
- U — 是 –> V[DROP删除首行空值]
- V –> W[🎯 最终分配结果]
🧩 公式逐层解析
1. 外层框架：DROP(REDUCE(...), 1)
=DROP(
REDUCE(“”, B2:B7, LAMBDA(X, Y, …)),
1
)
| 函数 | 作用 | 说明 |
REDUCE | 迭代累加器 | 遍历B2:B7每个零件，累积结果 |
LAMBDA | 自定义函数 | 定义每次迭代的处理逻辑 |
VSTACK | 垂直堆叠 | 将每次结果堆叠起来 |
DROP | 删除行 | 删除第一行空字符串占位符 |
2. LET函数变量定义
excel
LET(
Y, Y, # 📍 当前处理的零件
G, G2:G4, # 🔢 任务数量数组 [3, 25, 20]
D, SUMIF(OFFSET(Y,-1,):B1, Y, C1), # 📊 历史累计分配量
T, OFFSET(Y,,1), # 🎯 当前订单需求数量
F, TAKE(DROP(TOCOL(FILTER(…)), D), T), # 🎪 核心分配逻辑
U, UNIQUE(F), # 🔄 去重（实际未使用）
F # 📤 返回分配结果
)
🔍 核心分配逻辑详解
3. 任务号展开机制
excel
IF(G >= SEQUENCE(,SUM(G)), E2:E4, \)
示例转换过程：
| 步骤 | 说明 | 结果示例 |
| 原始任务 | WK-001(3), WK-002(25), WK-003(20) | 3个独立任务 |
| 数量展开 | 按数量重复任务号 | WK-001×3, WK-002×25, WK-003×20 |
| 一维数组 | 转换为单列 | 48行的任务列表 |
展开结果矩阵：
text
列1 列2 列3 … 列48
WK-001 WK-001 WK-001 … 空
WK-002 WK-002 WK-002 … WK-002
WK-003 WK-003 WK-003 … WK-003
4. 零件筛选与分配
excel
FILTER(展开数组, F2:F4=Y)
按零件筛选：
- 零件C：筛选出WK-001和WK-002
- 零件A：筛选出WK-003
5. 分配流程示例
📦 处理订单 PO-1 (C, 2)
| 步骤 | 计算 | 结果 |
| 历史累计D | SUMIF(空范围, “C”, C列) = 0 | 0 |
| 任务池 | 所有C零件任务展开 | [WK-001, WK-001, WK-001, WK-002×25,…] |
| 跳过D行 | DROP(任务池, 0) | 不跳过 |
| 取T个 | TAKE(…, 2) | [WK-001, WK-001] |
📦 处理订单 PO-2 (C, 3)
| 步骤 | 计算 | 结果 |
| 历史累计D | 前面C零件共2+0=2 | 2 |
| 跳过D行 | 从第3个开始 | [WK-001, WK-002, WK-002,…] |
| 取T个 | 取3个 | [WK-001, WK-002, WK-002] |
📊 完整分配过程
任务池初始化
text
C零件池: [WK-001, WK-001, WK-001, WK-002×25]
A零件池: [WK-003×20]
分配过程记录表
| 订单 | 零件 | 需求 | 累计D | 分配结果 | 剩余池 |
| PO-1 | C | 2 | 0 | WK-001×2 | C池: [WK-001, WK-002×25] |
| PO-2 | C | 3 | 2 | WK-001, WK-002×2 | C池: [WK-002×23] |
| PO-3 | A | 5 | 0 | WK-003×5 | A池: [WK-003×15] |
| PO-4 | C | 7 | 5 | WK-002×7 | C池: [WK-002×16] |
| PO-5 | A | 2 | 5 | WK-003×2 | A池: [WK-003×13] |
| PO-6 | A | 4 | 7 | WK-003×4 | A池: [WK-003×9] |
🎯 最终输出结果
text
WK-001 ← PO-1分配
WK-001 ← PO-1分配
WK-001 ← PO-2分配
WK-002 ← PO-2分配
WK-002 ← PO-2分配
WK-003 ← PO-3分配
WK-003 ← PO-3分配
WK-003 ← PO-3分配
WK-003 ← PO-3分配
WK-003 ← PO-3分配
WK-002 ← PO-4分配
WK-002 ← PO-4分配
WK-002 ← PO-4分配
WK-002 ← PO-4分配
WK-002 ← PO-4分配
WK-002 ← PO-4分配
WK-002 ← PO-4分配
WK-003 ← PO-5分配
WK-003 ← PO-5分配
WK-003 ← PO-6分配
WK-003 ← PO-6分配
WK-003 ← PO-6分配
WK-003 ← PO-6分配
⚡ 公式优化建议
- 性能优化：在大数据量时，每次迭代重新生成任务池效率较低
- 错误处理：添加
IFERROR处理任务不足的情况 - 去重优化：
UNIQUE(F)变量未使用，可移除 - 范围引用：使用结构化引用提高可读性
💡 应用场景
此公式适用于：
🏭生产任务分配系统📦库存先进先出(FIFO)管理🚚物流配送路线分配📊资源调度与优化
这是一个典型的按需分配、先到先得的动态分配系统，巧妙运用了Excel 365的新函数功能。