分类：数据计算

前面讲过的模拟运算得到的运算结果都是以数组形式保存在单元格中的，例如7.1.2节讲的单变量模拟运算的结果保存为类似“{=TABLE(,B4)}”这样的形式，这表示变量在列中，如果出现“{=TABLE(B4,)}”这样的形式，则表示变量在行中。而7.1.3节讲的双变量模拟运算的结果保存为类似“{=TABLE(B3,B4)}”这样的形式。无论是单变量模拟运算还是双变量模拟运算返回的单元格区域中，都不允许随意改变单个单元格的值，如果对其进行更改，则会弹出如图7-19所示的提示框。

图7-19　提示框

如果用户想要对其部分进行编辑修改操作，则需要将模拟运算结果转换为常量，然后才能对其进行修改。

将模拟运算结果转换为常量的方法通常有以下两种。

方法一：使用快捷键

选中包含模拟运算结果的单元格区域（也可以只选中显示模拟运算结果的单元格区域），按“Ctrl+C”组合键进行复制，然后在目标位置按“Ctrl+V”组合键进行粘贴即可。

方法二：使用快捷菜单命令

STEP01：打开“双变量数据表运算.xlsx”工作簿，选中包含模拟运算结果的单元格区域（也可以只选中显示模拟运算结果的单元格区域），这里选择B10:F14单元格区域，在选中的单元格区域处单击鼠标右键，在弹出的隐藏菜单中选择“复制”选项，如图7-20所示。

STEP02：在B16单元格处单击鼠标右键，在弹出的隐藏菜单中选择粘贴“值”选项，如图7-21所示。粘贴后的效果如图7-22所示。此时，可以对B16:F20单元格区域中的任意单元格进行编辑修改操作。

图7-20　选择“复制”选项

图7-21　选择粘贴选项

图7-22　常量转换效果

在双变量数据表运算中，为两个变量输入不同的值来查看它对一个公式值的影响变化。例如，在销售利润计算中，当销售量和单位售价都发生变化时，所对应的利润额随之发生变化。

STEP01：打开“双变量数据表运算.xlsx”工作簿，在工作表中输入总成本、单位售价、预计销售量的实际数据，然后在B5单元格中输入公式“=B3*B4”，按“Enter”键返回即可计算出销售总收入。根据“纯利润=销售总收入-总成本”的公式，在B6单元格中输入公式“=B5-B2”，按“Enter”键返回即可计算出预计销售量为20000时的纯利润，如图7-13所示。

STEP02：选中B8:F8单元格区域，切换至“开始”选项卡，单击“对齐方式”组中的“合并后居中”按钮，然后在A8和B8单元格中分别输入文本“实际销售量”和“单位售价”，如图7-14所示。

图7-13　计算总收入和纯利润

图7-14　输入文本

STEP03：分别在A10:A14单元格区域与B9:F9单元格区域中输入实际销售量和单位售价，并在A9单元格中输入公式“=B5-B2”，按“Enter”键即可返回计算结果，如图7-15所示。

STEP04：选中A9:F14单元格区域，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“模拟运算表”选项，打开“模拟运算表”对话框，如图7-16所示。

图7-15　不同销售量和单位售价表

图7-16　选中“模拟运算表”选项

STEP05：在“输入引用行的单元格”对应的文本框中输入单元格的引用地址为“$B$3”单元格，表示不同的单位售价，然后在“输入引用列的单元格”文本框中输入单元格的引用地址为“$B$4”单元格，如图7-17所示。然后单击“确定”按钮即可返回计算结果。此时，工作表中的计算结果如图7-18所示。

图7-17　设置引用单元格

图7-18　双变量数据表运算结果

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

STEP01：打开“不同销售量纯利润计算表.xlsx”工作簿，在工作表中输入总成本、单位售价、预计销售量的实际数据，然后在B5单元格中输入公式“=B3*B4”，按“Enter”键返回即可计算出销售总收入。根据“纯利润=销售总收入-总成本”的公式，在B6单元格中输入公式“=B5-B2”，按“Enter”键返回即可计算出预计销售量为20000时的纯利润，如图7-8所示。

STEP02：在A8单元格和B8单元格中分别输入文本“实际销售量”和“纯利润”，在A10:A13单元格区域中分别输入如图7-9所示的销售量，并在单元格B9中输入公式“=B5-B2”，按“Enter”键返回计算结果即可。

STEP03：选择A9:B13单元格区域，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“模拟运算表”选项，打开“模拟运算表”对话框，如图7-10所示。

STEP04：在“输入引用列的单元格”文本框中输入单元格的引用地址为“$B$4”单元格，表示不同的销售量，然后单击“确定”按钮即可返回计算结果，如图7-11所示。此时的工作表如图7-12所示。

图7-8　计算总收入和纯利润

图7-9　输入销售量和公式

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

图7-11　设置引用列的单元格

图7-12　模拟运算表求解结果

在Excel 2019中，单变量求解是提供的目标值，将引用单元格的值不断调整，直到达到所需要的公式的目标值时，变量值才能确定。利用单变量求解功能，可以求解二元一次方程，例如方程式为A=10-B，B=5+A。

STEP01：打开“求解方程式.xlsx”工作簿，在工作表中输入方程式，如图7-1所示。

STEP02：在A6单元格中输入公式“=10-B6”，按“Enter”键返回计算结果，如图7-2所示。

图7-1　输入方程式

图7-2　输入公式并得到A的值

STEP03：在B7单元格中输入公式“=B6-A6”，按“Enter”键返回计算结果，如图7-3所示。

STEP04：切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“单变量求解”选项，打开“单变量求解”对话框，如图7-4所示。

图7-3　输入公式并得到B的值

图7-4　选择“单变量求解”选项

STEP05：在“单变量求解”对话框中设置目标单元格为“$B$7”，设置目标值为“6”，设置可变单元格为“$B$6”，然后单击“确定”按钮，如图7-5所示。

STEP06：随后会弹出如图7-6所示的“单变量求解状态”对话框，再次单击“确定”按钮返回工作表即可，此时可以求得B的值，如图7-7所示。

图7-5　单变量求解对话框

图7-6　单变量求解状态对话框

图7-7　求解结果