EXCEL规划求解案例分析

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 st. 设产品I和II的产量分别为x1和x2，其数学 模型为： 其最优解，即最优生产计划为x18件， x22件，max Z64元

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

3、大 学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码. 线 性和整数规划问题取自Frontline Systems公司的 John Waston和Dan Fylstra提供的有界变量单纯 形法和分支定界法 在计划管理中常会遇到：人力资源的调 度、产品生产的安排、运输线路的规划、生 产材料的搭配、采购批次的确定等问题。 这类问题有一个共同点，即需要解决： 如何合理利用各种存在约束的资源，而获得 最佳的经济效益，也就是达到利润最大、成 本最低等目标。这就是本节要解决的“在约 束条件下寻求目标函数最优解的规划问题”。 1、约束条

4、件的表达 2、目标的数学描述 3、应用Excel的规划 求解工具对问题求解 一般来讲，规划问题都具有如下特点： 所求问题都有单一的目标（如求生产的最低 成本，求运输的最佳路线，求产品的最大盈 利，求产品周期的最短时间），要求求目标 函数的最优解。 对于问题涉及的对象（如路程、原材料等） 存在有明确的可以用不等式表达约束条件。 问题的表达可以描述为：一组约束条件（不 等式），和一个目标方程。 1.1. 利用ExcelExcel技术可以简单的求得问题满足约束 条件求的目标最优解。 规划问题的特点（共性） 2.如何加载“规划求解” 1) 在“工具”菜单上,单击“加载宏” 2) 在弹出的对话框中的“可

5、用加载宏”列表框中,选 定待添加的加载宏“规划求解”选项旁的复选框,然 后单击“确定”.单击“确定”后,“工具”菜单下就 会出现一项“规划求解” 3. “规划求解”各参数设置 单击“规划求解”按钮,将会出现以下规划求解参数 设置对话框 单击“添加”,显示添加约束对话框 选项:显示”规划求解选项”对话框.在其中可以加 载或保存规划求解模型,并对规划求解过程的高级属 性进行控制 4. “规划求解”步骤 例1. 雅致家具厂生产计划优化问题 雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂

6、每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。 应如何安排这四种家具的日产量，使得该厂的日利 润最大？ 表1 雅致家具厂基本数据 家 具 类 型 1 2 3 4 可提供量 劳动时间（小时/件） 2 1 3 2 400小时 木材（单位/件） 4 2 1 2 600单位 玻璃（单位/件） 6 2 1 2 1000单位 单位利润（元/件） 60 20 40 30 最大销售量（件） 100 200 50 100 解：依题意，设置四种家具的日产量分别为决策变量 1234 ,x x x x 约束条件为三种资源的供应量限制和产品销售量限制 。 据此

7、，列出下面的线性规划模型： ，目标要求是日利润最大化， 1234 1234 1234 1234 1 2 3 4 1234 60204030 422600 6221000 2132400 1001 . . 2002 503 1004 ,0 MaxZxxxx xxxx xxxx xxxx x s t x x x x x x x （木木材材约约束束） （玻玻璃璃约约束束） （劳劳动动时时间间约约束束） （家家具具 需需求求量量约约束束） （家家具具 需需求求量量约约束束） （家家具具 需需求求量量约约束束） （家家具具 需需求求量量约约束束） （非非负负约约束束） 其中 1234 ,x x x x分

8、别为四种家具的日产量。 SUMPRODUCT函数 SUMPRODUCT的意思是:乘积之和 在给定的几组数组中，将数组间对应的元素 相乘，并返回乘积之和。 语法 SUMPRODUCT（array1,array2,array3, .） Array1,array2,array3, . 为 2 到 30 个数组， 其相应元素需要进行相乘并求和。 下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。 =SUMPRODUCT(B6:E6,$B$15:$E$15) 第二步 在“工具”菜单中选择“规划求解”。 第三步 在“规划求解参数”对话框进行选择如下图。

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

10、 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)9 176( 元)。 (4)由敏感性报告可见，劳动时间与木材这两 种资源的使用量等于可提供量，所以它们的约 束条件为“紧”的，即无余量的；而玻璃的使 用量为800，可提供量为1000，所以玻璃的约 束条件是“非紧”的，即有余量的。 因此，应优先考虑购买劳动时间与木材这 两种资源。 (5)由敏感性报告可知，家具1的