下载

1下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 用Excel计算相关系数和进行回归分析

用Excel计算相关系数和进行回归分析.doc

用Excel计算相关系数和进行回归分析

wang新菊秋
2017-09-25 0人阅读 举报 0 0 暂无简介

简介:本文档为《用Excel计算相关系数和进行回归分析doc》,可适用于综合领域

用Excel计算相关系数和进行回归分析第一节回归和相关的概念前几章的方法都只涉及一种变量主要是比较它的各组值之间的差异。但生物学所涉及的问题是多种多样的对许多问题的研究需要考虑不只一个变量例如生物的生长发育速度就与温度营养湿度„„等许多因素有关我们常常需要研究类似的多个变量之间的关系。这种关系可分为两大类即相关关系与回归关系。相关关系:两变量XY均为随机变量任一变量的每一可能值都有另一变量的一个确定分布与之对应。回归关系:X是非随机变量或随机变量Y是随机变量对X的每一确定值xi都有Y的一个确定分布与之对应。从上述定义可看出相关关系中的两个变量地位是对称的可以认为它们互为因果而回归关系中则不是这样我们常称回归关系中的X是自变量而Y是因变量。即把X视为原因而把Y视为结果。这两种关系尽管有意义上的不同分析所用的数学概念与推导过程也有所不同但如果我们使用共同的标准即使y的残差平方和最小(最小二乘法详见下述)则不管是回归关系还是相关关系都可以得到相同的参数估计式。因此本章将集中讨论数学处理较简单的回归关系且X限定为非随机变量。从这些讨论中所得到的参数估计式也可用于X为随机变量的情况但我们不再讨论X为随机变量时的证明与推导。另外回归分析和相关分析的目的也有所不同。回归分析研究的重点是建立X与Y之间的数学关系式这种关系式常常用于预测即知道一个新的X取值然后预测在此情况下的Y的取值而相关分析的重点则放在研究X与Y两个随机变量之间的共同变化规律例如当X增大时Y如何变化以及这种共变关系的强弱。由于这种研究目的的不同有时也会引起标准和方法上的不同我们将在相关分析一节中作进一步介绍。从两个变量间相关(或回归)的程度来看可分为以下三种情况:()完全相关。此时一个变量的值确定后另一个变量的值就可通过某种公式求出来即一个变量的值可由另一个变量所完全决定。这种情况在生物学研究中是不太多见的。()不相关。变量之间完全没有任何关系。此时知道一个变量的值不能提供有关另一个变量的任何信息。()统计相关(不完全相关)。介于上述两种情况之间。也就是说知道一个变量的值通过某种公式就可以提供关于另一个变量一些信息通常情况下是提供有关另一个变量的均值的信息。此时知道一个变量的取值并不能完全决定另一个变量的取值但可或多或少地决定它的分布。这是科研中最常遇到的情况。本章讨论主要针对这种情况进行。为简化数学推导本章中如无特别说明一律假设X为非随机变量即X只是一般数字并不包含有随机误差。但所得结果可以推广到X为随机变量的情况。两个变数资料的散点图将两个变数的n对观察值(x,y)、(x,y)、„、(xn,yn)分别以坐标点的形式标记于同一直角坐标平面上得到的图称为散点图(scatterdiagram)。第二节直线回归前边已经说过回归关系就是对每一个X的取值xi都有Y的一个分布与之对应。在这种情况下怎么建立X与Y的关系呢,一个比较直观的想法就是建立X与Y的分布的参数间的关系首先是与Y的均值的关系。这就是条件均值的概念记为:。它的意思是在X=x的条件下求Y的均值。更一般地我们用代表X取一切值时Y的均值所构成的集合。所谓一元线性回归就是假定X与之间的关系是线性关系而且满足:()此时进行回归分析的目标就是给出参数α和β的估计值。例对大白鼠从出生第天起每三天称一次体重直到第天。数据见表。试计算日龄X与体重Y之间的回归方程。表大白鼠日龄的体重序号日龄xi体重yi首先我们可以把数对(xi,yi)标在XY坐标系中这种图称为散点图。它的优点是可以使我们对X、Y之间的关系有一个直观的、整体上的印象如它们是否有某种规律性是接近一条直线还是一条曲线等等。我们还可以画很多条接近这些点的直线或曲线但这些线中的哪一条可以最好地代表X,Y之间的关系就不是凭直观印象可以做出判断的了。例如对例我们可画出如下的散点图:图大白鼠日龄体重关系图图中的点看来是呈直线关系但那条直线是否最好地反映了这种关系呢,或者换一种说法:该如何找到最好地反映这种关系的直线呢,这就是我们以下要讨论的问题。一元正态线性回归统计模型线性回归意味着条件平均数与X之间的关系是线性函数:()对于每个Y的观察值yi来说由于条件均值由()式决定观察值就应该是在条件均值的基础上再加上一个随机误差即:()其中。正态线性回归中“正态”的意思是随机误差服从正态分布。()式就是一元正态线性回归的统计模型。参数α和β的估计统计模型中的α和β是总体参数一般是不知道的。由于只能得到有限的观察数据我们无法算出准确的α与β的值只能求出它们的估计值a和b并得到yi的估计值为:()那么什么样的a和b是α和β最好的估计呢,换句话说选取什么样的a和b可以最好地反映X和Y之间的关系呢,一个合理的想法是使残差最小。为了避免使正负ei互相抵消同时又便于数学处理我们定义使残差平方和达到最小的直线为回归线即令:且得:整理后得()上式称为正规方程。解此方程得:这种方法称为最小二乘法它也适用于曲线回归只要将线性模型()式换为非线性模型即可。但要注意非线性模型的正规方程一般比较复杂有些情况下甚至没有解析解。另一方面不管X与Y间的真实关系是什么样的使用线性模型的最小二乘法的解总是存在的。因此正确选择模型很重要而且用最小二乘法得出的结果一般应经过检验。记称为X的校正平方和称为Y的总校正平方和称为校正交叉乘积和则:()在实际计算时可采用以下公式:现在回到例。例对大白鼠从出生第天起每三天称一次体重直到第天。数据见表。试计算日龄X与体重Y之间的回归方程。表大白鼠日龄的体重序号日龄xi体重yi解:把数据代入上述公式,得:即:所求的回归方程为:y=x带有统计功能的计算器常常也可以做一元线性回归对于这样的计算器只需把数据依次输入然后按一下键就可得到上述结果。直线回归的假设测验在介绍最小二乘法时我们曾提到不管实际上X与Y之间有没有线性关系用这种方法总是可以得到解的。因此我们必须有一种方法可以检验得到的结果是不是反映了X和Y之间的真实关系。为此我们需要研究b与a的期望与方差。注意原式=各yi互相独立且D(yi)=σ各xi为常数为估计σ令:称为残差或剩余。则残差平方和为:由于(交叉项期望为)且D(Sxy)=Sxxs,E(Sxy)=bSxx,(已证)用MSe(剩余均方)代替s可得b与a的样本方差:由于MSe的自由度为n因此上述两方差的自由度也均为n。有了a和b的方差与均值我们就可构造统计量对它们进行检验:H:b=HA:b(双侧检验)或:HA:b>(或b<)(单侧检验)统计量:()当H成立时tb~t(n)可查相应分位数表进行检验。H:a=HA:a(双侧检验)或:HA:a>(或a<)(单侧检验)统计量:()当H成立时ta~t(n)可查相应分位数表进行检验。在对一个回归方程的统计检验中我们更关心的是b是否为而不是a是否为。这是因为若b=则线性模型变为Y=ae与X无关这意味着X与Y间根本没有线性关系。反之a是否为并不影响X与Y的线性关系。因此我们常常只对b作统计检验。例对例中的b作检验:H:b=解:查表t()=<t差异极显著应拒绝H即b或X与Y有着极显著的线性关系。直线回归的方差分析对回归方程的统计检验除可用上述t检验外还有一些其他方法。这里我们再介绍一种方差分析的方法它的基本思想仍是对平方和的分解。无重复的情况。y的总校正平方和可进行如下的分解:即:Syy=SSeSSRy的总校正平方和,残差平方和回归平方和自由度:n,n这样就把y的总校正平方和分解成了残差平方和与回归平方和。前已证明MSe可作为总体方差s的估计量而MSR可作为回归效果好坏的评价。如果MSR仅由随机误差造成的话说明回归失败X和Y没有线性关系否则它应显著偏大。因此可用统计量()对H:b=进行检验。若F<Fa(,n)则接受H否则拒绝。现在我们来证明这里的F检验与前述的t检验是一致的:前已证明:SSe=Syyb×Sxy,SSR=SyySSe=b×Sxy,例对例作方差分析解:由以前计算结果:Syy=df=SSe=,df=,SSR==,df=查表得F(,)=,F(,)=F>F(,)拒绝H差异极显著。即应认为回归方程有效。有重复的情况:设在每一个xi取值上对Y作了m次观察结果记为yi,yi,„„yim,则线性统计模型变为:,i=,,„n,j=,,„m估计值仍为:现在y的总校正平方和可分解为:Syy=SSRSSLOFSSpe其中SSLOF称为失拟平方和SSpe为纯误差平方和它们的表达式和自由度分别为:同学们可试证明上述分解中的三个交叉项均为。统计检验步骤为:I令它服从F(n,mnn)()若F检验差异显著则可能的原因有:()除X以外还有其他变量影响Y的取值而统计时没有加以考虑()模型不当即X与Y之间不是线性关系此时无必要再进一步对MSR作检验而应想办法找出原因并把它消除后重作回归。若差异不显著则把MSLOF和MSpe合并再对MSR作检验:II它服从F(,mn)()若差异显著说明回归是成功的X,Y间确有线性关系若差异仍不显著则回归失败其可能的原因为:()XY无线性关系()误差过大掩盖了X,Y间的线性关系。如有必要可设法减小实验误差或增加重复数重做实验后再重新回归。直线回归的区间估计α和β的区间估计我们已经证明a和b是α和β的点估计并求出了它们的方差。因此给出置信区间就很容易了:β的置信区间为:()同理a的置信区间为:()这与以前假设检验中的置信区间求法完全一样。若置信水平为把分位数相应换为t(n)即可。例对例中的a和b给出置信区间。解:从前边的计算可知:a=,b=,Sxx=,MSe=,n=,查表得t()=a的置信区间为:,即(,)b的置信区间为:,即(,)对条件均值mYX的估计。的点估计:证明:区间估计:首先需求出的方差。用MSe代替s可得的a置信区间为:()注意上述置信区间的宽度与有关当时其宽度最小偏离后逐渐加大。对一次观察值y的估计y的点估计:证明:区间估计:一般情况下置信区间是以随机变量的期望为中点此时只要求方差就可以了因为方差就是衡量随机变量以数学期望为中心的离散程度的统计量。而现在是以条件均值的估计值即另一个随机变量为中点因此应求这两个随机变量差值的方差。由于下一次观察值y和以前所有的观察值yi都是互相独立的而估计值是从以前的观察值yi计算出来的因此与y独立从而有:由于y和均为正态分布它们的差也为正态分布。用代替后为t分布即:在x=x处y的a置信区间为:()显然y的置信区间宽度也与x有关时最小偏离时增大。y的置信区间比的大一点这是因为y自己也有一个随机误差e。例江苏武进县测定年间月下旬至月中旬平均温度累积值x和一代三化螟蛾盛发期y的关系如下表(盛发期以月日为起算日):试作回归分析。表平均温度累积值与一代三化螟盛发期年代累积温x盛发期y–解:由原始数据算得:Sxx=,Syy=,Sxy=–,b–,SSR=bSxy=查表得:F(,)=,F(,)=,F>F(,),拒绝H差异极显著。即XY有极显著线性关系。为把上述回归结果用于预报可给出观察值y的置信区间:查表得t()=,把数据代入上式得:条件均值的置信区间公式为:代入数据得:把不同的x取值代入上述公式可得置信区间的数据及图形如下:表一代三化螟盛发期置信区间xyy的置信区间的置信区间下限上限下限上限图一代三化螟盛发期置信区间回归分析的目的常常是为了预报也就是说下一次我们知道了x的取值后在观察前就对y的取值作出估计。例如表中的数据就是为了预报用的下一年度如果我们知道了月下旬至月中旬的平均温度累积值就可以估计出一代三化螟蛾盛发期是月的什么时候。要特别注意的一点是预报范围只能是我们研究过的自变量变化范围例如在上例中当积温值是在到的范围内时使用这一预报公式比较有把握和使用已有点勉强再大或小就不能用了。这是因为一般来说直线关系只是局部的近似在更大的范围内变量间常常呈现一种非线性的关系。因此若贸然把局部研究中发现的线性关系推广到更大的范围常常是要犯严重错误的。同时从置信区间的宽度也可看出即使是在研究的范围内也是越接近所研究区间的中点()预报越准确。第三节直线相关相关系数设有一X、Y均为随机变量的双变数总体具有N对(XY)。若在标有这N个(XY)坐标点的直角坐标上移动坐标轴将X轴和Y轴分别平移到μX和μY上则各点位置不变而所取坐标变为(XμXYμY)。上述三图充分说明的值可用来度量两个变数直线相关程度和性质。但是X和Y的变异程度、所取单位及N的大小都会影响为了具有可比性需要将离均差转化为标准化离均差再以N除之从而得到双变数总体的相关系数为:从样本的角度分析y的平方和SSy是由离回归平方和及回归平方和构成的后者是由X的不同而引起。显然若坐标点愈靠近回归线则对y的比率愈大直线相关就愈密切USS故样本的相关系数r为:严格地说只有当XY均为随机变量时才能定义相关系数。这样一来在本章的大多数情况下由于我们假设X为非随机变量相关系数根本就无法定义。但一方面不管X是不是随机变量根据式样本相关系数总是可以计算的另一方面后边关于对样本相关系数进行统计检验的推导中也并没有受到X必须为随机变量的限制因此在回归分析中我们就借用了相关系数的名称和公式而不再去区分X是否为随机变量。这一点在使用中是很方便的。根据以前的推导结果有:因此,。当时从上式可看出SSe=即用可以准确预测y值。此时若X不是随机变量则Y也不是随机变量了。这种情况在生物学研究中是不多见的。当r=时SSe=Syy回归一点作用也没有即用X的线性函数完全不能预测Y的变化。但这时X与Y间还可能存在着非线性的关系。当时情况介于上述二者之间隔。X的线性函数对预测Y的变化有一定作用但不能准确预测这说明Y还受其他一些因素包括随机误差的影响。综上所述r可以作为XY间线性关系强弱的一种指标。它的优点是非常直观接近于就是线性关系强接近于就是线性关系弱而其他统计量都需要查表后才知检验结果。由于r是线性关系强弱的指标我们当然希望能用它来进行统计检验。在一般情况下r不是正态分布直接检验有困难。但当总体相关系数ρ=时r的分布近似于正态分布此时用MSe代替就可以对作t检验。这种检验与对回归系数b的检验:是等价的。可证明如下:b的t检验统计量为:t=bSb。b=SxySxx,代入t的表达式得:。因此我们可用上述统计量对作统计检验。为使用方便已根据上述公式编制专门的相关系数检验表可根据剩余自由度及自变量个数直接查出r的临界值。若必须对ρ的情况作统计检验可采用反双曲正切变换:当n充分大时可证明Z渐近正态分布N,其中。利用统计量Z可对等进行检验。但这一检验方法用得很少。例求出例回归系数r并作统计检验。解:利用以前的计算结果可得:这里求得的Z值与例中求得的t值是相同的它们本来就是同一个统计量。查表t()=<t,差异极显著即X与Y有极显著的线性关系。若直接查相关系数检验表可得:剩余自由度为独立自变量为α=的r临界值为,α=的临界值为,差异仍为极显著。相关系数与回归系数间的关系在X和Y均为随机变量的情况下我们通常可以X为自变量Y为因变量建立方程也可反过来以Y为自变量X为因变量建立方程。此时它们的地位是对称的。取X为自变量Y为因变量回归系b为:取Y为自变量X为因变量回归系数b'为:即:相关系数实际是两个回归系数的几何平均值。这正反映了相关与回归的不同:相关是双向的关系而回归是单向的。现在我们已介绍了三种对回归方程作统计检验的方法:对回归系数b作t检验方差方析对相关系数r作检验。对一元线性回归来说它们的基本公式其实是等价的因此结果也是一致的。但它们也各有自己的优缺点:对b的t检验可给出置信区间方差分析在有重复的情况下可分解出纯误差平方和从而可得到进一步的信息相关系数则既直观又方便(有专门表格可查)因此使用广泛。最后要提请注意的一点是不论采用什么检验方法数据都应满足以下三个条件:独立抽自正态总体方差齐性。在本附录中我们将通过一个具体例子运用Excel中的“数据分析”过程说明如何计算相关系数以及如何进行回归分析。例我国–年的城镇居民人均全年耐用消费品支出、人均全年可支配收入以及耐用消费品价格指数的统计资料如下表所示。试建立城镇居民人均全年耐用消费品支出关于可支配收入和耐用消费品价格指数的回归模型并进行回归分析。耐用消费品价格指数人均全年可支配收入人均耐用消费品支年份(元)(元)(年=)资料来源:《中国统计年鉴》一、计算相关系数步骤一:输入数据。打开Excel工作簿将样本观测值输入到A:C单元格中。步骤二:计算相关系数。选择“工具”下拉菜单的“数据分析”选项在分析工具中选择“相关系数”当出现“相关系数”对话框后在“输入区域”中键入A:C在“输出选项”中选择输出区域(这里我们选择“新工作薄”)单击“确定”按钮得下面的相关矩阵表。相关矩阵二、回归分析我们继续说明如何利用Excel进行回归分析。选择“工具”下拉菜单的“数据分析”选项在分析工具中选择“回归”当出现对话框后在“Y值输入区域”方框中键入A:A在“X值输入区域”方框中键入B:C在“输出选项”中选择输出区域(这里我们选择“新工作薄”)单击“确定”按钮得到的结果如下表所示:从表中得到的主要结果有:复相关系数:判定系数:估计的回归方程为:根据括号内的统计量的值可知:对有显著影响而对没有显著影响。根据统计量的值可知:回归方程是显著的。

VIP免券下载文档

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/26

用Excel计算相关系数和进行回归分析

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利