〖实训五〗用Excel进行统计相关回归分析.doc

1、实训五 用 Excel 进行统计相关分析目的与要求：掌握利用相关系数对相关关系进行测定，并且掌握相关系数的性质，明确相关分析与回归分析各自特点以及它们的区别与联系，建立回归直线方程，计算估计标准误差，理解估计标准误差的意义。案例 15：用 EXCEL 进行相关与一元线性回归分析一、用 EXCEL 绘制散点图例 1近年来国家教育部决定将各高校的后勤社会化。某从事饮食业的企业家认为这是一个很好的投资机会，他得到十组高校人数与周边饭店的季销售额的数据资料，并想根据高校的数据决策其投资规模。操作过程：打开 Excel 工作簿，输入原始数据如图 7-1 所示，该表为相关表。图 7-1从“插入”菜单中选择

2、“图表”选项，打开“图表向导”对话框如图 7-2 所示。在“图表类型”列表中选择 XY 散点图，单击“下一步”按钮。图 7-2在数据区域中输入 B2:C11，选择“系列产生在列” ，如图 7-3所示，单击“下一步”按钮。图 7-3打开“图例”页面，取消图例，省略标题，如图 7-4 所示。图 7-4单击“完成”按钮，便得到 XY 散点图如图 7-5 所示。图 7-50501001502002500 5 10 15 20 25 30二、计算相关系数用 EXCE 计算相关系数有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。例 210 个学生身高和体重的情况如下：学生 身高（公分） 体重（公

3、斤）1234567891017116717715416917516315217216053566449556652475850要求对身高和体重作相关和回归分析。 操作步骤如下：首先把有关数据输入 EXCEL 的单元格中，如图 7-6图 7-6 EXCEL 数据集在 EXCEL 中，提供了两个计算两个变量之间相关系数的方法，CORREL 函数和 PERSON 函数，这两个函数是等价的，这里我们介绍用 CORREL 函数计算相关系数：第一步：单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择 CORREL，单击确定后，出现 CORREL 对话

4、框。第二步：在 array1 中输入 B2：B11，在 array2 中输入C2：C11，即可在对话框下方显示出计算结果为 0.896。如图 7-7 所示：图 7-7 CORREL 对话框及输入结果2.用相关系数宏计算相关系数第一步：单击工具菜单，选择数据分析选项，在数据分析选项中选择相关系数，弹出相关系数对话框，如图 7-8 所示：图 7-8 相关系数对话框第二步：在输入区域输入$B$1:$C$1，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$1，单击确定，得输出结果如图 7-9图 7-9 相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为 1，身高和体重的相关系数

5、为 0.896，和用函数计算的结果完全相同。 三、用 EXCEL 进行一元线性回归分析 EXCEL 进行回归分析同样分函数和回归分析宏两种形式，其提供了 9 个函数用于建立回归模型和预测。这 9 个函数分别是：INTERCEPT 返回线性回归模型的截距SLOPE 返回线性回归模型的斜率RSQ 返回线性回归模型的判定系数FORECAST 返回一元线性回归模型的预测值STEYX 计算估计的标准误TREND 计算线性回归线的趋势值GROWTH 返回指数曲线的趋势值 LINEST 返回线性回归模型的参数LOGEST 返回指数曲线模型的参数用函数进行回归分析比较麻烦，我们这里介绍使用回归分析宏进行回归分

6、析。第一步：单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，如图 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 指调整后的判定系数，标

7、准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，该部分给出了自由度（df） ，回归平方和、残差平方和、总平方和（SS） ，回归和残差的均方（MS） ，F 检验的显著性水平（Significance F） ， p 值（F） ，该部分在主要作用是对回归方程的线性关系进行显著性检验；第三部分是参数估计的有关内容，包括回归方程的截距（Intercept） 、斜率（X Variable 1）Coefficient 指系数，用于检验的回归系数的 t统计量（t stat） 、P_值（P-value ） ，以及截距和斜率的置信区间（Lower 95%和 Upper95%） 。案例 16 多元线性回

8、归分析我们以中国民航客运量预测为例进行多元线性回归分析。第一步 确定因变量。我们以预测目标中国民航客运量，作为因变量。第二步 确定自变量。在定性分析的基础上，我们确定国内生产总值（ ） 、实际利用外资额（ ） 、民航线里程（ ） 、来华旅游1x2x3x入境人数（ ）为自变量。4搜集样本资料如表 7.4.1 所示。表 7.4.1年份 民航客运量 （万人）y 国内生产总值 （亿元）x1实际利用外资额（亿美元）x 2民航线里程（万公里）x 3旅游入境人数（万人）x41983 391 5934.5 19.81 22.91 947.71984 554 7171 27.05 26.02 1285.2198

9、5 747 8964.4 46.47 27.72 1783.31986 997 10202.2 72.58 32.43 2281.91987 1310 11962.5 84.52 38.91 2690.21988 1442 14928.3 102.26 37.38 3169.51989 1283 16909.2 100.59 47.19 2450.11990 1660 18547.9 102.89 50.68 2746.21991 2178 21617.8 115.54 55.91 3335.01992 2886 26638.1 192.023 83.66 3811.51993 3383 34

10、634.4 389.6 96.08 4152.71994 4038 46759.4 432.13 104.56 4368.41995 5117 58478.1 481.37 112.9 4638.71996 5555 67884.6 548.04 116.65 5112.81997 5630 74462.6 644.08 142.50 5758.81998 5755 78345.2 585.57 150.58 6347.81999 6094 82067.5 526.59 152.22 7279.62000 6722 89442.2 593.56 150.29 8344.42001 7524 9

11、5933.3 496.72 155.36 8901.3依据上述样本资料,计算出因变量 与每个自变量 的相关系数 ，yjxyjR利用 Excel 软件得到下列相关系数 （见表 7.4.2）yjR表 7.4.2X1 X2 X3 X4y 0.991557 0.954922 0.98455 0.965219利用 Excel 计算相关系数的步骤如下：“工具” “数据分析 ” “相关系数 ” “确定”在“输入区域”输入 y 与 x1数字区域在“输出区域” 输入 A1 单元格“确定”就得到 y 与 x1之间的相关系数 0.991557。 其余相关系数以此类推。第三步 建立模型，进行参数估计。由表 7.4.2

12、 知，自变量国内生产总值、实际利用外资额、民航线里程、来华旅游入境人数分别与因变量 y 间的简单线性相关系数都较高，都在 0.95 以上。不妨我们建立四元线性回归模型：。01234yxxu依据表 7.4.1 中的样本资料，利用 Excel 软件计算有关结果如输出结果 7.4.3 所示（操作步骤如一元线性回归） 。输出结果 7.4.1回归统计Multiple R 0.994469R Square 0.988968Adjusted R Square 0.985816标准误差 282.3387观测值 19方差分析df SS MS F Significance F回归分析 4 1E+082501116

13、1313.7568 1.58E-13残差 14 111601279715.12总计 181.01E+08 Coefficients标准误差 t Stat P-valueIntercept -139.555232.7273-0.59965 0.55832X Variable 1 0.0363560.0142192.5568060.022814X Variable 2 1.6114441.4682661.0975150.290935X Variable 3 7.515343 9.558490.7862480.444827X Variable 4 0.2125190.1426651.4896330.

14、158502第四步 进行有关统计显著性检验从 F 统计量角度看，四元线性回归模型通过检验（Significance F1.57571E-13 0.05） 。从 t 检验角度看，只有国内生产总值 通过 t 检验(P-value0.022814 0.05)。1x不妨我们先剔除 统计量（t Stat）最小的那个自变量，重新建立回归模型。因为 0.786248“X Variable 3 t Stat”3（0.786248）最小，所以我们先剔除自变量民航线里程 ，重新建x立三元线性回归模型。利用 Excel 软件计算出三元线性回归模型有关信息如输出结果7.4.2 所示。输出结果 7.4.2回归统计Mul

15、tiple R 0.994224R Square 0.988481Adjusted R Square 0.986177标准误差 278.7221观测值 19方差分析df SS MS F Significance F回归分析 3 99995365 33331788 429.0577 9.32E-15残差 15 1165290 77686.03总计 18 1.01E+08 Coefficients 标准误差 t Stat P-valueIntercept -54.3332 203.3073 -0.26725 0.792919X Variable 1 0.038591 0.013754 2.8057

16、66 0.013304X Variable 2 2.427005 1.025848 2.365853 0.03188X Variable 3 0.265121 0.124392 2.131346 0.05001从 F 统计量角度看，三元线性回归模型通过检验（Significance 9.32E-15 0.05） 。从 t 检验角度看，国内生产总值 通过 t 检验(P-value0.013304 0.05)和实际利用外资额 通1x 2x过 t 检验(P-value0.03188 0.05)，而来华旅游入境人数 没有3通过 t 检验(P-value0.05001 0.05)，但相差很小。如果我们再

17、剔除华旅游入境人数这个自变量，重新建立二元线性回归模型的话会降低整体模型的优良性。 （见输出结果 7.4.3）输出结果 7.4.3回归统计Multiple R 0.992468R Square 0.984992Adjusted R Square 0.983116标准误差 308.0371观测值 19方差分析df SS MS F Significance F回归分析 2 99642465 49821233 525.0593 2.57E-15残差 16 1518190 94886.86总计 18 1.01E+08 Coefficients 标准误差 t Stat P-valueIntercept

18、315.5839 117.0166 2.696916 0.015872X Variable 1 0.064319 0.007285 8.828926 1.51E-07X Variable 2 1.382271 0.995942 1.387903 0.184192综上所述，对于本例我们可以建立下列三元线性回归模型： 12-54.30.859.4705.61yxx3xu式中， 代表民航客运量； 代表国内生产总值； 代表实际利2用外资额； 代表来华旅游入境人数。3x第五步 进行预测1、点预测当国内生产总值为 100000 亿元，实际利用外资额为01x600 亿美元，来华旅游入境人数 9000 万人次

19、时，民航客运02x 03x量为：-54.32.8592.4756.25190y7647（万人）2、区间预测（1）个别值的区间预测若给定了显著水平 0.05，个别值在 95%概率保证下的区间预测为：2 10 00()1()TTuYtnmX其中， =278.7221u21niiy= =1.753 =1.1734342()t0.52()t 1001()TTX=0X69)1.374.53E-.289-.2706250-21047013E-8()-.856.496-T =0.376948100()TTX即 7647 1.753278.72211.173434 70748220 之间(2)平均值的区间预测

20、均值在 95%概率保证下的区间预测为2 1000()()TTuYtnmX其中， =278.7221u21niiy= =1.753 =0.6142()tn0.52(6)t 100()TTX即 7647 1.753278.72210.614 73477947 之间附：矩阵的运算（1）矩阵乘法按住鼠标左键拖放选定存放结果的单元格区域，输入计算公式=MMULT( A,B) 按 Ctrl+Shift+Enter 复合键确认。（2）矩阵转置按住鼠标左键拖放选定存放结果的单元格区域，输入计算公式=TRANSPOSE( A) 按 Ctrl+Shift+Enter 复合键确认（3）逆矩阵按住鼠标左键拖放选定存放

21、结果的单元格区域，输入计算公式=MINVERSE(A ) 按 Ctrl+Shift+Enter 复合键确认案例 17 非线性回归分析非线性回归模型具体形式很多，由于篇幅所限，在此不一一介绍，仅通过例题说明其应用思想。例 7.5.1设有 12 个同类企业的月产量与单位产品成本资料如表 7.5.1 所示。试配合适当的回归模型分析月产量与单位产品成本之间的关系。表 5.1.1编号 产量 X LgY 单位成本 Y1 10 2.204120 1602 16 2.178977 1513 20 2.056905 1144 25 2.107210 1285 31 1.929419 856 36 1.95904

22、1 917 40 1.875061 758 45 1.880814 769 51 1.819544 6610 56 1.778151 6011 60 1.785330 6112 65 1.778151 60根据表 7.5.1 资料，将月产量与单位产品成本作散点图如图7.5.1 所示。0204060801001201401601800 20 40 60 80系 列 1图 7.5.1由图 7.5.1 可以看出月产量与单位产品成本之间可以配合一条指数曲线： xyab两边取对数得： loglyaxlogb利用 软件，进行回归分析运行结果如输出结果 7.5.1 所示。Exce（操作步骤如一元线性回归）

23、。输出结果 7.5.1回归统计Multiple R 0.96097R Square 0.923463Adjusted R Square 0.91581标准误差 0.045212观测值 12方差分析df SS MS FSignificance F回归分析 10.2466310.246630906120.6562 6.68E-07残差 100.020441 0.00204408总计 110.267072Coefficients标准误差 t Stat P-valueIntercept2.261083 0.03150971.75913566.74E-15X Variable 1-0.008310.00

24、0756-10.984361156.68E-07由输出结果 7.5.1 知， 2.261083 ， -0.00831 ， lgalgb， ， ， ，0.92346r0.4210.24631ES0.241RSF120.6562 Significance F6.68E-07-10.98436115 P-value6.68E-071t对 2.261083 -0.00831 分别求反对数得lgalgb=182.4246 =0.981051利用 软件求反对数的操作步骤如下:Excel点击粘贴函数 POWER102.261083确定(182.4246)f例 7.5.2某面粉厂近十一年的面粉销售利润率、工人

25、劳动生产率、单位成本资料如表 7.5.2 所示。我们知道，劳动生产率的提高和单位成本的降低，能使利润增加。因此我们认为，利润率与劳动生产率成正比，与单位成本成反比。设利润率为 y，劳动生产率为 x1，单位成本为 x2，建立回归模型为： 2101xyb表 7.5.2年份 利润率（）y劳动生产率（吨人）x1单位成本（元吨）x21992 1.57 366 9.071993 1.97 468 7.161994 2.15 664 6.521995 1.93 505 7.531996 1.61 387 8.931997 1.37 359 9.781998 1.79 486 8.121999 2.22 54

26、8 6.232000 1.39 345 9.892001 1.63 504 8.832002 2.27 658 5.94设 ，则21x012ybx利用 软件，进行回归分析运行结果如输出结果 7.5.2 所示。Ecel由输出结果 7.5.2 知， 12.407.27.396xxF179.0624 Significance F2.28E-070.05 F 检验通过 10.679686 P-value0.5158850.05 检验没通过 2= 7.88228 P-value4.86E-050.05 检验通过0.989014输出结果 7.5.2回归统计Multiple R 0.989014R Squa

27、re 0.97815Adjusted R Square 0.972687标准误差 0.053186观测值 11方差分析df SS MS FSignificance F回归分析 2 1.013061 0.50653 179.0624 2.28E-07残差 8 0.02263 0.002829总计 10 1.035691Coefficients 标准误差 t Stat P-valueIntercept 0.104077 0.094529 1.101007 0.302915X Variable 1 0.000227 0.000334 0.679686 0.515885X Variable 2 12.

28、3916 1.572084 7.88228 4.86E-05根据上述检验，两个自变量中只有单位成本对利润率的影响显著，而劳动生产率对利润率的影响并不显著。这是因为两个自变量单位成本和劳动生产率之间存有多重共线性。不妨我们删去劳动生产率这个因素，重新建立回归模型。利用 软件，进行回归分析运行结果如输出结果 7.5.3 所示。Excel输出结果 7.5.3回归统计Multiple R 0.988376R Square 0.976888Adjusted R Square 0.97432标准误差 0.051572观测值 11方差分析df SS MS F Significance F回归分析 1 1.0

29、117541.011754380.4036 1.133E-08残差 9 0.023937 0.00266总计 10 1.035691Coefficients标准误差 t Stat P-valueIntercept 0.090187 0.0894921.0077670.339891X Variable 1 13.34642 0.68429319.503941.13E-08由输出结果 7.5.3 知， 0.9187+3.462yxF380.4036 Significance F1.133E-080.05 19.50394 P-value1.13E-080.05 0.988376 模型统计显著性检验

30、通过。【实训五上机练习】1.单位成本与产量的关系(见表 7.1)。 表 7.1 铸铁件产量及单位成本年 月 铸铁件产量(吨) 单位产品成本(元)上年 1 月 810 6702 月 547 7803 月 900 6204 月 530 8005 月 540 780 6 月 800 675 7 月 820 650 8 月 850 620 9 月 600 735 10 月 690 720 11 月 700 715 12 月 860 610今年 1 月 920 5802 月 840 630 3 月 1 000 570 要求:（1）绘制铸铁件产量与单位产品成本散点图，计算其相关系数；（2）拟合回归方程。2

31、.已知某地区 1978 年2003 年的国内生产总值 GDP 与货运周转量的数据如表 7.2 所示。年份GDP(亿元)货运周转量（亿吨公里） 年份GDP(亿元)货运周转量（亿吨公里）1978197919805.08.712.09.012.014.019911992199344.047.054.032.034.037.0198119821983198419851986198719881989199016.019.022.025.028.036.040.041.032.034.015.017.020.020.523.530.035.032.024.028.019941995199619971998

32、1999200020012002200356.556.057.059.063.066.567.070.570.673.040.044.043.543.543.544.045.547.046.052.0要求：（1）绘制散点图；（2）试对其进行一元线性回归分析（即计算相关系数、建立回归方程） ；（3）若 2005 年国内生产总值GDP 达到 80 亿元，试对其货运周转量做出预测 （包括点预测(5%)和区间预测）3.教育经费支出与学生成绩的关系学生教育达到的水平与学生所居住的洲在教育方面的经费支出多少有关系吗？在许多地区，这个重要问题被纳税人提出；而纳税人又被他们的学区请求增加用于教育方面的税收收入

33、。在这种情况下，为了确定在公立学校中教育经费支出和学生成绩之间是否存在某种关系，你将被邀请去参加教育经费支出和学生学习成绩的数据分析。美国联邦政府的全国教育进展评价 （NAEP）计划常常被用来测量学生的教育水平。对于参加 NAEP 计划的 35 个洲，表 1 给出了每名学生每年的经常性教育经费支出和 NAEP 测试综合分数的统计数据。综合分数是数学、自然科学和阅读三门课程 1996 年（阅读课是1994 年）NAEP 测试分数的总和。参加测试的是 8 年级学生，只有阅读课是 4 年级学生，满分是 1300 分。对于未参加 NAEP 计划的 13 个洲，表 2 给出了每名学生每年的经常性教育经费

34、支出。表 1 参加 NAEP 计划的洲每名学生每年的经常性教育经费支出和 NAEP 测试综合分数洲序号教育经费支出（美元）综合分数（分） 洲序号教育经费支出（美元）综合分数（分）1 4049 581 19 4521 6292 3423 582 20 6554 6383 4917 580 21 5338 6394 5532 580 22 4483 6415 4304 603 23 4772 6446 3777 604 24 5128 6497 4663 611 25 3280 6508 4934 611 26 5515 6579 4097 614 27 7629 65710 4060 614 2

35、8 6413 65811 6208 615 29 5410 66012 3800 618 30 5477 66113 4041 618 31 5060 66514 5247 625 32 4985 66715 6100 625 33 6055 66716 5020 626 34 4374 67117 4520 627 35 5561 67518 8162 628 表 2 未参加 NAEP 计划的洲每名学生每年的经常性教育经费支出洲序号 教育经费支出 （美元） 洲序号 教育经费支出 （美元）1 3602 8 54382 4067 9 55883 4265 10 62694 4658 11 639

36、15 5164 12 65796 5297 13 78907 5387 要求:（1）对这些数据做出数值的和图示的概述。（2）利用回归分析研究每名学生的教育经费支出和 NAEP 测试综合分数之间的关系，对你的调研结果进行讨论。（3）根据这些数据求出估计的回归方程，你认为能利用它来估计未参加 NAEP 计划洲的学生的综合分数吗？（4）假定你只考虑每名学生的教育经费支出在 4000 美元至 6000 美元之间的洲，对于这些洲，两变量之间的关系与根据 35 个洲的全部数据所得出的结论显现出任何不同吗？讨论你发现的结果，如果将教育经费支出少于 4000 美元或者多于 6000 美元的洲删除，你是否认为删

37、除是合理的？（5）对未参加 NAEP 计划洲，求出学生综合分数的估计值。（6）根据上面的分析，你认为学生的教育水平与洲教育经费支出的多少相关吗？4.发生车祸次数与司机年龄有关吗？作为交通安全研究的一部分，美国交通部采集了每 1000 个驾驶执照发生死亡事故的车祸次数和有驾驶执照的司机中 21 岁以下者所占比例的数据，样本由 42 个城市组成，在一年间采集的数据如下：21 岁以 每千个 21 岁以 每千个 21 岁以 每千个 21 岁以 每千个下者所占比例（%）驾驶执照中发生车祸次数下者所占比例（%）驾驶执照中发生车祸次数下者所占比例（%）驾驶执照中发生车祸次数下者所占比例（%）驾驶执照中发生车

38、祸次数13 2.962 16 2.801 8 2.190 18 3.61412 0.708 12 1.405 16 3.623 10 1.9268 0.885 9 1.433 15 2.623 14 1.64312 1.652 10 0.039 9 0.835 16 2.94311 2.091 9 0.338 8 0.820 12 1.91317 2.627 11 1.849 14 2.890 15 2.81418 3.830 12 2.246 8 1.267 13 2.6348 0.368 14 2.885 15 3.224 9 0.92613 1.142 14 2.352 10 1.014

39、 17 3.2568 0.645 11 1.294 10 0.493 9 1.082 17 4.100 14 1.443 要求:（1）对这些数据做出数值的和图示的概述。（2）利用回归分析研究发生死亡事故的车祸次数和有驾驶执照的司机中 21 岁以下者所占比例之间的关系，对你的调研结果进行讨论。（3）从你的分析中，你能得出什么结论或提出什么建议吗？5.消费者调查股份有限公司(Consumer Research，Inc.)是一家独立的机构，该机构为各种类型的厂商调查消费者的态度和行为。在一项研究中，客户为了能预测用信用卡进行支付的数额，要求对消费者的特点进行调查研究。对于由 25 名消费者组成的一个

40、样本，采集了有关年收入、家庭成员人数和年信用卡支付数额的统计资料如表7.4。表 7.4年收入（千美圆） 家庭成员人数（人） 信用卡支付数额（美圆）54 3 401630 2 315932 4 510050 5 474231 2 186455 2 407037 1 273140 2 334866 4 476451 3 411025 3 420848 4 421927 1 247733 2 251465 3 421463 4 496542 6 441221 2 244844 1 299537 5 417162 6 567821 3 362355 7 530142 2 302041 7 4828要求：（1）利用统计学的方法管理这些数据。对你的发现进行评述。（2）首先利用年收入作自变量，然后利用家庭成员人数作自变量，分别建立估计的回归方程。哪一个自变量是更好的预测年信用卡支付数额的变量？讨论你的发现。（3）利用年收入和家庭成员人数作自变量，建立估计的回归方程，讨论你的发现。（4）对于年收入为 40000 美圆的 3 口之家，预测该家庭的年信用卡支付数额是多少？