相关性分析(PowerBI、Excel)

off999 2025-04-26 20:25 64 浏览 0 评论

昨天介绍了相关性分析的原理、流程和常用的皮尔逊相关系数和斯皮尔曼相关系数，具体可以参考《数据分析-相关性分析》。昨天是使用R语言来进行案例演示的，今天使用常用的Excel和PowerBI来实现，后面再写一篇Python的就完整了。

【Excel相关性分析】

首先还是25个样本数据，理论上是要进行正态性检验的，但是Excel本身没有直接的正态性检验工具，但你可以使用第三方插件或外部统计软件，如R语言或Python，来进行正态性检验，具体的可以参考另外使用R和Python的内容。

首先是皮尔逊相关系数，这也是Excel里面默认的相关性计算方式。数据还是昨天的广告费用投入和销售额之间的相关性，按照流程，第一步先绘制散点图来观察两个变量之间是否具有相关性↓

从图形来看，广告费用投入和销售额之间有明显的正相关关系，接下来跳过正态性检验，直接在Excel里面计算相关性，使用CORREL函数就行了，我们的数据在B列和C列，公式里面就两列参数，如下↓

=CORREL(B2:B26,C2:C26)

结果是0.9444，具有很强的正相关性。

在Excel里面，除了使用公式计算，还可以使用数据分析工具里面的相关性计算，结果也是一样的↓

这种使用数据分析工具可以多列同时进行计算，并且结果会以下三角矩阵的形式展示，还是挺方便的。

接下来是斯皮尔曼相关系数，在Excel里面没有直接计算的方式，需要先把需要计算的两列分别计算排名，然后对排名使用CORREL函数，结果就是斯皮尔曼相关系数。我们使用的数据是工作年份与工作满意度之间的相关性分析，数据如下↓

按照流程，首先画散点图看一下整体的趋势↓

可以看到大概是成负相关，然后计算两列的排名，使用rank函数↓

=RANK.AVG(B2,$B$2:$B$26,1)
=RANK.AVG(C2,$C$2:$C$26,1)

最后对最后两列排名进行相关性计算，结果是-0.7513。如果直接使用皮尔森相关系数，结果是-0.7726，还是有一定的差异。

【PowerBI相关性分析】

PowerBI比Excel还要惨，连相关性计算函数都没有了。但是好在它灵活，我们可以根据相关系数的公式，自己来写相关系数的计算公式。

首先还是简单一点的皮尔森相关系数，它的计算公式如下↓

于是我们可以先计算X和Y列的均值，然后相减，相乘，开方，相除结果就出来了，数据还是我们最上面的数据，在PowerBI里面的DAX自定义公式如下↓

Pearson = 
VAR MeanX = AVERAGE(Pearson[广告费用])
VAR MeanY = AVERAGE(Pearson[销售额])
VAR SumProduct = SUMX('Pearson', (Pearson[广告费用] - MeanX) * (Pearson[销售额] - MeanY))
VAR StdDevX = SQRT(SUMX('Pearson', POWER('Pearson'[广告费用] - MeanX, 2)))
VAR StdDevY = SQRT(SUMX('Pearson', POWER(Pearson[销售额] - MeanY, 2)))
RETURN 
    IF(StdDevX * StdDevY = 0, BLANK(), SumProduct / (StdDevX * StdDevY))

结果和我们在Excel里面是一样的，然后在绘制一个散点图就完整了↓

接下来是在PowerBI里面求斯皮尔曼相关系数，还是和Excel里面的逻辑一样，先计算两列的排序，然后对排序进行相关性计算，排序的DAX如下↓

年份排名 = 
RANKX(
    ALL(Spearman),
    'Spearman'[工作年份],,
    ASC,
    Skip
)
评分排名 = 
RANKX(
    ALL(Spearman),
    'Spearman'[满意度评分],,
    ASC,
    Skip
)

计算斯皮尔曼相关系数↓

Spearman = 
VAR avg_year = AVERAGE(Spearman[年份排名])
VAR avg_score = AVERAGE(Spearman[评分排名])
VAR Numerator = 
    SUMX('Spearman',
        ('Spearman'[年份排名] - avg_year) * ('Spearman'[评分排名] - avg_score)
    )
VAR Denominator = 
    SQRT(
        SUMX('Spearman', 
            ('Spearman'[年份排名] - avg_year) * ('Spearman'[年份排名] - avg_year)
        ) * 
        SUMX('Spearman', 
            ('Spearman'[评分排名] - avg_score) * ('Spearman'[评分排名] - avg_score)
        )
    )
RETURN Numerator / Denominator

结果也是正确的。

如果在PowerBI里面只是要可视化展示相关系数，可以直接加载第三方的可视化工具。里面不仅可以展示相关性，还可以展示一元线性回归的系数和R方值，还是挺好用的↓

End

python开方