借助 Excel 内置函数和加载项，如今即便是最先进的分析也能在 Excel 中完成。对于初入门的数据科学从业者，熟悉 Excel 的各类分析技术已变得至关重要。
本教程将介绍“相关性”的统计概念、其不同类型及应用。在介绍相关内置函数和加载项后，我们将使用示例数据在 Excel 中创建、可视化并解读相关性矩阵。
什么是相关性矩阵？
相关性是一种统计度量，用于描述两个或多个变量之间相互关联的程度。它指示变量之间关系的强度和方向。
当变量存在相关性时，意味着一个变量的变化与另一个变量的变化相关——可能为正相关也可能为负相关：
- 正相关：当两个变量同时上升或同时下降时，它们呈正相关。例如，身高与体重通常呈正相关；身高增加时，体重也往往随之增加。
- 负相关：当一个变量增加而另一个变量减少。例如，车辆行驶速度与到达目的地所需时间；速度增加时，所需时间会减少。
相关系数是量化这种关系强度与方向的数值指标。可用多种统计方法来度量相关性，其中最常见的是皮尔逊相关系数。
理解皮尔逊系数
皮尔逊相关系数（常简称为“Pearson’s r”）用于度量两个变量 𝑋 和 𝑌 之间的线性相关程度。它量化了这两个变量之间的关系在多大程度上可以用一条直线来描述。
皮尔逊相关系数的计算方式是：两个变量的协方差除以它们标准差的乘积。数学表达为：
注意，𝑋‾ 和 𝑌‾ 分别表示变量 𝑋 与 𝑌 的均值。
计算得到的相关系数取值范围为 -1 到 +1，其中：
- +1 表示完全正线性关系：一个变量增加时，另一个变量以完全线性的方式增加。
- -1 表示完全负线性关系：一个变量增加时，另一个变量以完全线性的方式减少。
- 0 表示无线性相关：变量之间不存在线性关系。
从可视化角度来看，不同类型的相关性大致如下所示：
相关性的可视化示意（来源）
现在我们已经理解了相关性与相关系数等术语，接下来看看它们如何组合成相关性矩阵。
相关性矩阵是一张表，用于展示多个变量之间的相关系数。矩阵中的每个单元格都表示两变量之间的相关性。
该矩阵是统计数据分析中用于分析变量间关系强度与方向的有用工具。除了这一显而易见的用途外，相关性矩阵还广泛应用于数据科学、金融、市场调研等领域。
部分应用包括：
- 在数据科学中，我们用它来根据变量间的关系选择或排除特征。特征高度相关会在回归模型中引入多重共线性，从而可能扭曲结果。通过识别这些相关性，可以减少冗余并提升模型表现。
- 在金融领域，相关性矩阵用于了解不同资产之间的联动。这对投资组合的分散化和风险管理至关重要，有助于选择不强相关的资产，从而潜在降低风险。
- 企业使用相关性矩阵来发现不同消费者行为、产品评分与人口统计变量之间的关系，以改进产品的目标定位与市场定位。稍后我们将看到类似示例。
理解了相关性的概念、计算与应用后，下面进入其在 Excel 中的实现。
相关性矩阵：Excel 中的相关函数与加载项
在 Excel 中计算相关性矩阵，我们将使用两个工具：CORREL 函数和 Analysis ToolPak 加载项。
CORREL 函数
CORREL 函数提供了一种直接的方法来计算两组数据之间的皮尔逊相关系数。
CORREL 函数的语法为：
CORREL(array1, array2)
其中：
- array1：包含第一组数据/列的单元格区域。
- array2：包含第二组数据/列的单元格区域。
两数组元素数量必须一致。Excel 将返回介于 -1 到 +1 之间的相关系数。
若需更全面的统计分析（包括针对多个变量生成完整的相关性矩阵），可使用 Excel 的 Analysis ToolPak。
Analysis ToolPak 可能未在您的 Excel 文档中启用。请检查“数据”选项卡右侧是否可见“数据分析”图标，如下图所示。
Excel 中的 Analysis ToolPak。
如果看不到也不用担心；按照以下步骤即可启用 Analysis ToolPak。
- 从菜单中点击
File，然后选择 Options。
在“文件”选项卡中选择“选项”。
- 在
Excel Options 对话框中，选择 Add-ins。
在 Excel 选项对话框中选择“加载项”。
- 在视图底部的
Manage 框中，选择 Excel Add-ins，然后点击 Go。
管理 Excel 加载项。
- 在
Add-Ins 框中勾选 Analysis ToolPak，然后点击 OK。
启用 Analysis ToolPak。
若已按说明正确启用，您应能在 Data 选项卡的 Analysis 组下看到“数据分析”图标。
稍后您将看到，Analysis ToolPak 能让计算相关性矩阵的过程更简单、更直接。
如何在 Excel 中创建相关性矩阵：一个示例
设想您是某领先消费品公司的数据分析师，该公司在全国拥有多家门店，并已汇总了销售与客户人口统计数据。
您的目标是理解客户特征与其购买模式之间的关系。本次分析旨在找出经常被共同购买的产品，并探究各类人口统计因素对销售趋势的影响。
您决定使用相关性矩阵开展分析。以下是 Excel 文件中提供的 10 位客户的示例数据：
来自一家领先消费品公司的示例数据。
各列含义如下：
- Customer ID：每位客户的唯一标识。
- Age：客户年龄（岁）。
- Annual Income (K$)：客户年收入（千美元）。
- Frequency of Visits (per month)：客户每月到店频次。
- Spend per Visit ($)：客户每次到店的平均消费金额。
- Electronics ($) / Clothing ($) / Groceries ($)：每次到店在不同品类上的消费金额。
使用 CORREL 函数手动计算相关性
在 Excel 中计算皮尔逊相关系数的步骤如下：
- 步骤 1：选择要填入相关性矩阵的合适单元格。这里我们选择 B16。
选择用于计算相关性的单元格。
- 步骤 2：输入使用 CORREL 函数的公式。我们先计算 Age 与 Annual Income 的相关性。
=CORREL(B2:B11,C2:C11)
输入公式。
- 步骤 3：回车确认，并对其他列的所有组合重复此过程。
计算相关性。
如您所见，若需对每个列组合手动计算相关值会相当繁琐，尤其在数据列数量增加时更是如此。
我们也可以使用 OFFSET 函数 来自动调整单元格区域，从而在表格中填充系数值。
尽管了解并在逐个相关性计算时使用 CORREL 函数很重要，但在处理完整的相关性矩阵时，Analysis ToolPak 更加简便快捷，因此更为推荐。
创建相关性矩阵的步骤如下：
- 步骤 1：在“数据”选项卡下点击“数据分析”图标。
点击“数据分析”图标。
- 步骤 2：选择“Correlation”选项并点击“OK”。
选择相关性分析选项。
- 步骤 3：提供输入区域，并包含列名；勾选“Labels in first row”。输出区域选择希望显示结果的任意单元格（此处为 $A$14）。最后点击 OK。
在 Analysis ToolPak 加载项中填写相关性分析的参数。
您应能看到如下所示的相关性矩阵：
数据及其相关性矩阵。
任一列与其自身的相关值为 1，表示与自身完全相关。矩阵在行中也包含所有列，从而构成各列两两之间的所有组合。矩阵的右上三角为空，因为其与左下三角的组合完全对称。
我们已在 Excel 中成功创建了相关性矩阵。现在，是时候理解这些数值的含义，并将其转化为洞见。
如何在 Excel 中解读相关性矩阵
解读上述相关性矩阵并不困难，但当列数增多时就会变得棘手。列数很高时，不可能逐一查看每个组合。
我们需要一种机制，能在众多可能组合中快速识别最重要的相关性。
对相关性矩阵中的单元格进行条件格式设置，可以将注意力引导至最显著的单元格（色彩更深的部分），从而帮助更好地解读矩阵。
以下是在 Microsoft Excel 中为相关性矩阵着色的步骤：
- 步骤 1：选中相关性矩阵（仅数值部分），在“开始”选项卡下点击“条件格式”。按下图所示点击“新建规则”。
点击“条件格式”图标。
- 步骤 2：完成步骤 1 后，您应看到如下对话框。首先选择“根据各自值设置所有单元格的格式”，并将格式样式设置为“三色刻度”。三种颜色较为理想，分别可表示正相关、负相关与中性相关。最后按下图设置 Type、Value 和 Color。
创建新的条件格式规则。
您将看到如下输出：
示例数据及着色后的相关性矩阵。
就是这样！我们已经通过配色来更好地可视化相关性矩阵。
在完成着色以便于视觉理解之后，我们可以从结果中得出以下结论：
- 年龄与年收入呈显著正相关，意味着客户年龄越大，购买力越强。
- 在除到店频次外的所有变量上，杂货支出均呈负相关；而与到店频次呈正相关，这意味着客户到店越频繁，越可能购买更多杂货。
- 服装与电子产品两个品类呈强正相关，因此将这两类商品在门店中靠近陈列，可能有助于提升销售。
- 服装与杂货两个品类呈负相关，说明在线下门店中不宜将这两类品类相互靠近。
解读相关性矩阵的关键在于观察矩阵中数值的方向和强度，并回溯到与之对应的列。
请注意，我们的说明从未声称“因为年龄更大所以年收入更高”，或“服装销量更高会导致杂货销量更低”。这是因为相关性并不等于因果关系，这是一个常见误解。
通过相关分析，我们只能解读两个变量之间关系的存在（或不存在），而无法断言一个变量导致了另一个变量的改变。要确定因果关系，需要进行特定实验。您可以通过我们的“相关 vs 因果”教程了解更多。
结语
本教程介绍了相关性的概念、皮尔逊系数及其数学表达、相关性的不同类型，以及如何根据相关系数的方向和强度来识别它们。
随后我们转向实现层面，使用示例数据创建相关性矩阵，并通过条件格式进行可视化和解读，同时也指出了在解读相关性矩阵时常见的误区。
学习不止于此！如果您有兴趣用其他编程语言实现相关性分析，以下指南将有所帮助：
要巩固相关技术知识，请查看我们的入门级概率与统计课程。或者，如果您对技术概念已有把握，但希望提升在 Excel 中的实操能力，不妨看看Excel 基础学习路径。
%22%3E%3Cpath%20d%3D%22m-369%200h408v79h-408z%22%20fill%3D%22%237933ff%22%2F%3E%3Cpath%20d%3D%22m-148%200h306v45h-306z%22%20fill%3D%22%23974dff%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%2F%3E%3C%2FclipPath%3E%3Cg%20clip-path%3D%22url(%23a)%22%3E%3Cpath%20d%3D%22m76%200h484v36h-484z%22%20fill%3D%22%23974dff%22%2F%3E%3Cpath%20d%3D%22m0%200h384v24h-384z%22%20fill%3D%22%2306bdfc%22%20transform%3D%22matrix(1%200%200%20-1%200%2060)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
借助 Excel 内置函数和加载项，如今即便是最先进的分析也能在 Excel 中完成。对于初入门的数据科学从业者，熟悉 Excel 的各类分析技术已变得至关重要。
本教程将介绍“相关性”的统计概念、其不同类型及应用。在介绍相关内置函数和加载项后，我们将使用示例数据在 Excel 中创建、可视化并解读相关性矩阵。
什么是相关性矩阵？
相关性是一种统计度量，用于描述两个或多个变量之间相互关联的程度。它指示变量之间关系的强度和方向。
当变量存在相关性时，意味着一个变量的变化与另一个变量的变化相关——可能为正相关也可能为负相关：
相关系数是量化这种关系强度与方向的数值指标。可用多种统计方法来度量相关性，其中最常见的是皮尔逊相关系数。
理解皮尔逊系数
皮尔逊相关系数（常简称为“Pearson’s r”）用于度量两个变量 𝑋 和 𝑌 之间的线性相关程度。它量化了这两个变量之间的关系在多大程度上可以用一条直线来描述。
皮尔逊相关系数的计算方式是：两个变量的协方差除以它们标准差的乘积。数学表达为：
注意，𝑋‾ 和 𝑌‾ 分别表示变量 𝑋 与 𝑌 的均值。
计算得到的相关系数取值范围为 -1 到 +1，其中：
从可视化角度来看，不同类型的相关性大致如下所示：
现在我们已经理解了相关性与相关系数等术语，接下来看看它们如何组合成相关性矩阵。
相关性矩阵是一张表，用于展示多个变量之间的相关系数。矩阵中的每个单元格都表示两变量之间的相关性。
该矩阵是统计数据分析中用于分析变量间关系强度与方向的有用工具。除了这一显而易见的用途外，相关性矩阵还广泛应用于数据科学、金融、市场调研等领域。
部分应用包括：
理解了相关性的概念、计算与应用后，下面进入其在 Excel 中的实现。
相关性矩阵：Excel 中的相关函数与加载项
在 Excel 中计算相关性矩阵，我们将使用两个工具：CORREL 函数和 Analysis ToolPak 加载项。
CORREL 函数
CORREL 函数提供了一种直接的方法来计算两组数据之间的皮尔逊相关系数。
CORREL 函数的语法为：
CORREL(array1, array2)其中：
两数组元素数量必须一致。Excel 将返回介于 -1 到 +1 之间的相关系数。
Analysis ToolPak
若需更全面的统计分析（包括针对多个变量生成完整的相关性矩阵），可使用 Excel 的 Analysis ToolPak。
Analysis ToolPak 可能未在您的 Excel 文档中启用。请检查“数据”选项卡右侧是否可见“数据分析”图标，如下图所示。
Excel 中的 Analysis ToolPak。
如果看不到也不用担心；按照以下步骤即可启用 Analysis ToolPak。
File，然后选择Options。在“文件”选项卡中选择“选项”。
ExcelOptions对话框中，选择Add-ins。在 Excel 选项对话框中选择“加载项”。
Manage框中，选择ExcelAdd-ins，然后点击Go。管理 Excel 加载项。
Add-Ins框中勾选Analysis ToolPak，然后点击OK。启用 Analysis ToolPak。
若已按说明正确启用，您应能在
Data选项卡的Analysis组下看到“数据分析”图标。稍后您将看到，Analysis ToolPak 能让计算相关性矩阵的过程更简单、更直接。
如何在 Excel 中创建相关性矩阵：一个示例
设想您是某领先消费品公司的数据分析师，该公司在全国拥有多家门店，并已汇总了销售与客户人口统计数据。
您的目标是理解客户特征与其购买模式之间的关系。本次分析旨在找出经常被共同购买的产品，并探究各类人口统计因素对销售趋势的影响。
您决定使用相关性矩阵开展分析。以下是 Excel 文件中提供的 10 位客户的示例数据：
来自一家领先消费品公司的示例数据。
各列含义如下：
使用 CORREL 函数手动计算相关性
在 Excel 中计算皮尔逊相关系数的步骤如下：
选择用于计算相关性的单元格。
=CORREL(B2:B11,C2:C11)输入公式。
计算相关性。
如您所见，若需对每个列组合手动计算相关值会相当繁琐，尤其在数据列数量增加时更是如此。
我们也可以使用 OFFSET 函数 来自动调整单元格区域，从而在表格中填充系数值。
尽管了解并在逐个相关性计算时使用 CORREL 函数很重要，但在处理完整的相关性矩阵时，Analysis ToolPak 更加简便快捷，因此更为推荐。
使用 Analysis ToolPak 生成相关性矩阵
创建相关性矩阵的步骤如下：
点击“数据分析”图标。
选择相关性分析选项。
在 Analysis ToolPak 加载项中填写相关性分析的参数。
您应能看到如下所示的相关性矩阵：
数据及其相关性矩阵。
任一列与其自身的相关值为 1，表示与自身完全相关。矩阵在行中也包含所有列，从而构成各列两两之间的所有组合。矩阵的右上三角为空，因为其与左下三角的组合完全对称。
我们已在 Excel 中成功创建了相关性矩阵。现在，是时候理解这些数值的含义，并将其转化为洞见。
如何在 Excel 中解读相关性矩阵
解读上述相关性矩阵并不困难，但当列数增多时就会变得棘手。列数很高时，不可能逐一查看每个组合。
我们需要一种机制，能在众多可能组合中快速识别最重要的相关性。
对相关性矩阵中的单元格进行条件格式设置，可以将注意力引导至最显著的单元格（色彩更深的部分），从而帮助更好地解读矩阵。
以下是在 Microsoft Excel 中为相关性矩阵着色的步骤：
点击“条件格式”图标。
创建新的条件格式规则。
您将看到如下输出：
示例数据及着色后的相关性矩阵。
就是这样！我们已经通过配色来更好地可视化相关性矩阵。
在完成着色以便于视觉理解之后，我们可以从结果中得出以下结论：
解读相关性矩阵的关键在于观察矩阵中数值的方向和强度，并回溯到与之对应的列。
请注意，我们的说明从未声称“因为年龄更大所以年收入更高”，或“服装销量更高会导致杂货销量更低”。这是因为相关性并不等于因果关系，这是一个常见误解。
通过相关分析，我们只能解读两个变量之间关系的存在（或不存在），而无法断言一个变量导致了另一个变量的改变。要确定因果关系，需要进行特定实验。您可以通过我们的“相关 vs 因果”教程了解更多。
结语
本教程介绍了相关性的概念、皮尔逊系数及其数学表达、相关性的不同类型，以及如何根据相关系数的方向和强度来识别它们。
随后我们转向实现层面，使用示例数据创建相关性矩阵，并通过条件格式进行可视化和解读，同时也指出了在解读相关性矩阵时常见的误区。
学习不止于此！如果您有兴趣用其他编程语言实现相关性分析，以下指南将有所帮助：
要巩固相关技术知识，请查看我们的入门级概率与统计课程。或者，如果您对技术概念已有把握，但希望提升在 Excel 中的实操能力，不妨看看Excel 基础学习路径。