Excel函数在水质评价中的应用

杜久芳 冯 峰

（淮河水利委员会水文局（信息中心）蚌埠 233001）

1 引言

在编制淮河流域水质通报的过程中，需要对100 余处站点的水质数据进行统计，并按照地表水环境质量标准，分多个区域进行水质评价，大量的数据需要计算、处理，手工逐个计算，不仅费时费力，而且输入过程容易出错，准确率得不到保证。

水质评价中需要根据相应类别标准，对PH、溶解氧、高锰酸盐指数、化学需氧量等21 项指标进行单项水质项目水质类别评价，在此基础上对各站点进行水质站水质类别评价，得出评价结果，说明超标项目和单项水质项目超标倍数。笔者通过总结前人经验和自己的摸索实践，针对水质通报编制过程中数据处理费时、费力、出错率高的问题，阐述Excel 函数在水质评价数据统计分析中的应用。

2 Excel 函数介绍

Excel 是Microsoftoffice 公司推出的办公套件中的一个重要组件，它既可以制作电子表格,也可以进行各种数据的处理、统计分析和辅助决策操作,被广泛应用于管理、统计、财经、金融等众多领域。

Excel 包含了大量的内置函数，本文主要应用其中的逻辑函数IF，查找和引用函数VLOOKUP，统计函数MAX、COUNTIF， 文本函数LEFT、CONCATENATE、LEN，计算水质评价中涉及的水质类别、超标项目（倍数）、水质类别比例、超标和达标比例等评价参数。

2.1 逻辑函数IF

如果指定条件的计算结果为TRUE，IF 函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。

2.2 查找和引用函数

VLOOKUP 函数用于搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。

2.3 统计函数

2.3.1 MAX 函数

返回一组值中的最大值。

2.3.2 COUNTIF 函数

COUNTIF 函数会统计某个区域内符合指定的单个条件的单元格数量。

2.4 文本函数

2.4.1 LEFT 函数

LEFT 从文本字符串的第一个字符开始返回指定个数的字符。

2.4.2 CONCATENATE 函数

CONCATENATE 函数可将最多255 个文本字符串合并为一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合。

2.4.3 LEN 函数

LEN 函数返回文本字符串中的字符个数。

3 Excel 函数的具体应用

通过对上述函数的综合运用，可快速对水质站和流域及区域的水质数据进行统计、分析，完成水质评价工作，其中水质站水质评价包括单项水质项目水质类别评价、单项水质项目超标倍数评价、水质站水质类别评价和水质站主要超标项目评价，流域及区域水质评价应包括达标比例、超标比例、各类水质类别比例。

3.1 水质站水质评价

3.1.1 单项水质项目水质类别评价

单项水质项目水质类别根据该项目实测浓度值与《地表水环境质量标准》（GB3838-2002）中规定的限值的比对结果确定,以某水质站的高锰酸盐指数的水质类别评价为例，Ⅰ类、Ⅱ类、Ⅲ类、Ⅳ类、Ⅴ类水的高锰酸盐指数限值分别为2mg/L、4mg/L、6mg/L、10mg/L、15mg/L。

在计算单项水质项目水质类别的单元格中输入公式：=IF（G8="","",IF（G8<=2,1,IF（G8<=4,2,IF（G8<=6,3,IF（G8<=10,4,IF（G8<=15,5,IF（G8>15,6,""））））））），得到该站的高锰酸盐指数项目水质类别为2，即Ⅱ类水，公式中G8 表示该站的高锰酸盐指数浓度值。

3.1.2 水质站水质类别评价

水质站水质类别按所评价项目中水质最差项目的类别确定，在单元格AW 中输入公式=MAX（AA8:AG8,AI8:AV8），单元格AX 中输入公式=IF（AW8=1,"Ⅰ",IF（AW8=2,"Ⅱ",IF（AW8=3,"Ⅲ",IF（AW8=4," Ⅳ",IF（AW8=5," Ⅴ"," 劣Ⅴ"）））），得出结果Ⅲ，表示该站水质为Ⅲ类水。

3.1.3 单项水质项目超标倍数评价

单项水质项目浓度超过GB3838 Ⅲ类标准限值的称为超标项目。超标项目的超标倍数应按式（1）计算。

式中：Bi—某水质项目超标倍数；

Ci—某水质项目浓度，mg/L；

Si—某水质项目的Ⅲ类标准限值，mg/L。

在计算某水质站高锰酸盐指数超标倍数的单元格BJ8 中输入公式=IF（G8="","",IF（G8>BJ$4,BJ$1&"（"&ROUND（（G8-BJ$4）/BJ$4,2）&"）",""））， 其中G8 为该站的高锰酸盐指数浓度值，BJ$1 为文本“高锰酸盐指数”，BJ$4 为Ⅲ类水的高锰酸盐指数限值6mg/L，得出结果：高锰酸盐指数（0.43），其中0.43即为该站的高锰酸盐指数超标倍数。

3.1.4 水质站超标项目及倍数评价

水质站主要超标项目及倍数采用两个公式进行计算。

单元格AZ8 中输入第一个公式：=CONCATENATE（BH8,BI8,BJ8,BK8,BL8,BM8,BN8,BP8,BQ8,BR8,BS8,BT8,BU8,BV8,BW8,BX8,BY8,BZ8,CA8,CB8,CC8）。

将3.1.3 中计算的单项水质项目超标倍数进行连接，在单元格BA8 中输入第二个公式：=IF（BA8="","",LEFT（BA8,LEN（BA8）-1）），去掉最后的“、”，得出结果：高锰酸盐指数（0.43）、化学需氧量（0.6）、总磷（1.95）。

3.2 流域及区域水质评价

3.2.1 达标比例、超标比例

流域或区域的水质站的达标、超标比例，利用条件统计函数COUNTIF 来计算，以达标比例的计算为例，在单元格内输入公式：=COUNTIF（评价总表!$BD$8:$BD$100,"= 达标"）/$N$1，其中$BD$8:$BD$100 为达标情况所在列，$N$1 表示有监测数据的水质站数量（部分站因河干不监测），公式为=COUNTIF（评价总表!$BD$8:$BD$100,"=达标"）+COUNTIF（评价总表!$BD$8:$BD$100,"=不达标"）。

3.2.2 各类水质类别比例

各类水质类别比例为Ⅰ类、Ⅱ类、Ⅲ类、Ⅳ类、V 类及劣V 类的比例，以I 类水的比例，在单元格中输入公式：=COUNTIF（$E$3:$E$95,"Ⅰ"）/$N$1，其中$E$3:$E$95 为水质类别所在列。

另外，在区域水质评价中，可以利用VLOOKUP函数，在总表中查找并汇总所需要的区域内水质站对应的水质数据和评价结果，公式为：=VLOOKUP（$C3,评价总表!$C$7:$BE$100,48,FALSE）。

4 结语

水质评价需要对大量的数据进行汇总、统计、分析，人工手动统计耗时费力，出错率高。本文充分使用Excel 函数，具有以下优点：（1）Excel 函数的应用，可以减少重复性人工计算过程，大大节约工作时间，提高工作效率；（2）该水质评价数据表结构框架搭建好之后，如果基础数据改变，只需修改评价表中相应的基础数据部分，评价结果通过函数公式能自动计算完成；（3）水质站点增多时，进行拖拽操作即可对所增加的站点进行单站评价，进行区域评价时只需调整公式中的数据起始行和结束行的行标号即可；而监测项目增加，只需将现有监测项目的公式复制到新增加的监测项目相应的位置即可，监测点和监测项目越多，越能体现Excel 函数的优越性■