《excel规划求解案例分析》由会员分享，可在线阅读，更多相关《excel规划求解案例分析（34页珍藏版）》请在金锄头文库上搜索。
1、目标规划问题及其数学模型？目标规划问题的提出例1 某工厂生产两种产品，受到原材料供应和设备工 时的限制。在单件利润等有关数据已知的条件下，要 求制定一个获利最大的生产计划。具体数据如下：产品 I II 限量原材料(kg/件) 5 10 60 设备工时(h/件) 4 4 40利润(元/件) 6 8问该公司应制造两种家电各多少件，使获取的利润 为最大。max Z= 6x1 +8x25x1 + 10x2 604x1 + 4x2 40x1 , x2 0st.设产品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的“规划求解”工具取自德克萨 斯大学奥斯汀分校的Leon Lasdon和克里夫 兰州立大学的Allan Waren共同开发的 Generali
3、zed Reduced Gradient(GRG2)非线性 最优化代码. 线性和整数规划问题取自 Frontline Systems公司的John Waston和Dan Fylstra提供的有界变量单纯形法和分支定界 法在计划管理中常会遇到：人力资源的调 度、产品生产的安排、运输线路的规划、生 产材料的搭配、采购批次的确定等问题。这类问题有一个共同点，即需要解决： 如何合理利用各种存在约束的资源，而获得 最佳的经济效益，也就是达到利润最大、成 本最低等目标。这就是本节要解决的“在约 束条件下寻求目标函数最优解的规划问题” 。什么是规划问题什么是规划问题? ?1、约束条件的表达 2、目标的数学描
4、述 3、应用Excel的规划 求解工具对问题求解一般来讲，规划问题都具有如下特点： 1. 所求问题都有单一的目标（如求生产的最低 成本，求运输的最佳路线，求产品的最大盈 利，求产品周期的最短时间），要求求目标 函数的最优解。 2. 对于问题涉及的对象（如路程、原材料等） 存在有明确的可以用不等式表达约束条件。 3. 问题的表达可以描述为：一组约束条件（不 等式），和一个目标方程。 4. 利用Excel技术可以简单的求得问题满足约束 条件求的目标最优解。规划问题的特点（共性）2.如何加载“规划求解”1) 在“工具”菜单上,单击“加载宏”2) 在弹出的对话框中的“可用加载宏”列表框中, 选定待添加
5、的加载宏“规划求解”选项旁的复 选框,然后单击“确定”.单击“确定”后,“工具”菜 单下就会出现一项“规划求解”3. “规划求解”各参数设置单击“规划求解”按钮,将会出现以下规划求解 参数设置对话框 单击“添加”,显示添加约束对话框 选项:显示”规划求解选项”对话框.在其中可以 加载或保存规划求解模型,并对规划求解过程 的高级属性进行控制4. “规划求解”步骤 启用启用“规划求解规划求解”宏；宏； 输入数据；输入数据； 利用函数利用函数“SUMPRODUCT”SUMPRODUCT”引入约束与目标引入约束与目标 对话框对话框“规划求解规划求解”的各要素的各要素. .例1. 雅致家具厂生产计划优化
6、问题雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。应如何安排这四种家具的日产量，使得该厂的日利 润最大？表1 雅致家具厂基本数据家 具 类 型1 2 3 4 可提供量劳动时间 （小时/件）2 1 3 2400小时木材（单位/件）4 2 1 2600单单位玻璃（单位/件）6 2 1 21000单单位单位利润（元/件）60 20 40 30 最大销售量（件）100 200 50
7、100解：依题意，设置四种家具的日产量分别为决策变量约束条件为三种资源的供应量限制和产品销售量限制 。 据此，列出下面的线性规划模型：，目标要求是日利润最大化，其中分别为四种家具的日产量。SUMPRODUCT函数 SUMPRODUCT的意思是:乘积之和 在给定的几组数组中，将数组间对应 的元素相乘，并返回乘积之和。 语法 SUMPRODUCT（array1,array2,array3, .） Array1,array2,array3, . 为 2 到 30 个数 组，其相应元素需要进行相乘并求和。下面介绍用Excel中的“规划求解”功能求此题。第一步 在Excel中描述问题、建立模型，如下图所
8、示。=SUMPRODUCT(B6:E6,$B$15:$E$15)第二步 在“工具”菜单中选择“规划求解”。第三步 在“规划求解参数”对话框进行选择如下图。第四步 点击“选项”按钮，弹出“规划求解选项”对话框。第五步 选择“采用线性模型”和“假定非负”，单击“ 确定”，返回下图。单击“求解”，即可解决此题。最后结果如下页图所示。用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。最优解c+ c-cb对偶最 优解实际使用量 + b- b递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解，改进对 最大值为增加，对 最小值为减少。 在实际问题中，规划模型中的大多数
9、数 据是测量、统计、评估或决策而得出来的 。因此有必要分析当这些数据发生波动时 会对最优解和最优值产生什么影响。这就 是灵敏度分析。5. 敏感性分析出现以下假设，上述案例如何决策？? （2）家具厂是否愿意出10元的加班费，让 某工人加班1小时？ （3）如果可提供的工人劳动时间变为398 小时，该厂的日利润有何变化？ （4）该厂应优先考虑购买何种资源？ （5）若因市场变化，第一种家具的单位利 润从60元下降到55元，问该厂的生产计划 及日利润将如何变化？本问题的敏感性报告如上页表所示。 由上述敏感性报告可进行灵敏度分析，并回答题目中的问题 (2)一(5)。 (2)由敏感性报告可知，劳动时间的影子
10、价格为12元，即在劳 动时间的增量不超过25小时的条件下，每增加l小时劳动时间 ，该厂的利润(目标值)将增加12元。因此，付给某工人10元以增加l小时劳动时间是值得的，可多获利为：12102(元)。 (3)当可提供的劳动时间从400小时减少为398小时时，该减 少量在允许的减量(100小时)内，所以劳动时间的影子价格不 变，仍为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(元)。实验内容：
