用Excel进行最优值规划

《用Excel进行最优值规划》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、页码，1/7用Excel进行最优值规划在生产和生活中，有时会遇到需要最优值规划分析的事情。例如装修房子时买多少桶油漆合适，商品打几折既吸引顾客又能获得尽可能大的利润等等。用Excel来解决此类问题，可以很快地得到准确方案。在Excel中有一个增益工具——规划求解，它能够自动计算出Excel工作表中某些单元格数值达到最优时的解决方案，而且能够自动生成一些有价值的分析报表。下面就以计算某公司产品利润的最大化为例，来看看这一切是如何实现的。这家公司的基本生产情况是：生产A、B两种产品，其中每生产A产品1kg需要耗用原材料40kg，耗用工时30小时，

2、单位利润为137元/kg；每生产B产品1kg需要耗用原材料39kg，耗用工时33小时，单位利润为136元/kg，按照公司原料采购计划，每月原料供应量为9000kg，工时为7000小时。根据以上条件，就可以运用规划求解，计算出该公司在一个月内可以实现的最大利润额以及相应的各种产品生产量最佳组合。一、构建模型启动Excel，新建一个表格，在其中输入产品名称、单位耗用原料、单位耗时时间、单位利润、计划产量，另外在其下面输入月度原料配额、月度时间配额、原料总用量、总生产时间、总利润等项目（图1）。然后在这个工作表中，将前面已知的生产相关数据添加进去，

3、如单位耗用原料量、单位耗用时间、单位利润、月度原料限额、月度时间限额等，同时还必须输入相应公式以确定在一定的计划产量下，预计的原料总用量、总生产时间以及总利润。页码，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），采用同

4、样的方法，在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个数组，其相应元素需要进行相乘并求和。二、参数设置在完成上述工作表后，点击“工具→规划求解”，弹出“规划求解参数”设置窗口。在规

5、划求解前，必须先进行相关规划求解参数的设置，包括规划求解的目标、可变量、约束条件等，具体设置如下（图3）：页码，4/7图3单击“设置目标单元格”输入框右侧的选择按钮，选择工作表中总利润所在的单元格D12，由于是计算公司利润的最大化，因此从下面选择“最大值”选项；单击“可变单元格”中输入框右侧的选择按钮，选择工作表中可以变化的计划产量所在的单元格区域G4:G5，通过这里不同的产品产量组合，可以实现在满足约束条件情况下得到最大利润；根据前面公司的基本生产条件情况，存在几个影响计算利润最大化的约束条件，即：原料总用量≤9000kg、总生产时间≤70

6、00小时，必须将这些约束条件设置在规划求解参数窗口中。单击窗口中的“约束”下面的“添加”按钮，在弹出的对话框中，单击“单元格引用位置”右侧的选择按钮，选择工作表中“原料总用量”所在的单元格：D10，选择判断符号“≤”，然后单击“约束值”右侧的选择按钮，选择工作表中“月度原料限额”所在的单元格：D7，单击“添加”按钮，这样关于原料总用量≤9000kg的约束条件就创建了。采用同样的方法，将总生产时间≤7000小时的约束条件输入到“规划求解参数”设置窗口中（图4）。图4页码，5/7为防止计算结果出现负数，单击规划求解参数设置窗口中的“选项”按钮，在

7、弹出的对话框中，将其中的“假定非负”选项选中，单击“确定”按钮。小提示：因为规划求解工具是一个Excel增益插件，在第一次使用规划求解时，必须先予以加载，执行“工具→加载宏”命令，从弹出窗口中的“可用加载宏”列表框中，选择“规划求解”选项，单击“确定”即可。三、结果分析当在“规划求解参数”设置窗口中设置完毕后，单击“求解”按钮，在工作表的“总利润”单元格D12内，就显示出可以达到的最大利润值为30986.67元，以及相应的最佳产量组合，A产品的产量为160kg，B产品的产量为67kg（图5）。图5同时会弹出一个“规划求解结果”对话框，单击对话

8、框中的“保存规划求解结果”选项，将规划求解计算结果予以保存，为了得到详细的规划求解明细报表，选中对话框中右侧“报告”列表框中的“运算结果报告”、“敏感性报告”、“极