如何用 AI 创建相关矩阵：2026 年的 5 个简单检查

Powerdrill Bloom·2026年9月11日

相关矩阵将每个数值列与其他所有数值列进行两两对比，并报告每对变量之间协同运动的紧密程度。本指南将介绍什么是相关矩阵以及如何手动构建它。然后，它将提供通过导出文件生成该矩阵的三个步骤。最后，我们将介绍五个检查步骤，以帮助您区分有用的矩阵与具有误导性数字的网格。

开始前需要准备什么

一份每个主体占一行的导出数据：例如一个客户、一家商店、一个星期或一次营销活动。
至少三个数值列。少于这个数量，制作矩阵的工作量将大于其带来的价值。
明确“主体”的定义。各行必须是同一种类型的事物，否则系数将毫无意义。
记录哪些列是相互派生的。例如，收入和单笔订单收入在结构上必然存在相关性。
在运行任何计算之前，决定如何处理空白值。

最后一项不仅会改变呈现方式，还会改变最终的计算结果。这也是下面“检查 2”的主题。

如何手动制作

方案 1：使用 Analysis ToolPak 的相关系数工具

这是最快捷的途径，可以直接生成矩阵。微软将该加载项的作用描述为简化步骤。您只需“为每次分析提供数据和参数”。然后，该工具会使用“宏函数来计算并在输出表中显示结果”。

有两个限制值得提前了解。输出是一个静态的数字块，因此当数据发生变化时它不会自动更新。此外，根据文档，“数据分析功能一次只能在一个工作表上使用”。

方案 2：编写 CORREL 公式网格

将列名分别放在侧边和顶部，然后用针对这两个范围的 CORREL 公式填充每个单元格。

优点是它可以重新计算。缺点是规模问题。一个 12 列的矩阵意味着 144 个混合了绝对引用和相对引用的单元格。只要拖动公式时出现一次失误，就会把错误的数据对放入错误的单元格中，而且不会显示任何错误提示。

方案 3：在电子表格之外进行计算

任何统计学库都可以通过一行代码返回相关矩阵，并且它能显式地处理缺失数据。

这是最可靠的途径，但也是最难分享的。想要知道为什么两列存在相关性的同事无法打开您的脚本，而且矩阵最终只能以粘贴图片的形式呈现。

手动方式的瓶颈所在

算术计算是瞬间完成的。耗费时间的其实是围绕它的所有准备工作。

第一个成本是列的选择。原始导出数据中包含 ID 列、以数字形式存储的日期列，以及编码为 0 和 1 的状态标志。它们都是数值型的，因此都会进入矩阵。客户 ID 与收入之间的相关性纯属噪音，但它在矩阵中看起来仍然像一个正常的单元格。

第二个成本是阅读矩阵。一个包含 12 个变量的矩阵在对角线上方有 66 个不同的数据对。在 66 个数字中扫视并找出有价值的数字，正是人们最容易跳过的任务，这也是为什么矩阵被制作出来后往往会被忽略的原因。

第三个成本是重复劳动。没有人只构建一个相关矩阵。他们会为每个群组、每个地区或每个季度分别构建一个，而每次都需要重新进行列选择工作。

如何使用 Powerdrill Bloom 创建相关矩阵

步骤 1：上传每个主体占一行的导出文件

将文件直接拖入 Powerdrill Bloom，无需预先筛选列。免费计划支持 Excel、CSV、PDF 和文档上传，因此无需付费计划即可导入原始导出文件。

保留 ID 和日期列。在需求中说明排除它们，比直接删除列然后忘记删除了哪些列要可靠得多。

步骤 2：指定要包含的列以及如何处理空白值

用自然语言进行描述。列出属于真实度量的列，并指明要排除的列。然后说明是应该丢弃含有缺失值的行，还是进行两两处理。

同时也说明哪些列是派生出来的。如果一个矩阵把显而易见的算术关系当作“新发现”标记出来，那纯粹是在浪费读者的宝贵时间。

步骤 3：生成表格和热力图形式的矩阵

索取您需要的输出。即作为系数表的矩阵，外加相同网格的热力图。添加一个值得关注的数据对简短列表，并附带它们的样本量。定价页面介绍了如何获取包含图表、表格和导出文件的可靠答案，而高级分析页面在可视化图表中列出了热力图。

如果需要将矩阵写入分析报告中，Pro 计划可以将其扩展为更完整的 Office 文档输出。

每个相关矩阵都应通过的 5 项检查

检查 1：成对的列长度是否相同？

这是导致单元格出错最常见的原因。微软关于该函数的文档明确指出：“如果 array1 和 array2 的数据点个数不同，CORREL 将返回 #N/A 错误。”

在手动构建的网格中，当一个范围比另一个范围多拖动了一行时，就会发生这种情况。解决方法很简单，但发现它才是难点，因为在 144 个单元格的网格中，单个 #N/A 很容易被忽略。

检查 2：空白值和文本是否如你预期般消失？

文档中记录的两种行为在这里指向了不同的方向，了解这两者可以避免真正的错误。

该函数会按对丢弃非数值。根据微软的说法，参数中的“文本、逻辑值或空白单元格”会被忽略。但是“包含零值的单元格将被计算在内”。空白会被跳过，而零值则会被计算。

ToolPak 的相关系数工具则更为严格。其文档指出，“任何主体的任何缺失观测值都会导致该主体在分析中被忽略”。一个空白单元格就会将整行从矩阵中的每一对中移除。

因此，根据您采取的途径，相同的数据可能会产生两个不同的矩阵。如果某一列有许多空白值，ToolPak 途径可能会在不知不觉中基于小得多的样本量来计算网格。

检查 3：是否存在常数列？

如果某一列在每一行中都具有相同的值，则没有可用于相关的变异。文档中记录的结果是报错。如果“其值的标准偏差 s 等于零”，则“CORREL 将返回 #DIV/0! 错误”。

这种情况出现的频率比预期的要高。在您导出的期间内始终为 true 的标志，或者未发生变化的费用，都会表现出这种行为。

检查 4：你是否观察了分布形状，而不仅仅是数字？

该系数衡量的是两列在一条直线上协同运动的一致性。先上升后下降的关系在图表中可能非常强且明显，但其相关系数却可能接近于零。

因此，接近零的单元格并不能证明独立性。它只是关于线性关联的陈述。在得出任何结论之前，请先绘制您关心的两三个数据对的图表，并检查是否存在少数极端行将系数拉高。有关该步骤的详细内容，请参阅异常值指南。

检查 5：你是否考虑了自己观察了多少个数据对？

一个 10 列的矩阵包含 45 个不同的数据对。一个 12 列的矩阵包含 66 个。如果浏览足够多的数据对，总会有那么几个在毫无因果关系的情况下看起来相关性很强。

两个习惯可以解决这个问题。在阅读网格之前，先确定您预期的关系，并将其他关系视为候选关系而非最终结论。此外，在您计划引用的任何系数旁记录样本量，因为统计显著性取决于它。

这样能节省什么

任务	手动操作	通过上传的导出文件
排除 ID、日期和标志列	手动操作，每次运行都要重做	在需求中说明一次即可
构建网格	144 个公式或静态 ToolPak 数据块	属于您所提问题的一部分
应用单一缺失数据规则	取决于您使用的途径	显式说明
筛选值得阅读的数据对	用肉眼扫视 45 到 66 个单元格	随矩阵一同返回
制作下季度的版本	重新进行列选择	相同的需求，新的文件

最佳实践

报告系数及其样本量。 在 11 行数据上显示为 0.7 的单元格，与在 11,000 行数据上显示为 0.7 的单元格，其代表的意义完全不同。

在相互派生的数据对中丢弃其中一个。 派生列会用您早已知晓的关系使矩阵膨胀。

将矩阵显示为用于快速浏览的热力图和用于引用的表格。 热力图用于发现模式，而表格则提供他人可以粘贴到文档中的具体数字。

不要让矩阵来决定模型。 通过最强相关性来选择预测变量，会导致共线性变量被同时选中。回归分析才是解决该问题的正确工具。

在矩阵旁写明排除列表。 读者的第二个问题往往是“哪些列被排除了”。

结论

相关矩阵的计算成本很低，但很容易被过度解读。Excel 提供了这两种计算途径，但它们在处理缺失数据时存在分歧，这一点虽然记录在文档中，但很少有人注意到。

其价值在于这五项检查，而不在于网格本身。即：长度相同的数据对、明确的空白值规则、无常数列、观察实际分布形状，以及坦诚面对自己浏览了多少个数据对。

如果您手头有导出文件，但不想面对 144 个单元格的网格，可以尝试使用原始文件体验 Powerdrill Bloom。Excel AI assistant 途径则非常适合已经存在于工作簿中的数据。

常见问题解答

什么是相关矩阵？

它是一个显示数据集中每对数值变量之间相关系数的表格。微软的文档将 Analysis ToolPak 的输出描述为显示应用于每个可能数据对的 CORREL 或 PEARSON 的相关矩阵。当您拥有两个以上的度量变量时，它最为有用。

如何在 Excel 中制作相关矩阵？

有两种途径。Analysis ToolPak 包含一个相关系数工具，可将矩阵写入输出表中。或者，您可以构建一个 CORREL 公式网格，该网格可以重新计算，但需要占用许多单元格并需要仔细进行引用。

Excel 在相关性分析中如何处理缺失值？

这取决于您使用哪种途径。CORREL 函数会忽略文本、逻辑值和空单元格，但会包含零值。而 ToolPak 的相关系数工具则会将任何含有缺失观测值的主体从整个分析中剔除。

怎样才算强相关？

微软的指南是方向性的，而非具体的数值。越接近 +1 或 -1 的值表示越强的正相关或负相关。越接近 0 的值表示无相关或弱相关。任何固定的阈值都取决于您所处的领域和样本量。

高度相关是否意味着一个变量导致了另一个变量？

否。该系数仅描述了两列协同运动的一致性，而与方向或机制无关。驱动这两者的第三个因素，或者从另一个列派生出来的列，都会产生相同的读数。

相关矩阵能告诉你什么