EXCEL规划求解案例分析.ppt

EXCEL规划求解案例分析.ppt

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

目标规划问题及其数学模型？

目标规划问题的提出,例1某工厂生产两种产品，受到原材料供应和设备工时的限制。

在单件利润等有关数据已知的条件下，要求制定一个获利最大的生产计划。

具体数据如下：

产品III限量原材料（kg/件）51060设备工时（h/件）4440利润（元/件）68,问该公司应制造两种家电各多少件，使获取的利润为最大。

maxZ=6x1+8x25x1+10x2604x1+4x240x1,x20,st.,设产品I和II的产量分别为x1和x2，其数学模型为：

其最优解，即最优生产计划为x18件，x22件，maxZ64元。

例2假设在前面例子中，计划人员被要求考虑如下意见：

（1）由于产品II销售疲软，故希望产品II的产量不超过产品I的一半；

（2）原材料严重短缺，生产中应避免过量消耗；（3）最好能节约4h设备工时；（4）计划利润不少于48元。

类似这样的多目标决策问题是典型的目标规划问题。

运用EXCEL求解线性规划问题,outline,1.关于“规划求解”2.如何加载“规划求解”3.“规划求解”各参数设置4.“规划求解”步骤5.敏感性分析,1.关于“规划求解”,MicrosoftExcel的“规划求解”工具取自德克萨斯大学奥斯汀分校的LeonLasdon和克里夫兰州立大学的AllanWaren共同开发的GeneralizedReducedGradient（GRG2）非线性最优化代码.线性和整数规划问题取自FrontlineSystems公司的JohnWaston和DanFylstra提供的有界变量单纯形法和分支定界法,在计划管理中常会遇到：

人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等问题。

这类问题有一个共同点，即需要解决：

如何合理利用各种存在约束的资源，而获得最佳的经济效益，也就是达到利润最大、成本最低等目标。

这就是本节要解决的“在约束条件下寻求目标函数最优解的规划问题”。

什么是规划问题?

1、约束条件的表达2、目标的数学描述3、应用Excel的规划求解工具对问题求解,一般来讲，规划问题都具有如下特点：

所求问题都有单一的目标（如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间），要求求目标函数的最优解。

对于问题涉及的对象（如路程、原材料等）存在有明确的可以用不等式表达约束条件。

问题的表达可以描述为：

一组约束条件（不等式），和一个目标方程。

利用Excel技术可以简单的求得问题满足约束条件求的目标最优解。

规划问题的特点（共性）,2.如何加载“规划求解”,1）在“工具”菜单上,单击“加载宏”,2）在弹出的对话框中的“可用加载宏”列表框中,选定待添加的加载宏“规划求解”选项旁的复选框,然后单击“确定”.单击“确定”后,“工具”菜单下就会出现一项“规划求解”,3.“规划求解”各参数设置,单击“规划求解”按钮,将会出现以下规划求解参数设置对话框,单击“添加”,显示添加约束对话框,选项:

显示”规划求解选项”对话框.在其中可以加载或保存规划求解模型,并对规划求解过程的高级属性进行控制,4.“规划求解”步骤,启用“规划求解”宏；输入数据；利用函数“SUMPRODUCT”引入约束与目标对话框“规划求解”的各要素.,例1.雅致家具厂生产计划优化问题,雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。

该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。

应如何安排这四种家具的日产量，使得该厂的日利润最大？

表1雅致家具厂基本数据,解：

依题意，设置四种家具的日产量分别为决策变量,约束条件为三种资源的供应量限制和产品销售量限制。

据此，列出下面的线性规划模型：

，目标要求是日利润最大化，,其中,分别为四种家具的日产量。

SUMPRODUCT函数,SUMPRODUCT的意思是:

乘积之和在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

语法SUMPRODUCT（array1,array2,array3,.）Array1,array2,array3,.为2到30个数组，其相应元素需要进行相乘并求和。

下面介绍用Excel中的“规划求解”功能求此题。

第一步在Excel中描述问题、建立模型，如下图所示。

=SUMPRODUCT（B6:

E6,$B$15:

$E$15）,第二步在“工具”菜单中选择“规划求解”。

第三步在“规划求解参数”对话框进行选择如下图。

第四步点击“选项”按钮，弹出“规划求解选项”对话框。

第五步选择“采用线性模型”和“假定非负”，单击“确定”，返回下图。

单击“求解”，即可解决此题。

最后结果如下页图所示。

用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。

最优解,c,+c,-c,b,对偶最优解,实际使用量,+b,-b,递减成本指目标函数中决策变量的系数必须改进多少才能得到该决策变量的正数解，改进对最大值为增加，对最小值为减少。

在实际问题中，规划模型中的大多数数据是测量、统计、评估或决策而得出来的。

因此有必要分析当这些数据发生波动时会对最优解和最优值产生什么影响。

这就是灵敏度分析。

5.敏感性分析,出现以下假设，上述案例如何决策？

?

（2）家具厂是否愿意出10元的加班费，让某工人加班1小时？

（3）如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化？

（4）该厂应优先考虑购买何种资源？

（5）若因市场变化，第一种家具的单位利润从60元下降到55元，问该厂的生产计划及日利润将如何变化？

本问题的敏感性报告如上页表所示。

由上述敏感性报告可进行灵敏度分析，并回答题目中的问题

（2）一（5）。

（2）由敏感性报告可知，劳动时间的影子价格为12元，即在劳动时间的增量不超过25小时的条件下，每增加l小时劳动时间，该厂的利润（目标值）将增加12元。

因此，付给某工人10元以增加l小时劳动时间是值得的，可多获利为：

12102（元）。

（3）当可提供的劳动时间从400小时减少为398小时时，该减少量在允许的减量（100小时）内，所以劳动时间的影子价格不变，仍为12元。

因此，该厂的利润变为：

9200+12X（398400）9176（元）。

（4）由敏感性报告可见，劳动时间与木材这两种资源的使用量等于可提供量，所以它们的约束条件为“紧”的，即无余量的；而玻璃的使用量为800，可提供量为1000，所以玻璃的约束条件是“非紧”的，即有余量的。

因此，应优先考虑购买劳动时间与木材这两种资源。

（5）由敏感性报告可知，家具1的目标系数（即单位利润）允许的减量为20，即当家具1的单位利润减少量不超过20元时，最优解不变。

因此，若家具1的单位利润从60元下降到55元，下降量为5元，该下降量在允许的减量范围内，这时，最优解不变。

因此，四种家具的最优日产量仍分别为100件、80件、40件和0件。

最优值变为：

9200+（5560）X1008700（元）。

实验内容：