单变量求解与方案的研究与方法

首先应该说明，单变量求解与方案之间没有必然联系。因为这两种工具都比较简单，而且使用单变量求解的实例直观说明方案的应用，所以把它们合并在一起介绍。

（1）单变量求解

通过调整一个单元格中的值，使得另一个单元格中的公式（公式中引用前一个单元格）等于特定值的方法叫做单变量求解。在单变量求解过程中，Excel中不断调整指定单元格中的值，直到另一个单元格中的公式得到满足要求的值为止。如果已知公式的预期结果，而用于确定此结果的输入值未知，则可以使用“单变量求解”功能，通过单击“工具”菜单上的“单变量求解”即可调用“单变量求解”工具。如果把Excel中的公式称为“函数”的话，那么在某种意义上，可以称单变量求解过程是“反函数”运算。

下面通过一个分期偿还贷款的实例说明单变量求解的原理和使用方法。

【例2．1】 贷款金额为1 000 000．00元，贷款利率为5％，贷款年限为5年，每年年底偿还一次，到期时全部还清。

①求每年还款金额为多少？

在单元格B7中输入公式“＝PMT（B4，B5，B3）”，计算出每年还款金额为230 974．80元，因为是偿还金额，所以函数返回结果为负值，相应的贷款金额为正值。

图2．1

②假定公司每年最高的偿还能力为250 000．00元，要求在贷款期限不变的情况下计算公司可以贷款的最高限额是多少？

③如果每年偿还能力为200 000．00元，可以贷款的最高限额是多少？

对于问题②和问题③的解决要借助于“单变量求解”功能。先选中每年还款金额所在单元格B7，从菜单上执行“工具”——“单变量求解”，目标单元格设为B7，目标值设为－250 000．00元（因为是偿还贷款，所以是负值），可变单元格为贷款金额B3，如图2．2所示。

点击“确定”后得到计算结果，容许的贷款额为1 082 369．17元，如图2．3所示。

用同样的方法，可以计算出当每年偿还能力为200 000．00元时，容许的贷款最高限额为865 895．33元。

上面一共计算了三种情况：第一种是原始方案，贷款1 000 000．00元，5年偿还，利率为5％，每年偿还230 974．80元；第二种是比较乐观的方案，贷款1 082 369．17元，5年偿还，利率为5％，每年偿还250 000．00元；第三种是比较保守的方案，贷款865 895．33元，5年偿还，利率为5％，每年偿还200 000．00元。如果要把这些不同情况保存并进行比较，就可以使用Excel中的方案功能。

图2．2

图2．3

图2．4(www.xing528.com)

图2．5

（2）方案

方案是保存在工作表中并可以进行替换的一组值。可以使用方案来保存工作表中模型输出的不同结果。同时还可以在工作表中创建并保存不同的数组，然后切换到任意方案以查看不同的结果。

①创建方案。

例如，在上面的分期偿还贷款的实例中，分别计算了不同贷款金额的三种情况，其中后两个是通过单变量求解得到的。可以把3种情况分别保存为不同的方案并命名，然后针对不同方案进行对比分析。

原来预定贷款金额1 000 000．00元，每年偿还230 974．80元；如果每年偿还250 000．00元；则可贷款1 082 369．17元；如果每年偿还200 000．00元，则可贷款865 895．33元。可以按每期还款金额的不同把这三种方案命名为初始、乐观和保守方案，并保存在Excel工作表中，供以后参考使用。

操作步骤如下，执行“数据”—“数据工具”—“模拟分析”—“方案管理器”命令，弹出“方案管理器”对话框，如图2．6所示；点击“添加”按钮，弹出“编辑方案”对话框，如图2．7所示；输入方案名称、可变单元格和备注，单击“确定”按钮保存，打开“方案变量值”对话框，如图2．8所示；输入可变单元格的值为100 000．00。单击确定按钮，即添加了初始方案。按照上述步骤依次添加乐观方案和悲观方案。

图2．6

图2．7

图2．8

图2．9

当分别保存了上述三种方案后，再打开方案对话框就可以如图2．9中的情况所示。这时就可以根据需要随时显示不同方案，进行对比；并可对方案进行编辑、保存、删除、合并等操作。点击“摘要”按钮，还可以生成如图2．10所示的汇总报表。

图2．10

②合并方案。

如果在多张工作表中使用了方案，则可以把它们合并。当工作表中所有假设分析模型的结构相同时，合并方案将更加容易。所有源工作表的可变单元格都必须引用相应的当前工作表的可变单元格。Excel将源工作表中的所有方案复制到当前工作表中。