实验三、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。

Excel最多可解200个变量、600个约束条件的问题。

下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。

一、实验目的1、掌握如何建立线性规划模型。

2、掌握用Excel求解线性规划模型的方法。

3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。

4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。

二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。

在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。

使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。

图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。

如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。

图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。

图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。

图5第四步：在图5中单击“确定”按钮，则只将优化计算结果显示在表格设置中的可变单元格（决策变量）和目标单元格（目标函数）内。

在图5的“报告”框中有3个选项，每个选项对应着一个报告，各报告以单一工作表记载，它们不仅能给出优化结果，甚至还给出更重要信息，例如影子价格等。

2、 产品生产品种结构优化问题 数学模型 示例：一家制药厂生产两种产品：药品Ⅰ和药品Ⅱ。

每个产品要用到一种相同的原料A ，并要经过一道相同的工序，在机器B 上包装 。

因为这两种产品可以使用同样的机器，所以它们可以轮换使用设备，从而使其生产设施得到较充分的利用。

表 2 药品和药品的售价、可变成本和贡献 药品 销售价（元） 可变成本（元） 对利润的贡献 Ⅰ 350 300 50 Ⅱ450350100表3 两种药品在机器上加工两种产品的时间以及原材料A 和B 限制 药品 原料A （千克） 机器B （小时） 原料C （千克） Ⅰ 2 1 0 Ⅱ 1 1 1 资源限制400300250问该制药厂应该如何安排生产计划才能使企业的利润最大。

我们知道，如果分别设药品Ⅰ和药品Ⅱ的生产数量为x1和x2，那么该问题的线性规划模型如下：表格设置与公式说明根据本问题的规模和条件，拟设置如表1中A1︰E8所示形式：⑴区域B3︰C6和E3︰E5为原始数据区，输入如表1中所示的原始数据。

表4⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≥≤≤+≤+)(0)(0)(250)(300)(4002..2122121的最低产量药品的最低产量药品原料机器原料II x I x C x B x x A x x t s 2110050m ax x x Z +=⑵在单元格B8内输入数学模型中目标函数的计算公式，并求最大值。

⑶单元格B7︰C7分别作为药品Ⅰ和药品Ⅱ的产量（即决策变量x1、x2），即可变单元格。

其初始值设为0，求解过程中计算机会自动输入各组试验值。

⑷区域D3︰D5内的各单元格依次输入三个约束条件对应式的左侧部分。

操作步骤第一步：选择[工具][规划求解]命令，弹出图1所示对话框。

根据本问题的性质，在“设置目标单元格”文本框内填入$B$8,在“等于”选项后选取“最大值”，在“可变单元格”文本框内填入$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的录入。

第三步：重复第二步，录入$B$7︰$C$7>=0，即两决策变量的值必须大于0，最后如图1所示。

第四步：在图1中单击“选项”按钮，弹出图4对话框。

因本例题属于线性规划问题，选取“采用线性模型”按钮，再单击“确定”按钮，回到图1。

第五步：在图1中选取“求解”按钮或击回车键，Excel进入规划求解运行过程，屏幕左下角状态条上逐次显示运行过程报告。

一旦计算结束，弹出图5的对话框。

在图5内可以有四种选择：⑴若单击“确定”按钮或击回车键，则显示如表2的结果。

可变单元格$B$7︰$C$7内显示最优生产计划，即药品Ⅰ生产50件和药品Ⅱ生产100件，可获得最大利润27500元；单元格$D$3︰$D$5分别给出了各种资源的用量，只有原料A有50千克的剩余。

表5⑵若选择“运算结果报告”，Excel显示“运算结果报告<n>”，其中<n>表示求解本问题中已经连续第几次选择该选项，我们这里给出的是“运算结果报告1”（如表6），即在同一文件内首次选择该选项。

表6比较全面地报告了优化结果信息，包括目标单元格的位置、名称、初值和终值，可变单元格的位置、名称、初值和终值，约束单元格位置、名称、单元格内计算结果、单元格相应约束式、运算结果达到的状态（型数值为0表示到达限制值、否则未到达限制值）。

⑶若选择“敏感性报告”，Excel显示“敏感性报告<n>”（如表7）。

敏感性报告表的限制式中“阴影价格”（经济学中称影子价格、Shadow Price）是一个有特别意义的经济指标。

表6Microsoft Excel 9.0 运算结果报告工作表 [习题一.xls]Sheet2报告的建立: 2006-8-24 19:22:29目标单元格 (最大值)单元格名字初值终值$B$8 目标函数0 27500可变单元格单元格名字初值终值$B$7 决策变量产品1 0 50$C$7 决策变量产品2 0 250约束单元格名字单元格值公式状态型数值$D$3 原料A 350 $D$3<=$E$3 未到限制值50$D$4 机器B 300 $D$4<=$E$4 到达限制值0$D$5 原料C 250 $D$5<=$E$5 到达限制值0$B$7 决策变量产品1 50 $B$7>=0 未到限制值50$C$7 决策变量产品2 250 $C$7>=0 未到限制值250影子价格的经济学意义是，使在最优利用下的紧缺资源增加1个单位，将为企业创造的利润。

用影子价格与各紧缺资源的市场价格相比较，可以为企业是否购买紧缺资源扩大生产提供决策依据。

有剩余的资源影子价格为0。

表7Microsoft Excel 9.0 敏感性报告工作表 [习题一.xls]Sheet2报告的建立: 2006-8-25 11:01:22可变单元格终递减目标式允许的允许的单元格名字值成本系数增量减量$B$7 决策变量产品1 50 0 50 50 50$C$7 决策变量产品2 250 0 100 1E+30 50约束终阴影约束允许的允许的单元格名字值价格限制值增量减量$D$3 原料A 350 0 400 1E+30 50$D$4 机器B 300 50 300 25 50$D$5 原料C 250 50 250 50 50⑷若选择“极限值报告”，Excel 显示“极限值报告<n>”（如表8）。

除了给出最优决策对应最优目标值信息外，还显示各决策变量的上、下限值及其对应目标式结果。

表8 Microsoft Excel 9.0 极限值报告 工作表 [习题一.xls]Sheet2 报告的建立: 2006-8-25 11:02:45目标式 单元格 名字值 $B$8 最大利润27500变量 下限 目标式 上限 目标式 单元格名字值 极限结果 极限结果$B$7 决策变量 产品1 50 0 2500050 27500$C$7 决策变量 产品22500 2500 249.9999999 27499.999993、读懂Excel 求解线性规划问题输出的运算结果报告和敏感性报告利用Excel 求解线性规划问题系统将提供三个计算结果报告，即运算结果报告、敏感性报告、极限值报告。

这三个报告中的前两个报告非常重要，下面我们将结合教材第二章线性规划的对偶理论与灵敏度分析的内容讲述如何看Excel 求解线性规划问题输出的运算结果报告和敏感性报告。

⑴读懂运算结果报告运算结果报告比较容易看懂，可变单元格$B$7和$C$7分别表示两个决策变量，即药品Ⅰ和药品Ⅱ的产量，在计算时，由于我们最初赋予0,021==x x ，所以单元格$B$7和$C$7的初值为0，求得最优解后，$B$7和$C$7的值分别为50和250，即250,5021==x x ，表示使目标函数值最大的计划是生产50个单位的药品Ⅰ和250个单位的药品Ⅱ。

目标单元格$B$8表示目标函数2110050m ax x x z +=，由于我们最初赋予0,021==x x ，所以目标函数的初值为0，求得最优解后，目标函数的值为27500，即，如果生产50个单位的药品Ⅰ和250个单位的药品Ⅱ能使企业利润达到最大值27500元。

在单元格$D$3、$D$4、$D$5我们分别输入了三个约束条件的左边项，即212x x +，21x x +和2x ，随后在使用[工具][规划求解]时，在[规划求解参数]窗口，我们输入了5$$5$,$4$$4$,$3$$3$$E D E D E D ≤≤≤,而$E$3=400、$E$4=300、$E$5=250,从而完成了约束条件的输入。

当求得最优解250,5021==x x 后,将250,5021==x x 代入约束方程得：3$$35023$$21E x x D ≤=+=，未达到限制值，型数值为50；4$$3004$$21E x x D ==+=，达到限制值，型数值为0；5$$2505$$2E x D ===，达到限制值，型数值为0。