excel在相關(guān)與回歸分析中應(yīng)用

《excel在相關(guān)與回歸分析中應(yīng)用》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在學(xué)術(shù)論文-天天文庫。

1、Excel在相關(guān)與回歸分析中應(yīng)用財會金融學(xué)院李潭Excel作為強大的數(shù)據(jù)處理軟件，包含大量的相關(guān)與回歸分析函數(shù)和工具。通過這些函數(shù)和工具我們可以方便快捷地進(jìn)行相關(guān)與回歸分析，降低對統(tǒng)計專業(yè)知識的要求，提高應(yīng)用效率。應(yīng)用好Excel軟件，對于利用統(tǒng)計專業(yè)知識解決實際問題，具有事半功倍的效果。本文以Excel2007為例來介紹Excel在相關(guān)與回歸分析中的應(yīng)用。一、相關(guān)與回歸分析中常用Excel函數(shù)或功能模塊在相關(guān)與回歸分析中所用函數(shù)和功能模塊如表1所示。表1相關(guān)與回歸分析常用Excel函數(shù)列表函數(shù)或功能模塊名稱函數(shù)說明語法形式或操作步驟CORREL返回單元格區(qū)域數(shù)列1和數(shù)列2之間的相關(guān)

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ū)域。Known_x's??為自變量數(shù)據(jù)點集合區(qū)域LINEST可通過使用

3、最小二乘法計算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，返回描述此直線的截距和斜率LINEST(known_y's,[known_x's],[const],[stats])[]內(nèi)為可選項，返回回歸直線的截距和斜率（利用數(shù)組計算）STEYX返回通過線性回歸法計算每個x的y預(yù)測值時所產(chǎn)生的標(biāo)準(zhǔn)誤差STEYX(known_y's,known_x's)Known_y's??為因變量數(shù)據(jù)點數(shù)組或區(qū)域Known_x's??為自變量數(shù)據(jù)點數(shù)組或區(qū)域TREND返回一條線性回歸擬合線的預(yù)測值TREND(known_y's,known_x's,new_x's,const)Known_y's??為因變的觀察值或數(shù)據(jù)集合K

4、nown_x's??為自變的觀察值或數(shù)據(jù)集合New_x's??為用于預(yù)測y值的新x值相關(guān)系數(shù)工具返回兩列數(shù)據(jù)的相關(guān)系數(shù)矩陣操作步驟：數(shù)據(jù)→數(shù)據(jù)分析→相關(guān)系數(shù)回歸工具返回相關(guān)與回歸分析的各項參數(shù)及統(tǒng)計檢驗結(jié)果操作步驟：數(shù)據(jù)→數(shù)據(jù)分析→回歸作圖法利用散點圖，采用作圖法求解回歸方程操作步驟：插入→圖表→散點圖→（選中點，右擊）添加趨勢線→趨勢線選項→顯示公式和R平方值二、Excel在相關(guān)與回歸分析中的應(yīng)用示例（一）相關(guān)表的編制相關(guān)表是將自變量x按照從小到大的順序排列，所對應(yīng)的y依次對應(yīng)排列而形成的表格。通過相關(guān)表可以判斷兩個變量間是否具有相關(guān)關(guān)系以及相關(guān)的方向。根據(jù)自變量是否分組，相關(guān)表分

5、成簡單相關(guān)表、單變量分組相關(guān)表和雙變量分組相關(guān)表。下面以表2中20家同類工業(yè)企業(yè)產(chǎn)量與單位成本資料為例說明相關(guān)表的編制。表22007年某地區(qū)20家同類工業(yè)企業(yè)產(chǎn)量與單位成本統(tǒng)計表序號產(chǎn)量（噸）單位成本（元/噸）序號產(chǎn)量（噸）單位成本（元/噸）15001000117009002800890128008803600950131000720470096014600900590074015800850610007001660092075009801770092088009001880087097009101980085010600940209008001．簡單相關(guān)表的編制新建Excel工作簿

6、文件，文件名為“相關(guān)與回歸分析”，將表2中數(shù)據(jù)復(fù)制粘貼到相關(guān)與回歸分析工作簿中表sheet1中，如圖1所示。圖1產(chǎn)量與單位成本原始數(shù)據(jù)將光標(biāo)移至B列任意數(shù)值上，在“開始”選項卡下，單擊“編輯”組的“排序和篩選”按鈕，選擇升序，即可得到簡單相關(guān)表，如圖2所示。通過“數(shù)據(jù)”選項卡中“排序和篩選”組的相關(guān)按鈕，同樣可以實現(xiàn)數(shù)據(jù)的排序。根據(jù)簡單相關(guān)表即可對兩變量間相關(guān)方向加以判斷。圖2產(chǎn)量與單位成本簡單相關(guān)表1．單變量分組相關(guān)表的編制對圖1中sheet1數(shù)據(jù)，利用數(shù)據(jù)透視表，對自變量進(jìn)行分組，編制單變量分組相關(guān)表。具體操作步驟如下：（1）將光標(biāo)移至數(shù)據(jù)區(qū)域任意單元格中（這樣做是為了讓Exce

7、l自動選中連續(xù)的數(shù)據(jù)區(qū)域），在“插入”選項卡下“表”組中，單擊“數(shù)據(jù)透視表”，此時會彈出創(chuàng)建數(shù)據(jù)透視表對話框，點擊現(xiàn)有工作表，再點擊數(shù)據(jù)區(qū)域外的任意單元格，如F2，即可得到如圖3所示內(nèi)容。圖3創(chuàng)建數(shù)據(jù)透視表（2）點確定后，出現(xiàn)數(shù)據(jù)透視表字段列表對話框，將產(chǎn)量放入行標(biāo)簽區(qū)域，再將產(chǎn)量和單位成本均放入數(shù)值區(qū)域，然后對數(shù)值區(qū)域中“求和項產(chǎn)量”進(jìn)行操作：單擊下拉按鈕→值字段設(shè)置→計算類型改為計數(shù)；同樣將“求和項單位成本”計算類型改為平均值；最后即可得到單變量分組相