Excel回归结果的解读

#Excel回归结果的解读利用Excel的数据分析进行回归，可以得到一系列的统计参量。下面以连续10年积雪深度和灌溉面积序列（图1）为例给予详细的说明。AIBBC1年份最大积雪洙度風米）灌溉面积y（干田〕2197115.228.63197210.419.34197321.240.5519741S.635.66197526.448.9719恥23.4458197713.529.29197S1&.734.11019792446.71119S019.137.4图1连续10年的最大积雪深度与灌溉面积（1971—1980）回归结果摘要（SummaryOutput）如下（图2）：ABCDEFGHISIHMAP.YCUTPUT回归统卜Multiple0.989416RSqusre0.978944AdJusted0.976312标推误差1.412924观测價10方差分析df弟HSF;mfic：ELticeF回归分析1748.8542748.8542371.94535.425-03残差816.106762.013345总计9764.961Cnpffir.i亡rr标淮误莘tS：tatf.nwpTAE%TTpnpr9硏F限95.01匕限95.刖Iniereep12.3564381.8278761.2391670.233363-1.858656.5T153-1.858656.57153最大积雪?1.8129210.09403219.285885.42E-C81.5961512.0296911.5961512.029691RESIDUALOUTPUTPROBABILITYOUTPUT观测值體溉面和y残差踊准残差耳分比排弐面积辛〔千田|129.91284-1.31284-0.98136519.3221.21082-1.910B2-1.428361528.6540.79035-0.29035-0.Z1TQ5鹽.J\36.07677-0.47677-0.356393534.1550.21755-1.31755-0.984894535.6图2利用数据分析工具得到的回归结果第一部分：回归统计表这一部分给出了相关系数、测定系数、校正测定系数、标准误差和样本数目如下(表1):表1回归统计表回归统计Multiple0.989416RSquare0.978944Adjusted0.976312标淮误差1.418924观测值10逐行说明如下：Multiple对应的数据是相关系数(correlationcoefficient)，即R=0.989416。RSquare对应的数值为测定系数(determinationcoefficient)，或称拟合优度(goodnessoffit),它是相关系数的平方，即有R2=0.9894162=0.978944。Adjusted对应的是校正测定系数(adjusteddeterminationcoefficient)，计算公式为1_(n二1)(1—R2)n一m一1式中n为样本数，m为变量数，R2为测定系数。对于本例，n=10，m=1，R2=0.978944，代入上式得R=1_(1°一1)(1一°.978944)=0.976312a10一1一1标准误差(standarderror)对应的即所谓标准误差，计算公式为这里SSe为剩余平方和，可以从下面的方差分析表中读出，即有SSe=16.10676,代入上式可得I1s二*16.10676二1.41892410—1—1最后一行的观测值对应的是样本数目，即有n=10。第二部分，方差分析表方差分析部分包括自由度、误差平方和、均方差、F值、P值等(表2)。表2方差分析表(AN0VA)方差分析dfSS1ISFficance回归分析残差总计1748.S542T48.8542371.94535.42E-08816.10&7&2.0133459764.961逐列、分行说明如下：第一列df对应的是自由度(degreeoffreedom)，第一行是回归自由度dfr,等于变量数目,即dfr=m；第二行为残差自由度dfe，等于样本数目减去变量数目再减1，即有dfe=n-m-1；第三行为总自由度dft,等于样本数目减1,即有dft=n-1。对于本例，m=1，n=10，因此，

dfr=l,dfe=n-m-l=8,dft=n-l=9。第二列SS对应的是误差平方和，或称变差。第一行为回归平方和或称回归变差SSr,即有SSr二工(y-y)2二748.8542iii=1它表征的是因变量的预测值对其平均值的总偏差。SSr又称组间离差平方和，反应出不同的因子对样本波动的影响第二行为剩余平方和(也称残差平方和)或称剩余变差SSe，即有SSe=工(y-y)2=16.10676iii=1它表征的是因变量对其预测值的总偏差，这个数值越大，意味着拟合的效果越差。上述的y的标准误差即由SSe给出。SSe又称组内离差平方和，是不考虑组间方差的纯随机影响第三行为总平方和或称总变差SSt，即有SSt=£(y-y)2=764.961iii=1它表示的是因变量对其平均值的总偏差。容易验证748.8542+16.10676=764.961,即有SSr+SSe=SSt总离差平方和=组间离差平方和+组内离差平方和丄样本数据的波动有两个来源：一个是随机波动，一个是因子影响。样本数据的波动，可通过总离差平方和来反映。这个总离差平方和可分解为组间方差和组内方差两部分。而测定系数就是回归平方和在总平方和中所占的比重，即有R2=H=翳=0.978944显然这个数值越大，拟合的效果也就越好。方差、均方差：表示一组数相对平均值的离散程度丄R2：预测值与实际值相对平均值的分布情况比较，越接近1,说明预测值和实际值的分布情况越接近。第四列MS对应的是均方差，它是误差平方和除以相应的自由度得到的商。第一行为回归均方差MSr，即有MSr=竺=748.8542=748.8542第二行为剩余均方差MSe第二行为剩余均方差MSe，即有空=业空=2.013345MSe=dfe8显然这个数值越小，拟合的效果也就越好。对于一元线性回归，F值的计算公式为第四列对应的是对于一元线性回归，F值的计算公式为dfeR2(1-R2)式中R2=0.978944,dfe=10-l-l=8,因此8*0978944F=80.978944=371.94531-0.978944丄方差、均方差：表示一组数相对平均值的离散程度丄F检验完整公式SSRF=MSR=MSEnSSEn-m-1丄F检验法是英国统计学家Fisher提出的，主要通过比较两组数据的均方差，以确定他们的精密度是否有显著性差异。F<F表表明两组数据没有显著差异；F三F表表明两组数据存在显著差异。丄此处的F检验是比较回归均方差（组间均方差）和剩余均方差（组内均方差），如果组间均方差明显大于组内均方差，说明数据波动的主要来源是组间均方差，因子是引起波动的主要原因，可认为因子影响是显著的。第五列SignificanceF对应的是在显著性水平下的Fa临界值，其实等于P值，即弃真概率。所谓“弃真概率”即模型为假的概率，显然1-P便是模型为真的概率。可见，P值越小越好。对于本例，P=0.0000000542v0.0001，故置信度达到99.99%以上。第三部分，回归参数表回归参数表包括回归模型的截距、斜率及其有关的检验参数（表3）。表3回归参数表CoefficiEnts标准误差tStatIntercept2.35S4379291.S278761.289167最大积雪探度工（米）1.8129210650.09400219.28588P~valueLower95^Upper9隣下限95.0%上限95血0.233363-1.858G56.5715301-1.6.57153015.42E-O01.5961512.02969131.59615082.0296913第一列Coefficients对应的模型的回归系数，包括截距a=2.356437929和斜率B=1.812921065，由此可以建立回归模型Y=2.3564+1.8129xTOC\o"1-5"\h\zIIY=2.3564+1.8129x+£III第二列为回归系数的标准误差（用s或S表示），误差值越小，表明参数的精确度越高。AB这个参数较少使用，只是在一些特别的场合出现。例如L.Benguigui等人在WhenandwhereIsacityfractal?一文中将斜率对应的标准误差值作为分形演化的标准，建议采用0.04作为分维判定的统计指标（参见EPB2000）。不常使用标准误差的原因在于：其统计信息已经包含在后述的t检验中。第三列tStat对应的是统计量t值，用于对模型参数的检验，需要查表才能决定t值是

回归系数与其标准误差的比值，即有at=-asabt=-bsb根据表3中的数据容易算出：2.3564381.812921t==1.289167，t——19.28588a1.827876b0.094002对于一元线性回归，t值可用相关系数或测定系数计算，公式如下Rt=r:1-R2\n—m—1将R=0.989416、n=10、m=1代入上式得到=19.28588=19.28588t==：1—0.9894162V10—1—1对于一元线性回归，F值与t值都与相关系数R等价，因此，相关系数检验就已包含了这部分信息。但是，对于多元线性回归，t检验就不可缺省了。第四列Pvalue对应的是参数的P值（双侧）。当PV0.05时，可以认为模型在a=0.05的水平上显著，或者置信度达到95%；当PV0.01时，可以认为模型在a=0.01的水平上显著，或者置信度达到99%；当PV0.001时，可以认为模型在a=0.001的水平上显著，或者置信度达到99.9%。对于本例，P=0.0000000542v0.0001，故可认为在a=0.0001的水平上显著，或者置信度达到99.99%。P值检验与t值检验是等价的，但P值不用查表，显然要方便得多。最后几列给出的回归系数以95%为置信区间的上限和下限。可以看出，在a=0.05的显著水平上，截距的变化上限和下限为-1.85865和6.57153，即有—1.85865<a<6.57153斜率的变化极限则为1.59615和2.02969，即有1.59615<b<2.02969第四部分，残差输出结果这一部分为选择输出内容，如果在“回归”分析选项框中没有选中有关内容，则输出结果不会给出这部分结果。残差输出中包括观测值序号（第一列，用i表示），因变量的预测值（第二列，用y.表i示），残差（residuals，第二列，用e,表示）以及标准残差（表4）。表4残差输出结果观测值预测權溉面积讽千田〕残差标准残差129.912S3S11-1.31284-0.98136221.210817-1.91082-1.42836340.7903&45-0.29036-0.21705436.07676973-0.47677-0.35639550.21755404-1.31755-0.98489&44.778790840.2212090.165356726.S3087232.3691281.770947832.632219711.467781.097181945.S665434S0.8334570.6230171036.983230270.416770.31154预测值是用回归模型y二2.3564+1.8129xii计算的结果，式中X.即原始数据的中的自变量。从图1可见，x1=15.2，代入上式，得y二2.3564+1.8129x二2.3564+1.8129*15.2二29.9128411其余依此类推。残差e.的计算公式为e二y-yiii从图1可见，y1=28.6，代入上式，得到e二y-y二28.6-29.91284=-1.31284111其余依此类推。标准残差即残差的数据标准化结果，借助均值命令average和标准差命令stdev容易验证，残差的算术平均值为0标准差为1.337774。利用求平均值命令standardize（残差的单元格范围，均值，标准差）立即算出表4中的结果。当然，也可以利用数据标准化公式z—zz—zTOC\o"1-5"\h\zz*=i=i-°x.'var（z）bj逐一计算。将残差平方再求和，便得到残差平方和即剩余平方和，即有SSe=工e2=工（y一y）2=16.10676iiii=1i=1利用Excel的求平方和命令sumsq容易验证上述结果。以最大积雪深度x.为自变量，以残差e^为因变量，作散点图，可得残差图（图3）。残差点列的分布越是没有趋势（没有规则，即越是随机），回归的结果就越是可靠。用最大积雪深度x.为自变量，用灌溉面积y.及其预测值y为因变量，作散点图，可得j人i线性拟合图（图4）。最大积雪深度x（米）ResidualPlot1差0残01差0残0-1-2-310152025最大积雪深度x（米）图3残差图最大积雪深度x（最大积雪深度x（米）LineFitPlotooooooOooooooO654321千面溉灌•灌溉面积y（千亩）-预测灌溉面积y（千亩）102030最大积雪深度