運用Excel進行逐步回歸在體育研究中的應用①

朱禮恒

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

運用Excel進行逐步回歸在體育研究中的應用①

朱禮恒

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

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

Excel 數據分析工具 逐步回歸 體育 數據分析

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

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

圖1　“Excel-工具-數據分析-回歸”對話框

1　工具

1.1計算機操作系統條件

操作系統Windows XP，辦公軟件Microsoft offiee2007，采用經典安裝模式。

1.2安裝Excel“數據分析”工具

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

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

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

（3）單擊“轉到”。

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

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

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

2　方法

2.1操作思路

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

2.2操作步驟

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

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

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

（4）得到效果顯著的回歸方程參數，根據參數列出“最優”回歸方程，并登記相應的方差值、概率值、預測精度等檢驗結果。

2.3實例分析過程

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

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

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

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

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

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

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

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

（3）對照置信水平α值的大小（本例取α=0.05），對各自變量的回歸系數進行逐個檢驗，將不具有顯著意義的自變量去除，完成指標篩查。

表2顯示，各自變量回歸系數的檢驗數據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，表明這些自變量回歸具有顯著的統計學意義；而Χ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三項指標淘汰，不再引入到第二步的“最優”回歸分析中。

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

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

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

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

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

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

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

復相關系數R=0.9566；剩余標準差Sy=0.0967

表4　回歸方程方差分析表

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

3　討論

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

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

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

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

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

［2］江體乾.化工數據處理［M］.北京：化學工業出版社，1984.

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

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

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

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

［7］趙玉林，高英.農村電力系統負荷預測的研究——基于Excel回歸模型［J］.農機化研究，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—），男，漢，山東臨沂人，碩士研究生，職稱：副教授，研究方向：體育統計分析，體育心理學。