王歡 耿天召

（安徽省環(huán)境監(jiān)測中心站 安徽合肥 230071）

利用Excel中函數(shù)編程實現(xiàn)地表水環(huán)境質(zhì)量的自動評價

王歡 耿天召

（安徽省環(huán)境監(jiān)測中心站 安徽合肥 230071）

在地表水環(huán)境質(zhì)量評價中，要首先對監(jiān)測數(shù)據(jù)進行標(biāo)準(zhǔn)化處理，如對低于檢出限的數(shù)據(jù)取其檢出限的1/2，對數(shù)字按照“四舍六入五留雙”的修約規(guī)則進行數(shù)據(jù)修約；再對單個指標(biāo)進行水質(zhì)類別的判斷，從而得出綜合評價結(jié)果。文章利用Excel軟件中自帶的函數(shù)功能，把各類函數(shù)進行組合使用，在地表水環(huán)境質(zhì)量評價的過程中，實現(xiàn)對未檢出數(shù)據(jù)的自動處理及對數(shù)據(jù)的自動修約，提高工作效率以及減少人為錯誤。

地表水；Excel；環(huán)境質(zhì)量

目前，環(huán)保部門在進行地表水環(huán)境質(zhì)量的分析評價中，依據(jù)《地表水環(huán)境質(zhì)量評價辦法（試行）》[1]來進行地表水環(huán)境質(zhì)量的評價。對于單次監(jiān)測中低于檢出限的項目，取其檢出限的1/2參與評價，并且要對數(shù)據(jù)進行修約，手工處理效率低且容易出錯。筆者將運用Excel中自帶函數(shù)，實現(xiàn)環(huán)境監(jiān)測數(shù)據(jù)的自動處理。

1 常用函數(shù)

1.1 IF函數(shù)

IF（logical_test，value_if_ture，value_if_false），判斷是否滿足一個條件，如果滿足返回一個值，如果不滿足則返回另一個值。該函數(shù)可以多層嵌套使用。

1.2 ISNUMBER函數(shù)

ISNUMBER（value），檢測一個值是否為數(shù)值，返回ture或false。

1.3 LEN函數(shù)

LEN（test），返回文本字符串中的字符個數(shù)。

1.4 MID函數(shù)

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

1.5 ROUND函數(shù)

ROUND（number，num_digits），按指定的位數(shù)對數(shù)值進行四舍五入。

1.6 TRUNC函數(shù)

TRUNC（number，num_digits），將數(shù)字截為整數(shù)或保留指定位數(shù)的小數(shù)。

1.7 MOD函數(shù)

MOD（number，divisor），返回兩數(shù)相除的余數(shù)。

1.8 POWER函數(shù)

POWER（number，power），返回某數(shù)的乘冪。

以上函數(shù)為數(shù)據(jù)處理所常用的一些基本函數(shù)，可以單獨使用，但在大多數(shù)情況下都是數(shù)個函數(shù)組合使用，其中IF函數(shù)的使用頻率最高。

2 數(shù)據(jù)處理實例

2.1 未檢出數(shù)據(jù)自動取其檢出限的1/2

2.1.1 問題分析

當(dāng)監(jiān)測指標(biāo)未檢出時，要取其1/2檢出限進行計算。在數(shù)據(jù)表中以“檢出限+L”表示，例如石油類未檢出時填寫“0.01L”，在進行計算時，要將其變成“0.005”。

2.1.2 思路框架

當(dāng)數(shù)據(jù)為未檢出數(shù)據(jù)時，數(shù)據(jù)格式為字符型，否則為數(shù)字型。因此首先判斷單元格中字符串格式是否為數(shù)字型，如為非數(shù)字型數(shù)據(jù)，則提取單元格中的數(shù)字并取1/2。

2.1.3 解決方案及步驟

（1）確定單元格中字符串的類型，如為數(shù)值型則直接返回該數(shù)值，如為非數(shù)值型，則返回處理后的數(shù)值。利用邏輯函數(shù)IF嵌套ISNUMBER檢測單元格中字符串是否為數(shù)值型的真假，真則返回該數(shù)據(jù)，如果不是則返回單元格中提取后的數(shù)值的1/2。

（2）對于未檢出數(shù)據(jù)，首先確定單元格中字符串的長度。利用LEN函數(shù)計算出單元格中A1中字符串中字符的個數(shù)，即字符串中最后一個字符在字符串中的位置。

具體公式為：

｛=LEN（A1）｝。假定數(shù)據(jù)為“0.01L”，則返回結(jié)果為“5”。

（3）提取“L”之前的數(shù)字。利用MID函數(shù)提取文本字符串中從指定位置開始的特定數(shù)目的字符。由于地表水未檢出數(shù)據(jù)為檢出限后加L，則提取的字符長度為單元格中第一個數(shù)字位置起到最后一個數(shù)據(jù)位置止-1的字符。如0.01L，字符長度為5，提取的字符長度則為5-1，即從第一個數(shù)字起的4個數(shù)字。

由于函數(shù)MID返回的字符是文本，要將文本轉(zhuǎn)化成數(shù)值型數(shù)據(jù)，可以用函數(shù)VALUE，也可以同等功能地用符號“--”或“+0”或“-0”簡化表示，這里用“--”表示。具體公式是：

｛=--MID（A1，1，4）｝，與 LEN 函數(shù)聯(lián)用，并取 1/2 值，公式是：

｛=--MID（A1，1，LEN（A1）-1）/2｝，返回結(jié)果為“0.005”。

（4）以上幾個函數(shù)連用，具體公式是：

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

2.2 數(shù)據(jù)小數(shù)位數(shù)的自動修約

2.2.1 問題分析

環(huán)境監(jiān)測數(shù)據(jù)的數(shù)據(jù)統(tǒng)計結(jié)果要按照《數(shù)值修約規(guī)則與極限數(shù)值的表示和判定》（GB/T8170）[3]進行小數(shù)位數(shù)的修約，基本原則為“四舍六入五留雙”。擬舍棄數(shù)字的最左一位數(shù)字小于5，則舍去，保留其余各位數(shù)字不變；擬舍棄數(shù)字的最左一位數(shù)字大于5，則進一，即保留數(shù)字的末位數(shù)加1。擬舍棄數(shù)字的最左一位數(shù)字是5，且其后有非0數(shù)字時進一，即保留數(shù)字的末位數(shù)加1；擬舍棄數(shù)字的最左一位數(shù)字是5，且其后無數(shù)字或均為 0 時，若所保留的末位數(shù)字為奇數(shù)（1、3、5、7、9）則進一；若所保留的末位數(shù)字為偶數(shù)（0、2、4、6、8）則舍去。例如將 1.050 修約到一位小數(shù)，得1.0；將0.35修約到一位小數(shù)，得0.4。

2.2.2 思路框架

對要進行修約的數(shù)據(jù)進行判斷，首先判斷舍棄數(shù)字的最左一位數(shù)字與5的關(guān)系，如不為5或為5且其后有非0數(shù)字，則直接四舍五入；如為5且其后無數(shù)字或均為0時，則再通過對所保留的末位數(shù)字奇偶性的判定從而進行舍去或保留的操作。

2.2.3 解決方案及步驟

如把“A1”單元格中的“1.245”修約為兩位小數(shù)，“B1”單元格為小數(shù)位數(shù)“2”。

（1）取保留的數(shù)，具體公式是：｛=TRUNC（A1，B1）｝；

（2）取要舍棄的數(shù)，具體公式是：｛=A1-TRUNC（A1，B1）｝；

（3）把5變?yōu)榕c舍棄的數(shù)字同樣的位數(shù)，如本例中舍棄的“0.005”中的最左一位為第三位小數(shù)，則要把“5”處理為同樣在第三位小數(shù)上，具體公式是：｛=5/POWER（10，（B1+1））｝；

（4）判斷須舍棄的數(shù)字是否為5且其后有無數(shù)字，可以通過第（2步）與第（3）步得到數(shù)的差來判斷，具體公式是：｛=（A1-TRUNC（A1，B1））-（5/POWER（10，（B1+1）））｝，結(jié)果如不為 0，則直接四舍五入；如為 0，則要繼續(xù)判斷保留的最右邊一位數(shù)的奇偶性；

（5）把保留的數(shù)進行整數(shù)化，然后通過與2的商是否有余數(shù)來判斷其奇偶性，具體公式是：｛=MOD（TRUNC（A1，B1）·POWER（10，B1），2）｝，結(jié)果如為0，則說明需舍棄的“5”之前為偶數(shù)，則不需要進位；如結(jié)果不為0，則需進位；

（6）通過IF函數(shù)，先判斷“5”后有無數(shù)字，再判斷“5”前的數(shù)字奇偶性，在實際操作中，由于excel本身存在的BUG問題，在第（2）步取舍棄的數(shù)時，在小數(shù)點12位后會出現(xiàn)非0數(shù)字，因此在這一步要對結(jié)果的小數(shù)位數(shù)進行設(shè)定，考慮到環(huán)境監(jiān)測數(shù)據(jù)一般保留的小數(shù)位數(shù)，可以用ROUND函數(shù)取10位小數(shù)進行修約，可保證最終計算結(jié)果的準(zhǔn)確，因此最終公式是：｛=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]地表水環(huán)境質(zhì)量評價辦法（試行）[Z].北京：環(huán)境保護部，2011.

[2]《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》（GB3838-2002）[S].國家環(huán)境保護總局.北京：中國環(huán)境科學(xué)出版社，2002.

[3]《數(shù)值修約規(guī)則與極限數(shù)值的表示和判定》（GB/T8170）[S].

X832

A

1004-7344（2016）23-0328-02

2016-8-2

王歡（1980-），男，工程師，主要從事環(huán)境監(jiān)測數(shù)據(jù)綜合分析工作。