环境监测数据标准化修约的Excel函数实现

张 然，吕学研，刘 雷，陈亚男

(1.江苏省环境监测中心，南京 210036;2.中国环境监测总站国家环境保护环境监测质量控制重点实验室，北京 100012)

1 前 言

环境监测数据是环境管理的基础。随着大众对环境关注度的日益提升，环境监测数据呈逐年增加趋势，环境监测数据综合分析人员必须掌握一种甚至几种数据分析软件，以实现监测数据的快速处理与分析。

Excel软件，是一款通用的，容易掌握的数据处理与分析软件，在环境监测[1～3]、水文监测[4]以及其他行业[5]的数据处理中得到广泛应用。数据的标准化修约是实现数据标准化管理与应用的基础，如何利用Excel软件快速实现数据的标准化修约，也是该软件应用过程中较为关注的热点之一。目前，利用Excel实现数据标准化修约主要分为两个方向：一是利用Excel函数组合嵌套[1～4]；一是应用VBA宏[5～8]。由于VBA需要数据分析人员具备一定的程序编写能力，其推广应用受到限制。通过Excel函数的组合嵌套，实现数据的标准化修约，对数据分析人员的编程能力要求较低，更具推广潜力。

本文在深入分析数据修约规则的基础上，提出了一种在Excel中进行数据修约的函数组合嵌套模式，并按照《国家地表水环境质量监测数据修约处理规则(试行)》，结合省内监测数据给出了各个指标的修约函数公式，以期帮助同业人员快速实现环境监测数据的标准化修约。

2 标准化修约规则的Excel函数语言实现

2.1 需调用的函数及功能介绍

ROUND函数 按指定的位数对数值进行四舍五入。函数格式为：ROUND(number,digits)。其中，number为要四舍五入的数，digits为小数点后要保留的位数。

IF函数 判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另外一个值。函数格式为：IF(A,B,C)，其中，A为需要满足的条件，B为满足条件后返回的值，C为不满足条件后返回的值。

AND函数 逻辑函数，如果所有参数值均为逻辑“真(TRUE)”，则返回逻辑“真(TRUE)”，反之返回逻辑“假(FALSE)”，函数格式为：AND(条件1,条件2,条件3,…)。

INT函数 将数值向下取整为最接近的整数。函数格式为：INT(number)，其中，number 需要进行向下取整的实数。

EVEN函数 将正数向上舍入到最接近的偶数，将负数向下舍入到最接近的偶数；如果要舍入的数值为偶数，则不舍入，保留原数值。函数格式为：EVEN(number)，其中，number 是将进行向上取偶的数值。

FIXED函数 用定点小数格式将数值舍入呈特定位数并返回带或不带逗号的文本。函数格式为：FIXED(number,decimals,no_commas)，其中，number为要进行四舍五入并转换成文本字符串的数；decimals为一数值，用以指定小数点右边的小数位数，如果忽略，则默认decimals=2；no_commas为一逻辑值，如果其值为 FALSE 或被省略，则返回的文本中和往常一样包含逗号。

2.2 数值修约规则及相关函数引用分析

《数值修约规则与极限数值的表示和判断》(GB/T8170-2008)为科学技术与生产活动中测试和计算得出数值需要修约时应执行的国家标准。其中，数值的进舍规则包含以下4个主要方面。

(1)拟舍弃数字的最左一位数字小于5，则舍去，保留其余各位数字不变；

(2)拟舍弃数字的最左一位数字大于5，则进一，即保留数字的末位数字加1；

(3)拟舍弃数字的最左一位数字是5，且其后有非0数字时进一，即保留数字的末位数字加1；

(4)拟舍弃数字的最左一位数字是5，且其后无数字或皆为0时，若所保留的末位数字为奇数(1、3、5、7、9)则进一，即保留数字的末位数字加1；若所保留的末位数字为偶数(0、2、4、6、8)则舍去。

规则1和2的要求，实际上表达的是“四舍六入”的概念，通过常规的四舍五入就能实现。通过五入也可以实现规则3的全部和规则4中所保留的末位数字为奇数(1、3、5、7、9)则进一的要求。规则1～3以及规则4中所保留的末位数字为奇数(1、3、5、7、9)则进一的要求在Excel中对应的修约函数为ROUND函数。但是规则4中 “若所保留的末位数字为偶数(0、2、4、6、8)则舍去”会对利用ROUND函数修约产生制约。因此，需要设计一个嵌套函数，能够实现规则4中拟舍弃最左一位数字的判断和其后数字的判断，同时实现保留的末位数字“奇数进一偶数不进”。

进一步分析规则4后发现，满足“拟舍弃数字的最左一位数字是5，且其后无数字或皆为0”这一要求的数值，应同时满足在乘以2×10n(n为修约后需要保留的小数位数，取值为0、1、2、…，下同)并向下取整后，与该数值直接乘以2×10n相等，但是在乘以1×10n并向下取整后，则小于该数值直接乘以1×10n，根据这一特性，可以利用INT函数实现数值取整，并通过AND函数来实现该逻辑判断。同时，利用EVEN函数对整数数字末位奇数向上取偶，偶数保持不变的功能，对于保留末位数字为奇数的情况，只需将该数值乘以1×10n后向下取整，再向上偶数舍入即可；对于保留末位数字为偶数的情况，只需将该数值乘以1×10n后向下取整即可。综合以上分析，规则4中，将需要修约的原数值乘以1×10n后取整，再进行偶数舍入，再除以10n，即可实现保留的末位数字“奇数进一偶数不进”。

由于存在各种数值可能性判断，还需要调用Excel中的判断函数IF。同时可以调用定点格式函数FIXED，确保同一修约条件下修约后的数值位数一致。

3 特定环境监测数据修约要求

《国家地表水环境质量监测数据修约处理规则(试行)》对除水温和粪大肠菌外的24项指标进行了修约小数限定，具体见下表，当修约后结果为0时，继续向下修约至第一位有效数字。本文基于水温和粪大肠菌群的数据特点，限定水温和粪大肠菌群的保留小数位数分别为1位和0位。

表1 监测指标数值修约要求Tab.1 Monitoring data rounding requirements

4 指标修约函数

4.1 水温和pH值

由于水温可以出现0值，因此，在修约过程中不需要考虑修约结果为0的情形。据此设定水温的修约函数公式为：

=IF(B2="","",FIXED(IF(AND(INT(B2*20)=(B2*20),INT(B2*10)<(B2*10)),EVEN(INT(B2*10))/10,ROUND(B2,1)),1)*1)

B2为水温数据存放的单元格编号(后续所列的各函数公式中的C2、F2、H2，I2……等标识均为EXCEL数据存放的单元格编号)。该函数的功能是，首先用第一个IF函数判断B2单元格是否为空，如果为空，则返回也为空；如果B2单元格不为空，则用第二个IF函数判断B2单元格中的数值满足哪条修约规则。AND(INT(B2*20)=(B2*20),INT(B2*10)<(B2*10))对应的是“拟舍弃数字的最左一位数字是5，且其后无数字或皆为0”这一判断条件，如果成立说明5的后面不为0，否则5的后面为0。EVEN(INT(B2*10))/10实现的是保留的末位数字“奇数进一偶数不进”这一要求。ROUND(B2,1)实现规则1～3条件下数值的修约。紧随其后的1，是实现修约结果的一位小数定点格式。*1是为了实现修约后的数据以数值格式呈现，可以直接参与统计分析。也就是说，FIXED函数与*1可以同时存在，也可以同时不存在，返回的数据都是数值格式的，可以直接参与统计分析；当FIXED函数单独存在时，修约后的数据将是文本格式，不便于一些统计分析的直接使用。

由于环境水体存在一定的缓冲能力，自然环境下，水体的pH值按照1位小数修约的结果不可能为0，故其修约函数可以采用与水温相同的算法(具体如下)，不同点仅体现在数值存储列号的变化上。

=IF(C2="","",FIXED(IF(AND(INT(C2*20)=(C2*20),INT(C2*10)<(C2*10)),EVEN(INT(C2*10))/10,ROUND(C2,1)),1)*1)

4.2 有机污染综合指标

高锰酸盐指数、化学需氧量和五日生化需氧量是表征水体有机污染的综合指标。《国家地表水环境质量监测网监测任务作业指导书(试行)》(以下简称“作业指导书”)中，3个指标检出限的一半分别为0.25mg/L、2mg/L和0.25mg/L。省内监测数据统计结果表明，高锰酸盐指数、化学需氧量和五日生化需氧量的最小统计值分别为0.8mg/L、1.2mg/L和0.1mg/L。因此，以0.1为基准设计修约函数，即可实现3个指标的1位小数修约要求。高锰酸盐指数、化学需氧量和五日生化需氧量的修约函数依次分别如下：

=IF(F2="","",FIXED(IF(AND(INT(F2*20)=(F2*20),INT(F2*10)<(F2*10)),EVEN(INT(F2*10))/10,ROUND(F2,1)),1)*1)

=IF(H2="","",FIXED(IF(AND(INT(H2*20)=(H2*20),INT(H2*10)<(H2*10)),EVEN(INT(H2*10))/10,ROUND(H2,1)),1)*1)

=IF(I2="","",FIXED(IF(AND(INT(I2*20)=(I2*20),INT(I2*10)<(I2*10)),EVEN(INT(I2*10))/10,ROUND(I2,1)),1)*1)

4.3 溶解氧和透明度

根据省内监测数据统计结果，溶解氧和透明度目前的最小统计值分别为0.18mg/L和5cm。因此，以0.1为基准设计修约函数，同样可实现这2个指标的1位小数修约要求。溶解氧和透明度的修约函数依次分别如下：

=IF(G2="","",FIXED(IF(AND(INT(G2*20)=(G2*20),INT(G2*10)<(G2*10)),EVEN(INT(G2*10))/10,ROUND(G2,1)),1)*1)

=IF(AA2="","",FIXED(IF(AND(INT(AA2*20)=(AA2*20),INT(AA2*10)<(AA2*10)),EVEN(INT(AA2*10))/10,ROUND(AA2,1)),1)*1)

4.4 挥发酚、硫化物、总氮、粪大肠菌群和叶绿素a

进一步分析发现，挥发酚、硫化物、总氮、粪大肠菌群和叶绿素a均可以0.1为基准设计修约函数，仅仅是修约小数位数的不同而已。据此，设计挥发酚、硫化物、总氮、粪大肠菌群和叶绿素a的修约函数公式依次分别如下：

=IF(O2="","",FIXED(IF(AND(INT(O2*20000)=(O2*20000),INT(O2*10000)<(O2*10000)),EVEN(INT(O2*10000))/10000,ROUND(O2,4)),4)*1)

=IF(W2="","",FIXED(IF(AND(INT(W2*2000)=(W2*2000),INT(W2*1000)<(W2*1000)),EVEN(INT(W2*1000))/1000,ROUND(W2,3)),3)*1)

=IF(X2="","",FIXED(IF(AND(INT(X2*200)=(X2*200),INT(X2*100)<(X2*100)),EVEN(INT(X2*100))/100,ROUND(X2,2)),2)*1)

=IF(Y2="","",FIXED(IF(AND(INT(Y2*2)=(Y2*2),INT(Y2)<(Y2)),EVEN(INT(Y2)),ROUND(Y2,0)),0)*1)

=IF(Z2="","",FIXED(IF(AND(INT(Z2*2000)=(Z2*2000),INT(Z2*1000)<(Z2*1000)),EVEN(INT(Z2*1000))/1000,ROUND(Z2,3)),3)*1)

4.5 石油类

作业指导书中，石油类分析方法的检出限为0.01mg/L，即该方法的统计数据大于等于0.005mg/L，省内石油类指标最小统计值为0.001mg/L。按照2位小数的修约要求，修约结果均为0。因此，石油类的小数修约位数应该为3位。据此，设计石油类的修约函数公式为：

=IF(P2="","",FIXED(IF(AND(INT(P2*2000)=(P2*2000),INT(P2*1000)<(P2*1000)),EVEN(INT(P2*1000))/1000,ROUND(P2,3)),3)*1)

4.6 氨氮

根据省内监测数据统计结果，氨氮目前的最小统计值为0.002 5mg/L，小于作业指导书中方法检出限的一半0.015mg/L。如果按照2位小数的要求修约，则会出现修约结果为0的情况，因此需要增加一组针对修约结果为零的修约函数。根据氨氮的最小统计值，需要修约到小数点后第3位，才能保证修约结果不为0。据此，设定氨氮的修约函数公式为：

=IF(D2="","",IF(D2*1000>5,FIXED(IF(AND(INT(D2*200)=(D2*200),INT(D2*100)<(D2*100)),EVEN(INT(D2*100))/100,ROUND(D2,2)),2),FIXED(IF(AND(INT(D2*2000)=(D2*2000),INT(D2*1000)<(D2*1000)),EVEN(INT(D2*1000))/1000,ROUND(D2,3)),3))*1)

其中，D2*1000>5是判断D2单元格内的数值是否满足2位小数的修约要求，因为只有在D2单元格内的数值大于0.005的情况下，才能满足2位小数的修约要求；由于0.005按照2位小数修约的结果仍为0，故[0.0025,0.005]区间的数值均需要按照3位小数来修约。

4.7 阴离子表面活性剂

作业指导书中，阴离子表面活性剂检出限的数量级与氨氮相同，且数值修约要求也与氨氮相同。因此，这2个指标的修约函数应该存在相同之处。目前，省内阴离子表面活性剂的最小统计值为0.001mg/L，小于作业指导书中方法检出限的一半0.025mg/L。据此，以0.001为基准设定阴离子表面活性剂的修约函数公式为：

=IF(Q2="","",IF(Q2*1000>5,FIXED(IF(AND(INT(Q2*200)=(Q2*200),INT(Q2*100)<(Q2*100)),EVEN(INT(Q2*100))/100,ROUND(Q2,2)),2),FIXED(IF(AND(INT(Q2*2000)=(Q2*2000),INT(Q2*1000)<(Q2*1000)),EVEN(INT(Q2*1000))/1000,ROUND(Q2,3)),3))*1)

4.8 总磷

作业指导书中，总磷分析方法的检出限为0.01mg/L，省内目前的最小统计值为0.005mg/L，与检出限的一半相等，同时，按照3位小数修约也不会出现结果为0的情况。据此，以0.005为基准，设定总磷的修约函数公式为：

=IF(E2="","",FIXED(IF(AND(INT(E2*2000)=(E2*2000),INT(E2*1000)<(E2*1000)),EVEN(INT(E2*1000))/1000,ROUND(E2,3)),3)*1)

4.9 氟化物和氰化物

作业指导书中，氟化物两种分析方法的检出限分别为0.006mg/L和0.05mg/L。按照2位小数修约，则最小统计值0.003mg/L的修约结果为0。因此，以0.003为基准设定阴离子表面活性剂的修约函数公式为：

=IF(J2="","",IF(J2*1000>5,FIXED(IF(AND(INT(J2*200)=(J2*200),INT(J2*100)<(J2*100)),EVEN(INT(J2*100))/100,ROUND(J2,2)),2),FIXED(IF(AND(INT(J2*2000)=(J2*2000),INT(J2*1000)<(J2*1000)),EVEN(INT(J2*1000))/1000,ROUND(J2,3)),3))*1)

作业指导书中，氰化物同样给出了检出限不同的2种分析方法，且其中一种方法检出限的一半为0.000 5mg/L，根据3位小数修约的结果会为0。同时，省内氰化物的最小统计值为0.000 1mg/L，与0.000 5具有相同的修约结果制约能力。因此，以0.000 1为基准设计氰化物的修约函数公式为：

=IF(N2="","",IF(N2*10000>5,FIXED(IF(AND(INT(N2*2000)=(N2*2000),INT(N2*1000)<(N2*1000)),EVEN(INT(N2*1000))/1000,ROUND(N2,3)),3),FIXED(IF(AND(INT(N2*20000)=(N2*20000),INT(N2*10000)<(N2*10000)),EVEN(INT(N2*10000))/10000,ROUND(N2,4)),4))*1)

4.10 重金属

六价铬：分析发现，六价铬的修约函数式仅仅是与氨氮和阴离子表面活性剂的修约函数存在数量级上的差异。因为六价铬的修约小数为3位，而检出限的一半0.002mg/L和省内最小统计值0.000 5 mg/L分别是比氨氮和阴离子表面活性剂低1个数量级。据此，以0.000 1为基准设计六价铬的修约函数公式为：

=IF(M2="","",IF(M2*10000>5,FIXED(IF(AND(INT(M2*2000)=(M2*2000),INT(M2*1000)<(M2*1000)),EVEN(INT(M2*1000))/1000,ROUND(M2,3)),3),FIXED(IF(AND(INT(M2*20000)=(M2*20000),INT(M2*10000)<(M2*10000)),EVEN(INT(M2*10000))/10000,ROUND(M2,4)),4))*1)

硒：作业指导书中，硒的2种分析方法的检出限均为0.000 4mg/L，如果取其一半0.000 2mg/L进行3位小数修约，结果为0，故应该按照4位小数修约。目前省内硒的最小统计值为0.000 025mg/L，需要按照5位小数修约结果才不为0。因此，以0.000 01为基准，设计硒的修约函数公式为：

=IF(K2="","",IF(K2*100000>5,FIXED(IF(AND(INT(K2*20000)=(K2*20000),INT(LK*10000)<(LK*10000)),EVEN(INT(K2*10000))/10000,ROUND(K2,4)),4),FIXED(IF(AND(INT(K2*200000)=(K2*200000),INT(K2*100000)<(K2*100000)),EVEN(INT(K2*100000))/100000,ROUND(K2,5)),5))*1)

砷：作业指导书中，砷的2种分析方法检出限的一半分别为0.000 15mg/L和0.000 1mg/L。按照2位小数修约，则数值的修约结果可能均为0。因此，砷的数值修约小数应该限定为4位。省内目前砷的最小统计值为0.000 02mg/L。据此，以0.000 01为基准设计砷的修约函数公式为：

=IF(L2="","",IF(L2*100000>5,FIXED(IF(AND(INT(L2*20000)=(L2*20000),INT(L2*10000)<(L2*10000)),EVEN(INT(L2*10000))/10000,ROUND(L2,4)),4),FIXED(IF(AND(INT(L2*200000)=(L2*200000),INT(L2*100000)<(L2*100000)),EVEN(INT(L2*100000))/100000,ROUND(L2,5)),5))*1)

镉：镉与砷的情况相同，2种分析方法检出限的一半分别为0.000 025mg/L和0.000 05mg/L，如果按照3位小数修约，则结果均可能为0。因此，砷的数值修约小数应该限定为5位。5位小数的修约限定同时也能保证省内最小统计值0.000 01 mg/L的修约结果不为0。据此，以0.000 01为基准设计砷的修约函数公式为：

=IF(T2="","",IF(T2*100000>5,FIXED(IF(AND(INT(T2*20000)=(T2*20000),INT(T2*10000)<(T2*10000)),EVEN(INT(T2*10000))/10000,ROUND(T2,4)),4),FIXED(IF(AND(INT(T2*200000)=(T2*200000),INT(T2*100000)<(T2*100000)),EVEN(INT(T2*100000))/100000,ROUND(T2,5)),5))*1)

铜：作业指导书中，铜给出了3种推荐方法，检出限的一半分别为0.000 04mg/L、0.003mg/L和0.000 5mg/L。按照2位小数的修约要求，则3种方法的最小统计值均可能为0。因此，铜的小数修约位数最低应该设置为3位。以省内铜的最小统计值0.000 01mg/L为基准，设计铜的修约函数公式，可以同时满足3种分析方法的数据修约要求。铜的修约函数公式为：

=IF(R2="","",IF(R2*100000>50,FIXED(IF(AND(INT(R2*2000)=(R2*2000),INT(R2*1000)<(R2*1000)),EVEN(INT(R2*1000))/1000,ROUND(R2,3)),3),IF(R2*100000>5,FIXED(IF(AND(INT(R2*20000)=(R2*20000),INT(R2*10000)<(R2*10000)),EVEN(INT(R2*10000))/10000,ROUND(R2,4)),4),FIXED(IF(AND(INT(R2*200000)=(R2*200000),INT(R2*100000)<(R2*100000)),EVEN(INT(R2*100000))/100000,ROUND(R2,5)),5))*1)*1)

铅：虽然作业指导书中铅的推荐方法比铜少1种，但是其中石墨炉原子吸收分光光度法方法检出限的一半根据3位小数修约不为0，而电感耦合等离子体质谱法检出限的一半(0.000 045mg/L)与省内铅的最小统计值(0.000 02mg/L)的修约结果均为0，即使按照4位小数修约结果也均为0。因此，铅的修约位数需要横跨3～5。从而，铅的修约函数公式与铜的相同，具体如下：

=IF(U2="","",IF(U2*100000>50,FIXED(IF(AND(INT(U2*2000)=(U2*2000),INT(U2*1000)<(U2*1000)),EVEN(INT(U2*1000))/1000,ROUND(U2,3)),3),IF(U2*100000>5,FIXED(IF(AND(INT(U2*20000)=(U2*20000),INT(U2*10000)<(U2*10000)),EVEN(INT(U2*10000))/10000,ROUND(U2,4)),4),FIXED(IF(AND(INT(U2*200000)=(U2*200000),INT(U2*100000)<(U2*100000)),EVEN(INT(U2*100000))/100000,ROUND(U2,5)),5))*1)*1)

汞：作业指导书中，汞的原子荧光法检出限为0.000 04mg/L，冷原子吸收法的检出限为0.000 01 mg/L，如果以检出限的一半参与统计，按照5位小数修约，则冷原子吸收法的数值修约可能为0。省内目前汞的最小统计值为0.000 002mg/L，据此设定汞的修约函数公式，则能够保证冷原子吸收法的数值修约结果不为0。因此，以0.000 001为基准设计汞的修约函数公式为：

=IF(V2="","",IF(V2*1000000>=10,FIXED(IF(AND(INT(V2*200000)=(V2*200000),INT(V2*100000)<(V2*100000)),EVEN(INT(V2*100000))/100000,ROUND(V2,5)),5),FIXED(IF(AND(INT(V2*2000000)=(V2*2000000),INT(V2*1000000)<(V2*1000000)),EVEN(INT(V2*1000000))/1000000,ROUND(V2,6)),6))*1)

锌：作业指导书中，锌的3种分析方法的检出限分别为0.05mg/L、0.004mg/L和0.000 7mg/L，以检出限的一半按照2位小数修约，则有2种方法的结果为0。这2种方法需要分别按照3位和4位小数进行修约，而省内锌的最小统计值为0.000 05 mg/L，需要按照5位小数修约才不至于结果为0。因此，以0.000 01为基准设计锌的修约函数公式为：

=IF(S2="","",IF(S2*100000>500,FIXED(IF(AND(INT(S2*200)=(S2*200),INT(S2*100)<(S2*100)),EVEN(INT(S2*100))/100,ROUND(S2,2)),2),IF(S2*100000>50,FIXED(IF(AND(INT(S2*2000)=(S2*2000),INT(S2*1000)<(S2*1000)),EVEN(INT(S2*1000))/1000,ROUND(S2,3)),3),IF(S2*100000>5,FIXED(IF(AND(INT(S2*20000)=(S2*20000),INT(S2*10000)<(S2*10000)),EVEN(INT(S2*10000))/10000,ROUND(S2,4)),4),FIXED(IF(AND(INT(S2*200000)=(S2*200000),INT(S2*100000)<(S2*100000)),EVEN(INT(S2*100000))/100000,ROUND(S2,5)),5))*1)*1)*1)

采用省内2017年省控断面各指标的月均值和年均值，对以上各指标的修约结果进行验证。结果表明，各指标均按照预期的设想实现了数值标准化修约。

5 结 语

本文在深入分析数据修约规则的基础上，采用Excel 2010中的IF、EVEN、INT、ROUND和FIXED函数，提出了一种数据修约函数组合嵌套模式，并根据地表水环境质量监测各项指标的具体修约要求，结合省内实际监测数据，给出了具体的修约函数公式。

在修约函数公式设计过程中，发现重金属指标修约函数公式的设计是工作的难点。主要是因为不同分析方法的检出限存在数量级上的差异，导致按照规定的小数位数修约难以避免0值的出现。同时，部分重金属指标原定的修约小数位数还有待商榷。比如，硒、砷、镉、铜，按照原定的小数位数进行修约，无论哪种分析方法，检出限一半的修约结果均为0。石油类指标也面临同样的问题。因此，建议调整《国家地表水环境质量监测数据修约处理规则(试行)》中这几项指标的修约位数。或者，从简便、快捷实现数据修约以及促进各监测机构能力升级提升的角度考虑，建议以灵敏度最高的分析方法的检出限设置修约小数位数。