最新

Excel 怎么用规划求解算出最优方案

多个变量互相牵制时,手改单元格凑不出最优解。讲清规划求解加载项怎么开、目标与约束怎么设,以及整数约束和局部最优这两个坑。

排班、配货、下料这类问题,几个变量互相牵着,靠手改单元格凑不出最优解。Excel 的规划求解就是干这个的:你告诉它目标是什么、哪几个格子能动、必须满足哪些限制,它自己去找一组既满足全部限制、又让目标最好看的数。

入口在哪

规划求解不是默认显示的功能,而是一个加载项,第一次用要先把它调出来。打开文件选项卡,点最下面的选项,左侧选加载项,下方管理那一栏确认是 Excel 加载项,点转到,勾上规划求解加载项,确定。

调出来以后,按钮在数据选项卡靠右的位置,一般叫规划求解或求解。Mac 版在工具菜单的加载项里,名称和位置略有差别。找不到基本就是加载项没勾上。

具体步骤

  1. 先把模型搭在单元格上。目标格必须写公式,比如总成本等于各变量乘单价再求和;决策格先留空或填一个可行初值,这几个格子就是求解器要动的。
  2. 点规划求解,设置目标:选中目标单元格,再选最大值、最小值或等于某个指定值。
  3. 设置可变单元格,把刚才留空的决策格选进去,不连续的区域按住 Ctrl 多选。
  4. 逐条添加约束。常见三类:决策格不能小于零;总量不能超过库存或工时上限;人数、件数必须取整数。
  5. 选求解方法。线性关系选单纯形线性规划;有乘除、平方这类非线性关系选非线性 GRG;前两种都不收敛再试演化。
  6. 进选项勾上假定线性模型,迭代次数和精度先用默认值。
  7. 点求解,弹窗里选保留规划求解解。
  8. 动手前先复制一份工作表。求解器会直接改写决策格,关掉文件原值就找不回来了。

几个坑

  • 目标格写成固定数字。求解器会发现改哪个格子都没变化,直接收敛在原地。
  • 忘记加整数约束,人数、车辆数、箱数这些就会被算成三点七个人。
  • 约束方向写反。不超过写成不小于,结果要么无解,要么明显不合常理。
  • 拿线性方法跑非线性问题,乘除关系被当成常数,解代回原式对不上。
  • 所有决策格初值都填零。非线性问题从零点往往走不动,给个可行初值更容易收敛。
  • 把最优解当成唯一解。约束不紧时常有多组解,换初值再跑,目标值一致才说明最优是稳的。

怎么验证

  1. 把决策值一条条代回约束手算,确认每条都满足,尤其卡在边界上的那几条。
  2. 用决策值手算目标格,和求解器给的结果对一遍,差得多说明公式引错了区域。
  3. 换一组完全不同的初值再求。目标值相同而解不同是多解,可以接受;目标值不同说明上一次停在局部最优。
  4. 把某条约束放宽一档重跑,看目标值是否朝预期方向变。方向反了多半是符号写错。

常见问题

数据选项卡里找不到规划求解

去文件选项卡的选项里,左侧选加载项,下方管理选 Excel 加载项,点转到,勾上规划求解加载项。勾完仍然没有,检查装的是不是精简版或网页版,网页版不提供这个功能。

提示找不到可行解怎么办

先确认约束之间不打架。常见冲突是总量上限比最小值之和还小,或两个约束方向相反。把约束逐条临时删掉再跑,跑到哪条开始无解,问题就在那条上。

它和单变量求解有什么区别

单变量求解只改一个格子,反推让某个公式等于指定值,没有约束概念。规划求解能同时改很多格子并加限制,求出的是条件下的最优值。

一句话总结:规划求解的门槛在建模不在操作,目标格要靠公式联动、决策格留空、约束写清并加整数限制,跑完用代回和换初值两种办法验一遍。