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

1、实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel “规划求解”工具，就能轻而易举地求得结果。Excel最多可解200个变量、600个约束条件的问题。下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。一、实验目的1、掌握如何建立线性规划模型。2、掌握用Excel求解线性规划模型的方法。3、 掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变 化对最优方案产生的影响。4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。二、实验内容1、工具规划求解命

2、令规划求解加载宏是Excel的一个可选安装模块，在安装 Excel时，只有在选择“完全/定 制安装”时才可选择装入这个模块。在安装完成进入Excel后还要用工具加载宏命令选中“规划求解”，以后在工具菜单下就增加了一条规划求解命令。使用规划求解命令的一般步骤为：第一步：在选取工具规划求解命令后，弹出图1所示“规划求解参数”对话框，其 中各选项说明如表1。图1 “规划求解参数”对话框 表1 “规划求解参数”对话框选项选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单兀格等于按问题目标进行选择。如利润问题，选取“最大值”可变单兀格决策变量所在各单兀格、不含公式，可以有多个区域或单兀格

3、约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选 项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。?I X1舉證解阔V何满足所有的细施饶癖®极卩艮值报告确定1取消1保存方案.图

4、5第四步：在图5中单击“确定”按钮，则只将优化计算结果显示在表格设置中的可变单 元格（决策变量）和目标单元格（目标函数）内。在图5的“报告”框中有3个选项，每个选项对应着一个报告，各报告以单一工作表记载，它们不仅能给出优化结果，甚至还给出更重要信息，例如影子价格等。2、产品生产品种结构优化问题数学模型示例：一家制药厂生产两种产品： 药品I和药品n。每个产品要用到一种相同的原料 A,并要经过一道相同的工序，在机器B上包装。因为这两种产品可以使用同样的机器，所以它们可以轮换使用设备，从而使其生产设施得到较充分的利用。表2药品和药品的售价、可变成本和贡献药品销售价（元）可变成本（兀）对利润的贡献I3

5、5030050n450350100表3两种药品在机器上加工两种产品的时间以及原材料A和B限制药品原料A （千克）机器B （小时）原料C （千克）I210n111资源限制400300250x1和x2，那么该问题的线性规划问该制药厂应该如何安排生产计划才能使企业的利润最大。 我们知道，如果分别设药品I和药品n的生产数量为 模型如下：max Z = 50xi 100x22论+x2兰400（原料A） xx2 <300（机器 B） s.tX2 兰 250（原料 C）冷色0（药品I的最低产量） X2 _0（药品II的最低产量） 表格设置与公式说明根据本问题的规模和条件，拟设置如表1中A1 : E8所

6、示形式：区域B3： C6和E3： E5为原始数据区，输入如表 1中所示的原始数据。BCD12345ABC料器料原机原6利润7决策变量8最大轴闻线性规划模型产品2现有资源11150E1000在单元格B8内输入数学模型中目标函数的计算公式，并求最大值。单元格B7： C7分别作为药品I和药品H的产量（即决策变量x1、x2）,即可变单元格。其初始值设为0,求解过程中计算机会自动输入各组试验值。区域D3： D5内的各单元格依次输入三个约束条件对应式的左侧部分。操作步骤第一步：选择工具规划求解命令，弹出图1所示对话框。根据本问题的性质，在“设 置目标单元格”文本框内填入 $B$8,在“等于”选项后选取“最

7、大值”，在“可变单元格” 文本框内填入$B$7 : $C$7。第二步：单击“添加”按钮，弹出图 2所示对话框。该步骤的任务是要把前面数学模型 中的全部约束条件一个一个地填入图1所示的“约束”列表框内。图2所示就是填入三个资源约束条件的情形：在左边“单元格引用位置”文本框内填入$D$3： $D$5（可直接录入、或用鼠标拖入）、单击中间向下小箭头并选取符号“<=”、在右方“约束值”文本框内录入 $E$3:$E$5,也可以录入数字400, 300, 250,最后单击“确定”按钮或回车键，回到图1。这样就完成了约束条件 $D$3： $D$5<=$E$3： $E$5的录入。第三步：重复第二步

8、，录入$B$7 : $C$7>=0,即两决策变量的值必须大于 0,最后如图1 所示。第四步：在图1中单击“选项”按钮，弹出图 4对话框。因本例题属于线性规划问题，选 取“采用线性模型”按钮，再单击“确定”按钮，回到图 1。第五步：在图1中选取“求解”按钮或击回车键，Excel进入规划求解运行过程，屏幕左下角状态条上逐次显示运行过程报告。一旦计算结束，弹出图5的对话框。在图5内可以有四种选择：若单击“确定”按钮或击回车键，则显示如表 2的结果。可变单元格 $B$7： $C$7内显 示最优生产计划，即药品I生产50件和药品H生产100件，可获得最大利润27500元；单元格 $D$3： $D$

9、5分别给出了各种资源的用量，只有原料 A有50千克的剩余。表5现有资源1线性规划ABC DE4 5ABC 变利 料蛊料润策大 原机原利决最2 10 0 05 5135013001250若选择“运算结果报告”，Excel显示“运算结果报告<n>”,其中<n>表示求解本问题中已经连续第几次选择该选项，我们这里给出的是 “运算结果报告1 ”（如表6）,即在同一文件内首次选择该选项。表6比较全面地报告了优化结果信息，包括目标单元格的位置、名称、初值和终值，可变单元格的位置、名称、初值和终值，约束单元格位置、名称、单元格内计 算结果、单元格相应约束式、运算结果达到的状态（型数值为

10、0表示到达限制值、否则未到达限制值）。若选择“敏感性报告”，Excel显示“敏感性报告<n>”（如表7）。敏感性报告表的限制式中“阴影价格”（经济学中称影子价格、Shadow Price ）是一个有特别意义的经济指标。表6Microsoft Excel 9.0 运算结果报告 工作表习题一 .xlsSheet2 报告的建立：2006-8-24 19:22:29目标单元格（最大值）单元格名字初值终值$B$8目标函数027500可变单元格单元格名字初值终值$B$7决策变量产品1050$C$7决策变量产品20250约束单元格名字单元格值公式状态型数值$D$3原料A350 $D$3<=

11、$E$3未到限制值50$D$4机器B300 $D$4<=$E$4到达限制值0$D$5原料C250 $D$5<=$E$5到达限制值0$B$7决策变量产品150$B$7>=0未到限制值50$C$7决策变量产品2250 $C$7>=0未到限制值250影子价格的经济学意义是，使在最优利用下的紧缺资源增加1个单位，将为企业创造的利润。用影子价格与各紧缺资源的市场价格相比较，可以为企业是否购买紧缺资源扩大生产提供决策依据。有剩余的资源影子价格为0。表7Microsoft Excel 9.0 敏感性报告 工作表习题一 .xlsSheet2 报告的建立:2006-8-25 11:01:

12、22可变单元格单元格名字终值递减成本目标式 系数允许的增量允许的减量$B$7决策变量产品1500505050$C$7决策变量产品225001001E+3050约束终阴影约束允许的允许的单元格名字值价格限制值增量减量$D$3原料A35004001E+3050$D$4机器B300503002550$D$5原料C250502505050若选择“极限值报告”，Excel显示“极限值报告<n>”（如表8）。除了给出最优决策 对应最优目标值信息外，还显示各决策变量的上、下限值及其对应目标式结果。表8Microsoft Excel 9.0极限值报告工作表习题一 .xlsSheet2报告的建立：2

13、006-8-25 11:02:45单元格目标式名字值$B$8最大利润27500单元格变量名字值下限极限目标式结果上限极限目标式结果$B$7决策变量产品1500250005027500$C$7决策变量产品225002500249.999999927499.999993、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告利用Excel求解线性规划问题系统将提供三个计算结果报告，即运算结果报告、敏感性 报告、极限值报告。这三个报告中的前两个报告非常重要，下面我们将结合教材第二章线性规划的对偶理论与灵敏度分析的内容讲述如何看Excel求解线性规划问题输出的运算结果报告和敏感性报告。读懂运算结

14、果报告运算结果报告比较容易看懂，可变单元格$B$7和$C$7分别表示两个决策变量，即药品I和药品H的产量，在计算时，由于我们最初赋予x, =0,x2 =0，所以单元格$B$7和$C$7的初值为0,求得最优解后，$B$7和$C$7的值分别为50和250，即x 50, x 250，表示使目标函数值最大的计划是生产50个单位的药品I和 250个单位的药品H。目标单元格$B$8表示目标函数 max z =50捲 100x2，由于我们最初赋予 x, =0,x2 =0，所以目标函数的初值为0,求得最优解后，目标函数的值为27500，即，如果生产50个单位的药品I和250个单位的药品n能使企业利润达到最大值

15、27500元。在单元格$D$3、$D$4$D$5我们分别输入了三个约束条件的左边项，即2xi * X2 ,X2和X2，随后在使用工具规划求解时，在规划求解参数窗口，我们输入了$D$3 _$E$3,$D$4 _$E$4,$D$5 _$E$5 ,而$E$3=400、$E$4=300、$E$5=250,从而完成了约束条件的输入。当求得最优解x, = 50,x2 =250后，将x, =5Qx2 =250弋入约束方程得:$。$3=2为屜=：350$E$3，未达到限制值，型数值为 50；$D$4 =论 x2 =300 =$E$4 ,达到限制值，型数值为 0; $D$5 =X2 =250 =$E$5，达到限

16、制值，型数值为 0。读懂敏感性报告 可变单元格$B$7、$C$7分别表示两个决策变量 x1禾口 x2,在求得最优解后xi =50,X2 =250，即敏感性报告中所显示的$B$7、$C$7的终值分别为50和250。Xi在目标函数中的系数为 50,允许的增量为50,允许的减量为50,意思是当x1在目标函数 中的系数在50的基础上再增加50或减少50，该线性规划问题的最优解不变，即在其他条 件不变的情况下，当0乞c,乞100时，洛=50, x2 =250仍然是线性规划问题的最优解。同 理，X2在目标函数中的系数为 100，允许的增量为1E+30,允许的减量为50,意思是当X2 在目标函数中的系数在

17、100的基础上再增加1E+30或减少50,该线性规划问题的最优解不 变，即在其他条件不变的情况下，当C2 _50时，Xi =50恥=250仍然是线性规划问题的最优解。当求得最优解后，单元格$D$3、$D$4、$D$5的值分别为350, 300和250,即50个单位的药品I和 250个单位的药品H,消耗了原料A350千克、机器B300小时，原料C250 千克。由于企业拥有的原料 A、机器B,原料C的数量分别是：400千克300小时和250千 克，所以生产50个单位的药品I和 250个单位的药品H消耗掉了企业的拥有的全部机器B设备时数和原料 C,但原料A还有50千克的剩余。三种资源的阴影价格分别为

18、0、50、50,即该线性规划问题的对偶问题的最优解为yi =0, y2 =50, y3 =50意思是：保持其他生产条件不变， 每增加1个单位的原料 A使目标 函数增加的数量为 0,每增加1个小时的设备B使目标函数增加的数量为 50,每增加1个单 位的原料C使目标函数增加的数量为 50。约束限制值分别为 400、300和250,即企业拥有的原料 A、机器B,原料C的数量分别 是：400千克300小时和250千克。原料A的允许的增量为1E+30,允许的减量为50,意思 是原料A在现有数量的基础上再增加1E+30或减少50,原料A的影子价格不变，即在其他条件不变的情况下，当 d _350时，y 0。

19、同理可得：当250 _ b2 - 325时，y2 = 50 ； 当200乞b2 - 300时，y3 =50。课外练习1、利用Excel试算教材后面的习题，然后与手工计算的结果进行对比。2、练习利用线性规划模型制订总体计划。例：红西红柿公司是一家园艺工具生产商，公司主要是将购进的原材料制造成多用途的园艺工具。由于生产线需要的工具和场地是有限定的，红西红柿公司的生产能力主要由劳动力人数决定。该公司的产品需求季节性很强，需求最旺的时间在春季。该公司决定利用总体计划来克服需求季节性变动的障碍，同时实现利润最大化。公司的选择是，在淡季建立库存，当旺季到来时增加工人，签订转包合同，交积 压订单登记入册，以

20、后再将产品送达顾客。为了知道如何利用总体计划中的这些选择，“红西红柿”供应链的副总裁从预测下6个月的需求着手工作，如表 9所示。该公司以40美元的单价销售其工具。公司在1月的库存为1000个工具，在1月初有80名员工。每个月每条生产线有 20个工作日，每个工人每小时收入 4美元。每个工人每天工作 8 小时，其余时间休息。如前所述，生产线的生产能力主要取决于工人工作总时数。所以，机 器的生产能力并不限制生产线的生产能力。根据劳动法规定，工人每月加班不能超过10小时。各种成本如表10所示。目前，红西红柿公司在转包合同、库存缺货或积压上没有什么限制。所有库存缺货被积累起来，由下一个月生产出来的产品来满足。库存成本在当月库存结清时才计入。供应链管