运筹学中excel的运用(用excel解决线性规划、动态规划、排队论等问题)

EXCEL在运筹学规划论教学中的应用作者：于瑛英来源：《教育教学论坛》2014年第10期摘要：运筹学作为一门应用学科，其实验教学逐渐引起重视。

近年来，在教学中使用软件求解运筹学问题已经成为趋势。

鉴于EXCEL应用的广泛性，该文介绍使用EXCEL软件求解运筹学中规划论模型的方法，并详细给出了如何使用EXCEL软件求解线性规划、整数规划、目标规划和动态规划模型。

关键词：运筹学；规划论；EXCEL软件中图分类号G642.4 文献标志码：A 文章编号：1674-9324（2014）10-0278-03一、引言运筹学是一门应用科学，可以为决策者选择最优决策提供定量依据。

运筹学经过多年的发展已经成为体系，包括规划论（线性规划、整数规划、目标规划、动态规划和非线性规划）、图论与网络、排队论、存储论、对策论和决策论等[1]。

传统的运筹学主要是以讲授理论为主，尤其是比较枯燥的数学理论。

近年来，运筹学改革不断提高其应用性，减少枯燥的理论。

此外，随着运筹学计算机支撑技术的迅速发展，运筹学应用得到极大的推动，运筹学实验教学提上日程，因此开设运筹学的实验课程势在必行。

秦必瑜[2]和石磊[3]在运筹学的课程改革中都提出要增加软件应用。

我院运筹学教学团队多年致力于运筹学的教改研究，在提出应用软件的基础上，进一步开设了除理论课程外的专门实践课程，将理论课上学习到的内容使用软件来进行求解。

国内运筹学的实验教学已经有很大进展，目前运筹学经常使用的软件主要有lingo[4][5]、WinQSB[6]、MATLAB[7]等。

近年来，美国高校运筹学（管理科学）的思想、内容、方法和手段发生根本转变，开始使用“电子表格”这一全新的教学方法。

在运筹学中使用EXCEL已经成为运筹学教学的一个新潮流。

EXCEL软件使用方便，不需要重新安装和学习新软件的使用方法，一般的PC机上都安装有EXCEL软件，因此使用方便、应用广泛。

但是目前将EXCEL 在运筹学中的应用并不多，李雪虎[8]给出用EXCEL求解运输问题和网络最优化问题的例子；魏杰羽[9]阐述了用EXCEl求解运输问题的过程；而张辉[10]给出了使用EXCEL求解线性规划问题的例子。

利用Excel求解线性规划问题线性规划问题的求解有很多方法，也有很多工具。

比如常用的Matlab、Lingo，记得参加数学建模的时候就是用的Lingo解决线性规划问题的。

本文主要讲解如何使用Excel求解线性规划问题，Excel本身是没有计算线性规划问题能力的，因此我们首先要加载相应的宏定义。

一、加载宏定义（不同版本的加载方式有所不同）：Excel 2003：单击“工具”菜单，然后单击“加载宏”，选择“规划求解”点击确定。

Excel 2007：方法一：用快捷键。

先按Alt+T，再按I键，即可打开加载宏对话框。

方法二：单击“Office按钮→Excel 选项→加载项”，确保“管理”右侧下拉列表中的选项是“Excel 加载项”，单击“转到”按钮即可。

Excel 2010：直接在功能区中选择“开发工具”选项卡，在“加载项”组中单击“加载项”命令，选择“规划求解”点击确定。

注意：如果功能区中没有“开发工具”选项卡，可以通过自定义功能区来显示“开发工具”选项卡：单击“文件→选项→自定义功能区”，然后在右侧区域中勾选“开发工具”并单击“确定”。

二、初始化数据（以Excel 2010为例，其他版本大同小异）：比如我们要计算的线性规划问题如下：那么，我们可以构造如下的表格数据。

其中，B2:F2为待求的值Xi，B3:F3为目标函数的系数，B4:F4、B5:F5、B6:F6为约束条件的系数。

在G3单元格中输入公式=$B$2*B3+$C$2*C3+$D$2*D3+$E$2*E3+$F$2*F3，并将鼠标放到单元格的右下角会变成黑色十字架，向下拖拽复制单元格公式到G4、G5、G6单元格。

然后，单击“数据”选项卡，单击“规划求解”打开“规划求解参数”对话框。

∙修改“设置目标”为$G$3,即最优解下目标函数的值z所在的单元格。

∙选择是求最大值，还是最小值。

∙“可变单元格”指的是最优解取值变量所在的单元格。

∙“遵守约束”指的是约束条件中对各变量的约束情况。

实验三、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。

Excel最多可解200个变量、600个约束条件的问题。

下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。

一、实验目的1、掌握如何建立线性规划模型。

2、掌握用Excel求解线性规划模型的方法。

3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。

4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。

二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。

在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。

使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。

图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。

如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。

图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。

图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。

excel求解线性规划Excel是一种常用的电子表格软件，可以用于求解线性规划问题。

线性规划是一种数学优化问题，目标是找到一组决策变量的最优值，使得目标函数达到最大或最小值，并满足一系列约束。

下面将介绍如何使用Excel求解线性规划问题。

第一步是建立模型。

线性规划模型由目标函数和约束条件组成。

目标函数是需要最大化或最小化的线性函数，约束条件是决策变量需要满足的限制条件。

在Excel中，可以在一个工作表中设置一个单元格来表示目标函数，并使用其他单元格来表示约束条件。

第二步是确定决策变量和其范围。

决策变量是需要优化的变量，其范围通常是非负数。

在Excel中，可以使用单元格来表示决策变量，并设置其边界条件。

第三步是设置约束条件。

约束条件通常是一组线性不等式或等式。

在Excel中，可以使用单元格和公式来表示约束条件，并使用Excel内置的函数来计算约束条件的结果。

第四步是设置目标函数。

在Excel中，可以使用单元格和公式来表示目标函数，并使用Excel内置的函数来计算目标函数的结果。

第五步是求解线性规划问题。

在Excel中，可以使用ExcelSolver插件来求解线性规划问题。

Solver插件是一个用于求解最优化问题的工具，可以根据设置的目标函数和约束条件自动计算最优解。

可以在Excel的“数据”选项卡中找到Solver插件，并按照提示设置目标函数、约束条件和决策变量的范围，然后点击求解按钮进行计算。

最后，根据Solver求解结果，可以在Excel中找到最优解和目标函数的最优值。

总之，Excel是一种强大的工具，可以用于求解线性规划问题。

只需要将线性规划问题转化为Excel中的单元格和公式表示，然后使用Solver插件进行求解，就可以得到最优解和目标函数的最优值。

通过Excel求解线性规划问题，可以提高计算效率和准确性，帮助决策者进行决策分析和优化。

Excel求解运筹学问题方法简介Excel中的规划求解是功能强大的优化和资源配置工具。

它可以帮助人们求解运筹学中的许多问题，特别是“规划求解”模块可以解决许多求极值、解方程的问题。

本附件除介绍“规划求解”模块的使用外，还提供给读者“排队论”与“存储论”基本模块。

1 规划求解在使用“规划求解”时，首先需要“规划求解”出现在“工具”菜单中，如果没有，则需要加载“规划求解”宏。

另外，目标函数和约束函数必须要给出公式，变量的约束必须作为约束条件给出。

规划求解的特点：◆表格输入数据不能为分数，当遇到分数时，必须化为小数输入。

◆目标单元格依赖一组单元格（可变单元格），或通过公式间接依赖于可变单元格，规划求解可调整这组单元格来影响目标单元格。

◆目标单元格服从一定的约束和限制。

约束条件不同，结果就不同。

◆可求解特定单元格的最大值或最小值或某个值。

◆对一个问题可以求出多个解。

1.1加载“规划求解”模块首先，打开Excel文件，进入表格界面，单击“工具（T）”，如果存在“规划求解”项目，说明已经加载（加载只需进行一次，以后如果不人为删除，就会保留在工具栏内），可直接使用。

图1-1“加载宏”图如果不存在“规划求解”项目，单击“加载宏”，会出现如图1-1所示“加载宏”图框。

单击“规划求解”，使复选框中出现对勾，再单击“确定”，即完成了加载（注：若在Office软件装入时，系统未选择该工具模块装入，此时会引导读者插入软件安装盘，依据系统提示操作即可）。

1.2 线性规划问题求解为了便于说明，以一个线性规划例题来说明这个过程。

例1-1 某工厂在计划期内要安排甲、乙两种产品的生产，已知生产单位产品所需成本分别为2千元和3千元；根据产品特性，产品总数不得少于350件，产品甲不得少于125件；又知生产这两种产品需要某种钢材，产品甲、乙每件分别需要钢材2t 、1t ，钢材的供应量限制在600t 。

问题：工厂应分别生产多少单位甲、乙产品才能使总成本最低？解： 容易建立如下线性规划模型。

利用Excel中的加载宏新加入的规划求解功能可以方便的解决线性规划问题。

下面是详细步骤：
（1）打开Excel，单击“工具”弹出菜单，然后单击“加载宏”会出现如下画面：
选择“规划求解”点击确定，这样你的Excel就有了能解决线性规划问题的功能。

（2）依次输入以下数据作为准备工作，如下图：
（3）然后在表中选中“G3”位置如下图
输入以下公式“=$B$2*B3+$C$2*C3+$D$2*D3+$E$2*E3+$F$2*F3”（输入公式时必须在英文输入状态），然后回车即可。

（4）选中“G3”位置可以看到公式了，则用填充柄拖动让G4,G5,G6都相应填上公式
（5）再次选中“G3”点击“工具”----“规划求解”，出现下图：
根据题意选择目标单元格为“$G$3”，等于最小值，可变单元格为“$B$2:$C$2:$D$2:$E$2:$F$2”。

然后点击“添加”添加约束条件。

一共有八个约束条件要加入。

下图为其中之一：
（6）添加完约束条件后的图片是：
（7）如上图点击“求解”即可得到结果，如下图：。

利用excel 求解线性规划问题“规划求解”示例例1 美佳公司计划制造Ⅰ、Ⅱ两种家电产品。

已知各制造一件时分别占用的设备A ，B 的台时、调试工序时间及每天可用于这两种家电的能力、各售出一件时的获利情况，如下表所示。

问该公司应制造两种家电各多少件，使获取的利润为最大。

1.建立数学模型2. 打开excel ，输入下列数据。

3、如何在工作表中设置问题条件？先设置目标单元格，即最大利润，把它放在E1单元格上，可变单元格放置计划生产Ⅰ和Ⅱ产品的件数，这里把它放在C10：D10区域。

F4：F6是约束单元格，要对它们的值进行约束。

单击E1，在编辑框输入如图所示的公式。

注意，表示绝对引用的美元符号，可以单击F4功能键添加。

⎪⎪⎩⎪⎪⎨⎧>=<=+<=+<=+=0,52426155..2max 212121221x x x x x x x t s x x z4、单击E4单击格式，在编辑栏上输入公式：=$C$4*$C$10+$D$4*$D$10。

绝对引用单元格有一个好处，显示的单元格位置变化时，引用的数据没改变。

5、单击E5单击格式，在编辑栏上输入公式：=$C$5*$C$10+$D$5*$D$10。

6、单击E6单击格式，在编辑栏上输入公式：=$C$6*$C$10+$D$6*$D$10。

7、如何使用规划求解功能？单击工具菜单，如果看不到规划求解选项不要慌，先选加载宏。

然后勾选规划求解，确定单击数据菜单——点击“模拟分析”——8、单击“规划求解”：指定目标单元格。

一种方法是先选中目标单元格E1，单击工具---规划求解。

另一种先单击工具---规划求解，再输入目标单元格名称。

输入可变单元格区域。

比较快的方法是，单击折叠框，用鼠标选中可变单元格区域：$C$11：$E$11。

注意勾选最大值哦。

设置目标： $E$1；点选“最大值”；设置：可变单元: $C$10：$D$109.设置条件不等式。

单击添加，单击折叠框，选择单元格和不等号，单击关闭窗口，接着添加另一个条件。