如何在Excel中计算相关系数
Excel 是一个功能强大的工具,在处理统计时具有一些令人惊叹的功能和性能。
在处理大型数据集时,找到两个数据系列之间的相关性是最常见的统计计算之一,
几年前,我曾担任过一名金融分析师,尽管我们并没有深入地参与统计数据,但寻找相关性仍然是我们必须经常做的事情。
在本教程中,我将向你展示两种非常简单的方法 在 Excel 中计算相关系数。已经有一个内置函数可以执行此操作,您也可以使用数据分析工具包。
推荐:在Microsoft Excel中将负数显示为红色的5种方法
相关系数是什么?
由于这不是统计课,让我简单解释一下什么是相关系数,然后我们再进入在 Excel 中计算相关系数的部分。
相关系数是一个告诉您两个数据系列之间的关联程度的值。
一个常用的例子是一组10个人的体重和身高。如果我们计算这些人的身高和体重数据的相关系数,我们会得到一个介于-1和1之间的值。
小于零的值表示负相关,这意味着如果身高增加则体重减少,或者如果体重增加则身高减少。
大于零的值表示正相关,这意味着如果身高增加,则体重也增加,如果身高下降,则体重下降。
值越接近 1,正相关性越强。因此,值为 0.8 表示身高和体重数据高度相关。
注意:相关系数和统计数据有多种类型,但在本教程中,我们将研究最常见的类型,即皮尔逊相关系数
现在,让我们看看如何在 Excel 中计算这个相关系数。
Also read: Weighted Average Formula in Excel
在 Excel 中计算相关系数
正如我提到的,有几种方法可以在 Excel 中计算相关系数。
使用 CORREL 公式
CORREL 是 Excel 2007 中引入的统计函数。
假设您有一个如下所示的数据集,您想要计算10个人的身高和体重之间的相关系数。
以下是实现此目的的公式:
=CORREL(B2:B12,C2:C12)
上述 CORREL 函数接受两个参数——带有身高数据点的系列和带有体重数据点的系列。
就是这样!
只要您按下回车键,Excel 就会在后端进行所有计算,并为您提供一个皮尔逊相关系数数字。
在我们的例子中,该值略大于 0.5,这表明存在相当强的正相关性。
如果您有两个系列并且您想要的只是相关系数,则最适合使用此方法。
但是如果你有多个系列,并且想要找出所有这些系列的相关系数,那么你也可以考虑使用 Excel 中的数据分析工具包(下文将介绍)
使用数据分析工具包
Excel 有一个数据分析工具包,可以用来快速计算各种统计值(包括获取相关系数)。
但是 Excel 默认禁用数据分析工具库。因此,第一步是重新启用数据分析工具,然后使用它在 Excel 中计算皮尔逊相关系数。
推荐:打印机内存不足错误无法打印页面Excel PowerPoint
启用数据分析工具包
以下是在 Excel 中启用数据分析工具包的步骤:
- 单击“文件”选项卡
- 点击选项
- 在打开的 Excel 选项对话框中,单击侧栏窗格中的加载项选项
- 在管理下拉菜单中,选择 Excel 加载项
- 点击“前往”。这将打开加载项对话框
- 勾选分析工具库选项
- 点击确定
上述步骤将在 Excel 功能区的数据选项卡中添加一个名为“分析”的新组。在此组中,您将拥有数据分析选项
使用数据分析工具包计算相关系数
现在,功能区中又可以使用分析工具了,让我们看看如何使用它来计算相关系数。
假设您有如下所示的数据集,并且想要找出三个系列(身高和体重,身高和收入以及体重和收入)之间的相关性
以下是执行此操作的步骤:
- 单击数据选项卡
- 在分析组中,单击数据分析选项
- 在打开的数据分析对话框中,单击“相关性”
- 点击确定。这将打开关联对话框
- 对于输入范围,选择三个系列 – 包括标题
- 对于“分组依据”,请确保已选择“列”
- 选择选项 – “第一行标签”。这将确保结果数据具有相同的标题,并且结果更容易理解
- 在“输出”选项中,选择要将结果表放在何处。我将使用同一工作表上的单元格 G1。您还可以选择将结果放在新工作表或新工作簿中
- 单击“确定”
一旦您执行此操作,Excel 就会计算所有系列的相关系数并为您提供如下所示的表格:
请注意,生成的表格是静态的,如果表格中的任何数据点发生变化,它都不会更新。如果发生任何变化,您将不得不再次重复上述步骤以生成新的相关系数表。
所以这是在 Excel 中计算相关系数的两种快速简便的方法。