方案 – Excel22

Excel 显示方案

当假设分析方案创建完毕，即可在“方案管理器”中查看与管理方案。本节介绍如何在工作表中显示各方案所对应的可变单元格的信息。

选中工作表中的任意一个单元格，如B2单元格，切换至“数据”选项卡，单击“预测”组中的“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开如图22-21所示的“方案管理器”对话框。在“方案”列表框中选择要显示的方案，这里选择“常销期”方案，然后单击“显示”按钮，即可在工作表中显示该方案的结果。

图22-21　显示“常销期”方案

Excel 创建方案实例图解

下面通过实例介绍如何创建假设分析的方案。

STEP01：选中工作表中的任意一个单元格，如B2单元格，切换至“数据”选项卡，单击“预测”组中的“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项打开“方案管理器”对话框，如图22-12所示。

STEP02：因为是第1次打开该对话框，在“方案”列表框中会出现“未定义方案。若要增加方案，请选定‘添加’按钮”的提示。单击“添加”按钮打开“编辑方案”对话框，如图22-13所示。

STEP03：打开“编辑方案”对话框后，在“方案名”文本框中输入“促销期”，在“可变单元格”文本框中输入可变单元格的地址，此处按住Ctrl键的同时单击B2、B3、B4单元格，这样可以在“可变单元格”文本框中输入$B$2、$B$3、$B$4，然后单击“确定”按钮，如图22-14所示。

图22-12　选择“方案管理器”选项

图22-13　单击“添加”按钮

“编辑方案”对话框各选项简介如下。

1）方案名：假设分析方案的名称，可以使用任意的名称，但最好能有助于识别方案的内容。

2）可变单元格：在此输入引用单元格的地址，允许输入多个单元格，而且输入单元格不必是相邻的。也可以按住Ctrl键的同时单击要输入的单元格，Excel会自动完成输入。

3）备注：默认会显示创建者的名字以及创建的日期，也可以根据实际情况输入其他内容或修改与删除内容。

4）保护：当工作簿被保护且“保护工作簿”中的“结构”选项被选中时，这两个选项即生效。保护方案可以防止其他人更改此方案。如果选择隐藏方案，则被隐藏的方案不会在“方案管理器”中出现。

图22-14　编辑“促销期”方案

STEP04：随后会打开“方案变量值”对话框，在“请输入每个可变单元格的值”文本框中分别输入每个可变单元格所对应的值，然后单击“添加”按钮返回“编辑方案”对话框，如图22-15所示。

图22-15　设置方案变量值

STEP05：在“方案名”文本框中输入“滞销期”，在“可变单元格”文本框中输入可变单元格的地址，此处按住Ctrl键的同时单击B2、B3、B4单元格，这样可以在“可变单元格”文本框中输入$B$2、$B$3、$B$4，然后单击“确定”按钮，如图22-16所示。

图22-16　设置“滞销期”方案

STEP06：随后会打开“方案变量值”对话框，在“请输入每个可变单元格的值”文本框中分别输入每个可变单元格所对应的值，然后单击“添加”按钮再次返回“编辑方案”对话框，如图22-17所示。

STEP07：在“方案名”文本框中输入“常销期”，在“可变单元格”文本框中输入可变单元格的地址，此处按住Ctrl键的同时单击B2、B3、B4单元格，这样可以在“可变单元格”文本框中输入$B$2、$B$3、$B$4，然后单击“确定”按钮，如图22-18所示。

图22-17　输入可变单元格的值

图22-18　设置“常销期”方案

STEP08：随后会打开“方案变量值”对话框，在“请输入每个可变单元格的值”文本框中分别输入每个可变单元格所对应的值，然后单击“确定”按钮返回“方案管理器”对话框，如图22-19所示。

STEP09：此时，可以在“方案”列表框中看到刚才创建的3个方案，如图22-20所示。如果想返回工作表，单击对话框右下角的“关闭”按钮即可。

图22-19　输入每个可变单元格的值

图22-20　创建的3个方案

Excel 定义方案实例图解

下面通过一个具体案例来说明如何定义一个方案。打开“假设分析方案.xlsx”工作簿，本例中的原始数据如图22-11所示。这是一个简单的图书销售利润统计表，其中顾客折扣、运费和数量对应的数值是根据实际统计情况直接记录在工作表中的。

图22-11　图书销售利润统计表

在本例中，单价、进货折扣为固定值，单本书售价、单本书的利润、每种书的总利润、总利润则使用简单的公式计算得出，其公式分别如下。

单本书售价：从左至右依次为“=B7*B2”，“=C7*B2”，“=D7*B2”，“=E7*B2”。

单本书的利润：从左至右依次为“=B9-B7*(B2-B8-B3)”，“=C9-C7*(B2-C8-B3)”，“=D9-D7*(B2-D8-B3)”，“=E9-E7*(B2-E8-B3)”。

每种书的总利润：从左至右依次为“=B4*B10”，“=B4*C10”，“=B4*D10”，“=B4*E10”。

总利润：“=B11+C11+D11+E11”。

如果希望分析不同的顾客折扣、运费和数量下书籍销售的利润情况，则可以确定不同的方案。例如，可以分为“促销期”“滞销期”“常销期”3个方案，如表22-1所示。

表22-1　3个不同的方案

Excel 2019创建方案摘要

方案创建完成后，还可以建立方案摘要，以便更直观地显示不同变量值时的计算结果。创建方案摘要的具体操作步骤如下。

STEP01：打开“方案摘要.xlsx”工作簿，切换至“Sheet1”工作表，在主页将功能区切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开“方案管理器”对话框，如图7-44所示。

STEP02：在“方案管理器”对话框中单击“摘要”按钮，打开“方案摘要”对话框，如图7-45所示。

图7-44　选择“方案管理器”选项

图7-45　单击“摘要”按钮

STEP03：在“方案摘要”对话框中单击选中“方案摘要”单选按钮，然后设置结果单元格，即想查看具体数据信息的单元格或单元格区域。例如，这里选择显示“销售总额”“总成本”和“净利润”所在的连续单元格“=$B$7:$B$9”，然后单击“确定”按钮，如图7-46所示。此时，工作表中会新建一个“方案摘要”工作表，显示其摘要信息，如图7-47所示。

图7-46　设置结果单元格

在工作表中除了创建普通的方案摘要外，还可以创建数据透视表方案摘要。创建数据透视表方案摘要的具体操作步骤如下。

STEP01：打开“方案摘要.xlsx”工作簿，切换至“Sheet2”工作表，在主页将功能区切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开如图7-48所示的“方案管理器”对话框。然后单击“摘要”按钮打开“方案摘要”对话框。

图7-47　显示“方案摘要”工作表

图7-48　单击“摘要”按钮

STEP02：在“方案摘要”对话框中单击选中“方案数据透视表”单选按钮，然后设置结果单元格，这里选择显示“销售总额”“总成本”和“净利润”所在的连续单元格“=$B$7:$B$9”，然后单击“确定”按钮，如图7-49所示。此时，工作表中新建了一个方案数据透视表，如图7-50所示。

图7-49　选择报表类型

图7-50　新建“方案数据透视表”工作表

Excel 2019合并方案

在“方案管理器”对话框中只显示当前活动工作表中建立的方案，其他工作表中建立的方案不会显示出来。为了便于用户查看方案，可以使用“合并”功能将每张工作表中建立的方案合并到一张工作表中。其具体操作步骤如下。

STEP01：打开“方案管理.xlsx”工作簿，切换至“Sheet1”工作表，在主页将功能区切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开如图7-38所示的“方案管理器”对话框。此对话框中显示的是“Sheet1”工作表中建立的方案。

STEP02：切换至“Sheet2”工作表，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开如图7-39所示的“方案管理器”对话框。此对话框中显示的是“Sheet2”工作表中建立的方案。

图7-38　“Sheet1”工作表中建立的方案

图7-39　“Sheet2”工作表中建立的方案

STEP03：在“Sheet1”工作表中，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开“方案管理器”对话框，在对话框中单击“合并”按钮，如图7-40所示。

STEP04：随后会打开“合并方案”对话框，在工作表列表框中选择要合并其方案所在的工作表，这里选择“Sheet2”工作表，单击“确定”按钮，即可将选中工作表中所有的方案合并到当前活动工作表中，如图7-41所示。合并方案后的效果如图7-42所示。

图7-40　单击“合并”按钮

图7-41　选择合并方案

图7-42　合并方案后的结果

STEP05：如果希望显示“37售价方案”，在方案列表框中将其选中，单击“显示”按钮，即可在当前工作表中显示单位售价为“37”时的数据表，如图7-43所示。

图7-43　在当前工作表中显示方案

Excel 2019编辑方案

在方案建立完成后，有时可能还需要对其进行编辑修改，例如修改方案或删除方案等操作。如果用户要对方案进行编辑，则可以执行以下操作步骤。

STEP01：打开“方案管理.xlsx”工作簿，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开如图7-35所示的“方案管理器”对话框。在“方案管理器”对话框中选择要编辑的方案，这里选择“A产品30000销售方案”，单击“编辑”按钮。

STEP02：随后会打开“编辑方案”对话框，用户可以根据实际需要对方案进行编辑修改，如图7-36所示。

如果有一些不再使用的方案，则可以将其进行删除。打开“方案管理器”对话框后，在“方案”列表框中选择要删除的方案，然后单击“删除”按钮即可，如图7-37所示。

图7-35　单击“编辑”按钮

图7-36　编辑方案

图7-37　单击“删除”按钮

Excel 2019显示方案

通常情况下用户会在同一张工作表中建立多个方案，这时如果用户要查看不同的方案，则可执行以下操作步骤，来实现这一操作。

STEP01：打开“方案管理.xlsx”工作簿，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开“方案管理器”对话框，如图7-33所示。

STEP02：在“方案管理器”对话框中选择要显示的方案，这里选择“A产品30000销售方案”，单击“显示”按钮即可，如图7-34所示。

图7-33　选择“方案管理器”选项

图7-34　选择显示方案

Excel 2019建立方案

方案管理主要是管理多变量情况下的数据变化。例如，在分析销售利润时，不同的销售量都会影响利润的变化，这里将每一种不同的销售量所对应的利润称为一种方案。

STEP01：打开“方案管理.xlsx”工作簿，切换至“Sheet1”工作表。如图7-23所示是A产品预计销量为30000件时的销售方案。

STEP02：切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开“方案管理器”对话框，如图7-24所示。

图7-23　预计销量为30000件时的销售方案

图7-24　选择“方案管理器”选项

STEP03：在“方案管理器”对话框中单击“添加”按钮，如图7-25所示。随后会打开“编辑方案”对话框，在“方案名”文本框中输入方案名，例如这里输入“A产品30000销售方案”，设置“可变单元格”的值，这里选择“$B$6”单元格，然后单击“确定”按钮，如图7-26所示。

图7-25　单击“添加”按钮

图7-26　编辑方案

STEP04：随后会弹出“方案变量值”对话框，在“请输入每个可变单元格的值”文本框中输入B6单元格现在的值“30000”，如图7-27所示。然后单击“确定”按钮返回“方案管理器”对话框，此时，该方案已建立完成，如图7-28所示。

STEP05：单击“关闭”按钮关闭“方案管理器”对话框。在工作表中将B6单元格中的数据更改为45000件，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“方案管理器”选项，打开“方案管理器”对话框。再次单击“添加”按钮，打开“添加方案”对话框，如图7-29所示。