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.

【通聯編輯：唐一東】