excel做回歸分析的原理和實(shí)例

1、Excel做線性回歸分析基本原理及實(shí)例一、原理1、回歸分析原理由一個(gè)或一組非隨機(jī)變量來估計(jì)或預(yù)測某一個(gè)隨機(jī)變量的觀測值時(shí)，所建立的數(shù)學(xué)模型及所進(jìn)行的統(tǒng)計(jì)分析，稱為回歸分析。按變量個(gè)數(shù)的多少，回歸分析有一元回歸分析與多元回歸分析之分，多元回歸分析的原理與一元回歸分析的原理類似。按變量之間關(guān)系的形式，回歸分析可以分為線性回歸分析和非線性回歸分析。2、回歸分析的主要內(nèi)容回歸分析的內(nèi)容包括如何確定因變量與自變量之間的回歸模型；如何根據(jù)樣本觀測數(shù)據(jù)，估計(jì)并檢驗(yàn)回歸模型及未知參數(shù)；在眾多的自變量中，判斷哪些變量對(duì)因變量的影響是顯著的，哪些變量的影響是不顯著的；根據(jù)自變量的已知值或給定值來估計(jì)和預(yù)測因變量的

2、值。3、利用圖表進(jìn)行分析例23-1:某種合成纖維的強(qiáng)度與其拉伸倍數(shù)之間存在一定關(guān)系，圖23-1所示（“線性回歸分析”工作表）是實(shí)測12個(gè)纖維樣品的強(qiáng)度y與相應(yīng)的拉伸倍數(shù)x的數(shù)據(jù)記錄。試求出它們之間的關(guān)系。ABrc11編號(hào)拉伸倍數(shù)強(qiáng)度2-1T.91.43匚221.34.32.11.85r42,52.5652.72.87匚62.72.58r?3.53983.52.710匚944111043.512r114.54.213_124.6最5(1)打開“線性回歸分析”工作表。(2)利用“圖表向?qū)А崩L制XY散點(diǎn)圖”。(3)在XY散點(diǎn)圖”中繪制趨勢回歸直線，如圖23-2所示二、Excel中的回歸分析工作表函數(shù)

3、(1)截距函數(shù)語法：INTERCEPT(known_ys,known_xs)其中：Known_ys為因變的觀察值或數(shù)據(jù)集合，Known_xs為自變的觀察值或數(shù)據(jù)集合。(2)斜率函數(shù)語法：SLOPE(known_ys,known_xs)其中：Known_ys為數(shù)字型因變量數(shù)據(jù)點(diǎn)數(shù)組或單元格區(qū)域；Known_xs為自變量數(shù)據(jù)點(diǎn)集合。(3)測定系數(shù)函數(shù)語法：RSQ(known_ys,known_xs)其中：Known_ys為數(shù)組或數(shù)據(jù)點(diǎn)區(qū)域，Known_xs為數(shù)組或數(shù)據(jù)點(diǎn)區(qū)域。(4)估計(jì)標(biāo)準(zhǔn)誤差函數(shù)語法：STEYX(known_ys,known_xs)其中：Known_ys為因變量數(shù)據(jù)點(diǎn)數(shù)組或區(qū)域，

4、Known_xs為自變量數(shù)據(jù)點(diǎn)數(shù)組或區(qū)域。3利用工作表函數(shù)進(jìn)行回歸分析例23-2:在某大學(xué)一年級(jí)新生體檢表中隨機(jī)抽取10張，得到10名大學(xué)生的身高(x)和體重(y)的數(shù)據(jù)，如圖23-3(“身高體重”工作表)所不。用Excel提供的工作表函數(shù)進(jìn)行相關(guān)計(jì)算。(1)分別計(jì)算“截呵“斜率”“測定系數(shù)”“估計(jì)標(biāo)準(zhǔn)誤差”如圖23-3所示。(2)根據(jù)計(jì)算結(jié)果可以得出直線方程:ABC1蜀1wCera)悻重yCkm)216251317054416652S153476174S3716659815了559170GO1735Tl111685412截距T9.4201513斜率0.804182514測定系數(shù)0.68170

5、1S15估計(jì)標(biāo)準(zhǔn)誤差NE1S0733三、Excel回歸分析工具1、回歸分析工具的主要內(nèi)容回歸分析工具是通過對(duì)一組觀察值使用“最小平方法”進(jìn)行直線擬合，以分析一個(gè)或多個(gè)自變量對(duì)單個(gè)因變量的影響方向與影響程度的方法。 它是Excel中數(shù)據(jù)分析工具的一個(gè)內(nèi)容。在“回歸”對(duì)話框中主要包括也許內(nèi)容：Y值輸入?yún)^(qū)域、X值輸入?yún)^(qū)域、標(biāo)志、置信度、常數(shù)為零、輸出區(qū)域、新工作表組、新工作簿、殘差、標(biāo)準(zhǔn)殘差、殘差圖、線形擬合圖、正態(tài)概率圖。2回歸分析工具的應(yīng)用例23-3:以例23-2資料為例，利用回歸分析工具進(jìn)行回歸分析。(1)打開“身高體重”工作表。(2)在“數(shù)據(jù)分析”中的“分析工具”列表中選擇“H順， 進(jìn)行相關(guān)

6、設(shè)置， 輸出結(jié)果，如圖23-4所示。D二EFGHIJKL1SIMARYOUTPUT23回歸統(tǒng)什4 Multiple0.8256525RSquareQ.681702Adjusted0.6419157標(biāo)準(zhǔn)誤差2.S13074S觀測值10910方差分析11dfSS13F;nificansF12回歸分析1136.0677136,0677 17,13366 Q,00325713殘差E63.532327.9415414總計(jì).9199.6電16Coefficien,標(biāo)準(zhǔn)誤差tStatP-value Lower95%Upper95蝦限95.值上限甌0%17 Intercept-714202.32,53478-

7、2.44108.0.040496,-154.445,-4,39481-154,445,-4,39481.18身島工lew)。 .網(wǎng)乳鄧QJ942即4.139283 0,003257 0.356171 L252194 0.3561711.2521943回歸分析工具的輸出解釋Excel回歸分析工具的輸出結(jié)果包括3個(gè)部分:(1)回歸統(tǒng)計(jì)表回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容：MultipleR(復(fù)相關(guān)系數(shù)R):是R2的平方根， 又稱為相關(guān)系數(shù)， 用來衡量變量x和y之間相關(guān)程度的大小。本例中R為0.825652,表示二者之間的關(guān)系是高度正相關(guān)。RSquare(復(fù)測定系數(shù)R2):用來說明自變量解釋因變量變差的程

8、度，以測定因變量y的擬合效果。AdjustedRSquare（調(diào)整復(fù)測定系數(shù)R2）:僅用于多元回歸才有意義，它用于衡量加入獨(dú)立變量后模型的擬合程度。當(dāng)有新的獨(dú)立變量加入后，即使這一變量同因變量之間不相關(guān)，未經(jīng)修正的R2也要增大，修正的R2僅用于比較含有同一個(gè)因變量的各種模型。標(biāo)準(zhǔn)誤差：用來衡量擬合程度的大小，也用于計(jì)算與回歸相關(guān)的其他統(tǒng)計(jì)量，此值越小，說明擬合程度越好。觀測值：用于估計(jì)回歸方程的數(shù)據(jù)的觀測值個(gè)數(shù)。（2）方差分析表方差分析表的主要作用是通過F檢驗(yàn)來判斷回歸模型的回歸效果。表中“回歸分析”行計(jì)算的是估計(jì)值同均值之差（-）的各項(xiàng)指標(biāo)；“殘差”行是用于計(jì)算每個(gè)樣本觀察值與估計(jì)值之差（-

9、）的各項(xiàng)指標(biāo)；“總計(jì)”行用于計(jì)算每個(gè)值同均值之差（-）的各項(xiàng)指標(biāo)。第二列df是自由度，第三列SS是離差的平方和，第四列MS是均方差，它是離差平方和除以自由度，第五列是F統(tǒng)計(jì)量，第六列SignificanceF是在顯著性水平下的F%的臨界值3.回歸參數(shù)表回歸參數(shù)表主要用于回歸方程的描述和回歸參數(shù)的推斷。如圖23-4所示的表中最下面三行中的第二行和第三行分別是（30（截距）（31（斜率）的各項(xiàng)指標(biāo)。對(duì)于大多數(shù)回歸分析來講，關(guān)注B1要比（30重要。第二列是B0和B1的值，據(jù)此可以寫出回歸方程。第三列是各個(gè)回歸系數(shù)的P值（雙側(cè)），最后是給出（30和（31）的95%的置信區(qū)間的上下限例23-4:有一個(gè)工

10、廠會(huì)計(jì)部門在估計(jì)每月管理費(fèi)y時(shí)，用工人的勞動(dòng)日數(shù)x1與機(jī)器的開工臺(tái)數(shù)x2作自變量，現(xiàn)將當(dāng)年10個(gè)月的數(shù)據(jù)搜集起來，如圖23-5(“多元回歸分析”工作表)所示，估計(jì)y對(duì)x1與x2的線性回歸方程(=0.05)ACD1月份9日的機(jī)器開工臺(tái)數(shù)七 管理費(fèi)用y214516293242142443441527J54451325&5431326764614281874416309g4516231094415281110431527J(1)打開“回歸”對(duì)話框。(2)在Y值輸入?yún)^(qū)域”中輸入D1:D11；在義值輸入?yún)^(qū)域”中輸入B1:C11選擇“標(biāo)志”置信度默認(rèn)；在“輸出選項(xiàng)”中選擇“輸出區(qū)域”在其右邊輸入“

11、A12；單擊“確定”按鈕輸出結(jié)果，如圖23-6所示。12ABCDaFGHSJJH1URYOUTPL-13H.回歸經(jīng)什15 MtipleKdEE377HREquue723323217AJustedRSr.F.FU7S7標(biāo)淮誤差LD70E3S819睨加值1020方均葉斤df5能F；ni土士二心二亡?23回歸介祈221.5T51273310.T3S064-9.4114710.0L037LT&u第&改后1.14b26T匕如 息十g27Co&fficent行凌注羞t.Stat上限95.05(PilueLcsr晶Utper3居2E29I:trrrrpl13.8196313.3卷履93；03+530.