当前位置：文档之家 › 运筹学中excel的运用(用excel解决线性规划、动态规划、排队论等问题)

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

  • 格式：doc
  • 大小：891.50 KB
  • 文档页数：29

下载文档原格式

  / 29

合集下载

运筹学数学excel操作实例

运筹学数学excel操作实例
两种药品的总利润作为决策目标进入单元格E9，正好位于用来帮助计算总利润的数据单元格的右边.类似于E列的其他输出单元格，E9=C9×C10+D9×D10或E9=SUMPRODUCT（C9：D9，C10：D10）.由于它是在对产量做出决策时目标值定为尽可能大的特殊单元格，所以被称为目标单元格.
根据对上述建模过程的总结，在电子表格中建立线性规划模型的步骤可归纳如下：
回忆例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规划求解功能操作说明

EXCEL规划求解功能操作说明

Excel规划求解功能操作说明以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。

一、加载规划求解功能1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。

2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。

此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。

二、构造表格Excel表格并填入各项数据以教材18页【例题2-8】为例，构造表格如下：1.录入约束条件系数约束条件（1）为5x1+x2-x3+x4=3，则在约束系数的第一行的x1,x2,x3,x4,x5，限制条件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。

约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即-10,6,2,0,1，=，2；约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数b，即1,0,0,0,0，≥，0；约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0；约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0；约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0；约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。

如下图所示。

2.录入目标函数系数目标函数为maxZ=4x1-2x2-x3，则在目标函数的x1,x2,x3,x4,x5列下分别录入4，-2，-1,0,0，如下图所示。

3. 录入约束条件的计算公式双击约束条件（1）行的“总和”单元格，录入以下内容：“=B3*B12+C3*C12+D3*D12+E3*E12+F3*F12”说明：录入的内容即是约束条件（1）的计算公式，其中“B3*B12”代表5x 1; “C3*C12”代表1x 2；“D3*D12”代表-1x 3；“E3*E12”代表1x 4；“F3*F12”代表0x 5。

用Excel求解运筹学问题

用Excel求解运筹学问题
Unit Profit Optimal Units Produced for Doors $100 $200 $300 $400 $500 $600 $700 $800 $900 $1,000 Doors 4 2 2 2 2 2 2 2 4 4 4 Windows 3 6 6 6 6 6 6 6 3 3 3 Total Profit $5,500 $3,200 $3,400 $3,600 $3,800 $4,000 $4,200 $4,400 $4,700 $5,100 $5,500
可变单元格 单元格 名字 $C$12 Units Produced Doors $D$12 Units Produced Windows 约束 单元格 名字 $E$7 Plant 1 Used $E$8 Plant 2 Used $E$9 Plant 3 Used 终 阴影 约束 允许的 允许的 值 价格 限制值 增量 减量 2 0 4 1E+30 2 12 150 12 6 6 18 100 18 6 6 终 递减 目标式 允许的 允许的 值 成本 系数 增量 减量 2 0 300 450 300 6 0 500 1E+30 300
C D Optimal Units Produced 16 17 Doors Windows 18 =DoorsProduced =WindowsProduced
E Total Prof it =TotalProf it
(1) 只有一个目标函数系数变动的影响
门的单位利润从$100变到$1000，产品组合的变化
５．Under the Tools menu, choose the "Add-Ins" command.
６．Click the Solver Table checkbox to have Solver Table load with Excel every time it is loaded.

Excel求解运筹学问题简介.

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求解线性规划问题

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在运筹学规划论教学中的应用

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在求解线性规划问题中的应用

所有的检验数都小于等于 ０故该表对应的基可行解（ 妻，Ｏ 是 ， １ ０） ， ，Ｔ
最优解。 ３“ ．规划求解” 加载宏方法 求解线性规划 问题 的另一种方法是 “ 规划 求解” 加载宏 的方 法 ， 该 方 法 简单 、 方便 、 捷 ， 大 提 高 了计 算 的 效 率 和 准确 性 ， 直 接 得 到 最 快 大 能
单 纯形法［ 图解法 仅仅适用于含有 两个决 策变量的规划 问题 ， Ｊ Ｊ ， 而单纯 形法适用 于含有三个及其三个 以上决策变量 的线性规划问题 ，应用范 围更广 ， 但应用单纯形法时 ， 涉及的计算量大 。 为了减少计算量 ， 本文一 方 面借助 Ｅ ｃｌ ｘｅ 软件的表格和计算功 能， 现 了单纯形法 ； 一方 面应 实 另 用“ 规划求解” 加载宏 的方法直接求解线性规划问题 ， 得到最优解 。
１引 言 ．
线性 规划 问题 属 于 运 筹 学 的 一 个 分 支 ， 在 经 济 学 、 理 学 以及 生 它 管 产 生 活 中有 着 广 泛 的应 用 。求 解 线 性 规 划 问 题 的 方 法 主 要 有 图解 法 和
为 １ 所 在列的其他元 素变为 Ｏ ， ５ ，得到的新矩 阵再 反映在新单纯形 表 中 ， 根据检验数计算公式应用 ｓｍ ｒｄ ｃ 函数计算检验数 ， 并 ｕ ｐｏｕ ｔ 得到的新 单纯形表 如表 ２所示。
ｌ墓 Ｉ ｂ ０ ｆ翱 Ｉ ９ ０ ｌ ｌ ８
Ｏｊ
Ｘ ｌ ３
１ Ｏ
Ｘ ２ ４ ２
５
Ｘ ３ １ Ｏ
０
Ｘ ｔ ０ ｌ
Ｏ
在这 个初始单 纯形表 中， 据单纯形法 ， Ｅ ｃｌ Ｄ 根 在 ｘｅ 的 ５单元格 中 输 人计 算公式 “ Ｄ — ｕ Ｐ ０ ｕ Ｔ ￥ ￥ ： ￥，３ ４” ＝ Ｉｓ Ｍ Ｒ Ｄ ｃ （Ａ ３ Ａ ４ ： ）， ￥ Ｄ Ｄ 可得决策 变量 Ｘ ， 的检验数 ， 复制 Ｄ ５单元 格 ， 别粘贴在 Ｅ ，５Ｇ 分 ５Ｆ ，５单 元格 ， 到决策 得 变量 ｘ＇ Ｘ 的检验数 。该表对应一个基可行解（，，， 由于存在检验 ２ ４ ｘ Ｏ０ ８ ， ９）

精编Excel求解运筹学问题资料


450
300
6
0
500 1E+30
300
终 阴影 约束 允许的 允许的
值 价格 限制值 增量 减量
20
4 1E+30
2
12 150
12
6
6
18 100
18
6
6
极限值报告
Microsoft Excel 9.0 极限值报告 工作表 [Book1]Sheet1 报告的建立: 2006-7-18 10:04:47
1
0
0
2
3
2
Doors 1
Windows 1
Hours Used
1 2 5
Hours
Available
<=
1
<=
12
<=
18
Total Profit $800
第六步: 完成求解对话框 第七步:求解方式的选择
第八步: 从求解结果对话框选择所要的报告
Wyndor Glass Co. Product-Mix Problem
1 2 5
Hours
Available
<=
4
<=
12
<=
18
Total Profit $800
第五步: 增加约束条件
Unit Profit
Plant 1 Plant 2 Plant 3
Units Produced
Doors $300
Windows $500
Hours Used Per Unit Produced
第四步: 激活规划求解, 确定可变单元格和目标单元格
Unit Profit
Plant 1 Plant 2 Plant 3

用Excel求解LP(线性规划)问题


③光标指向“约束”栏，按“添加”， 光标指向“约束” 添加” 出现“添加约束”对话框（如下图）， 出现“添加约束”对话框（如下图），
对话框“改变约束”
依次填入约束关系，每输完一条，按“添加”， 输入所有约束条件后，按“确定”，
又退回到下图状态，在下图中可以选 又退回到下图状态， 更改” 删除” 全部重设” “更改”、“删除”、“全部重设” 来编辑约束条件及其他设置。 来编辑约束条件及其他设置。
结果
最后得到LP问题的求 最后得到LP问题的求 解结果(如下图8)，最 解结果(如下图8)，最 优解X1=2.909(即 优解X1=2.909(即 32/11),X2=2.727(即 32/11),X2=2.727(即 30/11), 目标函数的最 优值为28。 优值为28。
求解的结果
填入了参数的对话框“规划求解参数”
④在上图中按“求解”，即进入求解过程，求 在上图中按“求解” 即进入求解过程， 解结束，出现“规划求解结果”对话框( 解结束，出现“规划求解结果”对话框(如下 选择“保存规划求解结果” 图)，选择“保存规划求解结果”后，
对话框“规划求解结果”
工作表中可变单元格、目标单元格以及计 算约束条件的单元格均发生变化。如不想 破坏原始数据，可选择“恢复为原值”， 同时选中“报告”框中的“运算结果报 告”，或选“保存方案”以存储运算结果。
图1. 加载宏
以下面的LP为例， 以下面的LP为例，看如何利用 LPin f = 4x1+6x2 s.t. 3x1 - x2 ≥ 6 x1 + 2x2 ≤ 10 7x1 - 6x2 ＝ 4 x1 , x2 ≥ 0
2、输入系数： 输入系数：
在单元格A2:D4中分 在单元格A2:D4中分 别输入两个不等式约 束的系数与常数项, 束的系数与常数项,在 单元格A5:B5中分别 单元格A5:B5中分别 输入目标函数的两个 系数,在单元格A1:B1 系数,在单元格A1:B1 中任意输入两个数分 别作为决策变量x1,x2 别作为决策变量x1,x2 的值(如右图，C 的值(如右图，C列暂 空) 。
  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。