白愛(ài)民

（云南省環(huán)境監(jiān)測(cè)中心站，云南 昆明650034）

實(shí)現(xiàn)地表水環(huán)境質(zhì)量評(píng)價(jià)的一種方法
——使用 EXCEL自定義函數(shù)的新思路

白愛(ài)民

（云南省環(huán)境監(jiān)測(cè)中心站，云南 昆明650034）

介紹了在 EXCEL環(huán)境下常用的評(píng)價(jià)地表水環(huán)境質(zhì)量的方法，通過(guò)比較，“帶引用參數(shù)的 Excel自定義函數(shù)”集合了多個(gè)功能基本相同的自定義函數(shù)于一身，不僅能夠?qū)崿F(xiàn)復(fù)雜的數(shù)據(jù)處理與分析，還能象 EXCEL的內(nèi)置函數(shù)一樣使用方便，能進(jìn)一步提高工作效率。

地表水；環(huán)境質(zhì)量；評(píng)價(jià)；方法；EXCEL；自定義函數(shù)；引用參數(shù)

水環(huán)境質(zhì)量指水環(huán)境對(duì)人群的生存和繁衍以及社會(huì)經(jīng)濟(jì)發(fā)展的適宜程度，通常指水環(huán)境遭受污染的程度［1］。地表水環(huán)境質(zhì)量評(píng)價(jià)是環(huán)境監(jiān)測(cè)部門(mén)的常規(guī)任務(wù)之一。地表水環(huán)境質(zhì)量評(píng)價(jià)是將各項(xiàng)監(jiān)測(cè)指標(biāo)的監(jiān)測(cè)值與對(duì)應(yīng)的國(guó)家標(biāo)準(zhǔn)做比較判別，綜合后獲得評(píng)價(jià)結(jié)果，再與該水體的水質(zhì)功能區(qū)類(lèi)別做比較，從而判斷環(huán)境質(zhì)量的優(yōu)劣、變化和達(dá)標(biāo)與否。整個(gè)評(píng)價(jià)過(guò)程中，將各指標(biāo)的監(jiān)測(cè)值與對(duì)應(yīng)國(guó)家標(biāo)準(zhǔn)做判別是基礎(chǔ)，快速且準(zhǔn)確地獲得各項(xiàng)指標(biāo)的評(píng)價(jià)結(jié)果十分重要。

Microsoft EXCEL是使用最廣泛的電子表格軟件之一，它在數(shù)據(jù)綜合管理和分析方面具有功能強(qiáng)大、技術(shù)先進(jìn)、使用方便等特點(diǎn)［2］。很多工作人員使用 EXCEL仍停留在初級(jí)水平，對(duì)完成監(jiān)測(cè)指標(biāo)多、分類(lèi)判別麻煩的綜合評(píng)價(jià)，其工作量仍然很大，且極易出現(xiàn)錯(cuò)誤。找出一種簡(jiǎn)單、易用的地表水環(huán)境質(zhì)量評(píng)價(jià)方法是很有必要的。

1 地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)簡(jiǎn)介［3］

《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn) （GB3838－2002）》（下稱(chēng) 《標(biāo)準(zhǔn)》）是我國(guó)為加強(qiáng)地表水環(huán)境管理、防治水環(huán)境污染、保障人體健康實(shí)施的國(guó)家環(huán)境質(zhì)量標(biāo)準(zhǔn)?！稑?biāo)準(zhǔn)》按照地表水的使用功能將水質(zhì)劃分為五類(lèi)。此外，《標(biāo)準(zhǔn)》規(guī)定了水質(zhì)項(xiàng)目及標(biāo)準(zhǔn)值、水質(zhì)評(píng)價(jià)、水質(zhì)項(xiàng)目的分析方法以及標(biāo)準(zhǔn)的實(shí)施與監(jiān)督。《標(biāo)準(zhǔn)》的適用范圍包括中華人民共和國(guó)領(lǐng)域內(nèi)江河、湖泊、運(yùn)河、渠道、水庫(kù)等具有使用功能的地表水水域。

《標(biāo)準(zhǔn)》共涉及項(xiàng)目（即描述水質(zhì)狀況的監(jiān)測(cè)指標(biāo)）共計(jì)109項(xiàng)，其中地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)基本項(xiàng)目24項(xiàng)，集中式生活飲用水地表水源地補(bǔ)充項(xiàng)目5項(xiàng)，集中式生活飲用水地表水源地特定項(xiàng)目80項(xiàng)。

2 地表水環(huán)境質(zhì)量評(píng)價(jià)方法

目前，常用的地表水 （河流、湖庫(kù)）環(huán)境質(zhì)量評(píng)價(jià)采用單因子評(píng)價(jià)法，根據(jù)評(píng)價(jià)時(shí)段內(nèi)該監(jiān)測(cè)點(diǎn)參評(píng)的指標(biāo)中類(lèi)別最高的一項(xiàng)來(lái)確定。對(duì)單個(gè)監(jiān)測(cè)點(diǎn)，直接評(píng)價(jià)；當(dāng)河流、流域 （水系）、湖庫(kù)有多個(gè)監(jiān)測(cè)點(diǎn)位時(shí)，先計(jì)算這些點(diǎn)位中各評(píng)價(jià)指標(biāo)濃度的算術(shù)平均值，再行評(píng)價(jià)。評(píng)價(jià)項(xiàng)目的類(lèi)別是根據(jù)評(píng)價(jià)指標(biāo)的濃度值，對(duì)照 《標(biāo)準(zhǔn)》的項(xiàng)目標(biāo)準(zhǔn)限值來(lái)確定的 （見(jiàn)表1）。

《標(biāo)準(zhǔn)》將地表水分為五大類(lèi)，即每項(xiàng)監(jiān)測(cè)值經(jīng)判別后可能會(huì)在一類(lèi)、二類(lèi)、三類(lèi)、四類(lèi)、五類(lèi)或超五類(lèi)中的一種 （分別用 “Ⅰ”、“Ⅱ”、“Ⅲ”、“Ⅳ”、“Ⅴ”或 “＞Ⅴ”表示）。完成評(píng)價(jià)獲得水體的水質(zhì)類(lèi)別后，可對(duì)該水體的水質(zhì)做定性評(píng)價(jià)分級(jí)。水質(zhì)類(lèi)別與水質(zhì)定性評(píng)價(jià)分級(jí)的對(duì)應(yīng)關(guān)系見(jiàn)表2。

3 自定義函數(shù)的概念與功能

Excel提供了大量的內(nèi)置函數(shù)，在數(shù)據(jù)處理和信息化工作中起到了巨大的作用。通過(guò)多個(gè)內(nèi)置函數(shù)的組合，可完成更為復(fù)雜的計(jì)算和分析。但是，內(nèi)置函數(shù)組合使得公式變得冗長(zhǎng)，難理解，還易出錯(cuò)。使用自定義函數(shù)能彌補(bǔ)內(nèi)置函數(shù)組合之不足。

EXCEL的自定義函數(shù)功能是 MicroSoft Office先進(jìn)性的具體體現(xiàn)之一。先通過(guò) VBA完成自定義函數(shù)的編寫(xiě)，再在EXCEL中導(dǎo)入或加載，就可象EXCEL的內(nèi)置函數(shù)一樣使用自定義函數(shù)了。自定義函數(shù)能夠?qū)崿F(xiàn)復(fù)雜的數(shù)據(jù)處理與分析，使用象內(nèi)置函數(shù)一樣簡(jiǎn)單。

4 自定義函數(shù)的編寫(xiě)與使用

下面以一個(gè)簡(jiǎn)單的求解三角形面積的例子來(lái)說(shuō)明自定義函數(shù)的編寫(xiě)和使用。

（1）打開(kāi)VBA窗口

啟動(dòng)EXCEL后，按ALT＋F11鍵，調(diào)出VBA窗口，插入模塊，就可開(kāi)始編寫(xiě)自定義函數(shù)的代碼了。

（2）編寫(xiě)代碼

通常自定義函數(shù)是用 function命令開(kāi)始的，在這個(gè)命令后面給它指定一個(gè)名字和參數(shù)。編寫(xiě)完成的代碼如下：

Function sjxmj（di，gao）sjxmj＝di*gao／2 End Function

說(shuō)明：本例自定義函數(shù)的代碼非常簡(jiǎn)單，只有三行。先看第一行，其中 sjxmj是自己取的函數(shù)名字（“三角形面積”漢語(yǔ)拼音的首字母組成），括號(hào)中有兩個(gè)參數(shù)，也就是變量，di表示 “三角形的底邊長(zhǎng)”，gao表示 “三角形的高”，兩個(gè)參數(shù)用逗號(hào)隔開(kāi)。為增加可讀性，也可使用中文來(lái)命名函數(shù)和參數(shù)。

計(jì)算過(guò)程只一行代碼 （第二行，通常實(shí)現(xiàn)復(fù)雜計(jì)算和分析的代碼不止一行），將di*gao／2（底乘高除以 2）這個(gè)公式賦值給 sjxmj（自定義函數(shù)名）。最后一行（第三行）與第一行成對(duì)出現(xiàn)的，表示自定義函數(shù)的結(jié)束。

（3）使用自定義函數(shù)

回到EXCEL窗口，假設(shè)B3和C3單元格中分別輸入了三角形的底邊長(zhǎng)和高的值，則在 D3單元格中輸入公式 ＝sjxmj（B3，C3），就會(huì)得到這對(duì)應(yīng)的三角形面積了 （圖1）。

5 內(nèi)置函數(shù)組合與自定義函數(shù)的對(duì)比

（1）組合內(nèi)置函數(shù)法

通過(guò)實(shí)例來(lái)說(shuō)明如何使用組合內(nèi)置函數(shù)判別河流類(lèi)別。

使用組合內(nèi)置函數(shù)判別時(shí)，在目標(biāo)單元格中既要包含判別條件和各類(lèi)別的限值，還要包括存放監(jiān)測(cè)值的單元格。本例中，公式很復(fù)雜，存放監(jiān)測(cè)值的單元格名就出現(xiàn)了6次，還沒(méi)有考慮缺測(cè)和異常等特殊情況的處理 （圖2）。

（2）自定義函數(shù)

通過(guò)實(shí)例來(lái)說(shuō)明如何用自定義函數(shù)來(lái)判別硫化物類(lèi)別。

從圖3的編輯欄中可看到，自定義函數(shù)名為RiverSCata，只包括一個(gè)參數(shù)，即存放于 C3單元格中的監(jiān)測(cè)值。自定義函數(shù)在編寫(xiě)時(shí)已經(jīng)包括了判別條件和各個(gè) 《標(biāo)準(zhǔn)》限值，還包括了特殊情況的處理。相同，只是對(duì)應(yīng)的標(biāo)準(zhǔn)值不同。且除個(gè)別項(xiàng)目（如溶解氧）外，其它項(xiàng)目的判別條件基本相同。由此，引入了一種新的自定義函數(shù)：帶引用參數(shù)的自定義函數(shù)。

仍然通過(guò)實(shí)例來(lái)說(shuō)明如何用帶引用參數(shù)的自定義函數(shù)來(lái)判別硫化物類(lèi)別。

6 帶引用參數(shù)的自定義函數(shù)

在地表水水質(zhì)評(píng)價(jià)時(shí)，監(jiān)測(cè)指標(biāo)多，《標(biāo)準(zhǔn)》共涉及項(xiàng)目共計(jì)109項(xiàng)，其中地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)基本項(xiàng)目就達(dá) 24項(xiàng)之多。對(duì)多監(jiān)測(cè)指標(biāo) （項(xiàng)目）判別時(shí)，需要分別編寫(xiě)各項(xiàng)目的自定義函數(shù)，如：RiverDoCata、RiverCodCata、RiverBodCata、River氨氮Cata、……。也就是說(shuō)，通過(guò)逐一使用各項(xiàng)目的自定義函數(shù)來(lái)實(shí)現(xiàn)地表水環(huán)境質(zhì)量評(píng)價(jià)，工作量仍然很大，且自定義函數(shù)的編寫(xiě)、調(diào)用和管理等也較為困難，記住如此之多的函數(shù)名也不易。

經(jīng)分析，判別每項(xiàng)指標(biāo)類(lèi)別的原理和方法基本

從圖4的編輯欄中可看到，自定義函數(shù)名為RvrCata，包括二個(gè)參數(shù)，除了存放于 C3單元格中的監(jiān)測(cè)值（第二個(gè)參數(shù)）外，還多了一個(gè)引用參數(shù)。帶引用參數(shù)的自定義函數(shù)能根據(jù)引用參數(shù)值找到與監(jiān)測(cè)值對(duì)應(yīng)的標(biāo)準(zhǔn)限值以完成判別。因此，一個(gè)帶引用參數(shù)的自定義函數(shù)，相當(dāng)于數(shù)十個(gè)單項(xiàng)目判別的自定義函數(shù)，其調(diào)用、管理和使用都很方便。圖4中，引用參數(shù)值為21，是硫化物在 《標(biāo)準(zhǔn)》中的參與類(lèi)別判別的項(xiàng)目順序號(hào)。

7 帶引用參數(shù)的自定義函數(shù)的使用

圖5是常見(jiàn)的一張水質(zhì)監(jiān)測(cè)數(shù)據(jù)表，為方便說(shuō)明，只選取了部分行和列的內(nèi)容。且用另一張工作表來(lái)存放類(lèi)別判別結(jié)果。

將“河流數(shù)據(jù)”表復(fù)制到同工作簿的另一新表 “河流類(lèi)判”表中，刪除各項(xiàng)監(jiān)測(cè)數(shù)據(jù)，保留表頭以及河流和斷面名。在使用自定義函數(shù)前，先對(duì)照 《標(biāo)準(zhǔn)》中各監(jiān)測(cè)項(xiàng)目的序號(hào)，將項(xiàng)目代碼輸入到 “引用代碼”行（本例中的第100行）的對(duì)應(yīng)列中。在第一個(gè)判別單元格 （G2）中輸入帶引用參數(shù)的自定義函數(shù) “＝RvrCata（G＄100，河流數(shù)據(jù)！G2）”，函數(shù)中的第一個(gè)參數(shù)是引用參數(shù)，在G100單元格中取值 （輸入格式為 G＄100的含義是：當(dāng)向右拖動(dòng)填充柄，列標(biāo)會(huì)隨之變化；而向下拖動(dòng)填充柄時(shí)行標(biāo)保持不變），第二個(gè)參數(shù)為監(jiān)測(cè)值，在“河流數(shù)據(jù)”表的G2單元格中取值，隨著填充柄拖動(dòng)，取值單元格的列標(biāo)和行標(biāo)均隨之變化。第一個(gè)公式輸入完成后，就可看到判別結(jié)果。利用 EXCEL的填充柄功能，先向右拖動(dòng)填充柄，再向下拖動(dòng)填充柄，直到所有需要判別的單元格中的公式都填充完畢，見(jiàn)圖6。類(lèi)別判別結(jié)果同時(shí)顯示于報(bào)表中，見(jiàn)圖7。

8 結(jié)語(yǔ)

地表水環(huán)境質(zhì)量評(píng)價(jià)涉及的項(xiàng)目多，工作繁雜，需考慮的因素較多。對(duì)沒(méi)有建立數(shù)據(jù)庫(kù)系統(tǒng)的監(jiān)測(cè)部門(mén)，評(píng)價(jià)工作量是很大的。自定義函數(shù)能夠?qū)崿F(xiàn)復(fù)雜的數(shù)據(jù)處理與分析，象 EXCEL的內(nèi)置函數(shù)一樣簡(jiǎn)單直觀，使用方便，且易于推廣［4］。帶引用參數(shù)的 Excel自定義函數(shù)相當(dāng)于集合了多個(gè)功能基本相同的自定義函數(shù)于一身，能進(jìn)一步提高工作效率。在評(píng)價(jià)地表水環(huán)境質(zhì)量時(shí)，僅用一個(gè)函數(shù)就實(shí)現(xiàn)了整張河流水質(zhì)監(jiān)測(cè)數(shù)據(jù)表的類(lèi)別判別。利用EXCEL的引用功能，可方便快捷地完成整張表中大量公式的輸入［5］。由于帶引用參數(shù)的自定義函數(shù)對(duì)報(bào)表中各水質(zhì)監(jiān)測(cè)指標(biāo)的列位置沒(méi)有特殊要求，適合于各類(lèi)地表水質(zhì)量評(píng)價(jià)，使用靈活方便。沒(méi)有計(jì)算機(jī)編程基礎(chǔ)的工作者，也能輕易地掌握自定義函數(shù)的使用方法。

［1］百度百科 ［EB／OL］.http：／／baike.baidu.com／.

［2］單文坡.EXCEL和 ORIGIN在環(huán)境監(jiān)測(cè)數(shù)據(jù)處理中的應(yīng)用［J］.石家莊職業(yè)技術(shù)學(xué)院學(xué)報(bào)，2008，20（2）：58－60.

［3］GB 3838－2002，地表水環(huán)境質(zhì)量標(biāo)準(zhǔn) ［S］.

［4］白愛(ài)民.EXCEL與底泥數(shù)據(jù)的分析 ［J］.環(huán)境科學(xué)導(dǎo)刊，2010，29（5）：109-112.

［5］白愛(ài)民.湖庫(kù)富營(yíng)養(yǎng)化狀態(tài)的快速評(píng)價(jià) ［J］.環(huán)境科學(xué)導(dǎo)刊，2014，33（1）：80-83.

One Method Employing EXCEL Software to Assess Surface Water Quality

BAI Ai－min
（Yunnan Environmental Monitoring Center，Kunming Yunnan 650034 China）

In order to improve the efficiency of surface water quality assessment，the user－defined functions with parameters in EXCEL software were applied to complete complicated monitoring data treatment and analysis.

surface water；environmental quality；assess；method；EXCEL；user－defined function；parameter

X82

A

1673－9655（2014）04－0072－05

2014－03－17

白愛(ài)民 （1955－），男，高級(jí)工程師，主要從事環(huán)境信息、網(wǎng)絡(luò)管理、自動(dòng)監(jiān)測(cè)站、數(shù)據(jù)庫(kù)研究。