Excel數(shù)據(jù)分析工具進(jìn)行多元回歸分析

1、使用Excel數(shù)據(jù)分析工具進(jìn)行多元回歸分析使用Excel數(shù)據(jù)分析工具進(jìn)行多元回歸分析與簡單的回歸估算分析方法基本相同。但是由于有些電腦在安裝辦公軟件時(shí)并未加載數(shù)據(jù)分析工具，所以從加載開始說起（以Excel2010版為例，其余版本都可以在相應(yīng)界面找到）。園1 （kJ巧”石打開廿關(guān)因用文件新建打印保存井左送幫Bb在彈出的“選項(xiàng)”菜單中選擇“加載項(xiàng)”，在“加載項(xiàng)”多行文本框中使用滾動(dòng)條找到并選中“分析工具庫”，然后點(diǎn)擊最下方的“轉(zhuǎn)到”，如下圖所示：在彈出的“加載宏”菜單中選擇“分析工具庫”，然后點(diǎn)擊“確定”，如下圖所示:加載完畢，在“數(shù)據(jù)”工具欄中就出現(xiàn)“數(shù)據(jù)分析”工具庫，如下圖所示:X1nT :霊

2、片汀總-?日巧；茍一融亍£ 3J1Wft*.監(jiān)區(qū)吉產(chǎn)L it那d中削洋硬如g兒氏匚工Ji*電朗P也”vnZIP _i_R>f4Utr_Sf»給出原始數(shù)據(jù)，自變量的值在 A2: 121單元格區(qū)間中，因變量的值在J2: J21中，如下圖所示：12345118. 3S61*砧118.35Es213.65Cx3E Ek1s234, 65| TB, B6 41QoTs忍5 8S3L 3S 21灶也Fk2s3454. G1 c'i'27. _ 4Ej. t63k1*2Hx22233. S9 1 +007 45. 31200. 6丄y1.91U119107S9-DL

3、1St. T89.76105 iTaTsh仏05113.35aaj10.210,2玉755L 9：4,頭 4574. 751 914, 94 4574. 72115. 1生T2.9T 3TZ7.1 1 蹈”32 :9仝314007 183. 32520l2| 帥4£41*0電520. 2 8016. 1 10L Qi233.陽 T27. 1 45.氈3沾匸p, .4-11" 4斤=昭1刁m m"” *”_ " -IAW丄 F .4S36| izaiq 1215012U02BQ12601S；. 64-613. 6510- 2需 7SiRP_8昭 鬧8強(qiáng)Ba

4、miii ?一銅乩鋼跖聽mafli阻苗師L1亠旨TbIf:G19過21蟲/411138133価1站139130甲,5 1615.5 -=1X* S"EL4574,7EL 91茲0&J時(shí)電畀1200.6i ?lfin124C5121C5'138m gLO. 210.21(J.25151亙5L迫、匪7B. 45530.2 B04& 1 WL01320. 2L714 1QL 04230125012CL0.UziU14i>7. 662121 M.S1407.6乩 1407. Sbl 14W. 67TE*7TER731<：S4 l(n. 04190420.2

5、58LC. 9190* 252,81緲：2?0：130351I8E0124： 5:蘢起7030沃D,就 19血丄仇B(yǎng)OD. 19' 19044 104.052CL 219044. 104. 0<520. 21904 10L 0404. C C154.呂(HK.770124E01215.119bb假設(shè)回歸估算表達(dá)式為:y = b0 -F biX! + b2x3 + b3x3 + b4xrx2 +t>詳i盹 +bsX2X3 + b靑/ + bax22 + bgx32試使用Excel數(shù)據(jù)分析工具庫中的回歸分析工具對(duì)其回歸系數(shù)進(jìn)行估算并進(jìn)行回歸分析: 點(diǎn)擊“數(shù)據(jù)”工具欄中中的“數(shù)據(jù)

6、分析”工具庫，如下圖所示：，然后點(diǎn)擊“確定”，如下在彈出的“數(shù)據(jù)分析”-“分析工具”多行文本框中選擇“回歸” 圖所示：分析工具11yism平H析分$J彈出“回歸”對(duì)話框并作如下圖的選擇:上述選擇的具體方法是: 在“ Y值輸入?yún)^(qū)域” 再單擊折疊按鈕返回 在“ X值輸入?yún)^(qū)域” 再單擊折疊按鈕返回 置信度可選默認(rèn)的點(diǎn)擊右側(cè)折疊按鈕，這過程也可以直接在點(diǎn)擊右側(cè)折疊按鈕，這過程也可以直接在選取函數(shù) Y數(shù)據(jù)所在單元格區(qū)域 “ Y值輸入?yún)^(qū)域”文本框中輸入 選取自變量數(shù)據(jù)所在單元格區(qū)域“ X值輸入?yún)^(qū)域”文本框中輸入J2 J21,J2: J21選完后A2: 121,A2: 121;選完后在“輸出區(qū)域”如選“新工

7、作表”，就將統(tǒng)計(jì)分析結(jié)果輸出到在新表內(nèi)。為了比較對(duì)照，我 選本表內(nèi)的空白區(qū)域，左上角起始單元格為K10.點(diǎn)擊確定后，輸出結(jié)果如下：95%。SUIIAKT mtfutMuMlvLe tO.?134147$£R Square0.034326M5山畛益口悴M3, 7Z82MB309<3««51 <»2761. 2 !. 5»552<K 茁站護(hù)I游27641、Rii ； 1、r F M暑EMWiFIH第晦上1箭曲26272s29£313zm343sX TirlibLt II TtTibltt 2 X Ttriable $ X

8、 IiJlftDl* * % ftriabit 9I Ttrltmt »X t tri ahi r 7 £ TirlattL* & X IfiTlabls 爭tSTO. 43t3?7 1 腳兒亦 &6 7<2T01« 1». 60S73：04. 1137571 I05a 7WT 50.07100761 35t. 32321 a-«8LM)M3 Q T0Qfle*Q. 1®73«3S33 J. LSflB-0B OtrtVT&SC 1.M1C43FJ.EFIZ輯日工翎卸1斤 吐 瑪相曲3-0.1T

9、14D8M4 0.-； 0T5M1!0 56«35§硬飯L. 2733LG. 231707U叭溺起c.armif 0們1, 366681 G007LU- W " F "_t-o. 44駐屮.血遜 -Q. 383Mk.0?25fiS2731.廊$蝕 miB.SQ 22JLWT iOMB. »« LS.2S1ZLU0 1OJ. 2C42 L 3. 26132 100. 20<2 專轉(zhuǎn) 971 76 5恂 il -寶4. 672 5IE 苗帥 JT.稲氐帀撫.3 1 燈陰陽-37. !b4bS 17. 6B86 叫川迪緲竹上-LK；5

10、5 OTTLJh Hj. iLMt 7 CL駅巾陽-U. 42232也盤袖缺 叫韜939M珂a EM613 f W9帶為亦眈 勺飛更別1】陰 -0 俯由1 47TO1 -C.m 51 -l&. VrmilS 1Z 07234 -18.OT15 12, OT£M FMito撲扛f 0, 4S"2B -CL S2t510.<5S；61 111213r151720a aevr?吒專 衛(wèi)亙計(jì)25 第一張表是“回歸統(tǒng)計(jì)表”（K12: L17）:其中：Multiple R:（復(fù)相關(guān)系數(shù)R） R2的平方根，又稱相關(guān)系數(shù)，用來衡量自變量x與y之間的相關(guān)程度的大小。本例R=0.

11、9134表明它們之間的關(guān)系為高度正相關(guān)。（Multiple :復(fù)合、多種）R Square：復(fù)測定系數(shù)，上述復(fù)相關(guān)系數(shù)R的平方。用來說明自變量解釋因變量y變差的程度，以測定因變量y的擬合效果。此案例中的復(fù)測定系數(shù)為0.8343，表明用用自變量可解釋因變量變差的83.43%Adjusted R Square：調(diào)整后的復(fù)測定系數(shù)R2,該值為0.6852，說明自變量能說明因變量y的68.52%，因變量y的31.48%要由其他因素來解釋。（Adjusted :調(diào)整后的）標(biāo)準(zhǔn)誤差：用來衡量擬合程度的大小，也用于計(jì)算與回歸相關(guān)的其它統(tǒng)計(jì)量，此值越小，說 明擬合程度越好觀察值：用于估計(jì)回歸方程的數(shù)據(jù)的觀察值

12、個(gè)數(shù)。第二張表是“方差分析表”：主要作用是通過 F檢驗(yàn)來判定回歸模型的回歸效果。該案例中的Significanee F（ F顯著性統(tǒng)計(jì)量）的 P值為0.00636，小于顯著性水平 0.05，所以 說該回歸方程回歸效果顯著，方程中至少有一個(gè)回歸系數(shù)顯著不為0. （ Significanee :顯著）第三張表是“回歸參數(shù)表”：K26: K35為常數(shù)項(xiàng)和blb9的排序默認(rèn)標(biāo)示。L26: L35為常數(shù)項(xiàng)和blb9的值，據(jù)此可得出估算的回歸方程為：該表中重要的是 O列,該列的026: O35中的P-value為回歸系數(shù)t統(tǒng)計(jì)量的P值。值得注意的是：其中bl、b7的t統(tǒng)計(jì)量的P值為0.0156和0.017

13、5，遠(yuǎn)小于顯著性水平 0.05 , 因此該兩項(xiàng)的自變量與 y相關(guān)。而其他各項(xiàng)的t統(tǒng)計(jì)量的P值遠(yuǎn)大于bl、b7的t統(tǒng)計(jì)量的P 值，但如此大的P值說明這些項(xiàng)的自變量與因變量不存在相關(guān)性，因此這些項(xiàng)的回歸系數(shù)不顯著?；貧w分析是一種應(yīng)用很廣的數(shù)量分析方法，用于分析事物間的統(tǒng)計(jì)關(guān)系，側(cè)重?cái)?shù)量關(guān)系變化。回歸分析在數(shù)據(jù)分析中占有比較重要的位置。用來揭示被解釋變量與另一個(gè)解用來揭示被解釋變量與多個(gè)解釋一元線性回歸模型：指只有一個(gè)解釋變量的線性回歸模型, 釋變量的線性關(guān)系。多元線性回歸模型：指含有多個(gè)揭示變量的線性回歸模型, 變量的線性關(guān)系。此篇文章主要講述多元線性回歸分析。方法/步驟 線性回歸分析的內(nèi)容比較多

14、，比如回歸方程的擬合優(yōu)度檢驗(yàn)、 回歸方程的顯著性檢驗(yàn)、回歸 系數(shù)的顯著性檢驗(yàn)、殘差分析、變量的篩選問題、變量的多重共線性問題。操作見圖?；貧w分析通常需要多次試驗(yàn)操作才可以得出較好的模型。 “方法”中選擇“進(jìn)入”， 表示所有的自變量都進(jìn)入模型， 目前還沒有考慮到變量的多重共線問題， 要先觀察初步的結(jié) 果分析，才會(huì)考慮發(fā)哦變量的多重共線問題。XIinwgST7strs9±Jtw,13F12V9UTillMIf山 ffi閾It1TMtIS««19;廣雅20亠 iffA27asinXHM稱-F出種虧AOXi訂Nt打5 jrwso?nomsai»e7S琢TDttO

15、価DvareJlTOMDO5»«I? a誹B400pa打FwTrnFWUuX. k 刀匚扎忙1. 3通過觀察調(diào)整后的判定系數(shù)0.924，擬合優(yōu)度較高，不被解釋的變量較少。由回歸方程顯著性檢驗(yàn)的概率為0,小于顯著性水平 0.05，則認(rèn)為系數(shù)不同時(shí)為 0,被解釋變量與解釋變量全體的線性關(guān)系是顯著的，可建立線性方程。由系數(shù)表知，觀察回歸系數(shù)顯著性檢驗(yàn)中的概率值，如果顯著性水平為0.05，除去“投入人年數(shù)”夕卜，其他變量均大于顯著性水平，這些變量保留在方程中是不正確的。所以該模型不可用，應(yīng)重新建模。RR方i轄艮方*rl估計(jì)町詣1.9剜.939£24231 5255a KB

16、r甲右和FSig1回片總計(jì)1790312 071 JS649? 1312107B810.0Q5J43022386 4S05160404761 532oooJ預(yù)測蜜暫第列人坯宴槩投入皐訊事寶理口荒陀艾數(shù).嵯苦敏.抄入人隼歡,魚妒1$igB試甲能11(Wft)-35 31376 $80*401E49投入人年魏.ege20S1.361335?003投入墾蹄的人年諛4陽626*.464了舁463推入翱胡事業(yè)費(fèi)(S5E).003.002.237kGOl132.屈.377.014.05U彌*064053*.252-1.198343713503J19；M17：x ai毬蘭成牧2. 4重新建模操作見圖片，采用

17、的是“向后篩選”方法，依次剔除的變量是專著數(shù)、投入高級(jí)職 稱的人年數(shù)、投入科研事業(yè)費(fèi)、獲獎(jiǎng)數(shù)、論文數(shù)。最后的模型結(jié)果是“立項(xiàng)課題數(shù)=-94.524+0.492x 投入人年數(shù)”。Jk_rm am _ MHLin: *t叩1曠怦：.4?WJIIM.”«9WDIN*#Q13PBstow擺型匯總g模型RR方調(diào)整R方標(biāo)準(zhǔn)璽十的誤1.96孑.939.924231.52552.969b.939.927226.86443968c.937.927226.58204.965d.931.923232.08335.963e.927.921234.86946.953.919.917241.9582乩預(yù)測變星:

18、俸星）亦獎(jiǎng)數(shù)匚投入科斜事業(yè)費(fèi)（百元）怪 譏鈕電®機(jī)論 文數(shù)'投入人年數(shù)，按入高級(jí)歸和的人年線。C.預(yù)測變量侈砂 荻獎(jiǎng)紋投入科研爭業(yè)賢（百元），論 文臥投入人毎數(shù)中d預(yù)測變量:（常戦荻獎(jiǎng)數(shù)，論文數(shù)申入人年數(shù)。G預(yù)測變屋:富量）,論文數(shù).投入人年數(shù)。f預(yù)測寳量:厲動(dòng)投入人年數(shù)9g因?qū)D量:課題總數(shù): 'k HjM5t0*/ T 年試宙遠(yuǎn)1-35 31 37A.M0-4C1.£9S.20 &1.S613.362,003-.467.526364-J47.463丹人巧碉爭丄:常I門丄I.003002,2371 601.122.022.377.059S53J5 3

19、”.252-1.198J2.503dl1.416.1702-36 24673 442-J9426橙扎人年餐.6921Z51.34-83.932OO1-.44349?439- 967檯人科研事業(yè)裁（首朮）.003DQ2J401.77S488倫文教血4.052-.2531 230230.701,453.1171.5483-29 791730+7-J08.687堆人人卑舷.553.1021.0795J11.DOfr投人科研事業(yè)桃（臣JD0011521 525-臉.345-.343-1 加 34347164521201 586.125-4-63.3057U40.帕9楡人人弟刼.6449851 2557

20、.52?論文枚.04 5-.351-0.051,047.5994551.295.2065-67 狛572 109 9J235 J.628.cm1.22JL330,00&倫盂強(qiáng)-.070&4：-.2761666J07retxts?) 94.5247： U2*1.3057202-城人人穿敖027.959ISAM00C3. 5殘差分析：又P-P圖可知，原始數(shù)據(jù)與正態(tài)分布的不存在顯著的差異，殘差滿足線性模型的前提要求。 由庫克距離（0.041小于1）和杠桿指變量的值知，沒有顯著的差異。殘差點(diǎn)在0線周圍隨機(jī)分布。殘差統(tǒng)計(jì)ST極小值彼大值均值標(biāo)準(zhǔn)僞恙N拂W值-57.64232469869&

21、amp;0 000803.721331標(biāo)準(zhǔn)預(yù)測值 1.2662.845.0001 00031卡鹼U值的標(biāo)準(zhǔn)謂羞43.45$1330015$ 49$19.16131i陂的預(yù)測值64.6383240 918956 647799258531-466.2850509.6787.0000237.S91 431標(biāo)準(zhǔn)殘蓬1.9272106.00098?31Student化殘差1.9892.314.0071.02131已曬的殘遵496.6156614.886813530256732631Student化已勝降的殘羞2022.518.0121.06031Mahal-距甬.0008.097.9681.59231Cook的匪卓.000.653.041.10231居中杠桿值.000.270.03231回歸標(biāo)準(zhǔn)化魏芒的標(biāo)準(zhǔn)PF昭因變:S：諫埋總數(shù)0.2Q.406觀漢I的羅枳1«華O.6-O.4期宗的累積槪屮QO0O0O北為 C00 0g ° °O<5O0END經(jīng)驗(yàn)內(nèi)容僅供參考，如果您需解決具體問題（尤其法律、醫(yī)學(xué)等領(lǐng)域），建議您詳細(xì)咨詢相關(guān)領(lǐng)域?qū)I(yè)人士。僅供個(gè)人用于學(xué)習(xí)、研究；不得用于商業(yè)用途。For personal use only in study and research; not fo