版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、Excel求解运筹学问题方法简介 Excel中的规划求解是功能强大的优化和资源配置工具。它可以帮助人们求解运筹学中的许多问题,特别是“规划求解”模块可以解决许多求极值、解方程的问题。 本附件除介绍“规划求解”模块的使用外,还提供给读者“排队论”与“存储论”基本模块。1 规划求解 在使用“规划求解”时,首先需要“规划求解”出现在“工具”菜单中,如果没有,则需要加载“规划求解”宏。另外,目标函数和约束函数必须要给出公式,变量的约束必须作为约束条件给出。规划求解的特点: 表格输入数据不能为分数,当遇到分数时,必须化为小数输入。 目标单元格依赖一组单元格(可变单元格),或通过公式间接依赖于可变单元格,
2、规划求解可调整这组单元格来影响目标单元格。 目标单元格服从一定的约束和限制。约束条件不同,结果就不同。 可求解特定单元格的最大值或最小值或某个值。 对一个问题可以求出多个解。1.1加载“规划求解”模块 首先,打开Excel文件,进入表格界面,单击“工具(T)”,如果存在“规划求解”项目,说明已经加载(加载只需进行一次,以后如果不人为删除,就会保留在工具栏内),可直接使用。图1-1“加载宏”图 如果不存在“规划求解”项目,单击“加载宏”,会出现如图1-1所示“加载宏”图框。单击“规划求解”,使复选框中出现对勾,再单击“确定”,即完成了加载(注:若在Office软件装入时,系统未选择该工具模块装入
3、,此时会引导读者插入软件安装盘,依据系统提示操作即可)。1.2 线性规划问题求解 为了便于说明,以一个线性规划例题来说明这个过程。例1-1 某工厂在计划期内要安排甲、乙两种产品的生产,已知生产单位产品所需成本分别为2千元和3千元;根据产品特性,产品总数不得少于350件,产品甲不得少于125件;又知生产这两种产品需要某种钢材,产品甲、乙每件分别需要钢材2t、1t,钢材的供应量限制在600t。问题:工厂应分别生产多少单位甲、乙产品才能使总成本最低?解: 容易建立如下线性规划模型。设x1、x2分别为产品甲、乙的产量,模型为 (1) 数据输入:如图1-2所示。 图1-2 数据输入界面 在输入界面中,第
4、1、4、7行是说明文字,无论输入什么内容或不输入均不会影响计算。其中,单元格B2、C2为决策变量初值,对于线性规划求解,初值可任取;B5、C5是目标函数系数;B8、C8、B9、C9、B10、C10为各约束函数的左端系数;E8、E9、E10为约束右端项。以上数据均直接从键盘输入。关键的是,E5、D8、D9、D10分别为目标函数和约束左端三个函数的计算公式。E5的输入如图1-3所示,见红色箭头指示。E5格的公式 图1-3 E5公式输入方式D8、D9、D10用同样的方式可以输入:=B8*B2+C8*C2,=B8*B2+C8*C2,=B8*B2+C8*C2。(2) 调用“规划求解”模块:单击“工具(T
5、)”栏的“规划求解”,得到图1-4“规划求解参数”框。 图1-4 规划求解参数框 首先设置目标单元格,这里$E$5即E5。其余设置如图1-4所示,值得说明的是设置可变单元格通过单击“推测(G)”来完成,设置约束通过单击“添加(A)”来完成。 图1-5 规划求解选项框 图1-6 规划求解结果选择框 在这一步要通过单击“选项(0)”来设置有关信息,单击“选项(0)”后可得到如图1-5所示的框。在这里只需单击“采用线性模型(M)”即可完成,然后点“确定”,回到“规划求解参数”框。(3) 解线性规划模型:在“规划求解参数”框单击“求解(S)”,得到如图1-6所示的“规划求解结果”框,其中“报告(R)”
6、只选“运算结果报告”和“敏感性报告”即可。单击“确定”。 Excel文档中产生两个新表:“运算结果报告”(图1-7)和“敏感性报告”(图1-8)。 图1-7 运算结果报告 图1-8 敏感性报告 运算结果报告 ( 图1-7 ) 中,列出了线性规划的最优值(800)、最优解(x1=250, x2=100),以及约束松弛变量的值(s1 = 0, s2 = 125, s3 = 0)。 敏感性报告 ( 图1-8 ) 中,列出了线性规划的对偶价格(影子价格、阴影价格4、0、1);关于目标函数的单因素灵敏性信息:c1当前值为2,当其他参数均不变时,它最多增加1,减少时可趋于负无穷,最优解不会变;c2当前值为
7、3,当其他参数均不变时,它最多增加可趋于正无穷,最多减少1时,最优解不会变; 关于约束右端项:b1当前值为350,当其他参数均不变时,它最多增加125,最多减少50时,对偶价格(影子价格)不会变;b2当前值为125,当其他参数均不变时,它最多增加125,减少时可趋于负无穷,对偶价格(影子价格)不会变;b3当前值为600,当其他参数均不变时,它最多增加100,最多减少125时,对偶价格(影子价格)不会变。1.3 运输问题求解例1-2 某公司从三个产地A1、A2、A3 将物品运往四个销地B1、B2、B3、B4,各产地的产量、各销地的销量和各产地运往各销地每件物品的运费(百元)如表1-1 所示。表1
8、-1 运输费用表销地产地B1B2B3B4产量/tA13113107A219284A3741059销量/t365620(产销平衡)问应如何调运,可使得总运输费最小?解:对这个典型的运输问题,可用“规划求解”来计算求解。首先,运输问题模型本身就是线性规划模型,因此在计算方面,只需注意在它的决策变量输入时,列成矩阵形式即可。 图1-9是此问题的数据输入界面。B2到E4是运输费用矩阵,B10到E12是运输变量矩阵(这里输入的初值可以任取,此处全部取值是1),B7到E7是销地的销量限制,H10到H12是产地的产量限制。I3是目标函数,其公式输入同线性规划,如图1-9中所示;B14到E14、G10到G12
9、分别为运往销地的约束函数、产地运出的约束函数。 图1-9 运输问题输入界面 在“规划求解参数”界面,决策变量即运输变量可直接用矩阵输入,销量约束和产量约束均用向量形式表示,如图1-10所示。 图1-10 运输问题规划求解参数界面 单击“求解(S)”后,即可得到问题的解和最优值,如图1-11所示。图1-11 问题的解数据 由图1-11,可得到此问题的解为:A1到B1运输2t、A1到B3运输5t、A2到B1运输1t、A2到B4运输3t、A3到B2运输6t、A3到B4运输3t,总费用85百元。1.4 整数规划求解例1-3 求解下列整数规划问题 解:对这个整数规划问题,可用“规划求解”来计算求解。输入
10、与线性规划问题的输入完全一样,如图1-12所示。 图1-12例1-3输入表格 本问题的不同之处,就是在添加约束时,设置变量为“int”,则界面自动会显示“整数”,如图1-13所示。图1-13“添加约束”中的符号选择有5项,单击“ ”时即可显示“=, int, bin”,分别表示“小于等于,等于,大于等于,整数,二进制即0、1变量”。 图1-13 添加约束界面 设置完成的“规划求解参数”界面如图1-14所示。设置过程总体上与线性规划问题求解输入设置没有差别,这里不再赘述。单击“确定”后,可得到计算结果: 图1-14 例1-3规划求解参数界面例1.3 求解下列整数规划问题 解:对这个混合整数规划问
11、题,亦可用“规划求解”来计算求解。输入与线性规划问题的输入完全一样,如图1-15所示。 图1-15 例1-4输入数据表格 然后,设这个计算参数,如图1-16所示。 图1-16 例1-4规划求解参数界面 单击“确定”后,可得到计算结果: 2 排队论问题求解 Excel在表格中可以使用公式,进一步还可以编程,因而可以求解大部分规范化的运筹学问题。本节介绍求解排队论问题的Excel文档,由于试用了“宏”,因此当系统提示是否启用宏时,请单击“启用宏”。2.1 M/M/1模型(1) M/M/1/模型例2-1 某公交一卡通充值站,有1个服务员,前来充值的顾客按泊松分布到达,平均每小时45人,每次充值服务的
12、时间服从负指数分布,平均为1min。求: 1) 到达时,不需等待即可接收充值服务的概率p0; 2) 平均排队等待充值的和站内总的平均顾客数Lq、L; 3) 顾客为了充值等待和逗留的时间Wq、W; 4) 顾客到来需要等待的概率pW。解:首先,可以确定此问题的模型为:M/M/1/模型。参数 l = 45/60 = 0.75,m = 1。调用“Excel文档/排队论模型/M-M-1”中的 “M/M/1”表,如图2-1所示,输入参数,即可得到有关的结果。 图2-1 M/M/1/模型求解 于是,我们得到解为: 1) 到达时,不需等待即可接收充值服务的概率 p0 =0.25; 2) 平均排队等待充值的和站
13、内总的平均顾客数 Lq = 2.25,L = 3.0; 3) 顾客为了充值等待和逗留的时间 Wq = 3min,W = 4min; 4) 顾客到来需要等待的概率 pW =0.75 。(2) M/M/1/N/模型例2-2 一个小理发店只有1名理发师,除理发用椅外店里还准备了3三把座椅供顾客等待时休息。已知理发时间服从负指数分布,每名顾客的平均理发时间为20min。来理发顾客的到达服从泊松分布,平均每小时2人。求: 1) 顾客到达时,不需等待即可理发的概率p0; 2) 平均排队等待理发的和理发店内总的平均顾客数Lq、L; 3) 顾客为了理发等待和逗留的时间Wq、W; 4) 顾客到来需要等待的概率
14、pW 和 顾客损失率。解:首先,可以确定此问题的模型为:M/M/1/4/模型。参数 l = 2,m = 60、20=3。调用“Excel文档/排队论模型/M-M-1”中的 “M/M/1/N”表,如图2-2所示输入参数,即可得到有关的结果。 图2-2 M/M/1/4/模型求解于是,我们得到解: 1) 顾客到达时,不需等待即可理发的概率p0 = 0.3839; 2) 平均排队等待理发的和理发店内总的平均顾客数 Lq=0.6256,L=1.2417; 3) 顾客为了理发等待和逗留的时间 Wq=0.3385h=20.3077min,W=0.6718h=40.3077min; 4) 顾客到来需要等待的概
15、率 pW =0.6161, 顾客损失率 = 。2.2 M/M/c模型(1) M/M/c/模型例2-3 某公交一卡通充值站,有2个服务员,前来充值的顾客按泊松分布到达,平均每小时45人,每次充值服务的时间服从负指数分布,平均为1分钟。求: 1) 没有顾客充值服务的概率p0; 2) 平均排队等待充值的和站内总的平均顾客数Lq、L; 3) 顾客为了充值等待和逗留的时间Wq、W; 4) 顾客到来需要等待的概率pW。解:首先,可以确定此问题的模型为:M/M/2/模型。参数 l = 45/60 = 0.75,m = 1。调用“Excel文档/排队论模型/M-M-c”中的 “M/M/c”表,如图2-3所示,
16、输入参数,即可得到有关的结果。 图2-3 M/M/c/模型求解于是,得到解 1) 没有顾客充值服务的概率 p0 = 0.4545; 2) 平均排队等待充值的和站内总的平均顾客数 Lq=0.1227,L=0.8727; 3) 顾客为了充值等待和逗留的时间 Wq = 0.1636min、W = 1.1636min; 4) 顾客到来需要等待的概率 pW = 0.2045 。(2) M/M/c/N/模型例2-4 一个小理发店只有2名理发师,除理发用椅外店里还准备了3三把座椅供顾客等待时休息。已知理发时间服从负指数分布,每名顾客的平均理发时间为20min。来理发顾客的到达服从泊松分布,平均每小时2人。求
17、: a) 顾客到达时,不需等待即可理发的概率p0; b) 平均排队等待理发的和理发店内总的平均顾客数Lq、L; c) 顾客为了理发等待和逗留的时间Wq、W; d) 顾客损失率。解:首先,可以确定此问题的模型为:M/M/2/5/模型。参数 l = 2,m = 60/20=3。调用“Excel文档/排队论模型/M-M-c”中的 “M/M/c/N”表,如图2-2所示输入参数,即可得到有关的结果。 图2-4 M/M/2/5/模型求解于是,我们得到解: 1) 顾客到达时,不需等待即可理发的概率 p0 = 0.5010; 2) 平均排队等待理发的和理发店内总的平均顾客数 Lq= 0.0742,L= 0.7
18、409; 3) 顾客为了理发等待和逗留的时间 Wq=0.0373h=2.24min,W=0.3706h=22.24min; 4) 顾客损失率 =。2.3 M/G/1模型例2-5 某公司的1位顾客咨询接待人员,前来咨询的顾客按泊松分布到达,平均每小时21人,每人次的接待时间服从正态分布 N( 2, 1.22 )。求: 1) 顾客到达时,不需等待即可接收服务的概率 p0; 2) 平均排队等待的和接待室内总的平均顾客数Lq、L; 3) 顾客为了咨询等待和逗留的时间Wq、W; 4) 顾客到来需要等待的概率pW。解:首先,可以确定此问题的模型为:M/G/1/模型。参数 l = 21/60 = 0.35,
19、m = 1/2=0.5。调用“Excel文档/排队论模型/M-G-1”,如图2-5所示输入参数,即可得到有关的结果。 图2-5 M/G/1/模型求解于是,得到解 a) 到达时,不需等待即可接收服务的概率 p0 = 0.3; b) 平均排队等待的和接待室内总的平均顾客数 Lq = 1.1107,L = 1.8107; c) 顾客为了咨询等待和逗留的时间 Wq=3.173min,W = 5.1733min; d) 顾客到来需要等待的概率 pW =0.7。2.4 M/M/c/m/m模型例2-6 某机加工车间有6台相同的机器,每台机器平均20min需加油一次,由于工作强度是随机的,机器缺油时自动停机,
20、停机时间服从负指数分布。每个维修保养工完成1台机器的加油平均需要2min,加油时间服从负指数分布,现有2个维修保养工。求: 1) 系统里平均等待和正在加油的机器数; 2) 一台机器缺油而停机等待加油的平均时间; 3) 2个维修保养工都空闲的概率。解:首先,可以确定此问题的模型为:M/M/2/6/6模型。参数 l = 1/20=0.5,m = 1/2=0.5。调用“Excel文档/排队论模型/M-M-c-m-m”中的表,如图2-6所示输入参数,即可得到有关的结果。 图2-6 M/M/2/6/6模型求解于是,得到解 1) 系统里平均等待和正在加油的机器数 Lq=0.0227,L=0.5661; 2
21、) 一台机器缺油而停机等待加油的平均时间 Wq = 0.0834min; 3) 2个维修保养工都空闲的概率 p0 = 0.5602。3 存储论确定型问题求解 本节通过例题介绍求解四个确定型存储模型的Excel文档。3.1 不允许缺货的订购型存储问题例3-1某企业生产的产品中有一外购件,年需求量为 60000 件,单价为 35 元。该外购件可在市场立即采购得到,并设不允许缺货。已知每组织一次采购需 720 元,每件每年的存储费为该件单价的20%。试求经济定货批量及每年最小的存储加上采购的总费用。解:此问题是不允许缺货的订购模型,用“存贮论例”文档中的“经济订购不允许缺货”表进行计算。见图3-1。其中的参数: 年单件存储费 c1=35元20%=7元 订货费 c3=720 年需求 D=60000件 图3-1 不允许缺货的订购模型求解3.2 不允许缺货的生产型存储问题例3-2 一条生产线如果全部用于某种型号产品时,其年生产能力为台。据预测对该型号产品的年需求量为 台,并在全年内需求基本保持平衡,因此该生产线将用于多品种的轮番生产。已知在生产线上更换一种产品时,需准备结束费 1350 元。该产品每台成本为 45 元,年存储费用为产品成本的 24%,不允许发生供应短缺。求使费用最小的该产品的生产批量。解:此问题是不允许缺货的生产模型,用“存储论例”文
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 九年级音乐“非洲灵感”主题单元教学设计
- 初中数学八年级下册统计图教学设计
- 高中信息技术高二选择性必修《数据与数据结构》教学设计:组合数据类型的建模与实现
- 小学信息技术六年级“自动熄灭转向灯”跨学科教学设计
- 高二英语选择性必修第一册Unit 1 Welcome to the unit词汇默写与导入教学设计
- 小学一年级劳动课七巧板创意拼搭教学设计
- 九年级数学教学设计:用配方法解较简单的一元二次方程
- 2.2.2 脊椎动物(鱼)教学设计(初中生物七年级上册)
- 初中九年级数学教学设计:二次函数图象求一元二次方程根的近似值
- 2026事业单位工勤技能-新疆-新疆家禽饲养员四级(中级工)历年参考题库含答案详解
- 安装指导合同(标准版)
- (高清版)T∕CES 243-2023 《构网型储能系统并网技术规范》
- 华兴数控WA-32XTA用户手册
- 年产1亿片盐酸二甲双胍缓释片生产工艺设计
- 《结直肠癌的治疗进展》课件
- 《亲子沟通模式对幼儿情绪调节能力的影响探究国内外文献综述》1600字
- 《有效团队管理培训》课件
- DB11∕T 1655-2019 危险化学品企业装置设施拆除安全管理规范
- JB-T 8532-2023 脉冲喷吹类袋式除尘器
- 《测绘管理法律与法规》课件-测绘资质管理
- JGT14-2010 通风空调风口
评论
0/150
提交评论