Excel求解运筹学问题简介

1、Excel求解运筹学问题方法简介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。问题：工厂应分别生产多少单位甲、乙产品才能使总成本最低？解：容易建立如下线性规划模型。设Xi、X2分别为产品甲、乙的产量，模型为mins.t.z = 2x1 3x2x1 x2 -

4、 350x1_1252x1 x2 < 600x1,x2 _ 0（1）数据输入：如图1-2所示。7 icrosofl： Excel -线性现划求解例过1名文件编辑视图伯插入Q）格式地）工具任）数据窗口 口卢0总社昌昌争/船Eg,4也快译廖英中叵日中空中英臼设置，G9ABCDEF1决策变量2目标函数34567约束矩阵81011sum21350125600图1-2数据输入界面在输入界面中，第1、4、7行是说明文字，无论输入什么内容或不输入均不会影响计算。其中，单元格B2、C2为决策变量初值，对于线性规划求解， 初值可任取；B5、C5是目标函数系数;B8、C8、B9、C9、B10、C10为各约束

5、函数的左端系数；E8、E9、E10为约束右端项。以上数据均直接从键盘输入。关键的是，E5、D8、D9、D10分别为目标函数和约束左端三个函数的计算公式。E5的输入如图1-3所示，见红色箭头指示。图1-3 E5公式输入方式E5格的公式D8、D9、D10 用同样的方式可以输入：=B8*B2+C8*C2 , =B8*B2+C8*C2 , =B8*B2+C8*C2 。（2）调用“规划求解”模块：单击“工具（ T）”栏的“规划求解”，得到图1-4 “规划求解参数” 框。图1-4规划求解参数框目标函数格目标求最小决策变量格决策变量非负 第3个约束首先设置目标单元格，这里 $E$5即E5。其余设置如图1-4

6、所示，值得说明的是设置可变单 元格通过单击“推测（G）”来完成，设置约束通过单击“添加（ A）”来完成。图1-5 规划求解选项框图1-6 规划求解结果选择框在这一步要通过单击“选项（0）”来设置有关信息，单击“选项（0）”后可得到如图1-5所 示的框。在这里只需单击“采用线性模型（ M）”即可完成，然后点“确定”，回到“规划求解参 数”框。（3）解线性规划模型：在“规划求解参数”框单击“求解（S）”，得到如图1-6所示的“规划求解结果”框，其中“报告（R）”只选“运算结果报告”和“敏感性报告”即可。单击“确定”。Excel文档中产生两个新表：“运算结果报告”（图1-7）和“敏感性报告”（图1-

7、8）。E? licroinft Excel纯悍 1sl划定群例 >.lb li EEiFsufl Eurtsl -线性煤盘求解阀一工1工叨势f呼ism胤视固的 »AU）精式皿和8® Sa U 楷功小U ¥。安器史学*至”#1， ,事£"IMUWk出事其中日中中声tJI .41-6 （icroEoft Eicel虫。运算结果报告±1 B '：- DEF G ?1161 oft Ried 9r0运算结果报告T停费Bonkl Shccl It第告的Jtt立：2003-9-3 6:89:83目标单元格1最小值）一元帝名字 一 坤

8、值支件中 温强 观因® 茹人中 格式值 ia® 畜据®口占。白电自小/为喝的,42$15E7目_3_1»111213网15比1713地道21Z223网-可变单元帮华瑞名字整低以国一一能 il1瓯1ico约束单兀擀名字单元格使公支麻型物值350 JDJS,>=JEt8到这跳制值0，蟠260 jDt9/-tSi9排假期佰125SM10?LDjCOO JDJ1O<=JEJ1O到因鸵刍痼0犯能il250 JEJZ>=0未检腮制值250JCJ2t2100起晔口未生1闱制值1M二快译出英中 日申 中英设置.A1 足 licrosoft EiceL

9、9.0 粒感性报告AyrT B-FG -E1 ijcrosoft Excel 9. 0 瓶感性报告.作表BDDklSheet2告的建立：2003-9-3 6:59:33终单元格名字值递减目标式允许的允许的 版量成本*教增量m2al2500211E+30JCt2s2100031E+301线约束其甘的允许的单元相名字值价格限制值增量被量制咫S皿35035012550轴鞫-SLUD而"T1251251E+M5DJ10M皿600-1600100125可变单元将图1-7 运算结果报告图1-8敏感性报告运算结果报告 （图1-7 ）中，列出了线性规划的最优值（800）、最优解（xi=250, X2

10、=100）,以及约束松弛变量的值（Si= 0, S2= 125, S3= 0）。敏感性报告（图1-8 ）中，列出了线性规划的对偶价格（影子价格、阴影价格4、0、一 1）;关于目标函数的单因素灵敏性信息：C1当前值为2,当其他参数均不变时，它最多增加1,减少时可趋于负无穷，最优解不会变；C2当前值为3,当其他参数均不变时，它最多增加可趋于正无穷，最多减少1时，最优解不会变；关于约束右端项：b1当前值为350,当其他参数均不变时， 它最多增加125,最多减少50时, 对偶价格（影子价格）不会变；b2当前值为125,当其他参数均不变时，它最多增加125,减少时可趋于负无穷，对偶价格（影子价格）不会变

11、；b3当前值为600,当其他参数均不变时，它最多增加100,最多减少125时，对偶价格（影子价格）不会变。1.3 运输问题求解例1-2某公司从三个产地 AP A2、A3将物品运往四个销地 B1、B2、B3、B4,各产地的产量、各 销地的销量和各产地运往各销地每件物品的运费（百元）如表 1-1所示。表1-1运输费用表肖地 产地B8B3B4产量/tA3113107A19284A741059销量/t365620 （产销平衡）问应如何调运，可使得总运输费最小 ？解：对这个典型的运输问题，可用“规划求解”来计算求解。首先，运输问题模型本身就是 线性规划模型，因此在计算方面，只需注意在它的决策变量输入时，

12、列成矩阵形式即可。图1-9是此问题的数据输入界面。B2到E4是运输费用矩阵，B10到E12是运输变量矩阵（这里输入的初值可以任取，此处全部取值是1）, B7到E7是销地的销量限制，H10到H12是产地的产量限制。I3是目标函数，其公式输入同线性规划，如图 1-9中所示；B14到E14、G10至ij G12 分别为运往销地的约束函数、产地运出的约束函数。叵文件叫军班（U 祺密皿插入,格式9 工且中 致据5 窗口如常驰®口岸电鸟&争I总用色，啰G， 嗯石,却和1S吊胸 母斑.朱体一2 ，叵快讲 英中 日中区中英 *2 ,13 A =B2*B11 +34*&12+C2*Cl

13、 O+C3*C1L+C 4*C12+D2*DL Oi-D3*D1HD4*DL 3+E2*El OE3*E 11 +E4*E12在“规划求解参数”界面，决策变量即运输变量可直接用矩阵输入，销量约束和产量约束均 用向量形式表示，如图 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求解

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

15、进制即0、1变量”。图1-13添加约束界面设置完成的“规划求解参数”界面如图 1-14所示。设置过程总体上与线性规划问题求解输 入设置没有差别，这里不再赘述。单击“确定”后，可得到计算结果：x1 = 4, x2 = 2, z* = 14图1-14例1-3规划求解参数界面例1.3求解下列整数规划问题max z =15x1 +10x2 + 7x3s.t.5x1 -10x2 + 7x3 <86x1 +4x2 +8x3 <12-3x1 +2x2 +2x3 <10x1,x2,x3 _0»1为0、1变量，x3为整数解：对这个混合整数规划问题，亦可用“规划求解”来计算求解。输入与

16、线性规划问题的输入完 全一样，如图1-15所示。S Microsoft Escel -珪再 战划求鼾例.工工总目文件g 娜6 0视圉5 ffiACtl 格式®】 工具中 物8 3 国口始 帮助W口空冠昌2k十M电地一 勒名-短却15易叩* 国也.占 快评其中jj日中三中英 谀置.图1-15例1-4输入数据表格然后，设这个计算参数，如图 1-16所示。图1-16例1-4规划求解参数界面Excel求解运筹学问题方法简介单击“确定”后，可得到计算结果：Xi =1, X2 =1.5, X3 = 0, Z 3 302排队论问题求解Excel在表格中可以使用公式，进一步还可以编程，因而可以求解大

17、部分规范化的运筹学问 题。本节介绍求解排队论问题的Excel文档，由于试用了 “宏”，因此当系统提示是否启用宏时,请单击“启用宏”。2.1 M/M/1 模型 M/M/1/W无模型例2-1某公交一卡通充值站，有1个服务员，前来充值的顾客按泊松分布到达，平均每小时45人，每次充值服务的时间服从负指数分布，平均为 imin。求：1) 到达时，不需等待即可接收充值服务的概率po；2) 平均排队等待充值的和站内总的平均顾客数Lq、L;3) 顾客为了充值等待和逗留的时间Wq、W;4) 顾客到来需要等待的概率 pwo解：首先，可以确定此问题的模型为：M/M/1/s/K模型。参数 九=45/60 = 0.75

18、, = 1。调用“ Excel文档/排队论模型/M-M-1”中的“M/M/1”表，如图2-1所示，输入参数，即可得到有关 的结果。图2-1 M/M/1/苗因模型求解9 Excel求解运筹学问题方法简介于是，我们得到解为：1) 到达时，不需等待即可接收充值服务的概率Po =0.25 ;2) 平均排队等待充值的和站内总的平均顾客数Lq = 2.25 , L = 3.0 ;3) 顾客为了充值等待和逗留的时间Wq = 3min, W = 4min ;4) 顾客到来需要等待的概率Pw=0.75 。(2) M/M/1/N/ 2模型例2-2 一个小理发店只有1名理发师，除理发用椅外店里还准备了 3三把座椅供

19、顾客等待时休 息。已知理发时间服从负指数分布，每名顾客的平均理发时间为20min。来理发顾客的到达服从泊松分布，平均每小时 2人。求：1) 顾客到达时，不需等待即可理发的概率Po；2) 平均排队等待理发的和理发店内总的平均顾客数Lq、L;3) 顾客为了理发等待和逗留的时间Wq、W;4) 顾客到来需要等待的概率Pw和顾客损失率。解：首先，可以确定此问题的模型为：M/M/1/4/*模型。参数 K = 2, N = 60、20=3。调用“Excel文档/排队论模型/M-M-1”中的 “M/M/1/N”表，如图2-2所示输入参数，即可得到有关的结果。S Microsoft Excel -tls噂文件。

20、 编辑趣) 视图 插入9 格式(Q) 工具 数据 窗口也) 帮助但)隹J快译匡英中回日中生中英 设置.B11, 标ABCD4顾客到达为PoI落落on流5服务时间服从负指数分布67顾客平均到达率九二8每个服务台平均服务率上=9服务强度P =0.6666667|10系统等待空间容量为N=|4111I112运行参数1314系统中没有顾客的概率，Po0.38391 S48316顾客平均有效到达率1 e16顾客平均排队长(队列长)Lq0.62561 241717系统中的平均顾客数(队长)L13每个顾客的平均等待时间吟0.33850 671819每个顾客的平均逗留时间W20顾客进入系统需要等待的概率也0.

21、616121于是，我们得到解：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/M/c 模型 M/M/c/幻汜模型例2-3某公交一卡通充值站，有 2个服务员，前来充值的顾客按泊松分布到达，平均每小时45人，每次充值服务的时间服从负指数分布，平均为 1分钟。求:1) 没

22、有顾客充值服务的概率 Po；2) 平均排队等待充值的和站内总的平均顾客数Lq、L;3) 顾客为了充值等待和逗留的时间Wq、W;4) 顾客到来需要等待的概率 pwo解：首先，可以确定此问题的模型为：M/M/2/m任模型。参数 九=45/60 = 0.75, N = 1。调用“ Excel文档/排队论模型/M-M-c”中的“M/M/c”表，如图2-3所示，输入参数，即可得到有关的结果。图2-3 M/M/c/s/厘模型求解于是，得到解1) 没有顾客充值服务的概率Po = 0.4545 ;2) 平均排队等待充值的和站内总的平均顾客数Lq= 0.1227, L= 0.8727;3) 顾客为了充值等待和逗

23、留的时间Wq = 0.1636min、W = 1.1636min；4) 顾客到来需要等待的概率Pw = 0.2045 。(2) M/M/c/N / /模型例2-4 一个小理发店只有2名理发师，除理发用椅外店里还准备了 3三把座椅供顾客等待时休 息。已知理发时间服从负指数分布，每名顾客的平均理发时间为20min。来理发顾客的到达服从泊松分布，平均每小时 2人。求：a) 顾客到达时，不需等待即可理发的概率Po；b) 平均排队等待理发的和理发店内总的平均顾客数Lq、L;c) 顾客为了理发等待和逗留的时间Wq、W;d) 顾客损失率。解：首先，可以确定此问题的模型为：M/M/2/5/丈模型。参数 X =

24、 2, N = 60/20=3。调用“Excel文档/排队论模型/M-M-c”中的 “M/M/c/N”表，如图2-2所示输入参数，即可得到有 关的结果。MitrrasDft E.UQJ -注0 文件比)编捐旧 视图出)格式Q)工具皿)数据如 窗口炉 帮助的快译一英中,日中上中英 设置.C10ABC01泊松到达，指数服务等待空间I等量有限的多服务台等待模型23假设A顾客到达为Poisson流5服务时间服从负指数分布61服务台数。=2助客平均到达率1 =29每个服务台平均服务率卜=310系统等待空间容量为N =51112运行参数1314系统中没有顾客的概率，尸小0.501015顾客平均有效到达率又

25、?1.991S16顾客平均由瞅长(队列长)Lq0.074217系统中的平均顾客数(队长)L0.70916每个顾客的平均等待时间党0,057319每个顾客的平均逗留时间W0.370620图2-4 M/M/2/5/变模型求解 于是，我们得到解：1) 顾客到达时，不需等待即可理发的概率P0 = 0.5010;11Excel求解运筹学问题方法简介2) 平均排队等待理发的和理发店内总的平均顾客数Lq= 0.0742 , L= 0.7409 ;3) 顾客为了理发等待和逗留的时间Wq=0.0373h=2.24min , W= 0.3706h=22.24min ;4) 顾客损失率=±e =0.004

26、1。2.3 M/G/1 模型例2-5某公司的1位顾客咨询接待人员，前来咨询的顾客按泊松分布到达，平均每小时 21人，每人次的接待时间服从正态分布N( 2, 1.22 )o求：1) 顾客到达时，不需等待即可接收服务的概率P0；2) 平均排队等待的和接待室内总的平均顾客数Lq、L;3) 顾客为了咨询等待和逗留的时间Wq、W;4) 顾客到来需要等待的概率 pw。解：首先，可以确定此问题的模型为：M/G/1/力空模型。参数 Z = 21/60 = 0.35 , N = 1/2=0.5。调用“Excel文档/排队论模型/M-G-1"，如图2-5所示输入参数，即可得到有关的结果。E Mjcros

27、oft EjecpI W-G-l. J J期D晒文怦。 编辑 祝图比) 插入© 格式心) 工具编 安。也后&瞥品电玛-0 s .一 快历区英中回日中中英W设百,数据也)快口强X 71世)帮助如和注书IIMHSD25b标A8CDE1泊松到达,朋务时间为任意独立分布的单服务台等待模型23假设4政客到达为Poisson流5服务时问服从任意分布67顾客平均到达率九二0.35B当个服务台平均服务率LL =0 59服务时间标准差CT =1.2101112运行拳薮1314系统中投有顾客的概率，PoI0 300015顾客平均排队长(:队列长)£守1.110716系统中的平均顾客数(

28、队长)L1 R10717每个顾客的平塌等待时间施3 17331B每个顾客的平均逗留时间W5.173319顾客进入系统需要等待的慨率及，0 7000202122运行的经济分析2324单位时间等待的机会损失服务台的费用111!2627每个时间周期的费用S-29图2-5 M/G/1/3o/七模型求解于是，得到解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

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

30、2-6 M/M/2/6/6模型求解于是，得到解1)系统里平均等待和正在加油的机器数Lq=0.0227, L=0.5661 ;152） 一台机器缺油而停机等待加油的平均时间Wq = 0.0834min ;3） 2个维修保养工都空闲的概率p0 = 0.5602。3存储论确定型问题求解本节通过例题介绍求解四个确定型存储模型的3.1 不允许缺货的订购型存储问题例3-1某企业生产的产品中有一外购件，Excel文档。年需求量为 60000件，单价为35元。该外购件可在市场立即采购得到， 并设不允许缺货。已知每组织一次采购需 720元，每件每年的存储费为该件单价的20%。试求经济定货批量及每年最小的存储加上

31、采购的总费用。解：此问题是 不允许缺货的订购模型，用“存贮论例”文档中的“经济订购不允许缺货”表进行计算。见图3-1。其中的参数：年单件存储费订货费年需求ci=35 元父20%=7 元C3=720D=60000 件Lcrosoft Excel 在型检例.xls目 文件S） 编揖耍） 视图跳） 插入工） 格式工目中C7一彝ABC 1D1参数单件价格2年单件存储费工73每次订货费r37204年需求量力600005年工作天数N3656所需提前订货时间，独1711I8数拒篷”陵谛三英中Ln曰中 中英忙误置.91011121314计算量最优订货量0*平均存贮错年订货费U mi TTf.15163513.

32、 2412296. 341229G. 34存贮谎制.Hl S电文件 编霜但）视图插入© 格式坦了 工具 再T澳快璘超英中冏日中中中英,Lj-设道.D7图3-13.2不允许缺货的生产型存储问题年单件存潴费c ,10. 813E0福渊即对该型3生产推省费一例3-2 一条生产线如果全部用于某种等号产品时，年H害生阴咽.产品的年需求量为 250000台，并在£年内需求基本嗯角簟如F该生产线将用于余服的 网需准备结束费 ”一年存储费用为产品成本的 24%,不允许发4轮番生产。已知在生产线上更换一种产品为 600000;方声口 该厂口口45元,进行计算。见图3-2。其中的参数:皆商训生

33、产批量。J1 1计算量最优生产量Q中10350, 9811平均存贮费32605.612年生产费32605.613息费用TC6521L 214年生产次数2 1522915周期T （天数）111 35O'?E16再生产点r017表解：此问题是不允许缺货的生产模型已，用“存储论例”文档中的“经济生产不允许缺货Excel求解运筹学问题方法简介年单件存储费 生产准备费 年需求 生产率Ci=45 元父24%=10.8 元03=1350D=250000 台p=600000 台/年图3-2不允许缺货的生产模型求解11E3 Hi.crDKO-fEkcel看归徒网.:1b3.3允许缺货的订购型存贮问题治文件啜）用箱里j vlsi w 插入。 格式。nznst® r 口m&琼3 g 一 蜷A IBC口1参薮单件价格2年单件存赠费J43年单件域货费r2L 64每次订货费1305年需求至日50006年工作天数JV|2507所需提酎订货时阿，川0,8910讨算量最优订货量Q*1024.