如何利用Ｅｘｃｅｌ软件求解运筹学模型

摘要：随着科学技术和生产的发展，运筹学已渗透到人们工作生活的很多领域里，发挥了越来越重要的作用，但其烦琐、复杂的计算过程，令许多专业技术人员及在校学生望而生畏。文章通过阐述Excel软件辅助求解运筹学模型的优点，及求解的具体步骤，并以运输模型为例具体进行了演示。

关键词：运筹学；Excel软件；运筹学模型

中图分类号：TP391.1文献标识码：A

文章编号：1674-1145（2009）11-0144-02

《运筹学》是20世纪40年代前后发展起来的一门新兴应用学科，是利用应用分析、量化的方法，对所研究系统中有限资源合理分配，为决策者提供有依据的最优方案。

运筹学学习多围绕着学习线性规划、整数规划、运输模型、图论、决策论、排队论、存贮论等数学模型而展开的，线性规划及其解法——单纯形法的出现，对运筹学的发展起了重大的推动作用。许多实际问题都可以化成线性规划来解决，而单纯形法也是一个行之有效的算法，但计算烦琐、过程复杂是单纯形法的特点，很多人误认为《运筹学》的学习是漫长而复杂的手工计算过程。

计算机的出现，使一些大型复杂的实际问题的解决成为现实。事实上，《运筹学》的发展与计算机技术的发展有着密切联系，在没有使用计算机技术之前，模型求解过程相当复杂，一些复杂的模型几乎无法用人工方法求解，这也直接影响到运筹定量技术的推广与使用。只有借助计算机高效、便捷的计算功能，《运筹学》在实践中的应用才成为可能。而为了满足不同层次的需求，专业人士也开发研制了多种运筹学软件。如由美国芝加哥大学的L.E.Schrage教授开发的专门用于求解数学规划问题的Lindo软件包；“管理运筹学”2.0是由韩伯棠教授主编的《管理运筹学》教材中的随书软件；而Excel软件中的规划求解命令的根本就是用运筹学的线性规划原理解决实际问题。

一、利用Excel软件求解运筹学模型的优点

与专门的计算软件相比，Excel具有如下特点：

1．Excel软件易得易学、便于掌握。虽然专业的运筹学软件模型运算、分析功能强大，但较强专业性、复杂的操作界面以及昂贵的版权费用制约着这些软件的普及，相比之下，作为Office办公软件下的常用软件Excel软件较易获得。Excel提供的“规划求解”命令和分析工具接合电子表格具备较强的规划求解功能，且Excel 普及较广，大都数学生和技术人员都具备一定的Excel应用能力。

2．通用的表格计算。在建模过程中，不同类型的运筹学模型处理的变量间的表现形式有所不同，如建立生产计划问题等线性规划问题时，使用线性方程组形式来表现变量间的关系；运输问题则使用运输表展现产地、销地间的产销数量关系等等，当模型较大时，编制这些方程或表格的工作量比较大。而Excel软件具有强大的表格处理功能，能在工作表中简单直观地体现各类运筹学模型。

3．Excel具有大量内建函数、公式，除数学函数、逻辑函数等常用函数外，可以利用Excel提供的各种函数公式，通过确定参数的数值，按特定的数量关系执行复杂计算，大大简化了计算模型建立的过程，并有助于提高模型的准确性。

4．Excel具有自动重算能力。模拟模型建立以后，只要修改单元格中的数值，工作表中所有键入了与此单元格有关的公式就会被重新计算，并在相应单元格中显示出新的计算结果，我们可以在模型中一边对代表特定参数单元格中的数值进行修改，一边观察各种变量的数值变化情况，十分直观。

5．Excel软件具有强大的数据分析功能。Excel软件还可以根据实际需要生成运算报告、敏感性报告和极限值报告，详细统计分析在实际状况约束下最优的解决方案以及相应资源消耗情况，为了解并掌握复杂的运筹学模型的实际应用带来了极大的便利。

二、使用Excel软件求解运筹学模型的步骤

《运筹学》的各个分支虽然特点及解决方法不尽相同，但是各个分支基本上都可以归为线性规划问题，而使用Excel软件求解线性规划问题，基本步骤可归纳如下：

1．建立相应的数学模型。一般来说，一个运筹学的数学模型由决策变量、约束条件、目标函数三个要素组成，其中，决策变量是一些未知量，一般用x1，x2，…，xn来表示，若每个变量都取定一个数值，这些数值构成的集合就代表实际问题的一个决策方案。实际问题的决策方案往往受到某些现实条件的约束，这些关于决策变量的数学等式或不等式就称为约束条件。在决策人员最终做出决策之前，总会有一个预先期望达到的目标，如效益最大化或成本最小化，这种目标往往可以表示成关于决策变量的线性函数，称为目标函数，一般由Z来表示。因此，一般的运筹学模型用线性规划的形式表示如下：

n

opt Z =∑cj xj

j=1

n

∑aij xj ≥（≤ or =）bi

s.t.j=1

xj≥（≤）0 orfree（j=1,2,….n）

其中，opt Z表示为目标函数的优化目标，根据实际问题不同，可表示最大化或最小化。aij、cj、bi称为运筹学模型的参数，它们对于具体运筹学模型都是常数。

2．Excel软件求解运筹学模型。把已建立的运筹学数学模型，输入到Excel软件新建的工作表中，使决策变量、约束条件、目标函数在表中依次体现。利用Excel软件的规划求解工具确定最优的决策方案及最优目标函数值。

三、使用Excel软件求解运筹学模型的实例

某部门有3个生产同类产品的工厂（产地），生产的产品有4个销售点（销地）出售，各工厂的生产量、各销售点的销售量（假定单位为t）以及各工厂到各销售点的单位运价（元/t）示于表1中，要求研究产品如何调运才能使总运费最小？

则目标函数为：

Min Z=4X11+12X12+4X13+11X14+2X21+10X22+3X23+9X24+8X31+

5X32+11X33+6X44

同时，需满足以下约束条件:

（1）X11+X12+X13+X14≤16

（2）X21+X22+X23+X24≤10

（3）X31+X32+X33+X34≤22

（4）X11+X21+X31=8

（5）X12+X22+X32=14

（6）X13+X23+X33=12

（7）X14+X24+X34=14

（8）所有变量为非负数

采用Excel处理的步骤为：

第一步：在工作表的顶部输入数据。

第二步：输入模型部分（包括决策变量、目标函数、约束条件）。

（1）确定每个决策变量所对应的单元格的位置。

（2）选择某一单元格内输入目标函数的公式。（F15）

（3）选一个单元格输入公式,计算每个约束条件左边的值。

（4）选一个单元格输入公式,计算每个约束条件右边的值。

第三步：求最优解。

（1）安装“规划求解”工具。在“当前加载宏”的复选框中选中“规划求解”,单击“确定”按钮后返回，Excel“工具”菜单中就出现了“规划求解”选项。

（2）选择“工具”菜单。

（3）选择“规划求解”选项。

（4）在“规划求解参数”中设置参数。

（5）“选项”中选择“线性规划”和“假定非负”,单击“求解”。

（6）选择“保存”。

运算结果如图所示，此时运费最小，为244元。但这实际只是多个最优方案中的一个，因此，Excel进行运筹学模型处理虽然具有方便、简单的特点，但由于Excel不是处理运筹规划模型的专业软件，当模型有多个最优解时，往往只能显示其中一个结果，这是其局限性。

参考文献

[1]韩伯棠．管理运筹学(第2版)[M]．北京：高等教育出版社，2005．

[2]胡运权．运筹学教程[M]．北京：清华大学出版社，1998．

[3]唐永洪．基于物流运筹学的运输优化决策问题解决方案.2008．