爱华网Excel分析工具库系列教程之15
相关系数
重庆三峡学院 关文忠
1.相关系数的概念
著名统计学家卡尔·皮尔逊设计了统计指标——相关系数(Correlationcoefficient)。相关系数是用以反映变量之间相关关系密切程度的统计指标。相关系数是按积差方法计算，同样以两变量与各自平均值的离差为基础，通过两个离差相乘来反映两变量之间相关程度；着重研究线性的单相关系数。
依据相关现象之间的不同特征，其统计指标的名称有所不同。如将反映两变量间线性相关关系的统计指标称为相关系数（相关系数的平方称为判定系数）；将反映两变量间曲线相关关系的统计指标称为非线性相关系数、非线性判定系数；将反映多元线性相关关系的统计指标称为复相关系数、复判定系数等。相关系数的计算公式为：
复相关系数(multiple correlationcoefficient)：反映一个因变量与一组自变量(两个或两个以上)之间相关程度的指标。它是包含所有变量在内的相关系数。它可利用单相关系数和偏相关系数求得。其计算公式为：
当只有两个变量时，复相关系数就等于单相关系数。Excel中的相关系数工具是单相关系数。
2.相关系数工具的使用
CORREL 和 PEARSON工作表函数均可计算两个测量值变量之间的相关系数，条件是每种变量的测量值都是对 N个对象进行观测所得到的。（丢失任何对象的任何观测值都会导致在分析中忽略该对象。）相关系数分析工具特别适合于当 N个对象中的每个对象都有两个以上的测量值变量的情况。它提供一张输出表（相关矩阵），其中显示了应用于每个可能的测量值变量对的CORREL（或 PEARSON）值。
与协方差一样，相关系数是描述两个测量值变量之间的离散程度的指标。与协方差的不同之处在于，相关系数是成比例的，因此它的值与这两个测量值变量的表示单位无关。（例如，如果两个测量值变量为重量和高度，当重量单位从磅换算成千克时，相关系数的值并不改变。）任何相关系数的值都必须介于-1 和 +1 之间（包括 -1 和 +1）。
可以使用相关系数分析工具来检验每对测量值变量，以便确定两个测量值变量是否趋向于同时变动，即，一个变量的较大值是否趋向于与另一个变量的较大值相关联（正相关）；或者一个变量的较小值是否趋向于与另一个变量的较大值相关联（负相关）；或者两个变量的值趋向于互不关联（相关系数近似于零）。
【例】9个小麦品种(分别用A1,A2,...,A9表示)的6个性状资料见表，作相关系数计算。
表 15‑1 试验数据
性状 | A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8 | A9 |
X1(冬季分蘖） | 11.5 | 9.0 | 7.5 | 9.1 | 11.6 | 13.0 | 11.6 | 10.7 | 11.1 |
X2（株高） | 95.3 | 97.7 | 110.7 | 89.0 | 88.0 | 87.7 | 79.7 | 119.3 | 87.7 |
X3（每穗粒数） | 26.4 | 30.8 | 39.7 | 35.4 | 29.3 | 24.6 | 25.6 | 29.9 | 32.3 |
X4（千粒重） | 39.2 | 46.8 | 39.1 | 35.3 | 37 | 44.8 | 43.7 | 38.8 | 35.6 |
X5（抽穗期） | 4/9 | 4/17 | 4/17 | 2/9 | 1/5 | 4/19 | 4/19 | 4/19 | 2/9 |
X6（成熟期） | 6/2 | 6/6 | 6/3 | 6/2 | 6/7 | 6/7 | 6/5 | 6/5 | 6/3 |
（1）建立一张工作表，输入数据：
图 15‑1 EXCEL数据输入
（2）从“数据”选项卡选择“数据分析”，从“数据分析”列表框中选择“相关系数”，单击“确定”弹出相关系数对话框。
图 15‑2 相关系数对话框
数据区域可包括变量名称，但不包括样本编号。每个变量的样本按行排列的选择“逐行”按列排列的选择“逐列”。若包括变量名称，则选择“标志位于第一列”
（3）单击“确定”得偏相关系数如下表。
图 15‑3 结果输出
Excel分析工具中的“相关系数”仅计算出相关系数的值，并未进行相关性检验。相关系数检验可由相关系数临界值来判断。
相关系数为可决系数的平方根，可决系数为回归平方和与总误差平方和之比，而F统计量为回归均方和与总均方和之比，由于可借助F临界值求得相关系数临界值。即：
本例中n=9,在G9单元格输入=SQRT(FINV(0.05,1,7)/(FINV(0.05,1,7)+7))，在B12输入=IF(ABS(B4)>$G$9,"相关性显著","不显著")并复制，得：
图 15‑4 由函数判断显著性
