简介《Excel求解线性规划详解》是一份面向数据分析初学者、运筹优化入门者以及需要处理生产计划、投资组合、资源分配等线性规划问题读者的专题文档技能层级偏基础到中级。压缩包内共1个doc文件约3.5MB以文档形式集中整理Excel“规划求解”的核心知识。内容围绕加载宏、目标单元格与可变单元格设置、约束条件添加、求解参数解释及模型保存与装入展开并对最长运算时间、迭代次数、精度、允许误差、收敛度、采用线性模型、假定非负等选项给出说明同时提示宏加载数量对启动速度的影响。已有291人学习适合希望借助Excel快速建立线性规划模型、理解求解器参数含义并用于课程学习或实际业务测算的读者参考。1. 求解失败的锅八成不在算法很多人第一次在 Excel 里找“规划求解”翻遍功能区也找不到按钮以为这功能是传说找到了又在第一轮就撞上“求解器找不到可行解”。这两种挫败感指向同一件事规划求解不是一键计算器它是一层胶水负责把表格里的公式翻译成一个能被算法读懂的数学模型。翻译错了算法再强也白搭。真正值得搞懂的不是参数表而是这层胶水怎么沿引用关系抽依赖、怎么判定模型是不是线性、怎么在整数约束上做妥协——搞懂这三件事报错信息就不再是黑匣子。下面从一个默认不存在的按钮说起。2. 加载宏与模型边界为什么“规划求解”按钮默认不存在装好 Excel打开“数据”选项卡从头翻到尾也找不到“规划求解”。这不是版本阉割也不是安装包损坏而是这个功能从来没被当成编辑器本体的一部分。它属于一类可以事后挂载的扩展宿主进程在启动时才决定要不要把它挂进来。2.1 挂载机制一个被登记进宿主的功能而不是宿主的一部分加载宏是一个独立的文件宿主的配置里维护着一份“已登记但未启用”的清单。启动时扫描这份清单把打过勾的项挂进功能区没勾的就只是一条躺在清单里的记录。默认安装不会替用户勾选原因很现实每一项挂载都要占用启动时间和常驻内存而大多数人一年也用不上一次线性规划。这条设计直接带来了两个后果。第一勾得越多启动越慢所以按需勾选才是一个正确习惯不是小气。第二卸载的方式是“取消勾选”而不是删文件——文件删了清单里的登记记录还在下次启动可能报加载失败。取消勾选之后功能区入口消失但模型内容仍然留在工作簿里只是暂时没有引擎去读它。还有一条更隐蔽的约束求解器背后的动态库在同一时刻只能被一个宿主会话持有。所以同时开两个窗口、各自点求解第二个会收到“另一个实例正在使用”的提示。这不是缺陷而是这个库以进程内单例的方式注册跨会话没法共享。排查时遇到这条提示先关掉多余的窗口问题就没了不用怀疑模型有问题。2.2 目标、可变、约束三件套必须落在同一张依赖图上目标单元格必须是公式这是整个机制的地基。求解器并不理解“利润”或“成本”这种业务语义它只认一件事当你改动某个格子时另一个格子的值会不会跟着变。目标格如果是个常量迭代一万次它也不动求解器读到前后两次目标值相等直接判定收敛并返回——你会看到一个“成功”的结果实际上一个数都没优化。可变单元格是求解器唯一被允许写入的区域。一次迭代的完整链路是把当前解写进可变单元格 → 表格引擎按依赖关系重算 → 读回目标值、读回每条约束的左端值 → 决定下一步往哪走。这条链路解释了为什么勾上“显示迭代结果”能看到数字在跳迭代不是在求解器内部黑箱里跑的每一轮都要跟表格引擎交换一次数据。约束则可以作用在可变单元格、目标单元格或者任何一条中间汇总格上。中间汇总格这一点被严重低估把“总工时不超过 200”写成一行 SUM再对 SUM 的结果加约束比手工把系数展开成一行不等式要清晰得多改动工时定额时也只改一处。这是表格建模相对纯脚本建模最实在的一个优势——每个中间量都摆在明面上评审的人能对着格子核对。“推测”按钮做的事是从目标公式出发反向遍历引用关系把所有非公式的叶子节点收集起来。这就注定了它的软肋INDIRECT、OFFSET 这类运行时才确定引用的函数以及跨工作簿的外部链接都不会出现在静态解析出的依赖树里。于是求解器认为这些格子不是变量自然也就不会去改它们。提示可变单元格有数量上限单次模型最多 200 个。做排产、选址这类问题时要先在建模阶段压缩维度别等到点求解才发现装不下。2.3 用一段脚本把模型骨架固化下来手工在格子里敲公式第一次还新鲜第二十个模型就全是复制粘贴的痕迹。把骨架用脚本生成是可复现的前提。from openpyxl import Workbook wb Workbook() ws wb.active ws.title LP # 决策变量B2:D2给一个非零初值避免除零和退化 ws[A2] x1,x2,x3 ws[B2], ws[C2], ws[D2] 1, 1, 1 # 约束系数矩阵写入 A4:C6左端用 SUMPRODUCT 一次算完 coef [[2, 1, 0], [1, 2, 1], [0, 1, 3]] for r, row in enumerate(coef, start4): for c, v in enumerate(row, start2): ws.cell(rowr, columnc, valuev) # 变量区固定引用系数区相对引用方便整列复制 ws.cell(rowr, column5, valuefSUMPRODUCT($B$2:$D$2,B{r}:D{r})) # 目标函数同样必须是公式否则求解器读不到函数关系 ws[E1] SUMPRODUCT($B$2:$D$2,{3,5,4}) wb.save(lp_model.xlsx)这段代码做的事情手工也能做差别在于它把“模型结构”变成可 diff 的文本。$B$2:$D$2用绝对引用是因为所有约束行都要指向同一组变量B4:D4用相对引用是为了整列下拉时系数区跟着平移。左端刻意不用B2*2C2*1这种逐项展开的写法而是走SUMPRODUCT一来求解器抽取系数时面对的是一整块连续区域二来运输问题、指派问题都能复用同一个函数形态改模型时只需要换矩阵。参数落位如下E1是目标单元格B2:D2是可变单元格E4:E6是三条约束的左端右端常量另行放在F4:F6添加约束时引用这两个区域即可。3. 求解器内部单纯形法在表格上是怎么跑起来的3.1 一次迭代的真实开销在哪里打开“显示迭代结果”逐次点下去会看到一串很说明问题的现象前几步收敛很快中间某个阶段目标值几乎不动最后又突然跳一下。这个节奏不是算法在偷懒而是它每一步都要跟表格引擎做一次往返。线性情形下求解器其实不需要反复问表格。它一次性把所有系数抽出来组装成矩阵之后在内存里做纯矩阵运算——不重算、不读格、不调函数。所以“采用线性模型”之所以快不只是算法从梯度类换成了单纯形更重要的是省掉了每轮几十上百次单元格求值。非线性情形就没这么幸运梯度要靠差分估计每一步都得扰动变量、触发重算、读回输出成本跟模型大小成正比。这就解释了一个新手常见的误判模型只有三十个格子怎么算起来比几万行的数据透视还慢因为慢的不是数据量是迭代次数乘上每次重算的代价。3.2 “采用线性模型”勾选之后哪些东西变了这个复选框是整个选项面板里最关键的一项它实际上是在向求解器声明“我保证这个模型是凸的”换来的是全局最优和速度。维度勾选单纯形不勾广义既约梯度搜索方式沿可行域顶点移动每步解线性方程组算约化梯度定方向沿方向做一维搜索最优性凸问题上局部最优即全局最优只能保证局部最优结果依赖初值约束数量无硬性上限每个变量除范围与整数外约 100 条导数选项无意义面板项不参与计算向前差分 / 中心差分逐个生效初值敏感初值只影响路径不影响最优解初值影响能否找到好解甚至能否收敛所谓“可行域顶点”本质上就是约束方程组的基可行解。单纯形每次把一个变量换出基、一个换入基对应到表格上就是“某几个格子从 0 变成非 0”。它之所以能保证全局最优是因为线性目标在凸多面体上的极值一定落在顶点上——这条性质一旦不成立比如目标里出现两个变量相乘整个保证就塌了。非线性路径里的“导数”选项值得单独看一眼。梯度不是解析算出来的是差分估出来的def f(x, y): return x**2 3*x*y y**2 def grad_forward(x, y, h1e-6): # 向前差分只往前探一步一阶精度函数调用少 return ((f(x h, y) - f(x, y)) / h, (f(x, y h) - f(x, y)) / h) def grad_center(x, y, h1e-6): # 中心差分两侧各探一步二阶精度代价是两倍求值 return ((f(x h, y) - f(x - h, y)) / (2 * h), (f(x, y h) - f(x, y - h)) / (2 * h)) print(grad_forward(2, 3)) # 解析真值约为 (13.0, 12.0) print(grad_center(2, 3))f的解析偏导是2x3y与3x2y在 (2,3) 处正好是 13 和 12。向前差分的结果会带着一个与步长同阶的偏差中心差分把偏差压到二阶。这个差别在变量远离边界时无关紧要一旦某个变量贴着约束边界单侧差分可能一步跨到可行域外估出来的梯度方向就是错的迭代会来回震荡。所以面板上那句“中心差分用于约束条件变化迅速、接近限定值的问题”翻译成人话就是模型在边界附近反复横跳、求解器给出“无法改进当前解”的时候换成中心差分再试。3.3 三个旋钮各管一段时间、精度、收敛选项面板上的参数看着多其实分属三个互不相干的层面混着调只会越调越乱。最长运算时间与迭代次数是外部刹车只管“什么时候强制停”跟解的质量无关。默认 100 秒、100 次对小型模型足够一旦你把这两个值调大通常说明模型本身有问题——正常规模的问题不该需要几千次迭代。精度管的是“约束算不算被满足”。它是 0 到 1 之间的小数本质是判定左端 ≤ 右端 容差时那个容差。设得太大越界 0.3 也算可行解出来的方案可能真的违反工艺限制设得太小浮点误差会把明明可行的解判成不可行报出一堆莫名其妙的“找不到可行解”。收敛度只管非线性问题判定条件是连续五次迭代目标值的改善量小于阈值。这是个简易的停滞检测器阈值调小会让求解器更“较真”代价是时间。线性模型下这个参数不参与计算调它没反应是正常的。还有一项经常被忘掉但很有用自动按比例缩放。当决策变量是 0.001 量级、而约束右端是百万量级时矩阵的条件数会很差小量级的有效位在浮点运算里被直接吃掉。勾上这一项求解器内部对每列做尺度变换让各行各列在数值上“差不多大”。4. 整数约束与 0-1 约束为什么“变成整数”会让问题难一个量级4.1 Int 和 Bin 是关系不是值添加约束的对话框里中间那个下拉框除了、、还藏着Int和Bin。选中它们之后右侧的约束值框会自动变成“整数”或“二进制”并且不可编辑。这个交互细节背后是模型语义的差异不等式约束是把变量的定义域切掉一半而整数约束是把定义域从连续统改成格点两者根本不是同一类东西所以在界面上走不同的通道。由此可以解释一个高频报错在约束值框里直接敲integer或binary会得到“在目标或约束条件单元格中发现错误值”。解析器把它当成一个名字或一段文本去查查不到就报错。正确路径永远是下拉框。Int 与 Bin 只能施加在可变单元格上这条限制同样来自语义。中间汇总格的值是由公式算出来的求解器改不动它就算你声明它是整数也没有任何机制能保证公式结果落在格点上。要让某个量取整只能整在决策变量上或者加一个整数辅助变量再用等式把它和汇总格绑起来。0-1 变量有两条路直接用Bin或者加两条约束0 ≤ x ≤ 1再加Int。两者在语义上等价后者相当于用“整数 有界”手工拼出布尔域会多一层分支判定实际建模时直接用Bin更省事。4.2 分支定界允许误差到底在换什么整数问题的求解套路是分支定界。先把整数约束全部松开当成线性规划解一遍这个解叫松弛解它的目标值给出了原问题的一个界最小化问题时是下界。如果松弛解里所有变量恰好都是整数运气好直接收工否则挑一个取值为分数的变量按x ≤ floor(v)和x ≥ ceil(v)岔成两个子问题各自再解松弛。整个搜索是一棵树叶子节点上是“全整数”的候选解用当前最好的可行解去剪掉那些界已经不如今解的分支。“允许误差”就是在这棵树上做妥协的旋钮。它是个百分数判定条件是当前最好解与当前下界之间的相对差距小于这个值就停止搜索。设 5%意味着只要找到一个解它与理论最优的差距在 5% 以内求解器就认为够好了。设得越大剪枝越狠出结果越快设得越小树要搜得更深。业务上能不能接受这 5%得由提需求的人来定不能由建模的人图省事替他们定。这也解释了整数问题为什么“加几个变量就爆”变量翻倍分支树的宽度跟着翻倍而每多一个分支节点就要多解一次线性规划。4.3 运输、指派、最大流其实是同一个矩阵这三种问题在教材里分三章讲落到表格里却是同一个写法一个二维变量区加行方向的汇总、列方向的汇总。问题目标函数行约束列约束变量域运输SUMPRODUCT(单位成本, 运量)最小各产地发运量 产量各销地接收量 销量非负实数指派SUMPRODUCT(费用矩阵, 指派矩阵)最小每人恰好接一件事每件事恰好一人做0-1最大流汇点入流量 最大中间节点流入 流出每条边流量 ≤ 容量非负实数运输模型的精髓全在SUMPRODUCT上它算的是两个同形矩阵对应元素乘积之和正好等于“单位成本 × 运量”的总和不需要手工展开成一长串乘积相加。供需平衡表的行和、列和各自写成SUM再对结果加等式约束模型就立起来了。指派问题在此基础上把变量域改成Bin同时把行和列的和都约束成 1一一对应的语义就出来了。最大流只需要把“产”换成“源”、“销”换成“汇”中间节点改成流量守恒。小规模指派问题可以直接暴力枚举来交叉验证求解器的结果这是建立信任最快的方式from itertools import permutations cost [[4, 1, 3], [2, 0, 5], [3, 2, 2]] n len(cost) # 枚举全部 n! 种一一对应关系取总费用最小的一组 best min((sum(cost[i][p[i]] for i in range(n)), p) for p in permutations(range(n))) print(best) # (5, (1, 0, 2)) 这类结果可与求解器输出逐项对照三个人的时候3! 6十几个人的时候就上亿了——这正是需要求解器的理由也是为什么模型必须写成矩阵形态规模一大只有矩阵写法能让你在加一行约束时不至于改五十个格子。5. 常见问题排查说“已收敛”却拿不到想要的解从哪儿看5.1 五条反复出现的翻车记录约束方向写反报“未找到合适结果”。现象是点求解后立刻弹出“规划求解找不到合适的结果”速度快得可疑。原因是供需不平衡或不等号写反总产量 300、总销量 280用等式约束就没有可行域。解决办法是把行约束改成并加一个虚拟销地吸收差额或者先把所有约束删到只剩一条确认单条可行再逐条加回来。目标格是常量返回一个“假最优”。现象是求解秒完成可变单元格纹丝不动。原因是目标单元格直接填了数字而不是公式求解器读到的目标值前后一致判定已经收敛。解决办法是把它改成对可变单元格和系数区的SUMPRODUCT并且用“推测”确认依赖树抽得全。勾了“采用线性模型”却报不满足线性条件。现象是求解结束提示“无法满足设定的线性模型条件”。根因是模型里藏着非线性成分两列变量相乘、IF、ABS、MAX甚至跨表引用带条件判断的汇总。解决办法是先清空复选框跑一次看能否求出解确认非线性后再决定是保留非线性路径还是把ABS拆成两条不等式做线性化。结果差一点0.9999而不是1。现象是整数规划求出来的变量本该是整数却带着小数点后几位。这是精度容差的正常表现判定可行时允许微小的越界。解决办法是把精度调紧一档或者对结果做四舍五入后用约束把值固化下来再验算一遍目标函数。量级悬殊导致迭代停滞。现象是迭代几十次目标值几乎不动最后报“无法改进当前解”。原因是变量在千分位、约束右端在百万位浮点有效位被吃掉梯度方向失真。解决办法是勾上自动按比例缩放或者建模时统一量纲把“元”换成“万元”。5.2 一个照着走的定位顺序排查别从参数开始从可行域开始。先判断“这个模型到底有没有解”再判断“解得好不好”。第一步把所有约束删掉只留变量非负求解。能出解说明目标函数和依赖树是通的出不了解问题在目标格不是公式或者依赖断了。第二步逐条加回约束每加一条解一次。哪一条加进去之后开始报不可行问题就在那一条上——方向、右端值、引用区域三选一。第三步确认线性与否。把IF、ABS、乘积项、MAX/MIN挨个找出来要么清掉要么接受非线性路径。第四步才是调参数。量级差得多就缩放整数解质量差就调允许误差非线性停滞就换中心差分、给个更接近期望值的初值。最后一步经常被跳过但最该做把求解结果代回原始的约束格子逐条核对左端和右端。求解器说的“满足”是带着容差说的人得自己再确认一遍。6. 该不该把模型交给表格一次关于透明与规模的取舍表格求解器最被低估的价值不是算得快是模型可读。目标、变量、约束、中间汇总全部摆在格子里任何一个不做运筹的人都能指着某一行问“这个 200 是哪来的”而回答这个问题只需要点一下引用。这种可审计性在评审、交接、教学场景里几乎无可替代——脚本模型再优雅读的人得先会那门语言。代价也很清楚。可变单元格 200 的上限、非线性约束数的限制、单会话持有动态库、二进制文件难以做版本对比这些在一次性排产、课堂演示、部门级小模型里都不构成问题一旦要批量跑几百个场景或者放进流水线就会立刻变成硬墙。我的判断标准很简单模型要给别人看懂留在表格里模型要被程序调用、被反复回归、被 diff就搬去脚本。这不是谁替代谁是两套取舍各自守着自己的场景。还有一件事值得说模型可以另存成工作表上的一块区域下次直接装入参数不会丢。这个机制我用得很勤但从来不把它当版本管理——因为那块区域和工作表数据混在一起改一次就覆盖一次没有后悔药。从那以后我每次建完模型都强制先把所有约束的左端和右端单独算一遍、并排打出来对照再点求解。这个习惯救过我至少三次两次是不等号写反一次是SUMPRODUCT的区域少选了一列。希望帮到你。本文还有配套的精品资源点击获取