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

用Excel计算相关系数和进行回归 第一节 回归和相关的概念 前几章的方法都只涉及一种变量，主要是比较它的各组值之间的差异。但生物学所涉及的问题是多种多样的，对许多问题的研究需要考虑不只一个变量，例如生物的生长发育速度就与温度，营养，湿度 „„ 等许多因素有关，我们常常需要研究类似的多个变量之间的关系。这种关系可分为两大类，即相关关系与回归关系。 相关关系 :两变量 X ， Y 均为随机变量，任一变量的每一可能值都有另一变量的一个确定分布与之对应。 回归关系 : X 是非随机变量或随机变量， Y 是随机变量，对 X 的每一确定值 x i 都有 Y 的一个确定分布与之对应。 从上述定义可看出相关关系中的两个变量地位是对称的，可以认为它们互为因果;而回归关系中则不是这样，我们常称回归关系中的 X 是自变量，而 Y 是因变量。即把 X 视为原因，而把 Y 视为结果。 这两种关系尽管有意义上的不同，分析所用的数学概念与推导过程也有所不同，但如果我们使用共同的即使 y 的残差平方和最小(最小二乘法，详见下述)，则不管是回归关系还是相关关系都可以得到相同的参数估计式。因此本章将集中讨论数学处理较简单的回归关系，且 X 限定为非随机变量。从这些讨论中所得到的参数估计式也可用于 X 为随机变量的情况，但我们不再讨论 X 为随机变量时的证明与推导。 另外，回归分析和相关分析的目的也有所不同。回归分析研究的重点是建立 X 与 Y 之间的数学关系式，这种关系式常常用于预测，即知道一个新的 X 取值，然后预测在此情况下的 Y 的取值;而相关分析的重点则放在研究 X 与 Y 两个随机变量之间的共同变化规律，例如当 X 增大时 Y 如何变化，以及这种共变关系的强弱。由于这种研究目的的不同，有时也会引起标准和方法上的不同，我们将在相关分析一节中作进一步介绍。 从两个变量间相关(或回归)的程度来看，可分为以下三种情况: ( 1 ) 完全相关 。此时一个变量的值确定后，另一个变量的值就可通过某种公式求出来;即一个变量的值可由另一个变量所完全决定。这种情况在生物学研究中是不太多见的。 ( 2 ) 不相关 。变量之间完全没有任何关系。此时知道一个变量的值不能提供有关另一个变量的任何信息。 ( 3 ) 统计相关 (不完全相关)。介于上述两种情况之间。也就是说，知道一个变量的值通过某种公式就可以提供关于另一个变量一些信息，通常情况下是提供有关另一个变量的均值的信息。此时知道一 个变量的取值并不能完全决定另一个变量的取值，但可或多或少地决定它的分布。这是科研中最常遇到的情况。本章讨论主要针对这种情况进行。为简化数学推导，本章中如无特别说明，一律假设 X 为非随机变量，即 X 只是一般数字，并不包含有随机误差。但所得结果可以推广到 X 为随机变量的情况。 两个变数资料的散点图 将两个变数的 n 对观察值 ( x 1 , y 1 ) 、 ( x 2 , y 2 ) 、 „ 、 ( x n , y n ) 分别以坐标点 的形式标记于同一直角坐标平面上得到的图，称为散点图 (scatter diagram) 。 第二节 直线回归 前边已经说过，回归关系就是对每一个 X的取值x i ，都有Y的一个分布与之对应。在这种情况下，怎么建立X与Y的关系呢,一个比较直观的想法就是建立X与Y的分布的参数间的关系，首先是与Y的均值的关系。这就是条件均值的概念，记为: 。它的意思是在X=x 1 的条件下，求Y的均值。更一般地，我们用 代X取一切值时，Y的均值所构成的集合。所谓一元线性回归，就是假定X与 之间的关系是线性关系，而且满足: ( 9.1 ) 此时进行回归分析的目标就是给出参数 α和β的估计值。 [例 9.1] 对大白鼠从出生第6天起，每三天称一次体重，直到第18天。数据见表5.1。试计算日龄X与体重Y之间的回归方程。 表 9.1 大白鼠6-18日龄的体重 序号 1 2 3 4 5 日龄 x i 6 9 12 15 18 体重 y i 11 16.5 22 26 29 首先，我们可以把数对( x i , y i )标在 X-Y 坐标系中，这种图称为散点图。它的优点是可以使我们对 X 、 Y 之间的关系有一个直观的、整体上的印象，如它们是否有某种规律性，是接近一条直线还是一条曲线，等等。我们还可以画很多条接近这些点的直线或曲线，但这些线中的哪一条可以最好地代表 X, Y 之间的关系，就不是凭直观印象可以做出判断的了。例如对例 9.1 ，我们可画出如下的散点图: 图 9.1 大白鼠日龄 —体重关系图 图中的点看来是呈直线关系，但那条直线是否最好地反映了这种关系呢,或者换一种说法:该如何找到最好地反映这种关系的直线呢,这就是我们以下要讨论的问题。 9.2.1 一元正态线性回归统计模型 线性回归意味着条件平均数与 X 之间的关系是线性: ( 9.1 ) 对于每个 Y 的观察值 y i 来说，由于条件均值由 (9.1) 式决定，观察值就应该是在条件均值的基础上再加上一个随机误差，即: ( 9.2 ) 其中 。正态线性回归中 “ 正态 ” 的意思是随机误差服从正态分布。 (9.2) 式就是一元正态线性回归的统计模型。 9.2.2 参数 α和β的估计 统计模型中的α和β是总体参数，一般是不知道的。由于只能得到有限的观察数据，我们无法算出准确的α与β的值，只能求出它们的估计值 a和b，并得到y i 的估计值为: (9.3) 那么，什么样的 a和b是α和β最好的估计呢,换句话说，选取什么样的a和b可以最好地反映X和Y之间的关系呢,一个合理的想法是使残差 最小。为了避免使正负e i 互相抵消，同时又便于数学处理，我们定义使残差平方和 达到最小的直线为回归线，即令: ，且 得: 整理后，得 (9.4) 上式称为正规方程。解此方程，得: 这种方法称为最小二乘法，它也适用于曲线回归，只要将线性模型( 9.3)式换为非线性模型即可。但要注意非线性模型的正规方程一般比较复杂，有些情况下甚至没有解析解。另一方面，不管X与Y间的真实关系是什么样的，使用线性模型的最小二乘法的解总是存在的。因此正确选择模型很重要，而且用最小二乘法得出的结果一般应经过检验。 记 ，称为X的校正平方和; ，称为Y的总校正平方和; ，称为校正交叉乘积和， 则: (9.5) 在实际计算时，可采用以下公式: 现在回到例 9.1 。 例 9.1 对大白鼠从出生第6天起，每三天称一次体重，直到第18天。数据见表9.1。试计算日龄X与体重Y之间的回归方程。 表 9.1 大白鼠6-18日龄的体重 序号 1 2 3 4 5 日龄 x i 6 9 12 15 18 体重 y i 11 16.5 22 26 29 解:把数据代入上述公式 , 得: 即:所求的回归方程为: y = 2.6996 + 1.5167 x 带有统计功能的计算器常常也可以做一元线性回归，对于这样的计算器，只需把数据依次输入，然后按一下键就可得到上述结果。 9.2.3 直线回归的假设测验 在介绍最小二乘法时我们曾提到，不管实际上 X 与 Y 之间有没有线性关系，用这种方法总是可以得到解的。因此我们必须有一种方法可以检验得到的结果是不是反映了 X 和 Y 之间的真实关系。为此，我们需要研究 b 与 a 的期望与方差。 注意 ? 原式 = ? 各 y i 互相独立，且D(y i )=σ 2 ;各x i 为常数; ? 为估计 σ 2 ，令: ，称为残差或剩余。则残差平方和为: 由于 ( ? 交叉项期望为 0 ) 且 D(S xy ) = S xx s 2 , E(S xy ) = b S xx , (已证) 用 MS e (剩余均方)代替 s 2 ，可得 b 与 a 的样本方差: 由于 MS e 的自由度为 n-2 ，因此上述两方差的自由度也均为 n-2 。有了 a 和 b 的方差与均值，我们就可构造统计量对它们进行检验: H 0 : b = 0 H A : b ? 0 (双侧检验) 或: H A : b > 0 (或 b < 0 ) (单侧检验) 统计量: ( 9.6 ) 当 H 0 成立时， t b ~ t(n-2) ，可查相应分位数表进行检验。 H 0 : a = 0 H A : a ? 0 (双侧检验) 或: H A : a > 0 (或 a < 0 ) (单侧检验) 统计量: ( 9.7 ) 当 H 0 成立时， t a ~ t(n-2) ，可查相应分位数表进行检验。 在对一个回归方程的统计检验中，我们更关心的是 b 是否为 0 ，而不是 a 是否为 0 。这是因为若 b = 0 ，则线性模型变为 Y = a + e ，与 X 无关;这意味着 X 与 Y 间根本没有线性关系。反之， a 是否为 0 并不影响 X 与 Y 的线性关系。因此我们常常只对 b 作统计检验。 例 9.2 对例 9.1 中的 b 作检验: H 0 : b =0 解: 查表， t 0.995 (3) = 5.841 < t ， 差异极显著，应拒绝 H 0 ，即 b 1 0 ，或 X 与 Y 有着极显著的线性关系。 9.2.4 直线回归的方差分析 对回归方程的统计检验除可用上述 t 检验外，还有一些其他方法。这里我们再介绍一种方差分析的方法，它的基本思想仍是对平方和的分解。 1 无重复的情况。 y 的总校正平方和可进行如下的分解: 即: Syy = SSe + SSR y 的总校正平方和,残差平方和，回归平方和 自由度: n-1,n-2，1 这样就把 y 的总校正平方和分解成了残差平方和与回归平方和。前已证明， MS e 可作为总体方差 s 2 的估计量，而 MS R 可作为回归效果好坏的评价。如果 MS R 仅由随机误差造成的话，说明回归失败， X 和 Y 没有线性关系;否则它应显著偏大。因此可用统计量 ( 9.8 ) 对 H 0 : b = 0 进行检验。若 F < F a (1, n-2) ，则接受 H 0 ，否则拒绝。 现在我们来证明这里的 F 检验与前述的 t 检验是一致的: 前已证明: SS e = S yy ? b × S xy , SS R = S yy ? SS e = b × S xy , 例 9.4 对例 9.1 作方差分析 解:由以前计算结果: S yy = 210.2 ， df = 4; SS e = 3.1704, df = 3, SS R = 210.2 ?3.1704 = 207.03, df = 1 查表得 F 0.95 (1, 3) = 10.13, F 0.99 (1, 3) = 34.12 F > F 0.99 (1, 3) ，拒绝 H 0 ，差异极显著。即应认为回归方程有效。 2.有重复的情况: 设在每一个 x i 取值上对 Y 作了 m 次观察，结果记为 y i1 , y i2 , „„ y im , 则线性统计模型变为: , i = 1, 2, „ n, j = 1, 2, „ m 估计值仍为: 现在 y的总校正平方和可分解为: S yy = SS R + SS LOF + SS pe 其中 SS LOF 称为失拟平方和， SS pe 为纯误差平方和，它们的表达式和自由度分别为: 同学们可试证明上述分解中的三个交叉项均为 0 。 统计检验步骤为: I. 令 ，它服从 F(n-2, mn-n) ( 9.9 ) 若 F 检验差异显著，则可能的原因有: ( 1 )除 X 以外还有其他变量影响 Y 的取值，而统计时没有加以考虑; ( 2 )模型不当，即 X 与 Y 之间不是线性关系; 此时无必要再进一步对 MS R 作检验，而应想办法找出原因，并把它消除后重作回归。 若差异不显著，则把 MS LOF 和 MS pe 合并，再对 MS R 作检验: II. ，它服从 F(1, mn-2) ( 9.10 ) 若差异显著，说明回归是成功的， X, Y 间确有线性关系;若差异仍不显著，则回归失败，其可能的原因为: ( 1 ) X ， Y 无线性关系; ( 2 )误差过大，掩盖了 X, Y 间的线性关系。 如有必要，可设法减小实验误差，或增加重复数重做实验后再重新回归。 9.2.5 直线回归的区间估计 1 α和β的区间估计 我们已经证明 a 和 b 是 α和β 的点估计，并求出了它们的方差。因此给出置信区间就很容易了: ?β的 95%置信区间为: (9.11) 同理 a 的 95% 置信区间为: (9.12) 这与以前假设检验中的置信区间求法完全一样。若置信水平为 99% ，把分位数相应换为 t 0.995 (n-2) 即可。 例 9.5 对例 9.1 中的 a 和 b 给出 95% 置信区间。 解:从前边的计算可知: a = 2.6996, b = 1.5167, S xx = 90, MS e = 1.0568, n = 5, 查表，得 t 0.975 (3) = 3.182 a 的 95% 置信区间为: 2.6996 ? 4.3887, 即( -1.6891, 7.0883 ) b 的 95% 置信区间为: 1.5167 ? 0.3448, 即( 1.1719, 1.8615 ) 2. 对条件均值 m Y? X 的估计。 的点估计: 证明: 区间估计:首先需求出 的方差。 用 MS e 代替 s 2 ，可得 的 1 ? a 置信区间为: ( 9.13 ) 注意上述置信区间的宽度与 有关，当 时，其宽度最小，偏离 后，逐渐加大。 3. 对一次观察值 y 0 的估计 y 0 的点估计: 证明: 区间估计: 一般情况下置信区间是以随机变量的期望为中点，此时只要求方差就可以了，因为方差就是衡量随机变量以数学期望为中心的离散程度的统计量。而现在是以条件均值 的估计值，即另一个随机变量 为中点，因此应求这两个随机变量差值的方差。由于下一次观察值 y 0 和以前所有的观察值 y i 都是互相独立的，而估计值 是从以前的观察值 y i 计算出来的，因此 与 y 0 独立，从而有: 由于 y 0 和 均为正态分布，它们的差也为正态分布。用 代替 后，为 t 分布，即: 在 x = x 0 处 y 0 的 1- a 置信区间为: (9.14) 显然 y 0 的置信区间宽度也与 x 0 有关， 时最小，偏离 时增大。 y 0 的置信区间比 的大一点，这是因为 y 0 自己也有一个随机误差 e 。 例 9.6 江苏武进县测定 1959-1964 年间 3 月下旬至 4 月中旬平均温度累积值 x 和一代三化螟蛾盛发期 y 的关系如下表 ( 盛发期以 5 月 10 日为起算日 ) :试作回归分析。 表 9.2 平均温度累积值与一代三化螟盛发期 年代 1956 1957 1958 1959 1960 1961 1962 1963 1964 累积温 x 35.5 34.1 31.7 40.3 36.8 40.2 31.7 39.2 44.2 盛发期 y 12 16 9 2 7 3 13 9 –1 解:由原始数据算得: S xx = 144.6356, S yy = 249.5556, S xy = –159.0444, b ? –1.0996, SS R = bS xy = 174.8886 查表，得: F 0.95 (1, 7) = 5.591, F 0.99 (1, 7) = 12.25, F > F 0.99 (1, 7), 拒绝 H 0 ，差异极显著。即 X ， Y 有极显著线性关系。 为把上述回归结果用于预报，可给出观察值 y 0 的 95% 置信区间: 查表，得 t 0.975 (7) = 2.365, 把数据代入上式，得: 条件均值 的 95% 置信区间公式为: 代入数据，得: 把不同的 x 0 取值代入上述公式，可得置信区间的数据及图形如下: 表 9.3 一代三化螟盛发期置信区间 x 0 y 0 y 0 的 95% 置信区间 的 95% 置信区间 下限 上限 下限 上限 30 15.6 10.3 20.8 6.2 24.9 32 13.4 9.2 17.5 4.6 22.1 34 11.2 7.9 14.4 2.8 19.5 36 9.0 6.3 11.6 0.8 17.1 38 6.8 4.1 9.4 -1.4 14.9 40 4.6 1.4 7.8 -3.8 12.9 42 2.4 -1.7 6.4 -6.4 11.1 44 0.2 -5.0 5.3 -9.1 9.4 46 -2.0 -8.3 4.2 -12.0 7.9 图 9.2 一代三化螟盛发期置信区间 回归分析的目的常常是为了预报，也就是说下一次我们知道了 x 0 的取值后，在观察前就对 y 0 的取值作出估计。例如表 9.3 中的数据就是为了预报用的，下一年度如果我们知道了 3 月下旬至 4 月中旬的平均温度累积值，就可以估计出一代三化螟蛾盛发期是 5 月的什么时候。要特别注意的一点是预报范围只能是我们研究过的自变量变化范围，例如在上例中，当积温值是在 32 到 44 的范围内时，使用这一预报公式比较有把握， 30 和 46 使用已有点勉强，再大或小就不能用了。这是因为一般来说直线关系只是局部的近似，在更大的范围内，变量间常常呈现一种非线性的关系。因此若贸然把局部研究中发现的线性关系推广到更大的范围，常常是要犯严重错误的。同时从置信区间的宽度也可看出，即使是在研究的范围内，也是越接近所研究区间的中点( )预报越准确。 第三节 直线相关 9.3.1 相关系数 设有一 X 、 Y 均为随机变量的双变数总体，具有 N 对 ( X ， Y ) 。若在标有这 N 个 ( X ， Y ) 坐标点的直角坐标上移动坐标轴，将 X 轴和 Y 轴分别平移 到 μ X 和 μ Y 上，则各点位置不变，而所取坐标变为 ( X - μ X ， Y- μ Y ) 。 上述三图充分说明的值可用来度量两个变数直线相关程度和性质。但是， X 和 Y 的变异程度、所取单位及 N 的大小都会影响 为了具有可比性，需要将离均差转化为标准化离均差，再以 N 除之，从而得到双变数总体的相关系数为: 从样本的角度分析， y 的平方和 SS y 是由离回归平方和及回归平方和构成的，后者是由 X 的不同而引起。显然，若坐标点愈靠近回归线， 则对y 的比率愈大，直线相关就愈密切，U SS 故样本的相关系数 r 为: 严格地说，只有当 X ， Y 均为随机变量时才能定义相关系数。这样一来，在本章的大多数情况下，由于我们假设 X 为非随机变量，相关系数根本就无法定义。但一方面不管 X 是不是随机变量，根据式样本相关系数总是可以计算的;另一方面后边关于对样本相关系数进行统计检验的推导中，也并没有受到 X 必须为随机变量的限制，因此在回归分析中我们就借用了相关系数的名称和公式，而不再去区分 X 是否为随机变量。这一点在使用中是很方便的。 根据以前的推导结果，有: 因此 , 。 当 时，从上式可看出 SS e = 0 ，即用 可以准确预测 y 值。此时若 X 不是随机变量，则 Y 也不是随机变量了。这种情况在生物学研究中是不多见的。 当 r = 0 时， SS e = S yy ，回归一点作用也没有，即用 X 的线性函数完全不能预测 Y 的变化。但这时 X 与 Y 间还可能存在着非线性的关系。 当 时，情况介于上述二者之间隔。 X 的线性函数对预测 Y 的变化有一定作用，但不能准确预测，这说明 Y 还受其他一些因素，包括随机误差的影响。 综上所述， r 可以作为 X ， Y 间线性关系强弱的一种指标。它的优点是非常直观，接近于 1 就是线性关系强，接近于 0 就是线性关系弱;而其他统计量都需要查表后才知检验结果。 由于 r 是线性关系强弱的指标，我们当然希望能用它来进行统计检验。在一般情况下 r 不是正态分布，直接检验有困难。但当总体相关系数 ρ = 0 时， r 的分布近似于正态分布，此时用 MSe 代替 ，就可以对 作 t 检验。这种检验与对回归系数 b 的检验: 是等价的。可证明如下: b 的 t 检验统计量为: t = b/S b 。 b=S xy /S xx , 代入 t 的表达式，得: 。 因此我们可用上述统计量对 作统计检验。 为使用方便，已根据上述公式编制专门的相关系数检验表，可根据剩余自由度及自变量个数直接查出 r 的临界值。 若必须对 ρ? 0 的情况作统计检验，可采用反双曲正切变换: 当 n 充分大时，可证明 Z 渐近正态分布 N , 其中 。利用统计量 Z 可对 等进行检验。但这一检验方法用得很少。 例 9.7 求出例 9.1 回归系数 r ，并作统计检验。 解:利用以前的计算结果，可得: 这里求得的 Z 值与例 9.2 中求得的 t 值是相同的，它们本来就是同一个统计量。 查表， t 0.995 (3) = 5.841 < t, ? 差异极显著，即 X 与 Y 有极显著的线性关系。 若直接查相关系数检验表，可得:剩余自由度为 3 ，独立自变量为 1 ， α =0.05 的 r 临界值为 0.878, α =0.01 的临界值为 0.959, ? 差异仍为极显著。 9.3.2 相关系数与回归系数间的关系 在 X 和 Y 均为随机变量的情况下，我们通常可以 X 为自变量， Y 为因变量建立方程，也可反过来，以 Y 为自变量， X 为因变量建立方程。此时它们的地位是对称的。 取 X 为自变量， Y 为因变量，回归系 b 为: 取 Y 为自变量， X 为因变量，回归系数 b' 为: 即:相关系数实际是两个回归系数的几何平均值。这正反映了相关与回归的不同:相关是双向的关系，而回归是单向的。 现在我们已介绍了三种对回归方程作统计检验的方法:对回归系数 b 作 t 检验，方差方析，对相关系数 r 作检验。对一元线性回归来说，它们的基本公式其 实是等价的，因此结果也是一致的。但它们也各有自己的优缺点:对 b 的 t 检验可给出置信区间;方差分析在有重复的情况下可分解出纯误差平方和，从而可得到进一步的信息;相关系数则既直观，又方便(有专门可查)，因此使用广泛。 最后要提请注意的一点是，不论采用什么检验方法，数据都应满足以下三个条件:独立，抽自正态总体，方差齐性。 在本附录中，我们将通过一个具体例子，运用Excel中的“数据分析”过程，说明如何计算相关系数以及如何进行回归分析。 例 我国1988–1998年的城镇居民人均全年耐用消费品支出、人均全年可支配收入以及耐用消费品价格指数的统计资料如下表所示。试建立城镇居民人均全年耐用消费品支出关于可支配收入和耐用消费品价格指数的回归模型，并进 行回归分析。 耐用消费品价格指数 人均全年可支配收入 人均耐用消费品支 年 份 (元) (元) (1987年=100) 1988 137.16 1181.4 115.96 1989 124.56 1375.7 133.35 1990 107.91 1510.2 128.21 1991 102.96 1700.6 124.85 1992 125.24 2026.6 122.49 1993 162.45 2577.4 129.86 1994 217.43 3496.2 139.52 1995 253.42 4283.0 140.44 1996 251.07 4838.9 139.12 1997 285.85 5160.3 133.35 1998 327.26 5425.1 126.39 资料来源:《中国统计年鉴》 一、计算相关系数 步骤一:输入数据。 打开Excel工作簿，将样本观测值输入到A2:C12单元格中。 步骤二:计算相关系数。 1. 选择“工具”下拉菜单的“数据分析”选项; 2. 在分析工具中选择“相关系数”; 3. 当出现“相关系数”对话框后， ? 在“输入区域”中键入A2:C12; ? 在“输出选项”中选择输出区域(这里我们选择“新工作薄”); ? 单击“确定”按钮，得下面的相关矩阵表。 相关矩阵 1.000000 0.970809 1.000000 0.473984 0.55204 1.000000 二、回归分析 我们继续说明如何利用Excel进行回归分析。 1. 选择“工具”下拉菜单的“数据分析”选项; 2. 在分析工具中选择“回归”; 3. 当出现对话框后， ?在“Y值输入区域”方框中键入A2:A12; ?在“X值输入区域”方框中键入B2:C12; ?在“输出选项”中选择输出区域(这里我们选择“新工作薄”); ?单击“确定”按钮，得到的结果如下表所示: 从表中得到的主要结果有: 复相关系数:， 判定系数:， 估计的回归方程为: 根据括号内的统计量的值可知:对有显著影响，而对没有显著影 响。 根据统计量的值可知:回归方程是显著的。