Excel规划求解的使用

1、Excel软件“规划求解”的使用用Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线性规划问题。但如果安装 Office 97时采用的是典型安装方法，则【工具】菜单中是 无“规划求解”功能项的。可参照2.8中介绍的方法将未安装的组件安装完整。下面以第八章例8.1为例介绍用Excel求解线性规划的操作步骤和运行输出结 果的分析。一.求解线性规划的操作过程1 .输入数据、公式和说明文字(1)在工作表中按图9.7所示格式输入必要的说明文字(图中粗体字部分)和LP模型的原始数据(图中虚线框所示单元格内，注意并不需要化为标准型)；图中F4是放置目标函数的单元格，B5:D5是放置决策变

2、量Xi、X2、X3 (既“可变单元 格”)的区域。2C crosoifl iExcel -定量法上视案例-工-! x|宣文件如编髓5视图5 抽入中格式 工具(D数揩如国口如 TOC o 1-5 h z 帮助呼U1M A |_B C _J= D J_ EF二例Bl：线性规划的一般求解方法、,匚 | 口产品甲为产品乙出产品丙为修4惮位利润：132引和词总额：|S 产量.O3d 012al3序能力瓦9InL 2 &序序序工工T.1 1L图9.7(2)在F4单元格内输入目标函数 Xo的计算公式：=B4*B5+C4*C5+D4*D5或 =SUMPRODUCT (B4:D4,B5:D5)其中SUMPROD

3、UCT ()函数返回两个或多个区域(即数组)中对应单元格乘 积之和的值。该函数可在 Excel的“数学和三角函数”中找到。(1)在E8单元格中输入第一个约束条件左端的计算公式:=B8*$B$5+ C8*$C$5+D8*$D$5或 =SUMPRODUCT (B8:D8,$B$5:$D$5)然后拖曳E8的填充柄将公式复制到 E9、E10单元格(注意公式中的B5、C5、D5 或B5:D5要使用绝对引用)。当模型中的变量数较多时，使用 SUMPRODUCT ()函数可大大加快以上两个 公式的输入速度。说明：图中粗线框是表示要输入公式的单元格。用 Excel求解线性规划的数据 输入格式可由用户自行设计，

4、但以上介绍的格式不仅与我们所熟悉的 LP模型相似， 便于理解和使用；而且便于在对话框中输入约束条件。 按以上格式输入说明文字后， 还可以使系统所输出的三个运行结果报告更具可读性。.选【工具】一 “规划求解”，“打开规划求解参数”对话框，见图 9.8。图9.8(1)在“设置目标单元格”文本框中输入目标单元格(建议用鼠标选定的方法 输入，下同)，并选系统默认的“最大值”单选纽；(2)在“可变单元格”文本框中输入B5:D5 (既指定决策变量所在的单元格).单击“约束”框中的添加按纽，打开“添加约束”对话框，见图 99萍加约束单元格引用位置约束值:|SE16:ES10|=0 （方法同上）；说明：对有整

5、数约束要求的决策变量，应再将相应的可变单元格设为 “int ”约 束（即求解纯整数规划或混合整数规划问题）；对模型中的0-1变量，则应再将相应 的可变单元格设为“ bin”约束（即二进制变量）。（3）单击确定，返回“规划求解参数”对话框，见图 9.10。图 9.101.单击选项按纽，打开“规划求解选项”对话框，见图 9.11现划求解选项何采用嬲性模型也 r假定非负r自劭按比例得触也 r显示退低结果估计1导数1费索行正切函数6向前差分口汗牛顿法r二次方程r中心差分r共辗法也图 9.119.12选“采用线性模型” 复选框（选择该选项后，系统将使用单纯形法求解，否则 就使用非线性规划的算法求解，不仅

6、速度慢，精度低，而且输出的敏感性报告内容 也少）。单击确定，系统运算结束后打开“规划求解结果”对话框，见图图 9.124.在“报告”列表框中选定所有3个报告，并单击确定，系统在工作表中显示运行结果并生成3个输出报告的工作表，见图9.13案例直1手知文件如 编指理 观图值插kcd格式。1am 盈拈如 窗口 帮助如型JX例日.1：线性规划的一般求解方法产品甲X1产品乙12247单位利润.1产里100产品丙修司前嗣总额. 法01350包务工序能力hl2:3:序序序工匚工0110143 6 24 g 42JJ14 4 乂敏感样孤告/根限值差告1 Ml./运|1|图 9.13二.输出结果分析由图9.13

7、,可知本问题的最优解为：Xi*=0, X2*=100, X3*=230, X0*=1350即每天生产乙产品100件，丙产品230件，不生产甲产品，每天总利润为 1350元。 在以上最优生产方案下，工序1和2的能力都已用完，而工序3则每天还有20分钟 的剩余能力（仅用去400分钟）。系统输出的“运算结果报告”、“敏感性报告”和“极限值报告”三张工作表分别见图9.14、图9.15、图9.16I X Hi eroEfflfi-定量法上机案例11=【匚1刈为文件胆）偏辑旧视图中插入格式：1具数据囱口如帮助 比凶ABCDEFG6目可约标单元格（最大值）7S单元格$F$4名字初值利涧忌甑：XQ0辘1350

8、g10变单元格11单兀格名字初值终值12$B$5产量：产品甲XI0013$C$5产量：产品乙X2010014$D$5产量：产品丙X302301616束17单元格名字单元格值公式状态 型数值ISJE$8工序 1 : EaijXj430褪仁$F$8 到达限取值019$E$9工序2: SaijXj460p9=0到达限制值0二 223icjs产量：产品乙X2100 1C$5=0未到限制值100$D?5产量：产品丙应230 |D$5=0将限制值 230看起A会与宜一希成特招,松限值相的八|f川图 9.14图9.14所示“运算结果报告”中三道工序约束的“型数值”给出的是最优解中 松弛变量的值，即三道工序时

9、间的富裕量。图 9.15图9.15所示“敏感性报告”中“可变单元格”下的“递减成本”给出的是最优 单纯形迭代中各决策变量检验数的负值；”允许的增量”和“允许的减量”则给出了 在不影响当前最优基的条件下各决策变量目标函数系数Cj的可变动范围；而“约束”下的“阴影价格”则给出了各种资源的“影子价格”,其含义将将在第十章中介绍“允许的增量”和“允许的减量”则给出了在不影响当前最优基的条件下各有限资 源数量（约束条件右端常数）的可变动范围。以上敏感性分析的内容将在第十一章 中详细介绍。EkcwI 定量法上机塞例kIeTTriTx刊文件 编辑如 视图9 播人9 格式工具G 数据皿 囱口处）帮助-|g|

10、X|A67891011121314目标式 单元格名字值 $F$4 利润兑1350变量 单元格名字值 $8$5 产量： 0 iC15 _尸亳 100 $D$5 产量：230下限目标式 极限结果013500H500200上限目标式 极限结果Q 由100135023013501 JJif 7 工1认敏感性弗告限值能占1 ：例Ml图 9.16图9.16所示“极限值报告”给出了各决策变量分别取下限和上限值时对目标函 数的影响。三.关于“规划求解选项”对话框中各可选项的说明：“最长运算时间”一一可输入的最大值为 32767 （秒），默认值100 （秒）可以 满足大多数小型规划求解要求。“迭代次数”一一设定

11、求解过程中迭代运算的次数。可输入的最大值为32767, 默认值100次，可满足大多数小型规划求解要求。“精度”一一输入用于控制求解精度的数字，以确定约束条件单元格中的数值 是否满足目标值或上下限。默认值为 0.000001。“允许误差”一一输入满足整数约束条件的目标单元格求解结果与最佳结果问的允许百分偏差。此选项只应用于具有整数约束条件的问题。默认值为5%0“收敛度”一一输入收敛度数值，当最近五次迭代时，目标单元格中数值的变 化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只应用于 非线性规划问题，默认值为0.001“采用线性模型”一一当模型中的所有关系都是线性的，并且希望解决线性优 化问题或对非线性问题进行线性逼近时，选中此复选框可加速求解进程。“显示迭代结果”一一如果选中此复选框，每进行一次迭代后都将中断“规划求解”，并显示当前的迭代结果。“自动按比例缩放”一一当输入和输出值数量差别很大时，可以使用此功能。“假定非负”一一对于在“添加约束”对话框的“约束值”编辑框中没有设置 下限的