Excel模拟分析和运算(Excel 2010的功能和使用).pdfVIP

模拟分析和运算

Excel2010的功能和使用

目录

一．手动模拟运算1

二．单变量求解2

三．模拟运算表3

1.单变量模拟运算表3

2.双变量模拟运算表3

四方案管理器4

1.建立分析方案4

2.显示方案6

3.修改或删除方案6

模拟分析是在单元格中更改值以查看这些更改将如何影响工作表中公式结果的过程。在

Excel中使用模拟分析工具，可以在一个或多个公式中试用几组不同的数据来分析所有不同

的结果。

例如在个人住房贷款时，可以用模拟分析根据不同的贷款年利率和贷款偿还年份，计算出每

月的贷款偿还额。

Excel附带了三种模拟分析工具:单变量求解、模拟运算表和方案管理器。单变量求解是根据

希望获取的结果来确定生成该结果的可能的输入值。模拟运算表和方案管理器可获取一组输

入值并确定可能的结果。

一．手动模拟运算

在不用Excel工具进行模拟分析时，可以手动进行模拟运算。打开素材文件夹/第3章中的“模

拟运算.xlsx”工作簿，在“手动模拟运算”工作表中展示了某公司产品交易情况的试算表格，如

图所示。

表格的上半部分是交易中各相关指数的数值，下半部分则根据这些数值用公式统计出的交易

数量与交易额。

在这个试算模型中，单价、每次交易数量、每月交易次数和欧元汇率都直接影响着月交易额。

相关的模拟分析需求可能如下：

1.如果单价增加1元会增加多少交易额？

2.如果每次交易数量提高100会增加多少交易额？

3.如果欧元汇率上涨会怎么样？

面对这些分析需求，最简单的处理方法是直接将假设的值填入表格上半部分的单元格里，然

后利用公式自动重算的特性，观察表格下半年部分的结果变化。

二．单变量求解

打开素材文件夹/第3章中的“模拟运算.xlsx”工作簿，在“单变量求解”工作表中展示了某公司

产品交易情况的试算表格，其中销售单价、交易数量、交易次数和欧元汇率都会直接影响年

交易额，如果希望根据某个年交易额快速倒推，计算出销售单价、交易数量、交易次数和欧

元汇率的具体状况，具体操作步骤如下：

步骤1：选择年交易额所在的单元格B13，在【数据】|【数据工具】组中，单击【模拟分析】

按钮，在弹出的下拉列表中选择“单变量求解”命令，弹出“单变量求解”对话框。

步骤2：在“目标单元格”中显示目标值的单元格地址，此处为B13单元格。在“目标值”输入

希望达成的交易额，此处输入350000。单击激活“可变单元格”编辑框，在工作表中单击B4

单元格。结果如图所示。

步骤3：单击“确定”按钮，弹出“单变量求解状态”对话框，对单元格B13进行单变量求解求

得一个解，同时工作表中的“交易数量”和“年交易额”值发生了变化。单击“确定”按钮，接受

计算结果，如图所示。

说明：计算结果表明，在其他条件不变的情况下，要使交易额达到35万，可以提高销售单

价到230以上。

步骤4：重复步骤1～3，可以重新测试销售单价、交易次数等。

三．模拟运算表

模拟运算表实际上是一个单元格区域，它可以用列表的形式显示计算模型中某些参数的变化

对公式计算结果的影响。在这个区域中，生成的值所需要的若干个相同公式被简化成一个公

式，从而简化了公式的输入。根据模拟运算行、列个数的不同，可分为单变量模拟运算表和

双变量模拟运算表两种类型。

1.单变量模拟运算表

在素材文件夹/第3章中的“模拟运算.xlsx”工作簿的“单变量模拟运算表”工作表中，借助模拟

运算表分析欧元汇率变化对月交易额的影响，具体操作步骤如下：

步骤1：在D4：D13单元格区域中，输入可能的欧元汇率，在单元格E3中公式“=B11”。

步骤2：选择要创建模拟运算表的单元格区域D3：E13，在【数据】|【数据工具】组中，单

击“模拟分析”按钮，在弹出的下拉列表中选择“模拟运算表”命令，弹出“模拟运算表”对话框。

步骤3：单击激活“输入引用列的单元格”编辑框，在工作表中单击B6单元格，将自动输入“$B$6”，

单击“确定”按钮，选定区域中自动生成模拟运算表，如图所示。

说明：计算结果展示了在不同的欧元汇率下月交易额的变化。如果模拟运算表变量值输入在

一行中，应在“输入引用行的单元格”框中选择变量值所在的位置。

2.双变量模拟运算表

双变量模拟运算可以帮助用户分析两个因素对最终结果的影响。在素材文件夹/第3章中的

“模拟运算.xlsx”工作簿的“双变量模拟运算表”工作表中，分析销售单价和欧元汇率同时变化

对月交易额的影响，具体操作步骤如