【Excel】合并计算和模拟分析的应用

【Excel】合并计算和模拟分析的应用

彭怀文

一、合并计算的应用

Excel中的合并计算功能经常被忽视，其实它具备非常强大的合并功能，包括求和、平均值、计数、最大值、最小值等一系列合并计算功能，下面本文就以一个实例来说明Excel中合并计算的使用方法。

案例：企业有3个门店，每天都将销售情况汇报上来了，分别存在一个工作簿中的三张不同的表上，表名分别是"1店"、"2店"、"3店"，每张表上有品名、数量。表内品名可能重复，表与表之间品名可能相同也可能不一致。

问题：将三张表进行汇总。如图：5-4-1

操作步骤：

步骤1：在工作簿中增加增加一张工作表"汇总"，然后用鼠标点击选中单元格A1，然后点击"数据"→"合并计算"，就出现如下对话框。图5-4-2

步骤2：合并计算的设置。设置步骤基本上就是按照下图（图5-4-3）所示的步骤进行。

1：选择"函数"，就是合并的计算方式，包括求和、平均值、计数、最大值、最小值等一系列合并计算功能，根据需要选择。

2：选择"引用位置"，点击右边红色小箭头在本工作簿中选择引用，点击"浏览"则在其他工作簿引用。

3："添加"。在引用位置选择好以后点击"添加"，刚刚选中的引用就到了"所有引用位置"下方的空白处。如此引用错误，点击"删除"去除。

4：勾选"标签位置"。一般来讲，此处的"首行"和"最左列"是需要勾选的。如果是跨工作簿引用建议勾选"创建指向源数据的链接"，本工作簿内引用建议不要勾选。

5：点击"确定"，合并结果就呈现出来了。注意：合并计算的"汇总"表单元格A1是空白的，只有手工输入了。

二、模拟分析的应用

模拟分析是在单元格中更改值以查看这些更改将如何影响工作表中公式结果的过程。

Excel"数据"中的"模拟分析"带了三种模拟分析工具：方案管理器、模拟运算表和单变量求解。方案管理器和模拟运算表可获取一组输入值并确定可能的结果。模拟运算表仅可以处理一个或两个变量，但可以接受这些变量的众多不同的值。一个方案可具有多个变量，但它最多只能容纳 32个值。单变量求解与方案和模拟运算表的工作方式不同，它获取结果并确定生成该结果的可能的输入值。

（一）方案管理器

"方案管理器"是 Excel保存并可以在工作表单元格中自动替换的一组值。可以在工作表中创建和保存不同的组值，然后切换到其中的任一新方案来查看不同的结果。

例如，有一笔对外投资，有三个方案，需要计算并考虑不同折现率对投资净现值的影响。如图：5-4-4

当折现率发生变化时，三个不同方案的净现值都会发生变化，我们现在使用"方案管理器"将其反映并显示到一张表。

步骤1：点击"数据"→"模拟分析"→"方案管理器"，出现如下对话框。图5-4-5

步骤2：点击图5-4-5中的"添加"，出现如图：5-4-6

此处，我们需要进行两处设置。

"方案名"：根据情况进行命名。比如我们是要不同折现率的净现值，此处就以折现率大小进行命名，如10%、8%等。

"可变单元格"的设置：首先需要清楚我们计算中需要变化的单元格。我们案例中需要变化的是折现率，因此可变单元格是B1，通过点击右边红色小箭头更改。

"保护"建议默认设置，然后点击"确定"进行下一步。

步骤3：输入可变单元格的值。在步骤2点击"确定"后出现如图对话框。图3-4-7

5-4-7

本案例中此时只需输入一个希望的折现率就可以了，然后"确定"，出现下图对话框。图5-4-8

5-4-8

步骤4：继续添加"方案"。点击图3-4-8，重复前述的步骤1到步骤3，比如本案例继续输入12%、8%、6%等，最后出现如下图：5-4-9

步骤5：出结果报告。点击5-4-9中的"摘要"，出现如下图：5-4-10

点击"结果单元格"右边红色小箭头，选择结果单元格，最后"确定"，结果如图5-4-11和5-4-12

5-4-11

方案摘要型

5-4-12

数据透视表型

（二）模拟运算表

模拟运算表是进行预测分析的一种工具，它可以显示Excel工作表中一个或多个数据变量的变化对计算结果的影响，求得某一过程中可能发生的数值变化，同时将这一变化列在表中以便于比较。

运算表根据需要观察的数据变量的多少可以分为单变量数据表和多变量数据表两种形式，下面以创建多变量数据表为例来介绍在Excel工作表中使用模拟运算表的方法。本例数据表用于预测不同利率和不同年限所对应的复利终值，创建的是一个有两个变量的模拟运算表。

步骤1：创建一张数据表。如图：5-4-13

在单元格B4输入函数公式"=ROUND(-FV($B$2,$B$3,,$B$1,1),2)"，并将该函数公式复制到单元格B6。

步骤2：将鼠标点中单元格B6并选择区域B6:L12，然后点击"数据"→"模拟运算"→"模拟运算表"，出现下图对话框（图5-4-14）。

此时需要分清行和列单元格。在图5-4-13中行单元格是"年限"，列单元格是"利率"。因此，"输入引用行的单元格"应该是：$B$3；"输入引用列的单元格"应该是：$B$2；然后，"确定"。如图：5-4-15

5-4-15

最终结果，如图5-4-16：

该表是不是就是一张复利终值系数表呢？

（三）单变量求解

如果您知道要从公式获得的结果，但不确信为获得该结果所需的公式输入值，此时，您可以使用单变量求解功能。例如，我们在上一个案例中的复利终值计算。如图：如图5-4-17

5-4-17

复利终值所在单元格B4我们输入了函数公式，结果是根据利率、年限等计算而来。现在的问题是：如果我们希望得到复利终值是150，而其他条件不变的情况下，利率是多少？

此时，我们就可以使用"单变量求解"。

步骤1：鼠标点中单元格B4，然后点击"数据"→"模拟分析"→"单变量求解"，出现下图：5-4-18

5-4-18

步骤2：输入目标值和可变单元格。案例中问题已提出我们的目标值是150，因此在"目标值"处输入150；可变单元格是利率对应的单元格B1,用鼠标点击一下。"单变量求解"就变成如图：5-4-19

点击"确定"，结果就出来了：图5-4-20

利率变成了8.4472%，复利终值变成了150.00。