浅谈Excel在相关分析中的应用

浅谈Excel在相关分析中的应用

霍秀荣

霍秀荣

（重庆工贸职业技术学院重庆408000）

中图分类号：O212.1文献标识码：A文章编号：41-1413（2011）12-0000-01

摘要：相关分析是研究两个或两个以上的现象之间相关关系的形态和程度的一种统计方法，特点是定量分析的内容较多、计算量大，应用性和实践性强，而传统的分析和计算仍沿用经典的手工计算和半手工的计算器计算，学生难学，教师难教。本文就采用传统的计算分析方法与利用Excel工具进行分析进行了比较，提出在统计教学中应改进传统的教学方法，加强统计分析软件在统计教学中的应用，进而提高学生的计算能力与数据分析处理能力。

关键词：相关关系统计教学Excel

社会经济现象之间总是相互联系和相互制约的，例如：个人收入水平与受教育程度、居民的消费与收入水平、销售额与广告费用的支出、父母的身高与其子女的身高等等，就是一种不确定的依存关系，这种关系，在统计中称之为相关关系。如何对现象的这种依存关系及其表现出的规律性进行数量上的推断和认识，以便为回归分析提供依据？这就是相关分析的主要内容。

相关分析的教学内容，主要围绕以下三个方面进行：

判断现象之间是否存在相关关系（定性分析法）

分析现象间相关关系的形态特征（制作相关表和绘制散点图）

分析现象间相关关系的密切程度（计算相关系数）

对现象进行定性分析，就是根据现象质的规定性，运用理论知识、专业知识、实际经验来进行判断和分析，如：商品销售额与流通费用率、产量与生产费用、身高与体重的关系等等都是属于相关关系。

进一步的分析就需要实际操作和计算。以下就传统的分析方式和利用Excel软件分析进行比较。

一、相关关系的形态特征的分析

主要通过编制相关表和制作散点图来判断。

相关表是一种反映变量之间相关关系的统计表。将某一变量按其取值的大小排列，然后再将与其相关的另一变量的对应值平行排列，便可得到简单的相关表。例：

某企业8年产品产量与生产费用的相关表

某企业8年产品产量与生产费用的相关表

年份

产量(千吨)x

生产费用(万元)y

1993

1.2

62

1994

2.0

86

1995

3.1

80

2006

3.8

110

2007

5.0

115

2008

6.1

132

2009

7.2

135

2010

8.0

160

散点图又称相关图，它是将相关表中的观测值在平面直角坐标系中用坐标点描绘出来，以表明相关点的分布状况。通过相关图，可以大致看出两个变量之间有无相关关系以及相关的形态、方向和密切程度。

传统的方式是利用尺、笔工具，在直角坐标系中将产量与生产费用的各其数据点描绘出来，再观察数据点的分布特征，操作起来费时费力；而利用Excel中强大的图表功能，可以将选定区域的数据按图表向导的步骤轻松实现：

通过散点图可直观判断出产量与生产费用之间大致呈现的是单相关、正相关。但这种相关关系密切程度如何？还需通过计算相关系数来判断。

二、相关关系的密切程度的分析

相关表和相关科图只能大致判明现象之间是否存在相关关系及相关形态。因此需要计算相关系数进行定量分析。

相关系数是用来说明变量之间在直线相关条件下相关关系密切程度和方向的统计分析指标。

其计算公式为：

式中：n表示数据项数，x为自变量，y为因变量。

根据上述公式推导得简化公式：

传统的计算方式是利用计算器，编制计算表，分项计算后，再计算相关系数，如下例：

某企业8年产品产量与生产费用的相关表

某企业8年产品产量与生产费用的相关表

年份

产量(千吨)

x

生产费用

(万元)y

x2

y2

xy

1993

1.2

62

1.4

3844

74.4

1994

2.0

86

4.0

7396

172.0

1995

3.1

80

9.6

6400

248.0

2006

3.8

110

14.4

12100

418.0

2007

5.0

115

25.0

13225

575.0

2008

6.1

132

37.2

17424

805.2

2009

7.2

135

51.8

18225

972.0

2010

8.0

160

64.0

25600

1280.0

合计

36.4

880

207.5

104214

4544.6

单击空白单元格,选择“插入函数”命令；在打开的对话框中,选择“统计”；在函数框中选择,“CORREL”函数；在对应单元格选择两组数的单元格范围。

单击“输入”，结果马上在单元格显示出来：

当相关系数的|r|≥0.8，一般称为高度线性相关，在确定现象之间存在高度线性相关的前提下，就可以对利用数学模型对这种相关关系进行拟合，即进行回归分析，从而揭示现象间数量关系，并据此进行统计推断和预测。

教学中充分利用现代计算技术，通过Excel软件的应用，将统计方法中复杂烦琐的计算过程屏障起来，让学生直接看到统计输出结果，使得复杂的相关分析变得快速而简单，从而使统计数据处理、显示和分析变得简单、快速而容易理解。

教学可以在计算机机房中进行，通过教师演示、引导、学生可以在较短的时间内学会相关分析的主要内容，在教师的指导下，同学们可以登录国家统计局及各级政府网站，下载所所需数据资料，利用刚刚学到的统计学知识、过去学过的统计学知识和自学过的统计学知识，对身边熟视无睹的各类现象或者国民经济、居民生活等现象进行相关分析，加深了对统计学教学知识点的认识和理解，激发学好统计学的热情，同时也培养了同学们的创新精神。

总之，统计教学中，在内容篇幅上应加大Excel软件在统计分析中的应用；在教学方式上也应改进统计教学方法，对于需要进行大量的计算分析的教学内容，可采用计算机机房授课方式，培养学生熟练地运用计算机处理大量数据、分析数据的技能，这样学生不仅理解了统计思想和方法，而且锻炼和培养了研究和解决问题的能力。

=0.9697

参考文献：

1、粟方忠主编：《统计学原理》，东北财经大学出版社，2011年1月第四版P165～173

2、神龙工作室：《Excel函数应用500例》，人民邮电出版社，2006年3月第一版P208～210

作者简介：霍秀荣（1964-），女，重庆工贸职业技术学院副教授，长期从事统计、计算机教学和企业社会责任专题研究。