第八章_回歸分析

1、第八章_回歸分析 8.1 回歸分析基礎(chǔ) 8.2 用Excel進(jìn)行回歸分析處理 8.3 上機(jī)實(shí)驗(yàn)八 用Excel進(jìn)行回歸分析 回歸和相關(guān)已成為統(tǒng)計(jì)學(xué)中最基本的概念之一，其分析方法已是最標(biāo)準(zhǔn)、最常用的統(tǒng)計(jì)工具之一。從狹義上看，相關(guān)分析的任務(wù)主要是評(píng)判現(xiàn)象之間的相關(guān)程度高低以及相關(guān)的方向，而回歸分析則是在相關(guān)分析的基礎(chǔ)上進(jìn)一步借用數(shù)學(xué)方程將那種顯著存在的相關(guān)關(guān)系表示出來(lái)，從而使這種被揭示出的關(guān)系具體化并可運(yùn)用于實(shí)踐中去。從廣義的角度去理解相關(guān)和回歸，此時(shí)回歸分析就包含著相關(guān)分析。 【實(shí)例描述實(shí)例描述】 1887年生物統(tǒng)計(jì)學(xué)家高爾頓在研究豌豆和人體的身高遺傳規(guī)律時(shí)，首先提出“回歸”的思想 。1888年

2、他又引入“相關(guān)”（Correlation）的概念。原來(lái)，他在研究人類身高的遺傳時(shí)發(fā)現(xiàn)，不管祖先的身高是高還是低，成年后代的身高總有向一般人口的平均身高回歸的傾向。 8.1 回歸分析基礎(chǔ)回歸分析基礎(chǔ) 8.1.1 一元線性回歸 8.1.2 多元線性回歸 8.1.3 逐步回歸 8.1.1 一元線性回歸 回歸分析最簡(jiǎn)單的情形是一個(gè)自變量和一個(gè)因變量，且它們有線性關(guān)系，這叫一元線性回歸，即模型為YABX，這里X是自變量，Y是因變量，是隨機(jī)誤差，通常假定隨機(jī)誤差的均值為0，方差為2（20）,2與X的值無(wú)關(guān)。 A和B為未知待估的總體參數(shù)，又稱其為回歸系數(shù)。 8.1.1 一元線性回歸 如何根據(jù)樣本資料去估計(jì)就

3、成為回歸分析的基本任務(wù)。由此可以假設(shè)樣本的回歸方程如下： bxaY、上式中， 、和分別為Y、A和B的估計(jì)值Y222)()()(xxnyxxynxxyyxxbnxbnyxbya8.1.1 一元線性回歸 例例8-1:為研究某類企業(yè)的生產(chǎn)量和單位成本之間的關(guān)系，現(xiàn)隨機(jī)抽取10個(gè)企業(yè)，得如下數(shù)據(jù)（見表8-1）： 根據(jù)該資料，經(jīng)計(jì)算可得表8-2： 8.1.1 一元線性回歸由上表資料，可得： 2221024225446710336542.25()n xyx ybn xx 58.8546754( 2.25)1010yxabnn 這樣就可以得到生產(chǎn)量(x)和單位成本(y)之間的樣本回歸方程 58.85-2.2

4、5Yabxx8.1.2 多元線性回歸 總體的多元線性回歸方程為 1122kkYAB XB XB X總體回歸方程一般未知，需要通過樣本去估計(jì)。設(shè)估計(jì)方程為 1122kk +b x +b x +b xYa上式中，、b1、b2、bk稱為回歸系數(shù)，其中，bi(i=1,2,，k)又稱為偏回歸系數(shù)，它表示當(dāng)其它自變量均為零時(shí)，xi每變化一個(gè)單位對(duì)因變量影響的數(shù)值。 8.1.2 多元線性回歸 設(shè)樣本為(x1，x2，xk，y)，利用最小二乘法可估計(jì)出回歸方程中的參數(shù)，即要求221122kk()(b x +b x +b x )QyYya=最小值 當(dāng)k=2可解得：22211222211222211112221)(

5、)()()()()()()(xxxxxxxxyyxxxxxxyyxxxxb22211222211112211222112)()()()()()()()(xxxxxxxxyyxxxxxxyyxxxxb121 12212xxyayb xb xbbnnn8.1.2 多元線性回歸 例例8-2：為研究某商品的需求量、價(jià)格、消費(fèi)者收入三者之間的關(guān)系，經(jīng)調(diào)查得如表8-3所示資料： 根據(jù)上表資料，可得如表8-4的回歸計(jì)算表： 8.1.2 多元線性回歸據(jù)表中數(shù)據(jù)，如果設(shè)需求量(y)與收入(x1)及價(jià)格(x2)之間的回歸方程為： 8.1.2 多元線性回歸8.1.3 逐步回歸 逐步回歸分析的主要計(jì)算步驟如下： （1

6、）確定F檢驗(yàn)值 （2）逐步計(jì)算 如果已計(jì)算t步(包含t0), 且回歸方程中已引入l個(gè)變量, 則第t+1步的計(jì)算為: 計(jì)算全部自變量的貢獻(xiàn)V(偏回歸平方和)。 在已引入的自變量中, 檢查是否有需要剔除的不顯著變量。 在已引入的變量中選取具有最小V值的一個(gè)并計(jì)算其F值, 如果FF2,則不需要剔除變量, 這時(shí)則考慮從未引入的變量中選出具有最大V值的一個(gè)并計(jì)算F值, 如果FF1, 則表示該變量顯著, 應(yīng)將其引人回歸方程, 計(jì)算轉(zhuǎn)至。 如果F = F1, 表示已無(wú)變量可選入方程, 則逐步計(jì)算階段結(jié)束, 計(jì)算轉(zhuǎn)人（3） 剔除或引人一個(gè)變量后, 相關(guān)系數(shù)矩陣進(jìn)行消去變換, 第t+1步計(jì)算結(jié)束。其后重復(fù)計(jì)算。

7、 （3）其他計(jì)算, 主要是回歸方程入選變量的系數(shù)、復(fù)相關(guān)系數(shù)及殘差等統(tǒng)計(jì)量的計(jì)算 8.2 用用EXCEL進(jìn)行回歸分析處理進(jìn)行回歸分析處理 8.2.1相關(guān)函數(shù) 8.2.2 Excel回歸分析工具 8.2.1相關(guān)函數(shù) 1線性回歸函數(shù)LINEST 功能 :運(yùn)算結(jié)果返回一線性回歸方程的參數(shù)，即當(dāng)已知一組混合成本為Y因變量序列值、N組Xi有關(guān)自變量因素的數(shù)量序列值時(shí)，函數(shù)返回回歸方程的系數(shù)mi（i=1,2n單位變動(dòng)成本）和常數(shù)b（固定成本或費(fèi)用）。 多元回歸方程模型則為：y=m1x1m2x2mnxnb。 8.2.1相關(guān)函數(shù) 語(yǔ)法：LINEST (known_ys, known_xs, const, st

8、ats)。其中Known_ys 是關(guān)系表達(dá)式 y = mx + b 中已知的 y 值集合；Known_xs是關(guān)系表達(dá)式 y = mx + b 中已知的可選 x 值集合；Const 為一邏輯值，用于指定是否將常量 b 強(qiáng)制設(shè)為 0；Stats為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。 8.2.1相關(guān)函數(shù) 附加回歸統(tǒng)計(jì)值如下： 8.2.1相關(guān)函數(shù) 例例8-3：計(jì)算柯布-道格拉斯生產(chǎn)函數(shù)（Cobb-Douglas ）：lnQ = lnA + a lnL + b lnK，式中Q為產(chǎn)出，L和K分別表示勞動(dòng)和資本投入量，A表示平均生產(chǎn)技術(shù)水平， a和b分別是Q相對(duì)于L和K的彈性。使用下表的統(tǒng)計(jì)數(shù)據(jù)，求線性回

9、歸參數(shù)A, a, b。 8.2.1相關(guān)函數(shù)8.2.1相關(guān)函數(shù) （1）建立“線性回歸”工作表，如圖8-1所示。 圖8-1 “線性回歸”工作表8.2.1相關(guān)函數(shù) （2）在單元格E1中輸入“l(fā)n（Q）”，在單元格F1中輸入“l(fā)n（L）” ，在單元格G1中輸入“l(fā)n（K）”。 （3）選中單元格E2，輸入公式“=LN（B2）”，復(fù)制公式到單元區(qū)域E3：E19；選中單元格F2，輸入公式“=LN（C2）”，復(fù)制公式到單元區(qū)域F3：F19；選中單元格G2，輸入公式“=LN（D2）”，復(fù)制公式到單元區(qū)域G3：G19。 （4）將E2：E19定義為因變量，將F2：G19定義為自變量，選中單元格A20，在單元格中輸入

10、“回歸分析”。 （5）選中單元區(qū)域A21：C25，插入統(tǒng)計(jì)函數(shù)“LINEST”，在函數(shù)參數(shù)Known_ys中輸入“E2：E19”，在參數(shù)Knowm_xs中輸入“F2：G19”，在參數(shù)Const中輸入值“TRUE”，在參數(shù)Stats中輸入值“TRUE”。如圖8-2所示。 8.2.1相關(guān)函數(shù) 圖8-2 函數(shù)LINEST參數(shù)對(duì)話框8.2.1相關(guān)函數(shù) （4）按Ctrl + Shift + Enter將返回值以數(shù)組形式顯示，輸出結(jié)果如圖8-3所示。 圖8-3 回歸分析輸出結(jié)果8.2.1相關(guān)函數(shù) 由回歸計(jì)算得線性回歸參數(shù)LnA=-6.2119,a=2.437547,b=0.8577589，保留四位小數(shù)得柯

11、布-道格拉斯生產(chǎn)函數(shù)lnQ= -16.2119+2.4375lnL+0.8578lnK。8.2.1相關(guān)函數(shù) 2索引取值函數(shù)index 如果只想要返回值中的指定項(xiàng)，則可以用索引取值index函數(shù)。 語(yǔ)法格式：INDEX（單元格區(qū)域或數(shù)組常量，行序號(hào)，列序號(hào)）；功能：使用索引從單元格區(qū)域或數(shù)組中選取值。可用該函數(shù)在LINEST函數(shù)返回系數(shù)序列數(shù)組表中根據(jù)所需數(shù)據(jù)所處的行列位置定位選取。8.2.1相關(guān)函數(shù) 3四舍五入函數(shù)ROUND 語(yǔ)法格式：ROUND（數(shù)字，小數(shù)位數(shù)）；功能：將數(shù)字四舍五入到指定的小數(shù)位數(shù)。由于LINEST函數(shù)的返回值為6位小數(shù)，用此函數(shù)指定保留的小數(shù)位數(shù)。 例如在需要的位置輸入“

12、=ROUND（INDEX（LINEST（Y，X，TRUE，TRUE），1，4），2）”，則表示取linest函數(shù)返回的數(shù)組的第1行第4列的數(shù)據(jù)，并保留2位小數(shù)。 8.2.2 Excel回歸分析工具 例例8-4：某溶液濃度正比對(duì)應(yīng)于色譜儀器中的峰面積，現(xiàn)欲建立不同濃度下對(duì)應(yīng)峰面積的標(biāo)準(zhǔn)曲線以供測(cè)試未知樣品的實(shí)際濃度。已知8組對(duì)應(yīng)數(shù)據(jù)如下所示，建立標(biāo)準(zhǔn)曲線，并且對(duì)此曲線進(jìn)行評(píng)價(jià)，給出分析數(shù)據(jù)。=0.018.2.2 Excel回歸分析工具 （1）建立“溶液濃度”工作表，如圖8-4所示。 圖8-4 “溶液濃度”工作表8.2.2 Excel回歸分析工具 （2）選擇成對(duì)的數(shù)據(jù)列，將它們使用“X、Y散點(diǎn)圖”

13、制成散點(diǎn)圖，如圖8-5所示。 圖8-5 X、Y散點(diǎn)圖8.2.2 Excel回歸分析工具 （3）選中數(shù)據(jù)點(diǎn)，單擊右鍵，選擇“添加趨勢(shì)線”-“線性”，并在選項(xiàng)標(biāo)簽中要求給出公式和相關(guān)系數(shù)等，可以得到擬合的直線。如圖8-68-8所示。 圖8-6 “添加趨勢(shì)線”-“線性”對(duì)話框 圖8-7 “添加趨勢(shì)線”選項(xiàng)8.2.2 Excel回歸分析工具 圖8-8 趨勢(shì)線及其公式由圖中可知，擬合的直線是y=1639.5x-200.89，R2的值為0.9961。 8.2.2 Excel回歸分析工具 為了進(jìn)一步使用更多的指標(biāo)來(lái)描述這一個(gè)模型，可以使用數(shù)據(jù)分析中的“回歸”工具來(lái)詳細(xì)分析這組數(shù)據(jù)。 （1）打開“溶液濃度”工

14、作表，在“工具”菜單中選擇“數(shù)據(jù)分析”命令，選擇“回歸”分析工具。 （2）單擊確定按鈕，打開“回歸”分析工具對(duì)話框。在輸入選項(xiàng)Y值輸入?yún)^(qū)域中輸入$B$1:$B$9，X值輸入?yún)^(qū)域中輸入$A$1:$A$9，選中“標(biāo)志”、“常數(shù)為零”選項(xiàng)；在輸出選項(xiàng)中選擇輸出區(qū)域，本例中不妨選擇輸出區(qū)域?yàn)?D$1；選擇“殘差”、“正態(tài)分布”選項(xiàng)，如圖8-9所示。8.2.2 Excel回歸分析工具 圖8-9 “回歸”分析工具對(duì)話框8.2.2 Excel回歸分析工具 （3）單擊“確定”按鈕?！盎貧w”工具為我們提供了三張圖，分別是殘差圖、線性擬合圖和正態(tài)概率圖,輸出結(jié)果如圖8-10所示。 圖8-10 “回歸”分析工具輸出

15、結(jié)果8.2.2 Excel回歸分析工具 Excel回歸分析工具的輸出結(jié)果包括3個(gè)部分： 1回歸統(tǒng)計(jì)表 回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容： （1）Multiple R：是R2的平方根，又稱為相關(guān)系數(shù)，用來(lái)衡量變量x和y之間相關(guān)程度的大小。上例中R為0.999076，表示二者之間的關(guān)系是高度正相關(guān)的。 （2）R Square：用來(lái)說明自變量解釋因變量變差的程度，以測(cè)定因變量y的擬合效果。 8.2.2 Excel回歸分析工具 （3）Adjusted R Square：僅用于多元回歸才有意義，它用于衡量加入獨(dú)立變量后模型的擬合程度。當(dāng)有新的獨(dú)立變量加入后，即使這一變量同因變量之間不相關(guān)，未經(jīng)修正的R2也要

16、增大，修正的R2僅用于比較含有同一個(gè)因變量的各種模型。 （4）標(biāo)準(zhǔn)誤差：用來(lái)衡量擬合程度的大小，也用于計(jì)算與回歸相關(guān)的其他統(tǒng)計(jì)量，值越小說明擬合程度越好。 （5）觀測(cè)值：用于估計(jì)回歸方程的數(shù)據(jù)的觀測(cè)值個(gè)數(shù)。8.2.2 Excel回歸分析工具 2方差分析表 方差分析表的主要作用是通過F檢驗(yàn)來(lái)判斷回歸模型的回歸效果。 3回歸參數(shù)表 回歸參數(shù)表主要用于回歸方程的描述和回歸參數(shù)的推斷。 如圖8-10所示的回歸參數(shù)表中第16行和第17行分別是截距和諧率的各項(xiàng)指標(biāo)，第二列是具體的值，據(jù)此可以寫出回歸方程。第三列是各個(gè)回歸系數(shù)的P值，最后是置信區(qū)間的上下限。 從計(jì)算結(jié)果中可得到回歸方程：y=1627.15x

17、。 8.3 上機(jī)實(shí)驗(yàn)八 用Excel進(jìn)行回歸分析 一、實(shí)驗(yàn)?zāi)康募耙?1了解回歸分析與相關(guān)分析的不同點(diǎn)，了解回歸分析的基本方法。 2能用Excel相關(guān)函數(shù)求回歸方程。 3會(huì)用Excel回歸分析工具對(duì)數(shù)據(jù)進(jìn)行回歸分析，理解回歸分析的輸出結(jié)果，并能根據(jù)回歸分析工具的輸出結(jié)果正確寫出回歸方程。8.3 上機(jī)實(shí)驗(yàn)八 用Excel進(jìn)行回歸分析 二、實(shí)驗(yàn)內(nèi)容 （一）確定企業(yè)年設(shè)備能力與年勞動(dòng)生產(chǎn)率的關(guān)系 某市電子工業(yè)公司有14個(gè)所屬企業(yè)，各企業(yè)的年設(shè)備能力與年勞動(dòng)生產(chǎn)率統(tǒng)計(jì)數(shù)據(jù)如下表。試分析企業(yè)年設(shè)備能力與年勞動(dòng)生產(chǎn)率的關(guān)系。若該公司計(jì)劃新建一個(gè)設(shè)備能力為9.2千瓦/人的企業(yè)，估計(jì)勞動(dòng)生產(chǎn)率將為多少？8.3 上機(jī)實(shí)驗(yàn)八 用Excel進(jìn)行回歸分析 （二）測(cè)定某礦脈的金屬含量 一礦脈有13個(gè)相鄰樣本點(diǎn)，人為地設(shè)定一個(gè)原點(diǎn)，