利用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-），男，工程師，主要從事環境監測數據綜合分析工作。