Excel求解运筹学问题简介

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。问题：工厂应分别生产多少单位甲、乙产品才能使总成本最低？(1)数据输入：如图解：容易建立如下线性规划模型。设X1、X2分别为产品甲、乙的产量，模型为min z 2% 3x2s.t.X-!x2350X11252x1 x260

4、0xz01-2所示。S licrosoft Ezcel - 线性規划求解惻宀昌圈as 爲睚BVp快译占英中且日中中中英设置”官 文件 編辑足视團婕插AO 格式Q)工具 数据 窗口 ABCDEFI决策变量耳?2113q目标函数 Iffu 与血 Ulffltf. 环aUIU MDJ 口側 n)S R皿僧04审老篡暮I匚3l5$EAzQ5Q|1Fl11i1003d4120GO317图1-11问题的解数据由图1-11，可得到此问题的解为：A1到B1运输2t、A1到B3运输5t、A2到B1运输1t、A 到B4运输3t、A到B2运输6t、A3到B4运输3t,总费用85百元。1.4

5、整数规划求解例1-3求解下列整数规划问题max z 2% 3x2s.t. 195x, 273x213564%40x21400 x1 4, x2 0, x-!, x2为整数解：对这个整数规划问题，可用“规划求解”来计算求解。输入与线性规划问题的输入完全 一样，如图1-12所示。图1-12例1-3输入表格本问题的不同之处，就是在添加约束时，设置变量为“int”，则界面自动会显示“整数”，如图1-13所示。图1-13“添加约束”中的符号选择有5项，单击“ ”时即可显示“=,int, bin ”，分别表示“小于等于，等于，大于等于，整数，二进制即0、1变量”。图1-13添加约束界面设置完成的“规划求解

6、参数”界面如图1-14所示。设置过程总体上与线性规划问题求解输入设置没有差别，这里不再赘述。单击“确定”后，可得到计算结果：x14, x22, z* 14图1-14例1-3规划求解参数界面例1.3求解下列整数规划问题max z 15x-i 10x2 7x3s.t.5x110X2 7x386x-i 4x2 8x3123x-i 2x2 2x310X1, X2, X30,冷为0、变量，X3为整数解：对这个混合整数规划问题，亦可用“规划求解”来计算求解。输入与线性规划问题的输入完 全一样，如图1-15所示。图1-15例1-4输入数据表格然后，设这个计算参数，如图1-16所示。图1-16例1-4规划求解

7、参数界面单击“确定”后，可得到计算结果：Xi 1, X21.5, X30, z* 302排队论问题求解Excel在表格中可以使用公式，进一步还可以编程，因而可以求解大部分规范化的运筹学问 题。本节介绍求解排队论问题的Excel文档，由于试用了 “宏”，因此当系统提示是否启用宏时，请单击“启用宏”。2.1 M/M/1 模型(1)M/M/1/ / 模型例2-1某公交一卡通充值站，有1个服务员，前来充值的顾客按泊松分布到达，平均每小时45人，每次充值服务的时间服从负指数分布，平均为1mi n。求：1) 到达时，不需等待即可接收充值服务的概率po；2) 平均排队等待充值的和站内总的平均顾客数Lq、L

8、；3) 顾客为了充值等待和逗留的时间Wq、W；4) 顾客到来需要等待的概率 Pw。解：首先，可以确定此问题的模型为：M/M/1/ /模型。参数 =45/60 = 0.75，= 1。调用“ Excel文档/排队论模型/M-M-1 ”中的“M/M/1 ”表，如图2-1 所示，输入参数，即可得到有关 的结果。图2-1M/M/1/ /模型求解于是，我们得到解为：1) 到达时，不需等待即可接收充值服务的概率po =0.25 ;2) 平均排队等待充值的和站内总的平均顾客数Lq = 2.25，L = 3.0 ；3) 顾客为了充值等待和逗留的时间Wq = 3min，W = 4min ；4) 顾客到来需要等待的

9、概率pw=0.75。M/M/1/N/模型例2-2 一个小理发店只有1名理发师，除理发用椅外店里还准备了 3三把座椅供顾客等待时休 息。已知理发时间服从负指数分布，每名顾客的平均理发时间为20min。来理发顾客的到达服从泊松分布，平均每小时 2人。求：1) 顾客到达时，不需等待即可理发的概率P0；2) 平均排队等待理发的和理发店内总的平均顾客数Lq、L；3) 顾客为了理发等待和逗留的时间Wq、W；4) 顾客到来需要等待的概率Pw和顾客损失率。解：首先，可以确定此问题的模型为：M/M/1/4/模型。参数 =2，= 60、20=3。调用“ Excel文档/排队论模型/M-M-1 ”中的 “ M/M/

10、1/N”表，如图2-2所示输入参数，即可得到有 关的结果。Bl icrosoft Excel 一1. nls粵文件動 漏辑広) 观團世)插入 格式血 工員 数据) 蔚口 帮助on 口 3日壽乜 唇広貯|乳躬鶴富”斜裂lift祸 蟄快译医英中旦日中过中英團没置.B11十AABCD4顾客到达为Poisson流5服务时间脆从负挹数分布67顾客平均到达率入=0每个服务台平均服务率出=39服务强度P =0 666666710系统竽待空间容量为N =411III!12运行参数1314系统中没有顾客的慨率 Po0383915顾客平均有效到达率兄尸1.848316顾客平均护執长(队列长)Lq0.625617系

11、统中的平均顾客数队长)L1 241718每个顾客的平均等待时间Wq0.338519每个顾客的平均逗留时间炉0.671820顾客进入系统需要等待的慨率Z0.616121图2-2M/M/1/4/模型求解于是，我们得到解：1) 顾客到达时，不需等待即可理发的概率po = 0.3839 ；2) 平均排队等待理发的和理发店内总的平均顾客数Lq= 0.6256, L= 1.2417;3) 顾客为了理发等待和逗留的时间Wq=0.3385h=20.3077min ， W=0.6718h=40.3077min ；4) 顾客到来需要等待的概率pw =0.6161 ， 顾客损失率= e 0.007585。2.2 M

12、/M/c 模型(1) M/M/c/ / 模型例2-3某公交一卡通充值站，有 2个服务员，前来充值的顾客按泊松分布到达，平均每小时45人，每次充值服务的时间服从负指数分布，平均为 1分钟。求：1) 没有顾客充值服务的概率 P。；2) 平均排队等待充值的和站内总的平均顾客数Lq、L ；3) 顾客为了充值等待和逗留的时间Wq、W；4) 顾客到来需要等待的概率 Pw。解：首先，可以确定此问题的模型为：M/M/2/ /模型。参数=45/60 = 0.75，= 1。调用“ Excel文档/排队论模型/M-M-c”中的“M/M/c”表，如图2-3所示，输入参数，即可得到有关 的结果。图2-3 M/M/c/

13、/模型求解于是，得到解1) 没有顾客充值服务的概率po = 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人。求：a) 顾客到达时，不需等待即可理发的概率P。；

14、b) 平均排队等待理发的和理发店内总的平均顾客数Lq、L；c) 顾客为了理发等待和逗留的时间Wq、W；d) 顾客损失率。解：首先，可以确定此问题的模型为：M/M/2/5/模型。参数 =2，= 60/20=3。调用“ Excel文档/排队论模型/M-M-c”中的 “ M/M/c/N”表，如图2-2所示输入参数，即可得到有 关的结果。B icrorsoft Excel 一 一一匚.xls題交件銅輯團视的0斂迦工眞数葩筍口(I WQfJ占曹 a協亀强”令 時+制刘 眄解“慑澤金英呻i_ 口中送申駆设置+CIO T居ABC01泊松到达、橹數服务等持空间容昼有限的多JK务台等特模型23假设A&客到达为T

15、onsson.L0服务时间服从旬鶴数分布67服务台救C二产28顒客平均到达率1=9毎个服务會平均廉警率H=2U靈统尊持空间容量为tl12运行翁数口T4系统中役有頤客的慨率，刊0.501015坝客平均有效到这率丘r1 9918t6锁容平均申頼长(队列畏)g0.074217系统中的平均顾喜数(队长)L0.7409TS每个顾客的平均弩待时间Wq00373tg誓个顾專的平均逗留时间W0,370620图2-4 M/M/2/5/模型求解于是，我们得到解：1)顾客到达时，不需等待即可理发的概率P0 = 0.5010;2) 平均排队等待理发的和理发店内总的平均顾客数Lq= 0.0742 , L= 0.7409

16、 ;3) 顾客为了理发等待和逗留的时间 Wq=0.0373h=2.24min , W= 0.3706h=22.24min ；4) 顾客损失率=e 0.0041 o2.3 M/G/1 模型例2-5某公司的1位顾客咨询接待人员，前来咨询的顾客按泊松分布到达，平均每小时21人，每人次的接待时间服从正态分布N( 2, 1.22 )。求：1) 顾客到达时，不需等待即可接收服务的概率P0；2) 平均排队等待的和接待室内总的平均顾客数Lq、L ；3) 顾客为了咨询等待和逗留的时间Wq、W;4) 顾客到来需要等待的概率 pWo解：首先，可以确定此问题的模型为：M/G/1/ /模型。参数 =21/60 = 0.

17、35 ,= 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需加油一次，由于工作强度是随 机的，机器缺油时自动停机，停机时

18、间服从负指数分布。每个维修保养工完成1台机器的加油平均 需要2min，加油时间服从负指数分布，现有 2个维修保养工。求：1) 系统里平均等待和正在加油的机器数；2) 一台机器缺油而停机等待加油的平均时间；3) 2个维修保养工都空闲的概率。解：首先，可以确定此问题的模型为：M/M/2/6/6模型。参数 =1/20=0.5，= 1/2=0.5。调用“ExceI文档/排队论模型/M-M-c-m-m ”中的表，如图2-6所示输入参数，即可得到有关的结果。图2-6M/M/2/6/6模型求解于是，得到解1)系统里平均等待和正在加油的机器数Lq=0.0227，L=0.5661 ;2）一台机器缺油而停机等待加

19、油的平均时间Wq = 0.0834min ;3）2个维修保养工都空闲的概率po = 0.5602。3存储论确定型问题求解本节通过例题介绍求解四个确定型存储模型的Excel文档。3.1不允许缺货的订购型存储问题例3-1某企业生产的产品中有一外购件， 年需求量为 60000件，单价为35元。该外购件可 在市场立即采购得到， 并设不允许缺货。已知每组织一次采购需 720元，每件每年的存储费为该 件单价的20%。试求经济定货批量及每年最小的存储加上采购的总费用。解：此问题是 不允许缺货的订购模型，用“存贮论例”文档中的“经济订购不允许缺货”表 进行计算。见图3-1。其中的参数：年单件存储费 c仁35元

20、 20%=7元订货费C3=720年需求D=60000件3 i r r s of +e L 右制I - at Ji m可刃:件疋縊懾逼.1 视閏仪）插，兀也） 旦快译工黄中旦日中,一車英一设置A12345G7:S9计算量10丄丄1213141516单件价格年单件存储费厂,琴次订货费广蚩年需求量年工作天救7V所鬲捉前订货吋问川最优LTfSS 0 平均存贮费年订货费图3-1720eoonn3&513513. 2412296. 34C r*尺广C 住gDR 11 魁L曲怏译.叙英中曰中中英吃，设査r蛮文件溝啊觇图辺牺入 格式Q 工具Q 数茨D7的订购模型求解不允许E Miurosoiz-t cd 3.

21、2不允许缺货的生产型存储问题年单件存储费r（CD10. B1.，产t能力为600000台。据预测对该型no I的）元。该产品每台成本为一45元, 的该产品的生产批量。:-表例3-2 一条生产线如果全部用于某号产品的年需求量为 250000台，并在全年内需求基本保持平衡，因此该生产线将用于 轮番生产。已知在生产线上更换一种产品年存储费用为产品成本的24%，不允许发生供应短缺。种型号产品时,苴.F时，需准备结束费朋解：此问题是不允许缺货的生产模型弓，用“存储论例”文档中的“经济生产不允许缺货 进行计算。见图3-2。其中的参数：gLU计尊昼 最优坐产星CT11 平均存贮费12 生产费14 年空产次数1516-14周期T （天数）再生产点r1U3&U. 9832605,124.1522S10. 35