《用Excel进行最优值规划.doc》由会员分享，可在线阅读，更多相关《用Excel进行最优值规划.doc（8页珍藏版）》请在三一文库上搜索。
1、页码，4/7用Excel进行最优值规划在生产和生活中，有时会遇到需要最优值规划分析的事情。 例如装修房子时买多少桶油漆合适，商品打几折既吸引顾客又能 获得尽可能大的利润等等。用Excel来解决此类问题，可以 很快地得到准确方案。在Excel中有一个增益工具一一 规划求解，它能够 自动计算出Excel工作表中某些单元格 数值达到最优时的解决 方 案，而且能够自动生成一些有价值的分析报表。下面就以计算某 公司产品利润的最大 化为例，来看看这一切是 如何实现的。这家公司的基本生产情况是：生产A、B两种产品，其中每生 产A产品1kg需要耗用原材料40kg，耗用工时30小时，单位利润为 137元/kg
2、;每生产B产品1kg需要耗用原材料39kg，耗用工时33小 时，单位利润为136元/kg，按照公司原料采购计戈I，每月原料供 应量为9000kg，工时为7000小时。根据以上条件，就可以运用规 划求解，计算出该公司在一个月内可以实现的最大利润 额以及相 应的各种产品生产量最佳组合。一、构建模型启动Excel，新建一个表格，在其中输入产品名称、单位耗用 原料、单位耗时时间、单位利润、计划产量，另外在其下面输入 月度原料配额、月度时间配额、原料总用量、总生产时间、总利 润等项目（图1）。然后在这个工作表 中，将前面已知的生产相关 数据添加进去，如单位耗用原料量、单 位耗用时间、单位利润、 月度原料
3、限额、月度时间限额等，同时还必须输入相应公式以确 定在一定的计划产量下，预计的原料总用量、总生产时间以及总 利润。图1由于原料用量=计划产量X单位耗用原料量，而原料总用量 就等于A、B产品二者的原料用量之和，在此工作表中即：原料总 用量=D4X G4+DX G5,而总生产时间=E4X G4+E5X G5,总利润 =F4X G4+F5X G5b这里可以使用数组乘积函数SUMPRODL来T快速完成所求积之和，在D10单元格内输入 公式“ =SUMPRODUCT(D4:D5,G4:G”)即可(图2)，采用同样的方法，在D11、D12单元格内分别输入： =SUMPRODUCT(E4:E5,G4:G5)
4、 =SUMPRODUCT(F4:F5,G4:G5)用来 计算总生产时间以及总利润。图2小提示：SUMPRODUC数可以在 给定的几组数组中，将数组 间对应的元素相乘，并返回乘积之 和。其语法为SUMPRODUCT (array1,array2,array3,)，arrayl、array2、array3 为2到30个数组，其相应元素需要进行相乘并求和。二、参数设置在完成上述工作表后，点击“工具规划求解”，弹出“规 划求解参数”设置窗口。在规划 求解前，必须先进行 相关规划求 解参数的设置，包括规划求解的目标、可变量、约束条件等，具 体设置如下(图3):页码，5/7图3单击“设置目标单元格”输入框
5、右侧的选择按钮,选择工作 表中总利润所在的 单元格D12,由于是计算公司利润的最大化，因 此从下面选择“最大值”选项；单击“可变单元格”中输入框右 侧的选择按钮，选择工作表中可以变化的计划产量所在的单元格 区域G4:G5,通过这里不同的产品产量组合，可以实现在满足约束 条件情况下得到最大利润；根据前面公司的基本生产条件情况， 存在几个影响计算利润最大化的约束条件，即：原料总用量W 9000kg、总生产时间w 7000小时，必须将这些约束条件设置在规 划求解参数窗口中。单击窗口中的“约束”下面的“添加”按钮，在弹出的对话 框中，单击“单元格引用位置”右侧的选择按钮，选择工作表中“原料总用量”所在
6、的单元格：D10,选择判断符号“w” ,然后 单击“约束值”右侧的选择按钮，选择工作表中“月度原料限 额”所在的单元格：D7,单击“添加”按钮，这样关于原料总用 量w9000kg的约束条件就创建了。采用同样的方法，将总生产时 间w 7000小时的约束条件输入到“规划求解参数”设置窗口中（图 4）。图4页码,8/7为防止计算结果出现负数，单击规划求解参数设置窗口中的 “选项”按钮，在弹出的对话框中，将其中的“假定非负”选项 选中，单击“确定”按钮。小提示：因为规划求解工具是一个Excel增益插件，在第一 次使用规划求解时，必须先予以加载，执行“工具-加载宏”命 令，从弹出窗口中的“可用加载宏”列
7、表框中，选择“规划求 解”选项，单击“确定”即可。三、结果分析当在“规划求解参数”设置窗口中设置完毕后，单击“求 解”按钮，在工作表的“总利润”单元格D12内，就显示出可以达 到的最大利润值 为30986.67元，以及相应的最佳产量组合，A产品 的产量为160kg，B产品的产量为67kg （图5）。x图5同时会弹出一个“规划求解结果”对话框，单击对话框中的“保存规划求解结果”选项，将规划求解计算结果予以保存，为 了得到详细的规划求解明细报表，选中对话框中右侧“报告”列 表框中的“运算结果报告”、“敏感性报告”、“极限值报 告”，然后单击“确定”按钮，即可生成3个关于规划求解内容的 详细报表，分
8、别为运算结果报告、敏感性报告、极限值报告（图6）。图6从三个报告中可以得到 很多有价值的信息。比如打开“敏感 性报告”，其中列出了工作表中可变单元格以及约束条件公式对 微小变化的敏感度信息，也就是说刚才所计算出的结果在哪些范 围内还是有效的，如报告显示了A产品单位利润的允许增量、允许 减量分别为2.48717948717944、13.3636363636364，从此可以看 出即便是A产品的单位利润在 (137+2.48717948717944 )(137- 13.3636363636364 )范围内变动，依然能够在维持最佳利润的前 提下，保持原先的生产量组合。小提示：如果需要修改规划求解方案中的一些数值，如：月 度原料限额、月度时间限额等，在工作表中输入新数值后，必须 重新执行规划求解操作，单击“规划求解参数”设置窗口中的“求解”按钮，才能够得到正确的计算结果。而修改条件约束公 式，也同样必须在“规划求解参数”设置窗口中重新进行设置。编后：实际上利用规划求解不仅仅可以得到生产利润最大 化 方案，在其他的统计分析方面也大有用武之地，如家庭最低开 支、公司最低成本支出等，只要是涉及到最大效益和最小费用问 页码，#/7题,使用Excel的“规划求解”工具都可以得到较为满意的解决方 案。