运筹学中excel的运用(用excel解决线性规划、动态规划、排队论等问题)
运筹学数学excel操作实例
根据对上述建模过程的总结，在电子表格中建立线性规划模型的步骤可归纳如下：
回忆例2-1某制药厂的生产计划问题，其求解结果如图13-8所示，即生产4公斤药品Ⅰ和2公斤药品Ⅱ，总利润为1400元.但该最优解是在假设所有的模型参数都准确的前提下做出的，在此基础上，管理层如果进一步考虑下列问题：
图13-11右下部分的“规划求解”对话框显示了求解时应注意的问题：求目标单元格的最大值（利润最大）；约束为设备的实际使用时间小于等于设备的可用时间及实际总业务量小于等于总业务提供量的限制.
打开“选项”对话框，仍选择“采用线性模型”和“假定非负”，回到“规划求解”并按“求解”按钮，得到问题的最优方案为：每月X线及CT检查的业务量分别为1320人次和480人次，磁共振业务量为0，即不必购买该设备；按最优方案安排业务每月可获利55200元.
图13-10的右半部分显示了“规划求解”对话框及“选项”对话框的内容.该问题的目标是所用的胶管原料的总根数最少，因此设置目标单元格为I12等于最小值.由于实际获得的材料数量必须满足需求量的要求，考虑到最优方案（各种截法的某一组合）不一定能使截出的三种材料数量恰好等于需要的数量，而某种材料超过需求量是允许的，故在添加约束时可设置实际截得的数量大于等于需求量，即I9：I12>=K9：K12（本题中，该约束取“>=”和“=”的结果是相同的）；又由于截出的各种材料数量均为整数，因此约束中应包括决策变量取整数的限制，即C13:H13=整数.
EXCEL求解线性规划问题
约束右端值降低15时，目旳函数值旳变化量。
解：（1）最优解为x1=0, x2=12.4, x3=9.5
（2） x1旳目旳系数降低5，占允许降低旳百分比=5/∞=0%，x2 旳目旳系数增长4,占允许增长旳百分比=4/7.8=51.2%。
变化旳百分比和为51.2%，没有超出100%，所以最优解不变。
（3）第一资源约束右端值增长30，占允许增长旳30 /∞=0%， 第二资源约束右端值增长4 ，占允许增长旳4/15=26.7%，
•初值和终值分别指 单元格在此次求解 前旳数值和求解后 旳数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格旳地址 • “名字”是决策变量旳名称 • “终值”是决策变量旳终值,即最优值 • “递减成本”指最优解中档于0旳变量，相应旳目旳函数中旳系数
增长或降低多少，最优解不再为0 • “目旳式系数”目旳函数中旳系数，为已知条件 • “允许旳增量”与“允许旳减量”表达目旳函数中旳系数在增量
（1）引用旳类型
三种类型 ：
相对引用、 绝对引用、混合引用
（2） 相对引用
格式： A3 、B6
使用相对引用后，系统将会记住建立公式旳单元格和被 引用旳单元格旳相对位置，在复制这个公式时，新旳公式单 元和被引用旳单元依然保持这种相对位置。
（3）绝对引用 格式：$a$3 $d$5
绝对引用是指被引用旳单元与引用旳公式单元旳位置 关系是绝正确，不论将这个公式复制到任何单元，公式所 引用旳还是原来单元格旳数据。
2) 在弹出旳对话框中旳“可用加载宏”列表框 中,选定待添加旳加载宏“规划求解”选项旁 旳复选框,然后单击“拟定”.单击“拟定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
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求解线性规划问题
利用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求解线性规划问题演示文档.ppt
31
（2）约束右端值b同时变动的百分之百法则： 同时改变几个或所有函数约束的约束右端值，如果这些变动的幅 度不大，那么可以用影子价格预测变动产生的影响。为了判别这些 变动的幅度是否允许，计算每一变动占同方向可容许变动范围的百 分比，如果所有的百分比之和不超过百分之一百，那么影子价格还 是有效的；如果所有的百分比之和超过百分之一百，那就无法确定 影子价格是否有效。
个可变单元格可以取到的最小值。上限是在这种情况下可以取
到的最大值。
..........
30
延伸
下面对目标系数同时变动以及约束右端值同时变动的情况分别作延伸。 （1）目标系数c同时变动的百分之百法则： 如果目标函数系数同时变动，计算出每一系数变动量占该系数同方向 可容许变动范围的百分比，而后将各个系数的变动百分比相加，如果 所得的和不超过百分之一百，最优解不会改变；如果超过百分之一百， 则不能确定最优解是否改变。
..........
16
建立数学公式（步骤二）
• 在工作表的顶部输入数据
• 确定每个决策变量所对应 的单元格位置
• 选择单元格输入公式，找 到目标函数的值
• 选择一个单元格输入公式， 计算每个约束条件左边的 值
• 选择一个单元格输入公式， 计算每个约束条件右边的 值
图中，规定B12、C12 为可变单元格
在目标单元格中，需要填入......计.... 算目标函数值的公式。 18
建立数学公式（步骤四）
• 在工作表的顶部输入数据 • 确定每个决策变量所对应
的单元格位置 • 选择单元格输入公式，找
到目标函数的值 • 确定约束单元格输入公式，
计算每个约束条件左边的 值 • 确定约束单元格输入公式， 计算每个约束条件右边的 值
EXCEL在运筹学规划论教学中的应用
采用Ｅ Ｘ Ｃ Ｅ ｂ Ｊ ￣ 解该问题包括以下步骤 ： 第一步 ： 模型输入 １ ． 在Ｅ Ｘ Ｃ Ｅ Ｌ 表格 中输入数据 ， 输入 目标 函数 的系数和 约束条件 的系数 ２ ． 标识数据 ， 可以用不同颜色标识不 同类型的数据 ３ ． 计算 中间数据， 数据 、 公式分离 ， 显示出完整模型 第二步 ： 模型求解 １ ． 安装 “ 规划求解” 工具。在“ 工具” 中选择“ 加载宏” ， 选
关键词 ： 运筹 学； 规划－ ￣； Ｅ Ｘ Ｃ Ｅ Ｌ ｌ ｅ ｄ ， ． 件
中图分类号Ｇ６ ４ ２ ． ４
一
文献标 志码 ： Ａ‘
文章编号 ： １ ６ ７ ４ — ９ ３ ２ ４ （ ２ ０ １ ４ ） １ ０ — ０ ２ ７ ８ — ０ ３
、
引 言
运筹学是一门应用科学 ，可以为决策者选择最优决策 提供定量依据。 运筹学经过多年的发展已经成为体系 ， 包括 规划论（ 线性规划、 整数规划、 目标规划 、 动态规划和非线性 规划 ） 、 图论与网络 、 排队论 、 存储论 、 对策论 和决策论等【 】 １ 。
论 的 内容 中。运筹 学 规划 论包 括线 性规 划 、 整 数规 划 、 目 标
（ 一） 使用Ｅ Ｘ Ｃ Ｅ Ｌ Ｓ ￣ 解线性规划模 型
Ｍａ ｘ ｚ ＝ ２ ｘ Ｈ ＋３ ｘ １ ２
对于如下线性规划问题 ， 模型１
１ ＋ ２ ｘ ２ ８ ４ ｘ １ ≤ １ ６ ４ ｘ ， ≤ １ ６ ｘ １ ， ｘ ２ ＞０ ￣
传统的运筹学主要是以讲授理论为主，尤其是 比较枯燥 的 数学理论。近年来 ， 运筹学改革不断提高其应用性 ， 减少枯 燥 的理论。此外 ， 随着运筹学计算机支撑技术的迅速发展 ， 运筹学应用得到极大的推动 ， 运筹学实验教学提上 日程 ， 因 此开设运筹学的实验课程势在必行。秦必瑜［ ２ １ 和石磊【 在运 筹学的课程改革中都提出要增加软件应用 。我院运筹学教 学 团队多年致力于运筹学的教改研究，在提出应用软件的 基础上 ， 进一步开设了除理论课程外 的专门实践课程 ， 将理 论课上学习到的内容使用软件来进行求解 。 国内运筹学的实验教学 已经有很大进展 ，目前运筹学 经常使用的软件主要有ｌ ｉ ｎ ｇ ｏ ［ ４ １ ５ ］ 、 Ｗｉ ｎ Ｑ Ｓ Ｂ ｔ ￣ 、 Ｍ Ａ Ｔ Ｌ Ａ Ｂ ｍ 等。近 年来 ， 美国高校运筹学（ 管理科学 ） 的思想 、 内容 、 方法和手 段发生根本转变 ， 开始使用“ 电子表格” 这一全新 的教学方 法 。在运筹学中使用Ｅ Ｘ Ｃ Ｅ Ｌ 已经成为运筹学教学的一个新 潮 流。Ｅ Ｘ Ｃ Ｅ Ｌ 软件使用方便 ， 不需要重新安装 和学习新软 件的使用方法 ， 一般 的Ｐ Ｃ 机上都安装有Ｅ Ｘ Ｃ Ｅ Ｌ 软件 ， 因此 使用方便 、 应用广泛。但是 目前将Ｅ Ｘ Ｃ Ｅ Ｌ 在运筹学 中的应 用并不 多 ， 李雪 虎阎 给出用Ｅ Ｘ Ｃ Ｅ Ｌ 求解运输 问题 和网络最 优化问题的例子； 魏杰羽啡目 述 了用Ｅ Ｘ Ｃ Ｅ ｌ 求解运输问题的 过程 ； 而张辉［ ０ １ 给出了使用Ｅ Ｘ Ｃ Ｅ Ｌ 求解 线性规划问题的例 子。在运筹学 的体 系中， 内容远远不止这些 ， 即使规划论的 内容也不止这些。本文 中探讨将Ｅ Ｘ Ｃ Ｅ Ｌ ￣用于运筹学规划
Excel在求解线性规划问题中的应用
最优解。 ３“ ．规划求解” 加载宏方法 求解线性规划 问题 的另一种方法是 “ 规划 求解” 加载宏 的方 法 ， 该 方 法 简单 、 方便 、 捷 ， 大 提 高 了计 算 的 效 率 和 准确 性 ， 直 接 得 到 最 快 大 能
单 纯形法［ 图解法 仅仅适用于含有 两个决 策变量的规划 问题 ， Ｊ Ｊ ， 而单纯 形法适用 于含有三个及其三个 以上决策变量 的线性规划问题 ，应用范 围更广 ， 但应用单纯形法时 ， 涉及的计算量大 。 为了减少计算量 ， 本文一 方 面借助 Ｅ ｃｌ ｘｅ 软件的表格和计算功 能， 现 了单纯形法 ； 一方 面应 实 另 用“ 规划求解” 加载宏 的方法直接求解线性规划问题 ， 得到最优解 。
１引 言 ．
线性 规划 问题 属 于 运 筹 学 的 一 个 分 支 ， 在 经 济 学 、 理 学 以及 生 它 管 产 生 活 中有 着 广 泛 的应 用 。求 解 线 性 规 划 问 题 的 方 法 主 要 有 图解 法 和
为 １ 所 在列的其他元 素变为 Ｏ ， ５ ，得到的新矩 阵再 反映在新单纯形 表 中 ， 根据检验数计算公式应用 ｓｍ ｒｄ ｃ 函数计算检验数 ， 并 ｕ ｐｏｕ ｔ 得到的新 单纯形表 如表 ２所示。
ｌ墓 Ｉ ｂ ０ ｆ翱 Ｉ ９ ０ ｌ ｌ ８
Ｏｊ
Ｘ ｌ ３
１ Ｏ
Ｘ ２ ４ ２
５
Ｘ ３ １ Ｏ
０
Ｘ ｔ ０ ｌ
Ｏ
在这 个初始单 纯形表 中， 据单纯形法 ， Ｅ ｃｌ Ｄ 根 在 ｘｅ 的 ５单元格 中 输 人计 算公式 “ Ｄ — ｕ Ｐ ０ ｕ Ｔ ￥ ￥ ： ￥，３ ４” ＝ Ｉｓ Ｍ Ｒ Ｄ ｃ （Ａ ３ Ａ ４ ： ）， ￥ Ｄ Ｄ 可得决策 变量 Ｘ ， 的检验数 ， 复制 Ｄ ５单元 格 ， 别粘贴在 Ｅ ，５Ｇ 分 ５Ｆ ，５单 元格 ， 到决策 得 变量 ｘ＇ Ｘ 的检验数 。该表对应一个基可行解（，，， 由于存在检验 ２ ４ ｘ Ｏ０ ８ ， ９）
运筹学实验3用Excel求解线性规划模型
实验三、用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）。