在Excel中使用加载宏进行规划求解的方法

1、启动Excel并打开工作表，如图1所示。在“文件”选项卡中选择“选项”选项打开“Excel选项”对话框，在左侧列表中选择“加载宏”选项，在右侧单击“转到”按钮，如图2所示。

图1　打开工作表

图2　单击“转到”按钮

2、在打开的“加载宏”对话框的“可用加载宏”列表中勾选“规划求解加载项”复选框，如图3所示。单击“确定”按钮关闭“加载宏”对话框。

图3　勾选“规划求解加载项”复选框

3、在“数据”选项卡的“分析”组中单击“规划求解”按钮打开“规划求解参数”对话框，在对话框的“设置目标”文本框中指定D5单元格，选择“最小值”单选按钮，将“通过更改可变单元格”指定为表格中“数量”数据所在的单元格区域，即B2至B5单元格区域，单击“添加”按钮，如图4所示。

图4　打开“规划求解参数”对话框

提示

规划求解是一个可以执行反复试验搜索的优化引擎，其通过优化模型计算得出一个或多个变量的值。优化模型包括3个部分：目标单元格、可变单元格和约束条件。目标单元格是要优化的最终结果所在的单元格，在该单元格中必须按照数据关系建立与可变单元格关联的公式，其目标值有最大值、最小值和目标值这3种情况；可变单元格为需要求解的一个或多个未知数，其被目标单元格公式所引用，必须直接或间接与目标单元格相关，其最多可以指定200个可变单元格；约束条件是对目标单元格和可变单元格的限制条件，约束条件通过“添加约束”对话框来添加。

4、此时将打开“添加约束”对话框，在对话框的“单元格引用”文本框中指定“产品甲”的“数量”数据所在的单元格B2，在中间的下拉列表中选择“>=”选项，在右侧的“约束”文本框中输入数据“60”，完成设置后单击“添加”按钮，如图5所示。使用相同的方法添加其他的约束条件，这些条件将添加到“规划求解参数”对话框的“遵守约束”列表中，如图6所示。

图5　添加约束条件

图6　在“遵守约束”列表中添加约束条件

提示

“规划求解参数”对话框的“选择求解方法”下拉列表提供了3种求解方法供用户选择。其中，“非线性GRG”选项用于解决平滑非线性问题，“单纯线性规划”选项用于解决线性问题，“演化”选项用于解决非平滑问题。

在对话框中单击“选项”按钮将打开“选项”对话框，该对话框包含“对约束精确度”、“具有整数约束的求解”和“求解极限值”等设置项供用户选择，同时用户也可以对非线性GRG和演化等求解方法进行设置。

5、完成约束条件添加后，单击“求解”按钮。此时将打开“规划求解结果”对话框，在对话框中选择“保留规划求解的解”单选按钮，单击“确定”按钮关闭该对话框，如图7所示。

图7　“规划求解结果”对话框

6、此时在工作表中将显示规划求解的结果，这里将能够得到生产成本最低的时候的产品生产数量，如图8所示。

图8　显示求解结果