文档介绍：〖实训五〗用Excel进行统计相关分析
目的与要求:掌握利用相关系数对相关关系进行测定,并且掌握相关系数的性质,明确相关分析与回归分析各自特点以及它们的区别与联系,建立回归直线方程,计算估计标准误差,理解估计标准误差的意义。
案例15:用EXCEL进行相关与一元线性回归分析
一、用EXCEL绘制散点图
[例1]近年来国家教育部决定将各高校的后勤社会化。某从事饮食业的企业家认为这是一个很好的投资机会,他得到十组高校人数与周边饭店的季销售额的数据资料,并想根据高校的数据决策其投资规模。
操作过程:
①打开Excel工作簿,输入原始数据如图7-1所示,该表为相关表。
图7-1
②从“插入”菜单中选择“图表”选项,打开“图表向导”对话框如图7-2所示。在“图表类型”列表中选择XY散点图,单击“下一步”按钮。
图7-2
③在数据区域中输入B2:C11,选择“系列产生在—列”,如图7-3所示,单击“下一步”按钮。
图7-3
④打开“图例”页面,取消图例,省略标题,如图7-4所示。
图7-4
⑤单击“完成”按钮,便得到XY散点图如图7-5所示。
图7-5
二、计算相关系数
用EXCE计算相关系数有两种方法,一是利用相关系数函数,另一种是利用相关分析宏。
[例2]10个学生身高和体重的情况如下:
学生
身高(公分)
体重(公斤)
1
2
3
4
5
6
7
8
9
10
171
167
177
154
169
175
163
152
172
160
53
56
64
49
55
66
52
47
58
50
要求对身高和体重作相关和回归分析。 
操作步骤如下:
首先把有关数据输入EXCEL的单元格中,如图7-6
图7-6 EXCEL数据集
在EXCEL中,提供了两个计算两个变量之间相关系数的方法,CORREL函数和PERSON函数,这两个函数是等价的,这里我们介绍用CORREL函数计算相关系数:
第一步:单击任一个空白单元格,单击插入菜单,选择函数选项,打开粘贴函数对话框,在函数分类中选择统计,在函数名中选择CORREL,单击确定后,出现CORREL对话框。
第二步:在array1中输入B2:B11,在array2中输入C2:C11,。如图7-7所示:
图7-7 CORREL对话框及输入结果
第一步:单击工具菜单,选择数据分析选项,在数据分析选项中选择相关系数,弹出相关系数对话框,如图7-8所示:
图7-8 相关系数对话框
第二步:在输入区域输入$B$1:$C$1,分组方式选择逐列,选择标志位于第一行,在输出区域中输入$E$1,单击确定,得输出结果如图7-9
图7-9 相关分析输出结果
在上面的输出结果中,身高和体重的自相关系数均为1,,和用函数计算的结果完全相同。 
三、用EXCEL进行一元线性回归分析 
EXCEL进行回归分析同样分函数和回归分析宏两种形式,其提供了9个函数用于建立回归模型和预测。这9个函数分别是:
INTERCEPT 返回线性回归模型的截距
SLOPE 返回线性回归模型的斜率
RSQ 返回线性回归模型的判定系数
FORECAST 返回一元线性回归模型的预测值
STEYX 计算估计的标准误
TREND 计算线性回归线的趋势值
GROWTH 返回指数曲线的趋势值
LINEST 返回线性回归模型的参数
LOGEST 返回指数曲线模型的参数
用函数进行回归分析比较麻烦,我们这里介绍使用回归分析宏进行回归分析。
第一步:单击工具菜单,选择数据分析选项,出现数据分析对话框,在分析工具中选择回归,如图7-10
 
图7-10 数据分析对话框
第二步:单击确定按钮,弹出回归对话框,在Y值输入区域输入$B$2:$B$11,在X值输入区域输入$C$2:$C$11,在输出选项选择新工作表组,如图7-11所示:
图7-11回归对话框
第四步:单击确定按钮,得回归分析结果如图7-12所示
图7-12 EXCEL回归分析结果
在上面的输出结果中,第一部分为回归统计,该部分给出了回归分析中的一些常用统计量,Multiple指相关系数,R Square指判定系数,Adjusted指调整后的判定系数,标准误差指估计的标准误,观测值指样本容量;第二部分为方差分析,该部分给出了自由度(df),回归平方和、残差平方和、总平方和(SS),回归和残差的均方(MS),F检验的显著性水平(Significance F), p值(F),该部分在主要作用是对回归方程的线性关系进行显著性检验;第