Excel数据分析——数据表（模拟运算表）

renqiang851224

数据表的运算在Excel 2007版本以前，被称为“模拟运算表”的功能，现在称之为“数据表”。读者可以声速创建公式，并将要代入公式里运算数据组成行、列式表格，然后通过这项功能交叉运算出各种结果。这项功能运算的结果是{TABLE}数组，用户完全不需要注意公式里的单元格变量是否要加上绝对地址符号“$”，即可通过定义的行单元格与列单元格，创建行、列交叉的表格式运算。
一、数据的运算（模拟运算表）
公式的创建与运用往往是初学者感到迷惑的地方，尤其是在考虑到公式里的绝对地址究竟要如何设置时，经常不知道怎么办，因为复制公式后的结果常常出乎意料，总是花许多时间才能搞清楚，不过，当你知道Excel的“数据表”功能（以前称之“模拟运算表”）及操作特性，这些疑虑就迎刃而解了！

111111

（一）单一变量的数据表运算1垂直方向的数据运算
例如，一个简单的数学方程式f(x)=2X3+5X-7里含有一个变量的X，只要代入X的值后，即可算出这个方程的答案，也就是说，如果X代入0，则答案应为-7；如果代入1，则答案应为0。其实，只要心算一下就很容易得到答案。可是，如果X为0.678123,则代入方程后计算出来的答案就不是一般人可以算得知的了！另外，如果有一大堆的数字要分别代入此方程并分别计算出各个结果，这也不是心算可以很快计算出结果的。然后，如果交同计算来运算就会又正确又快速。Excel的数据表运算就是你最好的帮手。
以下图表 3而言，我们想创建一个2X3+5X-7的方程，并代入-4、-3、-2、-1、0、0.768123、1、2、3、4这10个数值到X变量时，Excel的数据表运算功能可以很轻松地为你计算出10个答案。

以下即为此范例的操作方式。
首先，将各个要代入量X的数值输入到A5:A14连续的单元格内。然后，再将公式输入到刚刚那块区域右上方单元格B4内。在输入公式之前，必须先假想公式内的变量X为工作表上的某一个空白单元格，意为“可变单元格”。例如：此范例中我们视为变量X为空白单元格D1，Excel将A5：A14连续单元格内的各个数值一一代入此单元格，而计算出来的方程答案便会自动呈现在旁边。因此，在单元格B4内所输入的公式为：=2 *D1^3+5*D1-7.
具体操作：
Step1:在A5:A14单元格区域中输入要代入变量X的各个数据。
Step2:在单元格B4中输入方程公式“=2 *D1^3+5*D1-7”如图表 4所示

Step3:输入完公式后，用鼠标选择包括各个X数值与公式在内的整个矩形，例如：A4：B14，而此区在术语上即称为“数据表”（以前称为“模拟运算表”），如图表 5

Setp 4:单击“数据”选项卡
Step 5:单击“数据工具”功能 区里的“假设分析”命令按钮。
Step 6：从下拉菜单中选择“数据表”选项
Step 7:立即打开“数据表”对话框，在“输入引用列的单元格”中输入D1
Step 8 单击“确定”按钮，如图表 6所示

说明：由于在本例中，我们将变量X视为单元格D1，因此在步骤2中输入的公式里便包含了D1。而要带入变量X进行运算的各个数据已经事先按照垂直方向排列并一一输入列A5：A14单元格区域中，所以单元格D1又称为引用“列”的单元格，在“数据表”对话框中输入D1。


[ 本帖最后由 renqiang851224 于 2010-11-7 04:10 编辑 ]

renqiang851224

水平方向的数据运算表
前面的操作就是所谓的“数据表”运算。当然，你也可以将要代入变量X的各个数值输入到横向的连续单元格内，如所图表 8示的B4：K4，此区域也称之为“常数区域”。假设此例中将变量X视为工作表上的空单元格D1。公式没则输入到常数区域左下方的单元格内，例如在单元格A5中输入“=2 *D1^3+5*D1-7”，输入完公式后，就可通过相同的操作，完成大量的数据运算。
具体步骤：
Step 1:在B4：K4单元格区域内要代入变量X的各个数据
Step2:在单元格A5中输入方程公式“=2 *D1^3+5*D1-7”

Step 3用鼠标选择包括常数区域与公式在内的矩形区域，也就是A4：A5单元格区域。
Setp 4:单击“数据”选项卡
Step 5:单击“数据工具”功能 区里的“假设分析”命令按钮。
Step 6：从下拉菜单中选择“数据表”选项
Step 7:立即找开“数据表”对话框，在“输入引用行的单元格”中输入D1
Step 8 单击“确定”按钮，如图表 9所示

说明：在此例中，我们仍将变量X[视为单元格D1，所以在步骤2中所输入的公式也包含了D1，而要代入变量X进行运算（也就是要代入单元格D1进行运算）的各个数据，已经事先按照水平方向排列并一一输入在区域B4：K4内。因此，单元格D1又称“行”的单元格。所以在“数据表”对话框中的“输入引用行的单元格”就必须是D1。如图表 10所示

双变量的数据表运算
上一小节的f(x)=2x3+5x-7是一个变量的数学方程，如果方程中有两个变量，例如f(x)=2x3+3y2-2xy+3y-2y+7,此方程中有变量X与y，必须代入X与y的值后，才可算出此方程的答案，这个方程比起前一节的f(x)= =2 *D1^3+5*D1-7 方程要复杂得多，要代入各种数据到X 与y时，运算也要多费此些时间，不过，你也可以将此方程的运算交给Excel的数据运算，瞬间即可完成大量数据的代入与运算。
例如，我们将X分别代入-2、-1、0、1、2、3这6个数值，而y分别代入-2、-1、0、1、2、3这6个数值。参考图表 11，我们将要代入变量X运算的数值分别输入在D6:I6单元格区域中，而要代入y分别输入在C7:C12单元格区域中，把X变量的可变单元格指定为D4，y变量的可变单元格指定E4.因此，这个化工应为：
=2*D4^3+3*E4^2-2*D4*E4+3*D4-2*E4+7
将此公式输入到单元格C6，区域C6：I12即为：“数据表”区域。
具体步骤：
Step1: 先准备好要代入公式进行运算的各个数据，形成一张数据表，首先把代入变量X的各个数据事先输入在D6：I6单元格区域中。
Step2:将要代入变量y的各个数据事先输入在C7：C12的单元格区域中。

Step3：在C6中输入公式“=2*D4^3+3*E4^2-2*D4*E4+3*D4-2*E4+7”如图表 12所示

Step4:选择整个数据表区域C6：I12。
Setp 5单击“数据”选项卡
Step 6:单击“数据工具”功能 区里的“假设分析”命令按钮。
Step 7：从下拉菜单中选择“数据表”选项
Step 8:立即打开“数据表”对话框，在“输入引用行的单元格”中输入D4
Step9: 在“输入引用行的单元格”中输入E4
Step 10: 单击“确定”按钮，如图表 13所示

说明：这次共有两个变量要代入公式中，因此，哪个是“输入引用行的单元格”，哪个是“输入引用列的单元格”就显得非常重要了。在此例中，要代入变量X，也是就单元格D4的各个数据是以水平方向排列输入区域D6：I6内的，所以，单元格D4便称为引用“行”的单元格，而要代入变量y，也就是单元格E4里的各个数据是以垂直方向排列输入在常数区域C7：C12内的，所以，单元格E4即称为引用“列”单元格。所以，“数据表”对话框内的“输入引用行的单元格”应输入D4，“输入引用列的单元格”则应输入E4。
从以上两个小节的介绍，我们可以得到一个结论，所谓的“数据表”运算就是在单元格内建立一个含有变量的公式，再将要代入运算的各个数值分别输入在工作表上接着，只要经过“假设分析”中的“数据表”命令操作，Excel即可计算数值代入公式运算后的每个答案，不过，Excel的数据表运算操作，最多吸能完成包含两个变量的公式运算。
当然，部分已经使用Excel有一段时间的读者或许有些凝问，因为他们对于设置绝对地址非常熟悉，只要输入含有$号的绝对地址公式，通过复制单元格公式也可以完成上述的数据表运算。但是，你不觉得含有$号的公式在阅读与理解上有点儿困难吗？尤其是被子学Excel的用户，只要$地址有一点标示错误，整个复制公式的结果就会不正确。


[ 本帖最后由 renqiang851224 于 2010-7-25 18:39 编辑 ]

zhangjimfu

谢谢楼主分享！！！

356468826

哇 以前的课件看不明白，谢谢楼主 收藏学习

zez

谢谢分享!!!!

bibisin

非常详细，谢谢LZ分享，学习了

deweyfbi

谢谢分享，~~

chengyongping

太好了，数据表这一块的知识在网上太少了，谢谢楼主分享啊

Wz2796

谢谢分享!!!!

jamy851224

谢谢分享下载学习一下