第11章 Excel在统计中的应用 29

图11-38 用Excel进行因素分析资料及结果

第三步：计算各个p1q1和∑p1q1：在I2中输入“=E2*F2”，并用鼠标拖曳将公式复制到I2：I4区域。选定I2：I4区域，单击工具栏上的“∑”按钮，在I5出现该列的求和值。

第四步：计算总成本指数：在C6中输入“=I5/G5”，即求得总成本指数。 第五步：计算产量指数：在C7中输入“=H5/G5”，即得产量指数。

第六步：计算单位成本指数：在C8中输入“=I5/H5”即求得单位成本指数。

第五节 用Excel进行相关与回归分析

例11-12 10个学生身高和体重的情况如下：

首先把有关数据输入Excel的单元格中，如图11-39所示：

30 统计学原理

图11-39 Excel数据集

一、用Excel进行相关分析

用Excel进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。

1．利用函数计算相关系数

在Excel中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON 函数，这两个函数是等价的，这里我们介绍用CORREL函数计算相关系数。

第一步：单击任一个空白单元格，单击“插入”菜单，选择“函数”，打开“插入函数”对话框，在“选择类别”中选择“统计”；在“选择函数”中选择“CORREL”，单击确定后，出现CORREL对话框。

第二步：在array1中输入B2：B11，在array2中输入C2：C11，即可在对话框下方显示出计算结果为0.896028。如图11-40所示。

第11章 Excel在统计中的应用 31

图11-40 CORREL对话框及输入结果

2．用相关系数宏计算相关系数

第一步：单击“工具”菜单，选择“数据分析”项，在“数据分析”项中选择“相关系数”，弹出“相关系数”对话框，如图11-41所示。

图11-41 相关系数对话框

第二步：在“输入区域”输入$B$1:$C$11，“分组方式”选择逐列，选择“标志位于第一行”，在“输出区域”中输入$E$1，单击“确定”，得输出结果如图11-42所示。

32 统计学原理

图11-42 相关分析输出结果

在上面的输出结果中，身高和体重的自相关系数均为1，身高和体重的相关系数为0.896028，与用函数计算的结果完全相同。

二、用Excel进行回归分析

Excel进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测，这9个函数分别是：

1．INTERCEPT返回线性回归模型的截距 2．SLOPE返回线性回归模型的斜率 3．RSQ返回线性回归模型的判定系数

4．FORECAST返回一元线性回归模型的预测值 5．STEYX计算估计的标准误差