EXCEL规划求解案例分析.ppt

《EXCEL规划求解案例分析.ppt》由会员分享，可在线阅读，更多相关《EXCEL规划求解案例分析.ppt（34页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、目标规划问题及其数学模型？目标规划问题及其数学模型？目标规划问题的提出目标规划问题的提出例1 某工厂生产两种产品，受到原材料供应和设备工时的限制。在单件利润等有关数据已知的条件下，要求制定一个获利最大的生产计划。具体数据如下：产品 I II 限量原材料(kg/件)5 10 60 设备工时(h/件)4 4 40利润(元/件)6 8问该公司应制造两种家电各多少件，使获取的利润为最大。max Z=6x1+8x2 5x1+10 x2 60 4x1+4x2 40 x1,x2 0设产品I和II的产量分别为x1和x2，其数学模型为：其最优解，即最优生产计划为x18件，x22件，max Z64元。例2 假设在

2、前面例子中，计划人员被要求考虑如下意见：(1)由于产品II销售疲软，故希望产品II的产量不超过产品I 的一半；(2)原材料严重短缺，生产中应避免过量消耗；(3)最好能节约4h设备工时；(4)计划利润不少于48元。类似这样的多目标决策问题是典型的目标规划问题。运用运用EXCEL求解线性规划问题求解线性规划问题outline1.关于关于“规划求解规划求解”2.如何加载如何加载“规划求解规划求解”3.“规划求解规划求解”各参数设置各参数设置4.“规划求解规划求解”步骤步骤5.敏感性分析敏感性分析1.关于关于“规划求解规划求解”Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校

3、的Leon Lasdon和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码.线性和整数规划问题取自Frontline Systems公司的John Waston和Dan Fylstra提供的有界变量单纯形法和分支定界法 在计划管理中常会遇到：人力资源的调在计划管理中常会遇到：人力资源的调度、产品生产的安排、运输线路的规划、生度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等问题。产材料的搭配、采购批次的确定等问题。这类问题有一个共同点，即需要解决：这类问题有一个共同点，即需要解决：如何合理利

4、用各种存在约束的资源，而获得如何合理利用各种存在约束的资源，而获得最佳的经济效益最佳的经济效益，也就是达到利润最大、成，也就是达到利润最大、成本最低等目标。这就是本本最低等目标。这就是本节节要解决的要解决的“在约在约束条件下寻求目标函数最优解的规划问题束条件下寻求目标函数最优解的规划问题”。什么是规划问题?1 1、约束条件的表达、约束条件的表达2 2、目标的数学描述、目标的数学描述3 3、应用、应用ExcelExcel的规划的规划求解工具对问题求解求解工具对问题求解 一般来讲，规划问题都具有如下特点：一般来讲，规划问题都具有如下特点：1.1.所求问题都有单一的目标（如求生产的最低所求问题都有单

5、一的目标（如求生产的最低成本，求运输的最佳路线，求产品的最大盈成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间），要求求目标利，求产品周期的最短时间），要求求目标函数的最优解。函数的最优解。2.2.对于问题涉及的对象（如路程、原材料等）对于问题涉及的对象（如路程、原材料等）存在有明确的可以用不等式表达约束条件。存在有明确的可以用不等式表达约束条件。3.3.问题的表达可以描述为：一组约束条件（不问题的表达可以描述为：一组约束条件（不等式），和一个目标方程。等式），和一个目标方程。4.4.利用利用ExcelExcel技术可以简单的求得问题满足约束技术可以简单的求得问题满足约束条件求的

6、目标最优解。条件求的目标最优解。规划问题的特点（共性）2.如何加载如何加载“规划求解规划求解”1)在“工具”菜单上,单击“加载宏”2)在弹出的对话框中的“可用加载宏”列表框中,选定待添加的加载宏“规划求解”选项旁的复选框,然后单击“确定”.单击“确定”后,“工具”菜单下就会出现一项“规划求解”3.“规划求解规划求解”各参数设置各参数设置单击“规划求解”按钮,将会出现以下规划求解参数设置对话框单击“添加”,显示添加约束对话框选项:显示”规划求解选项”对话框.在其中可以加载或保存规划求解模型,并对规划求解过程的高级属性进行控制4.“规划求解规划求解”步骤步骤 启用启用“规划求解规划求解”宏；宏；输

7、入数据；输入数据；利用函数利用函数“SUMPRODUCT”SUMPRODUCT”引入约束与目标引入约束与目标 对话框对话框“规划求解规划求解”的各要素的各要素.例例1.雅致家具厂生产计划优化问题雅致家具厂生产计划优化问题雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。应如何安排这四种家具的日产量，使得该厂的日利润最大？表1 雅致家具厂基本数据家 具 类 型 1 2 3 4可提供量劳动时间

8、（小时/件）2 1 3 2 400小时木材（单位/件）4 2 1 2 600单位单位玻璃（单位/件）6 2 1 2 1000单位单位单位利润（元/件）60 20 40 30 最大销售量（件）100 200 50 100解：依题意，设置四种家具的日产量分别为决策变量约束条件为三种资源的供应量限制和产品销售量限制。据此，列出下面的线性规划模型：，目标要求是日利润最大化，其中分别为四种家具的日产量。SUMPRODUCT函数函数SUMPRODUCT的意思是:乘积之和乘积之和 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法语法SUMPRODUCT（array1,array2,array

9、3,.）Array1,array2,array3,.为 2 到 30 个数组，其相应元素需要进行相乘并求和。下面介绍用下面介绍用Excel中的中的“规划求解规划求解”功能求此题。功能求此题。第一步第一步 在在Excel中描述问题、建立模型，如下图所示。中描述问题、建立模型，如下图所示。=SUMPRODUCT(B6:E6,$B$15:$E$15)第二步第二步 在在“工具工具”菜单中选择菜单中选择“规划求解规划求解”。第三步第三步 在在“规划求解参数规划求解参数”对话框进行选择如下图。对话框进行选择如下图。第四步第四步 点击点击“选项选项”按钮，弹出按钮，弹出“规划求解选项规划求解选项”对话框。对

10、话框。第五步第五步 选择选择“采用线性模型采用线性模型”和和“假定非负假定非负”，单，单击击“确定确定”，返回下图。单击，返回下图。单击“求解求解”，即可解决，即可解决此题。此题。最后结果如下页图所示。最后结果如下页图所示。用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。最优解c+c-cb对偶最优解实际使用量+b-b递减成本指目标函数中决策变量的系数必须改进多少才能得到该决策变量的正数解，改进对最大值为增加，对最小值为减少。在实际问题中，规划模型中的大多数数在实际问题中，规划模型中的大多数数据是测量、统计、评估或决策而得出来的。据是测量、统计、评估或决策而得出来的。因此有必要分析当

11、这些数据发生波动时会因此有必要分析当这些数据发生波动时会对最优解和最优值产生什么影响。这就是对最优解和最优值产生什么影响。这就是灵敏度分析。灵敏度分析。5.敏感性分析敏感性分析出现以下假设，上述案例如何决策？出现以下假设，上述案例如何决策？?（2）家具厂是否愿意出）家具厂是否愿意出10元的加班费，让元的加班费，让某工人加班某工人加班1小时？小时？（3）如果可提供的工人劳动时间变为）如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化？小时，该厂的日利润有何变化？（4）该厂应优先考虑购买何种资源？）该厂应优先考虑购买何种资源？（5）若因市场变化，第一种家具的单位利）若因市场变化，第一种家

12、具的单位利润从润从60元下降到元下降到55元，问该厂的生产计划元，问该厂的生产计划及日利润将如何变化？及日利润将如何变化？本问题的敏感性报告如上页表所示。由上述敏感性报告可进行灵敏度分析，并回答题目中的问题由上述敏感性报告可进行灵敏度分析，并回答题目中的问题(2)一一(5)。(2)由敏感性报告可知，劳动时间的影子价格为12元，即在劳动时间的增量不超过25小时的条件下，每增加l小时劳动时间，该厂的利润(目标值)将增加12元。因此，付给某工人10元以增加l小时劳动时间是值得的，可多获利为：12102(元)。(3)当可提供的劳动时间从400小时减少为398小时时，该减少量在允许的减量(100小时)内

13、，所以劳动时间的影子价格不变，仍为12元。因此，该厂的利润变为：9200+12X(398400)9 176(元)。(4)由敏感性报告可见，劳动时间与木材这两种资源的使用量等于可提供量，所以它们的约束条件为“紧”的，即无余量的；而玻璃的使用量为800，可提供量为1000，所以玻璃的约束条件是“非紧”的，即有余量的。因此，应优先考虑购买劳动时间与木材这两种资源。(5)由敏感性报告可知，家具1的目标系数(即单位利润)允许的减量为20，即当家具1的单位利润减少量不超过20元时，最优解不变。因此，若家具1的单位利润从60元下降到55元，下降量为5元，该下降量在允许的减量范围内，这时，最优解不变。因此，四种家具的最优日产量仍分别为100件、80件、40件和0件。最优值变为：9200+(5560)X1008 700(元)。实验内容：实验内容：