内容
金融领域的流程总是相互关联的——一个因素依赖于另一个因素并随之变化。 跟踪这些变化并了解未来的预期，可能使用 Excel 函数和电子表格方法。
使用数据表获取多个结果
数据表功能是假设分析的要素——通常通过 Microsoft Excel 完成。 这是敏感性分析的第二个名称。
概述
数据表是一种单元格范围，可以通过更改某些单元格中的值来解决问题。 它是在需要跟踪公式组件的更改并根据这些更改接收结果更新时创建的。 让我们了解如何在研究中使用数据表，以及它们是什么类型。
数据表基础知识
有两种类型的数据表，它们的组件数量不同。 您需要编译一个表格，重点是您需要检查的值的数量 uXNUMXbuXNUMXb。
当一个或多个表达式中只有一个变量可以改变其结果时，统计学家使用单个变量表。 例如，它经常与 PMT 功能结合使用。 该公式旨在计算定期付款的金额，并考虑到协议中规定的利率。 在这样的计算中，变量写在一列中，计算结果写在另一列中。 带有 1 个变量的铭牌示例：
接下来，考虑具有 2 个变量的板。 它们用于两个因素影响任何指标变化的情况。 这两个变量可能最终出现在与贷款相关的另一个表中，该表可用于确定最佳还款期和每月还款金额。 在此计算中，您还需要使用 PMT 功能。 具有 2 个变量的表的示例：
创建具有一个变量的数据表
考虑使用只有 100 本书库存的小书店示例的分析方法。 其中一些可以卖得更贵（50美元），其余的将减少买家的成本（20美元）。 计算出售所有商品的总收入——所有者决定以高价出售 60% 的书籍。 你需要弄清楚如果你提高大量商品的价格——70%，等等，收入将如何增加。
请注意！ 总收入必须使用公式计算，否则无法编制数据表。
- 选择一个远离工作表边缘的空闲单元格并在其中写入公式： =总收入的单元格。 例如，如果在单元格 C14 中写入收入（指示随机指定），您需要这样写： =S14。
- 我们在该单元格左侧的列中写下商品数量的百分比——而不是在其下方，这非常重要。
- 我们选择百分比列和总收入链接所在的单元格范围。
- 我们在“数据”选项卡上找到“假设分析”项并单击它——在打开的菜单中，选择“数据表”选项。
- 将打开一个小窗口，您需要在“按...中的行替换值”列中指定一个单元格，其中包含最初以高价出售的书籍的百分比。 完成此步骤是为了重新计算总收入，同时考虑到增加的百分比。
在输入数据用于编译表格的窗口中单击“确定”按钮后，计算结果将显示在行中。
将公式添加到单个变量数据表
从帮助计算只有一个变量的动作的表格中，您可以通过添加额外的公式来制作复杂的分析工具。 它必须在已经存在的公式旁边输入——例如，如果表格是面向行的，我们在现有公式右侧的单元格中输入表达式。 设置列方向后，我们将新公式写在旧公式下。 接下来，按照算法：
- 再次选择单元格区域，但现在它应该包含新公式。
- 打开“what if”分析菜单并选择“Datasheet”。
- 根据板的方向，我们将新公式添加到行或列中的相应字段中。
创建包含两个变量的数据表
这种表格的开头略有不同——您需要在百分比值上方放置一个指向总收入的链接。 接下来，我们执行以下步骤：
- 将价格选项写在与收入链接的一行中——每个价格对应一个单元格。
- 选择一系列单元格。
- 通过工具栏上的“数据”选项卡打开数据表窗口，就像用一个变量编译表时一样。
- 在“Substitute values by columns in ...”列中替换一个初始价格较高的单元格。
- 在“Substitute values by rows in ...”列中添加一个包含昂贵书籍销售初始百分比的单元格，然后单击“OK”。
结果，整个表格充满了不同商品销售条件下可能的收入金额。
加快包含数据表的工作表的计算
如果您需要在不触发重新计算整个工作簿的数据表中进行快速计算，您可以采取一些措施来加快该过程。
- 打开选项窗口，在右侧菜单中选择“公式”项。
- 在“工作簿中的计算”部分中选择“自动，数据表除外”项。
- 让我们手动重新计算表中的结果。 为此，请选择公式并按 F 键。
执行敏感性分析的其他工具
该程序中还有其他工具可帮助您执行敏感性分析。 它们将一些原本必须手动完成的操作自动化。
- “参数选择”功能适用于已知所需结果的情况，并且您需要知道变量的输入值才能获得这样的结果.
- “寻找解决方案”是解决问题的插件。 有必要设置限制并指向它们，然后系统会找到答案。 解决方案是通过更改值来确定的。
- 可以使用场景管理器进行敏感性分析。 此工具位于数据选项卡下的假设分析菜单中。 它替换了多个单元格中的值——数量可以达到 32。调度程序比较这些值，以便用户不必手动更改它们。 使用脚本管理器的示例：
Excel中某投资项目的敏感性分析
假设分析在需要预测的情况下特别有用，例如投资。 分析师使用这种方法来了解公司股票的价值将如何因某些因素的变化而变化。
投资敏感性分析法
在分析“假设”时，使用枚举——手动或自动。 取值范围已知，一一代入公式。 结果是一组值。 从它们中选择适当的数字。 让我们考虑在金融领域对其进行敏感性分析的四个指标：
- 净现值——通过从收入金额中减去投资金额计算得出。
- 内部收益率/利润——表示一年内需要从投资中获得多少利润。
- 回收率是所有利润与初始投资的比率。
- 贴现利润指数——表明投资的有效性。
公式
嵌入灵敏度可以使用以下公式计算： 以 % 为单位的输出参数变化 / 以 % 为单位的输入参数变化。
输出和输入参数可以是前面描述的值。
- 您需要知道标准条件下的结果。
- 我们替换其中一个变量并监控结果的变化。
- 我们计算两个参数相对于既定条件的百分比变化。
- 我们将获得的百分比插入公式并确定灵敏度。
Excel中投资项目敏感性分析示例
为了更好地理解分析方法，需要一个示例。 让我们用以下已知数据分析项目：
- 填表分析一下项目就可以了。
- 我们使用 OFFSET 函数计算现金流。 在初始阶段，流量等于投资。 接下来，我们应用公式： =IF(OFFSET(Number,1;)=2;SUM(流入 1:流出 1);SUM(流入 1:流出 1)+$B$ 5）公式中的单元格名称可能会有所不同，具体取决于表格的布局。 最后，添加来自初始数据的值——残值。
- 我们确定项目将获得回报的期限。 对于初始阶段，我们使用以下公式： =总结（G7：G17;»<0″)。 单元格范围是现金流列。 对于进一步的时期，我们应用这个公式： =初始周期+IF（第一个e.stream>0；第一个e.stream；0）。 该项目在 4 年内处于盈亏平衡点。
- 我们为项目获得回报的那些时期的数量创建一个列。
- 我们计算投资回报率。 有必要用一个特定时期的利润除以初始投资来表示。
- 我们使用以下公式确定折扣因子： =1/(1+光盘.%) ^号码.
- 我们使用乘法计算现值——现金流乘以贴现因子。
- 让我们计算 PI（盈利指数）。 一段时间内的现值除以项目开始时的投资。
- 让我们使用 IRR 函数定义内部收益率： =IRR（现金流范围）。
使用数据表进行投资敏感性分析
对于投资领域的项目分析，其他方法比数据表更适合。 许多用户在编译公式时会感到困惑。 要找出一个因素对其他因素变化的依赖性，您需要选择正确的单元格来输入计算和读取数据。
Excel 中的因子和离散度分析以及计算自动化
敏感性分析的另一种类型是因子分析和方差分析。 第一种类型定义了数字之间的关系，第二种揭示了一个变量对其他变量的依赖性。
Excel 中的方差分析
这种分析的目的是将价值的可变性分为三个部分：
- 由于其他值的影响而导致的可变性。
- 由于影响它的值的关系而发生的变化。
- 随机变化。
让我们通过 Excel 插件“数据分析”进行方差分析。 如果没有启用，可以在设置中启用。
初始表必须遵循两个规则：每个值有一列，其中的数据按升序或降序排列。 有必要检查教育水平对冲突行为的影响。
- 在数据选项卡中找到数据分析工具并打开其窗口。 在列表中，您需要选择单向方差分析。
- 填写对话框的行。 输入间隔是所有单元格，不包括标题和数字。 按列分组。 我们在新工作表上显示结果。
由于黄色单元格中的值大于 XNUMX，因此可以认为该假设不正确——教育与冲突行为之间没有关系。
Excel中的因子分析：一个例子
让我们分析一下销售领域的数据关系——需要识别流行和冷门的产品。 初始信息：
- 我们需要找出第二个月哪些商品的需求增长最多。 我们正在编制一个新表来确定需求的增长和下降。 使用以下公式计算增长： =IF((需求2-需求1)>0；需求2-需求1；0)。 减少公式： =IF（增长=0；需求1-需求2；0）。
- 计算商品需求的增长百分比： =IF（增长/结果 2 =0；减少/结果 2；增长/结果 2）。
- 为了清楚起见，让我们制作一个图表——选择一系列单元格并通过“插入”选项卡创建一个直方图。 在设置中，您需要删除填充，这可以通过格式化数据系列工具来完成。
Excel中方差的双向分析
对几个变量进行方差分析。 举个例子：你需要找出男人和女人对不同音量的声音的反应有多快。
- 我们打开“数据分析”，在列表中你需要找到一个没有重复的双向方差分析。
- 输入间隔 - 包含数据的单元格（没有标题）。 我们在新工作表上显示结果，然后单击“确定”。
F值大于F-critical，这意味着地板会影响对声音的反应速度。
结论
本文详细讨论了Excel电子表格中的敏感性分析，以便每个用户都能了解其应用方法。