浅谈Excel函数在水质资料整编中的应用

李海帆

（陕西省商洛水文水资源勘测局，陕西 商洛 726000）

0 引言

目前，水质资料整编还没有建立一套完善的资料整汇编系统，大量的数据需要手工计算并求得特征值，不仅费时费力，而且手工计算在输入过程中经常发生错误，准确率得不到保证。

水质资料整编中需要计算的特征值包括有每个断面全年的样品总数、检出率、超标率、实测范围（包括最小值、最大值）、最大超标倍数、最大值出现日期、年平均值等共7项8个参数，本文以化学需氧量项目为例，通过使用成果表（其行列分布见图1）中的数据，求得特征值表（其行列分布见图2），阐述Excel在求资料整编特征值中的应用方法。

图1 水质资料整编成果表各参数在Excel中的行列分布图

图2 水质资料整编特征值表各参数在Excel中的行列分布图

1 所用Excel函数介绍

Excel是Microsoft office公司推出的办公套件中的一个重要组件,使用它既可以制作电子表格,也可以进行各种数据的处理、统计分析和辅助决策操作,被广泛应用于管理、统计、财经、金融等众多领域。Excel包含了大量的内置函数，本文主要应用COUNTA、COUNTIF、IF、MIN、MAX、MATCH、INDEX、LEFT、COLUMN 函数，来计算水质资料整编中涉及的8个特征值参数。

1.1 COUNTA函数

COUNTA函数用于计算区域中所有不为空的单元格的个数。通过计算非空单元格数，即可统计出当前项目当年的样品总数。

1.2 COUNIF函数

COUNTIF函数用于对单元格区域中满足单个指定条件的单元格进行计数。此函数可以项目的方法检出限为条件，统计大于检出限的个数；也可以目标水质类别为条件，统计超标个数。

1.3 IF函数

IF函数来完成因指定的条件不同而需要返回不同结果的计算处理。以大于检出限样品个数为判断条件，与样品个数进行比较，如果相等，则最小值为小于检出限，否则调用MIN函数求最小值。

1.4 MAX函数

MAX函数用于计算某一组数据中的最大值。以项目的所有测定值为参数，计算其全年最大值。

1.5 MIN函数

与MAX函数的功能相反,MN函数用于计算一组数值中的最小值。以项目的所有测定值为参数，计算其全年最小值。当最小值为小于检出限，则与IF函数嵌套，求出全年最小值。

1.6 MATCH函数

MATCH函数用于在指定范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置。通过MATCH函数在成果表中查找最大值，并返回其所在位置。

1.7 INDEX函数

INDEX函数用于返回指定位置中的内容。INDEX函数与LEFT、COLUMN、MATCH嵌套使用可以求出最大检出日期。

1.8 COLUMN函数

Excel中默认情况下以字母的形式表示列号,可以使用COLUMN函数返回指定单元格引用的列号。通过COLUMN函数可以求出最大值出现的日期的列号。

1.9 LEFT函数

LEFT函数能够从文本左侧起提取文本中的第一个或前几个字符。由于成果表中年月日处于一个单元格内，使用LEFT函数截取最大值出现日，再通过字符串拼接，即可得到规范所要求的格式。

2 Excel函数的具体应用

通过对上述函数的综合运用，可快速计算出其特征值，为简洁起见，下文中的引用均为成果表中的单元格，在使用时只需在所引用单元格前加上“成果表!”即可。

2.1 计算样品总数

计算样品总数，通过统计所有测次中非空单元格的个数，用COUNTA函数即可。在特征值表的I4单元格输入=COUNTA(N4:N15)算出化学需氧量样品总数为12。

2.2 计算检出率

通过COUNTIF函数计算给定区域的单元格数目。计算化学需氧量检出率，因《水质 化学需氧量的测定 重铬酸盐法》（HJ 828-2017）规定化学需氧量的检出限为 4 mg/L，即COUNTIF（N4:N15,”＞=4”）。然后，检出数除以样品总数乘以100%即可得到检出率，即在特征值表的I5单元格中输入=COUNTIF（N4:N15,”＞=4”）/COUNTA(N4:N15)*100，得到化学需氧量检出率为75.0。

2.3 计算超标率

计算超标率，通过COUNTIF函数计算超标数。根据《地表水环境质量标准》（GB 3838-2002），在整编中超过每个参数的Ⅲ类标准即为超标，《地表水环境质量标准》（GB 3838-2002）化学需氧量的Ⅲ类标准限值为20 mg/L，即COUNTIF（N4:N15,”＞20”）。超标数除以样品总数乘以100%即可得到超标率，即在表 2的 I6单元格中输入 =COUNTIF（N4:N15,”＞20”）/COUNTA(N4:N15)*100，得到超标率为0。当检出率为0时，根据《水环境监测规范》（SL 219-2013）要求，超标率单元格为空，此时删除超标率即可。当检出率不为0且小于100%时保留1位小数，等于100%是保留整数。经计算化学需氧量的超标率为0。

2.4 计算实测范围

计算实测范围包括最小值和最大值，其求法如下：

（1）求最小值：原理如下，使用IF函数判断，当大于等于检出限样品的个数等于样品总数时，则表明全部检出。使用MIN函数求出最小值，当大于等于检出限样品的个数不等于样品总数时，则表明有未检出，则最小值为小于检出限，即在表2的I7单元格中输入=IF(COUNTIF(N8:N15,"＞=4")=COUNTA(N4:N15),MIN(N4:N15),"＜4")，得到最小值为＜4。经计算化学需氧量实测范围中最小值为为＜4。

（2）求最大值：使用用MAX函数求出最大值，即在表2的I8单元格中输入=MAX(N4:N15)。经计算化学需氧量最大值为16。

当全部未检出时，则最大值显示＜检出限，根据《水环境监测规范》（SL219-2013）要求，全部未检出时，最大值和最小值单元格为空，此时删除最大值和最小值即可。

2.5 计算最大超标倍数

当超标率不为0时，则用最大值除以Ⅲ类水标准值减1即可，即在I8单元格输入“=MAX(N4:N15)/20-1”，得到最大值超标倍数。若全部未检出，此单元格为空。本例中化学需氧量未超标，所以超标倍数单元格为空。

2.6 最大值出现日期

通过MATCH函数求出最大值所在的行数，即最大值所在的月份，然后通过INDEX函数和MATCH函数的套用，求出最大值所对应的的分析日期所在的单元格，并通过应用LEFT函数截取分析日期的前一个字符，即最大值出现日，再后通过&字符，将最大值出现月和最大值出现日连接，即为最大值出现日期，在I9单元格中输入=MATCH(MAX(N4:N15),N4:N15,0)&""&LEFT(INDEX(A4:BD15,MATCH(MAX(N4:N15),N4:N15,0),COLUMN(N4)),1)即可求得最大值出现日期。经使用函数得出化学需氧量最大值出现日期为502。

2.7 计算年平均值

《水环境监测规范》（SL 219-2013）中对年平均值的要求是：小于检出限的按1/2检出限参加计算；如果平均值小于方法检出限但超过小数保留位数时，以1/2检出限作为年平均值。

先用SUMIF函数求出检出单元格之和，然后再用COUNTIF函数求出未检出单元格个数后乘以1/2检出限，与之前检出单元格相加，除以非空单元格数即为平均值；再用IF函数判断平均值是否大于检出，如果大于检出限，则此值为年平均值，否则以1/2检出限作为平均值，因此在I9单元格中输入=IF((SUMIF(N4:N15,"＞=4",N4:N5)+COUNTIF(N4:N15,"＜4")*2)/COUNTA(N4:N15)＞=4,(SUMIF(N4:N15,"＞=4",N4:N5)+COUNTIF(N4:N15,"＜4")*2)/COUNTA(N4:N15),2)。经计算求得化学需氧量平均值为9。

3 方法改进探讨

3.1 关于方法检出限

近几年国家相关部门对环境保护日益重视，对监测标准的修订频率加大，当方法检出限改变后，往往所有公式中都得改检出限。因此，在工作簿中增加一个检出限表，需要用到检出限时直接调用检出限表中的检出限，需要用到1/2检出限是直接用函数求其一半即可。当检出限改变时直接在检出限表中修改检出限。

3.2 关于各类水的标准限值

《地表水环境质量标准》（GB 3838-2002）对方法检出限在未来也会修订，因此各类水限值有可能变化，可以写入检出限表中调用，以便后期的修改。

3.3 关于需要清除内容的单元格

《水环境监测规范》（SL 219-2013）对水质资料整编的规定有：检出率为0时，超标率、实测范围、最大值超标倍数、最大值出现日期均不填。因此，对VBA等熟悉者可以尝试自动对这些单元格进行删除。

4 结语

通过应用Excel预置的函数计算求得化学需氧量项目的特征值计算方法，提高了水质资料整编的效率和准确度，节省了数据归类和计算的时间。Excel函数在2018年水质资料整编中使用，其成果被正式采纳。此方法适用性强、操作快捷，为水质资料整编提供了很好的计算分析途径，对其它30余项水质项目的特征值求法也适用。

此方法仍有不足之处，比如自动清除部分单元格内容、对方法标准规定需要保留若干位有效数字的项目进行取舍还不能自动完成，期望通过VBA编程等方法予以解决。