《Excel会计信息处理》_会计信息处理的专家指南_7.1 数据模拟分析

【摘要】 本书摘自《Excel会计信息处理》一书中第7章，第1节，来自电子科技大学出版社。

第 7 章 数据决策分析

本章主要以财务数据为例，介绍 Excel 2016 为数据决策和分析提供的主要工具，包括加 载项、模拟运算表、单变量求解、规划求解、方案分析以及部分数据统计和分析工具等。

7.1 数据模拟分析

数据模拟分析，也称数据敏感度分析（what-if 分析）或假设分析，是数据分析中常用的 一种方法， 其主要用于观察当某一条件参数发生变化时， 与之相关的中间变量的相应变化以及 由中间变量变化引起的终极变量发生的变化。 Excel 模拟运算表能够实现数据敏感度分析。

所谓模拟运算表实际上是工作表中的一个单元格区域， 它可以显示一个计算公式中某些参 数的值的变化对计算结果的影响。由于它可以将所有不同的计算结果以列表方式同时显示出 来， 因而便于查看、比较和分析。在编制计划时， 先利用模拟运算表试算非常有用。在 Excel 中， 数据敏感度分析可以是基于一个变量， 也可以是两个变量， 如果加上窗体工具， 还可以是 多个变量，即单变量模拟、双变量模拟和多变量模拟。

7.1.1 单变量模拟运算

单变量模拟即单因素敏感性分析， 在假定其他因素不变的情况下， 只模拟某一参数变化对 最终结果的影响。在单变量模拟运算中， 输入的数据被安排在一行或一列中。同时， 单变量模 拟中使用的公式必须引用“输入单元格”。所谓“输入单元格”，就是被替换的含有公式计算结 果的数据单元格。要使用模拟运算表，需要具备以下元素。

①运算公式： 从已知参数得出最终结果所要的计算公式， 其作用在于告知 Excel 如何从参 数中得到用户希望了解的结果。如 Step 1 中 D2 单元格所示的公式， 其中所引用的 D2 单元格， 就是直接影响运算结果的变量参数。

②变量： 模拟运算中所需要调整变化之参数， 是【模拟运算表】/【输入引用行的单元格】 或【输入引用列的单元格】所指向的单元格， 且须包含于“运算公式”之中。如上例中所指向 的单元格 C2。

③工作区域： 包括变量参数取值的存放位置、运算公式的存放位置以及运算结果的存放位 置。其中变量参数取值的存放位置默认位于模拟运算工作区域的首行（【输入引用行的单元格】 参数引用行） 或首列（【输入引用列的单元格】参数引用列）。如是双变量模拟运算表， 运算公 式则必须位于工作区域左上角单元格。

7.1.2 双变量模拟运算

如果需要对两个变量同时影响运算结果的计算模型进行分析，就可以使用双变量模拟运算表。

【例 7-2】 图 7-3 假定某公司在贷款决策中， 主要考虑的是每月还款额能力，以选择合适的贷款方 案。而决定每月还款能力的因素主要是贷款额度和贷 款利率。假定希望的贷款额度在 20 万元～70 万元变

动、贷款期限则在 10～20 年的范围内变动时所对应的月供数据，并用条件格式标识可供选择的方 案（假定公司月还款能力在 5000～5500 元），则其分析过程如下。

Step 1：在 A6 单元格中输入 200 000，A7 单元格输入 250 000→ 同时选中 A6:A7 单元格向 下拖曳填充至 A16 单元格，生成一组 20 万到 70 万以 5 万为间隔的纵向等差数列。

Step 2：在 B5 单元格中输入 10，向右拖曳至 L5 单元格，生成一组 10～20 的以间隔为 1 的横向等差序列。

Step 3：在 D2 单元格中输入公式“=PMT(C2/12,B2*12,-A2) ”→在 A5 单元格中输入“=D2” →选中 A5:L16 单元格区域→ 【数据】/【模拟分析】/【模拟运算表】→打开模拟运算表对话框。

Step 4：在【输入引用行的单元格】编辑栏中输入变量所在单元格地址“$B$2 ”，在【输 入引用列的单元格】编辑栏中输入变量所在单元格地址“$A$2”→单击【确定】按钮完成操 作，整个操作过程如图 7-4 所示。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。