用Excel进行最优值规划.pdfVIP

页码，1/7 Excel进行最优值规划 在生产和生活中，有时会遇到需要最优值规划分析的事情。 例如装修房子时买多少桶油漆合适，商品打几折既吸引顾客又能 获得尽可能大的利润等等。用Excel来解决此类问题，可以 快地 得到准确方案。在Excel中有一个增益工具——规划求解，它能够 自动计算出Excel工作表中某些单元格数值达到最优时的解决方 案，而且能够自动生成一些有价值的分析报表。下面就以计算某 公司产品利润的最大化为例，来看看这一切是如何实现的。 这家公司的基本生产情况是：生产A、B两种产品，其中每生 产A产品1kg需要耗用原材料40kg，耗用工时30小时，单位利润为 137元/kg；每生产B产品1kg需要耗用原材料39kg，耗用工时33小 时，单位利润为136元/kg，按照公司原料采购计划，每月原料供 应量为9000kg，工时为7000小时。根据以上条件，就可以运用规 划求解，计算出该公司在一个月内可以实现的最大利润额以及相 应的各种产品生产量最佳组合。 一、构建模型 启动Excel，新建一个表格，在其中输入产品名称、单位耗用 原料、单位耗时时间、单位利润、计划产量，另外在其下面输入 月 原料配额、月 时间配额、原料总用量、总生产时间、总利 润等项目 （图1）。然后在这个工作表中，将前面已知的生产相关 数据添加进去，如单位耗用原料量、单位耗用时间、单位利润、 月 原料限额、月 时间限额等，同时还必须输入相应公式以确 定在一定的计划产量下，预计的原料总用量、总生产时间以及总 利润。 页码，2/7 图1 由于原料用量＝计划产量×单位耗用原料量，而原料总用量 就等于A、B产品二者的原料用量之和，在此工作表中即：原料总 用量＝D4 ×G4+D5 ×G5，而总生产时间＝E4 ×G4+E5 ×G5，总利润 ＝F4×G4+F5 ×G5。 这里可以使用数组乘积函数SUMPRODUCT来快速完成所求积之 和，在D10单元格内输入公式“=SUMPRODUCT(D4:D5,G4:G5)”即可 （图2），采用同样的方法，在D11、D12单元格内分别输入： =SUMPRODUCT(E4:E5,G4:G5)，=SUMPRODUCT(F4:F5,G4:G5)，用来 计算总生产时间以及总利润。 页码，3/7 图2 小提示：SUMPRODUCT函数可以在给定的几组数组中，将数组 间对应的元素相乘，并返回乘积之和。其语法为SUMPRODUCT (array1,array2,array3, ...)，array1、array2、array3…为2 到30个数组，其相应元素需要进行相乘并求和。 二、参数设置 在完成上述工作表后，点击“工具→规划求解”，弹出 “规 划求解参数”设置窗口。在规划求解前，必须先进行相关规划求 解参数的设置，包括规划求解的目标、可变量、约束条件等，具 体设置如下 （图3）： 页码，4/7 图3 单击 “设置目标单元格”输入框右侧的选择按钮，选择工作 表中总利润所在的单元格D12，由于是计算公司利润的最大化，因 此从下面选择“最大值”选项；单击“可变单元格”中输入框右 侧的选择按钮，选择工作表中可以变化的计划产量所在的单元格 区 G4:G5，通过这里不同的产品产量组合，可以实现在满足约束 条件情况下得到最大利润；根据前面公司的基本生产条件情况， 存在几个影响计算利润最大化的约束条件，即：原料总用量≤ 9000kg、总生产时间≤7000小时，必须将这些约束条件设置在规 划求解参数窗口中。 单击窗口中的 “约束”下面的 “添