相关系数excel_怎样征服老板？教你用excel找到数据之间隐藏信息

拿到一张报表，怎样做出让老板眼前一亮的分析？是简单分析一下每个数据项的占比？然后告诉老板现状是什么情况？

这些东西最多只能算合格的分析，原因是分析太简单，就是现状的描述。如果你能分析出数据之间隐藏的信息，告诉老板未来可能会发生什么，那就足够引起老板注意了。

01 怎样找到数据隐藏的信息？

“绝大多数数据分析问题，都可以归纳为一个问题：相关性问题”。

相关分析是研究两个或两个以上处于同等地位的随机变量间的相关关系的统计分析方法。例如，人的身高和体重之间；空气中的相对湿度与降雨量之间的相关关系都是相关分析研究的问题。

怎样用excel做数据的相关性分析呢？来看一个例子。

这是一个广告投放的案例，在不同的投放时间随着投入的各项的成本变换(x1和x2)，广告的曝光量(y)数据也各不相同。那y和x1、x2有没有相关性呢？

这时候，就可以相关性矩阵来分析变量之间的相关程度。

操作方法为：选择excel的数据-数据分析(需提前在excel配置数据分析模块)，选择相关系数。在弹出的窗口中如下选择：

注意：勾选标志位于第一行。

点击确定后，变量之间的相关关系一目了然。如下图：

这份结果怎样来解读呢？有一个标准：对于两个变量A和B，相关系数r的绝对值一般在0.8以上，认为A和B有强的相关性。0.3到0.8之间，可自以认为有弱的相关性。0.3以下，认为没有相关性。

从上面的结果看，相关系数都大于0.8，所以x1和x2都与y有强相关性。

02 怎样量化分析数据之间的关系？

找到了数据的相关关系后，怎样进行量化，并用于预测分析呢？这就需要回归分析。

回归分析在统计学中包含了很多类别，比如一元回归、多元回归、方差回归、线性回归、非线性回归等，本次案例的变量较简单，而且相关性很强，可以尝试多元回归来分析。

继续上面的案例来讲解多元回归分析。同样是使用excel表的数据-数据分析，选择回归。在弹出窗口选择Y值和X值的输入区域，如图所示。

结果是3张表格。

表格1：回归统计表

1.Multiple R即相关系数R的值，和我们之前做相关分析得到的值一样，大于0.8表示强正相关。

2. R Square是R平方值，R平方即R的平方，又可以叫判定系数、拟合优度，取值范围是[0,1],R平方值越大，表示模型拟合的越好。一般大于70%就算拟合的不错，60%以下的就需要修正模型了。这个案例里R平方为0.975，相当不错。

3. Adjusted R是调整后的R方，这个值是用来修正因自变量个数增加而导致模型拟合效果过高的情况，多用于衡量多重线性回归。

表格2：方差分析表

1. df是自由度，SS是平方和，MS是均方，F是F统计量，Significance F是回归方程总体的显著性检验，其中我们主要关注F检验的结果，即Significance F值，F检验主要是检验因变量与自变量之间的线性关系是否显著，用线性模型来描述他们之间的关系是否恰当，越小越显著。这个案例里F值很小，说明因变量与自变量之间显著。

2. 残差是实际值与预测值之间的差，残差图用于回归诊断，回归模型在理想条件下的残差图是服从正态分布的。

表格3：系数分析表

1.第三张表我们重点关注P-value，也就是P值，用来检验回归方程系数的显著性，又叫T检验，T检验看P值，是在显著性水平α(常用取值0.01或0.05)下F的临界值，一般以此来衡量检验结果是否具有显著性，如果P值>0.05，则结果不具有显著的统计学意义，如果0.01

表中的P值都小于0.05，因此都具有统计学意义。T检验是看某一个自变量对于因变量的线性显著性，如果该自变量不显著，则可以从模型中剔除。

2.从第上表的第一列我们可以得到这个回归模型的方程：y=7200+1.1564432x1-2.559187x2，此后对于每一个输入的自变量x1x2,都可以根据这个回归方程来预测出因变量Y。

至此，我们不仅找到了统计报表中有相关关系的数据，还可以通过数学表达式实现量化的分析和预测。这样一份分析报告相信能够对生产经营分析带来较大的帮助。