Excel求解运筹学问题方法简介Excel求解运筹学问题方法简介Excel中的规划求解是功能强大的优化和资源配置工具它可以帮助人们求解运筹学中的许 多问题，特别是“规划求解”模块可以解决许多求极值、解方程的问题本附件除介绍“规划求解”模块的使用外，还提供给读者“排队论”与“存储论”基本模块1规划求解在使用“规划求解”时，首先需要“规划求解”出现在“工具”菜单中，如果没有，则需要 加载“规划求解”宏另外，目标函数和约束函数必须要给出公式，变量的约束必须作为约束条 件给出规划求解的特点：♦ 表格输入数据不能为分数，当遇到分数时，必须化为小数输入♦ 目标单元格依赖一组单元格（可变单元格） ，或通过公式间接依赖于可变单元格，规划求解可调整这组单元格来影响目标单元格♦ 目标单元格服从一定的约束和限制约束条件不同，结果就不同♦ 可求解特定单元格的最大值或最小值或某个值♦ 对一个问题可以求出多个解1.1 加载“规划求解”模块首先，打开Excel文件，进入表格界面，单击“工具（ T）”，如果存在“规划求解”项目，说明已经加载（加载只需进行一次，以后如果不人为删除，就会保留在工具栏内） ，可直接使用图1-1 “加载宏”图如果不存在“规划求解”项目，单击“加载宏” ，会出现如图1-1所示“加载宏”图框。
单击“规划求解”，使复选框中出现对勾，再单击“确定” ，即完成了加载（注：若在 Office软件装入 时，系统未选择该工具模块装入，此时会引导读者插入软件安装盘，依据系统提示操作即可）1.2 线性规划问题求解为了便于说明，以一个线性规划例题来说明这个过程例1-1某工厂在计划期内要安排甲、乙两种产品的生产，已知生产单位产品所需成本分别为2千元和3千元；根据产品特性，产品总数不得少于 350件，产品甲不得少于 125件；又知生产这两种产品需要某种钢材，产品甲、乙每件分别需要钢材 2t、1t,钢材的供应量限制在 600t问题：工厂应分别生产多少单位甲、乙产品才能使总成本最低？解：容易建立如下线性规划模型设 Xi、X2分别为产品甲、乙的产量，模型为mins.t.z = 2x1 3x2x1 x2 - 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为各约束函数的左端系数； 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所示，值得说明的是设置可变单 元格通过单击“推测（G）”来完成，设置约束通过单击“添加（ A）”来完成图1-5 规划求解选项框 图1-6 规划求解结果选择框在这一步要通过单击“选项（0）”来设置有关信息，单击“选项（0）”后可得到如图1-5所 示的框在这里只需单击“采用线性模型（ M）”即可完成，然后点“确定”，回到“规划求解参 数”框。
3）解线性规划模型：在“规划求解参数”框单击“求解（ S）”，得到如图1-6所示的“规划求解结果”框，其中“报告（R）”只选“运算结果报告”和“敏感性报告”即可单击“确定” Excel文档中产生两个新表：“运算结果报告”（图1-7）和“敏感性报告”（图1-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 ： - D E F G ?1161 oft Ried 9r0运算结果报告T停费［Bonkl ］ Shccl It第告的Jtt立：2003-9-3 6:89:83目标单元格1最小值） 一元帝名字 『一 坤值支件中 温强⑧ 观因 茹人中 格式值 ia 畜据口占白电自小/为喝的,42$15E7目_3_1111213网15比1713地道21Z223网-可变单元帮 华瑞名字整低 以国一一能 il 1 瓯― 1 ico约束单兀擀名字单元格使 公支麻型物值350 JDJS,>=JEt8到这跳制值^0，蟠260 jDt9/-tSi9排假期佰125SM10?LDjCOO JDJ1O<=JEJ1O到因鸵刍痼0犯能il250 JEJZ>=0未检腮制值250JCJ2t2100起晔口未生1闱制值1M二快译出英中 日申 中英设置.A1 ▼足 licrosoft EiceL 9.0 粒感性报告A ~y rT B -F G - E1 [ijcrosoft Excel 9. 0 瓶感性报告.作表[BDDkl]Sheet2告的建立：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=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,减少时可趋于负无穷，对偶价格（影子价格）不会变； b3当前值为600,当其他参数均不变时，它最多增加100,最多减少125时，对偶价格（影子价格）不会变1.3 运输问题求解例1-2某公司从三个产地 AP A2、A3将物品运往四个销地 B1、B2、B3、B4,各产地的产量、各 销地的销量和各产地运往各销地每件物品的运费（百元）如表 1-1所示表1-1运输费用表肖地 产地B8B3B4产量/tA3113107A19284A741059销量/t365620 （产销平衡）问应如何调运，可使得总运输费最小 ？解：对这个典型的运输问题，可用“规划求解”来计算求解。
首先，运输问题模型本身就是 线性规划模型，因此在计算方面，只需注意在它的决策变量输入时，列成矩阵形式即可图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 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求解下列整数规划问题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”，分别表示“小于等于，等于，大于等于，整数，二进制即 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 +2。