教你做excel线性拟合技巧 excel线性拟合怎么做 _经验分享

y=β1+β2x+ε
β1、β2是未知参数，称为回归系数，需要从样本来估计。ε是随机误差项，又称为随机干扰项，它是一个特殊的随机变量，反映未列入方程式的其他各种因素对y的影响。
我们使用最小二乘法来做一元线性回归方程的拟合。

文章插图
最小二乘法
最小二乘法的性质
1、运用普通最小二乘法得到的样本回归线经过样本的均值点。
【教你做excel线性拟合技巧 excel线性拟合怎么做】

文章插图
最小二乘法
2、残差的均值为0；
3、残差和解释变量不相关，即

文章插图
最小二乘法
显著性校验
根据公式，我们可以得出一元线性回归方程，下面需要对拟合的质量做显著性校验。我们介绍SST、SSE、SSR的相关概念。

文章插图
显著性校验
SST（总平方和）=SSR（回归平方和）+SSE（残差平方和）
SST：total sum of square
反映因变量的n个观察值与其均值的总误差；
SSR：sum of squares of regression
反映自变量x对因变量y取值变化的影响，或者说，由于x和y之间的线性变化引起的y的取值变化，也成为可解释的平方和；
SSE：sum of squares of error残差平方和
反映了除x意外其他因素对y取值的影响，也称为不可解释的平方和或剩余平方和。
决定系数
1、回归比例占总误差平方和的比例；
2、反映曲线的拟合程度；
3、取值范围在[0,1]之间；
R2趋于1 ，说明回归方程拟合的越好；R2趋于0 ，说明回归方程拟合的越差；
4、决定系数的平方根等于相关系数；
下面我们拿一组数据来进行验证（编号、广告投入额x、产品销售额y）：
17.4928.39
26.4426.54
39.9134.89
48.6531.79
511.338.86
68.2528.64
75.2321.75
86.7326.49
910.3935.25
106.6228.09
116.527.23
129.431.95
137.3527.78
1410.4334.76
157.7530.22
168.2231.29
179.1733.15
188.733.08
1912.2538.99
208.1430.39
第一步，判断x与y之间线性相关性；根据我们在帖子”变量之间相关系数”公式求得如下：

lxy
2872.6084
lxx
1224.6016
lyy
7114.1251
r
0.973236226
说明x与y之间高度相关；
第二步、使用最小二乘法公式求得

x均
8.446
y均
30.9765
a
2.345749344
b
11.16430104
y=2.3457x+11.1643
第三步、显著性校验

SST
355.706255
MST
17.78531275
RMST
4.217263657
SSR
336.9209635
MST
16.84604817
RMST
4.104393764
R2
0.947188751
MSE
0.047359438
RMSE
0.217622236
和第一步做对比， R2=0.947188751；r=0.973236226；R=r
决定系数=0.947188751 ，趋于1 ，说明一元线性回归方程拟合效果很好。
我们利用excel做下试验，结论和我们通过运算完全一样。