1.5 Excel中的规划求解问题
规划求解是Microsoft Excel加载项程序,可用于模拟分析。使用“规划求解”查找目标单元格中公式的优化值或最大最小值,受限或受制于工作表上其他公式单元格的值。“规划求解”与一组用于计算目标和约束单元格中公式的单元格(称为决策变量或变量单元格)一起工作。“规划求解”调整决策变量单元格中的值以满足约束单元格上的限制,并产生对目标单元格期望的结果。简单来说,使用“规划求解”可通过更改其他单元格来确定一个单元格的优化值或最大最小值。例如,可以更改计划的广告预算金额,并查看对计划利润额产生的影响。
规划求解的整个工作过程如下。
首先建立Excel规划求解模型,即把实际问题用Excel表达出来,确定可变单元格、目标单元格和约束条件所对应的单元格区域。然后用Excel公式和函数建立可变单元格和目标单元格之间的联系。设置规划求解工具对话框即设置目标单元格、可变单元格和遵守的约束。可以看到,Excel规划求解工具把复杂问题的求解过程变得相当简单。使用Excel规划求解工具的核心是建立实际问题的Excel规划求解模型,即建立可变单元格和目标单元格之间的联系。如果模型建好了,问题就解决了一大半。
在“数据”选项卡的“分析”组中,单击“规划求解”,即可弹出“规划求解参数”对话框,如图1-29所示。
图1-29 “规划求解”参数对话框
注意:如果“规划求解”命令或“分析”组不可用,则需要激活“规划求解”加载项。Excel激活加载项的方法请参阅1.4节Excel分析工具库。
在“设置目标”框中,输入目标单元格的单元格引用或名称。目标单元格必须包含公式。执行下列操作之一:若要使目标单元格的值尽可能大,则单击“最大值”单选按钮;若要使目标单元格的值尽可能小,则单击“最小值”单选按钮;若要使目标单元格为确定值,则单击“值”单选按钮,然后在文本框中输入数值。在“通过更改可变单元格”框中,输入每个决策变量单元格区域的名称或引用。用逗号分隔不相邻的引用。可变单元格必须直接或间接与目标单元格相关联。最多可以指定200个可变单元格。
在“遵守约束”框中,通过执行下列操作输入任何要应用的约束。
(1)在“规划求解参数”对话框中,单击“添加”按钮。
(2)在“单元格引用”框中,输入要对其中数值进行约束的单元格区域的单元格引用或名称。
(3)单击希望在引用单元格和约束之间使用的关系(“＜=”、“=”、“＞=”、“int”、“bin”或“dif”)。如果单击“int”单选按钮,则“约束”框中会显示“整数”。如果单击“bin”单选按钮,则“二进制”将出现在“约束”框中。如果单击“dif”单选按钮,则“all different”将出现在“约束”框中。
(4)如果在“约束”框中选择关系＜=、=或＞=,请输入数字、单元格引用或名称、公式。
(5)要接受约束并添加另一个约束,可单击“添加”按钮;要接受约束条件并返回“规划求解参数”对话框,则单击“确定”按钮。
在“规划求解参数”对话框中选择以下三种算法或求解方法中的任意一种,其中广义简约梯度“GRG非线性”用于计算平滑非线性问题;“LP Simplex单纯线性规划”用于计算线性问题;“演化”用于非平滑问题。单击“求解”按钮,再执行下列操作之一:若要在工作表中保存求解值,在“规划求解结果”对话框中单击“保存规划求解的解”单选按钮;若要在单击“求解”按钮之前恢复原值,请单击“恢复原值”单选按钮。在计算的过程中,如需中断求解过程,可按Esc键。
Excel利用找到的有关决策变量单元格的最后值重新计算工作表。要在“规划求解”找到解决方案后创建基于解决方案的报告,单击“报表”框中的报告类型,然后单击“确定”按钮。此报告是在工作簿中的一个新工作表上创建的。如果“规划求解”未找到解决方案,则只有部分报表可用或全部不可用。要将决策变量单元格值保存为可以稍后显示的方案,可在“规划求解结果”对话框中单击“保存方案”按钮,然后在“方案名”框中输入方案的名称。
例1.3 假如我们有1 000元钱,要买齐5种商品,每种商品至少要买一件或以上,每种商品的价格如图1-30所示,如果希望恰好把1 000元钱用掉,每种商品应该各买多少件?
图1-30 5种商品的价格
【实验步骤】
(1)设置目标表达式
作为使用Excel规划求解工具的第一步,也是最重要的一步,必须把问题用Excel表达出来,变成Excel规划求解工具能够理解的模型。
如图1-31所示,把单元格C2:C6命名为“可变单元格”,设置数量的初始值都是1,把单元格B8命名为“目标单元格”。在目标单元格中输入公式:
"=SUMPRODUCT(B2:B6*C2:C6)"
用来计算当前商品的总价。该公式也可以写成
"=SUMPRODUCT(B2:B6,C2:C6)"
图1-31 计算可变单元格
注意:
SUMPRODUCT()函数在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
SUMPRODUCT()函数语法:
SUMPRODUCT(ar ray1,[ar ray2],[ar ray3],...)
SUMPRODUCT函数语法具有下列参数。
•array1:必需,其相应元素需要进行相乘并求和的第一个数组参数。
•array2,array3,…:可选,2～255个数组参数,其相应元素需要进行相乘并求和。
(2)选择“数据”选项卡“分析”组中的“规划求解”命令,弹出“规划求解参数”对话框,并按照图1-32填写参数。
图1-32 设置规划求解参数
•设置目标:$B$8。
•到:选中“目标值”单选按钮,设置为1000;
•遵守约束1:单击“添加”按钮,设置数量必须都大于等于1,即$C$2:$C$6＞=1,如图1-33所示。
图1-33 添加数量必须大于等于1的约束条件
继续单击“添加”按钮,设置数量必须是整数,即$C$2:$C$6为“int”,如图1-34所示。
图1-34 添加数量必须为整数的约数条件
•选择求解方法:GRG非线性。
•单击“求解”按钮,运算如图1-35所示。
图1-35 规划求解结果
【结论】运算结果报告如图1-36所示,如果希望恰好把1 000元钱花掉,商品1的数量为4,商品2的数量为5,商品3的数量为2,商品4的数量为5,商品5的数量为6。
图1-36 运算结果报告