Excel回歸分析結果的詳細闡釋

1、Excel回歸分析結果的詳細闡釋利用Excel的數據分析進行回歸，可以得到一系列的統(tǒng)計參量。下面以連續(xù)10年積雪深度和灌溉面積序列（圖1）為例給予詳細的說明。ABC1年份最大積雪深度咒冰）灌溉面積式千畝）2r197115.228.63r197210.419.34r197321.240.55r197418.635.66r197526.448.97r197623.4458197713.529.29197316.73111019792446.711198019.13。4圖1連續(xù)10年的最大積雪深度與灌溉面積（19711980）回歸結果摘要（SummaryOutput）如下（圖2）：ABcDEFrgL

2、hLiSUMMARYOUTPUT回歸統(tǒng)計Multiple6.9S9416RSquareQ.978944Adjusted0.976312標準誤差1.418924觀測值10方差分析dfssMSFmificanceF回歸分析1748.8542748.8542371.94535,42E-08夠316.106762.013345總計g764.961Cofficien'標準誤差tStatP-valueLower9&%Uor)er限95,。止限第.0¥Intercep12.3564331.E278761.2891670.233363-1.858656.57153-1.858656.

3、57153最大積雪中,1,8129210.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、部分：回歸統(tǒng)計表這一部分給出了相關系數、測定系數、校正測定系數、標準誤差和樣本數目如下(表1):表1回歸統(tǒng)計表回歸統(tǒng)計Multiple0.989416RSquare0.978944Adjusted0.976312標準誤差1.418924觀測值10逐行說明如下：Multiple對應的數據是相關系數(correlationcoefficient),即R=0.989416。RSquare對應的數值為測定系數(determinationcoefficient),或稱擬合優(yōu)度(goodnessoffit),它是相關系數的平方，即有R2=0.9894162=0.978944。Adjusted對應的是校正測

5、定系數(adjusteddeterminationcoefficient),計算公式為(n-1)(1-R2)Ra=n-m-1(10-1)(1-0.978944)Ra=1-二0.976312式中n為樣本數，m為變量數，R2為測定系數。對于本例，n=10,m=1,R2=0.978944,代入上式得10-1-1標準誤差(standarderror)對應的即所謂標準誤差，計算公式為“：一1sse這里SSe為剩余平方和，可以從下面的方差分析表中讀出，即有SSe=16.10676,代入上式可得J1*16.10676=1.418924.10-1-1最后一行的觀測值對應的是樣本數目，即有n=10o第二部分，方

6、差分析表方差分析部分包括自由度、誤差平方和、均方差、F值、P值等(表2)。表2方差分析表(ANOVA方差分析dfSSMSF;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,d

7、fe=n-m-1=8,dft=n-1=9。第二列SS對應的是誤差平方和，或稱變差。第一行為回歸平方和或稱回歸變差SSr,即有n2SSr='(%-yi)2=748,8542i4它表征的是因變量的預測值對其平均值的總偏差。第二行為剩余平方和（也稱殘差平方和）或稱剩余變差SSe,即有n2SSe="(yi-yPi)2=16.10676i4它表征的是因變量對其預測值的總偏差，這個數值越大,的標準誤差即由SSe給出。第三行為總平方和或稱總變差SSt,即有意味著擬合的效果越差。上述的nSSr八(y-弘)2i4=764,961它表示的是因變量對其平均值的總偏差。容易驗證748.8542+16

8、.10676=764,961,即有SSrSSe=SSt而測定系數就是回歸平方和在總平方和中所占的比重，即有2SSr748,8542R0.978944SSt764.961顯然這個數值越大，擬合的效果也就越好。第四列MS對應的是均方差，它是誤差平方和除以相應的自由度得到的商。歸均方差MSr,即有第一行為回SSrMSr=dfr第二行為剩余均方差MSe,即有SSeMSe=dfe748.8542=748,8542116,10676=2.0133458顯然這個數值越小，擬合的效果也就越好。第四列對應的是F值，用于線性關系的判定。對于一元線性回歸,F值的計算公式為式中R2=0,978944,R2-(1-R2

9、)n-m-1dfe=10-1-1=8,因此8*0,9789441-0,978944dfeR21-R2-371.9453第五列SignificanceF對應的是在顯著性水平下的F°臨界值，其實等于P值，即棄真概率。所謂“棄真概率”即模型為假的概率，顯然1-P便是模型為真的概率?？梢?，P值越小越好。對于本例，P=0.0000000542<0.0001,故置信度達到99.99%以上。第三部分，回歸參數表回歸參數表包括回歸模型的截距、斜率及其有關的檢驗參數（表3）。表3回歸參數表C'oefficients標準誤差tStatIntereept2.3564379291.827876

10、1.289167最大積雪深度米）1.8129210650.09400219,28588P-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.8129為y=2.35641.8129%.第二列為回歸系數的標準誤差（用sa或sb表示），誤差值越小，表明參數的

11、精確度越高。這個參數較少使用，只是在一些特別的場合出現(xiàn)。例如L.Benguigui等人在Whenandwhereisacityfractal?一文中將斜率對應的標準誤差值作為分形演化的標準，建議采用0.04作為分維判定的統(tǒng)計指標（參見EPB2000）。不常使用標準誤差的原因在于：其統(tǒng)計信息已經包含在后述的t檢驗中。第三列tStat對應的是統(tǒng)計量t值，用于對模型參數的檢驗，需要查表才能決定。t值是回歸系數與其標準誤差的比值，即有a?bta,tbsa根據表3中的數據容易算出:2.3564381.827876=1.289167,tb1.8129210.094002=19.28588對于一元線性回歸，

12、t值可用相關系數或測定系數計算，公式如下t=1-R2將R=0.989416、n=10、m=1代入上式得到=19.285880.9894161-0.989416210-1-1對于一元線性回歸，F(xiàn)值與t值都與相關系數R等價，因此，相關系數檢驗就已包含了這部分信息。但是，對于多元線性回歸，t檢驗就不可缺省了。第四列Pvalue對應的是參數的P值（雙側）。當P<0.05時，可以認為模型在爐0.05的水平上顯著，或者置信度達到95%;當P<0.01時，可以認為模型在妹0.01的水平上顯著，或者置信度達到99%;當P<0.001時，可以認為模型在妹0.001的水平上顯著，或者置信度達到9

13、9.9%。對于本例，P=0.0000000542<0.0001,故可認為在a=0.0001的水平上顯著，或者置信度達到99.99%oP值檢3與t值檢驗是等彳的，但P值不用查表，顯然要方便得多。最后幾列給出的回歸系數以95%為置信區(qū)間的上限和下限。可以看出，在行0.05的顯著水平上，截距的變化上限和下限為-1.85865和6.57153,即有-1.85865<a<6.57153斜率的變化極限則為1.59615和2.02969,即有1.59615<b<2.02969第四部分，殘差輸出結果這一部分為選擇輸出內容，如果在“回歸”分析選項框中沒有選中有關內容，則輸出結果不會

14、給出這部分結果。殘差輸出中包括觀測值序號（第一列，用i表示），因變量的預測值（第二列，用夕表示），殘差（residuals,第三列，用e表示）以及標準殘差（表4）。表4殘差輸出結果觀測值預測灌溉面積八千田）殘差標準殘差129.91283811-1.31284-0.98136221.210817-1.91082-1.42836340.7903645-0,29036-0.21705436.07676973-0.47677-0.35639550,21755404-L31755-0.98489644.778790840.221209。.165356726,83087232.369128L7709478

15、32.632219711.467781.097181945.866543480.8334570.6230171036.98323027Q416770.31154預測值是用回歸模型?i=2.35641.8129為計算的結果，式中X即原始數據的中的自變量。從圖1可見，Xi=15.2,代入上式，得?=2.35641.8129x1=2.35641.8129*15.2-29.91284其余依此類推。殘差e的計算公式為ei=yi-y?i從圖1可見，y1=28.6,代入上式，得到e=y-=28.6-29.91284=-1.31284其余依此類推。標準殘差即殘差的數據標準化結果，借助均值命令average和標

16、準差命令stdev容易驗證，殘差的算術平均值為0,標準差為1.337774。利用求平均值命令standardize（殘差的單元格范圍，均值，標準差）立即算出表4中的結果。當然，也可以利用數據標準化公式*Zi_Zi二Z_=乙一Z.var(Zi)二i逐一計算。將殘差平方再求和，便得到殘差平方和即剩余平方和，即有nnSSe八s2八(yi-刃2i4i4=16.10676利用Excel的求平方和命令sumsq容易驗證上述結果。以最大積雪深度xi為自變量，以殘差身為因變量,差點列的分布越是沒有趨勢（沒有規(guī)則，即越是隨機）作散點圖，可得殘差圖（圖3）。殘,回歸的結果就越是可靠。用最大積雪深度Xi為自變量，用

17、灌溉面積yi及其預測值？為因變量，作散點圖，可得線性擬合圖（圖4）。最大積雪深度x（米）ResidualPlot-1-2-31015202530最大積雪深度x（米）圖3殘差圖最大積雪深度x（米）LineFitPlotoooooooo654321畝J積面溉灌灌溉面積y（千畝）預測灌溉面積y（千畝）102030最大積雪深度x（米）圖4線性擬合圖第五部分，概率輸出結果在選項輸出中，還有一個概率輸出（ProbabilityOutput）表（表5）。第一列是按等差數列設計的百分比排位，第二列則是原始數據因變量的自下而上排序（即從小到大）一一選中圖1中的第三列（C歹U）數據，用鼠標點擊自下而上排序按鈕專,立即得到表5中的第二列數值。當然，也可以沿著主菜單的“數據（D）一套排序（§）”路徑，打開數據排序選項框，進行數據排序。用表5中的數據作散點圖，可以得到Excel所謂的正態(tài)概率圖（圖5）。表5概率輸出表百分比排位灌溉面積y（千田）519.3r1528.6r2529.23534.1r4535.6二5537.46540.57545_8546,79548.9NormalPro