VB结合Excel实现逐步回归分析

房鹏 马联敏 莫芳 王建嫱 马献菊

摘要：用VB结合Excel设计一个易于操作、实用性强的逐步回归程序。在程序设计中，基于逐步回归的相关矩阵变换原理，设定二维数组变量，通过多重循环实现矩阵变换，从而实现对众多自变量的筛选;借助Excel计算临界值，以判断自变量的引入和剔除，并对生成的回归方程进行显著性检验等。

关键词：VB;Excel;逐步回归;编程

中图分类号：TP18        文献标识码：A

文章编号：1009-3044（2019）16-0231-05

开放科学（资源服务）标识码（OSID）：

Abstract：This paper designs a stepwise regression procedure which is easy to operate and practical by using VB and Excel. In the process of program design， based on the principle of matrix transformation of correlation coefficient of stepwise regression， we set some two-dimensional array variables， and realize matrix transformation through multiple loops， so as to realize the selection of many independent variables. Excel is used to calculate the critical value to determine whether the independent variables are introduced or eliminated， and to test the significance of the regression equation.

Key words：VB;Excel;stepwise regression;programming

逐步回归在众多领域，包括气象上的短期天气预报、长期气候预测及农业气象等方面都有着较为广泛的应用，它是事先从专业角度初选出多个自变量，然后运用数学方法，将对因变量方差贡献显著的自变量引入方程，而将对因变量方差贡献不显著的自变量从方程中剔除，重复往返，直到没有变量可引入方程，同时方程中也没有变量可被剔除出去，从而使方程保留了相互之间相对独立或相关性较小，且与因变量相关相对显著的自变量，最终得到在初选自变量集合范围内、在某个选元显著性水平上的“最优”方程。由于逐步回归手工计算过程比较复杂，其间需要不停翻查统计表，步骤较为烦琐，为提高运算效率，故设计了一个操作方便、简洁实用的逐步回歸程序。本程序利用VB来操控Excel，是VB结合Excel编写逐步回归程序的一种尝试。

1 逐步回归程序设计

1.1 Excel工具文件

本程序运行需要两个Excel文件，将一个文件命名为“工作表”（程序中变量名为xlBook（1）），此文件用于输入因变量和自变量原始数据，并借助其输出拟合图和回归方程;另一个文件命名为“F检验”（程序中变量名为xlBook（2）），用于计算、贮存逐步回归运算过程中引入和剔除变量时的F检验值，以及对生成的回归方程进行显著性检验。

1.2逐步回归程序具体思路

1.2.1 读入原始数据

设定一个数组变量mycell（），将原始数据读入、贮存到该数组变量中，用于后面计算因变量与自变量、自变量与自变量之间的相关系数，以生成相关系数初始矩阵。

设定两个整型变量mym和myn，从“工作表.xls”工作簿的“输入数据”工作表中分别获取原始数据的行数和列数，这里用Excel自带的函数COUNTA（）取得mym值和myn值。

按照输入习惯，在“输入数据”工作表中，将第1列输入为因变量数据，在其他列输入自变量数据。在计算相关系数的时候，这里是将读入数组mycell（1 To mym， 1 To myn）中的原始数据最后一列（myn列）作为因变量列，其他列则作为自变量列。

1.2.2 求算相关系数矩阵

相关系数矩阵是因变量和自变量以及各自变量之间的相关系数形成的矩阵，该矩阵是逐步回归筛选自变量的基础矩阵。数组变量mycell（）已经获取了因变量和自变量原始数据，下面开始求算相关系数矩阵。

计算两组数据之间（线性）相关系数的公式为：

设定数组变量mycorel（），用于存贮相关系数计算结果。设定二维数组变量mydata（），以矩阵的形式读入mycorel（）中的相关系数值，通过以下二重循环实现：

从而得到以下形式的相关系数初始矩阵，贮存于数组mydata（）中：

1.2.3 因子筛选

设定一个二维数组变量nx（1 To myn - 1， 0 To 1），其中第一维是因子编号，第二维的取值为0和1，1表示某因子是引入状态，0则表示某因子是非引入状态，即未被引入或被剔除状态。变量nx（）的值为0和非0，非0时为因子序号。设定一个变量nxsum，用于统计引入的因子数量。

引入和剔除因子，以及生成回归方程的显著性需要进行检验，这里是通过调用Excel文件进行。在“F检验.xls”的工作簿中，其工作表“Sheet1”中的单元格A1用于获取文本框Text1（1）中的筛选水平（α值），B1为分子自由度，这里固定取值为1;C1为分母自由度，取值代码为Cells（1， 3） = mym - nxsum – 2;当分子自由度、分母自由度及检验水平（α值）确定的情况下，在D1中得到检验引入变量时的临界值，代码为Cells（1， 4） = "= FINV（A1，B1，C1） "，设置变量Fcriti，以获取D1中的值。

由于引入前两个因子时不需作剔除检验，当引入第三个因子时再进行剔除检验[1]。因此，引入因子部分分两个步骤进行，第一步是看首先能否引进两个因子，如果第一次引入检验都无法引进因子或只能引进少于三个因子，则无须进行后面的操作。第二步当引进的因子数量能够达到两个以上，则需要进行是否剔除的判断。

第一步，判断初次引进因子的数量。在这一步判断出能否引进因子，或者能否引进一个或两个因子;如果能引进，则是哪一个或哪两个因子。

能否引进因子通过F分布的统计量进行检验：

方差V（）值在程序中用变量myv（）贮存（下同），基于原始相关矩阵，得到各方差值后，找出其最大值。设定一个整型变量myk，用于记录方差最大值所在变量是第几列。

將最大方差值代入F检验公式计算出相应的F值（程序中用myf表示，下同）。如果myf>Fcriti，表示该因子可引进，则nx（myk，1） = myk： nx（myk， 0） = 0，即第myk个因子可引进，并将第myk个因子已引进状态值设为myk，未引进状态值设为0，然后对该列（myk列）进行矩阵变换，以判断下一个因子是否能被引入。如果myf

引入第一个因子后，判断能否引入第二个因子前，要对原矩阵进行一次变换。通过求解求逆紧凑变换方案（或叫高斯-亚当变换、消去求逆紧凑方案）对原矩阵进行一次变换，变换公式为：

假如刚开始已引进了两个因子，在引进了第三个因子之后，进入第二重循环的剔除因子判断，如果能剔除一个因子，则针对该因子所在序列值，运用公式（8）对矩阵进行一次变换，继续判断有没有因子可剔除，如此循环，直到没有因子可剔除，跳出该循环;然后再判断是否有因子可引入。这时将出现两种情况，一是能够继续引进因子，然后又回到上面的剔除循环，二是不能再继续引进因子，即表示既没有因子可引入，又没有因子可剔除，则逐步回归结束。

1.3 生成回归方程

1.3.1 方程因子系数

在回归方程中各因子系数b（）的计算公式为：

1.3.2 方程常数项

1.3.3 计算剩余标准差

剩余标准差的大小，反映了模型的预测精度或实测值与回归值的平均偏差，其值越小，说明回归效果越好，建立的模型越好。剩余标准差的计算公式为：

1.3.4 方程复相关系数

1.4 方程的显著性检验

1.4.1 用于检验的F值

1.4.2 不同显著性水平的F值

1.4.3 显著性检验结果

1.5 输出回归方程及拟合图

1.5.1 输出回归方程

用字符串型变量myEqtnx获取回归方程的各因子及其系数，其中没有包含常数项。

1.5.2 输出拟合图

1.5.2.1 计算模拟值

因变量的实测值为已知，回归方程中各因子的系数及常数项也已在前面计算出来，现计算模拟值（在程序中用变量mySimu（）表示）。被引入因子的观测值分别乘以其各自系数，并加上常数项，即得到模拟值。

1.5.2.2拟合图的显示

在名为“工作表”的Excel文件中，将因变量的模拟值和实测值分别贮存于工作表“拟合图”的A列和B列中。将数据进行整体写入，以增加写入速度，即设置一个中间过渡用的数组变量myary（），读入因变量的模拟值和实测值;工作表“拟合图”的A列和B列的相应范围取得myary值，程序代码为：

以A列、B列中的数据为数据源作曲线图，即xlApp.ActiveChart.SetSourceData Source：=.Range（"A" & 1 & "：B" & mym）， PlotBy：=xlColumns，复制该曲线图，即.ChartObjects（"图表 1"）.Copy，此时剪贴板中贮存了模拟值与实测值的拟合图。程序的图片框获取剪贴板中的拟合图，即Picture1.Picture = Clipboard.GetData，这样在程序图片框Picture1中显示出了拟合图（如图1）。

2 结论和讨论

1） 在程序设计中，矩阵中的数组变量如果采用三维及以上的数组，则程序运行时会占用电脑较大的内存空间，在数据量很大的情况下，如果电脑内存较小或不足，程序运行会出现错误，因此在这种情况下，应尽量使用维数较少的一维或二维数组。但一维数组虽然在运行时占用电脑内存空间较小，但程序设计起来会相对复杂一些，因此，本程序中矩阵数组变量采用的是二维数组。

2） 本程序从Excel中获取用于检验的临界值，以判断逐步回归过程中自变量的引入和剔除，同时运用Excel对生成的回归方程进行显著性检验以及对回归拟合图的生成等，进一步简化了程序代码，提高了编程效率。

3） 本程序的主要功能是对因变量和众多自变量数据间进行逐步回归分析，程序力图简便、易用，减少、简化一些烦琐的操作选项，程序界面简洁，易于操作，实用性强。后期从实际工作出发，结合实际工作的需要，逐步增加数理统计的其他分析功能。

4） vb虽然是一个较好的编程工具，但其函数公式、绘制图表等功能还不及excel的丰富和多样，同时，Excel有时并不能较方便地实现一些具有一定专业性、针对性的统计功能，很多时候其完成一项统计操作步骤较为复杂，因此这里用vb来调用Excel，各取所长，使操作步骤更简洁，运行速度更快捷，从而提高工作效率。

5） 逐步回归被应用于众多领域，鉴于其在气象上也有较广泛的应用，本文通过VB结合Excel设计了逐步回归程序，并提供了此方面的程序设计思路，同时也可为基层气象工作者提供经济实用的逐步回归分析程序。

参考文献：

[1] 黄嘉佑.气象统计分析与预报方法[M].北京：气象出版社，2004.

[2] 施能.气象科研与预报中的多元分析方法[M].北京.气象出版社.2002.

[3] 何晓群，刘文卿.应用回归分析[M].北京.中国人民大学出版社.2001.

[4] 刘晓宇，孟军.基于逐步回归的黑龙江省烟叶产量预测[J].中国农学通报，2012，28（7）：223-227.

[5] 杨廉伟，王会福，陈将赞，等.浙江省天台县晚稻褐飞虱发生关键因子分析与逐步回归预测[J].中国农学通报，2010，26（12）：227-232.

[6] 孟力力，杨其长.MATLAB和VB在温室环境模型构建中的混合编程研究[J].中国农学通报，2012，28（6）：262-268.

[7] 陈静，王昌斌，蒋万祥.2010年春季微山湖叶绿素a及初级生产力研究[J].中国农学通报，2011，27（23）：265-268.

[8] 赵丹丹，翟石艳.1951—2012年河南省气候变化对冬小麦和玉米产量的影响[J].中国农学通报，2015，31（29）：152-157.

[9] 姜燕敏，金志凤，李松平，等.浙南春茶开采前后气象条件分析及开采期预报[J].中国农业气象，2015，36（02）：212?219.

[10] 张利华，任曙霞，张永强，等.梨树始花期预报[J].气象科技，2012，40（3）：485-488.

【通联编辑：唐一东】