Excel詳細(xì)教程之回歸分析

#回歸分析可以靈活應(yīng)用于描述不同函數(shù)形式的變量關(guān)系。一元回歸模型中的參數(shù)的計(jì)算公式為,其中，n為樣本觀測(cè)點(diǎn)數(shù)，上劃線的X和Y分別代表均值。說-罰巴=;—觸-町<!?1FntQycQpi=￥-Slope-X根據(jù)上述公式，可以在原始數(shù)據(jù)基礎(chǔ)上逐步計(jì)算回歸的參數(shù)估計(jì)值。ABCDEFGH1_1」；_l_L1rlT16.002sto.ooSO.00:Denonninawrn5寸i幾V花也汨門［可X'sEDD■11ID(2CU40D.DD-amClaoP2￡0(LQ.QQ)10Q.QD-a-.oi■4,00wJ40檢-7230sao0-000-00Q-OOD.OQ飛4DQ泗-34丸日叩1'2.DO10D.DD0.D1&.□[)3Q汕0?9専50LGOO20.DO^OD.DD0.D2aa.ao40720iQ-111020ISwnLSC.COVvuUUO-UUL-UVITq?oSCO12:NewMeanK30.ODSk<pe16.OD13NewMmdV6CO.OOUw】ntg「口口:130.00MevOcnomirafrcfr1UUUnu15avM.-M&ariCXiivz也目FWStoo1ti10300(8Q.QQ)4K.QQ-9<0i-6,00180400rasomn(10.00)100,00-a.aj-4,0010340113g訓(xùn)LQQQD.QOD.DD0■皿DRDZQ旳0an19440gnu10.DOICiD.DD0.D13020550LGOO20.DO4OD.DD0.D22a.ao40820afF2:SunnISO33000?00lOOCOO0.0010.UU509B0?IQ曲X可以看出：1）回歸線通過X和Y的均值點(diǎn)；2）最小二乘斜率是樣本Y值的加權(quán)平均值；3）權(quán)重之和為零；回歸模型的函數(shù)形式回歸分析可以靈活應(yīng)用于描述不同函數(shù)形式的變量關(guān)系。線性模型可分為參數(shù)線性和變量線性模型，線性回歸僅指參數(shù)線性的回歸模型，而解釋變量無需是線性的。比較：F二垢+加2和y二俎+代主要的參數(shù)線性變量非線性模型形式:

?線性-對(duì)數(shù)：y=a+b*ln(x)+u?對(duì)數(shù)-線性：In(y)=a+b*x+u?雙對(duì)數(shù)：：ln(y)=a+b*ln(x)+u?多項(xiàng)式：y=a+b*x+c*x人2+d*x人3++u?雙曲：y=a+b*(1/x)+uExcel中處理非線性模型，可通過兩種方法實(shí)現(xiàn)：數(shù)據(jù)變換或趨勢(shì)線方法。前者是將非現(xiàn)性的數(shù)據(jù)轉(zhuǎn)換為線性數(shù)據(jù)后進(jìn)行回歸分析，方法和普通回歸分析相同，后者則是利用Excel的添加趨勢(shì)線功能，選擇合適的模型形式。本例采用三種不同的模型形式進(jìn)行擬合，效果及比較結(jié)果如下:E1FCHI11E1FCHI11X11U1N1StfflS；Irr^OP2；:i■i578泌制1SIW1J11MOT??afl對(duì)本例不同模型擬合的對(duì)比結(jié)果表明雙曲模型的殘差平方和最低。OLS回歸的缺陷：蒙特卡洛模擬演示OLS回歸在處理異常值時(shí)的表現(xiàn)較差。本節(jié)通過一個(gè)實(shí)例和蒙特卡洛模擬分析方法說明OLS回歸在穩(wěn)健性方面的缺陷。考慮兩個(gè)數(shù)據(jù)樣本，其中一個(gè)為干凈數(shù)據(jù)，另一個(gè)樣本包含一個(gè)異常值(J19單元格)：

3Q「ATT9ToCLEAH?DIRTY25-is.n13J:；■TT8

g

ill

1:D.QssT"41Ji：<?"'^ioT￡.E；11.6r'afi"：3'.'1:-185:2；.￡3Q「ATT9ToCLEAH?DIRTY25-is.n13J:；■TT8

g

ill

1:D.QssT"41Ji：<?"'^ioT￡.E；11.6r'afi"：3'.'1:-185:2；.￡；'?、Mi>i■■■■■?g-|'S3c閱因402001刮包40200ClearsD^la6-3.52.5■C-l.i!-J-is.img?ioi■fliiiSGIJ1.B8GIQ111旳1W皿12D1CO閨mDlriyDffin10150.5eTSL22翌202.a1i，鯉蜒辻L—N0.5疋吊融312063『'"l■"13.21-IS.ici.g懂小10.1從圖表和樣本的描述統(tǒng)計(jì)可以看出，異常值的引入導(dǎo)致OLS回歸效果發(fā)生很大變動(dòng)，也即單個(gè)數(shù)據(jù)觀察值的變動(dòng)可以完全破壞OLS回歸結(jié)果，因此OLS回歸的穩(wěn)健性存在較大缺陷。假定方程誤差項(xiàng)服從均值為0，標(biāo)準(zhǔn)差為10的正態(tài)分布，進(jìn)一步通過蒙特卡洛模擬可以觀察OLS回歸的參數(shù)結(jié)果。工作表中給定B5單元格的隨機(jī)項(xiàng)標(biāo)準(zhǔn)差，而后通過函數(shù)''=NORMINV(RAND(),0,s)''生成該分布假設(shè)下的隨機(jī)值，其中s為標(biāo)準(zhǔn)差。模擬結(jié)果的統(tǒng)計(jì)和直方圖如下：Clean亦Dirty時(shí)總體參敷均值4.993均值9.538bi5標(biāo)準(zhǔn)差0.9395標(biāo)準(zhǔn)差0.9395異常值因子1008,126最大12,&72最小2.509最小7.055EmpiricalHistogram-for1000尺巳卩巳titionsClesn-Clesn-Dirtyo51015t>1estimates本步中蒙特卡洛模擬的步驟是：獲得初