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