运用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—），男，汉，山东临沂人，硕士研究生，职称：副教授，研究方向：体育统计分析，体育心理学。