应用最小二乘法的实践：2.6版

在讲解最小二乘法的时候，我们有必要先回顾一个重要的数据分析工具：Solver Add－On（规划求解工具）。

Excel规划求解的选项工具可以用来解决线性规划与非线性规划优化问题。规划求解可以用来解决最多200个变量、100个外在约束和400个简单约束（决策变量整数约束的上下边界）的问题。要调用规划求解，从主菜单中选择工具（tool）／求解（Solver）。注意：如果没有加载该工具选项，读者将看不到求解／Solver此项。规划求解参数对话框如图2．58所示。

图2．58

规划求解参数对话框用来描述EXCEL的优化问题。可以设置决策变量为整数的约束条件。设置目标单元格应该包含正考虑问题目标函数的单元格地址。选择最大或最小可以用来确定设定目标单元格的寻找最大值或最小值。如果选择了相关值，规划求解将努力去寻找使目标单元格的值等于选项右侧框中的值。可变单元格框应该包含问题中决策变量的地址。最后，必须通过点击添加按钮在约束框中详细说明。修改按钮允许你对已经加入的约束进行修改，删除按钮允许你删除前面加入的约束。重新设置按钮清除当前问题，并且将参数重新设置为默认值。选项按钮调用规划求解对话框选项（下面将讨论）。推测按钮选项对于我们没有多大的用途，这里将不讨论。当点击添加按钮时，增加约束对话框如图2．59所示。

图2．59

点击单元格引用框允许你说明单元格地址（通常是有公式的单元格）。约束形式可以选择下面的箭头（≤，≥，＝，int，int是指整数，或bin，指二元）。约束框可以含有单元格的公式，简单的单元引用，或者数值。添加按钮向现存模型增加当前描述的约束，返回添加约束对话框。OK按钮将当前的约束加入到模型中，并返回规划求解对话框。注意：规划求解并不假定决策变量是非负的。下面讨论的选项对话框能设定变量必须非负。如果从规划求解参数对话框选中了选项按钮，将会出现下面的对话框：

图2．60（a）

图2．60（b）

图2．60（c）

最长时间允许在规划求解停止之前设定秒数。类似于最长时间，迭代次数允许设定迭代的最大数量（规划求解算法的步骤）。精确度是规划求解算法的准确程度（例如，在等于约束右端项之前，约束左边项的值如何接近）。允许误差被用于整数规划。这指定了在多大百分比内的解是最优的。如果寻求最优解，允许误差必须被设置为0。如果运行时间太长，则应将此设置为一个更大的值（如果可以在最优性的百分比内接受该解）。如果模型是线性规划或者线性整数规划，应该选择线性模型假定。这样规划求解就用单纯形法而不是更耗时间的非线性算法进行求解（一般简约梯度法）。如果希望所有的可变单元格都≥0，应该选择非负性。如果希望察看逐次迭代的信息（这将使速度降低），则选中显示迭代结果。如果模型比例比较差，则启用自动按比例缩放（如果输入的数量级显著不同）。最后，对话框的底部是关于非线性算法的选项，即，如何估计非线性，如何估计变化速率和应用的搜索技术。一般说来，大多数参数的默认值运行得很有效。重要的是，如果是线性规划或者整数规划，要记住选择线性模型假设。如果希望可变单元格仅仅取非负值，则可以选择非负性。同时，如果解整数规划问题，并求最优解，要确定允许误差为0％。当问题解决以后，Solver Result框将会出现：

图2．61(www.xing528.com)

你可以选择工作表中由Solver确定的解或者重新保存元数据。同时，如Report框中所示，可以自动产生三种报告。在框中点击可以选择其中的任何报告。

解整数线性模型、非线性模型，以及非线性整数模型可以运用前面叙述的技术来完成。要指定约束为整数，调用Add Constraint对话框，选择Cell Reference中的可变单元格，选择int（整数）类型（对于二元变量选择bin）。对于非线性模型，那么就不要在Option对话框中选中Assume Linear Model。

我们举一个投资组合的例子来说明如何具体应用规划求解分析工具。在图2．62中给出了组合的三种资产权重分别为40％、50％、10％时，组合的预期收益为2．2％。而目标收益是7％，因此所求得的有效组合权重必须使得组合的收益为7％，且组合收益的标准差最小。

为了解决这个最优化的问题，规划求解工具需要设定可变单元格，求最小或最大的目标单元格，以及限制条件说明。限制条件用来限制可变单元格中数据的变化范围。在B19：B21组合权重区域中，我们规定权重和为1，这个条件我们可以在Excel中事先设定，例如B21＝1－B20－B19，也可以在规划求解工具的限制条件中。可以想象，第一个方案更省时。我们还可以从这个问题中得到一个显然的限制条件，以及目标的预期回报率Expected Return＝7％，并将B26作为目标函数（组合的标准差）。

图2．62

图2．63

下面，我们就利用规划求解工具解决这个问题。

得到的结果如图2．64所示：

图2．64

我们从B19：B21区域中看到，我们得到一个解（Tbills＝－6％，Bonds＝36％， Shares＝70％）。