简介这是一份Excel规划求解案例分析的PPT教学课件主要面向经济管理类专业学生、企业运营与数据分析人员以及需要利用Excel完成优化建模的职场人士。课件围绕“在约束条件下求目标函数最优解”这一核心首先说明规划问题的概念与共性特点随后演示如何加载“规划求解”宏、配置参数对话框并按“输入数据→用SUMPRODUCT引入约束→求解”的标准流程操作。实例部分涵盖雅致家具厂生产计划优化、多目标规划等经典问题既给出了线性规划的数学模型也展示了实际求解与敏感性分析的思路能帮助读者从建模到落地方案完整走通。资源为单份PPT1个文件约541KB当前已有543人学习浏览内容精炼紧凑适合快速入门Excel规划求解的常见场景。1. 规划求解不是黑匣子它到底在算什么为什么经常被人放弃拿到《EXCEL规划求解案例分析.ppt》这类分享材料时很多人第一反应是翻页找现成模板直接套。但真做过几次的人会告诉你卡住你的从来不是按钮在哪而是“什么才算一个合格的模型”。规划求解Solver本质是在一组业务限制条件下自动试算最优解线性、整数、非线性问题都能处理排产、运输、分仓、预算分配这类场景能把几小时的手工试算压缩到几分钟。它不算复杂但有两个容易被低估的点一是结果对模型结构极其敏感二是在Excel里设置约束的方式和直觉有偏差。我默认你在看这类案例时手里有真实约束和真实目标想把它用到工作计划、生产排产或预算分配里。这篇按“建模三要素 → 最小可跑案例 → 完整排产案例 → 运输与指派变体 → 避坑 → 批量求解”的顺序尽量让你照着做就能落地。2. 用Excel规划求解建模的第一步把业务问题翻译成可求解的三要素2.1 目标函数、决策变量、约束条件建模前先回答三个问题规划求解之前先回答三个问题你要决定什么值你希望哪个指标最大或最小有哪些限制条件三个问题对应规划求解的决策变量、目标函数、约束条件。很多案例讲不好是因为一开始就急着开Solver对话框而不是先画清楚这三个要素。打个比方决策变量是你要拧的旋钮目标函数是你要看的仪表盘约束条件是红线。旋钮拧到什么位置仪表盘读数为最优同时所有红线不越界。业务问题里旋钮一般是“生产多少件”“从哪个仓库发多少货”“哪台机器做哪个任务”仪表盘一般是“总利润”“总成本”“总工时”红线是“可用工时上限”“供货量下限”“合同最低交付量”。Excel里规划求解的入口在“数据”选项卡下。如果看不到先加载加载项文件 → 选项 → 加载项 → 转到 → 勾选“规划求解加载项”。这一步是新手翻车的第一个点——不少人以为自己的Excel没有这个功能其实只是加载项没启用。建模时我一般建议把三要素放在独立的单元格区域不要随手写在公式里。目标函数单独一个单元格约束条件的左侧单独一行右侧是资源上限。这样在Solver对话框里填引用时非常直观也方便后续调试。2.2 用最小化成本案例跑通第一个规划求解附操作步骤和参数用一个最小案例跑通流程。假设一个小作坊要决定产品X和产品Y各生产多少件。每件产品X利润12元耗时2小时每件产品Y利润15元耗时3小时。总可用工时为50小时。目标是利润最大化决策变量是两个产品的产量。表格布局如下单元格内容B2X的单位利润 12C2Y的单位利润 15B3:C3X、Y的单位工时 2、3B4可用工时上限 50B5决策变量X产量留空待求解C5决策变量Y产量留空待求解D3SUMPRODUCT(B3:C3, B5:C5)即总工时B6SUMPRODUCT(B2:C2, B5:C5)即总利润先给决策变量填两个初始值比如0否则目标函数计算会出错。然后打开“数据 → 规划求解”按下面配置设置目标$B$6到最大值通过更改可变单元格$B$5:$C$5添加约束$D$3 $B$4添加约束$B$5:$C$5 0求解方法单纯线性规划点“求解”后Excel会弹结果对话框选择“保留规划求解的解”。这时B5和C5就是最优产量。这段操作里的几个参数值得解释。目标单元格必须是公式单元格不能是常量MaxMinVal里“最大值/最小值/目标值”对应Max、Min和具体数值可变单元格就是决策变量所在区域约束里的Relation1表示小于等于2表示等于3表示大于等于4是整数5是二进制。这些关系在VBA批量调用时也会用到现在记下来后面省事。这个模型是线性的因为目标函数12×产量15×产量约束2×产量3×产量都是变量的常数倍之和。线性问题选“单纯线性规划”求解速度最快结果也最稳定。不要在这个阶段选GRG或演化式非线性求解器在线性问题上是杀鸡用牛刀还可能因为初值问题返回奇怪结果。提示如果目标函数或约束条件包含变量的乘积、除法、指数那就不是线性模型需要选GRG非线性或演化式。第4章会专门说怎么选。3. 手把手拆解一个生产排产案例从业务需求到Solver参数落盘3.1 案例背景与数据表设计用一个典型的多产品、多产线排产案例把建模过程完整走一遍。某电子配件厂有3条产线共生产5种型号每条产线有各自的可用工时上限每种型号有合同要求的最低产量。目标是总利润最大决策变量是5种型号的产量。数据设计如下型号P1P2P3P4P5单位利润元/件1215201822L1工时小时/件0.50.20.300.4L2工时小时/件0.20.400.50.2L3工时小时/件0.300.40.20.1最低产量件10080605040可用工时上限L1是400小时L2是300小时L3是350小时。数据放好后在表格右侧单独留一列或一片区域放计算单元格。我习惯把可变单元格放在第2行把每条产线的总工时公式放在第4行到第6行把目标利润放在第7行。这个布局没有强制要求但行列结构必须让Solver的引用区域整齐尤其当你以后要写VBA批量求解时区域整齐会让循环代码简单一半。3.2 在Excel里搭建可变单元格与约束公式假设产量放在C5:G5单位利润放在C4:G4三条产线的单位工时分别放在C6:G6、C7:G7、C8:G8。那么核心公式如下总利润 SUMPRODUCT($C$5:$G$5, $C$4:$G$4) L1总工时 SUMPRODUCT($C$5:$G$5, $C$6:$G$6) L2总工时 SUMPRODUCT($C$5:$G$5, $C$7:$G$7) L3总工时 SUMPRODUCT($C$5:$G$5, $C$8:$G$8)SUMPRODUCT的两个区域必须尺寸一致这一点非常关键。很多人把决策变量区域选成C5:G5系数区域却选成C4:G6导致得到#VALUE!整个模型直接不能用。区域不一致时Excel不会提示只在结果里返回错误值。这些公式不是直接放进Solver的约束框里的。规划求解的约束里只能填单元格引用不能填“SUMPRODUCT(...)400”这类表达式。所以必须先把总工时算到某个单元格里再让约束引用这个单元格。这是新手最容易卡住的地方以为约束对话框里能直接写公式结果怎么写都不认。3.3 调用规划求解设置目标、可变单元格和约束的完整路径打开“数据 → 规划求解”完整配置一次设置目标$B$9总利润单元格到最大值通过更改可变单元格$C$5:$G$5添加约束$C$9:$E$9 $G$9:$I$9三条产线总工时 ≤ 对应可用上限添加约束$C$5:$G$5 $K$5:$O$5产量 ≥ 最低产量勾选“使无约束变量为非负数”选择求解方法单纯线性规划点“求解”前建议在“选项”里把“显示求解进度”打开。这样模型跑不动时你能看到它卡在哪一步是在尝试调整哪些变量的值。这里提到“使无约束变量为非负数”是一个值得单独强调的选项。勾选它等同于给所有可变单元格加了0约束省去手动添加。但要注意它只对可变单元格有效对中间计算单元格无效。如果你的模型里有些变量本身允许为负比如库存偏差就别勾改成手动添加单条约束灵活度更高。求解完成后如果结果满意记得使用“保存方案”功能。路径是求解结果对话框 → 保存方案输入方案名。这个操作看起来简单实际价值很大当你试第二个参数组合时才不会把第一个结果覆盖掉装回方案只需要一步。提示可变单元格最好填一个合理的初值比如最低产量。初值为0在某些约束组合下会让整数规划求解器少走弯路但在非线性问题里初值直接影响结果是否陷入局部最优这是第4章要展开的坑。4. 运输与指派问题的变体案例约束条件多了怎么处理4.1 运输问题模型供应量、需求量与整数约束排产案例是最基础的模型实际业务里更常见的是运输问题多个仓库往多个门店发货每个仓库有供应量上限每个门店有需求量下限各条线路运费不同求总运费最低的调拨方案。假设2个仓库W1、W2供应量分别是80吨和60吨3个门店S1、S2、S3需求量分别是40吨、50吨、50吨。运费矩阵放在一个2行3列的区域决策变量放一个同样大小的2行3列区域表示从各仓库发往各门店的数量。目标函数是总运费SUMPRODUCT(运费矩阵区域, 决策变量区域)。约束分两组每一行的合计小于等于对应仓库供应量每一列的合计大于等于对应门店需求量。这里需要两个SUM公式分别写在决策变量矩阵的右侧和下方。行合计W1发往各门店的总量 SUM(B11:D11) 列合计各仓库发往S1的总量 SUM(B11:B12)Solver配置里目标单元格是总运费可变单元格是决策变量矩阵约束分别是行合计供应量列列合计需求量列。这个模型是线性的选单纯线性规划即可。如果业务要求整车发货也就是运输量必须是整数就在约束里给决策变量区域加一个整数约束注意整数约束会显著增加求解时间但只要规模在几百个变量以内Excel一般都能接受。一个容易忽略的细节当总供应量大于总需求量时约束用“行供应量、列需求量”可以容纳多余的供应量不发出去如果写成行供应量就会强迫把多余的货也发出去导致成本虚高。先算一下供应总量和需求总量再决定用还是/。4.2 指派问题与If-Then逻辑怎么把“要么做要么不做”写进约束指派问题是运输问题的变体典型场景是3个任务分给3个人每个人只能做一个任务成本矩阵已知求总成本最低的分配方案。这里决策变量不是数量而是一个0/1矩阵某个位置为1表示把该任务分配给该人为0表示不分配。约束条件变成每行之和1每列之和1。这两组约束确保每个任务恰好被一个人承担每个人恰好做一个任务。在Solver里添加约束时选中决策变量区域然后选择“二进制”关系一次性把所有变量限制为0或1。如果业务上还有“选了任务A就必须选任务B”这类条件可以用线性不等式表达。设变量xA和xB都是0/1变量“A为1时B也必须为1”写成xA xB即可。因为当xA1时xB必须是1才满足xA0时xB随意。“A和B互斥”写成xA xB 1“A和B必须同时选”写成xA xB。这种写法是整数规划的基本功能覆盖大部分组合逻辑。如果任务关系中还涉及“如果选了A则必须在B和C里至少选一个”表达式是xA xB xC。这些约束全部是线性的不影响求解方法选择。4.3 非线性/整数问题的求解选项GRG非线性、LP单纯形、演化式怎么选Solver对话框里有三种求解方法选错是结果不合理的重要原因。三种方法的适用范围如下表求解方法适用问题特点单纯线性规划目标函数和约束都是变量的线性组合求解快结果稳定优先使用GRG非线性目标或约束含乘积、除法、指数、对数等非线性项对光滑函数有效初值敏感可能得到局部最优演化式目标不连续或公式里有IF、查表、取整等逻辑慢但在非线性不光滑时最通用实际问题里约束通常都是线性比如工时、运量、可用量它们都是乘常数再相加。利润如果是单价乘以数量也是线性。线性模型直接用单纯形这是最佳实践。什么时候会用到GRG比如成本函数里有批量折扣单价在产量超过某个阈值后自动变化这时总成本公式里出现了“产量 × 分段单价”的乘积。再比如目标函数包含单位利润除以良品率之类的比例项这就是非线性必须换GRG。什么时候用演化式公式里包含IF判断是最典型的信号。Excel公式里一旦出现IF求解器很难处理导数GRG经常会卡住或返回离谱的值。比如约束里写着“如果库存大于100则出货100否则出货库存”这种逻辑是分段的不光滑演化式是唯一能尝试的选项。另一种情况是求解方法选对了但结果明显不合理。GRG非线性对初值高度敏感同一个模型初始值从0开始和从合理估算值开始经常收敛到不同的结果。所以用GRG时初值一定要给接近业务实际的值不要留空或填0。演化式没有这个问题但速度慢很多一般把“最大求解时间”和“最大迭代次数”调大一些接受几十秒甚至几分钟的运行时间。注意整数约束与求解方法不冲突。带整数约束的模型无论选哪种方法Excel内部都会改用分支定界算法来处理整数部分。为减少耗时建模时尽量让整数变量控制在200个以内超出后求解速度会明显下降。5. 规划求解避坑指南结果不合理时先检查这5类问题5.1 报错“规划求解找不到可行的解决方案”现象约束条件看起来完全合理但一点“求解”就弹出找不到可行解。原因排查最常见的是约束方向写反把“”写成“”或者把“”写成“”。其次是约束引用了隐藏的行列或者可变单元格区域里混入了带公式的单元格。还有一种情况是初值填得过于离谱导致整数变量在分支定界中找不到起始可行点。解决先把所有约束从Solver对话框里临时删掉只留目标函数和可变单元格点“求解”确认目标能计算。能算再一条一条加约束每次加完都求解一遍加到最后一步时就能定位是哪条约束导致无解。这个方法笨但有效。另外检查可变单元格区域确认每个单元格都是空值或常数没有任何公式残留。初值建议填到业务正常范围内比如最低产量附近。5.2 结果出现小数而业务要求整数现象产量明明是“台”求解结果却是1.5台、2.7台。原因没有添加整数约束或者在添加整数约束时范围选错了。只给部分变量加了整数约束其他变量仍然允许小数。解决在Solver对话框点“添加”选中全部决策变量区域在关系里选“整数”。注意区域必须覆盖所有需要整数的变量缺一个都不行。还有一个隐蔽问题整数约束加在错误的单元格引用上比如引用了系数区域而不是决策变量区域。添加后观察约束列表确认每一条引用指向正确区域。5.3 目标单元格返回#VALUE!求解前就已经塌方现象还没点求解目标单元格里就显示#VALUE!或#NUM!。原因SUMPRODUCT里两个区域的形状不匹配比如变量区域是5列系数区域却是6列或者可变单元格被留空公式引用了空单元格导致计算错误。解决先单独测试目标公式。随便在某个空单元格输入SUMPRODUCT(区域1, 区域2)看一下是否返回数字。返回#VALUE!检查两个区域的列数和行数是否完全一致以及区域边界是否多选或少选。另外把可变单元格先手动填0这样可以立即验证公式是否能算。公式恢复计算后再清空可变单元格交给Solver去求解。5.4 敏感性报告读不懂阴影价格和允许变化量现象求解成功也勾选了“敏感性报告”拿到报告却不知道哪一行有用。原因没有理解阴影价格的含义。简单说阴影价格影子价格表示约束右端项增加一个单位时目标函数值的变化量。比如工时约束的阴影价格是5说明可用工时上限每增加1小时总利润会增加5元。但要注意两个边界第一这个价格只在“允许增加”和“允许减少”的范围内有效超出这个范围约束不再是紧约束阴影价格会改变。第二最大化问题和最小化问题中阴影价格的正负含义不同建议先看目标单元格的方向再看约束右端项的变化方向不要直接套“正就是好”。报告里真正值得每次查看的是“允许增加/减少”这两列它告诉你当前方案的资源瓶颈在哪个约束上以及加多少资源还有效。5.5 变量太多、求解太慢现象几十个变量加整数约束求解要十几分钟甚至卡到像死机。原因整数约束太多或误用了演化式求解线性问题。演化式在默认迭代次数下会花大量时间做随机搜索而问题本身用单纯形只需要几秒。解决先确认模型是线性问题在求解方法里明确选“单纯线性规划”不要选“演化式”。然后把整数变量数量压下来。很多变量并不需要整数限制比如运输问题里的吨数如果不是整车运输允许小数完全合理。减少一批整数约束后求解时间会指数级下降。仍需要大量整数变量时可以拆问题按产线或仓库把大模型拆成几个小模型先分步求解再合并结果。拆的时候注意跨模型共享的约束会丢所以只拆约束基本独立的部分。6. 让规划求解不止于手动点按钮用VBA循环批量求解多方案6.1 为什么要批量多场景对比的日常需求实际业务里很少只跑一次Solver。需求变化、成本变化、可用工时变化每次都要重跑一遍。如果每天都手动打开Solver对话框改参数点求解一天能浪费十分钟一周就是一个小时。批量求解的思路很简单用一个参数单元格作为输入用VBA循环修改它每次修改后都调用一次Solver把结果记录到指定区域。6.2 一个简单的VBA循环求解框架下面的代码实现了一个循环每次把B1单元格的值从1改到10求解一次线性规划并把目标值记录到H列。Sub BatchSolver() Dim i As Integer, res As Long For i 1 To 10 Range(B1).Value i 修改业务参数 SolverReset 重置求解器设置 SolverOk SetCell:$B$6, _ MaxMinVal:1, _ ValueOf:0, _ ByChange:$B$5:$C$5, _ EngineDesc:Simplex LP SolverAdd CellRef:$D$3, Relation:1, FormulaText:$B$4 SolverAdd CellRef:$B$5:$C$5, Relation:3, FormulaText:0 res SolverSolve(UserFinish:True) 求解关闭对话框 Range(H i).Value Range(B6).Value 记录目标值 If res 5 Then Range(I i).Value 无可行解 End If Next i End Sub这段代码需要注意几个参数。SolverOk里的MaxMinVal1表示最大值2表示最小值3表示目标值ByChange必须是可变单元格区域。SolverAdd里的Relation1、2、3分别对应、、4和5分别是整数约束和二进制约束。SolverSolve返回值0表示求解成功5表示无法找到可行解12表示超过了时间限制返回时不能用SolverSolve的返回值做判断。运行前还有一步必不可少在VBA编辑器里点击“工具 → 引用”勾选“Solver”。不勾选运行到SolverOk会直接报“子过程未定义”。这是VBA调用Solver最常见的翻车点错过的人不在少数。6.3 进阶建议把结果回填成多方案对比表上面只是批量求解的骨架。实际使用时把目标值和决策变量都回填到同一个区域最后生成横向对比表。比如B1是需求参数H列记录每次的总利润再把每次的决策变量B5和C5分别记录到J列和K列之后用条件格式给H列标注最大值就能一眼看出哪个参数组合下利润最高。我自己处理这类问题时的习惯是拿到新问题先建线性模型跑通再加整数约束最后才考虑GRG。以前在某模拟项目里一上来就用演化式求解几十个变量一等就是半小时。后来发现问题本身是线性的换成单纯形加整数约束后几秒钟就出结果。先把最简单的模型跑通再逐步加复杂度这个习惯救过很多次。希望帮到你。本文还有配套的精品资源点击获取