实验1用Excel求解线性规划模型.docx

实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大,而且没有较高的数学基础很难理解其计算过程和 方法，但是借助Excel “规划求解〃工具，就能轻而易举地求得结果Excel最多可解200个变 量、600个约束条件的问题下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具 体的经济决策问题一、实验目的1、 掌握如何建立线性规划模型2、 掌握用Excel求解线性规划模型的方法3、 掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变 化对最优方案产生的影响4、 读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告.二、实验内容1、 ［工具］［规划求解］命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制 安装”时才可选择装入这个模块•在安装完成进入Excel后还要用［工具］［加载宏］命令选中 “规划求解〃‘以后在［工具］菜单下就增加了一条［规划求解］命令使用［规划求解］命令的一般步骤为：第一步：在选取［工具］［规划求解］命令后，弹出图1所示“规划求解参数”对话框，其 中各选项说明如表1图1 “规划求解参数”对话框 表1 “规划求解参数”对话框选项选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择•如利润问题，选取“最大值”可变单兀格决策变量所在各单兀格、不含公式，可以有多个区域或单兀格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填 入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解图2约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。

x|确定1取消潘加⑥帮助QD」单元格引用位置夠束值© :K7 |C= 〒 | |=SEf3：SEf5图3第二步:完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项” 对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定〃按钮回到图1图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）縱号找到-珮可满足溜拋束髓忧*保存规划求辭结某蓟 广恢SSii尬报告⑥I运算结果报告刁 敏感性报告—1 极限值报告I确定I 取消I恨存方案⑤…I 帮助⑪图5第四步：在图5中单击“确定”按钮，则只将优化计算结果显示在表格设置中的可变单元 格（决策变量）和目标单元格（目标函数）内在图5的“报告”框中有3个选项，每个选项对应着一个报告，各报告以单一工作表记载, 它们不仅能给出优化结果，甚至还给出更重要信息，例如影子价格等2、 产品生产品种结构优化问题数学模型示例：一家制药厂生产两种产品：药品I和药品II每个产品要用到一种相同的原料A, 并要经过一道相同的工序，在机器B上包装因为这两种产品可以使用同样的机器，所以它们可以轮换使用设备，从而使其生产设施得到较充分的利用. 表2药品和药品的售价、可变成本和贡献药品销售价（元）可变成本（兀）对利润的贝献I35030050II450350100表3两种药品在机器上加工两种产品的时间以及原材料A和B限制药品原料A（千克）机器B （小时）原料C （千克）I210II111资源限制400300250问该制药厂应该如何安排生产计划才能使企业的利润最大。

我们知道，如果分别设药品I和药品II的生产数量为xl和x2,那么该问题的线性规划 模型如下：max Z = 50x +100x1 2‘2x + x < 400（原料A）1 2x + x < 300（机器B）1 2s.t. 0（药品I的最低产量）1x > 0（药品II的最低产量）I 2表格设置与公式说明根据本问题的规模和条件，拟设置如表1中A1 : E8所示形式:⑴区域B3 : C6和E3 : E5为原始数据区，输入如表1中所示的原始数据.表4ABCDE1线庄规划模型2产品1产品2现有资源3原料A2104004机器E1103005原料C0102506利润501007决策变量0 08最大刑润⑵在单元格B8内输入数学模型中目标函数的计算公式，并求最大值⑶单元格B7 : C7分别作为药品I和药品II的产量（即决策变量xl、x2）,即可变单元格 其初始值设为0,求解过程中计算机会自动输入各组试验值.⑷区域D3 : D5内的各单元格依次输入三个约束条件对应式的左侧部分操作步骤第一步：选择［工具］［规划求解］命令，弹出图1所示对话框根据本问题的性质，在 “设置目标单元格〃文本框内填入$B$ 8,在“等于”选项后选取“最大值〃,在“可变单元格” 文本框内填入$B$7 ：$C$7.第二步：单击“添加”按钮，弹出图2所示对话框。

该步骤的任务是要把前面数学模型 中的全部约束条件一个一个地填入图1所示的“约束”列表框内.图2所示就是填入三个资源 约束条件的情形：在左边“单元格引用位置”文本框内填入Q$3 ：$D$5 （可直接录入、或 用鼠标拖入）、单击中间向下小箭头并选取符号'<=”、在右方“约束值”文本框内录入5E $3：$E$5,也可以录入数字400,300, 250,最后单击“确定”按钮或回车键，回到图1 这样就完成了约束条件$D$3：$D$5〈=$E$3：$E$5的录入第三步：重复第二步，录入$B$7：$C$7〉=0，即两决策变量的值必须大于0,最后如图 1所示.第四步:在图1中单击“选项〃按钮，弹出图4对话框因本例题属于线性规划问题，选取 “采用线性模型”按钮，再单击“确定”按钮，回到图1第五步:在图1中选取“求解〃按钮或击回车键，Excel进入规划求解运行过程，屏幕左下 角状态条上逐次显示运行过程报告一旦计算结束，弹出图5的对话框在图5内可以有四种 选择：⑴若单击“确定”按钮或击回车键，则显示如表2的结果可变单元格$B$7 : $C$7内显 示最优生产计划，即药品I生产50件和药品II生产100件，可获得最大利润27500元；单元格 $D$3 : $D$5分别给出了各种资源的用量，只有原料A有50千克的剩余.表5ABCDE1线性规划2产品1产品2现有资源3原料直213F04004机器E113003005原料C01250250$刃17决策变量502508最大利润27500)⑵若选择“运算结果报告”，Excel显示“运算结果报告〈n〉”，其中〈n〉表示求解本问 题中已经连续第几次选择该选项,我们这里给出的是“运算结果报告1”（如表6），即在同一 文件内首次选择该选项•表6比较全面地报告了优化结果信息，包括目标单元格的位置、名称、 初值和终值，可变单元格的位置、名称、初值和终值，约束单元格位置、名称、单元格内计 算结果、单元格相应约束式、运算结果达到的状态（型数值为0表示到达限制值、否则未到 达限制值）。

⑶若选择“敏感性报告”，Excel显示“敏感性报告〈n〉”（如表7）敏感性报告表的限 制式中“阴影价格〃（经济学中称影子价格、Shadow Price）是一个有特别意义的经济指标.表6Microsoft Excel 9.0运算结果报告工作表[习题一.Xis] Sheet2报告的建立： 2006—8-24 19:22:29目标单元格（最大值）单元格名字初值终值＄B$8目标函数027500可变单兀格单元格名字初值终值$B$7决策变量产品1050$C$7决策变量产品20250约束单元格名字单元格值公式 状态型数值$D $ 3〈= $ E＄D$3原料A350 $3未到限制值50＄D$4机器B300$D$4〈=$E$4到达限制值0$D$5<=$D$5原料C250 $E$5 到达限制值0＄B$7决策变量产品150 $B$7>=0 未到限制值50＄C＄7决策变量产品2250$C$7>=0 未到限制值250影子价格的经济学意义是，使在最优利用下的紧缺资源增加1个单位，将为企业创造的利润 用影子价格与各紧缺资源的市场价格相比较，可以为企业是否购买紧缺资源扩大生产提供决 策依据.有剩余的资源影子价格为0.表7Microsoft EXcei 9. 0敏感性报告工作表[习题一.xls]Sheet2报告的建立: 2006-8-25 11：01:22可变单元格单元格名字终 值递减 成本目标式 系数允许的 增量允许的 减量$B$7决策变量产品1500505050$C$7决策变量产品225001001E+3050约束单元格名字终 值阴影 价格约束 限制值允许的 增量允许的 减量$D$3原料A35004001E+3050$D$4机器B300503002550$D$5原料C250502505050⑷若选择“极限值报告”，Excel显示“极限值报告〈n〉”（如表8）。

除了给出最优决策对应最优目标值信息外,还显示各决策变量的上、下限值及其对应目标式结果. 表8Microsoft Excel 90 极限值报告工作表［习题一.xls］ Sheet2报告的建立： 2006—8—25 11:02:45目标式单元格 名字 值$B$8最大利润 27500变量单元格 名字值下限 极限目标式结果上限 极限目标式结果$B$7决策变量产品1500250005027500$C$7决策变量产品225002500249.9999999 27499999993、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告利用Excel求解线性规划问题系统将提供三个计算结果报告，即运算结果报告、敏感性报 告、极限值报告.这三个报告中的前两个报告非常重要,下面我们将结合教材第二章线性规划 的对偶理论与灵敏度分析的内容讲述如何看Excel求解线性规划问题输出的运算结果报告和 敏感性报告⑴读懂运算结果报告运算结果报告比较容易看懂，可变单元格$B$7和$7分别表示两个决策变量，即 药品I和药品II的产量，在计算时，由于我们最初赋予x = 0,x = 0，所以单元格$B$7和12$C$7的初值为0,求得最优解后，$B$7和$。

7的值分别为50和250,即x 。