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

实验一、用Excel求解线性规划模型 线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。Excel最多可解200个变量、600个约束条件的问题。下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。 一、实验目的 掌握如何建立线性规划模型。 掌握用Excel求解线性规划模型的方法。 掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。 读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。 实验内容 [工具][规划求解]命令 规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。 使用[规划求解]命令的一般步骤为： 第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。 图1“规划求解参数”对话框 表1“规划求解参数”对话框选项 选项名 说明 设置目标单元格 选取计算问题的目标函数，并含有计算公式的单元格 等于 按问题目标进行选择。如利润问题，选取“最大值” 可变单元格 决策变量所在各单元格、不含公式，可以有多个区域或单元格 约束 增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改 添加 选择后弹出图2所示对话框 更改 选择后弹出图3所示对话框 删除 删除所选定的约束条件 选项 决定采用线性模型还是非线性模型求解 约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。 图2 图3 第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。 图4 第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。 图5 第四步：在图5中单击“确定”按钮，则只将优化计算结果显示在表格设置中的可变单元格（决策变量）和目标单元格（目标函数）内。 在图5的“报告”框中有3个选项，每个选项对应着一个报告，各报告以单一工作表记载，它们不仅能给出优化结果，甚至还给出更重要信息，例如影子价格等。 产品生产品种结构优化问题 数学模型 示例：一家制药厂生产两种产品：药品Ⅰ和药品Ⅱ。每个产品要用到一种相同的原料A，并要经过一道相同的工序，在机器B上包装 。因为这两种产品可以使用同样的机器，所以它们可以轮换使用设备，从而使其生产设施得到较充分的利用。 表2 药品和药品的售价、可变成本和贡献 药品 销售价（元） 可变成本（元） 对利润的贡献 Ⅰ 350 300 50 Ⅱ 450 350 100 表3 两种药品在机器上加工两种产品的时间以及原材料A和B限制 药品 原料A（千克） 机器B（小时） 原料C（千克） Ⅰ 2 1 0 Ⅱ 1 1 1 资源限制 400 300 250 问该制药厂应该如何安排生产计划才能使企业的利润最大。 我们知道，如果分别设药品Ⅰ和药品Ⅱ的生产数量为x1和x2，那么该问题的线性规划模型如下： 表格设置与公式说明 根据本问题的规模和条件，拟设置如表1中A1︰E8所示形式： ⑴区域B3︰C6和E3︰E5为原始数据区，输入如表1中所示的原始数据。 表4 ⑵在单元格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的录入。 第三步：重复第二