365办公网我们一直在努力
您的位置:365办公网 > excel表格制作 > Excel数据分析——最小二乘法-相关系数excel

Excel数据分析——最小二乘法-相关系数excel

作者:365办公网

返回目录:excel表格制作

最小二乘法,这名词看着挺专业的,一用上就感觉自己的水平好像莫名其妙高出了一个档次似的,但具体使用的时候,觉得又没深奥到哪里去,甚至和之前做过的东西有点重叠

不废话,直接举个例子:

我们有两列数据,目前我们猜他们之间可能是有关联的,但又不清楚它们是怎么关联在一起的,数据如下:

Excel数据分析——最小二乘法

为了能看得清楚点,咱画个散点图表示表示:

Excel数据分析——最小二乘法

为了能总结出一个方便后续使用的规律,咱需要沿着这些点的分布画条线,最好能再总结出个公式来,后续当咱有了(X)的数据后,就可以直接通过Y=f(X)做个预估了

这时候问题就来了,姑且不要说线有很多种,哪怕形式最简单如直线,咱还得判断下这条直线画在图上哪个位置最合适不是?

虽说之前在线性拟合那篇里,咱已经有了直接计算直线系数的公式和工具,但是为了解释今天的最小二乘法,我还是用线性规划求解再操作一下

首先,咱先确定下公式的形式,直线的话是Y=aX+b,需要求的系数是a和b,所以,咱先留出试算系数的位置:

Excel数据分析——最小二乘法

然后,放入公式Y=aX+b,此时因为系数是空的,计算结果都是0

Excel数据分析——最小二乘法

再然后,我们加一列计算残差值,残差等于用公式估计出来的Y值减原本真实的数值

Excel数据分析——最小二乘法

为了说明问题,我先随便在系数a和b那里打了两个数字进去,试算出来的结果像这样:

Excel数据分析——最小二乘法

如果我们希望画出来的线最接近原来的点,那自然是想让所有的残差都尽可能的小,而一次性评判所有的残差总归有点难度,所以为了简化问题,我们可以把残差加起来,只要他们的总和最小,那相应得到的系数就最符合我们的期望

但大家可以看到,残差这个地方的数据,计算出来是有正有负的,如果直接全部加一块儿,正负抵消的话,计算总和就没效果了,而所谓最小二乘法,就是在这个基础上做的一种改进的算法,把所有残差先平方,再全部加起来,这样计算出来的总和最小的时候,就能得到我们要的系数a和b,具体操作:

计算一个残差平方和的总数(此处是数组公式)

Excel数据分析——最小二乘法

打开规划求解工具(没装过这个加载项的可以参考下链接,虽然那篇写得也不算特完整):

规划求解链接:https://www.toutiao.com/i6597584864709444109/

Excel数据分析——最小二乘法

需要填写的地方请重点关注下图的三个红色圈圈

Excel数据分析——最小二乘法

填好了点求解,得到结果:

Excel数据分析——最小二乘法

由于Excel自行做线性拟合的时候用的就是这个算法,所以规划求解的结果和在散点图上添加趋势线是一样的,不信邪的兄弟们可以比对下:

Excel数据分析——最小二乘法

那恐怕有人要问了,既然Excel有提供方便的工具给我们,我们是不是就不用记着这么麻烦的做法了??

对于这个问题,我个人的看法是,有方便那当然是按方便的法子来,但如果没有呢?

比如说,现在这个图形虽然用直线可以画出来一个趋势,但感觉直线画出来的结果并不好,相关系数R2才0.64,咱如果尝试下改成曲线呢?

还是先定义个线条的公式形式,比如这次尝试下Y=aX^2+bX+c,规划求解的过程跟上面类似,只要改几个地方就行

1) 改动一:系数区域增加个空系数c

2) 改动二:公式Y=aX+b那列改成新的公式

Excel数据分析——最小二乘法

3) 改动三:规划求解里面的系数区域(可变单元格)

Excel数据分析——最小二乘法

什么?你问残差那地儿要不要改?

不用的,不管是直线还是曲线,最小二乘法的最终要求都是残差平方和最小,所以残差那片儿都不用动,看下求解的结果:

Excel数据分析——最小二乘法

相关系数R2可以用残差数据和真实数值直接计算:

Excel数据分析——最小二乘法

当然,有兴趣的可以再对照下趋势线看看,计算结果稍稍的有点差异,不过差得不多

Excel数据分析——最小二乘法

小小的总结下:这个方法虽然早就被融合在Excel自带的一些计算工具里,但是如果单独拿出来理解并使用的话,能拓展出其他的可能性,我们可以通过尝试各种公式的形式来看是否能得到更好的拟合效果

今天写的感觉跟之前的内容确实有点类似,但好像比以前整理得通顺点了呢~~

Excel数据分析——最小二乘法

相关阅读

关键词不能为空

ppt怎么做_excel表格制作_office365_word文档_365办公网