五相關與回歸分析

1、統(tǒng)計學實驗實習指導書  實驗五 用EXCEL進行相關與回歸分析 實驗目的：用EXCEL進行相關與回歸分析，并了解相關理論實驗步驟：我們用下面的例子進行相關和回歸分析：例1：10個學生身高和體重的情況如下：學生身高（公分）體重（公斤）1234567891017116717715416917516315217216053566449556652475850要求對身高和體重作相關和回歸分析。 一、用EXCEL進行相關分析 首先把有關數(shù)據(jù)輸入EXCEL的單元格中，如圖13-24圖13-24 EXCEL數(shù)據(jù)集用EXCEL進行相關分析有兩種方法，一是利用相關系數(shù)函數(shù)

2、，另一種是利用相關分析宏。1利用函數(shù)計算相關系數(shù)在EXCEL中，提供了兩個計算兩個變量之間相關系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價的，這里我們介紹用CORREL函數(shù)計算相關系數(shù)：第一步：單擊任一個空白單元格，單擊插入菜單，選擇函數(shù)選項，打開粘貼函數(shù)對話框，在函數(shù)分類中選擇統(tǒng)計，在函數(shù)名中選擇CORREL，單擊確定后，出現(xiàn)CORREL對話框。第二步：在array1中輸入B2：B11，在array2中輸入C2：C11，即可在對話框下方顯示出計算結果為0.896。如圖13-25所示：圖13-25CORREL對話框及輸入結果2用相關系數(shù)宏計算相關系數(shù)第一步：單擊工具菜單，選

3、擇數(shù)據(jù)分析選項，在數(shù)據(jù)分析選項中選擇相關系數(shù)，彈出相關系數(shù)對話框，如圖13-26所示：圖13-26相關系數(shù)對話框第二步：在輸入?yún)^(qū)域輸入$B$1:$C$1，分組方式選擇逐列，選擇標志位于第一行，在輸出區(qū)域中輸入$E$1，單擊確定，得輸出結果如圖13-27圖13-27相關分析輸出結果在上面的輸出結果中，身高和體重的自相關系數(shù)均為1，身高和體重的相關系數(shù)為0.896，和用函數(shù)計算的結果完全相同。 二、用EXCEL進行回歸分析 EXCEL進行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函數(shù)用于建立回歸模型和預測。這9個函數(shù)分別是：INTERCEPT 返回線性回歸模型的截距S

4、LOPE 返回線性回歸模型的斜率RSQ 返回線性回歸模型的判定系數(shù)FORECAST 返回一元線性回歸模型的預測值STEYX 計算估計的標準誤TREND 計算線性回歸線的趨勢值GROWTH 返回指數(shù)曲線的趨勢值LINEST 返回線性回歸模型的參數(shù)LOGEST 返回指數(shù)曲線模型的參數(shù)用函數(shù)進行回歸分析比較麻煩，我們這里介紹使用回歸分析宏進行回歸分析。第一步：單擊工具菜單，選擇數(shù)據(jù)分析選項，出現(xiàn)數(shù)據(jù)分析對話框，在分析工具中選擇回歸，如圖13-28 圖13-28數(shù)據(jù)分析對話框    第二步：單擊確定按鈕，彈出回歸對話框，在Y值輸入?yún)^(qū)域輸入$B$2：$B$11，在

5、X值輸入?yún)^(qū)域輸入$C$2：$C$11，在輸出選項選擇新工作表組，如圖13-29所示：圖13-29回歸對話框    第四步：單擊確定按鈕，得回歸分析結果如圖13-30所示圖13-30EXCEL回歸分析結果在上面的輸出結果中，第一部分為匯總統(tǒng)計，MultipleR指復相關系數(shù)，R Square指判定系數(shù)，Adjusted指調整的判定系數(shù)，標準誤差指估計的標準誤，觀測值指樣本容量；第二部分為方差分析，df指自由度，SS指平方和，MS指均方，F(xiàn)指F統(tǒng)計量，Significance of  F指p值；第三部分包括：Intercept指截距，Coefficient指

6、系數(shù)，t stat指t統(tǒng)計量。                      例2： 使用Excel數(shù)據(jù)分析工具進行多元回歸分析使用Excel數(shù)據(jù)分析工具進行多元回歸分析與簡單的回歸估算分析方法基本相同。 給出原始數(shù)據(jù)，自變量的值在A2：I21單元格區(qū)間中，因變量的值在J2：J21中，如下圖所示： 假設回歸估算表達式為： 試使用Excel數(shù)據(jù)分析工具庫

7、中的回歸分析工具對其回歸系數(shù)進行估算并進行回歸分析：點擊“數(shù)據(jù)”工具欄中中的“數(shù)據(jù)分析”工具庫，在彈出的“數(shù)據(jù)分析”-“分析工具”多行文本框中選擇“回歸”，然后點擊 “確定”，如下圖所示： 彈出“回歸”對話框并作如下圖的選擇： 上述選擇的具體方法是：在“Y值輸入?yún)^(qū)域”，點擊右側折疊按鈕，選取函數(shù)Y數(shù)據(jù)所在單元格區(qū)域J2：J21，選完后再單擊折疊按鈕返回；這過程也可以直接在“Y值輸入?yún)^(qū)域”文本框中輸入J2：J21；在“X值輸入?yún)^(qū)域”，點擊右側折疊按鈕，選取自變量數(shù)據(jù)所在單元格區(qū)域A2：I21，選完后再單擊折疊按鈕返回；這過程也可以直接在“X值輸入?yún)^(qū)域”文本框中輸入A2：I21

8、；置信度可選默認的95%。在“輸出區(qū)域”如選“新工作表”，就將統(tǒng)計分析結果輸出到在新表內(nèi)。為了比較對照，我選本表內(nèi)的空白區(qū)域，左上角起始單元格為K10.點擊確定后，輸出結果如下： 第一張表是“回歸統(tǒng)計表”(K12：L17): 其中：Multiple R：（復相關系數(shù)R）R2的平方根，又稱相關系數(shù)，用來衡量自變量x與y之間的相關程度的大小。本例R=0.9134表明它們之間的關系為高度正相關。（Multiple：復合、多種）R Square：復測定系數(shù)，上述復相關系數(shù)R的平方。用來說明自變量解釋因變量y變差的程度，以測定因變量y的擬合效果。此案例中的復測定系數(shù)為0.8343，表

9、明用用自變量可解釋因變量變差的83.43%Adjusted R Square：調整后的復測定系數(shù)R2，該值為0.6852，說明自變量能說明因變量y的68.52%，因變量y的31.48%要由其他因素來解釋。（ Adjusted：調整后的）標準誤差：用來衡量擬合程度的大小，也用于計算與回歸相關的其它統(tǒng)計量，此值越小，說明擬合程度越好觀察值：用于估計回歸方程的數(shù)據(jù)的觀察值個數(shù)。第二張表是“方差分析表”：主要作用是通過F檢驗來判定回歸模型的回歸效果。該案例中的Significance F（F顯著性統(tǒng)計量）的P值為0.00636，小于顯著性水平0.05，所以說該回歸方程回歸效果顯著，方程中至少有一個回歸系數(shù)顯著不為0.（Significance：顯著）第三張表是“回歸參數(shù)表”：K26：K35為常數(shù)項和b1b9的排序默認標示.L26：L35為常數(shù)項和b1b9的值，據(jù)此可得出估算的回歸方程為：該表中重要的是O列,該列的O26：O35中的 P-value為回歸系數(shù)t統(tǒng)計量的P