KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中计算两个变量之间的相关系数？

作者孙修改日期

我们通常使用相关系数（取值范围为 ——1 到 1）来衡量两个变量之间线性关系的强度与方向。作为一种广受信赖的统计指标，相关系数能助您洞察成对数据间的关联，例如销售额与广告支出、气温与冰淇淋销量等。在 Excel 中，您可通过多种便捷方式直接计算相关系数，包括使用内置函数或分析工具包。

注意：相关系数为 +1 表示完全正线性关系，即变量 X 增加时，变量 Y 也随之上升；X 减少时，Y 也相应下降。相反，相关系数为 ——1 表示完全负相关，即 X 增加时 Y 减少，反之亦然。而接近 0 的相关系数则表明变量之间几乎不存在线性关系。

方法 A：直接使用 CORREL 函数

方法 B：应用数据分析并输出分析结果

方法 C：使用 PEARSON 函数作为替代方案

方法 D：使用 VBA 代码为多组数据对计算相关系数

方法 A：直接使用 CORREL 函数

假设有两组数据列表，分别代表一个变量。在 Excel 中计算这两个变量之间的相关系数，此方法既快捷又高效。

实际使用时，请确保两个区域均为数值型，且包含相同数量的观测值。例如，如果您有以下成对数据：
示例数据

请选择一个空白单元格用于显示计算结果，然后输入以下公式，并按 Enter 键以计算相关系数：

=CORREL(A2:A7,B2:B7)
使用公式获取相关系数

在此公式中，A2:A7 和 B2:B7 代表您要分析的两组变量。两个区域的长度必须相等，且每对数据需对应同一观测值。

实用提示：CORREL 函数会自动忽略空单元格和文本，但如果两列中缺乏有效的数值对，将返回 #DIV/0! 错误。请确保您的数据正确对齐并包含足够的数值对，以获得准确的相关性计算结果。

计算出相关系数后，您可以插入折线图以直观观察变量间的关系，并进一步解读相关性，如下所示：
插入折线图以查看相关系数

此方法最适合快速手动检查两个小型数据集，或在电子表格中进行交互式操作。对于希望立即获得结果、无需高级统计输出的用户来说，这是理想之选。

kutools for excel AI 的截图

借助 KUTOOLS AI 解锁 Excel 的神奇功能

  • 智能执行：只需输入简单命令，即可执行单元格操作、分析数据并创建图表。
  • 自定义公式：生成量身定制的公式，助您优化工作流程！
  • VBA 编码：轻松编写并运行 VBA 代码。
  • 公式解析：轻松掌握复杂公式，一目了然！
  • 文本翻译：轻松打破电子表格中的语言障碍！
借助 AI 驱动的工具提升您的 Excel 能力。立即下载，体验前所未有的高效！

方法 B：应用数据分析并输出分析结果

如果您需要一次性分析多个变量之间的相关性，或希望获得更全面的输出表格，Excel 的“分析工具库”提供了实用的解决方案。该加载项可一键生成相关矩阵，轻松实现多变量对比，尤其适用于大型数据集或统计报告的构建。

1. 如果您已在“数据”选项卡中添加了数据分析加载项，可直接跳至第 3 步。否则，请单击文件 > 选项。在“Excel 选项”对话框中，从左侧窗格选择加载项，然后单击“Excel 加载项”框旁边的转到按钮。
在 Excel 选项对话框中单击加载项 > 转到

2. 在“加载项”对话框中，勾选分析工具库复选框，然后单击确定，即可在数据选项卡中添加“数据分析”组。
勾选分析工具库

3. 接下来，单击数据 > 数据分析。在弹出的“数据分析”对话框中，从列表中选择相关系数，然后单击确定。
在对话框中选择相关性在对话框中选择相关性

4. 在“相关系数”对话框中，请按以下步骤进行配置：
1) 选择包含数据的区域。
2) 根据数据的组织方式，选择“逐列”或“逐行”选项。
3) 若数据包含标题，请勾选“标志位于第一行”选项。
4) 在“输出选项”中指定结果的显示位置。
在相关性对话框中设置选项

5. 单击确定即可生成相关性分析表，相关系数将显示在限定区域中。
获取分析结果

当您需要评估两个以上变量之间的关系，或需汇总表格以用于报告时，此方法尤为适用。数据分析结果简洁明了，但不提供额外的显著性统计信息。若结果出现异常，请仔细检查数据的一致性、是否存在空单元格，以及所选范围是否正确。

方法 C：使用 PEARSON 函数作为替代方案

除了 CORREL 函数外，Excel 还提供了 PEARSON 函数，同样可用于计算两个变量之间的皮尔逊相关系数。从功能上看，PEARSON 与 CORREL 返回的结果完全相同，但 PEARSON 严格遵循原始数学公式，而 CORREL 则针对 Excel 环境进行了优化。如果您熟悉统计理论，或习惯在 Excel 之外使用统计工具，PEARSON 可能更符合您的预期。

例如，对于 A2:A7 和 B2:B7 中的两组数值列表，您可以按如下方式计算相关性：

1. 选择一个用于显示结果的单元格，然后输入以下公式：

=PEARSON(A2:A7,B2:B7)

2. 按 Enter 键完成计算。如需分析其他数据对，请相应调整单元格范围，或将公式拖动至其他单元格。
提示：PEARSON 函数会忽略文本或逻辑值，请确保两个范围仅包含数值且长度相等。若某列存在缺失数据，请相应对齐范围以避免错误。

对于从其他统计软件迁移而来的用户，或在要求严格遵循术语规范的学术环境中，使用 PEARSON 函数尤为实用。在 Excel 的典型应用场景中，CORREL 与 PEARSON 的计算结果完全一致。

若出现 #DIV/0! 错误，请检查两个区域的长度是否一致，并确保其中不含未匹配的空单元格或非数值单元格。

优点：操作简便，与统计软件保持一致；缺点：对大多数用户而言，与 CORREL 相比并无明显优势。

方法 D：使用 VBA 代码为多组数据对计算相关系数

如果您需要为多组数据对（例如在处理大量变量组合时）自动计算相关系数，编写一个简单的 VBA 宏将是高效之选。此方法尤其适合希望处理大型数据集或自动化重复分析任务的高级用户。

1. 要使用此方法，请首先单击开发工具>Visual Basic，打开 VBA 编辑器。在“Visual Basic for Applications”窗口中，依次选择插入> 模块，然后将以下代码粘贴到模块中：

Sub BatchCalculateCorrelations()
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    Dim lastRow As Long
    Dim i As Long
    Dim resultCol As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
    Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
    Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
    
    If rng1.Rows.Count <> rng2.Rows.Count Then
        MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
        Exit Sub
    End If
    
    For i = 1 To rng2.Columns.Count
        resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
        resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
    Next i
End Sub

2. 插入代码后，关闭 VBA 编辑器。在 Excel 中按 Alt + F8，选择 BatchCalculateCorrelations，然后单击运行，系统将提示您选择：

  • 第一个变量范围（单列，例如 A2:A7）
  • 第二个变量范围（一列或多列，例如 B2:D7）
  • 您希望结果开始显示的单元格（例如 F2）

该宏随后将计算第一个变量与第二个区域中各列之间的相关系数，并从所选单元格开始水平显示结果。

优势：自动处理重复性计算，显著节省处理大型数据集的时间，并确保结果始终一致。

若出现“两个区域必须具有相同的行数”等错误，请确保所选各列的行数完全一致，且不含空白行。如需排查问题，请确认是否已启用宏，并检查区域是否已正确选择。

在 Excel 中处理相关系数时，选择合适的方法取决于您的数据结构和分析需求。对于一次性快速计算两个序列之间的相关性，CORREL 或 PEARSON 等公式高效易用；对于涉及多个变量或需生成汇总表的场景，分析工具库非常实用；若需对大型数据集进行重复分析，或希望构建自定义工作流，则可通过 VBA 自动化节省时间并减少人为错误。
请始终确保所选区域对齐、整洁，且不含空单元格或非数值单元格，以免引发公式错误。如结果异常，请仔细核对所选区域及数据类型。

相关文章

  • 在 Excel 中计算或分配字母等级
    根据学生分数分配字母等级是教师的常见任务。例如，我设定了以下评分标准：0–59 分为 F，60–69 分为 D，70–79 分为 C，80–89 分为 B，90–100 分为 A，了解更多。

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱