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

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

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

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

当你拥有所需的所有方案后, 你可以创建一个方案摘要报表, 其中包含所有方案中的信息。

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

Excel 附带的假设分析工具有三种:方案数据表目标 Seek。方案和数据表获取输入值和项目转发集以确定可能的结果。目标寻道与方案和数据表的不同之处在于, 它会产生结果和项目来确定生成该结果的可能输入值。

每个方案最多可以容纳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 for Windows 中的键盘快捷方式

Excel for Mac 中的键盘快捷方式

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

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

此信息是否有帮助?

谢谢您的反馈!

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

×