吳良忠，韓波，韋華

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

水質(zhì)灰色預(yù)測(cè)模型在VBA中的實(shí)現(xiàn)

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

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

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

灰色預(yù)測(cè)；VBA；Excel；環(huán)境污染；水質(zhì)預(yù)測(cè)

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

1 GB（1，1）灰色預(yù)測(cè)模型

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

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

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

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

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

2 運(yùn)用Excel VBA編制計(jì)算程序

2.1 Excel計(jì)算表設(shè)計(jì)

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

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

表1 灰色預(yù)測(cè)設(shè)計(jì)表

2.2 VBA程序設(shè)計(jì)

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

圖1 灰色預(yù)測(cè)代碼清單

3 實(shí)例應(yīng)用

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

其程序計(jì)算步驟如下：

打開(kāi)已建好的上述程序名為“灰色預(yù)測(cè)”的Excel軟件；

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

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

單擊“灰色預(yù)測(cè)”按鈕；

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

溶解氧：

高錳酸鉀指數(shù)：

氨氮：

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

表3 北海市地表水2001-2011年水質(zhì)指標(biāo)的預(yù)測(cè)值與實(shí)際值比較結(jié)果

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

4 結(jié)果與討論

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

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

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

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

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

[1]鄧聚龍.灰色預(yù)測(cè)與決策[M].武漢：華中理工大學(xué)出版社，1990.

[2]郝文輝，任改娟，貢長(zhǎng)青．基于MATLAB的灰色模型對(duì)秦皇島市區(qū)地下水水位的預(yù)測(cè)[J].中國(guó)環(huán)境管理干部學(xué)院學(xué)報(bào)，2010，20（2）：61-64.

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

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

[5]陳青山,王聲湧．應(yīng)用Excel完成性病GM模型的預(yù)測(cè)和評(píng)價(jià)[J].疾病控制雜志，2003，7（5）：451-453.

[6]李超.灰色預(yù)測(cè)：EXCEL/VBA編程輕松實(shí)現(xiàn)[J].統(tǒng)計(jì)與信息論壇，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è)大學(xué)環(huán)境保護(hù)專業(yè)，環(huán)境影響評(píng)價(jià)工程師，主要從事環(huán)境影響評(píng)價(jià)工作。

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