利用ecel软件求解线性规划问题

《利用ecel软件求解线性规划问题》由会员分享，可在线阅读，更多相关《利用ecel软件求解线性规划问题（12页珍藏版）》请在装配图网上搜索。

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

2、原料（磅）3.22.61.50.80.70.31600单位利润（元）65.35.44.23.81.8需求量（磅）960928104197710841055设该公司生产药品16的产量分别为x ,x , ,x （磅），则最优产品组合的线性1 2 6规划模型为max z = 6x + 5.3x + 5.4x + 4.2x + 3.8x +1.8x1234566x + 5x + 4x + 3x + 2.5x +1.5x 45001234563.2x + 2.6x +1.5x + 0.8x + 0.7x + 0.3x 160023456x 9601x 9282x 10413x 9774x 10845x

3、0,1 j 6面用 规划求解加载宏来求解这个问题：首先，如下如所示，在Excel工作表内输入目标函数的系数、约束方程的系数、右端常数项；目标函数的系数系数常阵其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数DEG消耗系数产品3产品4产品&#VALUE目标函数单元约束函数单元可变单元劳动力 原料 单位利润 需求量产品刃产品3j产品其中，劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”，原料约束函数的定义公 式是 “=MMULT(B4:G4,J5:J10)”，目标函数的定义公式是 “MMULT(B5:G5, J5:J10)”。注：函数MMULT(B3:

4、G3, J5:J10)的意义是：单元区B3:G3表示的行向量与单元区J5:J10 表示的列向量的内积。这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列 并且两个单元区所含的单元格个数必须相等。最后1)3)(3.1)打开规划求解参数设定对话框设定模型( 2)目标函数和可边单元的设定很简单，在此就不再赘述 约束条件的设定f 6x + 5x + 4x + 3x + 2.5x +1.5x 4500 匕1600的设定:x 16006约束条件ABCDEFGHIJ消耗系数劳动力原料产品13. 2产品252. 6产品341. 5产品430. 82. 50. 7单位利润1011需求量添加均束单元格

5、引用位置产品6 I现有I利闸1.5( $5阿劳动力0.宛丄6_乌原料脚ALUE! 脚ALUE! TOLUE!口 口丄丿至1口 0 7 gL 口口厶i里产品3产量产品4产量产品5产量产品6产量曰(3.2 )约束条件fx 977 的设定 410845055x 9601x 9282x 0,x 0, x 0的设定这里值得注意的是： 我们采用向量的形式设定同向不等式，并且不等式两边可以一个时行向量, 另一个是列向量； 对所有分量都是 0 的向量，我们可以用一个 0 来代替。（4 ）求解：我们选择保存三个报告12345消耗系数劳动力原料单位利润产品133. 26产品255.3产品341. 55. 4产品4

6、30. E4. 2产品52. 50. 了3.8产品E1. 50. 31. 8现有45001600利润 劳动力原料产品1产量6此5. 24500123&.13 飞需求量门仁门0皿 1门冲1口丁丁 1门C 1门匚匚燥划求解结果89101112篠乎解找到1解可满足所有的约束及杲忧报告596.阿108413_确定取消14品6产量保存规划求解结果思 o恢复肯原值（U）得到的三张份告如下 icrosoft Excel 11.0运算结果报告工祚表新建i.czOt. Excel 工作表.zlsSheetl报告曲建立：2008-4-T 1:59:06$J$2利润#VALUE!6625.2可变单元格单元格名字初值

7、终值$J$5产品1产量00$斑产品2产量00$J$7产品3产量00$J$8产品4产量0596.6666667$J$9产品5产量01084$j$10产品E产量00约束匚单云格名字单云榕悄状玄型数值目标单元格大值）单荒格 名字初值终值$J$3劳动力4500 $J$3=$H$3到达限制值0$J$4矗組1236.133333 $J$4=$H$4未到限制值363.8666667$J$5产品1产量0未到限制值960$J$6产品2产量0 $j$6=$C$6未到限制值928$J$7产品3产量0 $J$7=$D$6未到限制值1041$J$8产品4产量596.6666667 $J$8=$E$6未到限制值380.3

8、333333$J$9产品5产量1084 $J$9=$F$6到达限制值0$J$10产品E产量0 $J$10=0到达限制值0$J$6产品2产量0 $J$6=0到达限制值0$J$7产品3产量0 $J$7=0到达限制值0$J$8产品4产量596.6666667 $J$8=0未到限制值596.6666667$J$9产品5产量1084 $J$9=0未到限制值1084$J$10产品E产量0 $J$10=0到达限制值0Iicrosoft Excel 11. 0 敏感性报告工作表新建 Iicrosoft Excel 工作表.zlsSheetl 报告的建立：2008-4-7 1:59:06可变单元格格 元字名终值

9、减度 递梯量 产1X 品 产O量 产 2 品 产O量 产 3 品 产O量 产4 品 产O量 产 5 品 产48O1I量 产 6 品 产O终格 元 *&1字 名值数力 动 劳OO54OIicrosoft Excel 11. 0 极限值报告工作表新建icmsoFt Excel工作表.xls限值报告4 报告的建立：2008-4-7 1:59:0711单元格目标式名字值$J$2利润6625. 2格 元 单量字 变名*限限 下极目纟上限 极限目纟5 J. TJ tvOO6 $. $JOO7量 产 3 品 产OO8量 产 4 品 产-O9 $. $J量 产5 品 产48O1O650248OTIo11 J-

10、 $J量 产 6 品 产OOO补充说明：a) 如果数学规划模型中包含整型变量或0 1 型变量，只需要在设定约束条件一步中设定相应的变量是整型变量或 0 1 型变量即可。例如，假定在上例中变 量 x,x 是整型变量，只 需要增加如下图所示的整型约束设定即可。不过要注12意的是，含整型变量或 0 1 型变量的问题是不能进行敏感性分析的。b) 求解报告中把输出目标函数单元、约束函数单元、和可边单元上方第一个输有文字单元及左边第一个输有文字单元内全部文字都作为对解释目标函数单元、约束函数单元、和可边单元的解释，例如，把 Excel 表改为ABCDEFGHIJKI2消耗系数产品I产品2产品3产品4产品5

11、产品5现有利润6625. 23劳动金65432. 5I. 54500_4原料3. 22. 6I. 50. 80. 70. 31600劳动力445005竜祓利润65. 35. 44. 23. 8I. 8原料1236. 136需求量960928104197710841055产品I产量07产品2产量08产品3产量09产品4产量596. 66710产品5产量1084II产品6产量012则求解报告输出变为Microsoft Excel 11.0运算站果报告工作表新建icz*o2ot Excel 工作表.xlsSheetl报告矗建立:2008-4-T 3:21:09目标单元格 瀑大值)单云格 名字初值终值

12、$J$2 利润6625.26625.2可变单元格约束单云格名字单元格值舍式状臺型数值$J$4劳动力原料4500 $J$4=$H$3到达限制值0原料原料1236.133333 $J$5=$K$4未到限制值363.8666667$J$6产品1产量原料0 $J$6=$B$6未到限制值960$J$7产品2产星原料0 $J$T=$C$6未到限制值928产品3产星原料0 $J$8=$D$6未到限制值1041产品4产墓療料596.6666667 $j$9=$E$6未药酿匍值380.3333333$J$10产品5产量原料1084 $J$10=$F$6到达限制值0$J$U产品6产星原料0 $J$11=0到达限制

13、值0$J$7产品2产星原料0 $J$T=0到达限制值0c) 另外，非线性规划模型的求解方法类似，在此不再赘述。五、“规划求解”疑难解答1、尚未找到满足要求的结果，“规划求解”即停止了运行 由于下列任意一个原因，“规划求解”在找到答案前，可能停止运行： 中断了求解过程。 在单击“求解”之前，选中了 “规划求解选项”对话框中的“显示迭代结果”选项。 在单步迭代过程中，或达到最长运算时间或最大迭代次数时，单击了“停止”按钮。 选中了 “规划求解选项”对话框中的“采用线性模型”复选框，但问题是非线性的。 在“规划求解参数”对话框的“设置目标单元格”框中指定的数值不收敛地增加或减少。 需要让“规划求解”

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

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

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

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

18、中的当前值，确定数值发散的 原因，并且检查约束条件，然后再次求解。 “规划求解”未找到合适结果这表明在满足约束条件和精度要求的条件下，“规划求解”无法得到合理的结果，这 可能是约束条件不一致所致。请检查约束条件公式或类型选择是否有误。 “规划求解”应用户要求而中止这表明在暂停求解过程之后，或在单步执行规划求解时，单击了“显示中间结果”对 话框中的“停止”。 无法满足设定的“采用线性模型”条件这表明求解时选中了“采用线性模型”复选框，但是“规划求解”最后计算结果并不 满足线性模型。计算结果对工作表中的公式无效。若要验证问题是否为非线性的，请选中 “自动按比例缩放”复选框，然后再运行一次。如果又一

19、次出现同样信息，请清除“采用 线性模型”复选框，然后再运行一次。 “规划求解”在目标或约束条件单元格中发现错误值这表明在最近的一次运算中，一个或多个公式的运算结果有误。请找到包含错误值的 目标单元格或约束条件单元格，更改其中的公式或内容，以得到合理的运算结果。还有可能是在“添加约束”或“改变约束”对话框中键入了无效的名称或公式，或者 在“约束”框中直接键入了 “integer”或“binary”。若要将数值约束为整数，请在比较 运算符列表中单击“In t”。若要将数值约束为二进制数，请单击“Bin”。 内存不足以求解问题Microsoft Excel 无法获得“规划求解”所需的内存。请关闭一些文件或应用程序， 再试一次。 其他的 Microsoft Excel 实例正在使用 SOLVER.DLL这表明有多个 Microsoft Excel 会话正在运行，其中一个会话正在使用 SOLVER.DLL。SOLVER.DLL 同时只能供一个会话使用。