Excel回歸分析結(jié)果的詳細闡釋

? ? ? ?利用Excel的數(shù)據(jù)分析進行回歸，可以得到一系列的統(tǒng)計參量。下面以連續(xù)10年積雪深度和灌溉面積序列（圖1）為例給予詳細的說明。

? ? ? ?回歸結(jié)果摘要（Summary Output）如下（圖2）：

第一部分：回歸統(tǒng)計表

? ? ? ?這一部分給出了相關(guān)系數(shù)、測定系數(shù)、校正測定系數(shù)、標準誤差和樣本數(shù)目如下（表1）：

表1 回歸統(tǒng)計表

逐行說明如下：

? ? ? ?Multiple對應的數(shù)據(jù)是相關(guān)系數(shù)(correlation coefficient)，即R=0.989416。

? ? ? ?R Square對應的數(shù)值為測定系數(shù)(determination coefficient)，或稱擬合優(yōu)度(goodness of fit)，它是相關(guān)系數(shù)的平方，即有R2=0.9894162=0.978944。

? ? ? ?Adjusted對應的是校正測定系數(shù)(adjusted determination coefficient)，計算公式為

? ? ? ?式中n為樣本數(shù)（實驗總次數(shù)，本例n=10），m為變量數(shù)（參數(shù)樣本總數(shù)，本例即最大積雪深度和灌溉面積，m=2），R2為測定系數(shù)。對于本例，n=10，m=1，R2=0.978944，代入上式得

? ? ? ?標準誤差（standard error）對應的即所謂標準誤差，計算公式為

? ? ? ?這里SSe為剩余平方和，可以從下面的方差分析表中讀出，即有SSe=16.10676，代入上式可得

? ? ? ?最后一行的觀測值對應的是樣本數(shù)目，即有n=10。

?

第二部分，方差分析表

? ? ? ?方差分析部分包括自由度、誤差平方和、均方差、F值、P值等（表2）。

表2 方差分析表（ANOVA）

F臨界值可用excel求解，方法如下：

F臨界值求法：

FINV(a,因子自由度,誤差項自由度)

a為1-置信水平，即犯錯幾率，一般取0.05

因子自由度即回歸分析df，誤差項自由度即殘差df。

逐列、分行說明如下：

? ? ? ?第一列df對應的是自由度（degree of freedom），第一行是回歸自由度dfr，等于變量數(shù)目，即dfr=m；第二行為殘差自由度dfe，等于樣本數(shù)目減去變量數(shù)目再減1，即有dfe=n-m-1；第三行為總自由度dft，等于樣本數(shù)目減1，即有dft=n-1。對于本例，m=1，n=10，因此，dfr=1，dfe=n-m-1=8，dft=n-1=9。

? ? ? ?第二列SS對應的是誤差平方和，或稱變差。第一行為回歸平方和或稱回歸變差SSr，即有

? ? ? ?它表征的是因變量的預測值對其平均值的總偏差。

? ? ? ?第二行為剩余平方和（也稱殘差平方和）或稱剩余變差SSe，即有

? ? ? ?它表征的是因變量對其預測值的總偏差，這個數(shù)值越大，意味著擬合的效果越差。上述的y的標準誤差即由SSe給出。

? ? ? ?第三行為總平方和或稱總變差SSt，即有

? ? ? ?它表示的是因變量對其平均值的總偏差。容易驗證748.8542+16.10676=764.961，即有

? ? ? ?而測定系數(shù)就是回歸平方和在總平方和中所占的比重，即有

? ? ? ?顯然這個數(shù)值越大，擬合的效果也就越好。

? ? ? ?第四列MS對應的是均方差，它是誤差平方和除以相應的自由度得到的商。第一行為回歸均方差MSr，即有

? ? ? ?第二行為剩余均方差MSe，即有

? ? ? ?顯然這個數(shù)值越小，擬合的效果也就越好。

? ? ? ?第四列對應的是F值，用于線性關(guān)系的判定。對于一元線性回歸，F(xiàn)值的計算公式為

式中R2=0.978944，dfe=10-1-1=8，因此

? ? ? ?第五列Significance F對應的是在顯著性水平下的Fα臨界值，其實等于P值，即棄真概率。所謂“棄真概率”即模型為假的概率，顯然1-P便是模型為真的概率。可見，P值越小越好。對于本例，P=0.0000000542<0.0001，故置信度達到99.99%以上。

?

? ? ? ?第三部分，回歸參數(shù)表

? ? ? ?回歸參數(shù)表包括回歸模型的截距、斜率及其有關(guān)的檢驗參數(shù)（表3）。

表3 回歸參數(shù)表

?

?

? ? ? ?第一列Coefficients對應的模型的回歸系數(shù)，包括截距a=2.356437929和斜率b=1.812921065，由此可以建立回歸模型

或

? ? ? ?第二列為回歸系數(shù)的標準誤差（用或表示），誤差值越小，表明參數(shù)的精確度越高。這個參數(shù)較少使用，只是在一些特別的場合出現(xiàn)。例如L. Benguigui等人在When and where is a city fractal?一文中將斜率對應的標準誤差值作為分形演化的標準，建議采用0.04作為分維判定的統(tǒng)計指標（參見EPB2000）。

? ? ? ?不常使用標準誤差的原因在于：其統(tǒng)計信息已經(jīng)包含在后述的t檢驗中。

? ? ? ?第三列t Stat對應的是統(tǒng)計量t值，用于對模型參數(shù)的檢驗，需要查表才能決定。t值是回歸系數(shù)與其標準誤差的比值，即有

根據(jù)表3中的數(shù)據(jù)容易算出：

對于一元線性回歸，t值可用相關(guān)系數(shù)或測定系數(shù)計算，公式如下

將R=0.989416、n=10、m=1代入上式得到

? ? ? ?對于一元線性回歸，F(xiàn)值與t值都與相關(guān)系數(shù)R等價，因此，相關(guān)系數(shù)檢驗就已包含了這部分信息。但是，對于多元線性回歸，t檢驗就不可缺省了。

? ? ? ?第四列P value對應的是參數(shù)的P值（雙側(cè)）。當P<0.05時，可以認為模型在α=0.05的水平上顯著，或者置信度達到95%；當P<0.01時，可以認為模型在α=0.01的水平上顯著，或者置信度達到99%；當P<0.001時，可以認為模型在α=0.001的水平上顯著，或者置信度達到99.9%。對于本例，P=0.0000000542<0.0001，故可認為在α=0.0001的水平上顯著，或者置信度達到99.99%。P值檢驗與t值檢驗是等價的，但P值不用查表，顯然要方便得多。

? ? ? ?最后幾列給出的回歸系數(shù)以95%為置信區(qū)間的上限和下限?？梢钥闯?，在α=0.05的顯著水平上，截距的變化上限和下限為-1.85865和6.57153，即有

斜率的變化極限則為1.59615和2.02969，即有

第四部分，殘差輸出結(jié)果

? ? ? ?這一部分為選擇輸出內(nèi)容，如果在“回歸”分析選項框中沒有選中有關(guān)內(nèi)容，則輸出結(jié)果不會給出這部分結(jié)果。

? ? ? ?殘差輸出中包括觀測值序號（第一列，用i表示），因變量的預測值（第二列，用表示），殘差（residuals，第三列，用ei表示）以及標準殘差（表4）。

表4 殘差輸出結(jié)果

預測值是用回歸模型

計算的結(jié)果，式中xi即原始數(shù)據(jù)的中的自變量。從圖1可見，x1=15.2，代入上式，得

其余依此類推。

殘差ei的計算公式為?????????????????????????????????

從圖1可見，y1=28.6，代入上式，得到

其余依此類推。

? ? ? ?標準殘差即殘差的數(shù)據(jù)標準化結(jié)果，借助均值命令average和標準差命令stdev容易驗證，殘差的算術(shù)平均值為0，標準差為1.337774。利用求平均值命令standardize(殘差的單元格范圍，均值，標準差)立即算出表4中的結(jié)果。當然，也可以利用數(shù)據(jù)標準化公式

逐一計算。將殘差平方再求和，便得到殘差平方和即剩余平方和，即有

? ? ? ?利用Excel的求平方和命令sumsq容易驗證上述結(jié)果。

? ? ? ?以最大積雪深度xi為自變量，以殘差ei為因變量，作散點圖，可得殘差圖（圖3）。殘差點列的分布越是沒有趨勢（沒有規(guī)則，即越是隨機），回歸的結(jié)果就越是可靠。

用最大積雪深度xi為自變量，用灌溉面積yi及其預測值為因變量，作散點圖，可得線性擬合圖（圖4）。

?

第五部分，概率輸出結(jié)果

? ? ? ?在選項輸出中，還有一個概率輸出（Probability Output）表（表5）。第一列是按等差數(shù)列設(shè)計的百分比排位，第二列則是原始數(shù)據(jù)因變量的自下而上排序（即從小到大）——選中圖1中的第三列（C列）數(shù)據(jù)，用鼠標點擊自下而上排序按鈕，立即得到表5中的第二列數(shù)值。當然，也可以沿著主菜單的“數(shù)據(jù)(D)→排序(S)”路徑，打開數(shù)據(jù)排序選項框，進行數(shù)據(jù)排序。

用表5中的數(shù)據(jù)作散點圖，可以得到Excel所謂的正態(tài)概率圖（圖5）。

表5 概率輸出表

【幾點說明】

? ? ? ??第一，多元線性回歸與一元線性回歸結(jié)果相似，只是變量數(shù)目m≠1，F(xiàn)值和t值等統(tǒng)計量與R值也不再等價,因而不能直接從相關(guān)系數(shù)計算出來。

? ? ? ? 第二，利用SPSS給出的結(jié)果與Excel也大同小異。當然，SPSS可以給出更多的統(tǒng)計量，如DW值。在表示方法上，SPSS也有一些不同，例如P Value（P值）用 Sig.（顯著性）表征，因為二者等價。只要能夠讀懂Excel的回歸摘要，就可以讀懂SPSS回歸輸出結(jié)果的大部分內(nèi)容。

?