2024年8月6日发(作者：)

秘诀70：规划求解（Solver）

规划求解是一组命令的组成部分（有时也称作假设分析工具）。借助“规划求解”，可求

得工作表上某个单元格（称为目标单元格）中公式的最优值。“规划求解”将对直接或间接与

目标单元格中的公式相关的一组单元格进行处理。“规划求解”将调整所指定的变动单元格

（称为可变单元格）中的值，从目标单元格公式中求得所指定的结果。您可以应用约束条件

来限制“规划求解”可在模型中使用的值，而且约束条件可以引用影响目标单元格公式的其他

单元格。

该项需要在Excel2007手工添加：Office2007按钮→ Excel选项→ 加载项→ “规划求

解项”→ 转到→ 确定。

1、合适规划求解的问题，包括满足如下标准的情况：

 目标单元格依靠于其他单元格或公式。如用户需要最大化或最小化这一目标的单元

格，或者使之同某些数值相等。

 目标单元格依靠一组单元格（称可变单元格），Solve可调整这组单元格来影响目标

单元格。

 解必须服从一定的约束与限制条件。

2、利用Solver来解决问题的基本过程：

1) 建立含有数值与公式的工作表，保证建立单元格的逻辑性；

2) 调出Solver命令；

3) 指定目标单元格及规划求解目标（最大化或最小化等）；

4) 指定可变单元格；

5) 指定约束条件；

6) 如果必要，改变Solver选项；

7) 使用Solver求解，并生成运算结果报告。

3、控制Solver选项

 最长运行时间：指需要规划求解一个问题所花费的最大时间。

 迭代次数：指用户需要规划求解执行的近似求解的最大次数。

 精度：指定单元格定位和约束公式必须满足约束条件的接近程度。约束越精确，

Excel求解问题的速度越快。

 允许误差：指定整数解的最大容许误差百分比。（对整数有效）

 收敛度：指定一个数值确定何时Solver应该停止求更佳解，仅适用于非线性问题

介于0~1之间。

 采用线性模型：可加速线性问题的求解速度。

 自动按比例缩放：

 假定非负：为所有可变单元格假设下限为零。

 显示迭代结果：Solver在每次迭代结束以后暂停并显示结果。

管理人员往往要在一定既定的条件下做决策，例如投资哪些项目、生产哪一中产品，

Excel的规划求解是用来解决其中一些问题。

为什么不用手工计算呢？第一，变量数目多，而影响的条件也可以很多，很难用手

工计算；第二，就算条件很简单，用手工计算也不一定得到最优化的结果。

例1：我们决定要生产多少个Ａ产品、多少个B产品。产品A需要2个马达、9米电线

和12个齿轮，每个产品A有400元的利润；产品B要1马达、6电线和16齿轮，每个产

品B有300元的利润。各种原料有限，库存有200个马达、1600米电线、2880个齿轮。目

标是利用现有资源去得出最大的利润。

生产量

原材料

马达

电线

齿轮

利润

A B 使用部件总数 库存量

100 0

2 1 200 200

9 6 900 1600

12 16 1200 2880

￥ 400 ￥ 300 ￥ 40,000

由于产品A每件利润多，可假设全部生产产品A共100件，根据生产量、所需原材料

个数和产生利润的关系，生成表格，如上表。

步骤：

1) 建立生产量、所需各种原材料个数和产生利润的关系表。使用部件总数=A件数*

所需部件数+B件数*所需部件数，总利润= A件数*400+B件数*300；

2) 数据功能区→ 规划求解；

3) 参数设定：目标单元格：$d$9；选：最大值；可变单元格：$b$2:$c$2；

4) 添加条件：条件1：$D$5: $D$7<=$E$5: $E$7，条件2：$b$2:$c$2 int 整数，在选

项中选中假定非负；

5) 求解，在窗口中生成一个最优结果；

6) 保存结果：报告→ 运算结果报告。