環(huán)境監(jiān)測(cè)數(shù)據(jù)標(biāo)準(zhǔn)化修約的Excel函數(shù)實(shí)現(xiàn)

張 然，呂學(xué)研，劉 雷，陳亞男

(1.江蘇省環(huán)境監(jiān)測(cè)中心，南京 210036;2.中國(guó)環(huán)境監(jiān)測(cè)總站國(guó)家環(huán)境保護(hù)環(huán)境監(jiān)測(cè)質(zhì)量控制重點(diǎn)實(shí)驗(yàn)室，北京 100012)

1 前 言

環(huán)境監(jiān)測(cè)數(shù)據(jù)是環(huán)境管理的基礎(chǔ)。隨著大眾對(duì)環(huán)境關(guān)注度的日益提升，環(huán)境監(jiān)測(cè)數(shù)據(jù)呈逐年增加趨勢(shì)，環(huán)境監(jiān)測(cè)數(shù)據(jù)綜合分析人員必須掌握一種甚至幾種數(shù)據(jù)分析軟件，以實(shí)現(xiàn)監(jiān)測(cè)數(shù)據(jù)的快速處理與分析。

Excel軟件，是一款通用的，容易掌握的數(shù)據(jù)處理與分析軟件，在環(huán)境監(jiān)測(cè)[1～3]、水文監(jiān)測(cè)[4]以及其他行業(yè)[5]的數(shù)據(jù)處理中得到廣泛應(yīng)用。數(shù)據(jù)的標(biāo)準(zhǔn)化修約是實(shí)現(xiàn)數(shù)據(jù)標(biāo)準(zhǔn)化管理與應(yīng)用的基礎(chǔ)，如何利用Excel軟件快速實(shí)現(xiàn)數(shù)據(jù)的標(biāo)準(zhǔn)化修約，也是該軟件應(yīng)用過程中較為關(guān)注的熱點(diǎn)之一。目前，利用Excel實(shí)現(xiàn)數(shù)據(jù)標(biāo)準(zhǔn)化修約主要分為兩個(gè)方向：一是利用Excel函數(shù)組合嵌套[1～4]；一是應(yīng)用VBA宏[5～8]。由于VBA需要數(shù)據(jù)分析人員具備一定的程序編寫能力，其推廣應(yīng)用受到限制。通過Excel函數(shù)的組合嵌套，實(shí)現(xiàn)數(shù)據(jù)的標(biāo)準(zhǔn)化修約，對(duì)數(shù)據(jù)分析人員的編程能力要求較低，更具推廣潛力。

本文在深入分析數(shù)據(jù)修約規(guī)則的基礎(chǔ)上，提出了一種在Excel中進(jìn)行數(shù)據(jù)修約的函數(shù)組合嵌套模式，并按照《國(guó)家地表水環(huán)境質(zhì)量監(jiān)測(cè)數(shù)據(jù)修約處理規(guī)則(試行)》，結(jié)合省內(nèi)監(jiān)測(cè)數(shù)據(jù)給出了各個(gè)指標(biāo)的修約函數(shù)公式，以期幫助同業(yè)人員快速實(shí)現(xiàn)環(huán)境監(jiān)測(cè)數(shù)據(jù)的標(biāo)準(zhǔn)化修約。

2 標(biāo)準(zhǔn)化修約規(guī)則的Excel函數(shù)語言實(shí)現(xiàn)

2.1 需調(diào)用的函數(shù)及功能介紹

ROUND函數(shù) 按指定的位數(shù)對(duì)數(shù)值進(jìn)行四舍五入。函數(shù)格式為：ROUND(number,digits)。其中，number為要四舍五入的數(shù)，digits為小數(shù)點(diǎn)后要保留的位數(shù)。

IF函數(shù) 判斷是否滿足某個(gè)條件，如果滿足返回一個(gè)值，如果不滿足則返回另外一個(gè)值。函數(shù)格式為：IF(A,B,C)，其中，A為需要滿足的條件，B為滿足條件后返回的值，C為不滿足條件后返回的值。

AND函數(shù) 邏輯函數(shù)，如果所有參數(shù)值均為邏輯“真(TRUE)”，則返回邏輯“真(TRUE)”，反之返回邏輯“假(FALSE)”，函數(shù)格式為：AND(條件1,條件2,條件3,…)。

INT函數(shù) 將數(shù)值向下取整為最接近的整數(shù)。函數(shù)格式為：INT(number)，其中，number 需要進(jìn)行向下取整的實(shí)數(shù)。

EVEN函數(shù) 將正數(shù)向上舍入到最接近的偶數(shù)，將負(fù)數(shù)向下舍入到最接近的偶數(shù)；如果要舍入的數(shù)值為偶數(shù)，則不舍入，保留原數(shù)值。函數(shù)格式為：EVEN(number)，其中，number 是將進(jìn)行向上取偶的數(shù)值。

FIXED函數(shù) 用定點(diǎn)小數(shù)格式將數(shù)值舍入呈特定位數(shù)并返回帶或不帶逗號(hào)的文本。函數(shù)格式為：FIXED(number,decimals,no_commas)，其中，number為要進(jìn)行四舍五入并轉(zhuǎn)換成文本字符串的數(shù)；decimals為一數(shù)值，用以指定小數(shù)點(diǎn)右邊的小數(shù)位數(shù)，如果忽略，則默認(rèn)decimals=2；no_commas為一邏輯值，如果其值為 FALSE 或被省略，則返回的文本中和往常一樣包含逗號(hào)。

2.2 數(shù)值修約規(guī)則及相關(guān)函數(shù)引用分析

《數(shù)值修約規(guī)則與極限數(shù)值的表示和判斷》(GB/T8170-2008)為科學(xué)技術(shù)與生產(chǎn)活動(dòng)中測(cè)試和計(jì)算得出數(shù)值需要修約時(shí)應(yīng)執(zhí)行的國(guó)家標(biāo)準(zhǔn)。其中，數(shù)值的進(jìn)舍規(guī)則包含以下4個(gè)主要方面。

(1)擬舍棄數(shù)字的最左一位數(shù)字小于5，則舍去，保留其余各位數(shù)字不變；

(2)擬舍棄數(shù)字的最左一位數(shù)字大于5，則進(jìn)一，即保留數(shù)字的末位數(shù)字加1；

(3)擬舍棄數(shù)字的最左一位數(shù)字是5，且其后有非0數(shù)字時(shí)進(jìn)一，即保留數(shù)字的末位數(shù)字加1；

(4)擬舍棄數(shù)字的最左一位數(shù)字是5，且其后無數(shù)字或皆為0時(shí)，若所保留的末位數(shù)字為奇數(shù)(1、3、5、7、9)則進(jìn)一，即保留數(shù)字的末位數(shù)字加1；若所保留的末位數(shù)字為偶數(shù)(0、2、4、6、8)則舍去。

規(guī)則1和2的要求，實(shí)際上表達(dá)的是“四舍六入”的概念，通過常規(guī)的四舍五入就能實(shí)現(xiàn)。通過五入也可以實(shí)現(xiàn)規(guī)則3的全部和規(guī)則4中所保留的末位數(shù)字為奇數(shù)(1、3、5、7、9)則進(jìn)一的要求。規(guī)則1～3以及規(guī)則4中所保留的末位數(shù)字為奇數(shù)(1、3、5、7、9)則進(jìn)一的要求在Excel中對(duì)應(yīng)的修約函數(shù)為ROUND函數(shù)。但是規(guī)則4中 “若所保留的末位數(shù)字為偶數(shù)(0、2、4、6、8)則舍去”會(huì)對(duì)利用ROUND函數(shù)修約產(chǎn)生制約。因此，需要設(shè)計(jì)一個(gè)嵌套函數(shù)，能夠?qū)崿F(xiàn)規(guī)則4中擬舍棄最左一位數(shù)字的判斷和其后數(shù)字的判斷，同時(shí)實(shí)現(xiàn)保留的末位數(shù)字“奇數(shù)進(jìn)一偶數(shù)不進(jìn)”。

進(jìn)一步分析規(guī)則4后發(fā)現(xiàn)，滿足“擬舍棄數(shù)字的最左一位數(shù)字是5，且其后無數(shù)字或皆為0”這一要求的數(shù)值，應(yīng)同時(shí)滿足在乘以2×10n(n為修約后需要保留的小數(shù)位數(shù)，取值為0、1、2、…，下同)并向下取整后，與該數(shù)值直接乘以2×10n相等，但是在乘以1×10n并向下取整后，則小于該數(shù)值直接乘以1×10n，根據(jù)這一特性，可以利用INT函數(shù)實(shí)現(xiàn)數(shù)值取整，并通過AND函數(shù)來實(shí)現(xiàn)該邏輯判斷。同時(shí)，利用EVEN函數(shù)對(duì)整數(shù)數(shù)字末位奇數(shù)向上取偶，偶數(shù)保持不變的功能，對(duì)于保留末位數(shù)字為奇數(shù)的情況，只需將該數(shù)值乘以1×10n后向下取整，再向上偶數(shù)舍入即可；對(duì)于保留末位數(shù)字為偶數(shù)的情況，只需將該數(shù)值乘以1×10n后向下取整即可。綜合以上分析，規(guī)則4中，將需要修約的原數(shù)值乘以1×10n后取整，再進(jìn)行偶數(shù)舍入，再除以10n，即可實(shí)現(xiàn)保留的末位數(shù)字“奇數(shù)進(jìn)一偶數(shù)不進(jìn)”。

由于存在各種數(shù)值可能性判斷，還需要調(diào)用Excel中的判斷函數(shù)IF。同時(shí)可以調(diào)用定點(diǎn)格式函數(shù)FIXED，確保同一修約條件下修約后的數(shù)值位數(shù)一致。

3 特定環(huán)境監(jiān)測(cè)數(shù)據(jù)修約要求

《國(guó)家地表水環(huán)境質(zhì)量監(jiān)測(cè)數(shù)據(jù)修約處理規(guī)則(試行)》對(duì)除水溫和糞大腸菌外的24項(xiàng)指標(biāo)進(jìn)行了修約小數(shù)限定，具體見下表，當(dāng)修約后結(jié)果為0時(shí)，繼續(xù)向下修約至第一位有效數(shù)字。本文基于水溫和糞大腸菌群的數(shù)據(jù)特點(diǎn)，限定水溫和糞大腸菌群的保留小數(shù)位數(shù)分別為1位和0位。

表1 監(jiān)測(cè)指標(biāo)數(shù)值修約要求Tab.1 Monitoring data rounding requirements

4 指標(biāo)修約函數(shù)

4.1 水溫和pH值

由于水溫可以出現(xiàn)0值，因此，在修約過程中不需要考慮修約結(jié)果為0的情形。據(jù)此設(shè)定水溫的修約函數(shù)公式為：

=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為水溫?cái)?shù)據(jù)存放的單元格編號(hào)(后續(xù)所列的各函數(shù)公式中的C2、F2、H2，I2……等標(biāo)識(shí)均為EXCEL數(shù)據(jù)存放的單元格編號(hào))。該函數(shù)的功能是，首先用第一個(gè)IF函數(shù)判斷B2單元格是否為空，如果為空，則返回也為空；如果B2單元格不為空，則用第二個(gè)IF函數(shù)判斷B2單元格中的數(shù)值滿足哪條修約規(guī)則。AND(INT(B2*20)=(B2*20),INT(B2*10)<(B2*10))對(duì)應(yīng)的是“擬舍棄數(shù)字的最左一位數(shù)字是5，且其后無數(shù)字或皆為0”這一判斷條件，如果成立說明5的后面不為0，否則5的后面為0。EVEN(INT(B2*10))/10實(shí)現(xiàn)的是保留的末位數(shù)字“奇數(shù)進(jìn)一偶數(shù)不進(jìn)”這一要求。ROUND(B2,1)實(shí)現(xiàn)規(guī)則1～3條件下數(shù)值的修約。緊隨其后的1，是實(shí)現(xiàn)修約結(jié)果的一位小數(shù)定點(diǎn)格式。*1是為了實(shí)現(xiàn)修約后的數(shù)據(jù)以數(shù)值格式呈現(xiàn)，可以直接參與統(tǒng)計(jì)分析。也就是說，F(xiàn)IXED函數(shù)與*1可以同時(shí)存在，也可以同時(shí)不存在，返回的數(shù)據(jù)都是數(shù)值格式的，可以直接參與統(tǒng)計(jì)分析；當(dāng)FIXED函數(shù)單獨(dú)存在時(shí)，修約后的數(shù)據(jù)將是文本格式，不便于一些統(tǒng)計(jì)分析的直接使用。

由于環(huán)境水體存在一定的緩沖能力，自然環(huán)境下，水體的pH值按照1位小數(shù)修約的結(jié)果不可能為0，故其修約函數(shù)可以采用與水溫相同的算法(具體如下)，不同點(diǎn)僅體現(xiàn)在數(shù)值存儲(chǔ)列號(hào)的變化上。

=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 有機(jī)污染綜合指標(biāo)

高錳酸鹽指數(shù)、化學(xué)需氧量和五日生化需氧量是表征水體有機(jī)污染的綜合指標(biāo)?！秶?guó)家地表水環(huán)境質(zhì)量監(jiān)測(cè)網(wǎng)監(jiān)測(cè)任務(wù)作業(yè)指導(dǎo)書(試行)》(以下簡(jiǎn)稱“作業(yè)指導(dǎo)書”)中，3個(gè)指標(biāo)檢出限的一半分別為0.25mg/L、2mg/L和0.25mg/L。省內(nèi)監(jiān)測(cè)數(shù)據(jù)統(tǒng)計(jì)結(jié)果表明，高錳酸鹽指數(shù)、化學(xué)需氧量和五日生化需氧量的最小統(tǒng)計(jì)值分別為0.8mg/L、1.2mg/L和0.1mg/L。因此，以0.1為基準(zhǔn)設(shè)計(jì)修約函數(shù)，即可實(shí)現(xiàn)3個(gè)指標(biāo)的1位小數(shù)修約要求。高錳酸鹽指數(shù)、化學(xué)需氧量和五日生化需氧量的修約函數(shù)依次分別如下：

=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 溶解氧和透明度

根據(jù)省內(nèi)監(jiān)測(cè)數(shù)據(jù)統(tǒng)計(jì)結(jié)果，溶解氧和透明度目前的最小統(tǒng)計(jì)值分別為0.18mg/L和5cm。因此，以0.1為基準(zhǔn)設(shè)計(jì)修約函數(shù)，同樣可實(shí)現(xiàn)這2個(gè)指標(biāo)的1位小數(shù)修約要求。溶解氧和透明度的修約函數(shù)依次分別如下：

=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 揮發(fā)酚、硫化物、總氮、糞大腸菌群和葉綠素a

進(jìn)一步分析發(fā)現(xiàn)，揮發(fā)酚、硫化物、總氮、糞大腸菌群和葉綠素a均可以0.1為基準(zhǔn)設(shè)計(jì)修約函數(shù)，僅僅是修約小數(shù)位數(shù)的不同而已。據(jù)此，設(shè)計(jì)揮發(fā)酚、硫化物、總氮、糞大腸菌群和葉綠素a的修約函數(shù)公式依次分別如下：

=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 石油類

作業(yè)指導(dǎo)書中，石油類分析方法的檢出限為0.01mg/L，即該方法的統(tǒng)計(jì)數(shù)據(jù)大于等于0.005mg/L，省內(nèi)石油類指標(biāo)最小統(tǒng)計(jì)值為0.001mg/L。按照2位小數(shù)的修約要求，修約結(jié)果均為0。因此，石油類的小數(shù)修約位數(shù)應(yīng)該為3位。據(jù)此，設(shè)計(jì)石油類的修約函數(shù)公式為：

=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 氨氮

根據(jù)省內(nèi)監(jiān)測(cè)數(shù)據(jù)統(tǒng)計(jì)結(jié)果，氨氮目前的最小統(tǒng)計(jì)值為0.002 5mg/L，小于作業(yè)指導(dǎo)書中方法檢出限的一半0.015mg/L。如果按照2位小數(shù)的要求修約，則會(huì)出現(xiàn)修約結(jié)果為0的情況，因此需要增加一組針對(duì)修約結(jié)果為零的修約函數(shù)。根據(jù)氨氮的最小統(tǒng)計(jì)值，需要修約到小數(shù)點(diǎn)后第3位，才能保證修約結(jié)果不為0。據(jù)此，設(shè)定氨氮的修約函數(shù)公式為：

=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單元格內(nèi)的數(shù)值是否滿足2位小數(shù)的修約要求，因?yàn)橹挥性贒2單元格內(nèi)的數(shù)值大于0.005的情況下，才能滿足2位小數(shù)的修約要求；由于0.005按照2位小數(shù)修約的結(jié)果仍為0，故[0.0025,0.005]區(qū)間的數(shù)值均需要按照3位小數(shù)來修約。

4.7 陰離子表面活性劑

作業(yè)指導(dǎo)書中，陰離子表面活性劑檢出限的數(shù)量級(jí)與氨氮相同，且數(shù)值修約要求也與氨氮相同。因此，這2個(gè)指標(biāo)的修約函數(shù)應(yīng)該存在相同之處。目前，省內(nèi)陰離子表面活性劑的最小統(tǒng)計(jì)值為0.001mg/L，小于作業(yè)指導(dǎo)書中方法檢出限的一半0.025mg/L。據(jù)此，以0.001為基準(zhǔn)設(shè)定陰離子表面活性劑的修約函數(shù)公式為：

=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 總磷

作業(yè)指導(dǎo)書中，總磷分析方法的檢出限為0.01mg/L，省內(nèi)目前的最小統(tǒng)計(jì)值為0.005mg/L，與檢出限的一半相等，同時(shí)，按照3位小數(shù)修約也不會(huì)出現(xiàn)結(jié)果為0的情況。據(jù)此，以0.005為基準(zhǔn)，設(shè)定總磷的修約函數(shù)公式為：

=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 氟化物和氰化物

作業(yè)指導(dǎo)書中，氟化物兩種分析方法的檢出限分別為0.006mg/L和0.05mg/L。按照2位小數(shù)修約，則最小統(tǒng)計(jì)值0.003mg/L的修約結(jié)果為0。因此，以0.003為基準(zhǔn)設(shè)定陰離子表面活性劑的修約函數(shù)公式為：

=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)

作業(yè)指導(dǎo)書中，氰化物同樣給出了檢出限不同的2種分析方法，且其中一種方法檢出限的一半為0.000 5mg/L，根據(jù)3位小數(shù)修約的結(jié)果會(huì)為0。同時(shí)，省內(nèi)氰化物的最小統(tǒng)計(jì)值為0.000 1mg/L，與0.000 5具有相同的修約結(jié)果制約能力。因此，以0.000 1為基準(zhǔn)設(shè)計(jì)氰化物的修約函數(shù)公式為：

=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 重金屬

六價(jià)鉻：分析發(fā)現(xiàn)，六價(jià)鉻的修約函數(shù)式僅僅是與氨氮和陰離子表面活性劑的修約函數(shù)存在數(shù)量級(jí)上的差異。因?yàn)榱鶅r(jià)鉻的修約小數(shù)為3位，而檢出限的一半0.002mg/L和省內(nèi)最小統(tǒng)計(jì)值0.000 5 mg/L分別是比氨氮和陰離子表面活性劑低1個(gè)數(shù)量級(jí)。據(jù)此，以0.000 1為基準(zhǔn)設(shè)計(jì)六價(jià)鉻的修約函數(shù)公式為：

=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)

硒：作業(yè)指導(dǎo)書中，硒的2種分析方法的檢出限均為0.000 4mg/L，如果取其一半0.000 2mg/L進(jìn)行3位小數(shù)修約，結(jié)果為0，故應(yīng)該按照4位小數(shù)修約。目前省內(nèi)硒的最小統(tǒng)計(jì)值為0.000 025mg/L，需要按照5位小數(shù)修約結(jié)果才不為0。因此，以0.000 01為基準(zhǔn)，設(shè)計(jì)硒的修約函數(shù)公式為：

=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)

砷：作業(yè)指導(dǎo)書中，砷的2種分析方法檢出限的一半分別為0.000 15mg/L和0.000 1mg/L。按照2位小數(shù)修約，則數(shù)值的修約結(jié)果可能均為0。因此，砷的數(shù)值修約小數(shù)應(yīng)該限定為4位。省內(nèi)目前砷的最小統(tǒng)計(jì)值為0.000 02mg/L。據(jù)此，以0.000 01為基準(zhǔn)設(shè)計(jì)砷的修約函數(shù)公式為：

=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位小數(shù)修約，則結(jié)果均可能為0。因此，砷的數(shù)值修約小數(shù)應(yīng)該限定為5位。5位小數(shù)的修約限定同時(shí)也能保證省內(nèi)最小統(tǒng)計(jì)值0.000 01 mg/L的修約結(jié)果不為0。據(jù)此，以0.000 01為基準(zhǔn)設(shè)計(jì)砷的修約函數(shù)公式為：

=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)

銅：作業(yè)指導(dǎo)書中，銅給出了3種推薦方法，檢出限的一半分別為0.000 04mg/L、0.003mg/L和0.000 5mg/L。按照2位小數(shù)的修約要求，則3種方法的最小統(tǒng)計(jì)值均可能為0。因此，銅的小數(shù)修約位數(shù)最低應(yīng)該設(shè)置為3位。以省內(nèi)銅的最小統(tǒng)計(jì)值0.000 01mg/L為基準(zhǔn)，設(shè)計(jì)銅的修約函數(shù)公式，可以同時(shí)滿足3種分析方法的數(shù)據(jù)修約要求。銅的修約函數(shù)公式為：

=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)

鉛：雖然作業(yè)指導(dǎo)書中鉛的推薦方法比銅少1種，但是其中石墨爐原子吸收分光光度法方法檢出限的一半根據(jù)3位小數(shù)修約不為0，而電感耦合等離子體質(zhì)譜法檢出限的一半(0.000 045mg/L)與省內(nèi)鉛的最小統(tǒng)計(jì)值(0.000 02mg/L)的修約結(jié)果均為0，即使按照4位小數(shù)修約結(jié)果也均為0。因此，鉛的修約位數(shù)需要橫跨3～5。從而，鉛的修約函數(shù)公式與銅的相同，具體如下：

=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)

汞：作業(yè)指導(dǎo)書中，汞的原子熒光法檢出限為0.000 04mg/L，冷原子吸收法的檢出限為0.000 01 mg/L，如果以檢出限的一半?yún)⑴c統(tǒng)計(jì)，按照5位小數(shù)修約，則冷原子吸收法的數(shù)值修約可能為0。省內(nèi)目前汞的最小統(tǒng)計(jì)值為0.000 002mg/L，據(jù)此設(shè)定汞的修約函數(shù)公式，則能夠保證冷原子吸收法的數(shù)值修約結(jié)果不為0。因此，以0.000 001為基準(zhǔn)設(shè)計(jì)汞的修約函數(shù)公式為：

=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)

鋅：作業(yè)指導(dǎo)書中，鋅的3種分析方法的檢出限分別為0.05mg/L、0.004mg/L和0.000 7mg/L，以檢出限的一半按照2位小數(shù)修約，則有2種方法的結(jié)果為0。這2種方法需要分別按照3位和4位小數(shù)進(jìn)行修約，而省內(nèi)鋅的最小統(tǒng)計(jì)值為0.000 05 mg/L，需要按照5位小數(shù)修約才不至于結(jié)果為0。因此，以0.000 01為基準(zhǔn)設(shè)計(jì)鋅的修約函數(shù)公式為：

=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)

采用省內(nèi)2017年省控?cái)嗝娓髦笜?biāo)的月均值和年均值，對(duì)以上各指標(biāo)的修約結(jié)果進(jìn)行驗(yàn)證。結(jié)果表明，各指標(biāo)均按照預(yù)期的設(shè)想實(shí)現(xiàn)了數(shù)值標(biāo)準(zhǔn)化修約。

5 結(jié) 語

本文在深入分析數(shù)據(jù)修約規(guī)則的基礎(chǔ)上，采用Excel 2010中的IF、EVEN、INT、ROUND和FIXED函數(shù)，提出了一種數(shù)據(jù)修約函數(shù)組合嵌套模式，并根據(jù)地表水環(huán)境質(zhì)量監(jiān)測(cè)各項(xiàng)指標(biāo)的具體修約要求，結(jié)合省內(nèi)實(shí)際監(jiān)測(cè)數(shù)據(jù)，給出了具體的修約函數(shù)公式。

在修約函數(shù)公式設(shè)計(jì)過程中，發(fā)現(xiàn)重金屬指標(biāo)修約函數(shù)公式的設(shè)計(jì)是工作的難點(diǎn)。主要是因?yàn)椴煌治龇椒ǖ臋z出限存在數(shù)量級(jí)上的差異，導(dǎo)致按照規(guī)定的小數(shù)位數(shù)修約難以避免0值的出現(xiàn)。同時(shí)，部分重金屬指標(biāo)原定的修約小數(shù)位數(shù)還有待商榷。比如，硒、砷、鎘、銅，按照原定的小數(shù)位數(shù)進(jìn)行修約，無論哪種分析方法，檢出限一半的修約結(jié)果均為0。石油類指標(biāo)也面臨同樣的問題。因此，建議調(diào)整《國(guó)家地表水環(huán)境質(zhì)量監(jiān)測(cè)數(shù)據(jù)修約處理規(guī)則(試行)》中這幾項(xiàng)指標(biāo)的修約位數(shù)。或者，從簡(jiǎn)便、快捷實(shí)現(xiàn)數(shù)據(jù)修約以及促進(jìn)各監(jiān)測(cè)機(jī)構(gòu)能力升級(jí)提升的角度考慮，建議以靈敏度最高的分析方法的檢出限設(shè)置修約小數(shù)位數(shù)。