实现地表水环境质量评价的一种方法——使用 EXCEL自定义函数的新思路

白爱民

（云南省环境监测中心站，云南 昆明650034）

实现地表水环境质量评价的一种方法
——使用 EXCEL自定义函数的新思路

白爱民

（云南省环境监测中心站，云南 昆明650034）

介绍了在 EXCEL环境下常用的评价地表水环境质量的方法，通过比较，“带引用参数的 Excel自定义函数”集合了多个功能基本相同的自定义函数于一身，不仅能够实现复杂的数据处理与分析，还能象 EXCEL的内置函数一样使用方便，能进一步提高工作效率。

地表水；环境质量；评价；方法；EXCEL；自定义函数；引用参数

水环境质量指水环境对人群的生存和繁衍以及社会经济发展的适宜程度，通常指水环境遭受污染的程度［1］。地表水环境质量评价是环境监测部门的常规任务之一。地表水环境质量评价是将各项监测指标的监测值与对应的国家标准做比较判别，综合后获得评价结果，再与该水体的水质功能区类别做比较，从而判断环境质量的优劣、变化和达标与否。整个评价过程中，将各指标的监测值与对应国家标准做判别是基础，快速且准确地获得各项指标的评价结果十分重要。

Microsoft EXCEL是使用最广泛的电子表格软件之一，它在数据综合管理和分析方面具有功能强大、技术先进、使用方便等特点［2］。很多工作人员使用 EXCEL仍停留在初级水平，对完成监测指标多、分类判别麻烦的综合评价，其工作量仍然很大，且极易出现错误。找出一种简单、易用的地表水环境质量评价方法是很有必要的。

1 地表水环境质量标准简介［3］

《地表水环境质量标准 （GB3838－2002）》（下称 《标准》）是我国为加强地表水环境管理、防治水环境污染、保障人体健康实施的国家环境质量标准。《标准》按照地表水的使用功能将水质划分为五类。此外，《标准》规定了水质项目及标准值、水质评价、水质项目的分析方法以及标准的实施与监督。《标准》的适用范围包括中华人民共和国领域内江河、湖泊、运河、渠道、水库等具有使用功能的地表水水域。

《标准》共涉及项目（即描述水质状况的监测指标）共计109项，其中地表水环境质量标准基本项目24项，集中式生活饮用水地表水源地补充项目5项，集中式生活饮用水地表水源地特定项目80项。

2 地表水环境质量评价方法

目前，常用的地表水 （河流、湖库）环境质量评价采用单因子评价法，根据评价时段内该监测点参评的指标中类别最高的一项来确定。对单个监测点，直接评价；当河流、流域 （水系）、湖库有多个监测点位时，先计算这些点位中各评价指标浓度的算术平均值，再行评价。评价项目的类别是根据评价指标的浓度值，对照 《标准》的项目标准限值来确定的 （见表1）。

《标准》将地表水分为五大类，即每项监测值经判别后可能会在一类、二类、三类、四类、五类或超五类中的一种 （分别用 “Ⅰ”、“Ⅱ”、“Ⅲ”、“Ⅳ”、“Ⅴ”或 “＞Ⅴ”表示）。完成评价获得水体的水质类别后，可对该水体的水质做定性评价分级。水质类别与水质定性评价分级的对应关系见表2。

3 自定义函数的概念与功能

Excel提供了大量的内置函数，在数据处理和信息化工作中起到了巨大的作用。通过多个内置函数的组合，可完成更为复杂的计算和分析。但是，内置函数组合使得公式变得冗长，难理解，还易出错。使用自定义函数能弥补内置函数组合之不足。

EXCEL的自定义函数功能是 MicroSoft Office先进性的具体体现之一。先通过 VBA完成自定义函数的编写，再在EXCEL中导入或加载，就可象EXCEL的内置函数一样使用自定义函数了。自定义函数能够实现复杂的数据处理与分析，使用象内置函数一样简单。

4 自定义函数的编写与使用

下面以一个简单的求解三角形面积的例子来说明自定义函数的编写和使用。

（1）打开VBA窗口

启动EXCEL后，按ALT＋F11键，调出VBA窗口，插入模块，就可开始编写自定义函数的代码了。

（2）编写代码

通常自定义函数是用 function命令开始的，在这个命令后面给它指定一个名字和参数。编写完成的代码如下：

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

说明：本例自定义函数的代码非常简单，只有三行。先看第一行，其中 sjxmj是自己取的函数名字（“三角形面积”汉语拼音的首字母组成），括号中有两个参数，也就是变量，di表示 “三角形的底边长”，gao表示 “三角形的高”，两个参数用逗号隔开。为增加可读性，也可使用中文来命名函数和参数。

计算过程只一行代码 （第二行，通常实现复杂计算和分析的代码不止一行），将di*gao／2（底乘高除以 2）这个公式赋值给 sjxmj（自定义函数名）。最后一行（第三行）与第一行成对出现的，表示自定义函数的结束。

（3）使用自定义函数

回到EXCEL窗口，假设B3和C3单元格中分别输入了三角形的底边长和高的值，则在 D3单元格中输入公式 ＝sjxmj（B3，C3），就会得到这对应的三角形面积了 （图1）。

5 内置函数组合与自定义函数的对比

（1）组合内置函数法

通过实例来说明如何使用组合内置函数判别河流类别。

使用组合内置函数判别时，在目标单元格中既要包含判别条件和各类别的限值，还要包括存放监测值的单元格。本例中，公式很复杂，存放监测值的单元格名就出现了6次，还没有考虑缺测和异常等特殊情况的处理 （图2）。

（2）自定义函数

通过实例来说明如何用自定义函数来判别硫化物类别。

从图3的编辑栏中可看到，自定义函数名为RiverSCata，只包括一个参数，即存放于 C3单元格中的监测值。自定义函数在编写时已经包括了判别条件和各个 《标准》限值，还包括了特殊情况的处理。相同，只是对应的标准值不同。且除个别项目（如溶解氧）外，其它项目的判别条件基本相同。由此，引入了一种新的自定义函数：带引用参数的自定义函数。

仍然通过实例来说明如何用带引用参数的自定义函数来判别硫化物类别。

6 带引用参数的自定义函数

在地表水水质评价时，监测指标多，《标准》共涉及项目共计109项，其中地表水环境质量标准基本项目就达 24项之多。对多监测指标 （项目）判别时，需要分别编写各项目的自定义函数，如：RiverDoCata、RiverCodCata、RiverBodCata、River氨氮Cata、……。也就是说，通过逐一使用各项目的自定义函数来实现地表水环境质量评价，工作量仍然很大，且自定义函数的编写、调用和管理等也较为困难，记住如此之多的函数名也不易。

经分析，判别每项指标类别的原理和方法基本

从图4的编辑栏中可看到，自定义函数名为RvrCata，包括二个参数，除了存放于 C3单元格中的监测值（第二个参数）外，还多了一个引用参数。带引用参数的自定义函数能根据引用参数值找到与监测值对应的标准限值以完成判别。因此，一个带引用参数的自定义函数，相当于数十个单项目判别的自定义函数，其调用、管理和使用都很方便。图4中，引用参数值为21，是硫化物在 《标准》中的参与类别判别的项目顺序号。

7 带引用参数的自定义函数的使用

图5是常见的一张水质监测数据表，为方便说明，只选取了部分行和列的内容。且用另一张工作表来存放类别判别结果。

将“河流数据”表复制到同工作簿的另一新表 “河流类判”表中，删除各项监测数据，保留表头以及河流和断面名。在使用自定义函数前，先对照 《标准》中各监测项目的序号，将项目代码输入到 “引用代码”行（本例中的第100行）的对应列中。在第一个判别单元格 （G2）中输入带引用参数的自定义函数 “＝RvrCata（G＄100，河流数据！G2）”，函数中的第一个参数是引用参数，在G100单元格中取值 （输入格式为 G＄100的含义是：当向右拖动填充柄，列标会随之变化；而向下拖动填充柄时行标保持不变），第二个参数为监测值，在“河流数据”表的G2单元格中取值，随着填充柄拖动，取值单元格的列标和行标均随之变化。第一个公式输入完成后，就可看到判别结果。利用 EXCEL的填充柄功能，先向右拖动填充柄，再向下拖动填充柄，直到所有需要判别的单元格中的公式都填充完毕，见图6。类别判别结果同时显示于报表中，见图7。

8 结语

地表水环境质量评价涉及的项目多，工作繁杂，需考虑的因素较多。对没有建立数据库系统的监测部门，评价工作量是很大的。自定义函数能够实现复杂的数据处理与分析，象 EXCEL的内置函数一样简单直观，使用方便，且易于推广［4］。带引用参数的 Excel自定义函数相当于集合了多个功能基本相同的自定义函数于一身，能进一步提高工作效率。在评价地表水环境质量时，仅用一个函数就实现了整张河流水质监测数据表的类别判别。利用EXCEL的引用功能，可方便快捷地完成整张表中大量公式的输入［5］。由于带引用参数的自定义函数对报表中各水质监测指标的列位置没有特殊要求，适合于各类地表水质量评价，使用灵活方便。没有计算机编程基础的工作者，也能轻易地掌握自定义函数的使用方法。

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

［2］单文坡.EXCEL和 ORIGIN在环境监测数据处理中的应用［J］.石家庄职业技术学院学报，2008，20（2）：58－60.

［3］GB 3838－2002，地表水环境质量标准 ［S］.

［4］白爱民.EXCEL与底泥数据的分析 ［J］.环境科学导刊，2010，29（5）：109-112.

［5］白爱民.湖库富营养化状态的快速评价 ［J］.环境科学导刊，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－），男，高级工程师，主要从事环境信息、网络管理、自动监测站、数据库研究。