《用Excel进行相关分析.doc》由会员分享，可在线阅读，更多相关《用Excel进行相关分析.doc（10页珍藏版）》请在三一办公上搜索。
1、第四部分上机实验 相关与回归分析一、用Excel进行相关分析【例4.1】用表41的资料对学生身高和体重做相关分析。表41 10名学生身高和体重数据表学生身高（cm）体重（kg）1171532167563177644154495169556175667163528152479172581016050首先将相关数据输入Excel的单元格中，如图41所示。 图41 学生身高和体重数据用Excel进行相关分析有两种方法，一是利用相关系数函数，二是利用相关分析分析工具。利用函数计算相关系数在Excel中，提供了两个计算两个变量之间相关系数的函数，即CORREL函数和PERSON函数，这两个函数是等价的，
2、这里介绍前者。操作步骤：单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择CORREL，如图42所示。图42 粘贴函数对话框单击确定，出现CORREL对话框，在Array1中输入B2：B11，在Array2中输入C2：C11，即可在对话框下方显示出计算结果为0.8960。如图43所示。图43 CORREL对话框及输出结果用相关分析工具计算相关系数操作步骤：在“工具”菜单中，单击“数据分析”选项，弹出“数据分析”对话框，选择“相关系数”分析工具。如图44所示。图44 数据分析对话框单击“确定”，弹出“相关系数”对话框，在输入区域中输入：$B
3、$1：$C$11，分组方式选择“逐列”，选择“标志位于第一行”，在输出区域中任选一单元格（如$B$15），如图1127所示。图45 相关系数对话框单击“确定”，得到输出结果如图46所示。 图46 相关分析输出结果二、用Excel进行回归分析用Excel进行多元线性回归分析【例4.2】某地区玻璃销售额与汽车产量、建筑业产值资料，试建立回归模型。 首先将相关数据输入Excel单元格中，如图47所示。图47 某地区玻璃销售量与汽车产量、建筑业产值资料操作步骤：在“工具”菜单中选择“数据分析”选项，弹出“数据分析”对话框，选择“回归”分析工具。如图48所示。图48 数据分析对话框 单击“确定”，弹出“
4、回归”对话框，在Y值输入区域中输入：$B$1：$B$18，在X值输入区域中输入：$C$1：$D$18，选中“标志”复选框，置信度选择95%（默认值为95%，可以根据需要修改），在输出区域中任选一单元格（如$F$3），如图49所示。图49 回归对话框单击“确定”，得到输出结果如图410所示。图410 回归分析输出结果回归分析工具的输出结果解释Excel的回归分析工具计算简便，但内容丰富，计算结果共分为三个模块：回归统计表回归统计表包括以下几部分内容：Multiple R（复相关系数R）：R2的平方根，又称为相关系数，它用来衡量变量x和y之间相关程度的大小。本例中：R为0.973，表示二者之间的关
5、系是高度正相关。R Square(复测定系数R2 )：用来说明用自变量解释因变量变差的程度，以测量同因变量y的拟合效果。复测定系数为0.9468，表明用自变量可解释因变量变差的94.68%。标准误差：又称为标准回归误差或叫估计标准误差，它用来衡量拟合程度的大小，也用于计算与回归有关的其他统计量，此值越小，说明拟合程度越好。观测值：是指用于估计回归方程的数据的观测值个数。方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果。回归参数表回归参数表是表中最后一个部分。图432中，回归参数如下：Intercept：截距0第二、三行：0(截距) 和1(斜率)的各项指标。第二列：回归系数0(
6、截距)和1(斜率)的值。第三列：回归系数的标准误差。第四列：根据原假设H0：0=1=0计算的样本统计量t的值。第五列：各个回归系数的p值(双侧)。第六列：0和195%的置信区间的上下限。 故回归方程为：Yc=19.16+35.68X1+ 10.86X2从方程检验来看，总体方程拟合优度较高，且通过了F检验，因此回归方程总体效果显著。从回归系数来看，两个自变量对应的回归系数对应的P值均显著小于0.05，表明这两个自变量对玻璃销售额均有显著影响。用Excel建立曲线回归方程【例4.3】测得不同地区某微量元素超标量与平均患病人数资料，试拟合Y关于X的回归方程。首先将资料输入Excel单元格中，如图41
7、1所示。图411 微量元素超标量与患病人数资料操作步骤：绘制散点图，如图412所示。从图中可以看出：Y与X是曲线相关关系。 图412 微量元素超标量与患病人数散点图在散点图的任一个散点上单击右键，选取快捷菜单中的“添加趋势线”。如图413所示。 图413 “添加趋势线”快捷菜单图在弹出的“添加趋势线”对话框类型中选取“对数”。如图414所示。图414 “添加趋势线”对话框在“选项”中选“显示公式” 和“显示R平方值”，如图415所示。图415 “添加趋势线”对话框单击“确定”，即可得到回归方程为：Y=7.7771Ln(X)+19.745 R2=0.9922 表明拟合精度较高。如图416所示。
8、图416 添加趋势线结果第四部分上机实验作业：用Excel软件计算已知以下数据：x8293105130144160170180y757879105120132167190要求：计算x与y的相关系数并建立简单直线回归方程。某化妆品公司在10个城市销售一种化妆品，有关销量、成年女性人口及人均可支配收入的资料如下表：城市编号销售量（万盒）成年女性人口（万人）人均月可支配收入（万元）116270.25212180.33322370.38413200.285780.23617260.3878100.3819330.25912190.2110650.26合计1322032.87要求：以销售量为因变量，以成年女性人口数和人均月可支配收入为自变量构造二元线性回归模型并进行一定的分析与判断。
