模拟分析和运算Excel2010的功能和使用目录一．手动模拟运算1二．单变量求解2三．模拟运算表31.单变量模拟运算表32.双变量模拟
运算表3四方案管理器41.建立分析方案42.显示方案63.修改或删除方案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”工作簿的“双变量
模拟运算表”工作表中，分析销售单价和欧元汇率同时变化对月交易额的影响，具体操作步骤如下：步骤1：在D4：D13单元格区域中，输入
不同的销售单价，在E3：J3单元格区域中输入可能的欧元汇率，在单元格E3中公式“=B11”。步骤2：选择要创建模拟运算表的单元格
区域D3：J13，在【数据】|【数据工具】组中，单击“模拟分析”按钮，在弹出的下拉列表中选择“模拟运算表”命令，弹出“模拟运算表”
对话框。步骤3：单击激活“输入引用行的单元格”编辑框，在工作表中单击B6单元格，将自动输入“$B$6”；单击激活“输入引用列的单
元格”编辑框，在工作表中单击B3单元格，将自动输入“$B$3”；单击“确定”按钮，选定区域中自动生成模拟运算表，如图所示。说明：
计算结果展示了在不同的销售单价和欧元汇率下月交易额的变化。此处也可根据需要模拟运算其他结果，如将D11中的公式改为“=B13“，将
会计算在不同的销售单价和欧元汇率下的年交易额。四方案管理器模拟运算表无法容纳两个以上的变量，如果要同时考虑更多的因素来进行分析
，可以使用方案管理器。1.建立分析方案在素材文件夹/第3章中的“模拟运算.xlsx”工作簿的“方案管理器”工作表中，可以为销售
单价、交易数量、欧元汇率等因素设置不同值的组合。例如要试算多种目标下的交易额情况，如最好状态、平均状态、最差状态3种，可以定义3个
方案与之对应，每个方案中都为这些因素设定不同的值，具体操作步骤如下：步骤1：选择可变单元格区域B3:B6，在【数据】|【数据工具
】组中，单击“模拟分析”按钮，在弹出的下拉列表中选择“方案管理器”命令，弹出“方案管理器”对话框，如图所示。步骤4：单击右上方的
“添加”按钮，弹出“添加方案”对话框。在“方案名”文本框中输入方案名称“最好状态”，在“可变单元格”框中可重新指定变量单元格，如图
所示。步骤5：单击“确定”按钮，继续弹出“方案变量值”对话框，依次输入最好情况下的变量值，如图所示。步骤6：单击“确定”按钮，
返回到“方案管理器”对话框。步骤7：重复步骤4～6，继续添加平均状态、最差状态下的方案，添加完成如图所示。步骤8：操作过程中引
用的可变单元格区域始终保持不变，所有方案添加完毕后，单击“方案管理器”对话框中的“关闭”按钮。2.显示方案分析方案制定好后，任
何时候都可以执行方案，以查看不同的执行结果，具体操作步骤如下：步骤1：在【数据】|【数据工具】组中，单击“模拟分析”按钮，在弹出
的下拉列表中选择“方案管理器”命令，弹出“方案管理器”对话框。步骤2：在“方案”列表框中选中一个方案后，单击下方的“显示”按钮，
或者直接双击某个方案，Excel将用该方案中设定的变量值替换掉工作表中相应单元格原来的值，同时公式中显示方案执行结果。3.修改或
删除方案打开“方案管理器”对话框，在“方案”列表中选择要修改的方案，单击“编辑”按钮，在随后弹出的对话框中可修改名称、变量值等。
单击“删除”按钮，可以删除方案。4.生成方案报告如果每次查看一个方案所生成的结果，显示不便于对比分析，Excel的方案功能允许
用户生成报告，将所有方案的执行结果都显示出来并进行比较，具体操作步骤如下：步骤1：在“方案管理器”工作表中，单击【数据】|【数据
工具】组中的“模拟分析”按钮，从下拉列表中选择“方案管理器”命令，弹出“方案管理器”对话框。步骤2：单击右侧的“摘要”按钮，弹出
“方案摘要”对话框，如图所示。步骤3：在该对话框中选择报表类型，其中“方案摘要”是以大纲形式展示报告，“方案数据透视表”是数据透视表形式的报告。步骤4：在结果单元格中指定方案中的计算结果，即用户希望进行分析对比的数据单元格。此处Excel根据计算模型自动推荐结果单元格为B12和B13,用户也可以自己修改。步骤5：单击“确定”按钮，将会在当前工作表之前自动插入“方案摘要”工作表，其中显示各种方案的计算结果，可以立即比较各方案的优劣。 |
|
