運用Excel進行逐步回歸在體育研究中的應(yīng)用①

朱禮恒

（齊魯師范學院體育學院 山東濟南 250200）

運用Excel進行逐步回歸在體育研究中的應(yīng)用①

朱禮恒

（齊魯師范學院體育學院 山東濟南 250200）

介紹運用Microsoft office的組件之一，辦公軟件Excel“數(shù)據(jù)分析”工具，對體育統(tǒng)計數(shù)據(jù)逐步回歸分析的簡便操作方法；參照基于相關(guān)系數(shù)矩陣進行公式計算的逐步回歸結(jié)果，進行數(shù)據(jù)對比，處理結(jié)果完全一致；相比采用Linest、Trend等Excel函數(shù)計算逐步回歸方程的方法，同樣具有明顯的效率優(yōu)勢，操作性能更加優(yōu)越；應(yīng)用Excel“數(shù)據(jù)分析”工具進行逐步回歸分析，工作界面熟悉，操作簡便快捷，數(shù)據(jù)結(jié)果可靠，可廣泛應(yīng)用于體育研究，以及其它領(lǐng)域的多元回歸分析。

Excel 數(shù)據(jù)分析工具 逐步回歸 體育 數(shù)據(jù)分析

在體育研究數(shù)據(jù)的處理中，經(jīng)常會遇到回歸分析的問題。即通過一定的回歸方程式來描述變量間的數(shù)量關(guān)系，以便對該體育現(xiàn)象的發(fā)展做出科學預(yù)測與控制。其中面對多元回歸時，需要對多個影響因子進行篩選，離不開逐步回歸分析的方法［1］。逐步回歸就是按照一定的置信標準，對所有影響因子逐個進行假設(shè)檢驗，篩選出有顯著影響的因子作為有效自變量，建立“最優(yōu)”回歸方程，提高預(yù)測與控制精度的一種數(shù)據(jù)分析方法。

傳統(tǒng)做法是，采用一定的公式算法優(yōu)化模型參數(shù)，使計算值與觀測值之間的殘差平方和最小，即常說的“最小二乘法”原理［2］，在相關(guān)系數(shù)矩陣的基礎(chǔ)上，每選擇引入一個自變量或剔除某個自變量都要進行F檢驗，直至篩選出最具顯著性變量，計算出相應(yīng)的回歸系數(shù)與截距，獲得“最優(yōu)”回歸方程，并求出相關(guān)檢驗參數(shù)等［3］。這一做法計算量大、步驟多、過程復雜，一般人員難以掌握。從統(tǒng)計實踐上看，逐步回歸往往讓人望而卻步。當前較為常用的統(tǒng)計軟件有SPSS和SAS等，但此類軟件需要專門購買安裝，人機交互界面比較復雜，對于大多數(shù)人來說難以理解和掌握，使用起來不方便。Excel是常見的電子表格軟件，應(yīng)用其“數(shù)據(jù)分析”工具能夠方便地進行逐步回歸分析，為數(shù)據(jù)處理提供了非常便捷有效的工具，但這方面的實踐研究還比較欠缺。該研究根據(jù)大量的體育統(tǒng)計實踐，對Excel逐步回歸方法加以探討，并舉例說明運用Excel進行逐步回歸的詳細過程，為同行做逐步回歸分析提供參考。該文所用軟件為Microsoft Excel 2007。

圖1　“Excel-工具-數(shù)據(jù)分析-回歸”對話框

1　工具

1.1計算機操作系統(tǒng)條件

操作系統(tǒng)Windows XP，辦公軟件Microsoft offiee2007，采用經(jīng)典安裝模式。

1.2安裝Excel“數(shù)據(jù)分析”工具

分析工具庫是安裝在Microsoft Office加載項（加載項：為Microsoft Office提供自定義命令或自定義功能的補充程序。）中的程序。但是，要在Excel中使用它，通常需要先進行加載。具體操作如下：打開Excel。

（1）單擊頁面左上角的“Microsoft Office按鈕”，然后單擊“Excel選項”。

（2）單擊“加載項”，然后在“管理”框中，選擇“Excel加載宏”。

（3）單擊“轉(zhuǎn)到”。

在“可用加載宏”框中，選中“分析工具庫”復選框，然后單擊“確定”；（提示：如果“可用加載宏”框中未列出“分析工具庫”，請單擊“瀏覽”以找到它。）

（4）如果系統(tǒng)提示計算機當前未安裝“分析工具”庫，請單擊“是”以安裝它。

（5）加載完成“分析工具”庫之后，“數(shù)據(jù)分析”命令將出現(xiàn)在“數(shù)據(jù)”選項卡上的“分析”組中。

2　方法

2.1操作思路

改變以往依賴相關(guān)系數(shù)矩陣，從大到小，由少到多，對各自變量依次引入，逐個進行方差檢驗的做法。變?yōu)橛扇繑?shù)據(jù)開始整體回歸分析開始，首先進行因變量y與全部自變量x之間的總回歸分析；再對總回歸及其每個自變量進行假設(shè)檢驗。當總回歸不顯著時，表明該多元回歸方程線性關(guān)系不成立；而當某些自變量對y影響不顯著時，就把它們剔除，重新建立包含具有顯著性影響變量的多元回歸方程，得到“最優(yōu)”回歸方程，同時獲得相應(yīng)的回歸效果檢驗參數(shù)，全程無需計算，具體參數(shù)由回歸分析表直接讀取，非?？旖葜庇^。

2.2操作步驟

（1）選用Excel“數(shù)據(jù)分析”工具，把所研究原始數(shù)據(jù)的因變量和所有自變量一次性導入“回歸”分析工具中，直接輸出計算結(jié)果。

（2）看表讀取回歸方程的回歸系數(shù)b、截距a、復相關(guān)系數(shù)R、標準誤差Sy（剩余標準差）、回歸分析方差F及概率值P，以及各回歸系數(shù)所對應(yīng)的t檢驗值及其概率值P。

（3）根據(jù)置信值α的大小，將不具有顯著性意義的變量剔除；對所剩余的各項顯著性變量，再進行第二步回歸分析。

（4）得到效果顯著的回歸方程參數(shù)，根據(jù)參數(shù)列出“最優(yōu)”回歸方程，并登記相應(yīng)的方差值、概率值、預(yù)測精度等檢驗結(jié)果。

2.3實例分析過程

以教材《體育統(tǒng)計學》［4］P166例題9.3為例。在跨欄課的教學研究中，為了分析各種教學手段與其它因素對跨欄成績的影響，測試了體育系30名學生的7項指標：Χ1（跳動跨皮筋）、Χ2（欄間小步跑）、Χ3（起跑過三欄）、Χ4（100米跑）、Χ5（挺舉）、Χ6（立定三級跳遠）、У（跨半程欄）的原始數(shù)據(jù)（見表1）。請對這些數(shù)據(jù)進行逐步回歸，分析各項訓練指標與跨半程欄跑成績的關(guān)系。

表1　體育系30名學生訓練測試成績登記表

（1）建立Excel數(shù)據(jù)表，選擇“回歸”工具，進行第一步回歸分析。

打開Excel，將（表1）原始數(shù)據(jù)輸入到空白的工作表中，審核無誤后，點擊“常用工具欄”右側(cè)的“數(shù)據(jù)分析”，從中找到“回歸”選項，點擊確定，打開“回歸”對話框。（如圖）

（2）輸入Y值和X值，讀取回歸分析表數(shù)據(jù)，作回歸效果檢驗，列出回歸方程。

在Y值輸入?yún)^(qū)域（Y）：輸人因變量數(shù)據(jù)H2：H31；X值輸人區(qū)域（X）：輸人所有6項自變量的全部數(shù)據(jù)B2：G31；置信度默認95%；在輸出選項中選擇“新工作表”，然后點擊確定，輸出回歸分析結(jié)果如下，分析數(shù)據(jù)見表2。

表2　跨半程欄跑成績與6個訓練項目成績的回歸分析表

根據(jù)表2結(jié)果，可以直接讀取回歸方程的主要檢驗參數(shù)：（1）Multiple R（回歸方程復相關(guān)系數(shù)）R=0.9567。（2）R Square（擬合程度決定系數(shù)）R2=0.9135。（3）方差值F=41.4192。（4）回歸分析概率值P=3.365×10-11＜0.001（回歸效果非常顯著）。（5）標準誤差Sy=0.1026。直接讀取Coefficients（系數(shù)）、Intercept（截距）數(shù)據(jù)，得到初步回歸方程：

（3）對照置信水平α值的大?。ū纠ˇ?0.05），對各自變量的回歸系數(shù)進行逐個檢驗，將不具有顯著意義的自變量去除，完成指標篩查。

表2顯示，各自變量回歸系數(shù)的檢驗數(shù)據(jù)t值和概率值P分別是：Χ1（t1=2.2028，P1=0.0379＜0.05）、Χ2（t2=3.4551，P2=0.0022＜0.05）、Χ5（t5=-2.2435，P5=0.0348＜0.05）三項自變量的概率值均小于0.05，表明這些自變量回歸具有顯著的統(tǒng)計學意義；而Χ3（t3=0.0911，P3=0.9282＞0.05）、Χ4（t4=0.0043，P4=0.9966＞0.05）和Χ6（t6=0.2654，P6=0.7931＞0.05）這三項自變量的概率值P均大于0.05，表明這些變量不具備顯著的回歸意義。因此，可以把Χ3、Χ4和Χ6三項指標淘汰，不再引入到第二步的“最優(yōu)”回歸分析中。

（4）對剩余指標Χ1、Χ2、Χ5原始數(shù)據(jù)進行第二步回歸分析，操作過程同上，分析結(jié)果見表3。

表3　跨半程欄跑成績與3個訓練項目成績的回歸分析表

表3結(jié)果顯示，回歸方程的主要檢驗參數(shù)：（1）Multiple R（復相關(guān)系數(shù)）R=0.9566。（2）方差值F=93.3098。（3）回歸分析概率值P=4.85×10-14＜0.001，表明回歸效果非常顯著。（4）R Square（決定系數(shù)）R2=0.9150，表明擬合程度非常好。（5）標準誤差Sy=0.0967。（6）該方程中的三個指標回歸系數(shù)的檢驗數(shù)據(jù)概率值P分別是：P1=0.0174、P2=0.0002、P5=0.0164，三值均小于0.05，說明這三個指標對回歸方程均具有顯著性意義。（7）直接讀取Coefficients（系數(shù)）、Intercept（截距）數(shù)據(jù)，得到“最優(yōu)”回歸方程為：

其中，Χ1、Χ2、Χ5分別代表著跳動跨皮筋、欄間小步跑、挺舉的訓練成績；檢驗表明，回歸方程高度顯著（P＜0.05）。

以上分析 表明，在6項教學手段中，跳動跨皮筋、欄間小步跑兩項與跨半程欄跑成績顯著正相關(guān)，貢獻率分別是0.2923和0.5423，加強這兩項訓練是有效的教學手段；挺舉與跨半程欄跑成績呈現(xiàn)負相關(guān)，應(yīng)當避免這種有害的教學訓練；另外3種教學手段與提高跨半程欄成績，沒有顯著意義，可以停止這些無益的教學訓練。

（5）與傳統(tǒng)方法人工計算逐步回歸方程的結(jié)果比較。對比數(shù)據(jù)來源于叢湖平著的《體育統(tǒng)計學》P170，摘錄其方差分析表、回歸方程、回歸檢驗等數(shù)據(jù)［4］如下：

回歸方程為：Y=0.6403+0.2923Χ1+0.5423Χ2-0.00808Χ5

復相關(guān)系數(shù)R=0.9566；剩余標準差Sy=0.0967

表4　回歸方程方差分析表

根據(jù)3、表4數(shù)據(jù)，逐一對比上述兩種方法得到的回歸方程、復相關(guān)系數(shù)、剩余標準差等主要檢驗參數(shù)，數(shù)據(jù)處理的結(jié)果完全一致。說明用Excel“數(shù)據(jù)分析”工具進行多元逐步回歸，可以替代傳統(tǒng)人工計算計算逐步回歸方程的做法，結(jié)果準確可靠。

3　討論

（1）Excel是最常用的辦公軟件之一，應(yīng)用其“數(shù)據(jù)分析”工具，無需專門購買，經(jīng)濟實用，方便可行；只需掌握計算機應(yīng)用能力的基本知識，就能獨立快速的完成逐步回歸分析。

（2）該研究表明，相對于傳統(tǒng)的計算方法，使用Excel“數(shù)據(jù)分析”工具進行多元逐步回歸，操作過程簡便，可以替代傳統(tǒng)人工計算方式，求解逐步回歸方程，避開了繁雜的公式運算、繁多的計算步驟等弊端，且輸出結(jié)果快捷直觀、準確可靠。

（3）相對于應(yīng)用Excel函數(shù)LINEST、函數(shù)TREND、函數(shù)TINV和FDIST［6］計算回歸方程，這一方法同樣具有非常明顯的優(yōu)勢，步驟簡潔，語法簡單，操作簡便，非常便于廣大體育工作者在教學研究中使用。

（4）應(yīng)用Excel“數(shù)據(jù)分析”工具進行多元逐步回歸，注意在完成第一步總體回歸后，要確定各自變量所對應(yīng)的概率P的臨界值標準，為自變量篩查提供可靠依據(jù)；P值越小，則篩選掉的自變量就越多；P值越大，篩選掉的自變量就越少；P=1時，逐步回歸就變成了普通的多元回歸；在第二步回歸時，因為各自變量的順序重新編排，需要認真對照自變量的序號、回歸系數(shù)、概率值，以達到一一對應(yīng)，確?！白顑?yōu)”回歸方程的準確性。

［1］陳及治.體育統(tǒng)計［M］.北京：人民體育出版社，2002：187-188.

［2］江體乾.化工數(shù)據(jù)處理［M］.北京：化學工業(yè)出版社，1984.

［3］郭強，施海波.利用Excel進行體育多種教法之間的比較［J］.體育世界，2007（2）：52.

［4］叢湖平.體育統(tǒng)計學［M］.北京：高等教育出版社，2007.

［5］盛紹增，朱禮恒，郭倩，等.29屆奧運會中國男子籃球隊進攻區(qū)域的統(tǒng)計與分析［J］.中國體育科技，2009（3）：21-24.

［6］王飛鳳，劉鑄飄.用Excel作逐步回歸分析［J］.廣東氣象， 2011（5）：48-51.

［7］趙玉林，高英.農(nóng)村電力系統(tǒng)負荷預(yù)測的研究——基于Excel回歸模型［J］.農(nóng)機化研究，2014（2）：226-228.

The use of Excel for the Application of Stepwise Regression in Sports Research

Zhu Li heng
（Qilu Normal University，Jinan Shandong，250200，China）

One use Microsoft office components，office software Excel "Data analysis" tool of sports statistics stepwise regression analysis method is simple；

tepwise regression formula to calculate the results based on the correlation coefficient matrix，comparing the data processing exactly the same result；compared to using the method Linest，Trend and other Excel function calculates regression equation，also has significant efficiency advantages，superior operating performance；application Excel "data Analysis" tool stepwise regression analysis，the working interface familiar，easy to operate fast，reliable data the results can be widely used in sports research，as well as other areas of multivariate regression analysis.

Excel；Data analysis tools；Stepwise regression；Sports；Data analysis

G80-32

A

2095-2813（2015）11（b）-0205-03

10.16655/j.cnki.2095-2813.2015.32.205

朱禮恒（1971，10—），男，漢，山東臨沂人，碩士研究生，職稱：副教授，研究方向：體育統(tǒng)計分析，體育心理學。