如何在Excel中计算相关系数

如何在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个人的身高和体重之间的相关系数。

如何在Excel中计算相关系数
如何在Excel中计算相关系数

以下是实现此目的的公式:

=CORREL(B2:B12,C2:C12)

Excel 中的相关公式

上述 CORREL 函数接受两个参数——带有身高数据点的系列和带有体重数据点的系列。

就是这样!

只要您按下回车键,Excel 就会在后端进行所有计算,并为您提供一个皮尔逊相关系数数字。

在我们的例子中,该值略大于 0.5,这表明存在相当强的正相关性。

如果您有两个系列并且您想要的只是相关系数,则最适合使用此方法。

但是如果你有多个系列,并且想要找出所有这些系列的相关系数,那么你也可以考虑使用 Excel 中的数据分析工具包(下文将介绍)

使用数据分析工具包

Excel 有一个数据分析工具包,可以用来快速计算各种统计值(包括获取相关系数)。

但是 Excel 默认禁用数据分析工具库。因此,第一步是重新启用数据分析工具,然后使用它在 Excel 中计算皮尔逊相关系数。

推荐:打印机内存不足错误无法打印页面Excel PowerPoint

启用数据分析工具包

以下是在 Excel 中启用数据分析工具包的步骤:

  1. 单击“文件”选项卡单击“文件”选项卡
  2. 点击选项点击选项
  3. 在打开的 Excel 选项对话框中,单击侧栏窗格中的加载项选项点击插件
  4. 在管理下拉菜单中,选择 Excel 加载项选择 Excel 加载项
  5. 点击“前往”。这将打开加载项对话框
  6. 勾选分析工具库选项检查分析工具包
  7. 点击确定

上述步骤将在 Excel 功能区的数据选项卡中添加一个名为“分析”的新组。在此组中,您将拥有数据分析选项

功能区中的数据分析选项

推荐:WordPress搜索插件SearchWP  + Addons

使用数据分析工具包计算相关系数

现在,功能区中又可以使用分析工具了,让我们看看如何使用它来计算相关系数。

假设您有如下所示的数据集,并且想要找出三个系列(身高和体重,身高和收入以及体重和收入)之间的相关性

使用数据分析工具库进行相关性数据

以下是执行此操作的步骤:

  1. 单击数据选项卡单击数据选项卡
  2. 在分析组中,单击数据分析选项点击数据分析图标
  3. 在打开的数据分析对话框中,单击“相关性”在数据分析工具库对话框中选择相关性
  4. 点击确定。这将打开关联对话框
  5. 对于输入范围,选择三个系列 – 包括标题选择输入范围
  6. 对于“分组依据”,请确保已选择“列”选择按列分组
  7. 选择选项 – “第一行标签”。这将确保结果数据具有相同的标题,并且结果更容易理解检查第一行的标签
  8. 在“输出”选项中,选择要将结果表放在何处。我将使用同一工作表上的单元格 G1。您还可以选择将结果放在新工作表或新工作簿中输入输出结果目的地
  9. 单击“确定”

一旦您执行此操作,Excel 就会计算所有系列的相关系数并为您提供如下所示的表格:

输出显示所有系列的相关性

请注意,生成的表格是静态的,如果表格中的任何数据点发生变化,它都不会更新。如果发生任何变化,您将不得不再次重复上述步骤以生成新的相关系数表。

所以这是在 Excel 中计算相关系数的两种快速简便的方法。

推荐:深入探讨WordPress和Shopify之间的主要区别


发表评论