VB結(jié)合Excel實(shí)現(xiàn)逐步回歸分析

房鵬 馬聯(lián)敏 莫芳 王建嬙 馬獻(xiàn)菊

摘要：用VB結(jié)合Excel設(shè)計(jì)一個(gè)易于操作、實(shí)用性強(qiáng)的逐步回歸程序。在程序設(shè)計(jì)中，基于逐步回歸的相關(guān)矩陣變換原理，設(shè)定二維數(shù)組變量，通過(guò)多重循環(huán)實(shí)現(xiàn)矩陣變換，從而實(shí)現(xiàn)對(duì)眾多自變量的篩選;借助Excel計(jì)算臨界值，以判斷自變量的引入和剔除，并對(duì)生成的回歸方程進(jìn)行顯著性檢驗(yàn)等。

關(guān)鍵詞：VB;Excel;逐步回歸;編程

中圖分類號(hào)：TP18? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2019）16-0231-05

開放科學(xué)（資源服務(wù)）標(biāo)識(shí)碼（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

逐步回歸在眾多領(lǐng)域，包括氣象上的短期天氣預(yù)報(bào)、長(zhǎng)期氣候預(yù)測(cè)及農(nóng)業(yè)氣象等方面都有著較為廣泛的應(yīng)用，它是事先從專業(yè)角度初選出多個(gè)自變量，然后運(yùn)用數(shù)學(xué)方法，將對(duì)因變量方差貢獻(xiàn)顯著的自變量引入方程，而將對(duì)因變量方差貢獻(xiàn)不顯著的自變量從方程中剔除，重復(fù)往返，直到?jīng)]有變量可引入方程，同時(shí)方程中也沒有變量可被剔除出去，從而使方程保留了相互之間相對(duì)獨(dú)立或相關(guān)性較小，且與因變量相關(guān)相對(duì)顯著的自變量，最終得到在初選自變量集合范圍內(nèi)、在某個(gè)選元顯著性水平上的“最優(yōu)”方程。由于逐步回歸手工計(jì)算過(guò)程比較復(fù)雜，其間需要不停翻查統(tǒng)計(jì)表，步驟較為煩瑣，為提高運(yùn)算效率，故設(shè)計(jì)了一個(gè)操作方便、簡(jiǎn)潔實(shí)用的逐步回歸程序。本程序利用VB來(lái)操控Excel，是VB結(jié)合Excel編寫逐步回歸程序的一種嘗試。

1 逐步回歸程序設(shè)計(jì)

1.1 Excel工具文件

本程序運(yùn)行需要兩個(gè)Excel文件，將一個(gè)文件命名為“工作表”（程序中變量名為xlBook（1）），此文件用于輸入因變量和自變量原始數(shù)據(jù)，并借助其輸出擬合圖和回歸方程;另一個(gè)文件命名為“F檢驗(yàn)”（程序中變量名為xlBook（2）），用于計(jì)算、貯存逐步回歸運(yùn)算過(guò)程中引入和剔除變量時(shí)的F檢驗(yàn)值，以及對(duì)生成的回歸方程進(jìn)行顯著性檢驗(yàn)。

1.2逐步回歸程序具體思路

1.2.1 讀入原始數(shù)據(jù)

設(shè)定一個(gè)數(shù)組變量mycell（），將原始數(shù)據(jù)讀入、貯存到該數(shù)組變量中，用于后面計(jì)算因變量與自變量、自變量與自變量之間的相關(guān)系數(shù)，以生成相關(guān)系數(shù)初始矩陣。

設(shè)定兩個(gè)整型變量mym和myn，從“工作表.xls”工作簿的“輸入數(shù)據(jù)”工作表中分別獲取原始數(shù)據(jù)的行數(shù)和列數(shù)，這里用Excel自帶的函數(shù)COUNTA（）取得mym值和myn值。

按照輸入習(xí)慣，在“輸入數(shù)據(jù)”工作表中，將第1列輸入為因變量數(shù)據(jù)，在其他列輸入自變量數(shù)據(jù)。在計(jì)算相關(guān)系數(shù)的時(shí)候，這里是將讀入數(shù)組mycell（1 To mym， 1 To myn）中的原始數(shù)據(jù)最后一列（myn列）作為因變量列，其他列則作為自變量列。

1.2.2 求算相關(guān)系數(shù)矩陣

相關(guān)系數(shù)矩陣是因變量和自變量以及各自變量之間的相關(guān)系數(shù)形成的矩陣，該矩陣是逐步回歸篩選自變量的基礎(chǔ)矩陣。數(shù)組變量mycell（）已經(jīng)獲取了因變量和自變量原始數(shù)據(jù)，下面開始求算相關(guān)系數(shù)矩陣。

計(jì)算兩組數(shù)據(jù)之間（線性）相關(guān)系數(shù)的公式為：

設(shè)定數(shù)組變量mycorel（），用于存貯相關(guān)系數(shù)計(jì)算結(jié)果。設(shè)定二維數(shù)組變量mydata（），以矩陣的形式讀入mycorel（）中的相關(guān)系數(shù)值，通過(guò)以下二重循環(huán)實(shí)現(xiàn)：

從而得到以下形式的相關(guān)系數(shù)初始矩陣，貯存于數(shù)組mydata（）中：

1.2.3 因子篩選

設(shè)定一個(gè)二維數(shù)組變量nx（1 To myn - 1， 0 To 1），其中第一維是因子編號(hào)，第二維的取值為0和1，1表示某因子是引入狀態(tài)，0則表示某因子是非引入狀態(tài)，即未被引入或被剔除狀態(tài)。變量nx（）的值為0和非0，非0時(shí)為因子序號(hào)。設(shè)定一個(gè)變量nxsum，用于統(tǒng)計(jì)引入的因子數(shù)量。

引入和剔除因子，以及生成回歸方程的顯著性需要進(jìn)行檢驗(yàn)，這里是通過(guò)調(diào)用Excel文件進(jìn)行。在“F檢驗(yàn).xls”的工作簿中，其工作表“Sheet1”中的單元格A1用于獲取文本框Text1（1）中的篩選水平（α值），B1為分子自由度，這里固定取值為1;C1為分母自由度，取值代碼為Cells（1， 3） = mym - nxsum – 2;當(dāng)分子自由度、分母自由度及檢驗(yàn)水平（α值）確定的情況下，在D1中得到檢驗(yàn)引入變量時(shí)的臨界值，代碼為Cells（1， 4） = "= FINV（A1，B1，C1） "，設(shè)置變量Fcriti，以獲取D1中的值。

由于引入前兩個(gè)因子時(shí)不需作剔除檢驗(yàn)，當(dāng)引入第三個(gè)因子時(shí)再進(jìn)行剔除檢驗(yàn)[1]。因此，引入因子部分分兩個(gè)步驟進(jìn)行，第一步是看首先能否引進(jìn)兩個(gè)因子，如果第一次引入檢驗(yàn)都無(wú)法引進(jìn)因子或只能引進(jìn)少于三個(gè)因子，則無(wú)須進(jìn)行后面的操作。第二步當(dāng)引進(jìn)的因子數(shù)量能夠達(dá)到兩個(gè)以上，則需要進(jìn)行是否剔除的判斷。

第一步，判斷初次引進(jìn)因子的數(shù)量。在這一步判斷出能否引進(jìn)因子，或者能否引進(jìn)一個(gè)或兩個(gè)因子;如果能引進(jìn)，則是哪一個(gè)或哪兩個(gè)因子。

能否引進(jìn)因子通過(guò)F分布的統(tǒng)計(jì)量進(jìn)行檢驗(yàn)：

方差V（）值在程序中用變量myv（）貯存（下同），基于原始相關(guān)矩陣，得到各方差值后，找出其最大值。設(shè)定一個(gè)整型變量myk，用于記錄方差最大值所在變量是第幾列。

將最大方差值代入F檢驗(yàn)公式計(jì)算出相應(yīng)的F值（程序中用myf表示，下同）。如果myf>Fcriti，表示該因子可引進(jìn)，則nx（myk，1） = myk： nx（myk， 0） = 0，即第myk個(gè)因子可引進(jìn)，并將第myk個(gè)因子已引進(jìn)狀態(tài)值設(shè)為myk，未引進(jìn)狀態(tài)值設(shè)為0，然后對(duì)該列（myk列）進(jìn)行矩陣變換，以判斷下一個(gè)因子是否能被引入。如果myf

引入第一個(gè)因子后，判斷能否引入第二個(gè)因子前，要對(duì)原矩陣進(jìn)行一次變換。通過(guò)求解求逆緊湊變換方案（或叫高斯-亞當(dāng)變換、消去求逆緊湊方案）對(duì)原矩陣進(jìn)行一次變換，變換公式為：

假如剛開始已引進(jìn)了兩個(gè)因子，在引進(jìn)了第三個(gè)因子之后，進(jìn)入第二重循環(huán)的剔除因子判斷，如果能剔除一個(gè)因子，則針對(duì)該因子所在序列值，運(yùn)用公式（8）對(duì)矩陣進(jìn)行一次變換，繼續(xù)判斷有沒有因子可剔除，如此循環(huán)，直到?jīng)]有因子可剔除，跳出該循環(huán);然后再判斷是否有因子可引入。這時(shí)將出現(xiàn)兩種情況，一是能夠繼續(xù)引進(jìn)因子，然后又回到上面的剔除循環(huán)，二是不能再繼續(xù)引進(jìn)因子，即表示既沒有因子可引入，又沒有因子可剔除，則逐步回歸結(jié)束。

1.3 生成回歸方程

1.3.1 方程因子系數(shù)

在回歸方程中各因子系數(shù)b（）的計(jì)算公式為：

1.3.2 方程常數(shù)項(xiàng)

1.3.3 計(jì)算剩余標(biāo)準(zhǔn)差

剩余標(biāo)準(zhǔn)差的大小，反映了模型的預(yù)測(cè)精度或?qū)崪y(cè)值與回歸值的平均偏差，其值越小，說(shuō)明回歸效果越好，建立的模型越好。剩余標(biāo)準(zhǔn)差的計(jì)算公式為：

1.3.4 方程復(fù)相關(guān)系數(shù)

1.4 方程的顯著性檢驗(yàn)

1.4.1 用于檢驗(yàn)的F值

1.4.2 不同顯著性水平的F值

1.4.3 顯著性檢驗(yàn)結(jié)果

1.5 輸出回歸方程及擬合圖

1.5.1 輸出回歸方程

用字符串型變量myEqtnx獲取回歸方程的各因子及其系數(shù)，其中沒有包含常數(shù)項(xiàng)。

1.5.2 輸出擬合圖

1.5.2.1 計(jì)算模擬值

因變量的實(shí)測(cè)值為已知，回歸方程中各因子的系數(shù)及常數(shù)項(xiàng)也已在前面計(jì)算出來(lái)，現(xiàn)計(jì)算模擬值（在程序中用變量mySimu（）表示）。被引入因子的觀測(cè)值分別乘以其各自系數(shù)，并加上常數(shù)項(xiàng)，即得到模擬值。

1.5.2.2擬合圖的顯示

在名為“工作表”的Excel文件中，將因變量的模擬值和實(shí)測(cè)值分別貯存于工作表“擬合圖”的A列和B列中。將數(shù)據(jù)進(jìn)行整體寫入，以增加寫入速度，即設(shè)置一個(gè)中間過(guò)渡用的數(shù)組變量myary（），讀入因變量的模擬值和實(shí)測(cè)值;工作表“擬合圖”的A列和B列的相應(yīng)范圍取得myary值，程序代碼為：

以A列、B列中的數(shù)據(jù)為數(shù)據(jù)源作曲線圖，即xlApp.ActiveChart.SetSourceData Source：=.Range（"A" & 1 & "：B" & mym）， PlotBy：=xlColumns，復(fù)制該曲線圖，即.ChartObjects（"圖表 1"）.Copy，此時(shí)剪貼板中貯存了模擬值與實(shí)測(cè)值的擬合圖。程序的圖片框獲取剪貼板中的擬合圖，即Picture1.Picture = Clipboard.GetData，這樣在程序圖片框Picture1中顯示出了擬合圖（如圖1）。

2 結(jié)論和討論

1） 在程序設(shè)計(jì)中，矩陣中的數(shù)組變量如果采用三維及以上的數(shù)組，則程序運(yùn)行時(shí)會(huì)占用電腦較大的內(nèi)存空間，在數(shù)據(jù)量很大的情況下，如果電腦內(nèi)存較小或不足，程序運(yùn)行會(huì)出現(xiàn)錯(cuò)誤，因此在這種情況下，應(yīng)盡量使用維數(shù)較少的一維或二維數(shù)組。但一維數(shù)組雖然在運(yùn)行時(shí)占用電腦內(nèi)存空間較小，但程序設(shè)計(jì)起來(lái)會(huì)相對(duì)復(fù)雜一些，因此，本程序中矩陣數(shù)組變量采用的是二維數(shù)組。

2） 本程序從Excel中獲取用于檢驗(yàn)的臨界值，以判斷逐步回歸過(guò)程中自變量的引入和剔除，同時(shí)運(yùn)用Excel對(duì)生成的回歸方程進(jìn)行顯著性檢驗(yàn)以及對(duì)回歸擬合圖的生成等，進(jìn)一步簡(jiǎn)化了程序代碼，提高了編程效率。

3） 本程序的主要功能是對(duì)因變量和眾多自變量數(shù)據(jù)間進(jìn)行逐步回歸分析，程序力圖簡(jiǎn)便、易用，減少、簡(jiǎn)化一些煩瑣的操作選項(xiàng)，程序界面簡(jiǎn)潔，易于操作，實(shí)用性強(qiáng)。后期從實(shí)際工作出發(fā)，結(jié)合實(shí)際工作的需要，逐步增加數(shù)理統(tǒng)計(jì)的其他分析功能。

4） vb雖然是一個(gè)較好的編程工具，但其函數(shù)公式、繪制圖表等功能還不及excel的豐富和多樣，同時(shí)，Excel有時(shí)并不能較方便地實(shí)現(xiàn)一些具有一定專業(yè)性、針對(duì)性的統(tǒng)計(jì)功能，很多時(shí)候其完成一項(xiàng)統(tǒng)計(jì)操作步驟較為復(fù)雜，因此這里用vb來(lái)調(diào)用Excel，各取所長(zhǎng)，使操作步驟更簡(jiǎn)潔，運(yùn)行速度更快捷，從而提高工作效率。

5） 逐步回歸被應(yīng)用于眾多領(lǐng)域，鑒于其在氣象上也有較廣泛的應(yīng)用，本文通過(guò)VB結(jié)合Excel設(shè)計(jì)了逐步回歸程序，并提供了此方面的程序設(shè)計(jì)思路，同時(shí)也可為基層氣象工作者提供經(jīng)濟(jì)實(shí)用的逐步回歸分析程序。

參考文獻(xiàn)：

[1] 黃嘉佑.氣象統(tǒng)計(jì)分析與預(yù)報(bào)方法[M].北京：氣象出版社，2004.

[2] 施能.氣象科研與預(yù)報(bào)中的多元分析方法[M].北京.氣象出版社.2002.

[3] 何曉群，劉文卿.應(yīng)用回歸分析[M].北京.中國(guó)人民大學(xué)出版社.2001.

[4] 劉曉宇，孟軍.基于逐步回歸的黑龍江省煙葉產(chǎn)量預(yù)測(cè)[J].中國(guó)農(nóng)學(xué)通報(bào)，2012，28（7）：223-227.

[5] 楊廉偉，王會(huì)福，陳將贊，等.浙江省天臺(tái)縣晚稻褐飛虱發(fā)生關(guān)鍵因子分析與逐步回歸預(yù)測(cè)[J].中國(guó)農(nóng)學(xué)通報(bào)，2010，26（12）：227-232.

[6] 孟力力，楊其長(zhǎng).MATLAB和VB在溫室環(huán)境模型構(gòu)建中的混合編程研究[J].中國(guó)農(nóng)學(xué)通報(bào)，2012，28（6）：262-268.

[7] 陳靜，王昌斌，蔣萬(wàn)祥.2010年春季微山湖葉綠素a及初級(jí)生產(chǎn)力研究[J].中國(guó)農(nóng)學(xué)通報(bào)，2011，27（23）：265-268.

[8] 趙丹丹，翟石艷.1951—2012年河南省氣候變化對(duì)冬小麥和玉米產(chǎn)量的影響[J].中國(guó)農(nóng)學(xué)通報(bào)，2015，31（29）：152-157.

[9] 姜燕敏，金志鳳，李松平，等.浙南春茶開采前后氣象條件分析及開采期預(yù)報(bào)[J].中國(guó)農(nóng)業(yè)氣象，2015，36（02）：212?219.

[10] 張利華，任曙霞，張永強(qiáng)，等.梨樹始花期預(yù)報(bào)[J].氣象科技，2012，40（3）：485-488.

【通聯(lián)編輯：唐一東】