吳良忠，韓波，韋華

（1. 北海市環(huán)境信息中心，廣西北海536000；2. 北海市環(huán)境監(jiān)測中心站，廣西北海536000）

水質灰色預測模型在VBA中的實現(xiàn)

吳良忠1，韓波2，韋華2

（1. 北海市環(huán)境信息中心，廣西北海536000；2. 北海市環(huán)境監(jiān)測中心站，廣西北海536000）

灰色預測是環(huán)境污染預測應用相當廣泛的方法。根據(jù)文獻報道，基于Matlab和Excel實現(xiàn)灰色預測中存在著占用內(nèi)存、單元格填埋計算公式等等的不足，因此提出了一種基于VBA在Excel下編寫宏程序輕松實現(xiàn)灰色預測的方法，并將其運用到北海市地表水污染物預測。結果表明，本程序簡短，操作方便，計算結果準確可靠，徹底把用戶從繁瑣的手工操作中解放出來。

灰色預測；VBA；Excel；環(huán)境污染；水質預測

灰色預測模型GM（1，1）是以時間序列數(shù)據(jù)為基礎，通過對無規(guī)律的原始數(shù)列進行轉換，建立有規(guī)律的生成數(shù)據(jù)列的回歸方程，并應用該方程對事物的動態(tài)發(fā)展趨勢進行預測的一種較為常見的分析方法[1]。由于數(shù)學方程參數(shù)涉及到矩陣運算，郝文輝、曹玉珍等人[2，3]提出基于Matlab矩陣數(shù)學軟件來實現(xiàn)，筆者認為有點“大材小用”，而且此軟件占用較大的計算機內(nèi)存。嚴明、陳青山等人[4~6]提出了基于Excel的實現(xiàn)方法，將參數(shù)矩陣展開為數(shù)學公式，然后將公式的各項填埋到相應的單元格。此法存在兩個主要問題：其一，單元格輸入計算公式不僅占用內(nèi)存，而且應用中單元格公式易被刪除；其二，通用性和可讀性差，如當系列數(shù)據(jù)樣本增加或減少變化后，還得重新編寫或修改單元格計算公式。本研究通過Excel內(nèi)置VBA編程環(huán)境進行程序開發(fā)，并利用Excel中的INTERCEPT()和SLOPE()兩個函數(shù)簡單實現(xiàn)灰色污染預測。

1 GB（1，1）灰色預測模型

GM（1，1）模型是灰色預測的核心，它是單個變量預測的一階微分方程模型，其離散時間響應函數(shù)近似呈指數(shù)規(guī)律。建立GM（1，1）模型的方法是：

設X(0)={X(0)（1），X(0)（2），…，X(0)（n）}為原始非負時間序列，X(1)（t）為累加生成序列，即：

GM（1，1）模型的白化微分方程為：

式（2）中，a為待辨識參數(shù)，亦稱發(fā)展系數(shù)；u為待辨識內(nèi)生變量，亦稱灰作用量。設待辨識向量），按最小二乘法求得，式中：

X(1)（t+1）為所得的累加的預測值，將預測值還原即為：

2 運用Excel VBA編制計算程序

2.1 Excel計算表設計

新建一個工作薄，把工作薄的“sheet1”工作表作為數(shù)據(jù)輸入和計算的數(shù)據(jù)表，如表1所示。表1中，“sheet1”工作表的第一列設計為數(shù)據(jù)輸入和計算結果的屬性，其中第一行和第二行為用戶輸入的序列數(shù)據(jù)，相應的數(shù)據(jù)屬性為“序列號”和“x(0)”；工作表其他各行則為需要計算的各項：x(1)為累加生成，(0)為累加生成的平均值，(1)（t+1）為累加生成的預測值，(0)（t+1）為累加生

于是可得到灰色預測的離散時間響應函數(shù)為：成預測值的還原值；u和a分別為內(nèi)生變量和參數(shù)，然后在工作表中添加一個按鈕，并在按鈕名稱屬性中取名“灰色預測”。最后將該工作薄保存為名為“灰色預測”的Excel文件。

表1 灰色預測設計表

2.2 VBA程序設計

要完成上述建好的工作表的計算內(nèi)容且使計算程序具有通用性（與樣本數(shù)量大小無關），程序設計是關鍵。這里程序設計采用VBA宏語言編寫，因為這種方法比在Excel電子表格內(nèi)編程靈活，而且程序能得到有效的保護。程序設計首先在Excel工具菜單中打開Visual Baics編輯器，然后在VBE屬性窗口中插入一個模塊，在模塊代碼窗口內(nèi)依次輸入截圖1所示的代碼。

圖1 灰色預測代碼清單

3 實例應用

北海市主要河流為南流江，歷年來主要污染物為溶解氧、高錳酸鉀指數(shù)和氨氮等耗氧指標。以北海市地表水2001-2010年溶解氧、高錳酸鉀指數(shù)及氨氮的年際統(tǒng)計數(shù)據(jù)進行灰色預測，年際統(tǒng)計數(shù)據(jù)如表2所示。

其程序計算步驟如下：

打開已建好的上述程序名為“灰色預測”的Excel軟件；

表2 北海市地表水2001-2010年溶解氧、高錳酸鉀指數(shù)及氨氮統(tǒng)計數(shù)據(jù)

然后將污染物年度數(shù)據(jù)分別輸入表1所示的第一行和第二行；

單擊“灰色預測”按鈕；

按上述步驟操作分別計算溶解氧、高錳酸鉀指數(shù)和氨氮，得出污染物預測模型為：

溶解氧：

高錳酸鉀指數(shù)：

氨氮：

程序根據(jù)上述公式分別計算出的預測值和殘差的計算結果如表3所示。

表3 北海市地表水2001-2011年水質指標的預測值與實際值比較結果

由表3的殘差可知，各污染物的殘差很小，預測模型預測結果精度較高，且與實際值吻合較好。故對2011年北海市地表水溶解氧、高錳酸鉀指數(shù)和氨氮進行預測，得到的預測值分別為6.400 mg/L、3.798 mg/L、0.415 mg/L是可靠的。同樣運用上述污染物預測模型可分別預測2012年、2013年等各年污染物的預測值。

4 結果與討論

與文獻[2～5]中的計算程序相比，本程序不僅簡短，節(jié)省內(nèi)存，占用機時少，而且還具有如下特點：

（1）操作簡單，運算快速。實例的三個污染物的數(shù)據(jù)，只需要輸入時間序列數(shù)據(jù)，然后單擊“灰色預測”按鍵，一步就能得到準確的計算結果。

（2）尤為適于大批量數(shù)據(jù)的處理。程序不僅可以對一種污染物進行灰色預測，而且采用相同的操作可進行多種污染物的預測，不需要修改程序。改革了在工作表中重復“填埋單元格”的煩瑣操作的計算模式。

（3）具有通用性與普及性。程序清晰，易掌握、易改寫，只要用戶將上述圖1的程序代碼寫入自己的Excel軟件VBA編程環(huán)境中，就能夠進行灰色預測，還能在本程序代碼的基礎上對于一元線和非線性的計算問題進行擴展。

（4）開拓性。本研究應用VBA編程技術為Excel數(shù)據(jù)處理提供了全新的數(shù)據(jù)處理方法，目前在環(huán)境監(jiān)測數(shù)據(jù)處理的報導中不多見，這種編程方式為開拓Excel潛在數(shù)據(jù)處理功能開辟了一種嶄新的設計思路，并為Excel開發(fā)其他數(shù)據(jù)處理系統(tǒng)展現(xiàn)了廣闊前景。

[1]鄧聚龍.灰色預測與決策[M].武漢：華中理工大學出版社，1990.

[2]郝文輝，任改娟，貢長青．基于MATLAB的灰色模型對秦皇島市區(qū)地下水水位的預測[J].中國環(huán)境管理干部學院學報，2010，20（2）：61-64.

[3]曹玉珍，莫翠云．基于MATLAB的灰色模型在廣州市降塵預測中的應用[J].中國環(huán)境監(jiān)測，2006，22（5）：54-56.

[4]嚴明，廖鐵軍．基于Excel的灰色預測模型在土地利用規(guī)劃中的應用[J].安徽農(nóng)業(yè)科學，2007，36（12）：3627-3628.

[5]陳青山,王聲湧．應用Excel完成性病GM模型的預測和評價[J].疾病控制雜志，2003，7（5）：451-453.

[6]李超.灰色預測：EXCEL/VBA編程輕松實現(xiàn)[J].統(tǒng)計與信息論壇，2004，19（2）：72-75.

Realizing Water Quality Grey Forecasting Model in VBA

Wu Liangzhong1，Han Bo2，Wei Hua2
（1.Beihai Environmental Information Center,Beihai Guangxi 536000,China；
2.Beihai Environmental Monitoring Center,Beihai Guangxi 536000,China）

Grey Prediction model is widely used for environmental pollution forecast.According to current reports,the Grey Prediction model based on Matlab and Excel exists the shortcomings such as taking lot of memory and leaving off the formula in the cell.In this paper,the macro-program based on VBA in Excel was built to achieve the Grey Prediction model easily,and it was used to forecast the contaminants in surface water in BeiHai.It showed that the program was short and easy to operate,and the calculation results were accurate and reliable.The program would relieve the users from their tedious manual operation.

Grey Prediction；VBA；Excel；Environment Pollution；Water Quality Prediction

X832

：A

：1008-813X（2013）04-0045-03

2013-06-05

吳良忠（1971-），男，廣西北海人，畢業(yè)于廣西農(nóng)業(yè)大學環(huán)境保護專業(yè)，環(huán)境影響評價工程師，主要從事環(huán)境影響評價工作。

10.3969/j.issn.1008-813X.2013.04.013