Excel回归分析结果的详细阐释

1、Excel回归分析结果的详细阐释利用Excel的数据分析进行回归，可以得到一系列的统计参量。下面以连续10年积雪深度和灌溉面积序列（图1）为例给予详细的说明。ABC1年份最大积雪深度咒冰）灌溉面积式千亩）2r197115.228.63r197210.419.34r197321.240.55r197418.635.66r197526.448.97r197623.4458197713.529.29197316.73111019792446.711198019.13。4图1连续10年的最大积雪深度与灌溉面积（19711980）回归结果摘要（SummaryOutput）如下（图2）：ABcDEFrgL

2、hLiSUMMARYOUTPUT回归统计Multiple6.9S9416RSquareQ.978944Adjusted0.976312标准误差1.418924观测值10方差分析dfssMSFmificanceF回归分析1748.8542748.8542371.94535,42E-08够316.106762.013345总计g764.961Cofficien标准误差tStatP-valueLower9&%Uor)er限95,。止限第.0Intercep12.3564331.E278761.2891670.233363-1.858656.57153-1.858656.57153最大积雪中,1,81

3、29210.09400219.285885.42E-081.5961512.0296911.5961512.029691RESIDUALOUTPUTPROBABILITYOUTPUT观测值瞿溉面积y残差标准残差百分比排慑面积排十田）129.91284-L31284-0.9S136519.3221,21OS2-1.91082-1.428361523,6340.79036-0.29036-0.217052529.2436,07677-0.47677-0.356393534,1550.21755-1.31755984394535.6图2利用数据分析工具得到的回归结果第一部分：回归统计表这一部分给出了

4、相关系数、测定系数、校正测定系数、标准误差和样本数目如下(表1):表1回归统计表回归统计Multiple0.989416RSquare0.978944Adjusted0.976312标准误差1.418924观测值10逐行说明如下：Multiple对应的数据是相关系数(correlationcoefficient),即R=0.989416。RSquare对应的数值为测定系数(determinationcoefficient),或称拟合优度(goodnessoffit),它是相关系数的平方，即有R2=0.9894162=0.978944。Adjusted对应的是校正测定系数(adjusteddet

5、erminationcoefficient),计算公式为(n-1)(1-R2)Ra=n-m-1式中n为样本数，m为变量数，R2为测定系数。对于本例，n=10,m=1,R2=0.978944,代(10-1)(1-0.978944)Ra=1-二0.976312入上式得10-1-1标准误差(standarderror)对应的即所谓标准误差，计算公式为1SSen-m-1这里SSe为剩余平方和，可以从下面的方差分析表中读出，即有SSe=16.10676,代入上式可得J1*16.10676=1.418924.10-1-1最后一行的观测值对应的是样本数目，即有n=10o第二部分，方差分析表方差分析部分包括自

6、由度、误差平方和、均方差、F值、P值等(表2)。表2方差分析表(ANOVA方差分析dfSSHSF;nificancE回归分析1748.8542748.8542371.94535.42E-08残差S16.106762.013345总计9764.961逐列、分行说明如下：第一列df对应的是自由度(degreeoffreedom),第一行是回归自由度dfr,等于变量数目，即dfr=m;第二行为残差自由度dfe,等于样本数目减去变量数目再减1,即有dfe=n-m-1;第三行为总自由度dft,等于样本数目减1,即有dft=n-1o对于本例，m=1,n=10,因此，dfr=1,dfe=n-m-1=8,df

7、t=n-1=9。第二列SS对应的是误差平方和，或称变差。第一行为回归平方和或称回归变差SSr,即有n2SSr=、0-yi)2=748.8542i4它表征的是因变量的预测值对其平均值的总偏差。第二行为剩余平方和(也称残差平方和)或称剩余变差SSe,即有n2SSe=(yi-y?i)2=16.10676i4它表征的是因变量对其预测值的总偏差，这个数值越大，意味着拟合的效果越差。上述的y的标准误差即由SSe给出。第三行为总平方和或称总变差SSt,即有n2SSr-(yi-yi)2=764.961i4它表示的是因变量对其平均值的总偏差。容易验证748.8542+16.10676=764.961,即有SSr

8、SSe=SSt而测定系数就是回归平方和在总平方和中所占的比重，即有SSt显然这个数值越大，拟合的效果也就越好2SSr748.8542R=0.978944764.961第四列MS对应的是均方差，它是误差平方和除以相应的自由度得到的商。归均方差MSr,即有第一行为回SSrMSr=dfr第二行为剩余均方差MSe,即有748.8542=748.8542SSe16.10676MSe=2.013345dfe显然这个数值越小，拟合的效果也就越好。第四列对应的是F值，用于线性关系的判定。对于一元线性回归,F值的计算公式为R21.(1-R2)n-mTdfeR21-R2式中R2=0.978944,dfe=10-1

9、-1=8,因此8*09789441-0.978944=371.9453第五列SignificanceF对应的是在显著性水平下的F临界值，其实等于P值，即弃真概率。所谓“弃真概率”即模型为假的概率，显然1-P便是模型为真的概率。可见，P值越小越好。对于本例，P=0.00000005420.0001,故置信度达到99.99%以上。第三部分，回归参数表回归参数表包括回归模型的截距、斜率及其有关的检验参数（表3）。表3回归参数表Coefficients标准误差tStatIntereept2.3564379291.8278761.289167最大积雪深度米）1.8129210650.09400219,2

10、8588P-alueLower95Upper95%下限95.ON上限95.0%0.233363-L858656,5715301-1.8586546.57153015.42E-081.5961512.02969131.59615082.0296913第一列Coefficients对应的模型的回归系数，包括截距a=2.356437929和斜率b=1.812921065,由此可以建立回归模型?i=2.35641.8129k或V、=2.35641.8129为；i第二列为回归系数的标准误差（用sa或sb表示），误差值越小，表明参数的精确度越高。这个参数较少使用，只是在一些特别的场合出现。例如L.Beng

11、uigui等人在Whenandwhereisacityfractal?一文中将斜率对应的标准误差值作为分形演化的标准，建议采用0.04作为分维判定的统计指标（参见EPB2000）。不常使用标准误差的原因在于：其统计信息已经包含在后述的t检验中。第三列tStat对应的是统计量t值，用于对模型参数的检验，需要查表才能决定。t值是回归系数与其标准误差的比值，即有satbb?b根据表3中的数据容易算出:2.3564381.827876=1.289167,tb1.8129210.094002=19.28588对于一元线性回归，t值可用相关系数或测定系数计算，公式如下R1-R2,n-m-1将R=0.989

12、416、n=10、m=1代入上式得到=19.28588x0.989416t:21-0.989416210-1-1对于一元线性回归，F值与t值都与相关系数R等价，因此，相关系数检验就已包含了这部分信息。但是，对于多元线性回归，t检验就不可缺省了。第四列Pvalue对应的是参数的P值（双侧）。当P0.05时，可以认为模型在炉0.05的水平上显著，或者置信度达到95%;当P0.01时，可以认为模型在妹0.01的水平上显著，或者置信度达到99%;当P0.001时，可以认为模型在妹0.001的水平上显著，或者置信度达到99.9%。对于本例，P=0.00000005420.0001,故可认为在a=0.00

13、01的水平上显著，或者置信度达到99.99%oP值检3与t值检验是等彳的，但P值不用查表，显然要方便得多。最后几列给出的回归系数以95%为置信区间的上限和下限。可以看出，在行0.05的显著水平上，截距的变化上限和下限为-1.85865和6.57153,即有-1.85865a6.57153斜率的变化极限则为1.59615和2.02969,即有1.59615b2.02969第四部分，残差输出结果这一部分为选择输出内容，如果在“回归”分析选项框中没有选中有关内容，则输出结果不会给出这部分结果。残差输出中包括观测值序号（第一列，用i表示），因变量的预测值（第二列，用夕表示），残差（residuals,

14、第三列，用ei表示）以及标准残差（表4）。表4残差输出结果观测值预测灌溉面积八千田）现左标准残差129,91283811-1.31284-0.98136221.210817-1.91082-L42836340.7903645-0.29036-0.21705436.07676973-0,47677-0,35639550.21755404-L31755-0.98489644.778790840.2212090.165356726.83087232.3691281.770947832.632219711,46778L097181945.866543480.8334570.6230171036.983

15、23027416770.31154.预测值是用回归模型夕=2.35641.8129为计算的结果，式中X即原始数据的中的自变量。从图1可见，Xi=15.2,代入上式，得%=2.35641.8129x1=2.35641.8129*15.2=29.91284其余依此类推。残差e的计算公式为3=yi-区从图1可见，y=28.6,代入上式，得到e=y1-%=28.6-29.91284=-1.31284其余依此类推。标准残差即残差的数据标准化结果，借助均值命令average和标准差命令stdev容易验证，残差的算术平均值为0,标准差为1.337774。利用求平均值命令standardize（残差的单元格范

16、围，均值，标准差）立即算出表4中的结果。当然，也可以利用数据标准化公式*Zi_Zi二Z_=乙一Z.var(Zi)二i逐一计算。将残差平方再求和，便得到残差平方和即剩余平方和，即有nnSSe八s2八(yi-刃2i4i4=16.10676利用Excel的求平方和命令sumsq容易验证上述结果。以最大积雪深度xi为自变量，以残差身为因变量,差点列的分布越是没有趋势（没有规则，即越是随机）作散点图，可得残差图（图3）。残,回归的结果就越是可靠。用最大积雪深度Xi为自变量，用灌溉面积yi及其预测值？为因变量，作散点图，可得线性拟合图（图4）。最大积雪深度x（米）ResidualPlot-1-2-3101

17、5202530最大积雪深度x（米）图3残差图最大积雪深度x（米）LineFitPlotoooooooo654321亩厂积面溉灌灌溉面积y（千亩）预测灌溉面积y（千亩）102030最大积雪深度x（米）图4线性拟合图第五部分，概率输出结果在选项输出中，还有一个概率输出（ProbabilityOutput）表（表5）。第一列是按等差数列设计的百分比排位，第二列则是原始数据因变量的自下而上排序（即从小到大）一一选中图1中的第三列（C歹U）数据，用鼠标点击自下而上排序按钮,立即得到表5中的第二列数值。当然，也可以沿着主菜单的“数据（D）一叁排序（）”路径，打开数据排序选项框，进行数据排序。用表5中的数据作散点图，可以得到Excel所谓的正态概率图（图5）。表5概率输出表百分比排位灌溉面积y（千田）519.3ri528.6r2529.23534.1r4535.6二5537.46540.57545_8546