在Excel2016中常用的数据分析工具

一、模拟运算表概述

模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。运算表根据需要观察的数据变量多少，可以分为单变量数据表和多变量数据表两种形式。单变量数据表中，用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。多变量数据表中，用户对多个变量输入不同值，而查看它对一个公式的影响。

二、单变量数据表运算

在单变量数据运算表中，可以对一个单变量输入不同的值来查看它对一个或多个公式的影响。例如根据产品的不同销售量计算产品所取得的纯利润。具体操作如下。

步骤1：在工作表中输入总成本、单位售价、预计销售量、销售总收入等数据，在单元格B5中输入公式“=B3*B4”，按回车键，然后在单元格B6中输入公式“=B5-B2”，按回车键，计算出预计销售量为20000时的纯利润，如图1所示。

图1　目标数据

步骤2：在单元格A8和B8中分别输入“实际销售量”和“纯利润”。在单元格区域A10:A13中分别输入如图2所示的销售量，并在单元格B9中输入公式“=B5-B2”，按回车键即可。

图2  输入销售量和公式

步骤3：选择单元格区域A9:B13，选择“数据”选项卡“数据工具”单元组中的“模拟分析”按钮，从弹出的菜单中单击“模拟运算表”选项，如图3所示。

图3　单击“模拟运算表”选项

步骤4：弹出“模拟运算表”对话框，在“输入引用列的单元格”中输入单元格的引用地址“$B$4”，表示不同的销售量，单击“确定”按钮，如图4所示。

图4 “模拟运算表”对话框

步骤5：查看利润运算结果，如图5所示。

图5　完成运算

三、双变量数据表运算

在双变量数据表运算中，在为两个变量输入不同的值时查看它对一个公式值的影响变化。下面以创建多变量数据表为例介绍在工作表中使用模拟运算表的方法，本例数据表用于预测不同销售金额和不同提成比率所对应的提成金额，创建的是一个有两个变量的模拟运算表。

步骤1：创建一个新的工作表，在工作表中输入数据。在工作表的B9单元格中输入提成额的计算公式“=$B$2*$B$3”，如图6所示。

图6　创建工作表并输入公式

步骤2：选中创建运算表的单元格区域，在“数据”选项卡的“数据工具”组中单击“模拟分析”按钮，在下拉列表中选择“模拟运算表”选项，如图7所示。

图7　选择“模拟运算表”选项

步骤3：打开“模拟运算表”对话框，在“输入引用行的单元格”文本框中单击销售金额值所在单元格生成地址“$B$2”，在“输入引用列的单元格”文本框中单击提成比率值所在单元格生成地址“$B$3”;完成单元格的引用后，单击“确定”按钮关闭对话框，如图8所示。

图8　指定引用单元格

步骤4：工作表中插入了数据表，通过该数据表将能查看不同的销售金额和不同提成比率下对应的提成金额，如图9所示。

图9　创建模拟运算表

技巧点拨

模拟运算表中的数据是存放在数组中的，表中的单个或部分数据是无法删除的。要想删除数据表中的数据，只能选择所有数据后再按Delete键。