實驗三EXCEL在相關與回歸分析中的應用

1、實驗三 EXCEL在相關與回歸分析中的應用一、相關圖的繪制將本章表71中的資料建立EXCEL工作表，如圖1所示。圖1 表71的EXCEL工作表制作相關圖的步驟如下：選擇區(qū)域A1:B11，如圖1所示；點擊EXCEL圖表向導； 在“圖表類型”中選擇“XY散點圖”；如圖2。圖2 散點圖的制作在“子圖表類型”中選擇第一種散點圖，并點擊“下一步”，即可得到圖1和2。圖2 散點圖的制作圖3 散點圖的制作點擊“完成”，并對圖形進行修飾編輯，最后得到如圖4所示廣告投入與月平均銷售額之間的散點圖。圖4 廣告投入與月平均銷售額的散點圖二、相關系數(shù) 在EXCEL中，相關系數(shù)函數(shù)和相關系數(shù)宏提供了兩種計算相關系數(shù)的方

2、法。（一）相關系數(shù)函數(shù) 在EXCEL中，CORREL函數(shù)和PERSON函數(shù)提供了計算兩個變量之間的相關系數(shù)的方法，這兩個函數(shù)是等價的。與相關系數(shù)有關的函數(shù)還有RSQ（相關系數(shù)的平方，即判定系數(shù)r2）和COVAR（協(xié)方差函數(shù)）。在這里我們以CORREL函數(shù)和表71中資料為例，介紹利用函數(shù)計算相關系數(shù)的方法。首先，點擊EXCEL函數(shù)圖鈕“fx”，選擇“統(tǒng)計”函數(shù)；在統(tǒng)計函數(shù)點擊“CORREL”，進入函數(shù)向導；在“array1”中輸入第一個變量“廣告投入”的數(shù)據(jù)區(qū)域A2:A11“array2”中輸入第二個變量“月均銷售額”的數(shù)據(jù)區(qū)域B2:B11樣，即可在當前光標所在單元格顯示函數(shù)的計算結果。如圖5

3、所示。圖5 CORREL函數(shù)計算相關系數(shù)（二）相關系數(shù)宏在EXCEL數(shù)據(jù)分析宏中，EXCEL專門提供了計算相關系數(shù)宏過程。利用此宏過程，可以計算多個變量之間的相關矩陣。仍然以表71中資料為例，利用相關系數(shù)宏計算相關系數(shù)矩陣的過程如下：點擊EXCEL“工具”菜單，選擇“數(shù)據(jù)分析”過程；在“數(shù)據(jù)分析”宏過程中，選擇“相關系數(shù)”過程。如圖6所示； 在“輸入?yún)^(qū)域”中輸入兩個變量所在區(qū)域A2:B11，數(shù)據(jù)以列排列，輸出區(qū)域選擇在同一工作表中的以D1:E5區(qū)域里。計算結果如圖7所示。圖6 相關系數(shù)宏圖7 利用相關系數(shù)宏計算的相關系數(shù)矩陣三、回歸分析宏除了回歸分析宏外，EXCEL雖然提供了9個函數(shù)用于建立回

4、歸模型和回歸預測。這9個函數(shù)列于表79中。但EXCEL提供的回歸分析宏仍然具有更方便的特點。仍以表71中廣告投入與銷售額的關系資料為例，利用一元線性回歸方程確定兩個變量之間的定量關系。表79 用于回歸分析的工作表函數(shù)函數(shù)名定 義INTERCEPT一元線性回歸模型截距的估計值SLOPE一元線性回歸模型斜率的估計值RSQ一元線性回歸模型的判定系數(shù)（r2）FORECAST依照一元線性回歸模型的預測值STEYX依照一元線性回歸模型的預測值的標準誤差TREND依照多元線性回歸模型的預測值GROWTH依照多元指數(shù)回歸模型的預測值LINEST估計多元線性回歸模型的未知參數(shù)LOGEST估計多元指數(shù)回歸模型的未知參數(shù)回歸宏確定兩個變量之間定量關系的過程如下：圖8 回歸分析宏過程在“工具欄”菜單“數(shù)據(jù)分析”過程中選擇“回歸”宏過程；在“Y值輸入?yún)^(qū)域”內輸入B2:B11，在“X值輸入?yún)^(qū)域”輸入A2：A11，如果是多元線性回歸，則X值的輸入?yún)^(qū)就是除Y變量以外的全部解釋變量；選擇“標志”；置信度水平為95，輸出結果選擇在一張新