Excel数据分析篇：EXCEL规划求解的简明教程

EXCEL的规划求解模块难道不是用来求解运筹学问题的专业工具吗？怎么没有一篇文章提及这一点呢？

一、背景介绍

《运筹学》研究的问题一般是在若干资源有限的情况下如何找到最优的决策，比如费用最小的方案，或者花费时间最短的方案，或者利润最大的方案等等。《运筹学》在经济、管理、交通运输、物流等领域得到广泛使用，也是这些行业管理决策的核心技术。

EXCEL的规划求解模块是一款以可选加载项的方式随微软Office软件一同发行的求解《运筹学》问题的专业软件的免费版本，内置单纯型法、对偶单纯型法、分支定界算法、广义既约梯度算法和演化算法，能够用于求解线性规划、整数规划和非线性规划问题，操作简单，求解迅速。

EXCEL的规划求解模块是Frontline Systems公司为通用电子表格软件提供的插件，其产品名称为Fontline Solvers。其目标是帮助普通用户对优化模型进行快速求解。

官方网站为：solver

商业版的规划求解模块功能强大，能够求解包含成千上万个决策变量和约束条件的多种类型的数学模型，性能也不错。

而免费版本在决策变量和约束条件的数量和求解时间上有如下限制：

最多200个决策变量

最多100个约束条件（包括变量上下界约束在内）

求解时间不超过30秒

二、加载规划求解模块

EXCEL的规划求解模块默认是不加载的，要使用规划求解必须先使之成为默认加载，这样每次启动EXCEL，都会加载这个模块。下面以EXCEL 2013版本为例，简单演示加载的方法，其他版本大同小异。

第1步，单击文件菜单，然后单击左侧最下面的选项按钮。

第2步，弹出如下Excel选项对话框，然后单击左侧下面倒数第二项的加载项：

第3步，在下面的对话框中，单击转到按钮

第4步，在弹出的对话框中，勾选规划求解加载项，然后点确定按钮，就完成了。

此时，如果你在EXCEL的工具栏上单击数据，就可以看到如下界面。最右侧出现了红色方框中的规划求解按钮。这就表示加载成功了。

三、实例演示

下面我以一个简单实例来说明如何运用EXCEL的规划求解模块求解线性规划问题。

设甲产品应该生产 x₁ 件，乙产品应该生产 x₂ 件。这就是决策变量。

显然，总利润 Z=2x₁+3x₂ ，

要使总利润最大，所以目标函数就是： max Z=2x₁+3x₂

对于设备A来说，每生产1件甲产品需要在设备A上加工2h，每生产1件乙产品也需要在设备A上加工2h，但设备A全天可以的加工时间最多只有12h（即设备A的生产能力）。显然，在生产甲乙两个产品时设备A的实际加工时间不能超过它的最大可用加工时间，这一约束条件可以表达为： 

依次类推，设备B、设备C、设备D也应当满足这样的约束条件。

所以完整的数学模型为：

最后一个约束表示甲乙两种产品生产的数量要么是0，要么是正数，反正不能是负数。

要想用EXCEL规划求解来求解这个数学模型，首先必须在规划求解对话框中把决策变量、目标函数和约束条件一一输入，然后才能求解。

第1步：输入常数

如上图所示，其中黄色背景的B4和C4两个单元格是用于存放决策变量，也就是未知数，将来由规划求解模块来确定变量的值。

第2步：输入目标函数的计算公式

在E4单元格输入目标函数的计算公式如下图所示：

计算公式为：=SUMPRODUCT(B4:C4,B5:C5)，它等价于：= B4*B5 + C4*C5

由于决策变量单元格是空白，在EXCEL中转换成数值默认为0，所以回车之后，结果如下：

其中，SUMPRODUCT( )函数是规划求解中使用率最高的函数，必须深入理解和牢牢掌握。下面一一详细解释。

在公式SUMPRODUCT(B4:C4,B5:C5)中，

B4:C4是Excel中表示单元格范围的标准格式，可以用来表示多个单元格。范围是一个矩形区域，而冒号前面的单元格地址应当是这个矩形区域左上角单元格的地址，冒号后面的单元格地址应当是这个矩形区域右下角单元格的地址。例如：B6:E9表示如下单元格范围：

本题中的B4:C4是一个一行两列的向量，存放两种产品的生产数量，可称为生产数量向量，用 X 表示。

B5:C5也是一个一行两列的向量，存放两种产品的单位利润，可称为单位利润向量，用 A 表示。即：

A = [a₁ a₂] ， X = [x₁ x₂] ，显然总利润 

也就是说，如果两个参数都是形状相同的向量，SUMPRODUCT函数求出的是两个向量的内积。如果参数超过两个，SUMPRODUCT函数求出的给定的这些向量的内积。

如果给SUMPRODUCT的两个参数是形状相同的矩阵，那么求出的是两个矩阵的内积。不知道线性代数中是否有这个概念，但在EXCEL中就实现了这个功能，用数学公式表示如下：

假定矩阵 

 ，矩阵  

，那么：

熟悉线性规划的同学，一眼就可以看出右侧的式子正是常见的目标函数的形式。事实上在目标函数和约束条件的表达中，SUMPRODUCT函数确实是使用频率最高的函数。

第3步：逐一输入每个约束条件左侧的计算公式

在单元格D6中输入：=SUMPRODUCT($B$4:$C$4,B6:C6)

在单元格D7中输入：=SUMPRODUCT($B$4:$C$4,B7:C7)

在单元格D8中输入：=SUMPRODUCT($B$4:$C$4,B8:C8)

在单元格D9中输入：=SUMPRODUCT($B$4:$C$4,B9:C9)

如果你会使用拖拽方式来复制公式，那就只需要在D6单元格中输入公式后，将单元格D6右下角的拖放柄拖拽到单元格D9即可。这一步结束后，结果如下图所示：

第4步：启动规划求解模块，设置相关参数

首先，用鼠标在工具栏上单击“数据”，然后点最右侧的“规划求解”按钮，然后在弹出的对话框中设置目标函数所在的单元格是$E$4，目标函数是求最大值，决策变量所在的单元格为$B$4:$C$4。

接下来，单击规划求解对话框右侧的添加按钮来添加约束条件。

这是第一个约束条件，即设备A的实际加工时间不超过它的最大可用加工时间，输入完毕后，单击添加按钮，可以添加下一个约束。全部约束添加完毕后，单击确定按钮，结果如下：

勾选使无约束变量为非负数，即表示： 

 ，

 

选择求解方法为：单纯线性规划（由于软件中文化得不够专业，所以有些用词与运筹学专业术语有一定差别）。

第5步：求解

单击上图对话框中的求解按钮，即可得到如下结果：

单击确定按钮，即可得到最优解： x₁=4 ,x₂=2 时，目标函数取到最大值14元。也就是说甲产品生产4件，乙产品生产2件的时候，总利润最大，为14元。

四、总结

用EXCEL的规划求解模块可以轻松求解运筹学问题，步骤简单，求解快速。是运筹学初学者的优秀工具，也可以帮助运筹学高手解决比较复杂，规模相对较大的实际问题，只要决策变量不超过200个，约束条件不超过100个。

EXCEL的规划求解可以设置五种约束：不等式约束、等式约束、一般整数约束、0-1整数约束和互异整数约束。运用不等式约束和等式约束可以解决线性规划问题，而使用一般整数约束和0-1整数约束可以分别求解整数规划（包括混合整数规划）、0-1整数规划问题（例如背包问题和指派问题）。互异整数约束是Frontline Systems公司的一个创造，用来表达若干元素的全排列形成的所有方案，然后在这些方案中寻求最优解。

所以，规划求解模块是求解线性规划、整数规划和非线性规划的专业软件。该软件轻松易学，值得每个学习运筹学的初学者来学习，不论你是学习经济管理、交通运输、工程管理还是物流管理专业。该软件操作方便，求解迅速，也适合不同行业需要使用运筹学解决实际问题的技术人员来学习。