使用方案在不同的值集之间切换

方案是 Excel 保存的、可在工作表上自动替换的一组值。 可以创建不同的值组并将其另存为方案,然后在这些方案之间切换以查看不同的结果。

如果多个用户具有想要在方案中使用的特定信息,您可以在单独的工作簿中收集信息,然后将不同工作簿中的方案合并到一个工作簿中。

获得所有所需的方案后,可以创建一个方案摘要报告,其中合并了来自所有方案的信息。

方案通过"数据"选项卡上的"假设分析"组中的方案管理器向导进行管理。

Excel 提供三What-If分析工具:"方案"、"数据表"和"目标查找"。 方案和数据表接受输入值集,并向前预测以确定可能的结果。 "目标寻道"不同于方案和数据表,因为它需要一个结果,而项目需要向后确定可能生成该结果的输入值。

每个方案最多可包含 32 个变量值。 如果要分析超过 32 个值,并且这些值只代表一个或两个变量,可以使用数据表。 尽管数据表只能包含一个或两个变量 (一个变量用于行输入单元格,一个变量用于列输入单元格) ,但数据表可以包含您想要的许多不同变量值。 一个方案最多可以有 32 个不同的值,但可以创建多个方案。

除了这三种工具之外,还可以安装加载项,以帮助执行What-If分析,例如规划求解 加载项。 规划求解加载项与单变量求解类似,但可以包含更多的变量。 还可以使用 Excel 中的填充柄和多种命令创建预测。 对于更高级的模型,可以使用分析工具库加载项

假设你要创建预算,但不确定你的收入。 通过使用方案,你可以为收入定义不同的可能值,然后在方案之间切换以执行假设分析。

例如,假设最差情况的预算方案是 50,000 美元的总成本和 13,200 美元的销售货物成本,留下 $36,800 的"总利润"。 若要将这组值定义为方案,首先在工作表中输入值,如下图所示:

方案使用 "更改" 和 "结果" 单元格设置方案

"更改"单元格包含您键入的值,而结果单元格包含基于"更改单元格"的公式 (单元格 B4 中的公式为=B2-B3) 。

然后,使用 "方案管理器 "对话框将这些值另存为方案。 在"方案管理器 "> What-If">"添加">"数据"选项卡

从数据 > 预测转到方案管理器? 模拟分析

方案管理器向导

在" 方案名称 "对话框中,命名方案最差情况,并指定单元格 B2 和 B3 是方案之间更改的值。 如果在添加方案之前在工作表中选择"更改单元格",则方案管理器会自动插入单元格,否则可以手动键入这些单元格,或使用"更改单元格"对话框右边的单元格选择对话框。

设置最糟糕的情形

注意: 尽管此示例仅包含 B2 和 B3 (两个) ,但一个方案可以包含最多 32 个单元格。

保护 - 还可以保护方案,因此在"保护"部分中,选中想要的选项,如果不需要任何保护,请取消选中这些选项。

  • 选择 "防止 更改"以防止在工作表受保护时编辑方案。

  • 选择 " 隐藏"以防止在工作表受保护时显示方案。

注意: 这些选项仅适用于受保护的工作表。 有关受保护工作表的信息,请参阅" 保护工作表"

现在,假设最佳方案预算方案为 150,000 元的总成本和 26,000 美元的销售货物成本,留下 $124,000 的"总利润"。 若要将这组值定义为方案,请创建另一个方案,将它命名为最佳情况,并针对单元格 B2 (150,000) 和单元格 B3 (26,000) 。 由于 Profit (单元格 B4) 是公式(收入 (B2) 与成本 (B3) 之间的差值)不会更改单元格 B4,这是最佳方案。

在方案之间切换

保存方案后,它会在方案列表中可用,可以在假设分析中使用该方案。 根据上图中的值,如果选择显示最佳方案,工作表中的值将更改为如下图所示:

最佳情形

有时,在一个工作表或工作簿中拥有创建要考虑的所有方案所需的全部信息。 但是,你可能希望从其他源收集方案信息。 例如,假设你尝试创建公司预算。 可以收集不同部门(例如销售、工资、生产、营销和法律)的方案,因为每个源在创建预算时使用不同的信息。

可以使用 Merge 命令将这些方案收集到一个 工作表 中。 每个源可以提供您任何需要更改的单元格值。 例如,你可能希望每个部门提供支出预测,但只需要少数部门的收入预测。

当您选择合并时,方案管理器将加载一个合并方案向导,该向导将列出活动工作簿中的所有工作表,以及列出您当时可能打开的其他任何工作簿。 该向导将告诉你在选择的每个源工作表上拥有的方案数。

"合并方案" 对话框

从各种源收集不同方案时,应在每个工作簿中使用相同的单元格结构。 例如,收入可能始终在单元格 B2 中,而"支出"可能始终在单元格 B3 中。 如果对来自各种源的方案使用不同的结构,则很难合并结果。

提示: 请考虑首先自己创建方案,然后向同事发送包含该方案的工作簿副本。 这样,可以更轻松地确保所有方案的结构都相同。

若要比较多个方案,可以创建一个报表,在同一页上汇总这些方案。 报表可以并排列出方案,或将它们呈现在 数据透视表 。

"方案摘要" 对话框

基于上述两个示例方案的方案摘要报告如下所示:

包含单元格引用的方案摘要

你会注意到 Excel 已自动添加分组级别,这将在单击不同的选择器时展开和折叠视图。

摘要报表末尾将显示一条注释,说明"当前值"列表示创建方案摘要报表时更改的单元格的值,并且每个方案更改的单元格以灰色突出显示。

注意: 

  • 默认情况下,摘要报表使用单元格引用来标识"更改单元格"和"结果"单元格。 如果在运行摘要报表之前为单元格创建了命名区域,则报表将包含名称而不是单元格引用。

  • 方案报告不会自动重新计算。 如果更改方案的值,这些更改不会显示在现有摘要报告中,但是,如果创建新的摘要报表,则会显示这些更改。

  • 不需要结果单元格来生成方案摘要报表,但方案数据透视表需要它们。

具有命名范围的方案摘要

方案数据透视表

返回页首

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

数据表

单变量求解

模拟分析简介

运用“规划求解”定义并求解问题

使用分析工具库执行复杂数据分析

Excel 中的公式概述

如何避免损坏的公式

查找和更正公式中的错误

Excel 中的键盘快捷方式

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×