数据分析之相关系数

相关表和散点图可反映两个变量之间的相互关系及其相关方向，但无法确切地表明两个变量之间相关的程度。相关系数是用以反映两个变量之间线性相关关系密切程度的统计指标。相关系数的计算公式为：

相关系数的计算方法在Excel中有多种，但均以公式（7－1）为基础。下面介绍三种常见相关系数计算方法，以例7.3的数据说明相关系数的计算方法。

1.用常规方法计算相关系数

例7.3 根据图7－4中A1：C13区域数据，采用常规方法计算相关系数。

（1）建立数据文件

新建一工作表，在A1：C13、D1：F1区域输入如图7－4所示相应区域数据。

（2）计算自变量平方、因变量平方、自变量与因变量乘积

在D2单元格中输入“＝B2B2”，按Enter键；在E2单元格中输入“＝C2C2”，按Enter键；在F2单元格中输入“＝B2C2”，按Enter键。

用鼠标选中D2：F2，鼠标移至F2单元格右下角，待鼠标变为“╋”时，按住鼠标左键向下拖至F13单元格，计算出D2：F13区域数据。

（3）计算自变量平方和（∑x2）、因变量平方和（平方和（∑y2）、变量乘积和（∑xy）

图7－4 相关系数计算表

在A14单元格中输入“合计”，单击B14单元格，单击【∑自动求和】图标，按Enter键，计算出B2：B13数据的合计“1025”，鼠标移至B14单元格右下角，待鼠标变为“╋”时，按住鼠标左键，向下拖至F14单元格，计算出C14：F14区域数据。

（4）根据公式计算相关系数

根据公式（7－1）分子部分，在H2单元格中输入“＝12F14－B14C14”，按Enter键，显示72103；在H3单元格中输入“＝SQRT（（12*D14－B14*B14）*（12*E14－C14*C14））”，按Enter键，显示78353.03，得出公式（7－1）分母部分（注：SQRT（）是计算平方根函数）；在H4单元格中输入“＝H2/H3”，按Enter键，得到相关系数为0.920232。

2.用函数CORREL（）计算相关系数

例7.4 根据图7－4中B1：C13区域数据，用CORREL（）计算相关系数。

（1）确定单元格

单击一空白单元格，如C16单元格。

（2）插入相关系数函数CORREL（）

单击编辑栏左侧的【fx】，显示【插入函数】对话框；单击【或选择函数类别（C）】文本框右侧的“▼”，显示函数类别，按右侧滚动条，选择【统计】函数，找到【CORREL】，单击【确定】按钮，出现如图7－5所示对话框。

（3）输入CORREL（）参数

将光标置于【Array1】右侧文本框中，用鼠标选择B2：B13单元格区域；再将光标置于【Array2】右侧文本框中，用鼠标选择C2：C13单元格区域，单击【确定】按钮，得出相关系数为0.920232。

3.用【相关系数】数据分析工具计算相关系数

若出现多个的变量，需要计算的相关系数比较多，而Correl（）函数一次只能计算一个相关系数，在这种情况下用函数计算相关系数比较麻烦。Excel的【数据分析】工具中还提供了【相关系数】分析工具，一次可以计算出多个相关系数。【相关系数】数据分析工具的操作步骤见例7.5。

图7－5 CORREL函数

例7.5 根据图7－4中B1：C13区域的数据，用【相关系数】数据分析工具计算相关系数。

（1）建立数据文件

如图7－4所示，在单元格区域A1：C13中输入相应的数据。

（2）调用【相关系数】数据分析工具

依次单击功能区选项【数据】→【数据分析】，在弹出的【数据分析】对话框中选择【相关系数】，单击【确定】按钮，弹出如图7－6所示的【相关系数】对话框。

图7－6 【相关系数】分析工具

（3）输入【相关系数】数据分析工具相应的参数

单击【输入区域（I）】左侧文本框，将光标置于其中，然后用鼠标选择B1：C13单元格区域。

鼠标选中单选框【标志位于第一行（L）】前面的“□”，使“□”里面出现一个“√”。（注：如果在【输入区域（I）】的文本框中输入的单元格区域为B2：C13，则该项不用选。）

单击【输出区域（O）】前的单选框，使“○”里面出现一个“·”，然后单击【输出区域（O）】右侧文本框，将光标置于其中，再单击工作表的一空白单元格，如E18单元格，最后单击【确定】按钮，则得计算结果，如图7－7所示。

图7－7 【相关系数】分析工具运算结果

为判断样本相关系数能否代表总体相关系数，需对求得的相关系数进行显著性检验。首先按公式（7－2）求t统计量：

例7.6 检验例7.5中的相关系数是否显著。

单击一空白单元格，如在H5单元格中输入：“＝0.9202*SQRT（12－2）/SQRT（1－0.9202*0.9202）”，按Enter键，得t统计量为7.4337431，然后按显著性水平0.05、自由度12－2＝10，在一空白单元格中输入“＝T．INV（0.05，10）”，按Enter键，得临界值2.2281。t统计量大于t临界值，表明产量与生产费用之间的相关系数是显著的。