★ FEATURED ARTICLE 简介《Excel求解线性规划详解》是一份面向办公人员、经管类学生及需要处理线性规划问题的初学者的实操型文档围绕Excel“规划求解”加载宏展开帮助读者理解如何用表格模型完成生产计划、投资组合优化与资源分配等典型问题。压缩包内为1个doc文件大小约3.5MB内容集中适合按主题查阅。文档系统梳理目标单元格、可变单元格与约束条件的设置逻辑并解释最大值、最小值、整数与二进制约束、采用线性模型、迭代次数、精度、收敛度等参数含义同时说明规划求解的加载与卸载注意事项。已有291人学习可作为建立Excel优化建模框架、理解求解参数与结果校验的入门材料也便于在实际表格中对照排查模型设置是否合理。1. 一句「加载项里怎么没有规划求解」卡住过不少人装完 Excel 打开一看「数据」选项卡右侧干干净净没有那个「规划求解」按钮很多人第一反应是装了个假 Office。实际情况是Excel 求解线性规划靠的是一个叫「规划求解」的加载宏默认安装方式根本不给你装得自己到「Excel 选项 → 加载项」里勾选勾完才会在「数据」选项卡下长出一个「分析」组。坑在于入口在不同版本里位置不一样365 和 2019/2021 在「文件 → 选项」2016 及更早版本走 Office 按钮勾选后还常常因为 SOLVER.XLAM 路径不对、被安全策略拦下、或者干脆文件被清理工具删了而不生效。这篇就从版本判断、加载、参数配置一路写到装完怎么验证适合第一次在 Excel 里做线性规划建模的人也适合要给一批机器统一装加载项的运维。下面按我实际装机的顺序走。2. 环境先列清Excel 版本、系统位数与 SOLVER.XLAM 的落点动手之前先把环境摸清楚比勾半天复选框不生效之后再回头查要省事得多。规划求解不是一个独立软件它随 Office 一起装在本地只是一份没被激活的加载宏文件能不能加载、加载之后跑不跑得动取决于三件事Office 的版本分支、安装位数、以及这个文件在磁盘上的实际位置有没有被策略或清理动作破坏。我一般先花两分钟确认这三项再决定是用界面勾选还是手工注册。2.1 三个版本分支365、2019/2021、2016 的入口不一样入口差异是新手最容易迷路的地方。Office 365、Excel 2019、Excel 2021 这一代路径是「文件 → 选项 → 加载项」对话框底部「管理」下拉框保持默认的「Excel 加载项」点「转到」弹出「加载宏」窗口里面能看到「规划求解加载项」英文界面是 Solver Add-in勾上确定。Excel 2016 及更早版本没有「文件」这个入口得点左上角的 Office 按钮再进「Excel 选项」后面的步骤一样。Mac 版更绕是「工具 → Excel 加载项」而且部分版本要求先退出再重进才认。还有个容易忽略的版本差异老版本「规划求解选项」对话框里是一个「采用线性模型」复选框勾上表示按线性问题加速求解2010 之后的版本把它换成了「选择求解方法」下拉框直接让你选单纯形线性规划、非线性 GRG 或演化。原文讲的是复选框那套界面如果你手上是新版 Excel找不到这个复选框别怀疑装错了它只是换了形态。老版本的模型文件在新版里打开时复选框状态会被翻译成对应的求解方法一般不用重设。判断版本最稳的方式不是看标题栏而是看「文件 → 账户 → 关于 Excel」里的版本号和位数那里写的是真实安装信息。同一台机器上同时装了 WPS 和 Office 的情况很常见WPS 的表格也有规划求解但加载项目录和 Excel 完全隔离别拿 WPS 的路径去 Excel 里找文件。2.2 32 位与 64 位SOLVER.DLL 只能被一个会话占用规划求解的加载宏文件叫 SOLVER.XLAM它背后依赖同目录下的 SOLVER.DLL。这个 DLL 有个硬限制同一时刻只能被一个 Excel 会话使用。这就是为什么同时开两个 Excel 窗口、其中一个正在跑求解时另一个会弹「其他的 Excel 实例正在使用 SOLVER.DLL」。这不是装坏了是设计如此。安装位决定了文件落在哪个根目录。64 位 Office 在C:\Program Files\Microsoft Office\root\Office16\Library\SOLVER\32 位 Office 在C:\Program Files (x86)\Microsoft Office\root\Office16\Library\SOLVER\。注意这里的Office16是个固定目录名不代表版本号2016、2019、2021 和 365 的即点即用版全都叫 Office16很多人以为 2021 应该对应 Office21照着改路径当然找不到。批量部署版MSI 安装路径不同是C:\Program Files\Microsoft Office\Office16\Library\SOLVER\少了 root 这一层。不确定落在哪用一行 PowerShell 全盘扫一遍最省事# 三个常见根目录都扫避免漏掉 32 位或 MSI 版 $roots ( C:\Program Files\Microsoft Office, C:\Program Files (x86)\Microsoft Office ) foreach ($r in $roots) { if (Test-Path $r) { Get-ChildItem -Path $r -Recurse -Filter SOLVER.X* -ErrorAction SilentlyContinue | Select-Object FullName, Length, LastWriteTime } }-Filter SOLVER.X*会同时匹配 SOLVER.XLAM 和 SOLVER.XLL一次看到加载宏和它的 DLL 伴生文件Length用来判断文件是不是被 0 字节的占位文件替换过某些清理工具会这么干正常大小在几百 KB 量级LastWriteTime能看出是不是被 Office 更新覆盖过。三个字段一起看基本能判断这个文件是完好、缺失还是残留。2.3 装前检查清单可信位置、受保护视图与 VBA 组件文件在、路径对也不一定加载得起来。加载宏属于可执行内容Excel 的信任中心会拦。装之前我会确认三件事一是这个 SOLVER 目录在不在「受信任位置」里不在的话某些企业策略会直接禁止加载二是工作簿不是从网络位置直接打开的状态受保护视图下加载项按钮是灰的三是宏安全性没有设成「禁用所有宏并且不通知」这一档会把加载宏一并挡掉。检查清单可以按这个顺序过一遍每项都能在「文件 → 选项 → 信任中心 → 信任中心设置」里找到对应开关检查项位置期望状态宏设置信任中心 → 宏设置选「禁用所有宏并发出通知」或更低受信任位置信任中心 → 受信任位置SOLVER 所在 Library 目录可加入受保护视图信任中心 → 受保护视图本地文件不触发加载项开关信任中心 → 加载项未勾选「禁用所有应用程序加载项」最后一项最容易被忽略它是企业统一下发的策略勾上之后无论你在加载宏窗口怎么点都不会生效而且不报错只是静默不动。遇到「勾了没反应」先来看这一项能省掉大量无效排查。另外如果机器上装过 WPS 或其他办公套件注册表里的加载项键值可能被改写这种情况用界面勾选往往无效得走后面第 3 章的手工注册路线。3. 加载规划求解从选项对话框到 SOLVER.XLAM 真正落地环境确认完就可以动手。加载这件事本身只有三步但「勾选」和「真正加载成功」之间隔着一层文件系统很多失败案例是勾选动作成功了、文件却没被读进来。这一章按从简到繁的顺序给三种加载方式界面勾选、VBA 批量、手工注册最后给一套验收标准确保你确认的是真加载而不是假成功。3.1 界面勾选法三步走完但要盯住「管理」下拉框标准路径打开「文件 → 选项」左侧选「加载项」底部「管理」下拉框确认是「Excel 加载项」不是「COM 加载项」也不是「禁用项目」点「转到」。弹出的「加载宏」窗口里勾选「规划求解加载项」确定。回到工作表「数据」选项卡最右侧会出现「分析」组里面有「规划求解」。三步里有两步会翻车。第一处是「管理」下拉框默认可能停在别的项上尤其装过第三方插件的机器下拉框会被记住上次的选择选错就看到一个完全不相干的列表。第二处是「加载宏」窗口里同时列着「规划求解加载项」和「数据分析加载项」两个都勾会让 Excel 启动变慢原文那句话说得很实在——加载的宏越多启动越慢。我一般只勾规划求解数据分析那套统计功能用不上就不装。卸载同理回到这个窗口取消勾选即可不用去删文件。取消勾选不会删除 SOLVER.XLAM只是不再加载下次想用再勾回来就行这一点比很多人想象的安全。真正会把文件删掉的是各种「Office 清理/瘦身」工具它们把 Library 目录当成无用组件处理删完之后加载宏窗口里这一项直接消失那就得走 3.3 的修复路线。3.2 批量装机用 VBA 和注册表确认加载项状态给一台机器装是三步给一批机器装就得考虑脚本化。VBA 里访问加载项集合很直接可以查询也可以设置状态Sub LoadSolverAddin() Dim ai As AddIn On Error Resume Next 键名用英文 Solver Add-in中文界面下同样能取到 Set ai Application.AddIns(Solver Add-in) If ai Is Nothing Then MsgBox 加载项列表中不存在规划求解SOLVER.XLAM 可能已被删除 Exit Sub End If ai.Installed True 等价于界面上的勾选 MsgBox 已加载 ai.FullName End SubApplication.AddIns(Solver Add-in)用的是加载项的固定键名不是界面显示名所以中文版 Excel 里也照写英文不用改成「规划求解加载项」Installed True就是勾选动作改成 False 就是取消勾选FullName打印出来的是真实文件路径这一步相当于顺手做了路径校验——如果路径指向的目录跟第 2 章扫出来的不一致说明加载项注册信息指向了错误位置。取不到对象时说明键值丢了不是加载失败是注册项本身没了。如果连这个键都取不到可以直接看注册表# 查看当前用户的 Excel 加载项注册信息 Get-ItemProperty -Path HKCU:\Software\Microsoft\Office\Excel\Add-ins\* | Select-Object PSChildName, Path, LoadBehaviorPSChildName是加载项键名规划求解对应的是SolverAddIn.SolverAddIn这一类命名Path是 XLAM 的绝对路径可以跟磁盘实际位置比对LoadBehavior里 3 表示随 Excel 启动自动加载2 表示只注册不自动加载0 表示不加载。批量部署时把这个键值预先写好、Path 指到正确目录用户第一次打开 Excel 就已经有按钮了比教每个人去点选项省事。3.3 勾选无效时手工定位并注册 SOLVER.XLAM如果加载宏窗口里压根没有「规划求解加载项」这一项说明注册信息丢了如果勾上之后确定没反应多半是路径指向了不存在的文件。两种情况的修法一样手工把 XLAM 注册进去。打开「加载宏」窗口在「浏览」按钮里定位到第 2 章扫出来的那个 SOLVER 目录选中 SOLVER.XLAM 确定。这时可能会出现「当前加载宏可能包含宏病毒」的提示选择启用。注册成功后加载宏列表里会多出一项路径显示的就是你选的那个文件。这一步本质上就是在补注册表里那条 Path。需要注意手工浏览注册只解决「注册项缺失」解决不了「文件缺失」。如果扫描结果里根本没有 SOLVER.XLAM那就得走修复安装控制面板 → 程序和功能 → 选中 Office → 更改 → 快速修复。快速修复不动用户数据只补组件一般几分钟。快速修复无效再考虑联机修复那个会重装整个 Office时间长但能解决文件被替换成占位符的情况。修复前先确认这台机器上有没有正在跑的求解任务SOLVER.DLL 被占用时修复程序会提示重启。还有一种更隐蔽的情况文件在、路径对、注册也对但每次重启 Excel 按钮又没了。这是LoadBehavior被写成了 0 或 2某些安全软件会主动改写这个值。把 3.2 里那个键值改回 3 就能固定下来改完重启 Excel 验证一次。3.4 加载成功的验收标准勾选完成不等于装好我会做三层验收。第一层看界面「数据」选项卡出现「分析」组组里有「规划求解」按钮按钮不是灰的。第二层看路径用 3.2 的 VBA 打印FullName确认指向的目录跟扫描结果一致。第三层看功能随便点开一个空白工作簿点「规划求解」能弹出「规划求解参数」对话框说明 SOLVER.XLAM 和背后的 DLL 都正常加载了。三层都过才算装好。第三层最容易暴露问题。有些机器上按钮在、能点开但一点「求解」就报找不到 SOLVER.DLL这通常是同目录的 DLL 缺失或位数不匹配——32 位 Office 加载了 64 位 DLL或者反过来。这种错没有明确提示只能靠第 2 章的路径和位数对照排查。验收时别只看界面一定点一次「求解」哪怕模型是空的让程序跑一遍加载流程。4. 参数配置选项面板里真正需要动的六个开关装好只是能跑跑出来的结果对不对、会不会中途停、为什么和手算不一样全在参数上。规划求解选项面板里的参数有十几个但绝大多数场景只需要盯住其中六个求解方法、最长运算时间、迭代次数、精度、自动按比例缩放、假定非负。剩下的收敛度、允许误差、导数、搜索这些属于非线性或整数规划场景才需要调的。这一章按「哪个参数影响什么」讲每个都给一个我会填的值。4.1 求解方法单纯形、GRG 非线性、演化三种怎么选新版 Excel 的「选择求解方法」下拉框有三个选项对应关系要弄清楚。纯线性问题目标函数和约束都是变量的线性组合选「单纯形线性规划」速度快、结果稳定、必定收敛到全局最优非线性问题选「非线性 GRG」用广义既约梯度法迭代逼近问题不可导、有离散跳变、或者约束很古怪时选「演化」它是启发式算法慢但能跳出局部极值。选错的代价很明显把非线性问题当线性解会得到一组看似收敛、实际上违反约束的解或者直接报「无法满足设定的线性模型条件」。原文那条报错就是这么来的——勾了「采用线性模型」复选框但模型里有乘积项或指数项。新版对应的是下拉框选了单纯形但模型是非线性的报错文字不同原因一样。老版本界面里没有这个下拉框只有「采用线性模型」复选框和「估计」「导数」「搜索」几个进阶项。复选框勾上等价于选单纯形不勾则走 GRG 那套。导数选项里「向前差分」用于约束变化缓慢的问题「中心差分」用于变化迅速、接近限值的问题后者计算量大但在求解器返回无效解时值得一试搜索选项里「牛顿法」迭代次数少但吃内存「共轭法」省内存但迭代多模型规模大、机器内存紧张时选共轭法。4.2 精度、收敛度、允许误差三个小数到底填多少这三个参数都是 0 到 1 之间的小数含义完全不同混起来填是常见错误。精度Precision控制的是约束满足程度判断约束单元格的数值是否落在目标值或上下限内。填得越小越严格默认量级在 1e-6 左右小型线性规划够用。精度设得过小会导致求解器反复迭代却始终认为约束未满足最后报「不能达到最优解」。遇到这种报错先把精度调大一个数量级再试。收敛度Convergence只在非线性问题里起作用判定条件是最近五次迭代目标值变化小于该值就停。填 1e-4 是个稳妥起点填小了求解时间明显变长填大了会提前停止拿到粗糙解。原文那句「设置的数值越小收敛度就越高求解时间越长」说的就是这个。允许误差Tolerance只对带整数约束的问题生效含义是接受解与真实最优解之间允许的百分比偏差。默认量级在 5%这个值越大求解越快因为求解器不用死磕到真正最优。整数规划规模一大5% 能让你从几分钟等到几秒。我一般在演示或需要精确最优时调到 0.5%日常排产这类问题保留默认。最长运算时间和迭代次数这两个是硬上限默认都是 100可填上限 32767。默认 100 秒对小型模型够但如果模型有几十个变量加几十条约束100 秒经常不够表现是求解中途停下并提示达到时间限制。这种时候不要盲目拉到 32767先看结果报告里的迭代次数用了多少按实际用量乘 3 到 5 倍设置更合理。4.3 自动按比例缩放与假定非负省一半排错时间「自动按比例缩放」这个复选框解决的是量级差异问题。当可变单元格的典型数值和约束值、目标值差好几个数量级时比如一个变量取值在 0.001 量级另一个在 10000 量级求解器内部的数值计算会失稳表现为结果莫名其妙或者直接发散。勾上之后程序自动做缩放代价是每轮迭代多一点开销。我遇到「结果和手算差很远」且模型本身没写错时第一件事就是勾这个。「假定非负」是老版本界面里的复选框勾上之后所有没在约束里显式设置下限的可变单元格自动假定下限为 0。这个默认行为对线性规划几乎总是对的因为线性规划的标准形式就要求变量非负但对某些建模场景是陷阱——如果某个变量真允许取负值忘了加显式下界约束勾了这个就会得到一组全正但不符合业务的解。新版 Excel 里这个选项的位置变了但语义一样建模时养成习惯变量非负要么靠这个默认要么显式加一条 0的约束二选一别都不做。4.4 模型保存与装入把参数写进单元格参数调好之后不该每次重设。「规划求解选项」里的「保存模型」能把当前这套参数目标单元格、可变单元格、全部约束、选项设置存到工作表的某个区域里存完之后那片区域会变成一组长数字看着像乱码其实是模型的序列化结果。「装入模型」再读回来。这个功能的实用价值在于一个工作簿里放多个方案比如不同产线配置下的排产模型第一个模型会自动保存第二个开始必须手工指定保存位置否则会互相覆盖。原文那句「只有需要在工作表上保存多个模型时才单击此命令」说的就是这个限制。保存区域建议放在远离建模区的位置比如从第 100 行开始并且给这块区域加个底色标注避免有人误删。模型序列化对单元格位置极其敏感——插入或删除行列会导致引用偏移装回来的参数就错位了。要改表结构先把模型删掉重存不要指望它能自适应。5. 排查避坑六条把求解器装崩的现场记录装和配都走完之后问题往往出现在使用阶段。这一章把我遇到过的翻车按「现象 → 原因 → 解决」记下来都是能复现的具体情况不是泛泛的注意事项。遇到同类现象可以直接对号入座省掉从头排查的时间。5.1 界面类问题按钮不出现、出现又消失现象加载宏窗口里勾选了「规划求解加载项」点确定后「数据」选项卡里没有「分析」组或者当天有第二天开机又没了。原因第一种是指向的文件路径失效注册表里的 Path 指向了一个被移动或删除的 SOLVER.XLAM第二种是LoadBehavior被安全软件改回 0 或 2导致每次启动不自动加载第三种是加载项被列进了「禁用项目」通常发生在 Excel 某次异常退出之后程序会自动禁用可疑加载项并静默记录。解决先用第 2 章的 PowerShell 脚本确认文件实际位置再对照 3.2 里的Path值路径不符就用「浏览」重新注册一次。然后查「禁用项目」文件 → 选项 → 加载项 → 管理下拉框切到「禁用项目」→ 转到如果规划求解在列表里选中并点「启用」。最后把LoadBehavior改回 3 并重启验证。三步做完仍然反复消失的检查是不是有安全软件在定时清理这种情况只能把 SOLVER 目录加进白名单。5.2 运行类问题内存不足与 DLL 被占用现象点「求解」后弹窗提示「内存不足以求解问题」或者提示「其他的 Excel 实例正在使用 SOLVER.DLL」。原因前者不一定是真没内存更常见的是模型规模超出了求解器的处理能力——可变单元格数量多、约束密集或者求解方法选错把大规模非线性问题交给 GRG。原文提到的可变单元格上限 200 个是个硬边界超过就只能拆模型。后者是 SOLVER.DLL 的单会话限制同时开了多个 Excel 且其中一个加载了求解器就会触发。解决内存不足先降规模把不参与当前方案的变量临时固定成常量再确认求解方法选的是单纯形而不是 GRG实在要跑大规模问题改用专门的求解工具Excel 这套适合中小规模建模。DLL 占用则简单关掉多余的 Excel 窗口只留一个在跑求解跑完再开别的。任务管理器里如果有残留的 EXCEL.EXE 进程界面关了进程没退也要一并结束否则限制依然存在。5.3 结果类问题解不对、解不出、每次结果不一样现象求解器返回「规划求解找到一解可满足所有约束」但结果和手算差很远或者提示「规划求解找不到可行解」或者同一个模型连跑两次结果不同。原因结果偏差通常来自三处——目标单元格不是公式而是常量、可变单元格与目标单元格没有引用关系、求解方法选错。找不到可行解多半是约束互相矛盾比如同一变量既要求 10又要求 5或者整数约束和等式约束组合起来无解。结果不稳定则是非线性问题的典型特征GRG 是局部搜索初始值不同会落到不同局部极值。解决先检查目标单元格是不是公式点进去看编辑栏有没有再用「推测」按钮让程序自动识别与目标单元格相关的非公式单元格比手填可变单元格可靠。约束矛盾需要逐条核对把约束临时删到只剩一半再求解用二分法定位冲突的那一条。非线性结果不稳定就换初始值把可变单元格预先填成接近预期最优的数量级再求解精度和收敛度各调小一个数量级重试。线性模型不存在这个问题改变初始值不影响最终结果这也是判断「我的模型到底是不是线性」的一个实用手段。6. 装完怎么验证三变量模型跑通一次全链路装好的最后一步是拿一个能算得出标准答案的小模型跑通从头到尾确认加载、建模、求解、报告四段都正常。我用得最多的是一道两约束三变量的线性规划目标最大化z 2x1 3x2 4x3约束x1 2x2 3x3 12、2x1 x2 x3 8变量非负。这道题的最优解是x1 4/3、x2 16/3、x3 0目标值56/3 ≈ 18.667两条约束都取等号是个很好的验证点——解出来约束没取等模型就建错了。6.1 建表与公式先按下面的布局把系数和公式铺好B2:D2 是决策变量初始填 0单元格内容说明B2:D20, 0, 0可变单元格先给 0 初值B3:D32, 3, 4目标函数系数B5:D51, 2, 3约束 1 系数B6:D62, 1, 1约束 2 系数F5 / F612 / 8约束右端值E5SUMPRODUCT($B$2:$D$2,B5:D5)约束 1 实际用量E6SUMPRODUCT($B$2:$D$2,B6:D6)约束 2 实际用量B8SUMPRODUCT($B$2:$D$2,$B$3:$D$3)目标单元格SUMPRODUCT在这里做的是对应元素相乘再求和等于把「系数 × 变量」这件事一次算完比写B2*B3C2*C3D2*D3好维护——加一个变量只要扩区域公式不用改。注意区域引用要混用绝对和相对$B$2:$D$2锁定变量行B5:D5保持相对往下填充到 E6 时系数行会自动跟着走。6.2 求解并读报告点「规划求解」按下面填设置目标$B$8选「最大值」可变单元格$B$2:$D$2添加约束$E$5:$E$6 $F$5:$F$6再添加$B$2:$D$2 0如果勾了「假定非负」可省。求解方法选「单纯形线性规划」点求解。结果应该是x1 ≈ 1.333、x2 ≈ 5.333、x3 0B8 ≈ 18.667。同时检查 E5 等于 12、E6 等于 8两条约束都取等。求解完成后在「报告」框里选「运算结果报告」和「敏感性报告」会生成新工作表。运算结果报告里三张表分别给出目标单元格的原值/终值、可变单元格的原值/终值、约束单元格的状态敏感性报告里的影子价格能告诉你哪条约束是瓶颈——这道题两条约束的影子价格都不为零说明两个资源都卡死了想再提高目标值只能放宽约束右端值。如果解出来x3不是 0或者约束没取等先别怀疑求解器回头查 B8 是不是公式、可变单元格有没有漏掉 D2、约束的引用区域有没有错位。这三个是新手建错模型的高频点。6.3 用 Python 复算确认不是「看起来对」Excel 给出数字之后我会用另一套实现复算一遍确认不是界面上看着像对的。线性规划用 SciPy 的linprog十几行就够import numpy as np from scipy.optimize import linprog # linprog 求解的是最小化最大化问题把系数取负 c [-2.0, -3.0, -4.0] A_ub [[1.0, 2.0, 3.0], # 约束 1x1 2x2 3x3 12 [2.0, 1.0, 1.0]] # 约束 22x1 x2 x3 8 b_ub [12.0, 8.0] bounds [(0, None)] * 3 # 三个变量均非负 res linprog(c, A_ubA_ub, b_ubb_ub, boundsbounds, methodhighs) print(x , res.x) # 期望 [1.3333, 5.3333, 0] print(z , -res.fun) # 取负还原期望 18.6667 print(约束余量 , res.slack) # 期望 [0, 0]c取负是因为linprog只做最小化最大化问题取负号转换A_ub和b_ub对应不等号约束若约束是需要整体乘 -1bounds显式给非负下界与 Excel 里那条 0约束对应res.slack是约束余量两个值都接近 0 说明约束全取等跟 Excel 报告里的状态一致。两边结果对得上这套环境才算真跑通——不是按钮能点而是算出来的数站得住。从那以后我每次装完规划求解都会拿这道小题跑一遍连 Python 复算一起走完才交机器。装机时觉得多余的一步到出问题的时候就是唯一能证明「不是求解器的锅」的证据。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
