利用Excel中函数编程实现地表水环境质量的自动评价

王欢 耿天召

（安徽省环境监测中心站 安徽合肥 230071）

利用Excel中函数编程实现地表水环境质量的自动评价

王欢 耿天召

（安徽省环境监测中心站 安徽合肥 230071）

在地表水环境质量评价中，要首先对监测数据进行标准化处理，如对低于检出限的数据取其检出限的1/2，对数字按照“四舍六入五留双”的修约规则进行数据修约；再对单个指标进行水质类别的判断，从而得出综合评价结果。文章利用Excel软件中自带的函数功能，把各类函数进行组合使用，在地表水环境质量评价的过程中，实现对未检出数据的自动处理及对数据的自动修约，提高工作效率以及减少人为错误。

地表水；Excel；环境质量

目前，环保部门在进行地表水环境质量的分析评价中，依据《地表水环境质量评价办法（试行）》[1]来进行地表水环境质量的评价。对于单次监测中低于检出限的项目，取其检出限的1/2参与评价，并且要对数据进行修约，手工处理效率低且容易出错。笔者将运用Excel中自带函数，实现环境监测数据的自动处理。

1 常用函数

1.1 IF函数

IF（logical_test，value_if_ture，value_if_false），判断是否满足一个条件，如果满足返回一个值，如果不满足则返回另一个值。该函数可以多层嵌套使用。

1.2 ISNUMBER函数

ISNUMBER（value），检测一个值是否为数值，返回ture或false。

1.3 LEN函数

LEN（test），返回文本字符串中的字符个数。

1.4 MID函数

MID（test，start_num，num_chars），从文本字符串中指定的起始位置起返回指定长度的字符。

1.5 ROUND函数

ROUND（number，num_digits），按指定的位数对数值进行四舍五入。

1.6 TRUNC函数

TRUNC（number，num_digits），将数字截为整数或保留指定位数的小数。

1.7 MOD函数

MOD（number，divisor），返回两数相除的余数。

1.8 POWER函数

POWER（number，power），返回某数的乘幂。

以上函数为数据处理所常用的一些基本函数，可以单独使用，但在大多数情况下都是数个函数组合使用，其中IF函数的使用频率最高。

2 数据处理实例

2.1 未检出数据自动取其检出限的1/2

2.1.1 问题分析

当监测指标未检出时，要取其1/2检出限进行计算。在数据表中以“检出限+L”表示，例如石油类未检出时填写“0.01L”，在进行计算时，要将其变成“0.005”。

2.1.2 思路框架

当数据为未检出数据时，数据格式为字符型，否则为数字型。因此首先判断单元格中字符串格式是否为数字型，如为非数字型数据，则提取单元格中的数字并取1/2。

2.1.3 解决方案及步骤

（1）确定单元格中字符串的类型，如为数值型则直接返回该数值，如为非数值型，则返回处理后的数值。利用逻辑函数IF嵌套ISNUMBER检测单元格中字符串是否为数值型的真假，真则返回该数据，如果不是则返回单元格中提取后的数值的1/2。

（2）对于未检出数据，首先确定单元格中字符串的长度。利用LEN函数计算出单元格中A1中字符串中字符的个数，即字符串中最后一个字符在字符串中的位置。

具体公式为：

｛=LEN（A1）｝。假定数据为“0.01L”，则返回结果为“5”。

（3）提取“L”之前的数字。利用MID函数提取文本字符串中从指定位置开始的特定数目的字符。由于地表水未检出数据为检出限后加L，则提取的字符长度为单元格中第一个数字位置起到最后一个数据位置止-1的字符。如0.01L，字符长度为5，提取的字符长度则为5-1，即从第一个数字起的4个数字。

由于函数MID返回的字符是文本，要将文本转化成数值型数据，可以用函数VALUE，也可以同等功能地用符号“--”或“+0”或“-0”简化表示，这里用“--”表示。具体公式是：

｛=--MID（A1，1，4）｝，与 LEN 函数联用，并取 1/2 值，公式是：

｛=--MID（A1，1，LEN（A1）-1）/2｝，返回结果为“0.005”。

（4）以上几个函数连用，具体公式是：

｛=IF（ISNUMBER（A1），A1，--MID（A1，1，LEN（A1）-1）/2）｝

2.2 数据小数位数的自动修约

2.2.1 问题分析

环境监测数据的数据统计结果要按照《数值修约规则与极限数值的表示和判定》（GB/T8170）[3]进行小数位数的修约，基本原则为“四舍六入五留双”。拟舍弃数字的最左一位数字小于5，则舍去，保留其余各位数字不变；拟舍弃数字的最左一位数字大于5，则进一，即保留数字的末位数加1。拟舍弃数字的最左一位数字是5，且其后有非0数字时进一，即保留数字的末位数加1；拟舍弃数字的最左一位数字是5，且其后无数字或均为 0 时，若所保留的末位数字为奇数（1、3、5、7、9）则进一；若所保留的末位数字为偶数（0、2、4、6、8）则舍去。例如将 1.050 修约到一位小数，得1.0；将0.35修约到一位小数，得0.4。

2.2.2 思路框架

对要进行修约的数据进行判断，首先判断舍弃数字的最左一位数字与5的关系，如不为5或为5且其后有非0数字，则直接四舍五入；如为5且其后无数字或均为0时，则再通过对所保留的末位数字奇偶性的判定从而进行舍去或保留的操作。

2.2.3 解决方案及步骤

如把“A1”单元格中的“1.245”修约为两位小数，“B1”单元格为小数位数“2”。

（1）取保留的数，具体公式是：｛=TRUNC（A1，B1）｝；

（2）取要舍弃的数，具体公式是：｛=A1-TRUNC（A1，B1）｝；

（3）把5变为与舍弃的数字同样的位数，如本例中舍弃的“0.005”中的最左一位为第三位小数，则要把“5”处理为同样在第三位小数上，具体公式是：｛=5/POWER（10，（B1+1））｝；

（4）判断须舍弃的数字是否为5且其后有无数字，可以通过第（2步）与第（3）步得到数的差来判断，具体公式是：｛=（A1-TRUNC（A1，B1））-（5/POWER（10，（B1+1）））｝，结果如不为 0，则直接四舍五入；如为 0，则要继续判断保留的最右边一位数的奇偶性；

（5）把保留的数进行整数化，然后通过与2的商是否有余数来判断其奇偶性，具体公式是：｛=MOD（TRUNC（A1，B1）·POWER（10，B1），2）｝，结果如为0，则说明需舍弃的“5”之前为偶数，则不需要进位；如结果不为0，则需进位；

（6）通过IF函数，先判断“5”后有无数字，再判断“5”前的数字奇偶性，在实际操作中，由于excel本身存在的BUG问题，在第（2）步取舍弃的数时，在小数点12位后会出现非0数字，因此在这一步要对结果的小数位数进行设定，考虑到环境监测数据一般保留的小数位数，可以用ROUND函数取10位小数进行修约，可保证最终计算结果的准确，因此最终公式是：｛=IF（ROUND（A1-TRUNC（A1，B1），10）-（5/POWER（10，（B1+1）））=0，（IF（MOD（TRUNC（A1，B1）·POWER（10，B1），2）=0，TRUNC（A1，B1），ROUND（A1，B1））），ROUND（A1，B1））｝。

[1]地表水环境质量评价办法（试行）[Z].北京：环境保护部，2011.

[2]《地表水环境质量标准》（GB3838-2002）[S].国家环境保护总局.北京：中国环境科学出版社，2002.

[3]《数值修约规则与极限数值的表示和判定》（GB/T8170）[S].

X832

A

1004-7344（2016）23-0328-02

2016-8-2

王欢（1980-），男，工程师，主要从事环境监测数据综合分析工作。