Excel求解线性规划问题实验教程要点

1、数学与信息科学学院exce解线性规划问题实验教程1 .关于“规划求解” 12 . 如何加载“规划求解” 23 . “规划求解”各参数解释和设置 34 . “规划求解”的步骤 65 .excel求解线性规划问题 86 .excel求解运输问题 147 .excel求解目标规划问题 188 .excel求解整数规划问题 221 . 关于“规划求解”“规划求解”是excel 中的一个加载宏，借助“规划求解” ，可求得工作表上某个单元格（被称为目标单元格）中公式（公式: 单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号（ = ）开始）的最优值。 “规划求解”将对直接或间

2、接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。 “规划求解”通过调整所指定的可更改的单元格（可变单元格） 中的值， 从目标单元格公式中求得所需的结果。在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件（约束条件： “规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、 目标单元格或其它与目标单元格直接或间接相关的单元格。 而且约束条件可以引用其它影响目标单元格公式的单元格。使用“规划求解” 可通过更改其它单元格来确定某个单元格的最大值或最小值。 ）microsoft excel 的“规划求解”工具取自德克萨斯大学奥斯汀分校的

3、 leonlasdon 和克里夫兰州立大学的 allan waren 共同开发的 generalized reducedgradient（grg2） 非 线性 最 优化 代码 。线 性和 整 数规 划问 题取自 frontline systems公司的john watson和danfylstra 提供的有界变量单纯形法和分支边 界法。152 .如何加载“规划求解”安装ofice的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装卜面是加载“规划求解”宏的步骤:(1)在“工具”菜单上，单击“加载宏”工具|数据窗口富辛 晋 捅写精查f7ii信息检索电)alt+clxck。指误检

4、查勺_二共享工作区®共享工作簿宜 保护©联机协作® 必式审核包 执划求解® 加载宏© 一 自日© . 选项 数据分析(2)在弹出的对话框中的“可用加载宏”列表框中，选定待添加的加载宏“规划求解”选项旁的复选框，然后单击“确定”。单击“确定”以后，“工具”菜单下就会出现一项“规划求解”命令。如果需要其他功能，也可以用鼠标勾选注意：加载的宏越多,excel启动的时候就会越慢，所以应工具需要选择(3)如果要卸载已经加载的宏，请在“可用加载宏”列表框中，选定待添加的 加载宏选项旁的复选框，然后单击“确定”。3 .“规划求解”各参数解释和设置单

5、击“规划求解”按钮，将会出现以下的规划求解参数的对话框 设置目标单元格：一些单元格、具体数值、运算符号的组合。注意：目 标单元格一定要是公式，即一定是以“=开始。在应用excel的“规划求 解”命令求解线性规划问题时，目标函数对应的单元格就是目标单元格。 最大值、最小值：根据线性规划问题的目标函数是求最大( ma%还是求 最小(min),进行相应设置。如果需要指数值，可在右侧编辑框中输入数值。 可变单元格：在此指定可变单元格。其实可变单元格就是线性规划问题 在excel中决策变量所在单元格。可变单元格必须直接或间接地与目标单元 格相关联。 推测：单击此按钮，自定推测“设置目标单元格”框中的公式

6、所引用的 所有非公式单元格，并在“可变单元格”框中定为这些单元格的应用。注意: 实际进行计算时，这个命令几乎不用。 约束；在此列出规划求解得所有约束条件。 添加：显示“添加约束”对话框。在该对话框中，设置线性规划问题中相应的约束条件。 更改：显示“更改约束”对话框。注意：单击此按钮的时候，要先选择 需要更改的约束。 删除：删除选定的约束条件。同样单击此按钮前，要先选择需要删除的约束 求解：对定义好的问题进行求解 关闭：关闭对话框，不进行规划求解。但保留通过“选项”、“添加”、 “更改”或“删除”按钮所做的更改。也就是说，当你下次再次单击“规划 求解”按钮后，对话框显示上回所设置的参数。 选项：

7、显示“规划求解选项”对话框。在其中可加载或保存规划求解模型，并对求解过程的高级属性进行控制 最长运算时间：在此设定求解过程的时间。可输入的最大值为32767（秒），默认值100 （秒）可以满足大多数小型规划求解的要求。注意：我们在求解线性规划问题时，该项一般不用改变 迭代次数：在此设定求解过程中迭代运算的次数， 限制求解过程的时间可输入的最大值为32767,默认值100次可满足大多数小型规划求解要求注意：我们在求解线性规划问题时，该项一般不用改变 精度：在此输入用于控制求解精度的数字，以确定约束条件单元格中的 数值是否满足目标值或上下限。精度值必须表示为小数（0到1之间），输 入数字的小数位数

8、越多，精度越高。例如：0.0001比0.01的精度高。注意: 我们在求解线性规划问题时，该项一般不用改变。 允许误差：在此输入满足整数约束条件并可被接受的目标单元格求解结 果与真实的最佳结果间的百分偏差。 这个选项只应用于具有整数约束条件的问题。设置的允许误差值越大，求解过程就越快。注意：我们在求解线性规 划问题时，该项一般不用改变。 收敛度：在此输入收敛度值，当最近五次迭代后目标单元格中数值的变 化小于“收敛度”框中设置的数值时，“规划求解”停止运行。收敛度只应 用于非线性规划求解问题，并且必须表示为（0至u1之间）。设置的数值越 小，收敛度就越高。例如，0.0001表示比0.01更小的相对

9、差别。收敛度越 小，“规划求解”得到结果所需的时间就越长。 采用线性模型：当模型中的所有关系都是线性的，并且希望解决线性优化 问题时，选中此复选框可加速求解进程。 显示迭代结果：如果选中此复选框，每进行一次迭代后都将中断“规划 求解”，并显示当前的迭代结果。 自动按比例缩放：如果选中此复选框，当输入和输出值量级差别很大时， 可自动按比例缩放数值。例如，基于百万美元的投资将利润百分比最大化。 假定非负：如果选中此复选框，则对于在“添加约束”对话框的“约束 值”框中没有设置下限的所有可变单元格，假定其下限为0 （零）。 估计：指定在每个一维搜索中用来得到基本变量初始估计值的逼近方案。 正切函数：使

10、用正切向量线性外推。 二次方程：用二次方程外推法，提高非线性规划问题的计算精度。 导数：指定用于估计目标函数和约束函数偏导数的差分方案。 向前差分：用于大多数约束条件数值变化相对缓慢的问题。 中心差分：用于约束条件变化迅速，特别是接近限定值的问题。虽然此 选项要求更多的计算，但在“规划求解”不能返回有效解时也许会有帮助。 搜索：指定每次的迭代算法，以确定搜索方向。 牛顿法：用准牛顿法迭代需要的内存比共腕法多。 但所需的迭代次数少。 共腕法：比牛顿法需要的内存少，但要达到指定精度需要较多次的迭代 运算。当问题较大和内存有限，或迭代进程缓慢时，可用此选项。 装入模型：显示“装入模型”对话框，输入对

11、所要加载的模型的引用。 保存模型：显示“保存模型”对话框，在其中可指定保存模型的位置。只有需要在工作表上保存多个模型时，才单击此命令。第一个模型会自动保 存。4.“规划求解”的步骤(1)首先在excel表格上建立模型，然后单击“规划求解”按钮，出现“规 划求解参数”对话框；(2)在“设置目标单元格”对话框中，输入目标单元格的单元格引用(单 元格引用：用于表示单元格在工作表上所处位置的坐标集)。(3)确定目标单元格中数值是最大还是最小，进行相应选择。如果要使目 标单元格中数值为确定值，单击“值为”，再在编辑框中键入数值。(4)在“可变单元格”框中，输入每个可变单元格的名称或引用，用逗号 分隔不相

12、邻的引用。可变单元格必须直接或间接与目标单元格相联系。最多 可指定200个可变单元格。若要使“规划求解”基于目标单元格自动设定可 变单元格，可单击“推测”。(5)在“规划求解参数”对话框的“约束”下，单击“添加”。(6)在“单元格引用位置”框中，输入需要对其中数值进行约束的单元格引用。其实是对应线性规划问题中约束条件的左端项(在 excel中用单元格 表示)。(7)单击希望在引用单元格和约束条件(约束条件：“规划求解”中设置 的限制条件。可以将约束条件应用于可变单元格、目标单元格或其它与目标 单元格直接或间接相关的单元格。)之间使用的关系(“<="、"="、

13、">=”、“int ”或“bin”)o如果单击“int ”，则“约束值”框中会显示“整数”； 如果单击“ bin ”，则“约束值框”中会显示“二进制”，表示取 0或1。(8)在“约束值”框中，键入数字、单元格引用或名称，或键入公式(公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)开始。)(9)若要接受约束条件并要添加其他约束条件，请单击“添加”按钮。若 要接受约束条件并返回“规划求解参数”对话框，单击“确定”。（10）注意：只能在可变单元格的约束条件中应用“int ”和“ bin ”关系。当“规划求解选项”对话框中的“采用线性模型”复

14、选框被选中时，对约束条件的数量没有限制。对于非线性问题，每个可变单元格除了变量的范围和整数限制外，还可以有多达100 个约束。（ 11）更改或者删除约束。在“规划求解参数”对话框的“约束”下，单击要更改或删除的约束条件（约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、 目标单元格或其它与目标单元格直接或间接相关的单元格）单击“更改”，并进行所需的更改，或单击“删除”。（ 12）单击“求解”，再执行下列操作之一：若要在工作表中保存求解后的数值，请在“规划求解结果”对话框中，单击“保存规划求解结果”；若要恢复原始数据，请单击“恢复为原值”。注意：按esc可以终止求解过程,e

15、xcel 将按最后找到的可变单元格的数值重新计算工作表。若求出解，请在“报告”框中单击一种报表类型，再单击“确定”。报表保存在工作簿中新生成的工作表上。5.excel求解一般线性规划问题例5-1(唯一解)excel建立线性规划模型及其求解实验目的：掌握在excel中建立线性规划模型和求解方法； 会查看唯一解得 情况实验内容：求解下列线性规划模型(课本 p28)max z = 2x1 x25x2 <156x1 +2x2 <24s.t 12x +x2 < 5xj 至0(j =1,2)j实验步骤:第一步：把线性规划模型反映在 excel表格中。如下图所示:图中，注意f5,f6,f7

16、单元格中的0分别表示约束条件的右端项表达式。即 单元格f5中应用了公式：=sumproduct(b5c5,b9c9), 单元格f6中应用了公式： =sumproduct(b6c6,b9c9),单元格 f7 中应用了 公式：=sumproduct(b7:c7,b9:c9)。目标单元格 g2表示目标函数值，在g2中输入了公 式：=sumproduct(b2:c2,b9:c9)。事实上，单元格b9, c9分别表示了决策变量 ox?第二步：设置规划求解参数打开求解规划参数对话框，进行相应的参数设置，如下图所示:设董目标单元格d）工等于：才最大值®最小值®值为o可变单元格®

17、 ：其中，“选项”中选取“假定非负”和“采用线性模型”，其他采用默认项，如下 图所示：最长运苴时阖夏）：精度口：先在俣差3西彳%|100脚肖,000001装入模型通).|5保存模型)收敛度理）：r采用性错型q7假超e负-估计公正切函数®：o方程）|0001r电动榭r显示渐-翱0向前差分0申心凄分©帮助.要匕厮故阳匕结果®-搜索在牛顿法如共葩法电）第三步：求解设置完毕后，点击“求解”按钮，出现如下图所示对话框:4保存规划求解结果电） 恢复为原值）相划求解找到一斛可满足所有的约束及最优概况报告如果1* i 鹿消i保存方案一）.- i 帮助出）该图中“报告”栏显示三类报

18、告，意味着该线性规划问题有唯一 据需要选择相应的报告，单击“确定”按钮，完成运算，在 excel 算结果也会显示出来，结果如下：最优解，根 中，相应的计可以看出，该线性规划模型的最优解为xi =3.5,x2=1.5见单元格（b9, c9 ,最优目标函数值为8.5 （见单元格g2另外，根据“规划求解结果”对话框中在报告一项的选择情况，也可以看到 相关结果。如果选择“运算结果报告”，可得如下图所示结果：目标单元格（最大值；单元格初值 终值$g$2目标函数系数目标函数值 8,58. 5可变单元格单元格名字初值 终值$b$93.53.5$c$9l5l5约奥单元格 名字$f$5 条件1班$6一条件2$f

19、$7 案拜3单元格值 公式状态_型数值7. 5 $f|5<=jh|5未到限制值 -524 $f$6«$h$6到达限制值5 $f$t<斗h$7到达限制值从这个报告中，可知，最优目标函数值为 8.5,可变单元格b9,c9对应的值 为3.5,1.5即是最优解；约束一项反映了在最优的情况下，实际资源的使用情况， 对于条件1,未到限制状态，型数值7.5表示资源剩余7.5;而条件2,条件3 的状态显示达到限制值，型数值都为 0表示资源全部用完。备注：在用excel求解线性规划问题时，涉及到的数如果有分数，则转化 为小数，或是对模型适当变形，使相关系数全部为整数，避免出现分数。例5-2

20、 无穷多最优解情况的区分实验目的：会区别报告中暗示的“无穷多最优解”情况;实验内容：求解下列线性规划模型max z = 3x1 x25x2 <156x1 +2x2 <24 s.tx1 +x2 <5xj >0(j =1,2) j通过图解法可知该线性规划问题的解为无穷多最优解，如下图所示:线段ab上的点都是最优解，该线性规划问题有无穷多最优解 应用excel求解如下：规划求解对话框如下所示:柳划求解找到一解，可,是所宥的约束及最优状况报告®才保存规划求解结果的 恢复为原值。）第一、二步与例1类似设置，但在第三步求解时,fi刁 吉母口告 果由 hi褊定i 明清1保存

21、方案一 , ！ 帮助比）i这个“规划求解结果”对话框情况与例1的“唯一最优解”情况一样。在excel表格中，计算结果显示如下：目标函数系数决策变量0他束未住春救力克举完格05620<=1524<=24目标函数值12目标单元格目标值*致|有1 2 3 束件件件 约条条条通过上图可知，在线性规划问题具有无穷多最优解情况下，excel只能计算出其中的一个最优解。通过例1与例2的excel应用可知，excel对线性规划问题具有无穷多最优解与唯一最优解的情况不能区分。例5-3 无界解情况的区分实验目的：会区别报告中暗示的“无界解”情况；实验内容：求解下列线性规划模型max z = 300x1

22、 500x2x1 x2 - 45. t «x1,x2 0图解法：通过图解法可知，该线性规划问题的可行域无界，最优解无界。如 下图所示：可以看出，目标函数直线往右上方平移，可以一直平移下去，而对应的目标 函数值会一直增大下去，可知该线性规划问题具有无界解。excel求解:第一、二步与例1类似设置，但在第三步求解时，规划求解对话框如下所示:从这个“规划求解结果”参数对话框的“报告”栏，可看出运算结果报告、 敏感性报告、极值报告都不可用，同时从对话框中“设置目标单元格的值未收敛 知该线性规划问题具有无界解。例5-4 无解情况的区分实验目的：会区别报告中暗示的“无解”情况；实验内容：求解下列

23、线性规划模型maxz = 2k x2xi x2 2st42xi +2x2 >6xi,x2 0对于该线性规划问题，很容易知道它无可行解。excel求解:第一、二步与例1类似设置，但在第三步求解时，规划求解对话框如下所示:从该对话框的“规划求解好不到有用的解”可知，该线性规划问题无解6. excel求解运输问题实验目的：掌握excel求解运输问题方法;解：设xj表示加工厂a (i =1,2,3)运往销售点8上”=1,2,3,4)的运输量，考虑到产量与销量相等，可建立产销平衡模型如下:min z =3x1111x123x1310x14x219x222x238x247x314x3210x335x

24、34xii +x2 +x3 +x4 =7x21 “22 +%3 +乂24 =4x31 + x32 + x33 + x34 = 9s.txii +x21 +x31 =3x12 *x22 *x32 =6x13 *x23 *%3 = 5x14 +乂24 +乂34 =6xj 之0(i =1,2,3;j =1,2,3,4)运输问题是一种特殊的线性规划问题， 理论上一般采用“表上作业法”求解 运输问题，但excel的“规划求解”还是采用“单纯形法”来求解。excel求解过程如下:第一步：根据运输问题特点，建立电子表格模型，如下图所示:在上图中，黄色框内表示初始运输方案，可全部记为 00实际销量一行，事 实

25、上是由各产地到某销地的实际运输量相加得到， 实际产量一列由相应的产地到 各销地的实际运输量相加得到。总费用对应的单元格j3为目标单元格，该单元格中输入了公式: =sumproduct(b3:e5,b9:e11), 事实上，它是由图中青绿色单元格与黄色单元格对 应位置相乘再相加得到的。第二步：利用“规划求解”命令求解。首先打开求解规划参数对话框，进行相应的参数设置，如下图所示:设置目标单元格0： 而一s等于：最大值也)*最小值但广值为。d其次，打开“选项”按钮进行设置，设置结果如下图所示:点击“确定”后回到“规划求解参数”对话框。婉划求解抵到一解，可莉足所有的约束及最优 状况口'保存妮则

26、求解结果00悔复为原值报雷时最后，点击“求解”命令，进入“规划求解结果”对话框，如下图所示:枕保存方案切器期血可以看出，该运输问题存在最优解，在“报告” 一栏，可以选择显示的3个报告，也可以不选择，直接点击“确定”按钮，可在第一步建立的电子表格模型 中显小最优解。第三步，确定最优解。如下图所示:从上述表格可以很容易看出最优运输方案 （黄色框内）和对应的最小总费用（见当然， 也可以根据建立的数学模型，应用线性规划模型求解方式求解，但没有上述这种方式方便。257. excel求解目标规划问题实验目的：掌握excel求解目标规划问题方法；实验内容：求解下列目标规划问题min rd - p2d2 ,p

27、3d315xi +10x2 <60x1 -2x2 +dj-d1+=0s.t <4x1 +4x2 +d2- d1+=366x1 +8x2 +d3-_d： = 48 xi,x2,di-di+>0 (i =1,2,3)实验过程：第一步：首先保证pi级目标的实现，这时不考虑其他次级目标。此时模型变为：min z1 = d1 一5x1 +10x2 <60x1 -2x2 +d1-d1+= 0s.t <4x1 +4x2 +df-d/=366x1 +8x2 +d3-_d3+=48多,x2,d1,di+之 0 (i =1,2,3)首先，根据这个模型建立电子表格模型(与线性规划模型类

28、似)如下图所示:abcdefg1第tp123决策变量k1x2di-d】/必d2+q0000056目标函数：07gq约束条件至钢坦苒105100000111-217001244001-1136s0000hijklrd3-d/0左赭恚达式约束条杵右潴常数国000<=再口0000000三疑1to=4b注意：图中，单元格b6中输入公式：=d2;左端表达式一列分别是用 b4:i4 一行与黄色方框内各行相乘再相加得到。其次，应用“规划求解”命令，打开“规划求解参数”对话框，进行相关设 置，设置情况如下图所示:同时打开“选项”按钮进行设置，主要是考虑非负和线性情况。 最后，点击“求解”命令，可得结果如

29、下：acdbfghijl鼠l第t及pi23决策变量xlx2dl-dl +d2-d2+d3-d3+q4.82.400- 200056目标函数：07s9约束条件祭救矩阵左端表达式48<=约束条件右鞠用敌顶 601051000000011-21-100000=01246480001-1000001-13648=3613=48可以看出，目标函数为0,即p1级目标对应的d= 0第二步，在保证pi级目标实现的基础上考虑 p2级目标。此时，把di=0加入第一步的模型中，目标函数变为 p2级对应的目标，此时的模型为:minz2 = d25x1 +10x2 < 60x -2x2 +二04xi + 4

30、x2 +d2-d/=36s.t6k +8x2 +dl-d/=48旷=0+.x,x2,d,di -0 (i =1,2,3)仿照第一步的做法，对该模型建立电子表格，并应用规划求解命令，同时进行相关设置，可得结果如下:注意：在该图中，单元格b6中输入了公式：=g4;从图中也可以看出，对于第二级目标也能实现，即有d2+ = 0o第三步，在保证pi级目标和p2级目标的基础上，考虑p3级目标。此时，在第二步模型基础上，添加约束条件d：=0,目标函数变为e级对应的目标，所得模型如下：min z3 = d3"5x1 +10x2 <60x1 -2x2 +d1-d/=04x1 +4x2 +d_d1+=36s.t 6x1 + 8x2 +dld=48dj=0d2+=0xi,x2,di；di -0 (i =1,2,3)可在第二步建立的电子表格基础上，添加约束条件 d/=0,并更改目标 函数，按照第一步类似做法，应用“规划求解”命令，可得计算结果如下：该步得到的结果即是整个目标规划问题的满意解。可以发现，第三级目标也能够实现。特别说明：目标规划的求解，是在分步骤的情况下进行的，即是在求出第一 级目标的情况下，再求第二级目标，依次类推，但要注意是在满足上级目标的 情况下，再求下