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

1、Excel 求解线性规划问题实验教程数学与信息科学学院Excel 求解线性规划问题实验 教 程二零一三零八月目 录1. 关于“规划求解” .1 2.如何加载“规划求解” .2 3.“规划求解”各参数解释和设置 .3 4.“规划求解”的步骤 .6 5.Excel求解线性规划问题8 6.Excel求解运输问题.14 7.Excel求解目标规划问题18 8.Excel22求解整数规划问题1. 关于“规划求解”“规划求解”是Excel 中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格 ( 被称为目标单元格 ) 中公式 ( 公式 : 单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新

2、的值。公式总是以等号(=) 开始 ) 的最优值。“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。“规划求解”通过调整所指定的可更改的单元格 ( 可变单元格 ) 中的值，从目标单元格公式中求得所需的结果。在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件( 约束条件 : “规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。而且约束条件可以引用其它影响目标单元格公式的单元格。使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。 )Mic

3、rosoft Excel 的“规划求解”工具取自德克萨斯大学奥斯汀分校的 Leon Lasdon 和克里夫兰州立大学的 Allan Waren 共同开发的 Generalized ReducedGradient(GRG2) 非线性最优化代码。线性和整数规划问题取自 Frontline Systems 公司的 John Watson 和 Dan Fylstra 提供的有界变量单纯形法和分支边界法。12. 如何加载“规划求解”安装 office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。下面是加载“规划求解”宏的步骤:(1) 在“工具”菜单上，单击“加载宏”。(2)

4、在弹出的对话框中的“可用加载宏”列表框中，选定待添加的加载宏“规划求解”选项旁的复选框，然后单击“确定”。单击“确定”以后，“工具”菜单下就会出现一项“规划求解”命令。如果需要其他功能，也可以用鼠标勾选。注意:加载的宏越多， Excel 启动的时候就会越慢，所以应工具需要选择。(3) 如果要卸载已经加载的宏，请在“可用加载宏”列表框中，选定待添加的加载宏选项旁的复选框，然后单击“确定”。23. “规划求解”各参数解释和设置单击“规划求解”按钮，将会出现以下的规划求解参数的对话框。设置目标单元格 : 一些单元格、具体数值、运算符号的组合。注意 : 目标单元格一定要是公式，即一定是以“ =”开始。

5、在应用 Excel 的“规划求解”命令求解线性规划问题时，目标函数对应的单元格就是目标单元格。最大值、最小值 : 根据线性规划问题的目标函数是求最大(max) 还是求最小(min) ，进行相应设置。如果需要指数值，可在右侧编辑框中输入数值。可变单元格 : 在此指定可变单元格。其实可变单元格就是线性规划问题在Excel中决策变量所在单元格。可变单元格必须直接或间接地与目标单元格相关联。推测 : 单击此按钮，自定推测“设置目标单元格”框中的公式所引用的所有非公式单元格，并在“可变单元格”框中定为这些单元格的应用。注意 : 实际进行计算时，这个命令几乎不用。约束 ; 在此列出规划求解得所有约束条件。

6、添加 : 显示“添加约束”对话框。在该对话框中，设置线性规划问题中相应的约束条件。更改 : 显示“更改约束”对话框。注意 : 单击此按钮的时候，要先选择需要更改的约束。3删除 : 删除选定的约束条件。同样单击此按钮前，要先选择需要删除的约束。求解 : 对定义好的问题进行求解。关闭 : 关闭对话框，不进行规划求解。但保留通过“选项”、“添加”、“更改”或“删除”按钮所做的更改。也就是说，当你下次再次单击“规划求解”按钮后，对话框显示上回所设置的参数。选项 : 显示“规划求解选项”对话框。在其中可加载或保存规划求解模型，并对求解过程的高级属性进行控制。最长运算时间 : 在此设定求解过程的时间。可输

7、入的最大值为32767( 秒 ) ，默认值 100( 秒) 可以满足大多数小型规划求解的要求。注意 : 我们在求解线性规划问题时，该项一般不用改变。迭代次数 : 在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大值为 32767，默认值 100 次可满足大多数小型规划求解要求。注意 : 我们在求解线性规划问题时，该项一般不用改变。精度 : 在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值或上下限。精度值必须表示为小数 (0 到 1 之间 ) ，输入数字的小数位数越多，精度越高。例如 :0.0001 比 0.01 的精度高。注意 : 我们在求解线性规划问

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

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

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

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

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

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

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

15、 可以终止求解过程， Excel 将按最后找到的可变单元格的数值重新计算工作表。若求出解，请在“报告”框中单击一种报表类型，再单击“确定”。报表保存在工作簿中新生成的工作表上。75.Excel 求解一般线性规划问题例 5-1 (唯一解 )Excel 建立线性规划模型及其求解实验目的 : 掌握在 Excel 中建立线性规划模型和求解方法; 会查看唯一解得情况实验内容 : 求解下列线性规划模型 ( 课本 P28)max2zxx, ，12515x,2,6224xx，, 12,st.,xx，,512,xj,0(1,2)j,实验步骤 :第一步 : 把线性规划模型反映在Excel 表格中。如下图所示 :图

16、中，注意 F5,F6,F7 单元格中的 0 分别表示约束条件的右端项表达式。即单元格F5 中应用了公式:=sumproduct(B5:C5,B9:C9),单元格F6 中应用了公式 :=sumproduct(B6:C6,B9:C9),单元格F7 中应用了公式 :=sumproduct(B7:C7,B9:C9) 。目标单元格 G2表示目标函数值，在 G2中输入了公式 :=sumproduct(B2:C2,B9:C9)。事实上，单元格B9，C9 分别表示了决策变量。xx,12第二步 : 设置规划求解参数打开求解规划参数对话框，进行相应的参数设置，如下图所示:8其中，“选项”中选取“假定非负”和“采用

17、线性模型”，其他采用默认项，如下图所示 :第三步 : 求解设置完毕后，点击“求解”按钮，出现如下图所示对话框:该图中“报告”栏显示三类报告，意味着该线性规划问题有唯一最优解，根据需要选择相应的报告，单击“确定”按钮，完成运算，在Excel 中，相应的计算结果也会显示出来，结果如下:9可以看出，该线性规划模型的最优解为见单元格 (B9，C9),xx,3.5,1.512最优目标函数值为 8.5( 见单元格 G2)另外，根据“规划求解结果”对话框中在报告一项的选择情况，也可以看到相关结果。如果选择“运算结果报告”，可得如下图所示结果:从这个报告中，可知，最优目标函数值为 8.5 ，可变单元格 B9,

18、C9 对应的值为 3.5,1.5 即是最优解 ; 约束一项反映了在最优的情况下，实际资源的使用情况，对于条件 1，未到限制状态，型数值7.5 表示资源剩余 7.5; 而条件 2，条件 3 的状态显示达到限制值，型数值都为0 表示资源全部用完。备注 : 在用 Excel 求解线性规划问题时，涉及到的数如果有分数，则转化为小数，或是对模型适当变形，使相关系数全部为整数，避免出现分数。10例 5-2无穷多最优解情况的区分实验目的 : 会区别报告中暗示的“无穷多最优解”情况;实验内容 : 求解下列线性规划模型max3zxx, ，12515x,2,6224xx，, 12,st.,xx，,512,xj,0

19、(1,2)j,通过图解法可知该线性规划问题的解为无穷多最优解，如下图所示:线段 AB上的点都是最优解，该线性规划问题有无穷多最优解。应用 Excel 求解如下 :、二步与例 1 类似设置，但在第三步求解时，规划求解对话框如下所示:第一这个“规划求解结果”对话框情况与例1 的“唯一最优解”情况一样。在Excel 表格中，计算结果显示如下:11通过上图可知，在线性规划问题具有无穷多最优解情况下，Excel 只能计算出其中的一个最优解。例 2 的 Excel 应用可知， Excel 对线性规划问题具有无穷多最优通过例1 与解与唯一最优解的情况不能区分。例 5-3无界解情况的区分实验目的 : 会区别报

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

21、4 无解情况的区分实验目的 : 会区别报告中暗示的“无解”情况;实验内容 : 求解下列线性规划模型max2zxx, ，12xx ，,2,12 ,stxx.226， ,12,xx,0,12,对于该线性规划问题，很容易知道它无可行解。Excel 求解 :第一、二步与例1 类似设置，但在第三步求解时，规划求解对话框如下所示:从该对话框的“规划求解好不到有用的解”可知，该线性规划问题无解。136. Excel 求解运输问题实验目的 : 掌握 Excel 求解运输问题方法 ;实验内容 : 求解下列产销平衡的运输问题销售点产量BBBB1234加工厂 ( 吨)311310A7 11928A4 274105A

22、9 3销量 ( 吨) 3 6 5 6 20在满足各销售点需求量的前提下，使总运费最小,解 : 设 x 表示加工厂运往销售点Bj(1,2,3,4),的运输量，考虑到产Ai(1,2,3),ijji量与销量相等，可建立产销平衡模型如下:，111213142122232431323334xxxx ， ,7,11121314,xxxx ， ,421222324,xxxx ， ,931323334,xxx ， ,3 ,112131st.,xxx， ,6122232,xxx ， ,5132333,xxx ， ,6,142434,xij,0(1,2,3;1,2,3,4)ij,运输问题是一种特殊的线性规划问题，

23、理论上一般采用“表上作业法”求解运输问题，但 Excel 的“规划求解”还是采用“单纯形法”来求解。Excel 求解过程如下 :第一步 : 根据运输问题特点，建立电子表格模型，如下图所示:14在上图中，黄色框内表示初始运输方案，可全部记为0。实际销量一行，事实上是由各产地到某销地的实际运输量相加得到，实际产量一列由相应的产地到各销地的实际运输量相加得到。总费用对应的单元格J3 为目标单元格，该单元格中输入了公式 :=sumproduct(b3:e5,b9:e11),事实上，它是由图中青绿色单元格与黄色单元格对应位置相乘再相加得到的。第二步 : 利用“规划求解”命令求解。首先打开求解规划参数对话

24、框，进行相应的参数设置，如下图所示:其次，打开“选项”按钮进行设置，设置结果如下图所示:15点击“确定”后回到“规划求解参数”对话框。最后，点击“求解”命令，进入“规划求解结果”对话框，如下图所示:可以看出，该运输问题存在最优解，在“报告”一栏，可以选择显示的3 个报告，也可以不选择，直接点击“确定”按钮，可在第一步建立的电子表格模型中显示最优解。第三步，确定最优解。如下图所示:从上述表格可以很容易看出最优运输方案 ( 黄色框内 ) 和对应的最小总费用 ( 见单元格 J3) 。16当然，也可以根据建立的数学模型，应用线性规划模型求解方式求解，但没有上述这种方式方便。177. Excel 求解目

25、标规划问题 实验目的 : 掌握 Excel 求解目标规划问题方法 ;实验内容 : 求解下列目标规划问题, ，,min,PdPdPd11223351060xx，,12,， xxdd20,，,1211, , ，stxxdd.4436， ,1221, ，6848xxdd， ,1233,，,xxddi,0 (1,2,3),12ii实验过程 :第一步 : 首先保证级目标的实现，这时不考虑其他次级目标。P1此时模型变为 :,min zd,1151060xx，,12,， xxdd, ，,201211, ,，stxxdd.4436， ,1221, ，6848xxdd， ,1233,，,xxddi,0 (1,2

26、,3),12ii首先，根据这个模型建立电子表格模型( 与线性规划模型类似 ) 如下图所示 :注意 : 图中，单元格 B6 中输入公式 :=d2; 左端表达式一列分别是用 B4:I4 一行与黄色方框内各行相乘再相加得到。其次，应用“规划求解”命令，打开“规划求解参数”对话框，进行相关设置，设置情况如下图所示 :18同时打开“选项”按钮进行设置，主要是考虑非负和线性情况。最后，点击“求解”命令，可得结果如下:, 可以看出，目标函数为 0，即级目标对应的。 d,0P11第二步，在保证级目标实现的基础上考虑级目标。PP12, 此时，把 d,0 加入第一步的模型中，目标函数变为级对应的目标，此时P12的

27、模型为 :， min zd,2251060xx，,12,， xxdd, ，,201211, ，,4436xxdd ， , ,1221st.,，6848xxdd， ,1233,d,01,，,xxddi,0 (1,2,3),12ii仿照第一步的做法，对该模型建立电子表格，并应用规划求解命令，同时进行相关设置，可得结果如下:19注意 : 在该图中，单元格B6 中输入了公式 :=g4; 从图中也可以看出，对于，第二级目标也能实现，即有。d,02第三步，在保证级目标和级目标的基础上，考虑级目标。PPP123，此时，在第二步模型基础上，添加约束条件, 目标函数变为级对应d,0P23 的目标，所得模型如下 :,min zd,3351060xx，,12,， xxdd, ，,201211, ，,4436xxdd ， ,1221, ,， stxxdd.6848， ,1233,d,01,，,d,02,，xxddi,0 (1,2,3),12,ii，可在第二步建立的电子表格基础上，添加约束条件d,0 ，并更改目标 2 函数，按照第一步类似做法，应用“规划求解”命令，可得计算结