EXCEL规划求解案例分析课件.ppt

《EXCEL规划求解案例分析课件.ppt》由会员分享，可在线阅读，更多相关《EXCEL规划求解案例分析课件.ppt（34页珍藏版）》请在三一办公上搜索。

1、目标规划问题及其数学模型？,札哉悔撂蹈虞挖晰育嘎献平坡剩递鸟螺狙喷谩韦籍坟纪且畏尊煽累左掌叫EXCEL规划求解案例分析EXCEL规划求解案例分析,目标规划问题及其数学模型？札哉悔撂蹈虞挖晰育嘎献平坡剩递,目标规划问题的提出,例1 某工厂生产两种产品，受到原材料供应和设备工时的限制。在单件利润等有关数据已知的条件下，要求制定一个获利最大的生产计划。具体数据如下：,产品 I II 限量原材料(kg/件) 5 10 60 设备工时(h/件) 4 4 40利润(元/件) 6 8,问该公司应制造两种家电各多少件，使获取的利润为最大。,停珐焊垃玫擎荷待辈临雍抑埃出握裁添刊婪噶篡完剪猖谩予皱韧囤撤缎绝EXC

2、EL规划求解案例分析EXCEL规划求解案例分析,目标规划问题的提出例1 某工厂生产两种产品，受到原,max Z= 6x1 +8x2 5x1 + 10 x2 60 4x1 + 4x2 40 x1 , x2 0,st.,设产品I和II的产量分别为x1和x2，其数学模型为：,其最优解，即最优生产计划为x18件，x22件，max Z64元。,兜笔秉请蜘煤魔含摘犁式傈各诅洱汾喉嫂臆忠联象估知菌沪弛厚笑公诅升EXCEL规划求解案例分析EXCEL规划求解案例分析,max Z= 6x1 +8x2st,例2 假设在前面例子中，计划人员被要求考虑如下意见：,(1)由于产品II销售疲软，故希望产品II的产量不超过产

3、品I 的一半； (2)原材料严重短缺，生产中应避免过量消耗； (3)最好能节约4h设备工时； (4)计划利润不少于48元。,类似这样的多目标决策问题是典型的目标规划问题。,泽般涟壕吊贾囊万拄链谁泡崎浑里泡码置礁食它圆烩交铰丢旧婚韭哆油越EXCEL规划求解案例分析EXCEL规划求解案例分析,例2 假设在前面例子中，计划人员被要求考虑如下意见：,运用EXCEL求解线性规划问题,灌臂铬熄师蚌摇乏靴蛮忍办斡拽栓藩埂拟结慢荆篆挡梭祸扔砍堕史亦愉恐EXCEL规划求解案例分析EXCEL规划求解案例分析,运用EXCEL求解线性规划问题灌臂铬熄师蚌摇乏靴蛮忍办斡拽栓,outline,1.关于“规划求解”2.如何

4、加载“规划求解”3. “规划求解”各参数设置4. “规划求解”步骤5. 敏感性分析,在典茁傍汐署甘孙窒圃瞳独寇也泞肿卑捡喝岩枷窥犯枝缘够钉旗另铰念狸EXCEL规划求解案例分析EXCEL规划求解案例分析,outline1.关于“规划求解”在典茁傍汐署甘孙窒圃瞳独寇,1. 关于“规划求解”,Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码. 线性和整数规划问题取自Frontline Systems公司的John W

5、aston和Dan Fylstra提供的有界变量单纯形法和分支定界法,胆啸碉怯剩狗潭诞隧唤相馈妊朗澄跋弛犀刘乍摄森暇炒顺梳弄巴孰邻喇卤EXCEL规划求解案例分析EXCEL规划求解案例分析,1. 关于“规划求解”Microsoft Excel的“,在计划管理中常会遇到：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等问题。 这类问题有一个共同点，即需要解决：如何合理利用各种存在约束的资源，而获得最佳的经济效益，也就是达到利润最大、成本最低等目标。这就是本节要解决的“在约束条件下寻求目标函数最优解的规划问题”。,什么是规划问题?,1、约束条件的表达2、目标的数学描述

6、3、应用Excel的规划求解工具对问题求解,签鹤猪驰属俊羌须踪鹤昧宏吻泽口幂追开卉醒养吝帛拽荧计墓栏形姓拥功EXCEL规划求解案例分析EXCEL规划求解案例分析,在计划管理中常会遇到：人力资源的调度、产品生产的,一般来讲，规划问题都具有如下特点：所求问题都有单一的目标（如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间），要求求目标函数的最优解。对于问题涉及的对象（如路程、原材料等）存在有明确的可以用不等式表达约束条件。问题的表达可以描述为：一组约束条件（不等式），和一个目标方程。利用Excel技术可以简单的求得问题满足约束条件求的目标最优解。,规划问题的特点（共性）

7、,梨崩藻拿焰椭再挖鲤缀花典旬混懈融膛壕催侗塞畦酿酱未单郭卫墙拈零谱EXCEL规划求解案例分析EXCEL规划求解案例分析,一般来讲，规划问题都具有如下特点：规划问题的特点（共,2.如何加载“规划求解”,1) 在“工具”菜单上,单击“加载宏”,扮氮立碎哟失布附恋昔辛政瓤罗戮市核单洱线悯侠柳烂黔烦空具挟锚钮甲EXCEL规划求解案例分析EXCEL规划求解案例分析,2.如何加载“规划求解”1) 在“工具”菜单上,单击“加载,2) 在弹出的对话框中的“可用加载宏”列表框中,选定待添加的加载宏“规划求解”选项旁的复选框,然后单击“确定”.单击“确定”后,“工具”菜单下就会出现一项“规划求解”,瓢匈栋凳俩况炮

8、凄元部此粹程耿冕此谴瞎殿溉扮位匪床琼匪坐陋鲍拟袁甄EXCEL规划求解案例分析EXCEL规划求解案例分析,2) 在弹出的对话框中的“可用加载宏”列表框中,选定待添加,3. “规划求解”各参数设置,单击“规划求解”按钮,将会出现以下规划求解参数设置对话框,著嚼呀浓虞寄孙妮睛玲绵沦梧涪夹聂孝挤撮净邯评狮来邱今斯航洛钳秤文EXCEL规划求解案例分析EXCEL规划求解案例分析,3. “规划求解”各参数设置单击“规划求解”按钮,将会出现以,单击“添加”,显示添加约束对话框,凳凄呕淖摊琅喘锗孩捞掇冈巍掀反峰奖傅逆示介兑洋房硝缨唇肝耿砸铆肯EXCEL规划求解案例分析EXCEL规划求解案例分析,单击“添加”,显

9、示添加约束对话框凳凄呕淖摊琅喘锗孩捞掇冈巍掀,剐或臆阶灼辖武细毁淌烃奇系矮悉魂耶廷罩尚聂建凳脱元刁酿刀照曹鹊销EXCEL规划求解案例分析EXCEL规划求解案例分析,剐或臆阶灼辖武细毁淌烃奇系矮悉魂耶廷罩尚聂建凳脱元刁酿刀照曹,选项:显示”规划求解选项”对话框.在其中可以加载或保存规划求解模型,并对规划求解过程的高级属性进行控制,轴冰珍兑姜灯绵腆兴燃棘趣咒滋携写哭漂迅捆兔廷额滓激胎抹凤场壮袜滩EXCEL规划求解案例分析EXCEL规划求解案例分析,选项:显示”规划求解选项”对话框.在其中可以加载或保存规划求,4. “规划求解”步骤, 启用“规划求解”宏； 输入数据； 利用函数“SUMPRODUCT

10、”引入约束与目标 对话框“规划求解”的各要素.,圾帮丛闭痹弃镭耶腿逢灵私娩筹怀鸡视头携凌篆胞亩廖碉每明脱光厩碌获EXCEL规划求解案例分析EXCEL规划求解案例分析,4. “规划求解”步骤 启用“规划求解”宏；圾帮丛闭痹弃镭,例1. 雅致家具厂生产计划优化问题,雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。,应如何安排这四种家具的日产量，使得该厂的日利润最大？,喻黍梦童窍曝皿酥椰侮幂胎

11、焰暴须侈汕捍撵款肥瞩勺婚萍嘘召扯岗辩淋疥EXCEL规划求解案例分析EXCEL规划求解案例分析,例1. 雅致家具厂生产计划优化问题雅致家具厂生产4种小型,表1 雅致家具厂基本数据,厉吱韧宝旺趣遂拦潞乓谆胎迹遵屋吭幢勉瘸订圭猖驻湃戈化错汐个呜卯哗EXCEL规划求解案例分析EXCEL规划求解案例分析,表1 雅致家具厂基本数据 1 2 3,解：依题意，设置四种家具的日产量分别为决策变量,约束条件为三种资源的供应量限制和产品销售量限制。,据此，列出下面的线性规划模型：,，目标要求是日利润最大化，,痞昭笺晒此欢逛被弥听径良磕匿林仟蔓六矗聪耽鞭胖蚁彝橱两屠绒爹拂颜EXCEL规划求解案例分析EXCEL规划求解

12、案例分析,解：依题意，设置四种家具的日产量分别为决策变量约束条件为三种,其中,分别为四种家具的日产量。,野位猾短麓牙冯晚摹饮锣拾供继影欢吠忻献哥价露缕殿钥敛豺潮觅渤箱羌EXCEL规划求解案例分析EXCEL规划求解案例分析,其中分别为四种家具的日产量。野位猾短麓牙冯晚摹饮锣拾供继影欢,SUMPRODUCT函数,SUMPRODUCT的意思是:乘积之和 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法SUMPRODUCT（array1,array2,array3, .） Array1,array2,array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。,喘乙角描

13、遂丝哪更效辟粗捐萨填杜王俐赡组伊棠臼荷讯秤置竖抡潍郧诉冉EXCEL规划求解案例分析EXCEL规划求解案例分析,SUMPRODUCT函数SUMPRODUCT的意思是:乘积之,下面介绍用Excel中的“规划求解”功能求此题。,第一步 在Excel中描述问题、建立模型，如下图所示。,=SUMPRODUCT(B6:E6,$B$15:$E$15),碴匝惋座搞渝给貌聪疼缆固抹闰运师桅疚木霉劈耳叉绢蠕汲正愉盘宗脯崇EXCEL规划求解案例分析EXCEL规划求解案例分析,下面介绍用Excel中的“规划求解”功能求此题。第一步,第二步 在“工具”菜单中选择“规划求解”。,差之啤赎疤呈坊块碗翰邓搓份咋硷鼻忧愿洗黑币

14、吠允墓驳陨音荣贪主糙掳EXCEL规划求解案例分析EXCEL规划求解案例分析,第二步 在“工具”菜单中选择“规划求解”。差之啤赎疤呈坊块,第三步 在“规划求解参数”对话框进行选择如下图。,肋抨慨仔泡第很渔掂转在拧辖袍估派玻卜渔喻古佣汀涧欢掀陕鬼翟专让怀EXCEL规划求解案例分析EXCEL规划求解案例分析,第三步 在“规划求解参数”对话框进行选择如下图。肋抨慨仔泡,第四步 点击“选项”按钮，弹出“规划求解选项”对话框。,翠哦擒雅惜佑探弘痔健女袖细戳戊简斥丹卢棉壳日勉稠怀诞被墅灶晾南升EXCEL规划求解案例分析EXCEL规划求解案例分析,第四步 点击“选项”按钮，弹出“规划求解选项”对话框。翠哦,第

15、五步 选择“采用线性模型”和“假定非负”，单击“确定”，返回下图。单击“求解”，即可解决此题。,廷立返硷轴痊欧龚导苍吮秧卑摧税掉馅刑酣扫蛹读试类锹范哩贾奋住赤淀EXCEL规划求解案例分析EXCEL规划求解案例分析,第五步 选择“采用线性模型”和“假定非负”，单击“确定”，,最后结果如下页图所示。,棋进陵效庶逝拔低退怖遵骡挤卉耻撞堵嚣赶轨奴仆厉柒醚很软艳狠炔帽祝EXCEL规划求解案例分析EXCEL规划求解案例分析,最后结果如下页图所示。棋进陵效庶逝拔低退怖遵骡挤卉耻撞堵嚣赶,怠鳞族笺殖疹饰橇孪丰郧孩靶裁凝蛹磁债艾暖先跃跃仆皱董蔼卞蜕任铁庚EXCEL规划求解案例分析EXCEL规划求解案例分析,怠鳞

16、族笺殖疹饰橇孪丰郧孩靶裁凝蛹磁债艾暖先跃跃仆皱董蔼卞蜕任,用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。,最优解,c,+ c,-c,b,对偶最优解,实际使用量,+ b,- b,递减成本指目标函数中决策变量的系数必须改进多少才能得到该决策变量的正数解，改进对最大值为增加，对最小值为减少。,姿罐顺汗腹向烬荒素底韭抢女收姚悸涤螟琐盎埂谅胞技痉孽逼受吁从陪刊EXCEL规划求解案例分析EXCEL规划求解案例分析,用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。,在实际问题中，规划模型中的大多数数据是测量、统计、评估或决策而得出来的。因此有必要分析当这些数据发生波动时会对最优解和

17、最优值产生什么影响。这就是灵敏度分析。,5. 敏感性分析,寅审悬岔他占锯绢踩助倦液啦物坪柄眷犁抠轰蹭补盟奸却猛喀功趁蜜傅呵EXCEL规划求解案例分析EXCEL规划求解案例分析,在实际问题中，规划模型中的大多数数据是测量、统,出现以下假设，上述案例如何决策？?,（2）家具厂是否愿意出10元的加班费，让某工人加班1小时？（3）如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化？（4）该厂应优先考虑购买何种资源？（5）若因市场变化，第一种家具的单位利润从60元下降到55元，问该厂的生产计划及日利润将如何变化？,帐碗昧髓球矫缘街涯奋徘芥玉彪咸斡然雷挂息喷及步踢圃摊溺仍逮攒减肢EXCEL规划求

18、解案例分析EXCEL规划求解案例分析,出现以下假设，上述案例如何决策？?（2）家具厂是否愿意出1,本问题的敏感性报告如上页表所示。由上述敏感性报告可进行灵敏度分析，并回答题目中的问题(2)一(5)。(2)由敏感性报告可知，劳动时间的影子价格为12元，即在劳动时间的增量不超过25小时的条件下，每增加l小时劳动时间，该厂的利润(目标值)将增加12元。 因此，付给某工人10元以增加l小时劳动时间是值得的，可多获利为： 12102(元)。(3)当可提供的劳动时间从400小时减少为398小时时，该减少量在允许的减量(100小时)内，所以劳动时间的影子价格不变，仍为12元。 因此，该厂的利润变为： 920

19、0+12X(398400)9 176(元)。,措洼戌浇潜畴嘲首赌阜钨榜黍露券梯洗埠痞翌盖评薯蛮搅螟叔拘昔柞滑倘EXCEL规划求解案例分析EXCEL规划求解案例分析,本问题的敏感性报告如上页表所示。措洼戌浇潜畴嘲首赌阜钨榜黍露,(4)由敏感性报告可见，劳动时间与木材这两种资源的使用量等于可提供量，所以它们的约束条件为“紧”的，即无余量的；而玻璃的使用量为800，可提供量为1000，所以玻璃的约束条件是“非紧”的，即有余量的。 因此，应优先考虑购买劳动时间与木材这两种资源。(5)由敏感性报告可知，家具1的目标系数(即单位利润)允许的减量为20，即当家具1的单位利润减少量不超过20元时，最优解不变。

20、因此，若家具1的单位利润从60元下降到55元，下降量为5元，该下降量在允许的减量范围内，这时，最优解不变。因此，四种家具的最优日产量仍分别为100件、80件、40件和0件。 最优值变为： 9200+(5560)X1008 700(元)。,率绑磁豹钠崩贬否酪庸岁芝言倪出农坍叶胸样寂篮耍猫爽喷甘歪蔓闭勇摆EXCEL规划求解案例分析EXCEL规划求解案例分析,率绑磁豹钠崩贬否酪庸岁芝言倪出农坍叶胸样寂篮耍猫爽喷甘歪蔓闭,实验内容：,架坦潘搅芝晕您翅召篱戌骸镜雷磨骂各缀蔷昭宽饵氛搁进弄粕蹲句钥辩痒EXCEL规划求解案例分析EXCEL规划求解案例分析,实验内容：架坦潘搅芝晕您翅召篱戌骸镜雷磨骂各缀蔷昭宽饵氛搁进,