课题名称-用Excel解线性规划57-61

1、课题名称184用Excel解线性规划问题授课班级授课时间13级课题序号授课课时第 57到 61 授课形式讲练结合使用教具教学目的教学重点教学难点更新、补充、删减内容课外作业P80-2、3授课主要内容或板书设计教学后记主 要 教 学 内 容 及 步 骤教学过程 师生活动 设计意图等一、什么是规划求解加载宏？规划求解加载宏（简称规划求解）是Excel的一个加载项，可以用来解决线性规划与非线性规划优化问题。规划求解可以用来解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的问题。可以设置决策变量为整型变量。规划求解加载宏的开发商是Fronline System公

2、司。用户通过自定义安装MS-Office所使用的是标准版本规划求解加载宏，Fronline System公司同时提供增强的Premium Solver工具。规划求解工具在Office典型安装状态下不会安装，可以通过自定义安装选择该项或通过添加/删除程序增加规划求解加载宏。二、怎样加载规划求解加载宏？加载规划求解加载宏的方法如下：（1） 打开“工具”下拉列菜单，然后单击“加载宏”，打开“加载宏”对话框。 （2） 在“可用加载宏”框中，选中“规划求解”旁边的复选框如果“规划求解”未列出，请单击“浏览”进行查找。，然后单击“确定”按钮。（3） 如果出现一条消息，指出您的计算机上当前没有安装规划求解，

3、请单击“是”用原Office安装盘进行安装。 （4） 单击菜单栏上的“工具”。加载规划求解后，“规划求解”命令会添加到“工具”菜单中。 三、怎样使用规划求解加载宏求解数学规划？规划求解加载宏是一组命令构成的一个子程序，这些命令有时也称作假设分析该过程通过更改单元格中的值来查看这些更改对工作表中公式结果的影响。例如，更改分期支付表中的利率可以调整支付金工具，其功能是可以求出线性和非线性数学规划问题的最优解和最优值。使用规划求解加载宏求解数学规划的步骤首先，在Excel工作表中输入目标函数的系数向量、约束条件的系数矩阵和右端常数项（每一个单元格输入一个数据）；其次，选定一个单元格存储目标函数（称为

4、目标单元格），用定义公式的方式在这个目标单元格内定义目标函数；再次，选定与决策变量个数相同的单元格（称为可变单元格），用以存储决策变量；再选择与约束条件个数相同的单元格，用定义公式的方式在每一个单元格内定义一个约束函数（称为约束函数单元格）；最后，点击下拉列菜单中的规划求解按钮，打开规划求解参数设定对话框（如图4所示），完成规划模型的设定模型设定方法如下：（1）设定目标函数和优化方向：光标指向规划求解参数设定对话框中的“设置目标单元格”提示后的域，点击鼠标左键，然后选中Excel工作表中的目标单元格。然后根据模型中目标函数的优化方向，在规划求解参数设定对话框中的“等于”一行中选择“最大值”或“

5、最小值”；（2）设定（表示决策变量的）可变单元：光标指向规划求解参数设定对话框中的“可变单元格”提示后的域，点击鼠标左键，然后选中Excel工作表中的可变单元组。可以点击“推测”按钮，初步确定可变单元格的范围，然后在此基础上进一步确定；（3）设定约束条件：直接点击规划求解参数设定对话框中的添加按钮，出现如下添加约束对话框：先用鼠标左键点击“单元格引用位置”标题下的域，然后在工作表中选择一个约束函数单元格，再点击添加约束对话框中向下的箭头，出现<=，=，>=，int和bin五个选项，根据该约束函数所在约束方程的情况选择，其中int和bin分别用于说明整型变量和01型变量。选择完成后，

6、如果还有约束条件未设定，就点击“添加”按钮，重复以上步骤设定约束条件，设定完所有约束条件后，点击确定完成约束条件设定，回到规划求解参数设定对话框。（4）设定算法细节：点击规划求解参数设定对话框中的“选项”按钮，出现如下规划求解选项对话框。该对话框为使用者提供了在一些可供选择的常用算法。主要是供高级用户使用，初学者不必考虑这些选择。选择完成后点击确定按钮回到规划求解参数设定对话框（5）求解模型：完成以上设定后，点击规划求解参数设定对话框中的“求解”按钮，将出现如下求解结果对话框。根据需要选择右边列出的三个报告中的一部分或全部，然后点击确定按钮就可以在Excel内看到求解报告。四、用规划求解加载宏

7、求解数学规划举例下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：l 产品组合使用的资源不能超标。 l 对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。 下面，我们来考虑让某医药公司的最优产品组合问题。该公司有六种可以生产的药品，相关数据如下表所示。表1消耗系数产品1产品2产品3产品4产品5产品6现有劳动力（小时）65432.51.5

8、4500原料（磅）3.22.61.50.80.70.31600单位利润（元）65.35.44.23.81.8需求量（磅）960928104197710841055设该公司生产药品16的产量分别为（磅），则最优产品组合的线性规划模型为下面用规划求解加载宏来求解这个问题：首先，如下如所示，在Excel工作表内输入目标函数的系数、约束方程的系数、右端常数项；目标函数的系数系数矩阵右端常数系数矩阵其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数目标函数单元约束函数单元可变单元其中，劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”，原料约束函数的定义公式是“

9、MMULT(B4:G4,J5:J10)”，目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。注：函数MMULT(B3:G3, J5:J10)的意义是：单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。最后，打开规划求解参数设定对话框设定模型（1）（2）目标函数和可边单元的设定很简单，在此就不再赘述（3）约束条件的设定(3.1) 约束条件的设定：（3.2）约束条件的设定（3.3）约束条件的设定这里值得注意的是：l 我们采用向量的形式设定同向不等式，并且不等式两

10、边可以一个时行向量，另一个是列向量；l 对所有分量都是0的向量，我们可以用一个0来代替。（4）求解：我们选择保存三个报告得到的三张份告如下补充说明：a) 如果数学规划模型中包含整型变量或01型变量，只需要在设定约束条件一步中设定相应的变量是整型变量或01型变量即可。例如，假定在上例中变量是整型变量，只需要增加如下图所示的整型约束设定即可。不过要注意的是，含整型变量或01型变量的问题是不能进行敏感性分析的。b) 求解报告中把输出目标函数单元、约束函数单元、和可边单元上方第一个输有文字单元及左边第一个输有文字单元内全部文字都作为对解释目标函数单元、约束函数单元、和可边单元的解释，例如，把Excel

11、表改为则求解报告输出变为c) 另外，非线性规划模型的求解方法类似，在此不再赘述。五、“规划求解”疑难解答1、尚未找到满足要求的结果，“规划求解”即停止了运行由于下列任意一个原因，“规划求解”在找到答案前，可能停止运行：Ø 中断了求解过程。 Ø 在单击“求解”之前，选中了“规划求解选项”对话框中的“显示迭代结果”选项。 Ø 在单步迭代过程中，或达到最长运算时间或最大迭代次数时，单击了“停止”按钮。 Ø 选中了“规划求解选项”对话框中的“采用线性模型”复选框，但问题是非线性的。 Ø 在“规划求解参数”对话框的“设置目标单元格”框中指定的数值不收敛地

12、增加或减少。 Ø 需要让“规划求解”运行更长的时间以求得结果。请调整“规划求解选项”对话框中的“最长运算时间”或“迭代次数”的设置。 Ø 对于具有整数约束条件的问题，应该减小“规划求解选项”对话框中的“允许误差”的设置，使“规划求解”找到更好的整数解。 Ø 对于非线性问题，应该减小“规划求解选项”对话框中的“收敛度”的设置，使目标单元格数值变化缓慢时，“规划求解”仍可以运行，最终找到较好的结果。 Ø 应该选中“规划求解选项”对话框中的“自动按比例缩放”复选框，可能一些输入数值相差几个数量级，或输入和输出数值相差几个数量级。 当“规划求解”停止运行时，在“

13、规划求解结果”对话框中显示出完成信息。单击“保存规划求解结果”或“恢复为原值”，进行所需的更改，然后再运行一次。2、可变单元格与约束条件或目标单元格中的数值差别很大当可变单元格的典型数值与约束单元格或目标单元格中的数值相差几个数量级时，请选中“规划求解选项”对话框中的“自动按比例缩放”复选框。对于非线性问题，在单击“规划求解参数”对话框中的“求解”之前，请确认可变单元格的初始数值与期望的最终数值的数量级相同。3、未得到预期的结果对于非线性问题，在可变单元格中尝试不同的初始值可能会有帮助，特别是在“规划求解”结果与期望的数值差别很大时。预先将可变单元格的数值设置为预期的最优值，可以减少求解时间。

14、对于线性模型（也就是当“规划求解选项”对话框的“采用线性模型”复选框被选中时），改变可变单元格的初始值不会影响最终数值或求解时间。4、“规划求解”得到的结果与以前的结果不同“规划求解”显示如下消息：“规划求解已收敛到当前结果。满足所有约束条件”。这表明目标单元格中的数值在最近五次求解过程中的变化量小于“规划求解选项”对话框中“收敛度”设置的值。“收敛度”中设置的值越小，“规划求解”在计算时就会越精细，但求解过程将花费更多的时间。5、“规划求解”不能达到最优解下面列出“规划求解”显示的完整消息。l “规划求解”不能改进当前解。所有约束条件都得到了满足这表明仅得到近似值，迭代过程无法得到比显示结果

15、更精确的数值；或是无法进一步提高精度，或是精度值设置得太小，请在“规划求解选项”对话框中试着设置较大的精度值，然后再运行一次。l 求解达到最长运算时间后停止这表明在达到最长运算时间限制时，没有得到满意的结果。若要保存当前结果并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。l 求解达到最大迭代次数后停止这表明在达到最大迭代次数时，没有得到满意的结果。增加迭代次数也许有用，但是应该先检查结果数值来确定问题的原因。若要保存当前值并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。l 目标单元格中的数值不收敛这表明即使满足全部约束条件，目标单元格数值也只是有增或有减但不收敛

16、。这可能是在设置问题时忽略了一项或多项约束条件。请检查工作表中的当前值，确定数值发散的原因，并且检查约束条件，然后再次求解。l “规划求解”未找到合适结果这表明在满足约束条件和精度要求的条件下，“规划求解”无法得到合理的结果，这可能是约束条件不一致所致。请检查约束条件公式或类型选择是否有误。l “规划求解”应用户要求而中止这表明在暂停求解过程之后，或在单步执行规划求解时，单击了“显示中间结果”对话框中的“停止”。l 无法满足设定的“采用线性模型”条件这表明求解时选中了“采用线性模型”复选框，但是“规划求解”最后计算结果并不满足线性模型。计算结果对工作表中的公式无效。若要验证问题是否为非线性的，请选中“自动按比例缩放”复选框，然后再运行一次。如果又一次出现同样信息，请清除“采用线性模型”复选框，然后再运行一次。l “规划求解”在目标或约束条件单元格中发现错误值这表明在最近的一次运算中，一个或多个公式的运算结果有误。请找到包含错误值的目标单元格或约束条件单元格，更改其中的公式或内容，以得到合理的运算结果。还有可能是在“添加约束”或“改变约束”对话框中键入了无效的名称或