白愛(ài)民
(云南省環(huán)境監(jiān)測(cè)中心站,云南昆明650034)
近20年來(lái),隨著經(jīng)濟(jì)和社會(huì)的發(fā)展,江河湖海的富營(yíng)養(yǎng)化也日益嚴(yán)重。據(jù)調(diào)查,我國(guó)有大小湖泊24800多個(gè),而處于富營(yíng)養(yǎng)化的占湖泊總量的65%,處于中營(yíng)養(yǎng)化轉(zhuǎn)化富營(yíng)養(yǎng)化的湖泊占湖泊總量的20%[1]。水體富營(yíng)養(yǎng)化不僅使當(dāng)?shù)仫嬘盟吹厥艿酵{,水質(zhì)普遍惡化,而且也制約了當(dāng)?shù)氐纳鐣?huì)經(jīng)濟(jì)的可持續(xù)發(fā)展和人民的生活[2],因此,對(duì)湖泊、水庫(kù)富營(yíng)養(yǎng)化程度做常規(guī)性評(píng)價(jià)對(duì)掌握水體富營(yíng)養(yǎng)化及變化情況十分重要,也是環(huán)境監(jiān)測(cè)部門(mén)的常規(guī)例行工作之一。
水體富營(yíng)養(yǎng)化(eutrophication)是指水體中含有過(guò)多的氮、磷等可溶性營(yíng)養(yǎng)物質(zhì),導(dǎo)致水體中的藻類(lèi)及其他浮游生物迅速繁殖,水體溶氧量下降,水質(zhì)惡化,魚(yú)蝦等生物大量死亡的現(xiàn)象[3]。湖泊、水庫(kù)富營(yíng)養(yǎng)化程度的評(píng)價(jià)一般采用綜合營(yíng)養(yǎng)狀態(tài)指數(shù)法。由于計(jì)算公式較復(fù)雜和麻煩,不僅工作量大,而且極易出現(xiàn)錯(cuò)誤[4]。
Microsoft EXCEL是使用最廣泛的電子表格軟件之一,它在數(shù)據(jù)綜合管理和分析方面具有功能強(qiáng)大、技術(shù)先進(jìn)、使用方便等特點(diǎn)。但很多工作人員使用EXCEL仍停留在初級(jí)水平,找出一種簡(jiǎn)單、易用的求解湖 (庫(kù))富營(yíng)養(yǎng)化指數(shù)方法是很有必要的。
1.1 評(píng)價(jià)指標(biāo)
湖泊 (水庫(kù))富營(yíng)養(yǎng)化狀況評(píng)價(jià)指標(biāo)有五項(xiàng):葉綠素a(chla)、總磷(TP)、總氮(TN)、透明度(SD)、高錳酸鹽指數(shù)(CODMn)。
1.2 計(jì)算公式
湖泊 (水庫(kù))富營(yíng)養(yǎng)化狀況評(píng)價(jià)方法是綜合營(yíng)養(yǎng)狀態(tài)指數(shù)法,其計(jì)算公式為:
式中:TLI(∑)-綜合營(yíng)養(yǎng)狀態(tài)指數(shù);
Wj-第j種參數(shù)的營(yíng)養(yǎng)狀態(tài)指數(shù)的相關(guān)權(quán)重;
TLI(j)-代表第j種參數(shù)的營(yíng)養(yǎng)狀態(tài)指數(shù)。
以chla作為基準(zhǔn)參數(shù),則第j種參數(shù)的歸一化的相關(guān)權(quán)重計(jì)算公式為:
式中:rij-第j種參數(shù)與基準(zhǔn)參數(shù)chla的相關(guān)系數(shù);
m-評(píng)價(jià)參數(shù)的個(gè)數(shù)。
中國(guó)湖泊(水庫(kù))的chla與其它參數(shù)之間的相關(guān)關(guān)系rij及見(jiàn)表1。
表1 中國(guó)湖泊(水庫(kù))部分參數(shù)與chla的相關(guān)關(guān)系rij及值
表1 中國(guó)湖泊(水庫(kù))部分參數(shù)與chla的相關(guān)關(guān)系rij及值
注:引自金相燦等著 《中國(guó)湖泊環(huán)境》,表中rij來(lái)源于中國(guó)26個(gè)主要湖泊調(diào)查數(shù)據(jù)的計(jì)算結(jié)果。
參數(shù) chla TP TN SD CODMn rij 1 0.84 0.82 -0.83 0.83 1 0.7056 0.6724 0.6889 0.6889 r2 ij
各項(xiàng)目營(yíng)養(yǎng)狀態(tài)指數(shù)計(jì)算:
(1)TLI(chla)=10×(2.5+1.086×ln(chla))
(2)TLI(TP)=10×(9.436+1.624×ln(TP))
(3)TLI(TN)=10×(5.453+1.694×ln(TN))
(4)TLI(SD)=10×(5.118-1.94×ln(SD))
(5)TLI(CODMn)=10×(0.109+2.661× ln(CODMn))
式中:葉綠素a(chla)單位為mg/m3,透明度(SD)單位為m;其它指標(biāo)單位均為mg/L。1.3 湖泊(水庫(kù))營(yíng)養(yǎng)狀態(tài)分級(jí)
采用0~100的一系列連續(xù)數(shù)字對(duì)湖泊(水庫(kù))營(yíng)養(yǎng)狀態(tài)進(jìn)行分級(jí):
TLI(∑)<30 貧營(yíng)養(yǎng)(Oligotropher);
30≤TLI(∑)≤50 中營(yíng)養(yǎng)(Mesotropher);
TLI(∑)>50 富營(yíng)養(yǎng)(Eutropher);
50<TLI(∑)≤60 輕度富營(yíng)養(yǎng)(light eutropher);
60<TLI(∑)≤70 中度富營(yíng)養(yǎng)(Middle eutropher);
TLI(∑)>70 重度富營(yíng)養(yǎng)(Hyper eutropher)。
在同一營(yíng)養(yǎng)狀態(tài)下,指數(shù)值越高,其營(yíng)養(yǎng)程度越重。
EXCEL有強(qiáng)大的函數(shù)庫(kù),內(nèi)置函數(shù)使用也很方便,不過(guò)EXCEL的內(nèi)置函數(shù)常常不能滿足具有特殊性和專(zhuān)業(yè)性的計(jì)算任務(wù),這時(shí),就可以通過(guò)定義自己的函數(shù)來(lái)解決問(wèn)題。
EXCEL自定義函數(shù)是使用者預(yù)先定義,可執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。自定義函數(shù)是用通用應(yīng)用程序腳本編程語(yǔ)言VBA(Visual Basic for Application)創(chuàng)建的。自定義函數(shù)和內(nèi)置函數(shù)一樣,由函數(shù)名、括號(hào)及括號(hào)內(nèi)的參數(shù)組成。一個(gè)函數(shù)只有唯一的一個(gè)名稱(chēng),它決定了函數(shù)的功能和用途。參數(shù)是函數(shù)中最復(fù)雜的組成部分,它規(guī)定了函數(shù)的運(yùn)算對(duì)象、順序或結(jié)構(gòu)等,使用戶(hù)可以對(duì)某些單元格或區(qū)域進(jìn)行處理。
使用自定義函數(shù)還有下面優(yōu)點(diǎn):
(1)自定義函數(shù)像EXCEL內(nèi)置函數(shù)一樣簡(jiǎn)單易用;
(2)自定義函數(shù)能滿足具有特殊性和專(zhuān)業(yè)性計(jì)算任務(wù)的需要,功能比內(nèi)置函數(shù)更強(qiáng)大,使用更靈活;
(3)有多種方法來(lái)輸出和調(diào)用自定義函數(shù),易于在同行業(yè)和有同樣需求的部門(mén)推廣使用;
(4)自定義函數(shù)可包含多個(gè)參數(shù),函數(shù)名和參數(shù)名可用漢字或英文字母表示,增加報(bào)表的可讀性。
3.1 編寫(xiě)自定義函數(shù)
(1)啟動(dòng)EXCEL,選擇菜單“工具→宏→Visual Basic編輯器”命令(或按下鍵盤(pán)Alt+F11組合鍵),打開(kāi)Visual Basic窗口。
(2)選擇菜單 “插入→模塊”命令,于是得到“模塊1”,將“模塊1”改名為“Eutro模塊”(見(jiàn)圖1左側(cè)),(取富營(yíng)養(yǎng)化的英文前5個(gè)字符作為模塊名。在代碼窗口完成自定義函數(shù)的編寫(xiě)(見(jiàn)圖1右側(cè)。本例中只保留了自定義函數(shù)的格式,省略了代碼部分)。
(3)自定義函數(shù)的編寫(xiě)由“Function”開(kāi)始,后跟函數(shù)名和括在括號(hào)中參數(shù),并可明確自定義函數(shù)返回值的數(shù)據(jù)類(lèi)型,以“End Function”語(yǔ)句結(jié)束。開(kāi)始語(yǔ)句和結(jié)束語(yǔ)句之間 (圖1中省略部分)是實(shí)現(xiàn)自定義函數(shù)功能的代碼或說(shuō)明。本例中兩個(gè)自定義函數(shù)名為“EutroIndex”(富營(yíng)養(yǎng)化指數(shù))和“EutroState”(富營(yíng)養(yǎng)化程度),均有5個(gè)參數(shù):Chl,TP,TN,Tr和COD,參數(shù)名與湖(庫(kù))富營(yíng)養(yǎng)化狀況評(píng)價(jià)指標(biāo)中的英文符號(hào)基本一致,方便記憶,使用時(shí)也不易弄錯(cuò)順序。
3.2 保存自定義函數(shù)
完成 “富營(yíng)養(yǎng)化指數(shù)”和 “富營(yíng)養(yǎng)化程度”自定義函數(shù)的編寫(xiě)與調(diào)試后,為方便推廣或在以后的工作中使用,可將模塊以文件的形式保存。方法:鼠標(biāo)右鍵點(diǎn)擊該模塊——選擇導(dǎo)出文件——選擇擬存放文件的目錄并輸入文件名 (本例使用缺省文件名“Eutro模塊.bas”)后保存即可。見(jiàn)圖2和圖3。
3.3 導(dǎo)入自定義函數(shù)
當(dāng)需要在EXCEL數(shù)據(jù)報(bào)表中使用自定義函數(shù)時(shí),須先將自定義函數(shù)模塊導(dǎo)入。方法:打開(kāi)數(shù)據(jù)報(bào)表——按Alt+F11(組合功能鍵)——在Visual Basic窗口的文件菜單中選擇“導(dǎo)入文件”(見(jiàn)圖4)——選擇模塊保存的文件夾和文件名——“打開(kāi)”該文件即完成模塊的導(dǎo)入。
3.4 使用自定義函數(shù)
自定義函數(shù)導(dǎo)入到EXCEL工作薄后,就可像內(nèi)置函數(shù)一樣使用了。在 “AD9”單元格輸入計(jì)算富營(yíng)養(yǎng)化指數(shù)的公式“=eutroindex(AA9,I9,J9,Z9,E9)”(見(jiàn)圖5的公式欄),公式中引用的單元格按順序分別代表了湖庫(kù)1的參數(shù):葉綠素a、總磷、總氮、透明度和高錳酸鹽指數(shù) (為使自定義函數(shù)的使用更簡(jiǎn)明,本例隱藏了EXCEL工作表中無(wú)關(guān)的行和列)。同理,在 “AE9”單元格輸入描述富營(yíng)養(yǎng)化程度的公式“=eutrostate(AA9,I9,J9,Z9,E9)”,就可獲得第一個(gè)湖庫(kù)的評(píng)價(jià)結(jié)果。通過(guò)公式復(fù)制粘貼 (或句柄拖動(dòng)方式)快速輸入公式后,整張報(bào)表的富營(yíng)養(yǎng)化評(píng)價(jià)完成(見(jiàn)圖5)。
本例中,使用自定義函數(shù)計(jì)算出了富營(yíng)養(yǎng)化指數(shù),對(duì)富營(yíng)養(yǎng)化程度給出了文字描述。在報(bào)表中,數(shù)據(jù)缺測(cè)時(shí)用 “-”表示,當(dāng)參與計(jì)算的參數(shù)出現(xiàn)缺測(cè)時(shí),富營(yíng)養(yǎng)化指數(shù)用紅色的 “(100)”(負(fù)一百,)表示,富營(yíng)養(yǎng)化程度用 “缺項(xiàng)”描述。
3.5 自定義函數(shù)的推廣
如果想要其它人員 (或在其它計(jì)算機(jī)上)使用自定義函數(shù),只要把保存的模塊文件 (本例為“Eutro模塊.bas”)發(fā)送給推廣對(duì)象,再按上面敘述的方法,將模塊導(dǎo)入到擬評(píng)價(jià)富營(yíng)養(yǎng)化程度的EXCEL文件中即可。當(dāng)然,也可使用加載宏文件的方式來(lái)保存和使用自定義函數(shù),但使用方法更復(fù)雜一些。
監(jiān)測(cè)和評(píng)價(jià)湖 (庫(kù))的富營(yíng)養(yǎng)化程度是環(huán)境監(jiān)測(cè)部門(mén)的常規(guī)例行工作之一。評(píng)價(jià)工作涉及的指標(biāo)多,計(jì)算公式復(fù)雜。EXCEL功能非常強(qiáng)大[5],但很多使用者只會(huì)分步計(jì)算,工作量很大,遠(yuǎn)沒(méi)有體現(xiàn)出EXCEL的優(yōu)越性。本文介紹了評(píng)價(jià)湖(庫(kù))的富營(yíng)養(yǎng)化程度的Excel自定義函數(shù),以及函數(shù)的編寫(xiě)、保存、導(dǎo)入與使用方法。自定義函數(shù)能夠?qū)崿F(xiàn)復(fù)雜的數(shù)據(jù)處理與分析,象EXCEL的內(nèi)置函數(shù)一樣簡(jiǎn)單直觀,使用方便,且易于推廣。利用EXCEL的引用功能,可方便快捷地完成大量重復(fù)計(jì)算的公式輸入 [6],快速獲得多個(gè)湖 (庫(kù))的富營(yíng)養(yǎng)化評(píng)價(jià)結(jié)果。
[1]劉光釗.水體富營(yíng)養(yǎng)化及其藻害 [M].北京:中國(guó)環(huán)境科學(xué)出版社,2005.
[2]劉慧.水體富營(yíng)養(yǎng)化及其防治[J].應(yīng)用能源技術(shù),2006,(5).
[3]謝小東.水體富營(yíng)養(yǎng)化及其治理措施 [J].城市建設(shè)理論研究,2013,(21).
[4]鄭婷婷.EXCEL在湖泊\水庫(kù)水質(zhì)富營(yíng)養(yǎng)化程度評(píng)價(jià)中的應(yīng)用[J].城市建設(shè)理論研究,2013,(43).
[5]張迎新.EXCEL2003函數(shù)應(yīng)用完全手冊(cè)[M].2004
[6]白愛(ài)民.EXCEL與底泥數(shù)據(jù)的分析[J].環(huán)境科學(xué)導(dǎo)刊,2010,29(5):109-112.