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

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

2、原料（磅）3.22.61.50.8:0.7:0.31600单位利润（元）65.35.44.23.81.8需求量（磅）9609281041977P 1084P 1055设该公司生产药品16的产量分别为Xi,X2i,X6（磅），则最优产品组合的线性规划模型为max z =6捲5.3X25.4X34.2x43.8x51.8x66为+5x2+4X3+3X4+2.5X5+1.5X6兰45003.2/ +2.6X2+1.5X3+0.8X4+0.7X5+0.3X6兰1600捲乞960 x2兰928s.HX3兰1041X4兰977X5兰1084x 1055Xj1E6下面用 规划求解加载宏来求解这个问题：首先，

3、如下如所示，在Excel工作表内输入目标函数的系数、约束方程的系数、右端常数 项；实用标准文案文档(3.1)约束条件6x5x24x33x42.5x31.5x 45003.2x2.6x21.5X3O.8X40.7花,0.3x1600的设定:其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数其中，劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”，原料约束函数的定义公式是“=MMULT(B4:G4,J5:J10)”，目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。注：函数MMULT(B3:G3, J5:J10)的意义是：单元区B3:G3表

4、示的行向量与单元区J5:J10表示的列向量的内积。这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。最后，打开规划求解参数设定对话框设定模型(1)(2)目标函数和可边单元的设定很简单，在此就不再赘述(3)约束条件的设定A丨C|D|EI | J |K产品&产量RVALUE63450原料7需求量0別约束函数单元121, 50,352.65.392341.55. 46960产品2产量消耗系数产品1产品2产品3产品4产品5产品6现有利润LVALUE产品茂产量可变单元苛动力3.20.8单位利阔1041乙5目标函数单元实用标准文案文档11ABC

5、D-,rkJ* -FGHIJK12消耗系数产品1产品2产品3产品4产品5产品&I现有I利润LVALU3劳动分65432.61.5| 4500劳动力LVALUE!4原料3.22.61”50.80.7o.脚ALUE!5审检帀润工匚.16r1- 1、1严、誓加约恿区1产品2产睪单元格引用位78g10=釣束值(): “ _ & |= 7 |細$3:$刚_就厂PPJ.L車壬吕量 产品5产量 产品6产量% 兰960 x2兰928x 1041(3.2)约束条件3的设定x4兰977X5弋1084x6_1055B123q5消耗系数劳砂切 原料产品163 2D产品22 6单位釉闰10111213(

6、3.3)约束条件4.2品35. 34产品4-d产品52.50产品&1.503现有1600利润劳动力1. 8原料产品1产量产品2产量ItaALUE.脚ALUE 脚ALUE.3.8-j- j r- r- f -j- j- _r*期工jm产品5产量产品6产量% 0,X2_0,川，X6_0的设定添加观 铭助QD 取消实用标准文案文档实用标准文案文档这里值得注意的是:我们采用向量的形式设定同向不等式，并且不等式两边可以一个时行向量, 另一个是列向量；对所有分量都是0的向量，我们可以用一个(4)求解：我们选择保存三个报告D得到的三张份告如下1234567消耗系数产品1610111213原料产品3q

7、1.5单位和润需求量69605.39235,41041977产品52 5QY3呂10S4产品61.51.81055现有45001&0Q劳动力原料厂口口 重产昴2严量#VALUE:脚ALUE1 |_LVALUE 1产品B产量产品4产量产品5产量0来代替。12345678消耗系数 劳动力 原料单位利润 需求量产品1&3.2一&0产品戈5& 3产品弓41.55.41 d/ii产品430.84.27产品52.50. T3.212 A产品&L50卫L卫1仃皿现有45001600利润 劳动力原料产品1产量45001236.1351011121314595&苗

8、1084啟产量BiE产睪品4产量品5产量实用标准文案文档 i rrosof t Ectl11.0 运尊錯果报告工作表新建! CTSf I Excel工作zlslSheell JR告禽建立：2006-4-7 1:59:06目 _单元格名字初值饕值$J$2利润#7ALUE!6625 2单兀蜡名字知值终值$J$5产晶 1 产量00$J$B产嶷产星00$J$T产晶 3 产量00$J$8产品 4 产垦0596. 6666S67$J$9产品吕产垦a1084$J$10产躲产量00抵_单元格 名字 单元轄值 公式肚 型数值$J$3劳动力_4E00 $J$3=$H$3到达限制0$Jf4原料1236. 13333

9、3 $J$4=$H$4未到限制363.8666667$J$5产品 1 产量 $J$5=$EJ6未到限制060$J$&产品产星0 $J$S=$cj&未到限制928$J$7产晶 3 产量0_SJtl=$EJ6未到限制1041$Jf3产品 4 产量596 一 &666E&T未到限制38CL 3333333$J$9上品吕产量1DB4 $J 翱 =$F 館限制Ci$J$10产品&产量a Sjfia=0限制值0$JJ5产氐产暈0jJ$&=0限制值0$JJ7产品 3 产皐0 JJJT=O限制值a$J18产品 4 产暈596. 5566667 $Jf8=0限制值

10、596.5666667$JJ9产品 5 产皐1084 SJJ9=0未甬限制值1034jflO产品 6 产昼0 $Jtl0=0限制0licrosoft Excel 11.0 敏感性报告I 作衷 I新建 licrqsafl Ricel 工作表.ilslSheell 报告禹建立;2008-4-7 1:59:06可变单元格递减单元格名务值悌度TJI5产品1产量0-2. 399999619$J56产品2产量0-1.7000040&9$Jf?产品3产呈0-0,1M99T648$J*8产品4产星5獣66666&T0$J59产品5产垦10340.300000111S.T11Q产品E产量0-0.

11、 300004125终拉林朗日容字乘教$JJ3劳动力4500L3999999两$JH原料1236. 1333330实用标准文案文档iCTOsoft Ezceln. o TOO告新建licrosofi nice：! Tfr ilsUK限値抿告1报吿的聲k: 20S3-4-7 l:5r07单元格目标式 窖字值5JU6625.2格元单虽字有吉目& &TJTJ= =O OO O& &JJf f- -O OO OO O7 7J5J5$ $-O OO O0 0g g ? ?- -J J3-3-虽产4 4品产一-6767O O67679 9 34341010C CO OTiT

12、i-O OO O量XX2是整型变量，只 需要增加如下图所示的整型约束设定即可。不过要注 意的是，含整型变量或o-1型变量的问题是不能进行敏感性分析的。A |b）求解报告中把输出目标函数单元、约束函数单元、和可边单元上方第一个输有文字单元及左边第一个输有文字单元内全部文字都作为对解释目标函数单A.BC0EFH 1IIK12消耗系数产品1产品2产品3产品4产品5严品&现有6625. 23芳动舟65432.5L54500厘料4原料3.22.61.50.80.70.3 1600劳动力 45005单住利润6&.34.23.8L.S1236. 136需求量96092S104197710S4

13、1055产品1产暈07产品沪重00产品沪暈0补充说明：a）如果数学规划模型中包含整型变量或步中设定相应的变量是整型变量或0-1型变量，只需要在设定约束条件一0-1型变量即可。例如，假定在上例中变逋耗系数劳动力 原料单位利润 需求量.产品1&3.26960产品255_3928产品341.5541041.产品43.4&977产品6现有N5Ta10840,3L8105545001600利涧 劳动力 原料i脚ALUE!r#ALUE IttVALUEL品3产量 产品4产量产品5产量 产品6产量元、约束函数单元、和可边单元的解释，例如，把Excel表改为产品1产量实用标准文案文档9产品q产

14、量&96.66710产品5产量108411产品沪罩012则求解报告输出变为jlcrftsoft Ecel 11.0 迂尊轄果:I&告工炸表新建 BicroEAft Ezcl XttS- ilsSLe*tl报告的建立：2008-4-7 3:21:09吕_单5E格名宇初值菩值$J 龍利润ES25.2&藍.2可变里元格单云福名宇初值终值疋品IT量原料00$J$T左品z产量庶套00上品沪量原料00上品4产星原挙596. 6666667_596.66666675J$10产品亦疑蔚耳10341064产品&产量原勒0D单元格名宇单元格値公式伏态加劳动力原科4500值05J55

15、原料原科1236. 13333? $J$5=$M$4限制363. 8666667$J$6产品1产量原科0卩艮960产品2产星原抽0 S.TS7j=fCj&限制928sjje产品沪量原掐0未尊值1041.辟产品4产量蘇S96. 6666667连鈿值380 3333333SJS10产品53F惑1034JJ510=0SI值0SJST产品沪星原超0 SJI7=0到0C）另外，非线性规划模型的求解方法类似，在此不再赘述。五、“规划求解”疑难解答1、尚未找到满足要求的结果，“规划求解”即停止了运行由于下列任意一个原因，“规划求解”在找到答案前，可能停止运行：中断了求解过程。在单击“求解”之前，选中

16、了 “规划求解选项”对话框中的“显示迭代结果”选项。在单步迭代过程中，或达到最长运算时间或最大迭代次数时，单击了“停止”按钮。选中了 “规划求解选项”对话框中的“采用线性模型”复选框，但问题是非线性的。在“规划求解参数”对话框的“设置目标单元格”框中指定的数值不收敛地增加或减少。需要让“规划求解”运行更长的时间以求得结果。请调整“规划求解选项”对话框中的“最 长运算时间”或“迭代次数”的设置。对于具有整数约束条件的问题，应该减小“规划求解选项”对话框中的“允许误差”的设置，使“规划求解”找到更好的整数解。对于非线性问题，应该减小“规划求解选项”对话框中的“收敛度”的设置，使目标单元实用标准文案

17、文档格数值变化缓慢时，“规划求解”仍可以运行，最终找到较好的结果。应该选中“规划求解选项”对话框中的“自动按比例缩放”复选框，可能一些输入数值相 差几个数量级，或输入和输出数值相差几个数量级。当“规划求解”停止运行时，在“规划求解结果”对话框中显示出完成信息。单击“保存 规划求解结果”或“恢复为原值”，进行所需的更改，然后再运行一次。2、 可变单元格与约束条件或目标单元格中的数值差别很大当可变单元格的典型数值与约束单元格或目标单元格中的数值相差几个数量级时，请选中“规划求解选项”对话框中的“自动按比例缩放”复选框。对于非线性问题，在单击“规划求 解参数”对话框中的“求解”之前，请确认可变单元格

18、的初始数值与期望的最终数值的数量级 相同。3、 未得到预期的结果对于非线性问题，在可变单元格中尝试不同的初始值可能会有帮助，特别是在“规划求解”结果与期望的数值差别很大时。预先将可变单元格的数值设置为预期的最优值，可以减少求解 时间。对于线性模型（也就是当“规划求解选项”对话框的“采用线性模型”复选框被选中时）， 改变可变单元格的初始值不会影响最终数值或求解时间。4、 “规划求解”得到的结果与以前的结果不同“规划求解”显示如下消息：“规划求解已收敛到当前结果。满足所有约束条件”。这表 明目标单元格中的数值在最近五次求解过程中的变化量小于“规划求解选项”对话框中“收敛 度”设置的值。“收敛度”中

19、设置的值越小，“规划求解”在计算时就会越精细，但求解过程 将花费更多的时间。5、 “规划求解”不能达到最优解下面列出“规划求解”显示的完整消息。“规划求解”不能改进当前解。所有约束条件都得到了满足这表明仅得到近似值，迭代过程无法得到比显示结果更精确的数值；或是无法进一步 提高精度，或是精度值设置得太小，请在“规划求解选项”对话框中试着设置较大的精度 值，然后再运行一次。求解达到最长运算时间后停止这表明在达到最长运算时间限制时，没有得到满意的结果。若要保存当前结果并节省 下次计算的时间，请单击“保存规划求解”或“保存方案”选项。求解达到最大迭代次数后停止这表明在达到最大迭代次数时，没有得到满意的结果。增加迭代次数也许有用，但是实用标准文案文档应该先检查结果数值来确定问题的原因。若要保存当前值并节省下次计算的时间，请单击 “保存规划求解”或“保存方案”选项。目标单元格中的数值不收敛这表明即使满足全部约束条件，目标单元格数值也只是有增或有减但不收敛。这可能是在设置问题时忽略了一项或多项约束条件。请检查工作表中的当前值，确定数值发散的 原因，并且检查约束条件，然后再次求解。“规划求解”未找到合适结果这表明