《实验1用Excel求解线性规划模型Word版.doc》由会员分享，可在线阅读，更多相关《实验1用Excel求解线性规划模型Word版.doc（12页珍藏版）》请在三一文库上搜索。
1、传播优秀Word版文档 ，希望对您有帮助，可双击去除！实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。Excel最多可解200个变量、600个约束条件的问题。下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。一、实验目的1、 掌握如何建立线性规划模型。2、 掌握用Excel求解线性规划模型的方法。3、 掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。4、 读懂Excel求解线性规划问题输出的
2、运算结果报告和敏感性报告。二、 实验内容1、 工具规划求解命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。在安装完成进入Excel后还要用工具加载宏命令选中“规划求解”，以后在工具菜单下就增加了一条规划求解命令。使用规划求解命令的一般步骤为：第一步：在选取工具规划求解命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。图1“规划求解参数”对话框表1“规划求解参数”对话框选项选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。如利润问题，选取“最大值”可变单元格决策变
3、量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件传播优秀Word版文档 ，希望对您有帮助，可双击去除！选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话
4、框（图5）。传播优秀Word版文档 ，希望对您有帮助，可双击去除！图5第四步：在图5中单击“确定”按钮，则只将优化计算结果显示在表格设置中的可变单元格（决策变量）和目标单元格（目标函数）内。在图5的“报告”框中有3个选项，每个选项对应着一个报告，各报告以单一工作表记载，它们不仅能给出优化结果，甚至还给出更重要信息，例如影子价格等。2、 产品生产品种结构优化问题数学模型示例：一家制药厂生产两种产品：药品和药品。每个产品要用到一种相同的原料A，并要经过一道相同的工序，在机器B上包装 。因为这两种产品可以使用同样的机器，所以它们可以轮换使用设备，从而使其生产设施得到较充分的利用。表2 药品和药品的售
5、价、可变成本和贡献药品销售价（元）可变成本（元）对利润的贡献35030050450350100表3 两种药品在机器上加工两种产品的时间以及原材料A和B限制药品原料A（千克）机器B（小时）原料C（千克）210111资源限制400300250问该制药厂应该如何安排生产计划才能使企业的利润最大。 我们知道，如果分别设药品和药品的生产数量为x1和x2，那么该问题的线性规划模型如下：表格设置与公式说明根据本问题的规模和条件，拟设置如表1中A1E8所示形式：区域B3C6和E3E5为原始数据区，输入如表1中所示的原始数据。表4传播优秀Word版文档 ，希望对您有帮助，可双击去除！在单元格B8内输入数学模型中
6、目标函数的计算公式，并求最大值。单元格B7C7分别作为药品和药品的产量（即决策变量x1、x2），即可变单元格。其初始值设为0，求解过程中计算机会自动输入各组试验值。区域D3D5内的各单元格依次输入三个约束条件对应式的左侧部分。操作步骤第一步：选择工具规划求解命令，弹出图1所示对话框。根据本问题的性质，在“设置目标单元格”文本框内填入$B$8,在“等于”选项后选取“最大值”，在“可变单元格”文本框内填入$B$7$C$7。第二步：单击“添加”按钮，弹出图2所示对话框。该步骤的任务是要把前面数学模型中的全部约束条件一个一个地填入图1所示的“约束”列表框内。图2所示就是填入三个资源约束条件的情形：在左
7、边“单元格引用位置”文本框内填入$D$3$D$5（可直接录入、或用鼠标拖入）、单击中间向下小箭头并选取符号“=”、在右方“约束值”文本框内录入$E$3$E$5，也可以录入数字400，300，250，最后单击“确定” 按钮或回车键，回到图1。这样就完成了约束条件$D$3$D$5=0，即两决策变量的值必须大于0，最后如图1所示。第四步：在图1中单击“选项”按钮，弹出图4对话框。因本例题属于线性规划问题，选取“采用线性模型”按钮，再单击“确定”按钮，回到图1。第五步：在图1中选取“求解”按钮或击回车键，Excel进入规划求解运行过程，屏幕左下角状态条上逐次显示运行过程报告。一旦计算结束，弹出图5的对
8、话框。在图5内可以有四种选择：若单击“确定”按钮或击回车键，则显示如表2的结果。可变单元格$B$7$C$7内显示最优生产计划，即药品生产50件和药品生产100件，可获得最大利润27500元；单元格$D$3$D$5分别给出了各种资源的用量，只有原料A有50千克的剩余。表5若选择“运算结果报告”，Excel显示“运算结果报告”，其中表示求解本问题中已经连续第几次选择该选项，我们这里给出的是“运算结果报告1”（如表6），即在同一文件内首次选择该选项。表6比较全面地报告了优化结果信息，包括目标单元格的位置、名称、初值和终值，可变单元格的位置、名称、初值和终值，约束单元格位置、名称、单元格内计算结果、单
9、元格相应约束式、运算结果达到的状态（型数值为0表示到达限制值、否则未到达限制值）。若选择“敏感性报告”，Excel显示“敏感性报告”（如表7）。敏感性报告表的限制式中传播优秀Word版文档 ，希望对您有帮助，可双击去除！“阴影价格”（经济学中称影子价格、Shadow Price）是一个有特别意义的经济指标。表6Microsoft Excel 9.0 运算结果报告工作表 习题一.xlsSheet2报告的建立: 2006-8-24 19:22:29目标单元格 (最大值)单元格名字初值终值$B$8目标函数027500可变单元格单元格名字初值终值$B$7决策变量 产品1050$C$7决策变量 产品20
10、250约束单元格名字单元格值公式状态型数值$D$3原料A350$D$3=$E$3未到限制值50$D$4机器B300$D$4=$E$4到达限制值0$D$5原料C250$D$5=0未到限制值50$C$7决策变量 产品2250$C$7=0未到限制值250影子价格的经济学意义是，使在最优利用下的紧缺资源增加1个单位，将为企业创造的利润。用影子价格与各紧缺资源的市场价格相比较，可以为企业是否购买紧缺资源扩大生产提供决策依据。有剩余的资源影子价格为0。表7Microsoft Excel 9.0 敏感性报告工作表 习题一.xlsSheet2报告的建立: 2006-8-25 11:01:22可变单元格终递减目
11、标式允许的允许的单元格名字值成本系数增量减量$B$7决策变量 产品1500505050$C$7决策变量 产品225001001E+3050约束终阴影约束允许的允许的单元格名字值价格限制值增量减量$D$3原料A35004001E+3050$D$4机器B300503002550传播优秀Word版文档 ，希望对您有帮助，可双击去除！$D$5原料C250502505050若选择“极限值报告”，Excel显示“极限值报告”（如表8）。除了给出最优决策对应最优目标值信息外，还显示各决策变量的上、下限值及其对应目标式结果。表8Microsoft Excel 9.0 极限值报告工作表 习题一.xlsSheet
12、2报告的建立: 2006-8-25 11:02:45目标式单元格名字值$B$8最大利润 27500变量下限目标式上限目标式单元格名字值极限结果极限结果$B$7决策变量 产品1500250005027500$C$7决策变量 产品225002500249.999999927499.999993、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告利用Excel求解线性规划问题系统将提供三个计算结果报告，即运算结果报告、敏感性报告、极限值报告。这三个报告中的前两个报告非常重要，下面我们将结合教材第二章线性规划的对偶理论与灵敏度分析的内容讲述如何看Excel求解线性规划问题输出的运算结果报告和
13、敏感性报告。读懂运算结果报告运算结果报告比较容易看懂，可变单元格$B$7和$C$7分别表示两个决策变量，即药品和药品的产量，在计算时，由于我们最初赋予，所以单元格$B$7和$C$7的初值为0，求得最优解后，$B$7和$C$7的值分别为50和250，即，表示使目标函数值最大的计划是生产50个单位的药品和250个单位的药品。目标单元格$B$8表示目标函数，由于我们最初赋予，所以目标函数的初值为0，求得最优解后，目标函数的值为27500，即，如果生产50个单位的药品和250个单位的药品能使企业利润达到最大值27500元。在单元格$D$3、$D$4、$D$5我们分别输入了三个约束条件的左边项，即，和，
14、随后在使用工具规划求解时，在规划求解参数窗口，我们输入了传播优秀Word版文档 ，希望对您有帮助，可双击去除！,而$E$3=400、$E$4=300、$E$5=250,从而完成了约束条件的输入。当求得最优解后,将代入约束方程得：，未达到限制值，型数值为50；，达到限制值，型数值为0；，达到限制值，型数值为0。读懂敏感性报告可变单元格$B$7、$C$7分别表示两个决策变量，在求得最优解后，即敏感性报告中所显示的$B$7、$C$7的终值分别为50和250。在目标函数中的系数为50，允许的增量为50，允许的减量为50，意思是当在目标函数中的系数在50的基础上再增加50或减少50，该线性规划问题的最优
15、解不变，即在其他条件不变的情况下，当时，仍然是线性规划问题的最优解。同理，在目标函数中的系数为100，允许的增量为1E+30，允许的减量为50，意思是当在目标函数中的系数在100的基础上再增加1E+30或减少50，该线性规划问题的最优解不变，即在其他条件不变的情况下，当时，仍然是线性规划问题的最优解。 当求得最优解后，单元格$D$3、$D$4、$D$5的值分别为350，300和250，即50个单位的药品和250个单位的药品，消耗了原料A350千克、机器B300小时，原料C250千克。由于企业拥有的原料A、机器B，原料C的数量分别是：400千克300小时和250千克，所以生产50个单位的药品和2
16、50个单位的药品消耗掉了企业的拥有的全部机器B设备时数和原料C，但原料A还有50千克的剩余。三种资源的阴影价格分别为0、50、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的影子价格不变，即
17、在其他条件不变的情况下，当时，。同理可得：当时，；当时，。三、 课外练习1、利用Excel试算教材后面的习题，然后与手工计算的结果进行对比。2、练习利用线性规划模型制订总体计划。例：红西红柿公司是一家园艺工具生产商，公司主要是将购进的原材料制造成多用途的园艺工具。由于生产线需要的工具和场地是有限定的，红西红柿公司的生产能力主要由劳动力人数决定。该公司的产品需求季节性很强，需求最旺的时间在春季。该公司决定利用总体计划来克服需求季节性变动的障碍，同时实现利润最大化。公司的选择是，在淡季建立库存，当旺季到来时增加工人，签订转包合同，交积压订单登记入册，以后再将产品送达顾客。为了知道如何利用总体计划中
18、的这些选择，传播优秀Word版文档 ，希望对您有帮助，可双击去除！“红西红柿”供应链的副总裁从预测下6个月的需求着手工作，如表9所示。该公司以40美元的单价销售其工具。公司在1月的库存为1000个工具，在1月初有80名员工。每个月每条生产线有20个工作日，每个工人每小时收入4美元。每个工人每天工作8小时，其余时间休息。如前所述，生产线的生产能力主要取决于工人工作总时数。所以，机器的生产能力并不限制生产线的生产能力。根据劳动法规定，工人每月加班不能超过10小时。各种成本如表10所示。目前，红西红柿公司在转包合同、库存缺货或积压上没有什么限制。所有库存缺货被积累起来，由下一个月生产出来的产品来满足
19、。库存成本在当月库存结清时才计入。供应链管理者的目标是，制定出一个最合理的总体计划，它将使库存量在6月底至少为500个单位，即6月底没有库存缺货，而是至少有500个单位的库存量。问企业应该如何制定总体计划才能使总成本最小。表9 “红西红柿”工具的需求月份123456需求预测160030003200380022002200表10“红西红柿”工具公司的成本项目成本原材料成本每单位10美元库存成本每月每单位2美元库存缺货或积压的边际成本每月每单位5美元雇用或者培训劳动力成本每个工人300美元解雇员工成本每个工人500美元需要的劳动时间每单位4美元日常工作成本每小时4美元加班成本每小时6美元转包成本每
20、单位30美元3、捷运公司在下一年度的14月的4个月内拟租用仓库堆放物资。已知各月份所需仓库面积如表1所示。仓库租借费用随合同期而定，期限越长，折扣越大，具体数据见表2。租借仓库的合同每月初都可办理，每份合同具体规定租用面积和期限。因此该厂可根据需要，在任何一个月初办理租借合同。每次办理时可签订一份合同，也可签订若干份租用面积和租借期限不同的合同，试确定该公司租借合同的最优决策，目的是使所付租借费用最小。月份1234所需仓库面积1500100020001200四、 实验要求1、 课前预习，写出实验提纲；2、 能建立常见的线性规划模型，并用Excel进行运算。3、 能看懂Excel输出的结果报告，了解结果的经济学含义，以将计算结果用于指导企业经营实践。4、 根据实验目的和实验内容写出实验报告。传播优秀Word版文档 ，希望对您有帮助，可双击去除！五、 指示指导1、 单元格引用地址2、 单元格公式显示3、 SUMPRODUCT函数的使用。六、七、 八、九、 十、1、