如何用excel做最小二乘法 excel最小二乘法拟合直线!

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

不废话，直接举个例子：

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

为了能看得清楚点，咱画个散点图表示表示：

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

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

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

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

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

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

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

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

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

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

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

规划求解链接：

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

填好了点求解，得到结果：

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

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

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

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

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

1） 改动一：系数区域增加个空系数c

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

3） 改动三：规划求解里面的系数区域（可变单元格）

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

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

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

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

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

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