运用Excel 等软件求解线性规划模型的比较研究

2022-07-11罗云中宗胜亮

电子技术与软件工程 2022年7期

罗云中宗胜亮

（兰州大学管理学院甘肃省兰州市 730000）

管理运筹学是经济学类和管理学类专业一门十分重要的基础课程，通过对现实状况的观察总结基本规律后，以数学方法进行量化分析，通过建立数学模型和求解获得最优的解决方法，辅助决策者进行科学决策，在经济管理类专业的人才培养中占据十分重要的地位。线性规划是管理运筹学最重要的领域之一，它主要研究线性约束条件下线性目标函数的最大值和最小值问题。随着计算机技术的快速发展和应用，线性规划有了很多的求解模块和工具，使用这些软件能快速对线性规划问题进行求解。目前，求解线性规划问题的大型软件主要有Matlab、Mathematica 等；还有LINGO 和LINDO 等专业软件，能处理大型多变量的矩阵运算；EXCEL 也有模块（插件）来处理小型的线性规划问题；Python 作为最流行的开源脚本语言，也有scipy、pulp 和pyomo 等多个库可以用来求解线性规划问题。

1 线性规划模型及软件求解

本文以如下线性规划模型为例，该模型具有10 个决策变量8 个约束条件，分别运用Excel、Lingo 和Python 软件这三个工具进行求解。

1.1 Excel求解线性规划模型

1.1.1 Excel 规划求解介绍

Excel 规划求解模块是一款随Office 软件一同发行的规划求解模块（Solver），该模块免费使用不需要单独激活，内置有单纯型法、对偶单纯型法等算法，可以用来求解线性规划、整数规划和非线性规划问题。

1.1.2 加载规划求解模块

Excel 中封装的“规划求解”模块（插件）是可选安装，一般不会默认安装，在安装完office 之后，需要通过Excel选项--加载项的方式来安装规划求解模块，成功安装完毕后在数据菜单会出现规划求解插件，之后启动office 会自动加载规划求解模块。下面以Excel2016 版本为例，简单演示如何加载规划求解模块，其他版本office 加载方式类似。

第一步，打开文件菜单，点击左下角的选项按钮；

第二步，在Excel 选项对话框左侧选择加载项，右下管理选择Excel 加载项，点击右侧转到按钮；

第三步，在加载宏对话框勾选规划求解加载项，点击确定按钮；

第四步，安装成功后在数据菜单会新增分析选项卡，分析选项卡新增规划求解插件。

1.1.3 Excel 线性规划求解实现

（1）创建求解模板。

针对以上的线性规划模型，需要在EXCEL 中制作10 个决策变量和10 个约束条件的线性规划求解模板来求解这个模型。下面是模板制作步骤：

第1 步，在Excel 编辑区划分目标函数区，约束条件区和最优解区和最优值区四个功能区。

第2 步，在约束条件实际值M7：M16 中输入公式。M7单元格输入公式M7=SUMPRODUCT(C7:L7,$C$20:$L$20)，通过填充柄将公式填充到M16。

第3 步在最优值单元格N20 单元格录入计算公式

N20=SUMPRODUCT(C5:L5,C20:L20)。

第4 步设置规划求解参数对话框，完成模板制作。具体过程如图1 所示。

图1：线性规划求解模板

（2）输入数据模板求解。

第1 步，在制作好的线性规划模板中录入数学模型目标函数系数、约束条件系数和常数项数据。第2 步，通过规划求解插件设定规划求解参数。在规划求解参数对话框中，设置目标中输入$N$20，选择到最大值。可变单元格输入或者选择$C$20:$L$20。遵守约束按添加按钮，在添加约束对话框单元格引用中把$M$7：$M$11 单元格选定，约束条件选择小于等于，约束把$O$7：$O$11 单元格选定。勾选“使无约束变量为负数”单选框。求解方法选择“单纯线性规划”，选项保持默认。具体过程如图2 所示。

图2：规划求解参数

（3）求解结果。

点击求解按钮，后会弹出规划求解结果对话框提示找到一解可满足所有的约束及最优状况，报告中可以选择运算报告、敏感性报告和极限值报告单独输出或者一起输出，点确定按钮和选择运算结果报告后在求解模板中C20：L20 单元格会显示最优解，N20 单元格会显示最优值的求解结果。输出运算报告如图3 所示，由此可知，最优解（x1=1200,x2=0,x3=230.0493,x4=500,x5=324.1379,x6=0,x7=500,x8=858.6207,x9=324.1379,x10=5714.4286），最优值为1200.567。

图3：Excel 规划求解结果

1.2 Lingo求解线性规划模型

1.2.1 Lingo 软件简介

Lingo 是Linear Interactive and General Optimizer 的缩写。由美国Lindo 公司推出的一款专门用于求解运筹学优化问题的软件包。Lingo 软件除了能求解线性规划问题外，还能求解非线性规划问题、线性和非线性混合问题，功能十分强大。Lingo 软件内置了十几个内部函数，还能与Excel 和数据库等软件交换数据，运算执行速度快，操作方便简单。模型输入简练，约束条件右面可以包含变量，能最大程度满足数学模型的输入，通过引入集的方式，给决策变量下标形式更简单直观的方式表达模型，更容易理解和维护。整体上代码量较少，对编程能力要求不高，易于掌握。

1.2.2 Lingo 软件求解实现

（1） Lingo 程序基本语法规则。

Lingo 程序的使用主要规则如下：

以“MAX=”或者“MIN=”开始来定义目标函数是最大值或者最小值问题。

决策变量和系数之间用“*”代表“×”连接，为了美观之间使用空格。

变量名不区分大小写，但必须以字母开始，变量名使用有意义单词或词语，不超过32 个字符。

表达式用“;”表示结束。表达式过长时可以回车多行显示。

使用“<”来表示“小于等于”，用“>”表示“大于等于”。

数据段以DATA:开始，以ENDDATA 表示数据段结束。

（2）求解具体实现过程。

对以上线性规划模型的Lingo 软件求解具体实现过程，在Lingo Model 界面中输入以下内容：

点击slove 按钮求解后，得到Solver Status 和Solution Report 两个弹出框，目标值和迭代次数等信息。如图4 所示的结果报告给出的信息比较详细，提示找到了全局最优解、目标值、变量、变量值以及灵敏度分析的Reduced Cost（表示当变量有微小变动时, 目标函数的变化率）、Slack or Surplus（松弛量或剩余量，常数项有剩余的资源）、Dual Price（对偶价格，维持最优解不变的情况下，常数项每增加一个单位而使最优目标函数得到改进的数量）。

图4：LINGO 求解结果

从Lingo 对此线性规划模型求解报告得到结果最优值为1200.567，最优解为（X1=1200，X2=0，X3=230.0493，X4=500，X5=324.1379，X6=0，X7=500，X8=858.6207，X9=324.1379，X10=571.4286）。

1.3 Python求解线性规划模型

1.3.1 Python 简介

Python 是由荷兰人Guido van Rossum 于1989 年圣诞节开发的一门解释性脚本语言。它继承了传统编译语言的强大性和通用性，同时有借鉴了脚本语言和解释语言的易用性。它的特点是只有较少的关键字，结构简单；代码定义清晰；源代码容易维护；拥有丰富的标准库和第三方模块；跨平台兼容性好；能在互动模式下支持代码的输入和调试；可扩展性强，可以使用C 或C++完成部分程序，在Python 程序中调用；支持GUI 编程；可以将Python 嵌入到C/C++程序等。Python 广泛应用于工业界和学术界，在web 网络开发、自动化运维、网络爬虫和图像图形处理得到很多运用，得益于大数据分析、机器学习和人工智能应用的迅速发展和全民大数据理念接受，Python 语言得到飞速的发展，在TIOBE 的语言排行榜上已经是第一名，超过JAVA、C 和C++语言。

1.3.2 Python 求解线性规划问题的实现

Python 中能求解线性规划的库比较多，有Scipy 库、Pulp 库和Pyomo 库等，本文介绍Scipy 库来求解线性规划模型。要运行python 程序首先需要安装python 解释器，可以安装python 官方的IDLE，也可以安装Anaconda 或者Pycharm 解释器，本文作者推荐使用Anaconda 解释器，该解释器内置了Python 基本库和依赖包，初学者不用学习安装各种依赖包。

在Anaconda 中求解线性规划问题，第1 步，需要引入Scipy 库的和numpy 库。第2 步，因为该库只能求解最小化问题和约束关系小于等于的约束条件，需要将目标函数乘以-1 转化为求最小化问题，将大于等于约束条件两边乘以-1转化为小于等于。第3 步，把目标函数系数组合成一维数组C，每个决策变量系数用“,”分开；将每个约束条件组成的一维数组组合成二维数组A，约束条件也用“,”分开；每个约束条件常数项组成一维数组b；约定决策变量的取值范围。第4 步，调用optimize 模块的linprog 方法并求解。第5步输出模型结果。

Scipy 求解线性规划的具体代码实现如下：

即最优值为1200.56，最优解为（X1=1200，X2=0，X3=230.0493，X4=500，X5=324.1379，X6=0，X7=500，X8=858.6207，X9=324.1379，X10=571.4286）

2 结果分析

通过运用Excel 软件的运筹规划模块、Lingo 软件和Python 语言的Scipy 模块这三种不同方法对线性规划模型进行求解，计算的最优值和最优解都是一样，但求解过程和操作还存在一定的差别，现对这三种方法处理线性规划模型进行对比分析。

2.1 Excel规划求解模块优点和不足

Excel 规划求解模块，操作通过菜单操作，可视化程度较高，步骤简单，使用难度最低。Office 平台广泛，几乎所有台式电脑都安装有Office，只需要通过加载的方式就能运行规划求解程序，不用专门下载软件，可以求解线性规划问题、整数规划（包括混合整数规划）、0-1 整数规划问题和非线性规划问题，使用方式能最便捷。但Excel 规划求解以行或者列代表决策变量，当线性规划模型中有较多变量时操作不方便，在输入约束条件和目标函数时容易出现错误。结果输出时中文翻译也不太贴切，初学者或者偶尔使用时不容易明白结果真正意义，造成误解。Excel 规划求解模块在决策变量数量、约束条件数量和求解时间上有限制，最多200个决策变量和包括变量上下界约束在内最多100 个约束条件，求解时间也不能超过30 秒，这些因素了Excel 规划求解模块不能处理大型的实际问题。

2.2 Lingo软件优点和不足

Lingo 软件结构形式简单，符合线性规划模型的书写习惯，容易阅读、了解和修改。可以从Excel 或数据库读取数据计算，结果也可以输出到Excel 或者数据库。Lingo 求解器十分强大，会根据模型自动选择线性、非线性或整数优化算法。但Lingo 软件需要单独安装，试用版本决策变量只有30 个，软件购买费用较高，对于高校教学来说是一笔巨大的投入。使用过程还是需要一定的代码编写，对编程零基础的学生来说有存在一定的挑战。

2.3 Python的优点和不足

Python 语言是免费开源的程序，变量数量和约束条件数量均无限制，可以处理特别巨大的线性规划模型，并且自带的Python 编程语言能灵活处理复杂的数据输入等功能，但scipy 的linprog 模块只能处理最小化问题，输出结果可读性也较差。

总体来说，这三个工具都能很好地求解线性规划模型，但相比而言，Lingo 软件在处理线性规划模型时更加专业，输出结果也更加合理，可读性更强；Python 更适合于大型或者巨型线性规划模型；EXCEL 插件适用于较小的线性规划模型使用，但优点是不需要特别安装，也不需要有编程基础都能顺利将线性规划模型求解出来。