實訓五用Excel進行統(tǒng)計相關(guān)回歸分析

1、實訓五用Excel進行統(tǒng)計相關(guān)分析目的與要求：掌握利用相關(guān)系數(shù)對相關(guān)關(guān)系進行測定，并且掌握相關(guān)系數(shù)的性質(zhì)，明確相關(guān)分析與回歸分析各自特點以及它們的區(qū)別與聯(lián)系，建立回歸直線方程，計算估計標準誤差，理解估計標準誤差的意義。案例15：用EXCEL進行相關(guān)與一元線性回歸分析一、用EXCEL繪制散點圖例1近年來國家教育部決定將各高校的后勤社會化。某從事飲食業(yè)的企業(yè)家認為這是一個很好的投資機會，他得到十組高校人數(shù)與周邊飯店的季銷售額的數(shù)據(jù)資料，并想根據(jù)高校的數(shù)據(jù)決策其投資規(guī)模。操作過程：打開Excel工作簿，輸入原始數(shù)據(jù)如圖7-1所示，該表為相關(guān)表。圖7-1從“插入”菜單中選擇“圖表”選項，打開“圖表向?qū)?/p>

2、”對話框如圖7-2所示。在“圖表類型”列表中選擇XY散點圖，單擊“下一步”按鈕。圖7-2在數(shù)據(jù)區(qū)域中輸入B2:C11，選擇“系列產(chǎn)生在列”，如圖7-3所示，單擊“下一步”按鈕。圖7-3打開“圖例”頁面，取消圖例，省略標題，如圖7-4所示。圖7-4單擊“完成”按鈕，便得到XY散點圖如圖7-5所示。圖7-5二、計算相關(guān)系數(shù)用EXCE計算相關(guān)系數(shù)有兩種方法，一是利用相關(guān)系數(shù)函數(shù)，另一種是利用相關(guān)分析宏。例210個學生身高和體重的情況如下：學生身高（公分）體重（公斤）1234567891017116717715416917516315217216053566449556652475850要求對身高和體

3、重作相關(guān)和回歸分析。 操作步驟如下：首先把有關(guān)數(shù)據(jù)輸入EXCEL的單元格中，如圖7-6圖7-6 EXCEL數(shù)據(jù)集在EXCEL中，提供了兩個計算兩個變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價的，這里我們介紹用CORREL函數(shù)計算相關(guān)系數(shù)：第一步：單擊任一個空白單元格，單擊插入菜單，選擇函數(shù)選項，打開粘貼函數(shù)對話框，在函數(shù)分類中選擇統(tǒng)計，在函數(shù)名中選擇CORREL，單擊確定后，出現(xiàn)CORREL對話框。第二步：在array1中輸入B2：B11，在array2中輸入C2：C11，即可在對話框下方顯示出計算結(jié)果為0.896。如圖7-7所示：圖7-7 CORRE

4、L對話框及輸入結(jié)果2.用相關(guān)系數(shù)宏計算相關(guān)系數(shù)第一步：單擊工具菜單，選擇數(shù)據(jù)分析選項，在數(shù)據(jù)分析選項中選擇相關(guān)系數(shù)，彈出相關(guān)系數(shù)對話框，如圖7-8所示：圖7-8 相關(guān)系數(shù)對話框第二步：在輸入?yún)^(qū)域輸入$B$1:$C$1，分組方式選擇逐列，選擇標志位于第一行，在輸出區(qū)域中輸入$E$1，單擊確定，得輸出結(jié)果如圖7-9圖7-9 相關(guān)分析輸出結(jié)果在上面的輸出結(jié)果中，身高和體重的自相關(guān)系數(shù)均為1，身高和體重的相關(guān)系數(shù)為0.896，和用函數(shù)計算的結(jié)果完全相同。 三、用EXCEL進行一元線性回歸分析 EXCEL進行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函數(shù)用于建立回歸模型和預

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

6、輸入$B$2：$B$11，在X值輸入?yún)^(qū)域輸入$C$2：$C$11，在輸出選項選擇新工作表組，如圖7-11所示：圖7-11回歸對話框第四步：單擊確定按鈕，得回歸分析結(jié)果如圖7-12所示圖7-12 EXCEL回歸分析結(jié)果在上面的輸出結(jié)果中，第一部分為回歸統(tǒng)計，該部分給出了回歸分析中的一些常用統(tǒng)計量，Multiple指相關(guān)系數(shù)，R Square指判定系數(shù)，Adjusted指調(diào)整后的判定系數(shù)，標準誤差指估計的標準誤，觀測值指樣本容量；第二部分為方差分析，該部分給出了自由度（df），回歸平方和、殘差平方和、總平方和（SS），回歸和殘差的均方（MS），F(xiàn)檢驗的顯著性水平（Significance F），

7、p值（F），該部分在主要作用是對回歸方程的線性關(guān)系進行顯著性檢驗；第三部分是參數(shù)估計的有關(guān)內(nèi)容，包括回歸方程的截距（Intercept）、斜率（X Variable 1）Coefficient指系數(shù)，用于檢驗的回歸系數(shù)的t統(tǒng)計量（t stat）、P_值（P-value），以及截距和斜率的置信區(qū)間（Lower 95%和Upper95%）。案例16 多元線性回歸分析我們以中國民航客運量預測為例進行多元線性回歸分析。第一步確定因變量。我們以預測目標中國民航客運量，作為因變量。第二步確定自變量。在定性分析的基礎(chǔ)上，我們確定國內(nèi)生產(chǎn)總值（）、實際利用外資額（）、民航線里程（）、來華旅游入境人數(shù)（）為自變

8、量。搜集樣本資料如表所示。表年份民航客運量（萬人）y國內(nèi)生產(chǎn)總值（億元）x1實際利用外資額（億美元）x2民航線里程（萬公里）x3旅游入境人數(shù)（萬人）x419833915934.519.8122.91947.71984554717127.0526.021285.219857478964.446.4727.721783.3198699710202.272.5832.432281.91987131011962.584.5238.912690.21988144214928.3102.2637.383169.51989128316909.2100.5947.192450.11990166018547.9

9、102.8950.682746.21991217821617.8115.5455.913335.01992288626638.1192.02383.663811.51993338334634.4389.696.084152.71994403846759.4432.13104.564368.41995511758478.1481.37112.94638.71996555567884.6548.04116.655112.81997563074462.6644.08142.505758.81998575578345.2585.57150.586347.81999609482067.5526.5915

10、2.227279.62000672289442.2593.56150.298344.42001752495933.3496.72155.368901.3依據(jù)上述樣本資料,計算出因變量與每個自變量的相關(guān)系數(shù)，利用Excel軟件得到下列相關(guān)系數(shù)（見表）表X1X2X3X4y0.9915570.9549220.984550.965219利用Excel計算相關(guān)系數(shù)的步驟如下：“工具”“數(shù)據(jù)分析”“相關(guān)系數(shù)”“確定”在“輸入?yún)^(qū)域”輸入y與x1數(shù)字區(qū)域在“輸出區(qū)域” 輸入A1單元格“確定”就得到y(tǒng)與x1之間的相關(guān)系數(shù)0.991557。 其余相關(guān)系數(shù)以此類推。第三步 建立模型，進行參數(shù)估計。由表知，自變量國內(nèi)

11、生產(chǎn)總值、實際利用外資額、民航線里程、來華旅游入境人數(shù)分別與因變量y間的簡單線性相關(guān)系數(shù)都較高，都在0.95以上。不妨我們建立四元線性回歸模型：。依據(jù)表中的樣本資料，利用Excel軟件計算有關(guān)結(jié)果如輸出結(jié)果7.4.3所示（操作步驟如一元線性回歸）。輸出結(jié)果回歸統(tǒng)計Multiple R0.994469R Square0.988968Adjusted R Square0.985816標準誤差282.3387觀測值19方差分析dfSSMSFSignificance F回歸分析41E+0825011161313.75681.58E-13殘差14111601279715.12總計181.01E+08Co

12、efficients標準誤差t StatP-valueIntercept-139.555232.7273-0.599650.55832X Variable 10.0363560.0142192.5568060.022814X Variable 21.6114441.4682661.0975150.290935X Variable 37.5153439.558490.7862480.444827X Variable 40.2125190.1426651.4896330.158502第四步 進行有關(guān)統(tǒng)計顯著性檢驗從F統(tǒng)計量角度看，四元線性回歸模型通過檢驗（Significance F1.57571

13、E-130.05）。從t檢驗角度看，只有國內(nèi)生產(chǎn)總值通過t檢驗(P-value0.0228140.05)。不妨我們先剔除統(tǒng)計量（t Stat）最小的那個自變量，重新建立回歸模型。因為0.786248“X Variable 3t Stat”（0.786248）最小，所以我們先剔除自變量民航線里程，重新建立三元線性回歸模型。利用Excel軟件計算出三元線性回歸模型有關(guān)信息如輸出結(jié)果所示。輸出結(jié)果回歸統(tǒng)計Multiple R0.994224R Square0.988481Adjusted R Square0.986177標準誤差278.7221觀測值19方差分析dfSSMSFSignificance

14、 F回歸分析39999536533331788429.05779.32E-15殘差15116529077686.03總計181.01E+08Coefficients標準誤差t StatP-valueIntercept-54.3332203.3073-0.267250.792919X Variable 10.0385910.0137542.8057660.013304X Variable 22.4270051.0258482.3658530.03188X Variable 30.2651210.1243922.1313460.05001從F統(tǒng)計量角度看，三元線性回歸模型通過檢驗（Signific

15、ance 9.32E-150.05）。從t檢驗角度看，國內(nèi)生產(chǎn)總值通過t檢驗(P-value0.0133040.05)和實際利用外資額通過t檢驗(P-value0.031880.05)，而來華旅游入境人數(shù)沒有通過t檢驗(P-value0.050010.05)，但相差很小。如果我們再剔除華旅游入境人數(shù)這個自變量，重新建立二元線性回歸模型的話會降低整體模型的優(yōu)良性。（見輸出結(jié)果）輸出結(jié)果回歸統(tǒng)計Multiple R0.992468R Square0.984992Adjusted R Square0.983116標準誤差308.0371觀測值19方差分析dfSSMSFSignificance F回歸

16、分析29964246549821233525.05932.57E-15殘差16151819094886.86總計181.01E+08Coefficients標準誤差t StatP-valueIntercept315.5839117.01662.6969160.015872X Variable 10.0643190.0072858.8289261.51E-07X Variable 21.3822710.9959421.3879030.184192綜上所述，對于本例我們可以建立下列三元線性回歸模型：式中，代表民航客運量；代表國內(nèi)生產(chǎn)總值；代表實際利用外資額；代表來華旅游入境人數(shù)。 第五步 進行預測

17、 1、點預測當國內(nèi)生產(chǎn)總值為100000億元，實際利用外資額為600億美元，來華旅游入境人數(shù)9000萬人次時，民航客運量為： 7647（萬人）2、區(qū)間預測（1）個別值的區(qū)間預測若給定了顯著水平0.05，個別值在95%概率保證下的區(qū)間預測為：其中，=278.7221=1.753 =1.173434=0.376948即76471.753×278.7221×1.173434 70748220之間(2)平均值的區(qū)間預測均值在95%概率保證下的區(qū)間預測為其中，=278.7221=1.753 =0.614即76471.753×278.7221×0.614 73477

18、947之間附：矩陣的運算（1）矩陣乘法 按住鼠標左鍵拖放選定存放結(jié)果的單元格區(qū)域，輸入計算公式=MMULT( A,B) 按Ctrl+Shift+Enter復合鍵確認。（2）矩陣轉(zhuǎn)置 按住鼠標左鍵拖放選定存放結(jié)果的單元格區(qū)域，輸入計算公式=TRANSPOSE( A) 按Ctrl+Shift+Enter復合鍵確認（3）逆矩陣 按住鼠標左鍵拖放選定存放結(jié)果的單元格區(qū)域，輸入計算公式=MINVERSE(A ) 按Ctrl+Shift+Enter復合鍵確認案例17 非線性回歸分析非線性回歸模型具體形式很多，由于篇幅所限，在此不一一介紹，僅通過例題說明其應(yīng)用思想。例設(shè)有12個同類企業(yè)的月產(chǎn)量與單位產(chǎn)品成本

19、資料如表7.5.1所示。試配合適當?shù)幕貧w模型分析月產(chǎn)量與單位產(chǎn)品成本之間的關(guān)系。表編號產(chǎn)量XLgY單位成本Y1102.2041201602162.1789771513202.0569051144252.1072101285311.929419856361.959041917401.875061758451.880814769511.8195446610561.7781516011601.7853306112651.77815160根據(jù)表資料，將月產(chǎn)量與單位產(chǎn)品成本作散點圖如圖7.5.1所示。圖由圖可以看出月產(chǎn)量與單位產(chǎn)品成本之間可以配合一條指數(shù)曲線：兩邊取對數(shù)得：利用軟件，進行回歸分析運行結(jié)果

20、如輸出結(jié)果所示。（操作步驟如一元線性回歸）。輸出結(jié)果回歸統(tǒng)計Multiple R0.96097R Square0.923463Adjusted R Square0.91581標準誤差0.045212觀測值12方差分析dfSSMSFSignificance F回歸分析10.2466310.246630906120.65626.68E-07殘差100.0204410.00204408總計110.267072Coefficients標準誤差t StatP-valueIntercept2.2610830.03150971.75913566.74E-15X Variable 1-0.008310.000

21、756-10.984361156.68E-07由輸出結(jié)果知，2.261083 ，-0.00831 ， ， ，F(xiàn)120.6562Significance F6.68E-07-10.98436115P-value6.68E-07對2.261083-0.00831分別求反對數(shù)得=182.4246 =0.981051利用軟件求反對數(shù)的操作步驟如下:點擊粘貼函數(shù)POWER102.261083確定(182.4246)例某面粉廠近十一年的面粉銷售利潤率、工人勞動生產(chǎn)率、單位成本資料如表7.5.2所示。我們知道，勞動生產(chǎn)率的提高和單位成本的降低，能使利潤增加。因此我們認為，利潤率與勞動生產(chǎn)率成正比，與單位成本

22、成反比。設(shè)利潤率為y，勞動生產(chǎn)率為x1，單位成本為x2，建立回歸模型為：表年份利潤率（）y勞動生產(chǎn)率（噸人）x1單位成本（元噸）x219921.573669.0719931.974687.1619942.156646.5219951.935057.5319961.613878.9319971.373599.7819981.794868.1219992.225486.2320001.393459.8920011.635048.8320022.276585.94設(shè)，則利用軟件，進行回歸分析運行結(jié)果如輸出結(jié)果所示。由輸出結(jié)果知， F179.0624Significance F2.28E-070.05

23、F檢驗通過10.679686P-value0.5158850.05檢驗沒通過2= 7.88228P-value4.86E-050.05 檢驗通過0.989014輸出結(jié)果回歸統(tǒng)計Multiple R0.989014R Square0.97815Adjusted R Square0.972687標準誤差0.053186觀測值11方差分析dfSSMSFSignificance F回歸分析21.0130610.50653179.06242.28E-07殘差80.022630.002829總計101.035691Coefficients標準誤差t StatP-valueIntercept0.104077

24、0.0945291.1010070.302915X Variable 10.0002270.0003340.6796860.515885X Variable 212.39161.5720847.882284.86E-05根據(jù)上述檢驗，兩個自變量中只有單位成本對利潤率的影響顯著，而勞動生產(chǎn)率對利潤率的影響并不顯著。這是因為兩個自變量單位成本和勞動生產(chǎn)率之間存有多重共線性。不妨我們刪去勞動生產(chǎn)率這個因素，重新建立回歸模型。利用軟件，進行回歸分析運行結(jié)果如輸出結(jié)果所示。輸出結(jié)果回歸統(tǒng)計Multiple R0.988376R Square0.976888Adjusted R Square0.97432

25、標準誤差0.051572觀測值11方差分析dfSSMSFSignificance F回歸分析11.0117541.011754380.40361.133E-08殘差90.0239370.00266總計101.035691Coefficients標準誤差t StatP-valueIntercept0.0901870.0894921.0077670.339891X Variable 113.346420.68429319.503941.13E-08由輸出結(jié)果知，F(xiàn)380.4036Significance F1.133E-080.0519.50394P-value1.13E-080.050.9883

26、76模型統(tǒng)計顯著性檢驗通過?！緦嵱栁迳蠙C練習】1.單位成本與產(chǎn)量的關(guān)系(見表7.1)。 表7.1 鑄鐵件產(chǎn)量及單位成本年 月鑄鐵件產(chǎn)量(噸)單位產(chǎn)品成本(元)上年1月8106702月5477803月9006204月5308005月540780 6月800675 7月8206508月850 620 9月600 735 10月690 720 11月700 715 12月860 610今年1月920 5802月840 630 3月1 000 570 要求:（1）繪制鑄鐵件產(chǎn)量與單位產(chǎn)品成本散點圖，計算其相關(guān)系數(shù)；（2）擬合回歸方程。2.已知某地區(qū)1978年2003年的國內(nèi)生產(chǎn)總值GDP與貨運周轉(zhuǎn)量的

27、數(shù)據(jù)如表7.2所示。年份GDP(億元)貨運周轉(zhuǎn)量（億噸公里）年份GDP(億元)貨運周轉(zhuǎn)量（億噸公里）19781979198019811982198319841985198619871988198919905.08.712.016.019.022.025.028.036.040.041.032.034.09.012.014.015.017.020.020.523.530.035.032.024.028.0199119921993199419951996199719981999200020012002200344.047.054.056.556.057.059.063.066.567.070.57

28、0.673.032.034.037.040.044.043.543.543.544.045.547.046.052.0要求：（1）繪制散點圖；（2）試對其進行一元線性回歸分析（即計算相關(guān)系數(shù)、建立回歸方程）；（3）若2005年國內(nèi)生產(chǎn)總值GDP達到80億元，試對其貨運周轉(zhuǎn)量做出預測（包括點預測和區(qū)間預測）3.教育經(jīng)費支出與學生成績的關(guān)系學生教育達到的水平與學生所居住的洲在教育方面的經(jīng)費支出多少有關(guān)系嗎？在許多地區(qū)，這個重要問題被納稅人提出；而納稅人又被他們的學區(qū)請求增加用于教育方面的稅收收入。在這種情況下，為了確定在公立學校中教育經(jīng)費支出和學生成績之間是否存在某種關(guān)系，你將被邀請去參加教育經(jīng)費

29、支出和學生學習成績的數(shù)據(jù)分析。美國聯(lián)邦政府的全國教育進展評價（NAEP）計劃常常被用來測量學生的教育水平。對于參加NAEP計劃的35個洲，表1給出了每名學生每年的經(jīng)常性教育經(jīng)費支出和NAEP測試綜合分數(shù)的統(tǒng)計數(shù)據(jù)。綜合分數(shù)是數(shù)學、自然科學和閱讀三門課程1996年（閱讀課是1994年）NAEP測試分數(shù)的總和。參加測試的是8年級學生，只有閱讀課是4年級學生，滿分是1300分。對于未參加NAEP計劃的13個洲，表2給出了每名學生每年的經(jīng)常性教育經(jīng)費支出。 表1 參加NAEP計劃的洲每名學生每年的經(jīng)常性教育經(jīng)費支出和NAEP測試綜合分數(shù)洲序號教育經(jīng)費支出（美元）綜合分數(shù)（分）洲序號教育經(jīng)費支

30、出（美元）綜合分數(shù)（分）140495811945216292342358220655463834917580215338639455325802244836415430460323477264463777604245128649746636112532806508493461126551565794097614277629657104060614286413658116208615295410660123800618305477661134041618315060665145247625324985667156100625336055667165020626344374671174520627

31、355561675188162628   表2 未參加NAEP計劃的洲每名學生每年的經(jīng)常性教育經(jīng)費支出洲序號教育經(jīng)費支出（美元）洲序號教育經(jīng)費支出（美元）136028543824067955883426510626944658116391551641265796529713789075387  要求:（1）對這些數(shù)據(jù)做出數(shù)值的和圖示的概述。（2）利用回歸分析研究每名學生的教育經(jīng)費支出和NAEP測試綜合分數(shù)之間的關(guān)系，對你的調(diào)研結(jié)果進行討論。（3）根據(jù)這些數(shù)據(jù)求出估計的回歸方程，你認為能利用它來估計未參加NAEP計劃洲的學生的綜合分數(shù)嗎？（4）假

32、定你只考慮每名學生的教育經(jīng)費支出在4000美元至6000美元之間的洲，對于這些洲，兩變量之間的關(guān)系與根據(jù)35個洲的全部數(shù)據(jù)所得出的結(jié)論顯現(xiàn)出任何不同嗎？討論你發(fā)現(xiàn)的結(jié)果，如果將教育經(jīng)費支出少于4000美元或者多于6000美元的洲刪除，你是否認為刪除是合理的？（5）對未參加NAEP計劃洲，求出學生綜合分數(shù)的估計值。（6）根據(jù)上面的分析，你認為學生的教育水平與洲教育經(jīng)費支出的多少相關(guān)嗎？4.發(fā)生車禍次數(shù)與司機年齡有關(guān)嗎？作為交通安全研究的一部分，美國交通部采集了每1000個駕駛執(zhí)照發(fā)生死亡事故的車禍次數(shù)和有駕駛執(zhí)照的司機中21歲以下者所占比例的數(shù)據(jù)，樣本由42個城市組成，在一年間采集的數(shù)據(jù)如下：21歲以下者所占比