EXCEL求解线性规划问题PPT课件

1、 一、一、EXCEL 基本知识基本知识 1、命名工作表、命名工作表 功能：功能： 存储信息、进行计算、排序数据、用图或表的形存储信息、进行计算、排序数据、用图或表的形式显示数据、规划求解、财会分析、概率与统计分析等式显示数据、规划求解、财会分析、概率与统计分析等等等（1）激活工作表）激活工作表1，单击，单击sheet 1 标签标签（2）命名工作表）命名工作表 或或 （3）Format / sheet Rename 显示显示Rename sheet 对话框对话框（4）键入工作表名）键入工作表名第1页/共43页 2、工作表、工作表 4、进行计算、进行计算 Excel-2003 256列列 6553

2、5 行行Excel-2007 16384列列 1048576行行 3、向单元格中输入数据、向单元格中输入数据常数：不能改变的文字或其它数据常数：不能改变的文字或其它数据公式：输入到单元格中的数字值，或返回数字值的表达式。公式：输入到单元格中的数字值，或返回数字值的表达式。（1）输出数据时计算）输出数据时计算A1 =456+789（2）根据其它单元格的数据进行计算）根据其它单元格的数据进行计算A3456 B3-789 C3=A3+B3第2页/共43页 5、在公式中引用其它单元格、在公式中引用其它单元格 在公式中可以引用本工作簿中任何单元格或单元格在公式中可以引用本工作簿中任何单元格或单元格组的数

3、组的数 据，也可以引用其它工作簿中任何单元格或据，也可以引用其它工作簿中任何单元格或单元格组的数据。引用单元格数据后，公式的运算单元格组的数据。引用单元格数据后，公式的运算值将随着被引用的单元格数据变化而变化。值将随着被引用的单元格数据变化而变化。（1）引用的类型）引用的类型三种类型三种类型 ：相对引用、相对引用、 绝对引用、混合引用绝对引用、混合引用第3页/共43页格式：格式： A3 、B6（2） 相对引用相对引用 使用相对引用后，系统将会记住建立公式的单元格和被使用相对引用后，系统将会记住建立公式的单元格和被引用的单元格的相对位置，在复制这个公式时，新的公式单引用的单元格的相对位置，在复制

4、这个公式时，新的公式单元和被引用的单元仍然保持这种相对位置。元和被引用的单元仍然保持这种相对位置。第4页/共43页（3）绝对引用）绝对引用 绝对引用是指被引用的单元与引用的公式单元的位置绝对引用是指被引用的单元与引用的公式单元的位置关系是绝对的，无论将这个公式复制到任何单元，公式所关系是绝对的，无论将这个公式复制到任何单元，公式所引用的还是原来单元格的数据。引用的还是原来单元格的数据。（4）混合引用）混合引用 格式：格式：$a$3 $d$5格式：格式： $A3 列是绝对的，行是相对的列是绝对的，行是相对的 B$ 3 列是相对的，行是绝对的列是相对的，行是绝对的第5页/共43页使用Excel进行

5、求解1.关于“规划求解”2.如何加载“规划求解”3. “规划求解”各参数设置4. “规划求解”步骤5. 利用“规划求解”解线性规划问题第6页/共43页2.如何加载“规划求解”1) 在“工具”菜单上,单击“加载宏”第7页/共43页2) 在弹出的对话框中的“可用加载宏”列表框中,选定待添加的加载宏“规划求解”选项旁的复选框,然后单击“确定”.单击“确定”后,“工具”菜单下就会出现一项“规划求解”第8页/共43页3. “规划求解”各参数设置单击“规划求解”按钮,将会出现以下规划求解参数设置对话框第9页/共43页 单击“添加”,显示添加约束对话框第10页/共43页第11页/共43页 选项:显示”规划求

6、解选项”对话框.在其中可以加载或保存规划求解模型,并对规划求解过程的高级属性进行控制第12页/共43页线性规划求解步骤：1. 确定目标函数系数存放单元格，并在这些单元格中输入目标确定目标函数系数存放单元格，并在这些单元格中输入目标函数系数。函数系数。2. 确定决策变量存放单元格，并任意输入一组数据；确定决策变量存放单元格，并任意输入一组数据；3. 确定约束条件中左端项系数存放单元格，并输入约束条件左确定约束条件中左端项系数存放单元格，并输入约束条件左端项系数；端项系数；4. 在约束条件左端项系数存放单元格右边的单元格中输入约束在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算

7、公式，计算出约束条件左端项对应于目前决条件左端项的计算公式，计算出约束条件左端项对应于目前决策变量的函数值。策变量的函数值。5. 在步骤在步骤4的的数据右边输入约束条件中右端项（即常数项）的的数据右边输入约束条件中右端项（即常数项）6. 确定目标函数值存放单元格，并在该单元格中输入目标函数确定目标函数值存放单元格，并在该单元格中输入目标函数值电容计算公式。值电容计算公式。第13页/共43页一个简单的例子 某工厂计划生产两种产品，利润分别为2和3，已知生产单位产品所需的设备台时和A、B两种原材料的消耗，如表产品产品1产品产品2设备设备128台时台时原材料原材料A4016KG原材料原材料B0412

8、KG 目标是不超过资源限制的情况下，确定两产品产量，得到最大利润。第14页/共43页建立数学公式（步骤一） 在工作表的顶部输入数在工作表的顶部输入数据据 确定每个决策变量所对确定每个决策变量所对应的单元格位置应的单元格位置 选择单元格输入公式，选择单元格输入公式，找到目标函数的值找到目标函数的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件左边的值左边的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件右边的值右边的值可采用可采用 复制粘贴复制粘贴 或或 直直接输入接输入 的方式导入数据。的方式导入数据。第15页/共43页建

9、立数学公式（步骤二） 在工作表的顶部输入数在工作表的顶部输入数据据 确定每个决策变量所对确定每个决策变量所对应的单元格位置应的单元格位置 选择单元格输入公式，选择单元格输入公式，找到目标函数的值找到目标函数的值 选择一个单元格输入公选择一个单元格输入公式，计算每个约束条件式，计算每个约束条件左边的值左边的值 选择一个单元格输入公选择一个单元格输入公式，计算每个约束条件式，计算每个约束条件右边的值右边的值图中，规定B12、C12为可变单元格可变单元格存放决策变量的取值，可变单元格数目等于决策变量个数可变单元格存放决策变量的取值，可变单元格数目等于决策变量个数第16页/共43页建立数学公式（步骤三

10、） 在工作表的顶部输入数在工作表的顶部输入数据据 确定每个决策变量所对确定每个决策变量所对应的单元格位置应的单元格位置 选择单元格输入公式，选择单元格输入公式，找到目标函数的值找到目标函数的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件左边的值左边的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件右边的值右边的值在目标单元格中，需要填入计算目标函数值的公式。在目标单元格中，需要填入计算目标函数值的公式。第17页/共43页建立数学公式（步骤四） 在工作表的顶部输入数在工作表的顶部输入数据据 确定每个决策变量所对确定每个决策

11、变量所对应的单元格位置应的单元格位置 选择单元格输入公式，选择单元格输入公式，找到目标函数的值找到目标函数的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件左边的值左边的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件右边的值右边的值在约束单元格中，需要填入计算约束函数值的公式。在约束单元格中，需要填入计算约束函数值的公式。第18页/共43页建立数学公式（步骤五） 在工作表的顶部输入数在工作表的顶部输入数据据 确定每个决策变量所对确定每个决策变量所对应的单元格位置应的单元格位置 选择单元格输入公式，选择单元格输入公式，找到

12、目标函数的值找到目标函数的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件左边的值左边的值 确定约束单元格输入公确定约束单元格输入公式，计算每个约束条件式，计算每个约束条件右边的值右边的值第19页/共43页调用 规划求解 模块 选择工具下拉菜单 选择规划求解选项（事先需用Office安装盘安装规划求解的功能）第20页/共43页填写目标单元格和可变单元格 出现规划求解参数对话框 在在目标单元格目标单元格中输入中输入B14B14 在在等于等于选择最大选择最大 在在可变单元格可变单元格中输入中输入B12:C12B12:C12 选择选择添加添加在上图显示的界面中，需

13、要输入目标单元格、可变单元格，添加约束条件，另外还可能需要进行选项设置。第21页/共43页添加约束 在添加约束对话框中，在单元格引用位置中输入B17，选择=，在约束值中输入D17。选择添加 第三个条件添加完毕后，选择确定 当规划求解参数对话框重新出现时，选择选项第22页/共43页“选项”设置 当选项对话框出现时，选择假设非负。选择确定第23页/共43页用Excel求解 出现规划求解参数对话框，选择求解。第24页/共43页保存求解结果 当求解结果对话框出现时，选择保存规划求解结果。选择确定。第25页/共43页运算结果报告列出目标单元格和可变单元格以及它们的初始值、最终结果、约束条件和有关约束条件

14、的信息。初值和终值分别指单元格在本次求解前的数值和求解后的数值。第26页/共43页敏感性分析报告（1）可变单元格中 “单元格”指决策变量所在单元格的地址 “名字”是决策变量的名称 “终值”是决策变量的终值,即最优值 “递减成本”指最优解中等于0的变量，对应的目标函数中的系数增加或减少多少，最优解不再为0 “目标式系数”目标函数中的系数，为已知条件 “允许的增量”与“允许的减量”表示目标函数中的系数在增量与减量的变化范围内变化时，最优解保持不变（注：最优值发生变化）第27页/共43页敏感性分析报告（2）约束单元格 “单元格”指约束条件左边所在单元格的地址 “名字”是约束条件左边的名称 “终值”是

15、约束条件左边的值 “阴影价格”指约束条件右边增加或减少一个单位，目标函数值增加或减少的数量 “约束的限制值”指约束条件右边的值，为已知条件 “允许的增量，减量”表示约束条件右边在允许的范围内变化时，影子价格不变，即约束条件右边的值每增加1个单位，目标函数值的增加仍然为影子价格的大小。因此，右端项在一定范围内变化时，影子价格不变，目标函数值的变动等于右端项变动值乘以影子价格第28页/共43页极限值报告解释列出目标单元格和可变单元格以及它们的数值、上下限和目标值。含有整数约束条件的模型不能生成本报告。其中，下限是在满足约束条件和保持其它可变单元格数值不变的情况下，某个可变单元格可以取到的最小值。上

16、限是在这种情况下可以取到的最大值。 第29页/共43页延伸下面对目标系数同时变动以及约束右端值同时变动的情况分别作延伸。（1）目标系数c同时变动的百分之百法则： 如果目标函数系数同时变动，计算出每一系数变动量占该系数同方向可容许变动范围的百分比，而后将各个系数的变动百分比相加，如果所得的和不超过百分之一百，最优解不会改变；如果超过百分之一百，则不能确定最优解是否改变。如果x1对应的目标系数c1由2变为1.8，x2对应的目标系数c2由3变为3.5，运用百分之百法则，根据敏感性分析报告c1由2变为1占允许减量的百分比为 (2-1.8)/0.5=40%C2由3变为3.5占允许增加量的百分比为 (3.

17、5-3)/1=50%. 改变的百分比和为90%，没有超过100%，因此最优解不变。第30页/共43页（2）约束右端值b同时变动的百分之百法则： 同时改变几个或所有函数约束的约束右端值，如果这些变动的幅度不大，那么可以用影子价格预测变动产生的影响。为了判别这些变动的幅度是否允许，计算每一变动占同方向可容许变动范围的百分比，如果所有的百分比之和不超过百分之一百，那么影子价格还是有效的；如果所有的百分比之和超过百分之一百，那就无法确定影子价格是否有效。如果右端值b分别由8, 16, 12变为8.5,15，和11，运用百分之百法则:b1由8变为8.5占允许增量的百分比为 (8.5-8)/2=25%b2

18、由16变为15占允许减量的百分比为 (16-15)/8=12.5%b3由12变为11占允许减量的百分比为 (12-11)/4=25%. 改变的百分比和为62.5%，没有超过100%，因此影子价格仍然有效。第31页/共43页单元格变量名最终值减少成本目标系数允许增加值允许减少值$B$9x102.862.81E+30$C$9x212.4097.83.4$D$9x39.50122.61.5已知一个线性规划问题的灵敏度分析报告如下已知一个线性规划问题的灵敏度分析报告如下变动单元格变动单元格单元格名 称最终值影子价格右端值允许增加值允许减少值$E$4第一资源约束580701E+3012$E$5第二资源约

19、束372.8371531$E$6第三资源约束601.2601250约束条件2012年12月管理创新实验班期末考试题（1）写出该问题的最优解（2）分析当x1的目标系数减少5，同时x2的目标系数增加4时，最优解是否改变。（3）分析当第一资源约束右端值增加30，同时第二资源约束右端值增加4和第三资源约束右端值减少15时，目标函数值的改变量。第32页/共43页解：（1）最优解为x1=0, x2=12.4, x3=9.5（2） x1的目标系数减少5，占允许减少的百分比=5/=0%，x2的目标系数增加4,占允许增加的百分比=4/7.8=51.2%。改变的百分比和为51.2%，没有超过100%，因此最优解不变。（3）第一资源约束右端值增加30，占允许增加的30 /=0%，第二资源约束右端值增加4 ，占允许增加的4/15=26.7%，第三资源约束右端值减少15，占允许减少的15/50=30%。改变的百分比和为56.7%，没有超过100%，因此影子价格仍然有效。因此目标函数值改变量为 30 30 0 + 4 0 + 4 2.8 15 2.8 15 1.2=11.2 1.2=11.2 18= 18= 6.86.8第33页/共43页练习：话务员排班问题 某寻呼公