白愛民

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

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

白愛民

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

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

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

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

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

1 地表水環(huán)境質(zhì)量標準簡介［3］

《地表水環(huán)境質(zhì)量標準 （GB3838－2002）》（下稱 《標準》）是我國為加強地表水環(huán)境管理、防治水環(huán)境污染、保障人體健康實施的國家環(huán)境質(zhì)量標準?！稑藴省钒凑盏乇硭氖褂霉δ軐⑺|(zhì)劃分為五類。此外，《標準》規(guī)定了水質(zhì)項目及標準值、水質(zhì)評價、水質(zhì)項目的分析方法以及標準的實施與監(jiān)督?！稑藴省返倪m用范圍包括中華人民共和國領(lǐng)域內(nèi)江河、湖泊、運河、渠道、水庫等具有使用功能的地表水水域。

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

2 地表水環(huán)境質(zhì)量評價方法

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

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

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

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

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

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

下面以一個簡單的求解三角形面積的例子來說明自定義函數(shù)的編寫和使用。

（1）打開VBA窗口

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

（2）編寫代碼

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

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

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

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

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

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

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

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

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

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

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

通過實例來說明如何用自定義函數(shù)來判別硫化物類別。

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

仍然通過實例來說明如何用帶引用參數(shù)的自定義函數(shù)來判別硫化物類別。

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

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

經(jīng)分析，判別每項指標類別的原理和方法基本

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

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

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

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

8 結(jié)語

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

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

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

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

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

［5］白愛民.湖庫富營養(yǎng)化狀態(tài)的快速評價 ［J］.環(huán)境科學導刊，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

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