如何在Excel 2019中使用假设分析高级功能

文章来源:羽兔大学 发布时间:2020-07-31 浏览量:10


1、目标搜寻工具

Excel的目标搜索工具是最常见的假设分析工具。当您具有数据表并要进行更改时,可以使用目标搜索工具对公式进行更改以查看基于这些计算的结果。

使用成员资格表,显示2月份每天的付款净收入。该计算将显示每个收入日的结果,以便审阅者可以看到每天的实际收入减去付款费用。如果付款费用发生变化怎么办?您正在审核的付款处理商可能有较高的费用,或者可能有较低的费用。这如何改变您的收入?您可以使用目标寻求假设分析来回答这两个方案问题。

Excel 2019具有三个假设分析工具。寻求目标只是其中之一。可以在Excel的“数据”菜单中找到使用Excel的假设功能的工具。单击此菜单选项卡,然后在“预测”部分中找到“假设分析”按钮。

image.png

(Whiat-If分析按钮)

单击“假设分析”按钮,然后显示一个选项下拉列表。这些选项是您可以用于假设分析的三种不同工具。对于此示例,单击“目标搜索”选项。

image.png

(目标配置)

在执行目标搜索之前,必须满足一些要求,然后才能使用该工具。该示例使用数据透视表显示数据,但是目标搜索无法更改数据透视表数据。因此,创建了“付款费用”和“新收入”列。付款费用代表付款的百分比。通过从总付款金额中减去付款费用百分比,新的收入列表示总收入。

使用目标搜索工具时,目标单元格必须是一个公式。这是因为目标搜索工具将获取您的目标值,并临时更改公式中的值以达到目标方案。

在此示例中,业务情景确定了达到特定金额所需要使用的支付费用。当前“新收入”列中第一个单元格的净收入为43.4366。这是包含公式的单元格,该公式通过从总收入中减去费用百分比来计算获得多少净收入。“设置单元格”输入文本框必须包含带有公式的单元格,并且E4列具有公式要求。

“达到价值”输入文本框是您的目标金额。您可以在工作表数据上运行多个目标搜索评估,但是您需要更改值并使用多个目标搜索方案。对于此示例,目标42设置为查看付款费用的最大阈值,以使净收入保持在原始费用金额的42。

包含可以更改的付款费用的单元格为D4。“通过更改单元格”输入文本框是您要更改的值。在此示例中,付款费用将发生变化,以确定将导致净收入为42美元的费用。

输入目标搜索值后,单击“确定”,Excel 2019会更改值以显示结果。

image.png

(目标搜寻结果)

目标搜索将搜索与您的方案结果匹配的值。在此示例中,状态窗口显示一条通知,通知它能够找到适合您要求的值。在电子表格中,Excel会更改结果,以便您可以查看达到目标所需的值。

在此示例中,增加了支付费用以检查支付阈值,该阈值将使总净收入保持为$ 42。结果是,付款费用最多可能会增加6%,您的总净收入不会低于$ 42。

“目标搜索状态”窗口具有“步骤”和“暂停”按钮。对于某些复杂的目标寻求方案,假设分析工具将无法找到与您建议的最终结果相匹配的值。您可以使用“步骤”按钮向目标搜索添加另一个步骤,也可以使用“停止”按钮取消目标搜索过程并更改每个输入选项的值。


2、场景工具

使用目标搜索工具,您可以查看一种情况并输入一个最终结果的值。场景工具使这一步骤更进一步,并使您具有多个值和目标来查找投影的变化。要打开方案配置窗口,请单击“数据”选项卡,然后单击“预测”部分中的“假设分析”按钮。单击下拉菜单中的“方案管理器”选项将打开一个配置窗口,您可以在其中设置方案。

image.png

(“方案管理器”窗口)

由于方案使您可以汇总几个预测,因此在使用方案时,您将拥有更为复杂的配置窗口。单击“添加”以基于电子表格中的数据创建第一个方案。本示例通过从总收入中减去费用来使用来自会员费用,支付费用和净收入的数据。

image.png

(添加方案)

由于方案使您可以查看具有多个值的多个项目,因此可以在“更改单元格”输入文本框中输入多个单元格。您还可以通过使用鼠标并单击工作表中的每个单元格,将单元格添加到此文本框中。

Excel 2019会在“注释”输入文本框中自动创建方案的创建日期。当您有多个要查看的场景时,可以将此文本框保留为空白,也可以添加自己的注释来组织场景。通过在“方案名称”输入文本框中输入一个值来为方案命名。当您将来查看一系列场景时,此名称将帮助您找到所需的场景。

完成方案配置后,单击“确定”进行创建。现在,该输入所选单元格的值了。

image.png

(方案值)

与目标搜索相比,方案的优点是可以更改多个单元格中的值,创建方案后的下一个窗口是在前一个窗口中为选择更改的每个单元格配置值。默认值是已经输入的值。

在上一个窗口中配置的每个单元格都会显示一个相邻的输入文本框,您可以在其中输入新值。您可以添加多个值,仅输入一个,然后保留其他值,或者与您的方案匹配的新值的任何其他组合。在此示例中,将3%的支付费用更改为2%,总收入值为45美元。输入新值后,单击“确定”按钮。

创建方案后,将返回到一个窗口,您可以在其中看到所有已配置方案的列表。

image.png

(方案列表)

在此窗口中,您可以编辑,删除或添加其他方案。您还可以在此窗口中激活方案并查看计算结果。单击要查看的方案(在本例中为“测试”),然后单击窗口底部的“显示”按钮。

在此示例中,单击“显示”将第一行的付款费用更改为2,支付的总金额为45。最终结果是新的收入值。该值更改为稍高的值。如果要创建具有不同值的其他方案或要更改的其他单元格,则可以添加具有各种更改的另一个方案。使用场景时,您并不仅限于一个变量输入参数的一个目标。您可以存储多个方案,以快速查看各种收入或控制成本的方法,从而实现目标。

完成方案值后,单击“关闭”退出窗口。


3、数据表

使用方案和目标搜索工具,可以使用根据设置的变量而变化的数据来设置单元。例如,使用付款和费用支出,您可能需要分配工作表的一部分,以显示特定收入的可能的收入数据点和净收入结果的列表。本示例使用3%的付款费用,然后显示带有最终净收入值的几个数据点。

要创建数据表,首先需要设置数据列。

image.png

(数据表测试数据)

在此示例中,收入值列设置为$ 41- $ 45。每个值的右边是一个空白单元格,其列标题为“ Revenue”。在此列中将显示数据表计算的结果。

要创建数据表,请在“数据”菜单选项卡的“预测”部分中单击“假设分析”按钮。从下拉菜单中选择“数据表”,然后会打开一个配置窗口,您可以在其中设置数据。

image.png

(数据表配置)

设置表格之前,必须在列标签下的第一个单元格中输入公式。在此示例中,“收入”下值为43.65的第一个单元格包含公式= D4-(D4 * E4 / 100),以计算数据表中的第一个值。该公式告诉数据表如何计算其他值。

您还必须选择包含变量值的两列以及将在其右侧显示结果的单元格。确保包括具有公式的单元格,否则无论您输入多少值,数据表都将无法计算结果。

数据表有两个输入文本框,可使用您选择的数据对其进行配置。在“行输入单元格”文本框中,可以指定包含每个公式的静态信息的单元格。由于此示例将针对3%的固定支付费用显示不同的净收入值,因此“行输入单元格”文本框应为带有支付费用的单元格,即单元格E4。但是,此数据表将使用列行文本框。

“列输入单元格”文本框包含原始的第一个值45。“行输入单元格”输入文本框可以保留为空白,因为将不使用它。单击“确定”,Excel将使用存储的公式来配置每一行中的值,并将结果显示在右侧相邻的单元格中。对于每个列输入,都会进行计算,然后结果显示在表中。

image.png

(数据表结果)

当您将配置应用于数据表时,该窗口将关闭,并且您可以在所选列中看到公式化的值。使用数据表,您可以标识计算中使用的值,因为Excel 2019用红色和蓝色突出显示它们。这样,您可以快速确定计算中使用了哪些值,从而可以了解工作表中显示的信息。

数据表与目标搜索和方案工具有些不同,因为您只能使用静态值,然后使用这些静态值从表中指定的输入值列表中计算结果。任何其他格式都将失败,并且Excel 2019会给您一个错误,并且不能让您完成数据表配置。

Excel不允许您简单地重新定义数据表数据。创建它之后,除非决定删除它,否则必须将其保留在工作表中。您可以通过突出显示所有列数据并按“删除”键来清除数据表。删除表后,您可以重新设计新配置并向显示您的信息的新表中添加新配置。

Excel中的假设分析工具可帮助您确定财务和业务的正确方向。当您拥有数据并希望进行更改但不确定如何更改这些数据会导致您的财务预测时,这些工具会很有用。Excel的假设分析工具是强大的功能,当您想知道信息中的数据更改时会发生什么情况时,这些功能很有用。


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持羽兔网。

如对本文有所疑义或者对本文内容提供补充建议,请联系小编 QQ交谈 本站会保留修改者版权
扫描右侧二维码
关注羽兔网

你与百万设计师在一起

微信公众号搜索“ 羽兔网”选择关注

分享设计课程、设计资源,还可下载软件,版本超齐全