Excel求解运筹学问题简介

Excel求解运筹学问题方法简介–1–Excel求解运筹学问题方法简介Excel中的规划求解是功能强大的优化和资源配置工具。它可以帮助人们求解运筹学中的许多问题，特别是“规划求解”模块可以解决许多求极值、解方程的问题。本附件除介绍“规划求解”模块的使用外，还提供给读者“排队论”与“存储论”基本模块。1规划求解在使用“规划求解”时，首先需要“规划求解”出现在“工具”菜单中，如果没有，则需要加载“规划求解”宏。另外，目标函数和约束函数必须要给出公式，变量的约束必须作为约束条件给出。规划求解的特点：◆表格输入数据不能为分数，当遇到分数时，必须化为小数输入。◆目标单元格依赖一组单元格（可变单元格），或通过公式间接依赖于可变单元格，规划求解可调整这组单元格来影响目标单元格。◆目标单元格服从一定的约束和限制。约束条件不同，结果就不同。◆可求解特定单元格的最大值或最小值或某个值。◆对一个问题可以求出多个解。1.1加载“规划求解”模块首先，打开Excel文件，进入表格界面，单击“工具（T）”，如果存在“规划求解”项目，说明已经加载（加载只需进行一次，以后如果不人为删除，就会保留在工具栏内），可直接使用。图1-1“加载宏”图如果不存在“规划求解”项目，单击“加载宏”，会出现如图1-1所示“加载宏”图框。单击“规划求解”，使复选框中出现对勾，再单击“确定”，即完成了加载（注：若在Office软件装入Excel求解运筹学问题方法简介–2–时，系统未选择该工具模块装入，此时会引导读者插入软件安装盘，依据系统提示操作即可）。1.2线性规划问题求解为了便于说明，以一个线性规划例题来说明这个过程。例1-1某工厂在计划期内要安排甲、乙两种产品的生产，已知生产单位产品所需成本分别为2千元和3千元；根据产品特性，产品总数不得少于350件，产品甲不得少于125件；又知生产这两种产品需要某种钢材，产品甲、乙每件分别需要钢材2t、1t，钢材的供应量限制在600t。问题：工厂应分别生产多少单位甲、乙产品才能使总成本最低？解：容易建立如下线性规划模型。设x1、x2分别为产品甲、乙的产量，模型为0,6002125350s.t.32min212112121xxxxxxxxxz(1)数据输入：如图1-2所示。图1-2数据输入界面在输入界面中，第1、4、7行是说明文字，无论输入什么内容或不输入均不会影响计算。其中，单元格B2、C2为决策变量初值，对于线性规划求解，初值可任取；B5、C5是目标函数系数；B8、C8、B9、C9、B10、C10为各约束函数的左端系数；E8、E9、E10为约束右端项。以上数据均直接从键盘输入。关键的是，E5、D8、D9、D10分别为目标函数和约束左端三个函数的计算公式。E5的输入如图1-3所示，见红色箭头指示。Excel求解运筹学问题方法简介–3–图1-3E5公式输入方式D8、D9、D10用同样的方式可以输入：=B8*B2+C8*C2，=B8*B2+C8*C2，=B8*B2+C8*C2。(2)调用“规划求解”模块：单击“工具（T）”栏的“规划求解”，得到图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）”只选“运算结果报告”和“敏感性报告”即可。单击“确定”。E5格的公式Excel求解运筹学问题方法简介–4–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当前值为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-1运输费用表销地产地B1B2B3B4产量/tA13113107A219284A3741059销量/t365620（产销平衡）问应如何调运，可使得总运输费最小?解：对这个典型的运输问题，可用“规划求解”来计算求解。首先，运输问题模型本身就是线性规划模型，因此在计算方面，只需注意在它的决策变量输入时，列成矩阵形式即可。图1-9是此问题的数据输入界面。B2到E4是运输费用矩阵，B10到E12是运输变量矩阵（这里输入的初值可以任取，此处全部取值是1），B7到E7是销地的销量限制，H10到H12是产地的Excel求解运筹学问题方法简介–5–产量限制。I3是目标函数，其公式输入同线性规划，如图1-9中所示；B14到E14、G10到G12分别为运往销地的约束函数、产地运出的约束函数。图1-9运输问题输入界面在“规划求解参数”界面，决策变量即运输变量可直接用矩阵输入，销量约束和产量约束均用向量形式表示，如图1-10所示。图1-10运输问题规划求解参数界面单击“求解（S）”后，即可得到问题的解和最优值，如图1-11所示。图1-11问题的解数据由图1-11，可得到此问题的解为：A1到B1运输2t、A1到B3运输5t、A2到B1运输1t、A2Excel求解运筹学问题方法简介–6–到B4运输3t、A3到B2运输6t、A3到B4运输3t，总费用85百元。1.4整数规划求解例1-3求解下列整数规划问题为整数2121212121,,0,401404041356273195s.t.32maxxxxxxxxxxxz解：对这个整数规划问题，可用“规划求解”来计算求解。输入与线性规划问题的输入完全一样，如图1-12所示。图1-12例1-3输入表格本问题的不同之处，就是在添加约束时，设置变量为“int”，则界面自动会显示“整数”，如图1-13所示。图1-13“添加约束”中的符号选择有5项，单击“▼”时即可显示“=,=,=,int,bin”，分别表示“小于等于，等于，大于等于，整数，二进制即0、1变量”。图1-13添加约束界面设置完成的“规划求解参数”界面如图1-14所示。设置过程总体上与线性规划问题求解输入设置没有差别，这里不再赘述。单击“确定”后，可得到计算结果：142421*z,x,xExcel求解运筹学问题方法简介–7–图1-14例1-3规划求解参数界面例1.3求解下列整数规划问题为整数变量、为31321321321321321,10,0,,102231284687105s.t.71015maxxxxxxxxxxxxxxxxxxz解：对这个混合整数规划问题，亦可用“规划求解”来计算求解。输入与线性规划问题的输入完全一样，如图1-15所示。图1-15例1-4输入数据表格然后，设这个计算参数，如图1-16所示。图1-16例1-4规划求解参数界面Excel求解运筹学问题方法简介–8–单击“确定”后，可得到计算结果：300511321*z,x,.x,x2排队论问题求解Excel在表格中可以使用公式，进一步还可以编程，因而可以求解大部分规范化的运筹学问题。本节介绍求解排队论问题的Excel文档，由于试用了“宏”，因此当系统提示是否启用宏时，请单击“启用宏”。2.1M/M/1模型(1)M/M/1//模型例2-1某公交一卡通充值站，有1个服务员，前来充值的顾客按泊松分布到达，平均每小时45人，每次充值服务的时间服从负指数分布，平均为1min。求：1)到达时，不需等待即可接收充值服务的概率p0；2)平均排队等待充值的和站内总的平均顾客数Lq、L；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//模型求解Excel求解运筹学问题方法简介–9–于是，我们得到解为：1)到达时，不需等待即可接收充值服务的概率p0=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一个小理发店只有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/1/N”表，如图2-2所示输入参数，即可得到有关的结果。图2-2M/M/1/4/模型求解Excel求解运筹学问题方法简介–10–于是，我们得到解：1)顾客到达时，不需等待即可理发的概率p0=0.3839；2)平均排队等待理发的和理发店内总的平均顾客数Lq=0.6256，L=1.2417；3)顾客为了理发等待和逗留的时间Wq=0.3385h=20.3077min，W=0.6718h=40.3077min；4)顾客到来需要等待的概率pW=0.6161，顾客损失率=0075850.e。2.2M/M/c模型(1)M/M/c//模型例2-3某公交一卡通充值站，有2个服务员，前来充值的顾客按泊松分布到达，平均每小时45人，每次充值服务的时间服从负指数分布，平均为1分钟。求：1)没有顾客充值服务的概率p0；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-3M/M/c//模型求解于是，得到解Excel求解运筹学问题方法简介–11–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/