Excel中回歸分析的方法

1、第8章 Excel回歸分析步驟分析 8.1 線性回歸分析的基本原理 8.2 圖表分析與回歸函數(shù)分析 8.3 Excel回歸分析工具 8.4 多元回歸分析 8.5 非線性回歸分析本章學(xué)習(xí)目標(biāo)u 回歸分析的基本思想u 利用Excel圖表進(jìn)行線性回歸分析u利用Excel回歸分析工作表函數(shù)進(jìn)行線性回歸分析u利用Excel回歸分析工具進(jìn)行一元及多元線性回歸分析u 非線性回歸分析的基本思路8.1 線性回歸分析的基本原理 8.1.1 回歸分析的概念 8.1.2 回歸分析的主要內(nèi)容返回首頁(yè)8.1.1 回歸分析的概念 首先要區(qū)分兩種主要類型的變量：一種變量相當(dāng)于通常函數(shù)關(guān)系中的自變量，對(duì)這樣的變量能夠賦予一個(gè)需

2、要的值（如室內(nèi)的溫度、施肥量）或者能夠取到一個(gè)可觀測(cè)但不能人為控制的值（如室外的溫度），這樣的變量稱為自變量；自變量的變化能引起另一些變量（如水稻畝產(chǎn)量）的變化，這樣的變量稱為因變量。 由一個(gè)或一組非隨機(jī)變量來(lái)估計(jì)或預(yù)測(cè)某一個(gè)隨機(jī)變量的觀測(cè)值時(shí)，所建立的數(shù)學(xué)模型及所進(jìn)行的統(tǒng)計(jì)分析，稱為回歸分析。因此，回歸分析是研究隨機(jī)變量與非隨機(jī)變量之間的數(shù)量關(guān)系的一種數(shù)學(xué)方法。如果所建立的模型是線性的就稱為線性回歸分析。線性回歸分析不僅告訴我們?cè)鯓咏⒆兞块g的數(shù)學(xué)表達(dá)式，即經(jīng)驗(yàn)公式，而且還利用概率統(tǒng)計(jì)知識(shí)進(jìn)行分析討論，判斷出所建立的經(jīng)驗(yàn)公式的有效性，從而可以進(jìn)行預(yù)測(cè)或估計(jì)。返回本節(jié)8.1.2 回歸分析的主要

3、內(nèi)容 回歸分析的內(nèi)容包括如何確定因變量與自變量之間的回歸模型；如何根據(jù)樣本觀測(cè)數(shù)據(jù)，估計(jì)并檢驗(yàn)回歸模型及未知參數(shù)；在眾多的自變量中，判斷哪些變量對(duì)因變量的影響是顯著的，哪些變量的影響是不顯著的；根據(jù)自變量的已知值或給定值來(lái)估計(jì)和預(yù)測(cè)因變量的值。 Excel提供了許多回歸分析的方法與工具，它們可用于不同的分析目的。返回本節(jié)8.2 圖表分析與回歸函數(shù)分析 8.2.1 利用圖表進(jìn)行分析 8.2.2 Excel中的回歸分析工作表函數(shù) 8.2.3 利用工作表函數(shù)進(jìn)行回歸分析返回首頁(yè)8.2.1 利用圖表進(jìn)行分析 例8-1 某種合成纖維的強(qiáng)度與其拉伸倍數(shù)之間存在一定關(guān)系，圖8-1所示（“線性回歸分析”工作表

4、）是實(shí)測(cè)12個(gè)纖維樣品的強(qiáng)度y與相應(yīng)的拉伸倍數(shù)x的數(shù)據(jù)記錄。試求出它們之間的關(guān)系。 （1）打開(kāi)“線性回歸分析”工作表。 （2）在工具欄上選擇“圖表向?qū)А卑粹o，單擊打開(kāi)圖表向?qū)?duì)話框，如圖8-2所示，在“圖表類型”列表框中選擇“XY散點(diǎn)圖”，單擊“下一步”按鈕進(jìn)入圖表向?qū)Р襟E2。 （3）在圖表向?qū)Р襟E2對(duì)話框的“數(shù)據(jù)區(qū)域”中輸入“B2:C13”，選擇“系列產(chǎn)生在”為“列”，如圖8-3所示，單擊“下一步”按鈕進(jìn)入步驟3。 （4）在圖表向?qū)Р襟E3的對(duì)話框中，打開(kāi)“圖例”頁(yè)面，取消“顯示圖例”，省略標(biāo)題，如圖8-4所示。 （5）單擊“完成”按鈕，得到XY散點(diǎn)圖如圖8-5所示。 （6）在散點(diǎn)圖中，把鼠標(biāo)

5、放在任一數(shù)據(jù)點(diǎn)上，右擊，在快捷菜單中選擇“添加趨勢(shì)線”，打開(kāi)趨勢(shì)線對(duì)話框。 （7）在“添加趨勢(shì)線”對(duì)話框中打開(kāi)“類型”頁(yè)面，選擇“線性”選項(xiàng)，在“選項(xiàng)”頁(yè)面中選擇“顯示公式”和“顯示R平方”選項(xiàng)，單擊“確定”按鈕，得到趨勢(shì)回歸圖，如圖8-6所示。圖8-1 “線性回歸分析.xls”工作表圖8-2 圖表向?qū)Вú襟E1） 圖8-3 圖表向?qū)Вú襟E2）圖8-4 圖表向?qū)Вú襟E3）圖8-5 XY散點(diǎn)圖圖8-6 趨勢(shì)回歸直線返回本節(jié)8.2.2 Excel中的回歸分析工作表函數(shù) Excel提供的回歸分析工作表函數(shù)主要有以下幾個(gè)： （1）截距函數(shù)。 （2）斜率函數(shù)。 （3）測(cè)定系數(shù)函數(shù)。 （4）估計(jì)標(biāo)準(zhǔn)誤差函數(shù)

6、。 （1）截距函數(shù)。 其功能是利用現(xiàn)有的x值與y值計(jì)算直線與y軸的截距。截距為穿過(guò)已知的known_xs和known_ys數(shù)據(jù)點(diǎn)的線性回歸線與y軸的交點(diǎn)。當(dāng)自變量為0時(shí)，使用INTERCEPT函數(shù)可以決定因變量的值。例如，當(dāng)所有的數(shù)據(jù)點(diǎn)都是在室溫或更高的溫度下取得的，可以用INTERCEPT函數(shù)預(yù)測(cè)在0C時(shí)金屬的電阻。 語(yǔ)法：INTERCEPT(known_ys,known_xs)圖8-7 x、y數(shù)據(jù)圖8-8 計(jì)算截距（2）斜率函數(shù)。 該函數(shù)返回根據(jù)known_ys和known_xs中的數(shù)據(jù)點(diǎn)擬合的線性回歸直線的斜率。斜率為直線上任意兩點(diǎn)的垂直距離與水平距離的比值，也就是回歸直線的變化率。 語(yǔ)

7、法：SLOPE (known_ys,known_xs) 其中：Known_ys為數(shù)字型因變量數(shù)據(jù)點(diǎn)數(shù)組或單元格區(qū)域；Known_xs為自變量數(shù)據(jù)點(diǎn)集合。 （3）測(cè)定系數(shù)函數(shù)。 （3）測(cè)定系數(shù)函數(shù)。該函數(shù)返回根據(jù)known_ys和known_xs中數(shù)據(jù)點(diǎn)計(jì)算得出的乘積矩相關(guān)系數(shù)的平方。R平方值可以解釋為y方差與x方差的比例。 語(yǔ)法：RSQ(known_ys,known_xs) 回歸直線的斜率計(jì)算公式如下：22xxnyxxynb圖8-9 計(jì)算斜率（4）估計(jì)標(biāo)準(zhǔn)誤差函數(shù)。 該函數(shù)返回通過(guò)線性回歸法計(jì)算每個(gè)x的y預(yù)測(cè)值時(shí)所產(chǎn)生的標(biāo)準(zhǔn)誤差。標(biāo)準(zhǔn)誤差用來(lái)度量根據(jù)單個(gè)x變量計(jì)算出的y預(yù)測(cè)值的誤差量。 語(yǔ)法：

8、STEYX(known_ys,known_xs)其中：Known_ys為因變量數(shù)據(jù)點(diǎn)數(shù)組或區(qū)域，Known_xs為自變量數(shù)據(jù)點(diǎn)數(shù)組或區(qū)域。 預(yù)測(cè)值y的標(biāo)準(zhǔn)誤差計(jì)算公式如下： 2222221xxnyxxynyynnnSyx返回本節(jié)8.2.3 利用工作表函數(shù)進(jìn)行回歸分析例8-4 在某大學(xué)一年級(jí)新生體檢表中隨機(jī)抽取10張，得到10名大學(xué)生的身高（x）和體重（y）的數(shù)據(jù)，如圖8-10（“身高體重”工作表）所示。用Excel提供的工作表函數(shù)進(jìn)行相關(guān)計(jì)算。（1）在單元格A12A15中分別輸入“截距”、“斜率”、“測(cè)定系數(shù)”、“估計(jì)標(biāo)準(zhǔn)誤差”。（2）在單元格B12中輸入公式“=INTERCEPT(C2:C1

9、1,B2:B11)”，回車后顯示-79.42015。（3）在單元格B13中輸入公式“=SLOPE(C2:C11,B2:B11)”，回車后顯示0.8041825。（4）在單元格B14中輸入公式“=RSQ(C2:C11,B2:B11)”，回車后顯示0.6817018。（5）在單元格B15中輸入公式“=STEYX(C2:C11,B2:B11)”，回車后顯示2.8180738。計(jì)算結(jié)果如圖8-8所示。 圖8-10 “身高體重”工作表 圖8-11 “身高體重”回歸計(jì)算結(jié)果返回本節(jié)8.3 Excel回歸分析工具 8.3.1 回歸分析工具的主要內(nèi)容 8.3.2 回歸分析工具的應(yīng)用 8.3.3 回歸分析工具的

10、輸出解釋返回首頁(yè)8.3.1 回歸分析工具的主要內(nèi)容 回歸分析工具是通過(guò)對(duì)一組觀察值使用“最小平方法”進(jìn)行直線擬合，以分析一個(gè)或多個(gè)自變量對(duì)單個(gè)因變量的影響方向與影響程度的方法。它是Excel中數(shù)據(jù)分析工具的一個(gè)內(nèi)容。 在“工具”菜單中選擇“數(shù)據(jù)分析”選項(xiàng)，會(huì)出現(xiàn)“數(shù)據(jù)分析”對(duì)話框，在分析工具中選擇“回歸”，單擊“確定”按鈕就會(huì)進(jìn)入“回歸”對(duì)話框，如圖8-12所示。在此對(duì)話框中主要包括以下內(nèi)容： Y值輸入?yún)^(qū)域： X值輸入?yún)^(qū)域： 標(biāo)志： 置信度： 常數(shù)為零： 輸出區(qū)域： 新工作表組： 新工作簿： 殘差： 標(biāo)準(zhǔn)殘差： 殘差圖： 線形擬合圖： 正態(tài)概率圖： 返回本節(jié)8.3.2 回歸分析工具的應(yīng)用例8-

11、5 以例8-4資料為例，利用回歸分析工具進(jìn)行回歸分析。（1）打開(kāi)“身高體重”工作表。（2）在“工具”菜單中選擇“數(shù)據(jù)分析”選項(xiàng)，在“分析工具”列表中選擇“回歸”，單擊“確定”按鈕，打開(kāi)“回歸”對(duì)話框。（3）在“Y值輸入?yún)^(qū)域”中輸入“$C$1: $C$11”，在“X值輸入?yún)^(qū)域”中輸入“$B$1: $B$11”；選擇“標(biāo)志”，置信度默認(rèn)；在“輸出選項(xiàng)”中選擇“輸出區(qū)域”，在其右邊輸入“$D$1”，如圖8-13所示，單擊“確定”按鈕輸出結(jié)果，如圖8-14所示。圖8-13 “回歸”對(duì)話框 圖8-14 回歸分析結(jié)果返回本節(jié)8.3.3 回歸分析工具的輸出解釋 Excel回歸分析工具的輸出結(jié)果包括3個(gè)部分：

12、 1回歸統(tǒng)計(jì)表 2方差分析表 3回歸參數(shù)表 回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容： （1）Multiple R（復(fù)相關(guān)系數(shù)R）： （2）R Square（復(fù)測(cè)定系數(shù)R2）： （3）Adjusted R Square（調(diào)整復(fù)測(cè)定系數(shù)R2）： （4）標(biāo)準(zhǔn)誤差： （5）觀測(cè)值： 返回本節(jié)8.4 多元回歸分析 例8-6 有一個(gè)工廠會(huì)計(jì)部門(mén)在估計(jì)每月管理費(fèi)y時(shí)，用工人的勞動(dòng)日數(shù)x1與機(jī)器的開(kāi)工臺(tái)數(shù)x2作自變量，現(xiàn)將當(dāng)年10個(gè)月的數(shù)據(jù)搜集起來(lái)，如圖8-15（“多元回歸分析”工作表）所示，估計(jì)y對(duì)x1與x2的線性回歸方程(=0.05)。返回首頁(yè) （1）在“工具”菜單中選擇“數(shù)據(jù)分析”選項(xiàng)，在“分析工具”列表中選擇“回歸”，單擊“確定”按鈕，打開(kāi)“回歸”對(duì)話框。 （2）在“Y值輸入?yún)^(qū)域”中輸入“D1:D11”，在“X值輸入?yún)^(qū)域”中輸入“B1:C11”；選擇“標(biāo)志”，置信度默認(rèn)；在“輸出選項(xiàng)”中選擇“輸出區(qū)域”，在其右邊輸入“A12”，單擊“確定”按鈕輸出結(jié)果，如圖8-16所示。圖8-15 “多元回歸分析”工作表圖8-16 二元線性回歸分析計(jì)算結(jié)果返回本節(jié)8.5 非線性回歸分析 以最小平方法分析非線性關(guān)系資料在數(shù)量變化上的規(guī)律叫做非線性回歸分析。從非線性回歸的角度看，線性回歸僅是其中的一個(gè)特例。一個(gè)恰當(dāng)?shù)姆蔷€性回歸方程的確定不是很容易的，一般要經(jīng)過(guò)