极客笔记什么是Excel中的假设分析
在Excel中，假设分析是一种改变单元格值的过程，以查看这些改变将如何影响工作表的结果。您可以使用多组不同的值来探索一个或多个公式的所有不同结果。
几乎每个数据分析师，特别是中高级管理专业人士都使用假设分析，根据数据做出更好、更快和更准确的决策。假设分析在许多情况下都很有用，例如：
- 基于收入，您可以提出不同的预算。
- 根据给定的历史值，您可以预测未来的值。
- 如果您期望由一个公式产生某个确定的值，您可以找到产生所需结果的不同一组输入值。
要启用假设分析工具，请转到 数据 菜单选项卡，并在预测部分下选择假设分析选项。
现在点击 什么如果分析 Excel 有以下基于数据分析需求可用的什么如果分析工具：
- 方案管理器
- 目标搜索
- 数据表
数据表和场景可以将一组输入值向前推进，以确定可能的结果。目标寻求与数据表和场景不同之处在于，它以结果为基础，向后推进以确定能够产生该结果的可能输入值。
1. 场景管理器
场景是Excel保存的一组值，可以自动替换工作表中的单元格。以下是以下关键特点：
- 您可以在工作表上创建和保存不同的值组，并切换到任何这些新场景以查看不同的结果。
- 一个场景可以有多个变量，但只能容纳最多32个值。
- 您还可以创建场景摘要报告，将所有场景合并在一个工作表上。例如，您可以创建几个不同的预算场景，比较各种可能的收入水平和支出，然后创建一个报告，让您能够并排比较这些情景。
- 场景管理器是一个对话框，允许您将值保存为场景并命名场景。
2. 目标寻求
如果您想要知道公式的结果，但不确定公式需要哪个输入值才能得出该结果，目标寻找非常有用。例如，如果您想要借款，并知道贷款金额、贷款期限和您能够支付的等额分期付款额，您可以使用目标寻求来找到您可以获得贷款的利率。
目标寻求只能用于一个可变的输入值。如果您有多个用于输入值的变量，可以使用求解器插件。
3. 数据表
数据表是一系列单元格的范围，您可以更改其中某些单元格的值，并回答问题的不同答案。例如，您可能想知道根据不同的贷款金额和利率，您能负担得起多少房屋贷款。您可以将这些不同的值和 PMT 函数放入数据表中，获得所需的结果。
数据表仅适用于一个或两个变量，但它可以接受许多不同的变量值。
场景管理器的假设分析
场景管理器是Excel中的一种假设分析工具。场景管理器适用于在敏感性分析中有两个以上变量的情况下。场景管理器为所考虑的变量的每组输入值创建场景。场景可以帮助您探索一组可能的结果，支持以下功能：
- 变幻多达32个输入集。
- 合并来自多个不同工作表或工作簿的场景。
如果您想要分析超过32个输入集，并且这些值仅代表一个或两个变量，您可以使用数据表。
场景的初始值
在创建几个不同的场景之前，您需要定义一组初始值，这些值将是场景的基础。以一家公司计划购买所需金属为例，由于资金短缺，该公司希望了解不同购买可能性将产生多少成本。
在这些情况下，我们可以使用情景管理器来应用不同的情景，以了解结果并作出相应决策。现在以下是为情景设置初始值的步骤：
步骤1： 定义包含输入值的单元格。
步骤2： 将单元格命名为 Metals_name 和 Cost 。
步骤3： 定义包含结果的单元格。
步骤4： 将结果单元格命名为 Total_cost 。
步骤5： 在结果单元格中插入公式。
步骤6： 下面是创建的表格。
使用场景管理器创建分析报告，请按照以下步骤进行：
步骤1: 点击 Data 选项卡。
步骤2: 转到 What-If Analysis 按钮，然后从下拉列表中选择 Scenario Manager 。
第三步： 现在出现一个场景管理器对话框，请点击 添加 按钮创建一个场景。
步骤 4： 创建场景，命名场景，为每个变动输入单元格输入值，并点击 Ok 按钮。
步骤5 ：现在，B3、B4、B5、B6和B7出现在单元格框中。
步骤 6： 现在，将 B3 的值更改为 500，然后点击 添加 按钮。
第7步： 点击添加按钮后，将再次出现添加方案对话框。
- 在方案名称框中，创建方案2。
- 选择阻止更改。
- 然后点击 确定 。
步骤 8： 再次出现带有 B3 单元格变更值的场景值框。
第9步： 将B5的值更改为20000，并点击 确定 按钮。
第10步： 同样地，创建场景3并点击 确定 按钮。
第11步： 再次出现了一个带有B5单元格值更改的方案值框。
第12步： 将B7的值更改为10000，然后点击 确定 按钮。
方案管理器 对话框出现。在”方案”下的框中，您会找到您创建的所有方案的名称。
第13步： 现在，点击 汇总 按钮。出现“情景汇总”对话框。
Excel提供两种类型的情境摘要报告：
- 情境摘要。
- 情境数据透视表报告。
步骤14： 在报告类型下选择 情境摘要 并点击 确定 。情境摘要报告将出现在新的工作表上。你将得到以下情境摘要报告。
您可以在场景摘要报告中观察到以下内容：
- 更改单元格： 列出了所有用作更改单元格的单元格。
- 结果单元格： 显示指定的结果单元格。
- 当前值： 这是第一列，它列出了在创建摘要报告之前在场景管理器对话框中选择的场景的值。
- 对于您创建的所有场景，将以灰色突出显示更改单元格。
- 在C9行中，将显示每个场景的结果值。
目标搜索
目标搜索是一种什么如果分析工具，它可以帮助您找到产生所需目标值的输入值。
目标搜索 需要使用输入值并以目标值给出结果的公式。然后，通过改变公式的输入值，目标搜索试图解决输入值。
目标搜索仅适用于一个变量输入值。如果有多个待定的输入值，您必须使用求解器加载项。以下是在Excel中使用目标搜索功能的步骤。
步骤1： 在 数据 选项卡上，找到 什么如果分析 ，并点击 目标搜索 选项。
第二步： 目标寻找 ** 对话框出现。
步骤3： 在 设置单元格 框中输入C9。此框是包含你想要解决的公式的单元格的引用。
步骤4： 在 目标值 框中输入57000。你会获得该公式的结果。
步骤5： 在 更改单元格 框中输入B9。此框包含你想要调整的值的单元格的引用。
步骤6： 此公式必须参照目标单元格，并在 设置单元格 框中指定的单元格中进行Goal Seek更改。点击 确定 。
第7步： 目标求解框产生下列结果。
正如您所看到的，Goal Seek使用B9找到了解决方案，并且在B9单元格中返回0，因为目标值和当前值相同。
数据表的假设分析
通过Excel中的数据表，您可以轻松地改变一个或两个输入并进行假设分析。数据表是一系列单元格，您可以在其中更改某些单元格中的值，并对问题回答不同的答案。数据表有两种类型，分别是：
- 单变量数据表
- 双变量数据表
如果您的分析问题涉及超过两个变量，您需要使用Excel的情景管理器工具。
单变量数据表
单变量数据表可用于查看一个或多个公式中一个变量的不同值如何改变这些公式的结果。换句话说，通过单变量数据表，您可以确定更改一个输入如何改变任意数量的输出。以下是创建单变量数据表的示例。
一个很好的数据表示例是使用不同的贷款金额和利率来计算贷款的 PMT 函数。
有一笔贷款为1 00,000，期限为5年。您想知道不同利率下的月付款（EMI）。您还想知道第二年支付的利息和本金金额。
步骤1： 创建所需的表格。
- 假设利率为10%。
- 列出所有所需值。
- 对包含值的单元格进行命名。
- 使用Excel的PMT、CUMIPMT和CUMPRINC函数设置EMI、累计利息和累计本金的计算。
- 下面是创建的表格。
第二步： 在输入单元格中输入要替换的利率值列表。
正如您所观察到的，利率值上方有一行空行。这一行是用来填写公式的。
步骤3： 在值所在列的上方一行、右侧一个单元格中输入第一个函数（ PMT ）。在第一个函数的右侧单元格中输入其他函数（ CUMIPMT 和 CUMPRINC ）。
第四步： 数据表如下所示。
步骤5： 选择包含您要替换的公式和值的单元格范围，即E2:H13。
第六步: 前往 数据 选项卡，选择 What-if Analysis 并在下拉列表中点击 数据表 工具。
第七步: 数据表对话框出现。
- 点击“列输入单元格”框。
- 并点击利率单元格，即C2。
你可以看到列输入单元格被设置为C2。
步骤8： 点击 确定 按钮。
数据表中填充了每个输入值的计算结果。
双变量数据表
双变量数据表可用于查看公式中两个变量的不同值将如何改变该公式的结果。换句话说，通过双变量数据表，您可以确定更改两个输入如何改变单个输出。
例如，贷款金额为100000，您想知道不同利率组合将如何影响每月付款。
步骤1： 创建以下表格。
第2步： 现在创建数据表
- 在F2单元格中写入 =EMI。
- 将第一组输入值（利率）一列一列地输入到F列中，从公式下方的单元格F3开始。
- 将第二组输入值（还款期数）一行一行地输入到公式右侧的单元格G2开始。
- 数据表如下所示。
步骤3： 选择包含公式和您想要替换的两组数值的单元格范围，即F2:L13。
步骤 4: 进入 Data 选项卡，点击 What-if Analysis ，然后从下拉列表中选择 Data Table 。
步骤 5: 数据表对话框出现。
第六步： 点击行输入单元格。
- 点击 NPER 单元格，即C3。
- 再次点击列输入单元格。
- 点击利率单元格，即C2。
你会看到行输入单元格被视为C3，列输入单元格被视为C2。
步骤7： 点击 确定 按钮。
数据表将填充计算结果，针对两个输入值的每个组合。
数据表计算
数据表每次重新计算工作表时都会重新计算，即使它们没有变化。
为了加快包含数据表的工作表的计算，您需要将计算选项更改为 自动重新计算 工作表，而不重新计算数据表。