Excel進(jìn)行線性回歸分析

1、、相關(guān)函數(shù)EXCEL電子制表系統(tǒng)中函數(shù)的語法分為函數(shù)名和參數(shù)兩部分，參數(shù)用圓括號(hào)括起來，之間以逗號(hào)隔開。參數(shù)可以為單元格區(qū)域、數(shù)組、函數(shù)、常數(shù)（邏輯型、數(shù)值型等）。進(jìn)行回歸分析時(shí)，主要采用線性回歸函數(shù)LINEST ,輔以使用索引取值INDEX與四舍五入ROUND函數(shù)。1、線性回歸函數(shù) LINEST。使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。因?yàn)榇撕瘮?shù) 返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。該函數(shù)的功能為：運(yùn)算結(jié)果返回一線性回歸方程的參數(shù)，即當(dāng)已知一組混合成本為Y因變量序列值、N組Xi有關(guān)自變量因素的數(shù)量序列值時(shí)，函數(shù)返回回歸方程的系數(shù)bi （i=1 ,2n單位變動(dòng)

2、成本）和常數(shù) a （固定成本或費(fèi)用）。多元回歸方程模型則為：y=b1x1 +b2X2 + bnXn + a語法LINEST （known_y's ,known_x's,const,stats）Known_y's是關(guān)系表達(dá)式 y = mx + b中已知的y值集合。 如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變 量。 如果數(shù)組known-y's在單獨(dú)一行中，則 known-x's的每一行被視為一個(gè)獨(dú)立的變 量。Known_x's是關(guān)系表達(dá)式 y = mx + b中已知的可選 x值集合。 數(shù)組k

3、nown_x's 可以包含一組或多組變量。如果只用到一個(gè)變量，只要known_y's和known_x's維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，則 known_y's必須為向量（即必須為一行或一列）。 如果省略known_x's ,則假設(shè)該數(shù)組為 1,2,3,其大小與 known_y's相同。Const 為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為TRUE或省略，b將按正常計(jì)算。如果const為FALSE , b將被設(shè)為 0,并同時(shí)調(diào)整 m值使y = mx。Stats為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。 如果st

4、ats為TRUE,則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值，這時(shí)返回的數(shù)組為mn,mn-1,m1,b;sen,sen-1,se1,seb;r2,sey;F,df;ssreg,ssresid。 如果stats為FALSE或省略，LINEST函數(shù)只返回系數(shù)m和常量b。附加回歸統(tǒng)計(jì)值如下：統(tǒng)計(jì)值說明se1,se2,.,sen 系數(shù) m1,m2,.,mn的標(biāo)準(zhǔn)誤差值sebr2seyFdfssregssresid常量b的標(biāo)準(zhǔn)誤差值(當(dāng) const為FALSE時(shí)，seb = #N/A )判定系數(shù)。Y的估計(jì)值與實(shí)際值之比，范圍在 0到1之間。如果為 1 ,則樣本有很好的相關(guān)性，Y的估計(jì)值與實(shí)際值之間沒有差別。如

5、果判定系數(shù)為0,則回歸公式不能用來預(yù)測Y值。有關(guān)計(jì)算 r2的方法的詳細(xì)信息，請(qǐng)參閱本主題后面的說明Y估計(jì)值的標(biāo)準(zhǔn)誤差。F統(tǒng)計(jì)或F觀察值。使用 F統(tǒng)計(jì)可以判斷因變量和自變量之間是否偶爾發(fā)生 過可觀察到的關(guān)系。自由度。用于在統(tǒng)計(jì)表上查找F臨界值。所查得的值和 LINEST函數(shù)返回的 F統(tǒng)計(jì)值的比值可用來判斷模型的置信度。有關(guān)如何計(jì)算df,請(qǐng)參閱在此主題中后面的 說明示例4說明了 F和df的使用?；貧w平方和。殘差平方和。卜面的圖示顯示了附加回歸統(tǒng)計(jì)值返回的順序o二、示例計(jì)算柯布-道格拉斯生產(chǎn)函數(shù)(Cobb-Douglas )12345678910111213141516171819202122232

6、4251、2、3、InQ = InA + a InL + b InK式中Q為產(chǎn)出，L和K分別表示勞動(dòng)和資本投入量，A表示平均生產(chǎn)技術(shù)水平,b分別是Q相對(duì)于L和K的彈性。使用下表的統(tǒng)計(jì)數(shù)據(jù)，線性回歸參數(shù) A, a, b。ABCD年份產(chǎn)出投入萬人1984733.691531.21985985.11701.419861330.81800.619871603.611852.519881959.421899.419892169.481773.419901947.581716.719912284.781783.319923298.71961.219935498.352156.719947684.36244

7、8.8199595052511.9199611579.152992.3199712462.572804.6199813740.692778.9199915151.462765.7200016780.962740.9200120009.82872.8回歸值：0.857762.437547-16.2120.170320.48303692.663020.979730.1648699#N/A362.48815 #N/A19.70640.4077314#N/A投入億元351.3376.4459.3501.6565.3675.28717.5792.3792.63865.52906.481152.34161

8、0.861773.381875.882066.192255.092690.76定義名稱:將 A2:A19定義為Y,將B2:D19定義為X在 A21 中輸入公式：=LINEST(LN(Y),LN(X),TRUE,TRUE)將返回值以數(shù)組形式顯示：選中 A21:C25，按F2,再按Ctrl + Shift + Enter回歸計(jì)算得 lnY = -16.212 + 2.4375 lnL + 0.8578 lnK三、返回值的其它操作如果只想要返回值中的指定項(xiàng)，則可以index 函數(shù)。1、索引取值函數(shù)INDEX.語法格式：INDEX （單元格區(qū)域或數(shù)組常量，行序號(hào)，列序號(hào)） ； 功能： 使用索引從單元格區(qū)域或數(shù)組中選取值。 可用該函數(shù)在LINEST 函數(shù)返回系數(shù)序列數(shù)組表中根據(jù)所需數(shù)據(jù)所處的行列位置定位選取。2、四舍五入函數(shù) ROUND.語法格式：ROUND （數(shù)字，小數(shù)位數(shù)）；功能：將數(shù)字四舍五入到指定的小數(shù)位數(shù)。由于LINES