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)容返回首頁8.1.1 回歸分析的概念 首先要區(qū)分兩種主要類型的變量：一種變量相當(dāng)于通常函數(shù)關(guān)系中的自變量，對這樣的變量能夠賦予一個需

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

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

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

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

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

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

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

10、輸出解釋返回首頁8.3.1 回歸分析工具的主要內(nèi)容 回歸分析工具是通過對一組觀察值使用“最小平方法”進(jìn)行直線擬合，以分析一個或多個自變量對單個因變量的影響方向與影響程度的方法。它是Excel中數(shù)據(jù)分析工具的一個內(nèi)容。 在“工具”菜單中選擇“數(shù)據(jù)分析”選項，會出現(xiàn)“數(shù)據(jù)分析”對話框，在分析工具中選擇“回歸”，單擊“確定”按鈕就會進(jìn)入“回歸”對話框，如圖8-12所示。在此對話框中主要包括以下內(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）打開“身高體重”工作表。（2）在“工具”菜單中選擇“數(shù)據(jù)分析”選項，在“分析工具”列表中選擇“回歸”，單擊“確定”按鈕，打開“回歸”對話框。（3）在“Y值輸入?yún)^(qū)域”中輸入“$C$1: $C$11”，在“X值輸入?yún)^(qū)域”中輸入“$B$1: $B$11”；選擇“標(biāo)志”，置信度默認(rèn)；在“輸出選項”中選擇“輸出區(qū)域”，在其右邊輸入“$D$1”，如圖8-13所示，單擊“確定”按鈕輸出結(jié)果，如圖8-14所示。圖8-13 “回歸”對話框 圖8-14 回歸分析結(jié)果返回本節(jié)8.3.3 回歸分析工具的輸出解釋 Excel回歸分析工具的輸出結(jié)果包括3個部分：

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