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

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

2、間的相關(guān)系數(shù)CORREL(array1,array2)Array1   第一組數(shù)值單元格區(qū)域Array2   第二組數(shù)值單元格區(qū)域INTERCEPT利用現(xiàn)有的 x 值與 y 值計(jì)算直線與 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ù)點(diǎn)擬合的線性回歸直線的斜率SLOPE(kno

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

4、_y's   為因變量數(shù)據(jù)點(diǎn)數(shù)組或區(qū)域Known_x's   為自變量數(shù)據(jù)點(diǎn)數(shù)組或區(qū)域TREND返回一條線性回歸擬合線的預(yù)測(cè)值TREND(known_y's,known_x's,new_x's,const)Known_y's   為因變的觀察值或數(shù)據(jù)集合Known_x's   為自變的觀察值或數(shù)據(jù)集合New_x's   為用于預(yù)測(cè) y 值的新 x 值相關(guān)系數(shù)工具返回兩列數(shù)據(jù)的相關(guān)系數(shù)矩陣操作步驟：數(shù)據(jù)數(shù)據(jù)分析相關(guān)系數(shù)回歸工具返回相關(guān)

5、與回歸分析的各項(xiàng)參數(shù)及統(tǒng)計(jì)檢驗(yàn)結(jié)果操作步驟：數(shù)據(jù)數(shù)據(jù)分析回歸作圖法利用散點(diǎn)圖，采用作圖法求解回歸方程操作步驟：插入圖表散點(diǎn)圖（選中點(diǎn)，右擊）添加趨勢(shì)線趨勢(shì)線選項(xiàng)顯示公式和R平方值二、 Excel在相關(guān)與回歸分析中的應(yīng)用示例（一） 相關(guān)表的編制相關(guān)表是將自變量x按照從小到大的順序排列，所對(duì)應(yīng)的y依次對(duì)應(yīng)排列而形成的表格。通過相關(guān)表可以判斷兩個(gè)變量間是否具有相關(guān)關(guān)系以及相關(guān)的方向。根據(jù)自變量是否分組，相關(guān)表分成簡(jiǎn)單相關(guān)表、單變量分組相關(guān)表和雙變量分組相關(guān)表。下面以表2中20家同類工業(yè)企業(yè)產(chǎn)量與單位成本資料為例說明相關(guān)表的編制。表 2 2007年某地區(qū)20家同類工業(yè)企業(yè)產(chǎn)量與單位成本統(tǒng)計(jì)表序號(hào)產(chǎn)量（

6、噸）單位成本（元/噸）序號(hào)產(chǎn)量（噸）單位成本（元/噸）15001 000117009002800890128008803600950131 00072047009601460090059007401580085061 0007001660092075009801770092088009001880087097009101980085010600940209008001 簡(jiǎn)單相關(guān)表的編制新建Excel工作簿文件，文件名為“相關(guān)與回歸分析”，將表2中數(shù)據(jù)復(fù)制粘貼到相關(guān)與回歸分析工作簿中表sheet1中，如圖1所示。圖 1 產(chǎn)量與單位成本原始數(shù)據(jù)將光標(biāo)移至B列任意數(shù)值上，在“開始”選項(xiàng)卡下，單擊“編

7、輯”組的“排序和篩選”按鈕，選擇升序，即可得到簡(jiǎn)單相關(guān)表，如圖2所示。通過“數(shù)據(jù)”選項(xiàng)卡中“排序和篩選”組的相關(guān)按鈕，同樣可以實(shí)現(xiàn)數(shù)據(jù)的排序。根據(jù)簡(jiǎn)單相關(guān)表即可對(duì)兩變量間相關(guān)方向加以判斷。圖 2 產(chǎn)量與單位成本簡(jiǎn)單相關(guān)表2 單變量分組相關(guān)表的編制對(duì)圖1中sheet1數(shù)據(jù)，利用數(shù)據(jù)透視表，對(duì)自變量進(jìn)行分組，編制單變量分組相關(guān)表。具體操作步驟如下：（1） 將光標(biāo)移至數(shù)據(jù)區(qū)域任意單元格中（這樣做是為了讓Excel自動(dòng)選中連續(xù)的數(shù)據(jù)區(qū)域），在“插入”選項(xiàng)卡下“表”組中，單擊“數(shù)據(jù)透視表”，此時(shí)會(huì)彈出創(chuàng)建數(shù)據(jù)透視表對(duì)話框，點(diǎn)擊現(xiàn)有工作表，再點(diǎn)擊數(shù)據(jù)區(qū)域外的任意單元格，如F2，即可得到如圖3所示內(nèi)容。圖

8、3 創(chuàng)建數(shù)據(jù)透視表（2） 點(diǎn)確定后，出現(xiàn)數(shù)據(jù)透視表字段列表對(duì)話框，將產(chǎn)量放入行標(biāo)簽區(qū)域，再將產(chǎn)量和單位成本均放入數(shù)值區(qū)域，然后對(duì)數(shù)值區(qū)域中“求和項(xiàng)產(chǎn)量”進(jìn)行操作：?jiǎn)螕粝吕粹o值字段設(shè)置計(jì)算類型改為計(jì)數(shù)；同樣將“求和項(xiàng)單位成本”計(jì)算類型改為平均值；最后即可得到單變量分組相關(guān)表，操作結(jié)果如圖4所示。圖 4 數(shù)據(jù)透視表字段列表及結(jié)果3 雙變量分組相關(guān)表的編制將自變量和因變量均分組，借助于數(shù)據(jù)透視表即可編制雙變量分組相關(guān)表。具體步驟如下：（1） 采用IF函數(shù)對(duì)因變量變位成本進(jìn)行組距式分組。在相關(guān)分析工作簿表sheet1中，D1單元格輸入“按單位成本分組”字段名，D2單元格中輸入公式：=IF(C2<

9、;800,"800以下",IF(C2<900,"800900",IF(C2<1000,"900-1000","1000以上")，即可實(shí)現(xiàn)將單位成本按數(shù)值大小，分成“800以下，800-900，900-1000，1000以上”四組。再利用智能填充按鈕將公式從D2復(fù)制到D21，即可標(biāo)示出20個(gè)數(shù)值所屬的組。（2）利用數(shù)據(jù)透視表，編制雙變量分組相關(guān)表。操作步驟同單變量相關(guān)表的編制，下面重點(diǎn)介紹數(shù)據(jù)透視表字段列表對(duì)話框的設(shè)置。將產(chǎn)量字段放入列標(biāo)簽，將按單位成本分組字段放入行標(biāo)簽區(qū)域和數(shù)值區(qū)域。如圖5所示。圖 5

10、 雙變量分組相關(guān)表字段列表設(shè)置然后右擊數(shù)據(jù)透視表F5至F8任一單元格，選排序降序，再進(jìn)行拖動(dòng)調(diào)整即可得到如圖6所示雙變量分組相關(guān)表。圖 6 雙變量分組相關(guān)表（二） 相關(guān)圖的繪制相關(guān)圖即散點(diǎn)圖，是將自變量和因變量在第一象限描繪出來的點(diǎn)組成的圖形。散點(diǎn)圖的繪制步驟如下：在相關(guān)與回歸分析工作簿sheet1工作表中，選中B1：C21單元格，“插入”選項(xiàng)卡“圖表”組“散點(diǎn)圖”下拉按鈕選第一項(xiàng)（僅帶數(shù)據(jù)標(biāo)記的散點(diǎn)圖）得到原始相關(guān)圖，選中相關(guān)圖，通過“設(shè)計(jì)”和“布局”選項(xiàng)卡，經(jīng)過對(duì)標(biāo)題欄及坐標(biāo)軸欄的美化，即可得到如圖7所示散點(diǎn)圖。圖 7 相關(guān)圖（三） 相關(guān)系數(shù)的計(jì)算1函數(shù)計(jì)算法。運(yùn)用Excel中CORREL

11、函數(shù)可以很方便計(jì)算出所要的結(jié)果。具體操作步驟如下：在相關(guān)與回歸分析工作簿sheet1工作表中，E2單元格輸入公式“=CORREL(B2:B21,C2:C21)”，其中B2:B21表示自變量數(shù)列，C2:C21表示因變量數(shù)列，可分別通過拖動(dòng)鼠標(biāo)選中的形式實(shí)現(xiàn)?；剀嚭蠹纯傻玫絻勺兞块g的相關(guān)系數(shù)。結(jié)果如圖 8所示。圖 8 CORREL函數(shù)相關(guān)系數(shù)2數(shù)據(jù)分析工具法。（1）數(shù)據(jù)分析工具庫(kù)的加載。數(shù)據(jù)分析工具庫(kù)需要加載后才能使用，具體加載步驟如下：?jiǎn)螕糇笊辖莖ffice按鈕Excel選項(xiàng)加載項(xiàng)分析工具庫(kù)轉(zhuǎn)到，即出現(xiàn)“加載宏”對(duì)話框，在分析工具庫(kù)前的方框內(nèi)打“”，單擊“確定”即完成分析工具庫(kù)的加載。此時(shí)，在“

12、數(shù)據(jù)”選項(xiàng)卡“分析”組出現(xiàn)了“數(shù)據(jù)分析”工具。（2） 利用數(shù)據(jù)分析工具計(jì)算相關(guān)系數(shù)。在相關(guān)與回歸分析工作簿sheet1工作表中，單擊“數(shù)據(jù)”選項(xiàng)卡“分析”組“數(shù)據(jù)分析”工具相關(guān)系數(shù)確定，出現(xiàn)“相關(guān)系數(shù)”對(duì)話框，如圖9所示。將輸入?yún)^(qū)域選中B1:C21，標(biāo)志位于第一行打“”，輸出區(qū)域選中E2，確定后即可計(jì)算得到相關(guān)系數(shù)計(jì)算矩陣，如圖10所示。圖 9 分析工具庫(kù)計(jì)算相關(guān)系數(shù)圖 10 分析工具庫(kù)計(jì)算相關(guān)系數(shù)結(jié)果（四） 一元線性回歸分析一元線性回歸分析的關(guān)鍵是確定回歸方程中參數(shù)，的值。在Excel中參數(shù)，的確定，常用的方法有以下三種：1函數(shù)法。確定參數(shù)，的函數(shù)可以有兩組，一組是利用SLOPE函數(shù)和INT

13、ERCEPT函數(shù)分別計(jì)算和的值；另一組是利用LINEST函數(shù)采用數(shù)組計(jì)算形式直接計(jì)算出和的值。現(xiàn)利用相關(guān)與回歸分析工作簿sheet1工作表中的數(shù)據(jù)來介紹兩組函數(shù)的用法。具體公式及計(jì)算結(jié)果如圖11所示。圖 11 函數(shù)計(jì)算回歸參數(shù)方法1中，在H2、H3單元格中分別輸入左邊的公式，即可得到回歸系數(shù)和截距的值。注意公式中，數(shù)值區(qū)域范圍是先輸入因變量的值域，再輸入自變量的值域。方法2中，同時(shí)選中H6和I6單元格，輸入公式“=LINEST(C2:C21,B2:B21)”，然后同時(shí)按Ctrl+Shift+Enter，進(jìn)行數(shù)組運(yùn)算，即可在H6和I6單元格得到回歸系數(shù)和截距的值。2數(shù)據(jù)分析工具法。仍以相關(guān)與回歸分析工作簿sheet1工作表中的數(shù)據(jù)為例，操作步驟如下：?jiǎn)螕簟皵?shù)據(jù)”選項(xiàng)卡“分析”組“數(shù)據(jù)分析”工具回歸確定，出現(xiàn)“回歸”對(duì)話框，如圖12所示。正確輸入Y值和X值區(qū)域，并標(biāo)志和置信度前方框內(nèi)打“”，輸出區(qū)域選中E2，確定后即可計(jì)算得到回歸分析結(jié)果，如圖13所示。圖 12 分析工具庫(kù)進(jìn)行回歸分析圖 13 分析工具庫(kù)進(jìn)行回歸分析通過分析工具庫(kù)中回歸分析，可以