线性规划问题的图解（规划求解究竟有多好用）

Excel里面，有一个很有用，但是很少被大家重视的功能：规划求解。这个功能很多人都还不太清楚，那这个功能究竟有什么用？在什么情况下使用？该怎么用？我用九个案例给你答案。

规划求解是MicrosoftExcel加载项程序，可用于模拟分析。使用“规划求解”查找一个单元格（称为目标单元格）中公式的优化（最大或最小）值，受限或受制于工作表上其他公式单元格的值。“规划求解”与一组用于计算目标和约束单元格中公式的单元格（称为决策变量或变量单元格）一起工作。“规划求解”调整决策变量单元格中的值以满足约束单元格上的限制，并产生您对目标单元格期望的结果。

上面是官方的解释，虽然说了一大堆，但还是不明白这个工具能做什么（官方的解释，都是书面的，可以直接跳过）。接下来我用案例来给大家说说这个工具的强大。做好准备哦。

---

准备工作

规划求解这个工具是默认未加载的，使用前需要先加载【规划求解加载项】，操作如下：开发工具 → 加载项 → 勾选规划求解加载项 → 切换到数据选项卡 → 规划求解就在最右边了。（见下图）

加载规划求解

---

案例一：凑数。给一个结果，然后在一堆数据中找哪些数字的和等于这个结果

比如下面这个案例，在A3:A17区域有很多数据，现在要计算哪些数据相加会得到336（大家可以想想这个问题用常规方法该怎么解决呢）。下面我们看规划求解的解题过程：

案例1解题演示

案例1：规划求解的参数设置

下面给大家说说规划求解中，各约束条件的含义（约束条件是对可变单元格的值进行约束）：

解析：案例1中，我们设置的约束条件为【bin】二进制，即符合条件的数据，在可变单元格中显示1；不符合条件的数值，在可变单元格中显示0。

---

案例2：解一元方程。公式【3^x 6^x＝8^x】，求 x 的值

接下来我们看看规划求解的解题过程，在解题前，需要参照下面图片，将相关数据录入表格中。

案例2国画求解解一元方程

案例2：规划求解参数设置

解析：案例2中，用数学基础知识，可以判断出X的值在1和2之间，所以，设置的x≥0.01作为约束条件，然后由电脑自己去计算。当然有些题目的答案超出Excel的数值位数（Excel里面最大支持15位数，超过了就算不出来，比如π），就会存在解不出来的情况。比如下图这个题目，得到的x是一个对数，转换成数值是一个无限小数，这种是不能转换成具体数值的，即使计算出答案了，结果也不准确。

---

案例3：解二元方程

题目：今有雉（鸡）兔同笼，上有三十五头，下有九十四足。问雉兔各几何？

假设鸡有x只、兔有y只，那么可以组成以下二元一次方程组

接下来我们看看如何用规划求解解开答案的：

案例3：解二元一次方程组

案例3：规划求解参数设置

解析：这里把鸡、兔的数量作为变量，脚的和做为目标值，头的和作为约条件，鸡、兔的数量约束条件为非零整数，即可完成解题。

---

案例4：趣味填数游戏1，要求详见下图

案例3：题目

此题需要用到辅助单元格（辅助单元格设置，详见下面的操作动画），解题过程如下：

案例4：趣味填数字游戏1

案例4：参数设置

解析：设置辅助单元格的目的，是在约束条件中，要设定一个不重复值，且这个不重复值需要是连续区域，如果按题目中各个单元格是分开的，就无法实现这个效果。本题用的三边之和作为目标值，用的每边之和作为约束条件。

---

案例5：趣味填数游戏2，要求详见下图

案例5：题目

此题的解题过程如下：

案例5：趣味填数游戏2

案例5：规划求解参数设置

解析：此题的九宫格由于是连续区域，故不需要辅助单元格；目标值用的是各边之和的和（120）；约束条件有两条，一为九宫格区域为不重复值，二是用每边的和等于15（正常情况下，应该还有一个约束条件，即九宫格区域额值＜=9，由于设置了和为15，所以这个条件可以忽略）。

---

案例6：取最优组合1，题目见下图，要求从所有可能的方案中计算出最优组合

案例6：题目

接下来我们看下解题过程：

案例6：计算最优组合1

案例6：规划求解参数设置

解析：此题设置了三个约束条件，一为目标产量是整数，二是目标产量大于等于最低要求产量，三是原料消耗总量小于等于现有原料。

---

案例7：取最优组合2。

某玩具厂有三个车间，计划生产甲、乙、丙三种玩具，要求三个车间生产同一种玩具的数量相同，并且要按照规定的时间内完成，每个车间所给的时间不一样。其他信息如下图所示。求能获得最大利润的最佳生产方案。

案例7：题目

接下来我们看解题过程：

案例7：取最优方案2

案例7：规划求解参数设置

解析：本题的约束条件有二，一为产量为整数，二最优限时要小于等于限时。

---

案例8：排班，员工值班排班（要求如下）

值班要求： 1、店内有4名员工；

2、每天的营业时间是早上8:30至晚上21:30，共13小时；

3、营业高峰期：中午11:30-13:00、下午17:00-19：00，至少要有3人上班；

4、每天早上8:30至9：30位准营业时间，至少要2名员工上班；

5、每个员工每天至少工作8小时，一周内工作时长为54小时；

我们把以上数据整理成表格

案例8：题目

接下来我们看解题过程：

案例8：值班人员排班

案例8：规划求解设置

解析：用总工时等于224（8小时×4人×7天）作为目标；排班区域用二进制，安排值班的结果为1，安排休息的结果为0，然后设置每人的工时大于等于54，每个时间段实际的人数大于等于需要的人数。

---

案例9：批量处理规划求解

如下图所示，已知单价、已知目标金额，要求计算每个单价所对应的数量，让单价与数量之和等于目标金额。

案例9：题目

此题解其中一个的过程：

案例9解其中一题

但是本题数据较多，如果一道题、一道题的解，很浪费时间。由于此题比较有规律，就可以用VBA来实现批量解题。在VBA中使用规划求解，需要引用Solver

引用Slover

然后用以下代码，就可以实现批量解题：

Sub 批量执行规划求解()

Application.AlertBeforeOverwriting = False

Dim a As Integer, Arr

Arr = Range("F4:F" & Cells(Rows.Count, 1).End(xlUp).Row)

Dim Rng1, Rng2

For a = 4 To UBound(Arr, 1) 3

Rng1 = Range("$E$" & a).Address

Rng2 = Range("$B$" & a & ":$D$" & a).Address

SolverReset

SolverOk SetCell:=Rng1, MaxMinVal:=3, ValueOf:=Arr(a - 3, 1), ByChange:=Rng2, Engine:=1, EngineDesc:="GRG Nonlinear"

If Arr(a - 3, 1) = VBA.Int(Arr(a - 3, 1)) Then

SolverAdd CellRef:=Rng2, Relation:=4, FormulaText:="整数"

Else

SolverAdd CellRef:=Rng2, Relation:=3, FormulaText:=0.1

End If

SolverSolve Userfinish = False

Next a

Application.AlertBeforeOverwriting = True

End Sub

批量解题

---

以上是我收集整理的9个规划求解应用案例。耐心看完的朋友，或许会发现这个工具在实际应用中比较方便，需要用很复杂算法来计算的内容，用这个工具直接就可以解开。如果你们以前也有用过规划求解的案例，也请在留言区留言讨论，大家一起学习。

此文中案例、数据较多，有需要此文件的朋友，可以关注我，然后通过私信发送关键字【规划求解】获取文件。（还不太会发私信的朋友，可以参考我之前分享过的文章，里面有详细介绍）

,