透视表怎么做相关性分析_散点图矩阵制作【数据科学】

想在Excel里用数据透视表做相关性分析，并且用散点图矩阵来直观展示多个变量之间的关系？这个想法很专业。数据透视表本身不直接生成散点图矩阵，但它是个绝佳的“数据加工站”。我们可以分几步走，把透视表汇总出的干净数据，变成一张信息量丰富的专业图表。

一、从数据透视表到干净的汇总数据

散点图矩阵的原料是纯粹的数值表格。数据透视表的作用，就是帮你把原始数据按需“揉”成这种格式。

首先，选中你的原始数据区域，通过“插入”选项卡创建一个新的数据透视表。在字段列表中，把你要分析的两个数值字段（比如“广告投入”和“转化率”）拖到“值”区域。如果需要分组查看（比如按月份或地区），就把相应的分类字段拖到“行”区域。

这里有个关键点：确保数据透视表显示的是基础的聚合值（如求和、平均值）。右键点击数值单元格，在“值显示方式”里选择“无计算”。最后，复制整个透视表区域，在新工作表里“选择性粘贴”为“数值”。这一步至关重要，它剥离了透视表的动态链接和格式，留下一张静态、干净的二维数值表，为后续绘图打好基础。

二、整理成对的变量数据集

拿到干净的汇总表后，下一步是把它整理成散点图矩阵能“吃”的格式。矩阵要求每一对要比较的变量，都以独立的两列形式存在，并且行数要对齐。

先检查一下数据表的结构。如果它是“长格式”（比如列是：月份、指标A值、指标B值），你可能需要把它转换成“宽格式”（指标A、指标B、指标C并排成列）。反之，如果已经是宽格式，那就省事了。

接着，处理数据质量：删除纯文本列或满是错误值的列。对于缺失的数值，一个实用的办法是用该列的平均值来填充，保证数据连续性。

如果要分析超过3个变量，就需要手动创建所有两两组合。比如有A、B、C三个指标，就需要准备A-B、A-C、B-C三组数据。可以把每组数据分别放在独立的工作表里，并清晰命名，比如“X_广告投入_Y_点击量”，这样后面管理起来一目了然。

三、创建独立的散点图

Excel没有一键生成散点图矩阵的功能，所以我们需要为每一对变量单独创建散点图，最后再拼装起来。

以“A-B”这组数据为例。选中包含两列数据的区域（记得带上标题行），然后在“插入”图表里选择“仅带数据标记的散点图”。图表生成后，别急着看效果，先右键进入“选择数据源”，仔细核对一下：X轴系列值是否准确引用了第一列数据，Y轴系列值是否引用了第二列数据。这个步骤能避免很多张冠李戴的错误。

为了让图表更规范，建议手动设置坐标轴范围。双击横坐标轴，取消“自动”设置，根据你的数据分布设定一个合理的起点和终点。Y轴也做同样处理。然后，为散点添加趋势线，并在选项中勾选“显示R²值”和“显示公式”。R²值能直观地告诉你这两个变量线性关系的强弱。

四、将散点图排列成矩阵

单个图表准备好了，现在要把它们组装成专业的矩阵。核心原则是：统一和对齐。

首先，把所有创建好的散点图复制粘贴到一个新的工作表中。调整第一个图的大小（例如，固定为宽8厘米、高6厘米），然后将它作为模板，将其余所有图表都调整为完全相同的尺寸。

接下来就是“拼图”了。按照变量顺序，将图表排列成网格状。通常，矩阵的对角线位置是变量与自身的关系（可以留空或做特殊处理），而非对角线位置则展示不同变量间的关系。排列时，确保同一行的图表顶端对齐，同一列的图表左侧对齐。

排列好后，全选所有图表，在“图表设计”选项卡中点击“切换行/列”。这个操作能确保所有图表中X轴和Y轴的数据指向逻辑是一致的。最后，统一修改图表标题为“X: [变量名] vs Y: [变量名]”的格式，并删掉多余的图例，让画面更简洁。

五、添加标注与统一标准

散点图矩阵的魅力在于可比性。如果每个子图的坐标轴范围都不一样，那就很容易产生误导。因此，强制统一标准是关键。

检查所有共享同一X轴变量的图表，将它们X轴的最小值和最大值设置为相同的数值。对Y轴也执行同样的操作。这能确保你在比较不同关系时，是在同一个尺度下观察的。

趋势线的设置也需要留意。除非有特殊的业务理由要求趋势线必须从原点（0,0）出发，否则一般保持“自动”设置即可。

为了快速捕捉关键信息，可以给那些表现出强线性关系（比如R²值大于0.7）的图表标题加个醒目的绿色边框。同时，在矩阵的空白处（比如左上角）插入一个文本框，添加简短的图例说明，例如：“本矩阵中，每格展示一对变量的关系。R²≥0.7表示强线性关联，R²≤0.3则表示关联较弱。”这样一来，整张图表的专业度和可读性就大大提升了。