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

1、1Excel回歸分析結(jié)果的詳細(xì)闡釋（圖利用Excel的數(shù)據(jù)分析進(jìn)行回歸，可以得到一系列的統(tǒng)計(jì)參量。下面以連續(xù)10年積雪深度和灌溉面積序列1）為例給予詳細(xì)的說明。ABC1年份最大積雪探度囂冰）灌溉面積y（千畝）2197115. 228. &3197210. 419. 34197321. 240. 55197418.635.66197526.448.97197623.4458197713.529.29_ 197816.73111019792446.711198019,137.4圖1連續(xù)10年的最大積雪深度與灌溉面積（1971 1980）回歸結(jié)果摘要（Summary Output）如下（圖2

2、）:ABcDEFI_ GHISUMMARY OUTPUT回歸統(tǒng)計(jì)Multiple0. 989416R Square6 978944Adjusted0. 976312標(biāo)準(zhǔn)誤差1.418924視測值10方差分析dfssMSFmificance F回歸分析1748. 8542748.8542371. 94535. 42E-08316.106762. 013345總計(jì)g764. 961Cofficien'標(biāo)準(zhǔn)誤差t S t atP-valueLower 9&%Upper 95證限 皈址限95*朗I nt er cep 1 2. 3564381. E278761. 2891670. 23

3、3363-1. 85265&. 57153-1. 85S6E6.57153最大積雪L 8129210. 09400219. 2858S5. 42E-0B1.5961512. 029&911.5961512. 029691RESIDUAL OUTPUTPROBABILITY OUTPUT觀泌值翟溉面枳y殘差標(biāo)準(zhǔn)殘差百分比郴槪面積v（千壬）129. 91284-1.31284-0.9813&519.3221,21OS2-1. 91082-1.42S361523,6340. 79036-0. 2903&-0. 217052529.2436. 07677-0. 4767

4、7-0. 356393534,1550.21755-1.31755-0. 984S94535.6圖2利用數(shù)據(jù)分析工具得到的回歸結(jié)果第一部分：回歸統(tǒng)計(jì)表這一部分給出了相關(guān)系數(shù)、測定系數(shù)、校正測定系數(shù)、標(biāo)準(zhǔn)誤差和樣本數(shù)目如下（表1）:表1回歸統(tǒng)計(jì)表第二部分，方差分析表方差分析部分包括自由度、誤差平方和、均方差、F值、P值等（表2）。表2方差分析表（ANOVA方差分析dfSSMSFnificancE回歸分析1748. 8542748.8542371.94535. 42E-08殘差816.106762. 013345總計(jì)9764. 961逐列、分行說明如下：第一列df對應(yīng)的是自由度（degree of

5、 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-仁9。第二列SS對應(yīng)的是誤差平方和，或稱變差。第一行為回歸平方和或稱回歸變差SSr，即有nSSr =送（? -yj2 = 748.8542i =1逐行說明如下：Multiple對應(yīng)的數(shù)據(jù)是相關(guān)系數(shù)R Square對應(yīng)的數(shù)值為測定系數(shù)即有 R =0.989416 =0.

6、978944。Adjusted對應(yīng)的是校正測定系數(shù)回歸統(tǒng)計(jì)Multiple0. 989416R Square0. 973944Adjusted0. 976312標(biāo)準(zhǔn)誤差1.418924觀測值10(correlation coefficient)，即 R=0.989416。(determination coefficient)，或稱擬合優(yōu)度(goodness of fit)，它是相關(guān)系數(shù)的平方,(adjusted determ in ati on coefficie nt)，計(jì)算公式為式中n為樣本數(shù)，m為變量數(shù)，R2為測定系數(shù)。對于本例，n=10，m=1，R2=0.978944，代入上式得Ra

7、亠 Wfi.978944） =0.97631210-1-1標(biāo)準(zhǔn)誤差（standard error）對應(yīng)的即所謂標(biāo)準(zhǔn)誤差，計(jì)算公式為這里SSe為剩余平方和，可以從下面的方差分析表中讀出，即有SSe=16.10676,代入上式可得s10 -11 -1* 16.10676 "418924最后一行的觀測值對應(yīng)的是樣本數(shù)目，即有n=10。它表征的是因變量的預(yù)測值對其平均值的總偏差。第二行為剩余平方和（也稱殘差平方和）或稱剩余變差SSe，即有nSSe”： $ -必）2 =16.10676i #它表征的是因變量對其預(yù)測值的總偏差，這個數(shù)值越大，意味著擬合的效果越差。上述的y的標(biāo)準(zhǔn)誤差即由SSe給出

8、。第三行為總平方和或稱總變差SSt,即有n2SSr =遲（yi yj =764.961i 4它表示的是因變量對其平均值的總偏差。容易驗(yàn)證748.8542+16.10676=764.961，即有SSr SSe= SSt而測定系數(shù)就是回歸平方和在總平方和中所占的比重，即有R2SSrSSt748.8542764.961=0.978944顯然這個數(shù)值越大，擬合的效果也就越好第四列MS對應(yīng)的是均方差，它是誤差平方和除以相應(yīng)的自由度得到的商。第一行為回歸均方差MSr，即有第二行為剩余均方差 MSe，即有二竺7=748.8542 dfrMSeSSedfe空 2.01334584顯然這個數(shù)值越小，擬合的效果也

9、就越好。第四列對應(yīng)的是F值，用于線性關(guān)系的判定。對于一元線性回歸，F(xiàn)值的計(jì)算公式為R212n(1 R)dfeR21 - R2#式中 R2=0.978944 , dfe=10-1-1=8，因此F= 8*0.978944 =371.94531 -0.978944第五列Significanee F對應(yīng)的是在顯著性水平下的F a臨界值，其實(shí)等于 P值，即棄真概率。所謂“棄真概率”即模型為假的概率，顯然1-P便是模型為真的概率。可見，P值越小越好。對于本例，P=0.0000000542<0.0001，故置信度達(dá)到99.99%以上。第三部分，回歸參數(shù)表回歸參數(shù)表包括回歸模型的截距、斜率及其有關(guān)的檢驗(yàn)

10、參數(shù)（表3）。表3回歸參數(shù)表C'efficients標(biāo)準(zhǔn)誤差t StatIntereept2. 3564379291,8278761.289167最大積雪除度L 8129210650, 09400219. 28588P-valueLower 9硏 Upper 95% 下限 9& 0% 上限 95. 0%0. 233363-1. 858656. 5715301-L 8586546.57153015. 42E-081. 5961512. 02969131.59615082.0296913第一列Coeffieients對應(yīng)的模型的回歸系數(shù)，包括截距a=2.356437929和斜率b=

11、1.812921065，由此可以建立回歸模型? =2.3564 1.8129xiyi =2.35641.8129Xj 亠冷第二列為回歸系數(shù)的標(biāo)準(zhǔn)誤差（用?a或Sb表示），誤差值越小，表明參數(shù)的精確度越高。這個參數(shù)較少使用，只是在一些特別的場合出現(xiàn)。例如L. Benguigui等人在 When and where is a city fractal ?一文中將斜率對應(yīng)的標(biāo)準(zhǔn)誤差值作為分形演化的標(biāo)準(zhǔn)，建議采用0.04作為分維判定的統(tǒng)計(jì)指標(biāo)（參見 EPB2000）。不常使用標(biāo)準(zhǔn)誤差的原因在于：其統(tǒng)計(jì)信息已經(jīng)包含在后述的t檢驗(yàn)中。第三列t Stat對應(yīng)的是統(tǒng)計(jì)量t值，用于對模型參數(shù)的檢驗(yàn)，需要查表才能

12、決定。t值是回歸系數(shù)與其標(biāo)準(zhǔn)誤差的比值，即有tbb?b根據(jù)表3中的數(shù)據(jù)容易算出：2.356438 =1.289167,乙二 1812921 = 19.285881.8278760.094002a?a對于一元線性回歸，t值可用相關(guān)系數(shù)或測定系數(shù)計(jì)算，公式如下5#將R=0.989416、n=10、m=1代入上式得到-19.285880.9894161 -0.989416210-1 一1對于一元線性回歸，F(xiàn)值與t值都與相關(guān)系數(shù) R等價，因此，相關(guān)系數(shù)檢驗(yàn)就已包含了這部分信息。但是，對 于多元線性回歸，t檢驗(yàn)就不可缺省了。第四列P value對應(yīng)的是參數(shù)的 P值（雙側(cè)）。當(dāng)P<0.05時，可以認(rèn)

13、為模型在a=0.05的水平上顯著，或者置信度達(dá)到95%;當(dāng)P<0.01時，可以認(rèn)為模型在a=0.01的水平上顯著，或者置信度達(dá)到99%;當(dāng)P<0.001時，可以認(rèn)為模型在 a=0.001的水平上顯著，或者置信度達(dá)到 99.9%。對于本例，P=0.0000000542<0.0001，故可認(rèn)為在 a=0.0001 的水平上顯著，或者置信度達(dá)到99.99%。P值檢驗(yàn)與t值檢驗(yàn)是等價的，但 P值不用查表，顯然要方便得多。最后幾列給出的回歸系數(shù)以95%為置信區(qū)間的上限和下限?？梢钥闯觯跍?.05的顯著水平上，截距的變化上限和下限為-1.85865和6.57153，即有1.85865譽(yù)

14、 a 譽(yù) 6.57153斜率的變化極限則為1.59615和2.02969，即有1.59615 E b 蘭 2.02969第四部分，殘差輸出結(jié)果這一部分為選擇輸出內(nèi)容，如果在“回歸”分析選項(xiàng)框中沒有選中有關(guān)內(nèi)容，則輸出結(jié)果不會給出這部分結(jié)果。殘差輸出中包括觀測值序號（第一列，用i表示），因變量的預(yù)測值（第二列，用 ?表示），殘差（residuals,第三列，用ei表示）以及標(biāo)準(zhǔn)殘差（ 表4 ）。表4殘差輸出結(jié)果觀瀕值預(yù)測 灌溉面積y （千田）殘差標(biāo)準(zhǔn)殘差129. 91283811-L 31284-0* 98136221. 210817-1. 91082-1. 42836340. 7903645-

15、0.29036-0. 21705436. 07676973-0.47677-0+ 35639550. 21755404-1. 31755-0. 98489644.778790840. 2212090.165356726. 83087232. 369128L770947832. 632219711. 467781.097181945. 866543480. 8334570. 6230171036. 98323027Q 416770. 31154預(yù)測值是用回歸模型? =2.3564 1.8129Xj計(jì)算的結(jié)果，式中Xi即原始數(shù)據(jù)的中的自變量。從圖1可見，Xi=15.2，代入上式，得? =2.356

16、4 1.8129x1 =2.3564 1.8129*15.2 = 29.91284 其余依此類推。殘差ei的計(jì)算公式為ei = yi - ?從圖1可見，y1=28.6，代入上式，得到& =y1 一 ？1 =28.6 -29.91284 - -1.31284其余依此類推。標(biāo)準(zhǔn)殘差即殘差的數(shù)據(jù)標(biāo)準(zhǔn)化結(jié)果，借助均值命令average和標(biāo)準(zhǔn)差命令stdev容易驗(yàn)證，殘差的算術(shù)平均值為0，標(biāo)準(zhǔn)差為1.337774。利用求平均值命令 standardize（殘差的單元格范圍，均值，標(biāo)準(zhǔn)差）立即算出表4中的結(jié)果。當(dāng)然, 也可以利用數(shù)據(jù)標(biāo)準(zhǔn)化公式*Zi - Z乙 一 zZivar(Zi)ci逐一計(jì)算。

17、將殘差平方再求和，便得到殘差平方和即剩余平方和，即有nnSSe=為 e2 二為(yi -？)2 =16.10676i Ai A利用Excel的求平方和命令sumsq容易驗(yàn)證上述結(jié)果。以最大積雪深度 Xi為自變量，以殘差 e為因變量，作散點(diǎn)圖，可得殘差圖（ 圖3）。殘差點(diǎn)列的分布越是沒有 趨勢（沒有規(guī)則，即越是隨機(jī)），回歸的結(jié)果就越是可靠。用最大積雪深度Xi為自變量，用灌溉面積 yi及其預(yù)測值?為因變量，作散點(diǎn)圖，可得線性擬合圖（圖4）。6最大積雪深度x（米）Residual Plot2 -1 -差C1015*20*2530殘0L05-1 -2 -3最大積雪深度x（米）圖3殘差圖最大積雪深度x（

18、米）Line Fit Plot+灌溉面積y（千畝）-預(yù)測灌溉面積 y（千畝）0102030最大積雪深度x（米）圖4線性擬合圖第五部分，概率輸出結(jié)果在選項(xiàng)輸出中，還有一個概率輸出（Probability Output ）表（表5）。第一列是按等差數(shù)列設(shè)計(jì)的百分比排位，第二列則是原始數(shù)據(jù)因變量的自下而上排序（即從小到大）一一選中圖1中的第三列（C列）數(shù)據(jù)，用鼠標(biāo)點(diǎn)擊自下而上排序按鈕立即得到表 5中的第二列數(shù)值。當(dāng)然，也可以沿著主菜單的“數(shù)據(jù) （D） 排序（S）”路徑,打開數(shù)據(jù)排序選項(xiàng)框，進(jìn)行數(shù)據(jù)排序。用表5中的數(shù)據(jù)作散點(diǎn)圖，可以得到Excel所謂的正態(tài)概率圖（圖5）。表5概率輸出表百分比排位曜溉面積譏千田）519.3r1528.6r2529.23534.1r4535.6r5537.46540,57545二8546, 7954& 9Normal Probabilit