运筹学小白也能懂：用Excel表格手把手演示单纯形法迭代过程
运筹学小白也能懂：用Excel表格手把手演示单纯形法迭代过程
第一次接触运筹学中的单纯形法时，那些数学符号和抽象概念总让人望而生畏。但你知道吗？其实用我们熟悉的Excel表格，就能直观地模拟这个寻找最优解的过程。本文将以一个生产计划优化问题为例，带你一步步用Excel实现单纯形法的完整迭代，无需编程基础，只需掌握基础公式和表格操作。
1. 从实际问题到线性规划模型
假设你管理着一家小型家具厂，主要生产桌子和椅子。每张桌子利润300元，每把椅子利润400元。生产过程中有两个关键限制：
- 木材消耗：每张桌子消耗2立方米，每把椅子消耗1立方米，每日木材供应上限40立方米
- 人工工时：每张桌子需要1小时，每把椅子需要3小时，每日可用工时30小时
这个典型的生产优化问题，用数学语言描述就是：
目标函数：最大化利润 Z = 300x₁ + 400x₂
约束条件：
- 2x₁ + x₂ ≤ 40 （木材限制）
- x₁ + 3x₂ ≤ 30 （工时限制）
- x₁, x₂ ≥ 0 （非负约束）
在Excel中建立初始表格时，建议按以下结构布局：
| 单元格 | 内容 | 公式示例 |
|---|---|---|
| B2 | 目标函数系数 | 手动输入300, 400 |
| B5:B6 | 约束条件系数 | 手动输入2,1;1,3 |
| D5:D6 | 约束值 | 手动输入40,30 |
| F5:F6 | 当前资源使用量 | =SUMPRODUCT(B5:C5,$B$2:$C$2) |
提示：使用SUMPRODUCT函数可以方便地计算线性组合，这是单纯形法在Excel中实现的关键函数
2. 构建初始单纯形表
将上述标准型转化为Excel表格，需要引入松弛变量x₃和x₄，把不等式转为等式：
2x₁ + x₂ + x₃ = 40 x₁ + 3x₂ + x₄ = 30在Excel中创建初始单纯形表（以A1为起点）：
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 1 | 基变量 | x₃ | x₄ | ||
| 2 | 系数 | 0 | 0 | ||
| 3 | 解 | 40 | 30 | ||
| 4 | x₁=2 | x₁=1 | x₁ | θ₁ | |
| 5 | x₂=1 | x₂=3 | x₂ | θ₂ | |
| 6 | x₃=1 | x₃=0 | x₃ | ||
| 7 | x₄=0 | x₄=1 | x₄ |
关键操作步骤：
- 在B1:D7区域构建上表框架
- 计算检验数σⱼ（未在表中显示）：在G4输入
=D4-SUMPRODUCT(B4:C4,$B$2:$C$2) - 复制G4公式到G5，得到x₁和x₂的检验数
3. 第一次迭代：入基与出基选择
当检验数存在正值时，当前解非最优。我们的迭代策略是：
入基变量选择：
- 在G4:G5中找出最大正检验数对应变量
- 本例中σ₂=400 > σ₁=300，选择x₂入基
出基变量选择：
- 计算θ比率：在F4输入
=IF(B4>0,B3/B4,"")，下拉到F5 - 找出最小非负θ值对应行
- 本例中θ₂=10 < θ₁=40，因此x₄出基
基变换操作：
- 将x₂替换x₄作为新基变量：A3单元格改为x₂
- 更新基变量系数：C2单元格改为400
- 执行高斯消元使x₂对应列变为单位向量：
- 主元行（第5行）除以主元系数3
- 其他行减去主元行的适当倍数
更新后的表格关键区域：
| A | B | C | D |
|---|---|---|---|
| 1 | 基变量 | x₃ | x₂ |
| 2 | 系数 | 0 | 400 |
| 3 | 解 | 30 | 10 |
| 4 | 5/3 | 1/3 | x₁ |
| 5 | -1/3 | 1/3 | x₂ |
注意：所有数值变化都应通过Excel公式实现，而非手动输入，确保可重复计算
4. 第二次迭代与最优解验证
重复检验数计算过程：
- 在G4计算新的σ₁：
=D4-SUMPRODUCT(B4:C4,$B$2:$C$2)- 结果为300 - (0*(5/3) + 400*(1/3)) ≈ -33.33
- 计算σ₃和σ₄（对应松弛变量）：
- σ₃ = 0 - (01 + 4000) = 0
- σ₄ = 0 - (0*(-1/3) + 400*(1/3)) ≈ -133.33
此时所有检验数非正，说明当前解已达最优。最终解为：
- x₁ = 0（不生产桌子）
- x₂ = 10（生产10把椅子）
- 最大利润Z = 0300 + 10400 = 4000元
5. 敏感度分析与实际应用
单纯形法的价值不仅在于找到最优解，更在于提供灵敏度分析。在Excel中我们可以轻松实现：
影子价格分析：
- 增加1单位木材（40→41），观察Z值变化
- 增加1单位工时（30→31），观察Z值变化
- 通过数据→模拟分析→单变量求解实现
允许变化范围：
- 保持当前基变量不变条件下
- 计算目标函数系数和约束条件的允许变化区间
- 使用Excel的规划求解工具验证结果
实际业务中，你可能会发现：
- 当前最优解建议全部生产椅子，这可能不符合市场需求
- 此时需要添加新的约束条件（如最小桌子产量）
- 在Excel中只需添加新行并重新迭代
6. 常见问题与解决技巧
问题1：退化现象
- 当θ比率出现相等值时可能导致循环
- 解决方案：使用Bland规则，选择下标最小的变量入基
问题2：无界解
- 表现为θ值全部为无穷大
- 检查是否遗漏了关键约束条件
问题3：人工变量处理
- 当初始无明显可行基时
- 使用大M法或两阶段法在Excel中实现
实用技巧：
- 使用条件格式标记检验数和θ值
- 创建迭代历史记录表跟踪解的变化
- 结合图表展示解的空间位置变化
通过这个案例可以看到，即使没有专业的优化软件，用Excel也能完整实现单纯形法的所有关键步骤。我在实际教学中发现，这种可视化的迭代过程能让学习者直观理解"为什么这样迭代"而不是机械记忆步骤。下次当你遇到资源分配问题时，不妨试试用这个方法来寻找最佳方案。