資源描述：
《excel在相關與回歸分析中應用》由會員上傳分享，免費在線閱讀，更多相關內容在行業(yè)資料-天天文庫。
1、Excel在相關與回歸分析中應用財會金融學院李潭Excel作為強大的數(shù)據(jù)處理軟件，包含大量的相關與回歸分析函數(shù)和工具。通過這些函數(shù)和工具我們可以方便快捷地進行相關與回歸分析，降低對統(tǒng)計專業(yè)知識的要求，提高應用效率。應用好Excel軟件，對于利用統(tǒng)計專業(yè)知識解決實際問題，具有事半功倍的效果。本文以Excel2007為例來介紹Excel在相關與回歸分析中的應用。一、相關與回歸分析中常用Excel函數(shù)或功能模塊在相關與回歸分析中所用函數(shù)和功能模塊如表1所示。表1相關與回歸分析常用Excel函數(shù)列表函數(shù)或功能模塊名稱函數(shù)說明語法形式或操作步驟CORREL返回
2、單元格區(qū)域數(shù)列1和數(shù)列2之間的相關系數(shù)CORREL(array1,array2)Array1??第一組數(shù)值單元格區(qū)域Array2??第二組數(shù)值單元格區(qū)域INTERCEPT利用現(xiàn)有的x值與y值計算直線與y軸的截距INTERCEPT(known_y's,known_x's)Known_y's??為因變的觀察值或數(shù)據(jù)集合Known_x's??為自變的觀察值或數(shù)據(jù)集合SLOPE返回根據(jù)known_y's和known_x's中的數(shù)據(jù)點擬合的線性回歸直線的斜率SLOPE(known_y's,known_x's)Known_y's??為因變量數(shù)據(jù)點數(shù)組或單元格區(qū)域
3、。Known_x's??為自變量數(shù)據(jù)點集合區(qū)域LINEST可通過使用最小二乘法計算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，返回描述此直線的截距和斜率LINEST(known_y's,[known_x's],[const],[stats])[]內為可選項，返回回歸直線的截距和斜率（利用數(shù)組計算）STEYX返回通過線性回歸法計算每個x的y預測值時所產生的標準誤差STEYX(known_y's,known_x's)Known_y's??為因變量數(shù)據(jù)點數(shù)組或區(qū)域Known_x's??為自變量數(shù)據(jù)點數(shù)組或區(qū)域TREND返回一條線性回歸擬合線的預測值TREND(known_y
4、's,known_x's,new_x's,const)Known_y's??為因變的觀察值或數(shù)據(jù)集合Known_x's??為自變的觀察值或數(shù)據(jù)集合New_x's??為用于預測y值的新x值相關系數(shù)工具返回兩列數(shù)據(jù)的相關系數(shù)矩陣操作步驟：數(shù)據(jù)→數(shù)據(jù)分析→相關系數(shù)回歸工具返回相關與回歸分析的各項參數(shù)及統(tǒng)計檢驗結果操作步驟：數(shù)據(jù)→數(shù)據(jù)分析→回歸作圖法利用散點圖，采用作圖法求解回歸方程操作步驟：插入→圖表→散點圖→（選中點，右擊）添加趨勢線→趨勢線選項→顯示公式和R平方值二、Excel在相關與回歸分析中的應用示例（一）相關表的編制相關表是將自變量x按照從小到大
5、的順序排列，所對應的y依次對應排列而形成的表格。通過相關表可以判斷兩個變量間是否具有相關關系以及相關的方向。根據(jù)自變量是否分組，相關表分成簡單相關表、單變量分組相關表和雙變量分組相關表。下面以表2中20家同類工業(yè)企業(yè)產量與單位成本資料為例說明相關表的編制。表22007年某地區(qū)20家同類工業(yè)企業(yè)產量與單位成本統(tǒng)計表序號產量（噸）單位成本（元/噸）序號產量（噸）單位成本（元/噸）15001000117009002800890128008803600950131000720470096014600900590074015800850610007001660
6、092075009801770092088009001880087097009101980085010600940209008001．簡單相關表的編制新建Excel工作簿文件，文件名為“相關與回歸分析”，將表2中數(shù)據(jù)復制粘貼到相關與回歸分析工作簿中表sheet1中，如圖1所示。圖1產量與單位成本原始數(shù)據(jù)將光標移至B列任意數(shù)值上，在“開始”選項卡下，單擊“編輯”組的“排序和篩選”按鈕，選擇升序，即可得到簡單相關表，如圖2所示。通過“數(shù)據(jù)”選項卡中“排序和篩選”組的相關按鈕，同樣可以實現(xiàn)數(shù)據(jù)的排序。根據(jù)簡單相關表即可對兩變量間相關方向加以判斷。圖2產量與
7、單位成本簡單相關表1．單變量分組相關表的編制對圖1中sheet1數(shù)據(jù)，利用數(shù)據(jù)透視表，對自變量進行分組，編制單變量分組相關表。具體操作步驟如下：（1）將光標移至數(shù)據(jù)區(qū)域任意單元格中（這樣做是為了讓Excel自動選中連續(xù)的數(shù)據(jù)區(qū)域），在“插入”選項卡下“表”組中，單擊“數(shù)據(jù)透視表”，此時會彈出創(chuàng)建數(shù)據(jù)透視表對話框，點擊現(xiàn)有工作表，再點擊數(shù)據(jù)區(qū)域外的任意單元格，如F2，即可得到如圖3所示內容。圖3創(chuàng)建數(shù)據(jù)透視表（2）點確定后，出現(xiàn)數(shù)據(jù)透視表字段列表對話框，將產量放入行標簽區(qū)域，再將產量和單位成本均放入數(shù)值區(qū)域，然后對數(shù)值區(qū)域中“求和項產量”進行操作：單擊
8、下拉按鈕→值字段設置→計算類型改為計數(shù)；同樣將“求和項單位成本”計算類型改為平均值；最后即可得到單變量分組相
