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

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

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

在拥有所有所需方案后,您可以创建方案汇总报表,其中包含所有方案中的信息。

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

Excel 附带三种假设分析工具:方案数据表目标寻道。 方案和数据表获取输入值和项目转发集以确定可能的结果。 目标寻道不同于方案和数据表,因此它会生成结果并向后推项目,以确定生成该结果的可能输入值。

每个方案最多可以容纳32个变量值。 如果要分析超过32的值,并且值仅表示一个或两个变量,则可以使用数据表。 虽然它仅限于一个或两个变量(一个用于行输入单元格,另一个用于列输入单元格),但数据表可以包含任意数量的不同变量值。 一个方案最多可以有32个不同值,但你可以根据需要创建任意数量的方案。

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

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

例如,假设最差的案例预算方案是 $50000 的总收入和 $13200 的销售货物成本,从而使 $36800 保持毛利润。 若要将此值集定义为方案,请首先在工作表中输入值,如下图所示:

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

可变单元格具有你在其中键入的值,而结果单元格包含基于可变单元格的公式(在此插图单元格 B4 中,公式= B2-B3)。

然后,使用方案管理器对话框将这些值保存为方案。 转到 "数据" 选项卡 > "模拟分析" > 方案管理器 > "添加"。

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

方案管理器向导

在 "方案名称" 对话框中,为方案命名为最差的大小写,并指定单元格 B2 和 B3 是在方案之间更改的值。 如果在添加方案之前选择工作表上的可变单元格,方案管理器将自动为您插入单元格,否则可以手动键入单元格,或使用 "可变单元格" 对话框右侧的 "单元格选择" 对话框。

设置最糟糕的情形

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

保护-你也可以保护你的方案,在 "保护" 部分中检查所需的选项,或取消选中这些选项(如果不需要任何保护)。

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

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

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

现在,假设您的最佳案例预算方案是 $150000 的总收入和 $26000 的销售货物成本,从而使 $124000 保持毛利润。 若要将此值集定义为方案,请创建另一个方案,为其命名为最佳事例,并为单元格 B2 (150000)和单元格 B3 (26000)提供不同的值。 由于总利润(单元格 B4)是公式(即收入(B2)和成本(B3)之间的差值),因此不会更改最佳情况下的单元格 B4。

在方案之间切换

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

最佳情形

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

可以使用 "合并" 命令将这些方案收集到一个工作表中。 每个源都可以提供任意数量的单元格值,并根据需要进行更改。 例如,你可能希望每个部门都提供支出预测,但只需几个部门的收入预测。

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

"合并方案" 对话框

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

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

若要比较多个方案,可以创建一个报表,在同一页面上对其进行汇总。 报表可以并排列出方案,也可以将它们呈现在 数据透视表 中。

"方案摘要" 对话框

基于上述两个示例方案的方案摘要报表类似于以下内容:

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

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

将在摘要报告的结尾处显示注释,说明 "当前值" 列表示在创建方案摘要报表时更改单元格的值,以及为每个方案更改的单元格以灰色突出显示。

注意: 

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

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

  • 不需要结果单元格生成方案汇总报表,但需要为方案数据透视表提供这些单元格。

具有命名范围的方案摘要

方案数据透视表

返回页首

需要更多帮助吗?

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

另请参阅

数据表

单变量求解

模拟分析简介

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

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

Excel 中的公式概述

如何避免损坏的公式

查找和更正公式中的错误

Excel 中的键盘快捷方式

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

注意:  本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 能否告知我们此信息是否有所帮助? 下面是该参考内容的英文版

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

此信息是否有帮助?

谢谢您的反馈!

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

×