统计学实验excel-实验3

1、试验 3 相关分析与回归分析一、试验目的与要求（ 1）根据统计数据绘制散点图；（ 2）运用不同方法计算相关系数；（ 3）掌握回归分析的方法；（ 3）对回归分析结果进行显著性检验，进行回归预测，能对结果进行解释。二、相关分析例 3-1：某市电子公司所属14 个企业 2018 年的设备能力和劳动生产率如表 3-1所示，为了方便计算的方便，将企业的设备能力用x 表示，劳动生产率用 y 表示，试绘制 x 和 y 的散点图，并运用不同方法计算其相关系数。表 3-1 某市电子公司所属14 个企业 2018 年的设备能力和劳动生产率汇总表企业编号设备能力（千瓦 /小时） x劳动生产率（千元/人） y12.8

2、6.722.86.933.07.242.97.353.48.463.98.874.09.184.89.894.910.6105.210.7115.411.1125.511.8136.212.1147.012.41、利用 excel计算相关系数（ 1）绘制散点图图 3-1 设备能力余劳动生产率散点图（ 2）计算相关系数1）常规方法计算相关系数nxyxyr22n x2xy2ny分别计算公式中的各项，如图3-2 所示。图 3-2 常规法计算相关系数常规方法还可以通过=(COUNT(A2:A15)*SUM(D2:D15)-SUM(B2:B15)*SUM(C2:C15)/(SQRT(COUNT(A2:A

3、15)*SUM(E2:E15)-SUM(B2:B15)2)*SQRT(COUNT(A2:A15)*SUM(F2:F15)-SUM(C2:C15)2) 公式来计算相关系数r。2）运用函数计算相关系数运用函数 correl 公式来计算其相关系数，如图3-3 所示。图 3-3 函数法计算相关系数3）运用数据分析工具计算相关系数运用数据分析工具中“相关系数”工具计算相关系数如图3-4 至图 3-6 所示。图 3-4 数据分析工具计算相关系数（一）图 3-5 数据分析工具计算相关系数（二）图 3-6 数据分析工具计算相关系数（三）2、利用 SPSS计算相关系数相关分析使用某个指标来表明现象之间相互依存关

4、系的密切程度，用来测度简单线性相关关系的系数是pearson简单相关系数。（ 1）绘制散点图在做相关分析之前，可以先利用散点图来观察两个变量之间有无相关趋势，从图形能判断它们存在一定的相关趋势后，能使随后的相关分析更有意义。步骤为：依次点击菜单“【图形】【图形构建程序】 ”，如图 3-7 所示，打开图形构建器，选择“散点图 /点图”，将“设备能力”变量作为横轴， “劳动生产率”变量作为纵轴作图，如图 3-8 所示，输出如图3-9 所示。图 3-7 图形 -图形构建程序图 3-8 图形构建设置窗口图 3-9 设备能力与劳动生产率的散点图观察设备能力与劳动生产率的散点图，可以初步判断二者存在一定的

5、正相关关系。下一步可以进行相关分析，以明确这种相关性的存在性及其程度大小。（ 2）计算相关系数依次选择“【分析】【相关】【双变量】 ”打开对话框如图 3-10 所示，将待分析的两个指标移入右边的变量列表框内；最下方的“标记显著性相关”复选框要求在结果中用星号标记有统计学意义的现关系数，默认为选中。此时 P0.05 的系数值旁会标记一个星号， P0.01 会标记两个星号。图 3-10 双变量相关分析的设置窗口计算结果如图3-11 所示。图 3-11 相关分析结果输出图 3-11 显示了 pearson相关系数及其检验结果。 可见，pearson相关系数在 0.01的显著性（双侧检验）上都非常显著

6、，可以推断出设备能力与劳动生产率之间存在着明显的正相关。三、回归分析例 3-2：利用例 3-1 中数据，运用不同方法建立回归方程。1、利用 excel进行回归分析（ 1）常规方法建立一元线性回归方程nxyx ya y bxbx22nx分别计算公式中的各项，如图3-12 所示。图 3-12 常规方法回归分析常规方法还可以通过=(COUNT(A2:A15)*SUM(D2:D15)-SUM(B2:B15)*SUM(C2:C15)/(COUNT(A2:A15)*SUM(E2:E15)-SUM(B2:B15)2) 公式来计算相关系数b，通过=AVERAGE(C2:C15)-E18*A VERAGE(B2

7、:B15) 计算 a。通过此方法计算得到的回归方程为：y3.101.448 x（ 2）用“添加线性趋势线”建立一元线性回归方程1）绘制散点图；2）单击散点图中任一点，右击鼠标，选择“添加趋势线”命令，如图3-13 所示，选择“线性”，并勾选“显示公式”和“显示R 平方值”，点击确定，得到结果如图 3-14 所示。图 3-13 “添加趋势线”对话框图 3-14 散点图中插入趋势线结果由插入的趋势线得到的回归方程为：y3.10031.4481x（ R2=0.9613），该模型的拟合优度可用判定系数来决定， 表明劳动生产率的变化中有96.13%可由设备能力加以解释，剩余的5.77%则由其余因素引起。

8、（ 3）用“统计函数”建立一元线性回归方程1）运用 intercept 函数和 slope 函数求解截距和斜率图 3-15 intercept 函数和 slope 函数求解结果此方法得到的回归方程为：y3.10031.4481x2）运用 linest 函数建立一元线性回归方程图 3-16 linest 函数求解结果此方法得到的回归方程为：y3.10031.4481x（ 4）用数据分析工具建立一元线性回归方程图 3-17 数据分析工具“回归”结果输出图图 3-18 数据分析工具“回归”结果输出图（残差图& 概率输出图）图 3-19 数据分析工具“回归”结果输出图（残差图）图 3-20 数据分析工

9、具“回归”结果输出图（线性拟合图）图 3-21 数据分析工具“回归”结果输出图（正态概率图）2、利用 SPSS进行回归分析步骤 1：选择菜单“【分析】【回归】【线性】 ”，打开线性回归对话框。将变量“劳动生产率”移入“因变量”列表框中，将“设备能力”移入“自变量”列表框中。单击“方法”框下拉列表，选择“进入” ，表示所选自变量全部进入回归模型，如图 3-22 所示。图 3-22 线性回归对话框步骤 2：单击“统计量”按钮，在弹出的对话框中设置要输出的统计量。这里选中估计、模型拟合度及描述性复选框，如图3-23 所示。图 3-23 线性回归 -统计量子对话框估计：输出有关回归系数的统计量，包括回

10、归系数、回归系数的标准差、标准化的回归系数、 t 统计量及其对应的p 值等；置信区间：输出每个回归系数的95的置信度估计区间；协方差矩阵：输出解释变量的相关系数矩阵和协差阵；模型拟合度：输出可决系数、调整的可决系数、回归方程的标准误差、回归方程 F 检验的方差分析。步骤 3：单击绘制按钮，在图子对话框中的标准化残差图选项栏中选中正态概率图复选框，以便对残差的正态性进行分析，如图3-24 所示。图 3-24 线性回归 -图子对话框步骤 4：单击保存按钮，在 Save子对话框中残差选项栏中选中未标准化复选框，如图 3-25 所示，这样可以在数据文件中生成一个变量名尾res_1 的残差变量， 以便对

11、残差进行进一步分析。图 3-25 线性回归 -保存子对话框其余保持 SPSS 默认选项。在主对话框中单击“确定”按钮，执行线性回归命令，其结果如图 3-26 至图 3-3 所示。图 3-26 回归模型的描述图 3-26 所示是对模型中各个变量纳入模型情况进行汇总， 由表可见，只有一个自变量，变量选择的方法是强行进入法，也就是将所有的自变量都放入模型中。图 3-27 回归方程拟合情况的描述图 3-27 所示是对回归方程拟合情况的描述， 可知相关系数的取值、 相关系数的平方即决定系数， 校正后的决定系数和回归系数的标准误。 R 为 0.980，与前面相关分析中的结果一致；决定系数 R2 为 0.9

12、61，证明模型效果较好（决定系数越大，模型的效果越好）。图 3-28 回归系数及其显著性检验结果图 3-28 所示给出了回归方程中常数项、回归系数的估计值和检验结果，a 为3.100，b 为 1.448，回归方程为：劳动生产率=3.100+1.448*设备能力。对回归系数的检验拒绝了原假设，认为自变量对因变量的线性关系是确实存在的。图 3-29 回归模型的显著性检验结果图 3-29 所示为对模型进行方法分析的结果，F 值为 298.363，P 为 0.000，提示模型具有统计学意义，即存在显著线性关系。图 3-30 标准残差P-P 图为了判断随机扰动项是否服从正态分布，观察图 3-30 所示的标准化残差的PP 图，可以发现，各观测的散点基本上都分布在对角线上，据此可以初步判断残差服从正态分布。四、练习某大型连锁店调查了12 家分店的营业支出和营业收入，结果如表3-2 所示，试求：（1