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

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

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

中圖分類號：TP18? ? ? ? 文獻標(biāo)識碼：A

文章編號：1009-3044（2019）16-0231-05

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

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

1.1 Excel工具文件

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

1.2逐步回歸程序具體思路

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

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

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

按照輸入習(xí)慣，在“輸入數(shù)據(jù)”工作表中，將第1列輸入為因變量數(shù)據(jù)，在其他列輸入自變量數(shù)據(jù)。在計算相關(guān)系數(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ù)矩陣。

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

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

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

1.2.3 因子篩選

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

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

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

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

能否引進因子通過F分布的統(tǒng)計量進行檢驗：

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

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

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

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

1.3 生成回歸方程

1.3.1 方程因子系數(shù)

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

1.3.2 方程常數(shù)項

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

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

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

1.4 方程的顯著性檢驗

1.4.1 用于檢驗的F值

1.4.2 不同顯著性水平的F值

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

1.5 輸出回歸方程及擬合圖

1.5.1 輸出回歸方程

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

1.5.2 輸出擬合圖

1.5.2.1 計算模擬值

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

1.5.2.2擬合圖的顯示

在名為“工作表”的Excel文件中，將因變量的模擬值和實測值分別貯存于工作表“擬合圖”的A列和B列中。將數(shù)據(jù)進行整體寫入，以增加寫入速度，即設(shè)置一個中間過渡用的數(shù)組變量myary（），讀入因變量的模擬值和實測值;工作表“擬合圖”的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，此時剪貼板中貯存了模擬值與實測值的擬合圖。程序的圖片框獲取剪貼板中的擬合圖，即Picture1.Picture = Clipboard.GetData，這樣在程序圖片框Picture1中顯示出了擬合圖（如圖1）。

2 結(jié)論和討論

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

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

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

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

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

參考文獻：

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

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

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

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

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

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

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

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

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

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

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