EXCEL求解线性规划问题PPT课件_第1页
EXCEL求解线性规划问题PPT课件_第2页
EXCEL求解线性规划问题PPT课件_第3页
EXCEL求解线性规划问题PPT课件_第4页
EXCEL求解线性规划问题PPT课件_第5页
已阅读5页,还剩39页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、2021/3/91第五章第五章 利用利用EXCEL求解线性规划问题求解线性规划问题 建立线性规划问题的模型建立线性规划问题的模型利用利用EXCEL求解线性规划问题求解线性规划问题分析运算结果(敏感性分析)分析运算结果(敏感性分析)目的目的:2021/3/92 一、一、EXCEL 基本知识基本知识 1、命名工作表、命名工作表 功能:功能: 存储信息、进行计算、排序数据、用图或表的形存储信息、进行计算、排序数据、用图或表的形式显示数据、规划求解、财会分析、概率与统计分析等式显示数据、规划求解、财会分析、概率与统计分析等等等(1)激活工作表)激活工作表1,单击,单击sheet 1 标签标签(2)命名

2、工作表)命名工作表 或或 (3)Format / sheet Rename 显示显示Rename sheet 对话框对话框(4)键入工作表名)键入工作表名2021/3/93 2、工作表、工作表 4、进行计算、进行计算 Excel-2003 256列列 65535 行行Excel-2007 16384列列 1048576行行 3、向单元格中输入数据、向单元格中输入数据常数:不能改变的文字或其它数据常数:不能改变的文字或其它数据公式:输入到单元格中的数字值,或返回数字值的表达式。公式:输入到单元格中的数字值,或返回数字值的表达式。(1)输出数据时计算)输出数据时计算A1 =456+789(2)根据

3、其它单元格的数据进行计算)根据其它单元格的数据进行计算A3456 B3-789 C3=A3+B32021/3/94 5、在公式中引用其它单元格、在公式中引用其它单元格 在公式中可以引用本工作簿中任何单元格或单元格在公式中可以引用本工作簿中任何单元格或单元格组的数组的数 据,也可以引用其它工作簿中任何单元格或据,也可以引用其它工作簿中任何单元格或单元格组的数据。引用单元格数据后,公式的运算单元格组的数据。引用单元格数据后,公式的运算值将随着被引用的单元格数据变化而变化。值将随着被引用的单元格数据变化而变化。(1)引用的类型)引用的类型三种类型三种类型 :相对引用、相对引用、 绝对引用、混合引用绝

4、对引用、混合引用2021/3/95格式:格式: A3 、B6(2) 相对引用相对引用 使用相对引用后,系统将会记住建立公式的单元格和被使用相对引用后,系统将会记住建立公式的单元格和被引用的单元格的相对位置,在复制这个公式时,新的公式单引用的单元格的相对位置,在复制这个公式时,新的公式单元和被引用的单元仍然保持这种相对位置。元和被引用的单元仍然保持这种相对位置。2021/3/96(3)绝对引用)绝对引用 绝对引用是指被引用的单元与引用的公式单元的位置绝对引用是指被引用的单元与引用的公式单元的位置关系是绝对的,无论将这个公式复制到任何单元,公式所关系是绝对的,无论将这个公式复制到任何单元,公式所引

5、用的还是原来单元格的数据。引用的还是原来单元格的数据。(4)混合引用)混合引用 格式:格式:$a$3 $d$5格式:格式: $A3 列是绝对的,行是相对的列是绝对的,行是相对的 B$ 3 列是相对的,行是绝对的列是相对的,行是绝对的2021/3/97使用使用Excel进行求解进行求解1.关于关于“规划求解规划求解”2.如何加载如何加载“规划求解规划求解”3. “规划求解规划求解”各参数设置各参数设置4. “规划求解规划求解”步骤步骤5. 利用利用“规划求解规划求解”解线性规划问题解线性规划问题2021/3/982.如何加载如何加载“规划求解规划求解”1) 在“工具”菜单上,单击“加载宏”202

6、1/3/992) 在弹出的对话框中的“可用加载宏”列表框中,选定待添加的加载宏“规划求解”选项旁的复选框,然后单击“确定”.单击“确定”后,“工具”菜单下就会出现一项“规划求解”2021/3/9103. “规划求解规划求解”各参数设置各参数设置单击“规划求解”按钮,将会出现以下规划求解参数设置对话框2021/3/911 单击“添加”,显示添加约束对话框2021/3/9122021/3/913 选项:显示”规划求解选项”对话框.在其中可以加载或保存规划求解模型,并对规划求解过程的高级属性进行控制2021/3/914线性规划求解步骤:线性规划求解步骤:1. 确定目标函数系数存放单元格,并在这些单元

7、格中输入目标确定目标函数系数存放单元格,并在这些单元格中输入目标函数系数。函数系数。2. 确定决策变量存放单元格,并任意输入一组数据;确定决策变量存放单元格,并任意输入一组数据;3. 确定约束条件中左端项系数存放单元格,并输入约束条件左确定约束条件中左端项系数存放单元格,并输入约束条件左端项系数;端项系数;4. 在约束条件左端项系数存放单元格右边的单元格中输入约束在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式,计算出约束条件左端项对应于目前决条件左端项的计算公式,计算出约束条件左端项对应于目前决策变量的函数值。策变量的函数值。5. 在步骤在步骤4的的数据右边输入约束条

8、件中右端项(即常数项)的的数据右边输入约束条件中右端项(即常数项)6. 确定目标函数值存放单元格,并在该单元格中输入目标函数确定目标函数值存放单元格,并在该单元格中输入目标函数值电容计算公式。值电容计算公式。2021/3/915一个简单的例子一个简单的例子 某工厂计划生产两种产品,利润分别为某工厂计划生产两种产品,利润分别为2和和3,已知生,已知生产单位产品所需的设备台时和产单位产品所需的设备台时和A、B两种原材料的消耗,两种原材料的消耗,如表如表产品产品1产品产品2设备设备128台时台时原材料原材料A4016KG原材料原材料B0412KG 目标是不超过资源限制的情况下,确定两产品产量,目标是

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

10、数据。2021/3/917建立数学公式(步骤二)建立数学公式(步骤二) 在工作表的顶部输入数据在工作表的顶部输入数据 确定每个决策变量所对应确定每个决策变量所对应的单元格位置的单元格位置 选择单元格输入公式,找选择单元格输入公式,找到目标函数的值到目标函数的值 选择一个单元格输入公式,选择一个单元格输入公式,计算每个约束条件左边的计算每个约束条件左边的值值 选择一个单元格输入公式,选择一个单元格输入公式,计算每个约束条件右边的计算每个约束条件右边的值值图中,规定图中,规定B12、C12为可变单元格为可变单元格可变单元格存放决策变量的取值,可变单元格数目等于决策变量个数可变单元格存放决策变量的取

11、值,可变单元格数目等于决策变量个数2021/3/918建立数学公式(步骤三)建立数学公式(步骤三) 在工作表的顶部输入数据在工作表的顶部输入数据 确定每个决策变量所对应确定每个决策变量所对应的单元格位置的单元格位置 选择单元格输入公式,找选择单元格输入公式,找到目标函数的值到目标函数的值 确定约束单元格输入公式,确定约束单元格输入公式,计算每个约束条件左边的计算每个约束条件左边的值值 确定约束单元格输入公式,确定约束单元格输入公式,计算每个约束条件右边的计算每个约束条件右边的值值在目标单元格中,需要填入计算目标函数值的公式。在目标单元格中,需要填入计算目标函数值的公式。2021/3/919建立

12、数学公式(步骤四)建立数学公式(步骤四) 在工作表的顶部输入数据在工作表的顶部输入数据 确定每个决策变量所对应确定每个决策变量所对应的单元格位置的单元格位置 选择单元格输入公式,找选择单元格输入公式,找到目标函数的值到目标函数的值 确定约束单元格输入公式,确定约束单元格输入公式,计算每个约束条件左边的计算每个约束条件左边的值值 确定约束单元格输入公式,确定约束单元格输入公式,计算每个约束条件右边的计算每个约束条件右边的值值在约束单元格中,需要填入计算约束函数值的公式。在约束单元格中,需要填入计算约束函数值的公式。2021/3/920建立数学公式(步骤五) 在工作表的顶部输入数据在工作表的顶部输

13、入数据 确定每个决策变量所对应确定每个决策变量所对应的单元格位置的单元格位置 选择单元格输入公式,找选择单元格输入公式,找到目标函数的值到目标函数的值 确定约束单元格输入公式,确定约束单元格输入公式,计算每个约束条件左边的计算每个约束条件左边的值值 确定约束单元格输入公式,确定约束单元格输入公式,计算每个约束条件右边的计算每个约束条件右边的值值2021/3/921调用调用 规划求解规划求解 模块模块 选择选择工具工具下拉菜单下拉菜单 选择选择规划求解规划求解选项(事先选项(事先需用需用Office安装盘安装规安装盘安装规划求解的功能)划求解的功能)2021/3/922填写目标单元格和可变单元格

14、填写目标单元格和可变单元格 出现出现规划求解参数规划求解参数对话框对话框 在在目标单元格目标单元格中输入中输入B14B14 在在等于等于选择最大选择最大 在在可变单元格可变单元格中输入中输入B12:C12B12:C12 选择选择添加添加在上图显示的界面中,需要输入目标单元格、可变单元格,添加约束条件,另外还可能需要进行选项设置。2021/3/923添加约束添加约束 在在添加约束添加约束对话框中,在对话框中,在单元格引用位置单元格引用位置中输入中输入B17,选择,选择=,在约束值,在约束值中输入中输入D17。选择。选择添加添加 第三个条件添加完毕后,第三个条件添加完毕后,选择选择确定确定 当当规

15、划求解参数规划求解参数对话框重对话框重新出现时,选择新出现时,选择选项选项2021/3/924“选项选项”设置设置 当选项对话框出现时,选当选项对话框出现时,选择择假设非负假设非负。选择。选择确定确定2021/3/925用Excel求解 出现出现规划求解参数规划求解参数对话框,对话框,选择选择求解求解。2021/3/926保存求解结果保存求解结果当当求解结果求解结果对话框出现时,选对话框出现时,选择择保存规划求解结果保存规划求解结果。选择。选择确确定定。2021/3/927运算结果报告运算结果报告列出列出目标单元格目标单元格和和可变单可变单元格元格以及它们的初始值、以及它们的初始值、最终结果、

16、约束条件和有最终结果、约束条件和有关约束条件的信息。关约束条件的信息。初值初值和和终值终值分别指单元格分别指单元格在在本次求解前的数值和求本次求解前的数值和求解后的数值解后的数值。2021/3/928敏感性分析报告(敏感性分析报告(1)可变单元格中可变单元格中“单元格单元格”指决策变量所在单元格的地址指决策变量所在单元格的地址“名字名字”是决策变量的名称是决策变量的名称“终值终值”是决策变量的终值是决策变量的终值,即最优值即最优值“递减成本递减成本”指指最优解中等于0的变量,对应的目标函数中的系数增加或减少多少,最优解不再为0“目标式系数目标式系数”目标函数中的系数,为已知条件目标函数中的系数

17、,为已知条件“允许的增量允许的增量”与与“允许的减量允许的减量”表示目标函数中的系数在增量表示目标函数中的系数在增量与减量的变化范围内变化时,最优解保持不变(注:最优值发生与减量的变化范围内变化时,最优解保持不变(注:最优值发生变化)变化)2021/3/929敏感性分析报告(敏感性分析报告(2)约束单元格约束单元格“单元格单元格”指约束条件左边所在单元格的地址指约束条件左边所在单元格的地址“名字名字”是约束条件左边的名称是约束条件左边的名称“终值终值”是约束条件左边的值是约束条件左边的值“阴影价格阴影价格”指约束条件右边增加或减少一个单位,目标函指约束条件右边增加或减少一个单位,目标函数值增加

18、或减少的数量数值增加或减少的数量“约束的限制值约束的限制值”指约束条件右边的值,为已知条件指约束条件右边的值,为已知条件“允许的增量允许的增量,减量减量”表示约束条件右边在允许的范围内变表示约束条件右边在允许的范围内变化时,影子价格不变,即约束条件右边的值每增加化时,影子价格不变,即约束条件右边的值每增加1个单位,个单位,目标函数值的增加仍然为影子价格的大小。目标函数值的增加仍然为影子价格的大小。因此,右端项在因此,右端项在一定范围内变化时,影子价格不变,目标函数值的变动等于一定范围内变化时,影子价格不变,目标函数值的变动等于右端项变动值乘以影子价格右端项变动值乘以影子价格2021/3/930

19、极限值报告解释极限值报告解释列出列出目标单元格目标单元格和和可变单元格可变单元格以及它们的数值、上下限和目标以及它们的数值、上下限和目标值。含有整数约束条件的模型不能生成本报告。其中,下限是值。含有整数约束条件的模型不能生成本报告。其中,下限是在满足约束条件和保持其它可变单元格数值不变的情况下,某在满足约束条件和保持其它可变单元格数值不变的情况下,某个可变单元格可以取到的最小值。上限是在这种情况下可以取个可变单元格可以取到的最小值。上限是在这种情况下可以取到的最大值。到的最大值。 2021/3/931延伸延伸下面对目标系数同时变动以及约束右端值同时变动的情况分别作延伸。下面对目标系数同时变动以

20、及约束右端值同时变动的情况分别作延伸。(1)目标系数目标系数c同时变动的百分之百法则同时变动的百分之百法则: 如果目标函数系数同时变动,计算出每一系数变动量占该系数同方向如果目标函数系数同时变动,计算出每一系数变动量占该系数同方向可容许变动范围的百分比,而后将各个系数的变动百分比相加,可容许变动范围的百分比,而后将各个系数的变动百分比相加,如果如果所得的和不超过百分之一百,最优解不会改变所得的和不超过百分之一百,最优解不会改变;如果超过百分之一百,;如果超过百分之一百,则不能确定最优解是否改变。则不能确定最优解是否改变。如果如果x1对应的目标系数对应的目标系数c1由由2变为变为1.8,x2对应

21、的目标系数对应的目标系数c2由由3变为变为3.5,运,运用百分之百法则,根据敏感性分析报告用百分之百法则,根据敏感性分析报告c1由由2变为变为1占允许减量的百分比为占允许减量的百分比为 (2-1.8)/0.5=40%C2由由3变为变为3.5占允许增加量的百分比为占允许增加量的百分比为 (3.5-3)/1=50%. 改变的百分比和为改变的百分比和为90%,没有超过,没有超过100%,因此最优解不变。,因此最优解不变。2021/3/932(2)约束右端值)约束右端值b同时变动的百分之百法则:同时变动的百分之百法则: 同时改变几个或所有函数约束的约束右端值,如果这些变动的幅同时改变几个或所有函数约束

22、的约束右端值,如果这些变动的幅度不大,那么可以用影子价格预测变动产生的影响。为了判别这些度不大,那么可以用影子价格预测变动产生的影响。为了判别这些变动的幅度是否允许,计算每一变动占同方向可容许变动范围的百变动的幅度是否允许,计算每一变动占同方向可容许变动范围的百分比,分比,如果所有的百分比之和不超过百分之一百,那么影子价格还如果所有的百分比之和不超过百分之一百,那么影子价格还是有效的是有效的;如果所有的百分比之和超过百分之一百,那就无法确定;如果所有的百分比之和超过百分之一百,那就无法确定影子价格是否有效。影子价格是否有效。如果右端值如果右端值b分别由分别由8, 16, 12变为变为8.5,1

23、5,和,和11,运用百分之百法则,运用百分之百法则:b1由由8变为变为8.5占允许增量的百分比为占允许增量的百分比为 (8.5-8)/2=25%b2由由16变为变为15占允许减量的百分比为占允许减量的百分比为 (16-15)/8=12.5%b3由由12变为变为11占允许减量的百分比为占允许减量的百分比为 (12-11)/4=25%. 改变的百分比和为改变的百分比和为62.5%,没有超过,没有超过100%,因此影子价格仍然有效。,因此影子价格仍然有效。2021/3/933单元格变量名最终值减少成本目标系数允许增加值允许减少值$B$9x102.862.81E+30$C$9x212.4097.83.

24、4$D$9x39.50122.61.5已知一个线性规划问题的灵敏度分析报告如下已知一个线性规划问题的灵敏度分析报告如下变动单元格变动单元格单元格名 称最终值影子价格右端值允许增加值允许减少值$E$4第一资源约束580701E+3012$E$5第二资源约束372.8371531$E$6第三资源约束601.2601250约束条件约束条件2012年年12月管理创新实验班期末考试题月管理创新实验班期末考试题(1)写出该问题的最优解)写出该问题的最优解(2)分析当)分析当x1的目标系数减少的目标系数减少5,同时,同时x2的目标系数增加的目标系数增加4时,最优解是否改变。时,最优解是否改变。(3)分析当第

25、一资源约束右端值增加)分析当第一资源约束右端值增加30,同时第二资源约束右端值增加,同时第二资源约束右端值增加4和第三资源和第三资源约束右端值减少约束右端值减少15时,目标函数值的改变量。时,目标函数值的改变量。2021/3/934解解:(:(1)最优解为最优解为x1=0, x2=12.4, x3=9.5(2) x1的目标系数减少的目标系数减少5,占允许减少的百分比,占允许减少的百分比=5/=0%,x2的目标系数增加的目标系数增加4,占允许增加的百分比占允许增加的百分比=4/7.8=51.2%。改变的百分比和为改变的百分比和为51.2%,没有超过,没有超过100%,因此最优解不变。,因此最优解

26、不变。(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.82021/3/935练习:话务员排班问题练习:话务员排班问题 某寻呼公司雇用了多名话务员工作,他们某寻呼公司雇用了多名话务员工作,他们每天工作每天工作3节,每节节,每节3小时,每节开始时间小时,每节开始时间为午夜、凌晨为午夜、凌晨3点钟、凌晨点钟、凌晨6点钟,上午点钟,上午9点、点、中午中午12点、下午点、下午3点、点、6点、点、9点,为方便话点,为方便话务员上下班,管理层安排每位话务员每天务员上下班,管理层安排每位话务员每天连续工作连续工作3节,根据调查,对于不同的时间,节,根据调查,对于不同的时间,由于业务量不同,需要的话务员的人数也由于业务

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论