使用Excel进行线性回归、计算R2、RMSE、MAE等精度方法

SPSS个人用不太明白，所以还是想着用excel软件计算一下这些基本的统计学数据：

首先是这几个统计学精度评价的概念:

对模型精度进行评价，采用决定系数（Coefficient of determination，R2）、均方根误差（Root Mean Square Error，RMSE）、平均绝对误差（Mean Absolute Error，MAE）、均方误差（Mean Square Error，MSE）和平均绝对百分比误差（Mean Absolute Percentage Error，MAPE）等五个特征指标进行比较，指标公式见下文。本研究采取多次试验结果的平均数作为最终结果，降低模型随机性的影响。

模型拟合精度的高低通过R2进行评价，范围在0~1之间，越接近1说明模型的构建效果越好，拟合能力越强，反之，则说明模型构建效果较差，拟合能力弱。R2的计算公式如（3.18）：

                                                                 R2=1-i=1N(wi-yi)2i=1N(wi-y)2                                                         (3.18)

均方误差MSE又称二次损失，常用于回归问题，是模型反演值与实测值之间误差的平方评价模型的优劣，即反演值与实测值差距越小，MSE就越小，均方误差的值越低，也说明模型对实验数据的解释更加精确，计算公式如（3.19）：

                                                 MSE=1Ni=1N(wi-yi)2                                                     (3.19)

均方根误差RMSE又称标准误差，是模型反演值与实测值误差的平方的均值，反映了反演结果与实测结果之间偏离的程度，RMSE越小，说明偏离的越少，模型的精度越高，计算公式如（3.20）：

                                               RMSE=1Ni=1N(wi-yi)2                                                   (3.20)

平均绝对误差MAE表示模型反演值与实测值绝对误差的平均值，由于评价的是误差的绝对值，因此损失是固定的，受离散点影响较小，同样，MAE越小，说明模型的精度更好，计算公式如（3.21）：

                                                  MAE=1Ni=1N|wi-yi|                                                      (3.21)

平均百分比误差MAPE，常用于衡量反演准确性的统计标准，MAPE低于10%时表示模型构建合理，计算公式如（3.22）：

                                             MAPE=1Ni=1N|wi-yi|wi×100%                                                (3.22)

式中，N为样本数，wi 为模型反演土壤含水量值，yi 为实测土壤含水量值，y 为实测值均值。

(简要的概括一下，

R方即为拟合优度，0~1，表示模型预测结果与实测之间的拟合情况；  越高越好

MSE和RMSE，均方误差a和均方根，a:反演值和实测值差值的平方和/项数，b：前者的开根式。两者用以表示反演结果与实测结果的偏离程度；越低越好

MAE,MAPE, a平均绝对误差,b平均百分比误差，a：反演值和实测值差值的绝对值和/项数；b：反演值和实测值差值的绝对值/反演值    的和  /项数*100%。  用以表示反演的精度。

越小越好，MAPE小于10%表示合理。

计算方法：

1、计算r2和线性回归

打开excel，wps不行！ 在选项栏→加载项找到分析数据库→转到，在分析数据库打勾

选择回归，对选项进行输入：

此处注意：这个x、y值的输入区域，要选择右边红框的按钮选择序列中的数据，而不能用ctrl选择首尾；

否则会出现：回归输入区域必须相邻引用的报错。

R2的计算：
RSQ(B2:B11,A2:A11)     
RSQ(ys,xs)——ys是指响应变量的值，xs是预测变量的值

2、MSE、RMSE，MAE、MAPE的计算

MSE的计算：SUM((A3-B3)^2)/n=AVERAGE((A3-B3)^2)
rmse：mse开算术平方根

MAE:AVERAGE(ABS(A2-B2))
MAPE:AVERAGE(ABS((A2-B2)/A2))