Excel回歸分析應(yīng)用解讀

1、回歸分析8.18.28.38.48.5線性回歸分析的基本原理圖表分析與回歸函數(shù)分析Excel回歸分析工具多元回歸分析非線性回歸分析本章學(xué)習(xí)目標u 回歸分析的基本思想u 利用Excel圖表進行線性回歸分析u利用Excel回歸分析工作表函數(shù)進行線性回歸分析u利用Excel回歸分析工具進行一元及多元線性回歸分析u 非線性回歸分析的基本思路8.1 線性回歸分析的基本原理8.1.1 回歸分析的概念8.1.2 回歸分析的主要內(nèi)容返回首頁8.1.1 回歸分析的概念首先要區(qū)分兩種主要類型的變量：一種變量相當(dāng)于通常函數(shù)關(guān)系中的自變量，對這樣的變量能夠賦予一個需要的值（如室內(nèi)的溫度、施肥量）或者能夠取到一個可觀測

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

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

4、它們之間的關(guān)系。（1）打開“線性回歸分析”工作表。（2）在工具欄上選擇“圖表向?qū)А卑粹o，單擊打開圖表向?qū)υ捒?，如圖 8-2所示，在“圖表類型”列表框中選擇“XY散點圖”，單擊“下一步”按鈕進入圖表向?qū)Р襟E2。（3）在圖表向?qū)Р襟E2對話框的“數(shù)據(jù)區(qū)域”中輸入“B2:C13”，選擇“系列產(chǎn)生在”為“列”，如圖8-3所示，單擊“下一步”按鈕進入步驟3。（4）在圖表向?qū)Р襟E3的對話框中，打開“圖例”頁面，取消“顯示圖例”，省略標題，如圖8-4所示。（5）單擊“完成”按鈕，得到XY散點圖如圖8-5所示。（6）在散點圖中，把鼠標放在任一數(shù)據(jù)點上，右擊，在快捷菜單中選擇“添加趨勢線”，打開趨勢線對話框。（

5、7）在“添加趨勢線”對話框中打開“類型”頁面，選擇“線性”選項，在“選項”頁面中選擇“顯示公式”和“顯示R平方”選項，單擊“確定”按鈕，得到趨勢回歸圖，如圖8-6所示。圖8-1 “線性回歸分析.xls”工作表圖8-2 圖表向?qū)Вú襟E1）圖8-3 圖表向?qū)Вú襟E2）圖8-4 圖表向?qū)Вú襟E3）圖8-5 XY散點圖圖8-6 趨勢回歸直線返回本節(jié)8.2.2 Excel中的回歸分析工作表函數(shù)Excel提供的回歸分析工作表函數(shù)主要有以下幾個：（1）截距函數(shù)。（2）斜率函數(shù)。（3）測定系數(shù)函數(shù)。（4）估計標準誤差函數(shù)。（1）截距函數(shù)。其功能是利用現(xiàn)有的x值與y值計算直線與y軸的 截 距 。 截 距 為 穿

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

7、語法：SLOPE (known_y's,known_x's)其中：Known_y's為數(shù)字型因變量數(shù)據(jù)點數(shù)組或單元格區(qū)域；Known_x's為自變量數(shù)據(jù)點集合。（3）測定系數(shù)函數(shù)。（3）測定系數(shù)函數(shù)。該函數(shù)返回根據(jù)known_y's和known_x's中數(shù)據(jù)點計算得出的乘積矩相關(guān)系數(shù)的平方。R平方值可以解釋為y方差與x方差的比例。語法：RSQ(known_y's,known_x's)回歸直線的斜率計算公式如下：b =nå xy - (å x)(å y)2nå x - (å x)2圖

8、8-9 計算斜率（4）估計標準誤差函數(shù)。該函數(shù)返回通過線性回歸法計算每個x的y預(yù)測值時所產(chǎn)生的標準誤差。標準誤差用來度量根據(jù)單個x變量計算出的y預(yù)測值的誤差量。語法：STEYX(known_y's,known_x's)其中：Known_y's為因變量數(shù)據(jù)點數(shù)組或區(qū)域，Known_x's為自變量數(shù)據(jù)點數(shù)組或區(qū)域。預(yù)測值y的標準誤差計算公式如下：é 1ú ên222û返回本節(jié)ù éêS yx = êë n(n - 2)û ënå xy - (

9、29; x)(å y) ùúå y - (å y) - n x - ( x)2å åú28.2.3 利用工作表函數(shù)進行回歸分析例8-4 在某大學(xué)一年級新生體檢表中隨機抽取10張，得到10名大學(xué)生的身高（x）和體重（y）的數(shù)據(jù)，如圖8-10（“身高體重”工作表）所示。用Excel提供的工作表函數(shù)進行相關(guān)計算。（1）在單元格A12A15中分別輸入“截距”、“斜率”、“測定系數(shù)”、“估計標準誤差”。（2）在單元格B12中輸入公式“=INTERCEPT(C2:C11,B2:B11)”，回車后顯示-79.42015。（3）在

10、單元格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 回歸分析工具的輸出解釋返回首頁8.3.1 回歸分析工具的主要內(nèi)容回歸分析工具是通過對

11、一組觀察值使用“最小平方法”進行直線擬合，以分析一個或多個自變量對單個因變量的影響方向與影響程度的方法。它是Excel中數(shù)據(jù)分析工具的一個內(nèi)容。在“工具”菜單中選擇“數(shù)據(jù)分析”選項，會出現(xiàn)“數(shù)據(jù)分析”對話框，在分析工具中選擇“回歸”，單擊“確定”按鈕就會進入“回歸”對話框，如圖8-12所示。在此對話框中主要包括以下內(nèi)容：Y值輸入?yún)^(qū)域：X值輸入?yún)^(qū)域：標志：置信度：常數(shù)為零：輸出區(qū)域：新工作表組：新工作簿：殘差：標準殘差：殘差圖：線形擬合圖：正態(tài)概率圖：返回本節(jié)8.3.2 回歸分析工具的應(yīng)用例8-5以例8-4資料為例，利用回歸分析工具進行回歸分析。（1）打開“身高體重”工作表。（2）在“工具”菜單

12、中選擇“數(shù)據(jù)分析”選項，在“分析工具”列表中選擇“回歸”，單擊“確定”按鈕，打開“回歸”對話框。（ 3）在“ Y值輸入?yún)^(qū)域”中輸入“ $C$1: $C$11”，在“X值輸入?yún)^(qū)域”中輸入“$B$1: $B$11”；選擇“標志”，置信度默認；在“輸出選項”中選擇“輸出區(qū)域”，在其右邊輸入“$D$1”，如圖8-13所示，單擊“確定”按鈕輸出結(jié)果，如圖8-14所示。圖8-13 “回歸”對話框圖8-14 回歸分析結(jié)果返回本節(jié)8.3.3 回歸分析工具的輸出解釋Excel回歸分析工具的輸出結(jié)果包括3個部分：1回歸統(tǒng)計表2方差分析表3回歸參數(shù)表回歸統(tǒng)計表包括以下幾部分內(nèi)容：（1）Multiple R（復(fù)相關(guān)系

13、數(shù)R）：（2）R Square（復(fù)測定系數(shù)R2）：（ 3） Adjusted R Square（調(diào)整復(fù)測定系數(shù)R2）：（4）標準誤差：（5）觀測值：返回本節(jié)8.4 多元回歸分析例8-6 有一個工廠會計部門在估計每月管理費y時，用工人的勞動日數(shù)x1與機器的開工臺數(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”；選擇“標志”，置信度默認；在“輸出選項”中選擇“輸出區(qū)域”，在其右邊輸入“A12”，單擊“確定”按鈕輸出結(jié)果，如圖8-16所示。圖8-15 “多元回歸分析”工作表圖8-16 二元線性回歸分析計算結(jié)果返回本節(jié)8.5 非線性回歸分析以最小平方法分析非線性關(guān)系資料在數(shù)量變化上的規(guī)律叫做非線性回歸分析。從非線性回歸的角度看，線性回歸僅是其中的一個特例。一個恰當(dāng)?shù)姆蔷€性回歸方程的確定不是很容易的，一般