淺談Excel函數(shù)在水質(zhì)資料整編中的應(yīng)用

李海帆

（陜西省商洛水文水資源勘測(cè)局，陜西 商洛 726000）

0 引言

目前，水質(zhì)資料整編還沒(méi)有建立一套完善的資料整匯編系統(tǒng)，大量的數(shù)據(jù)需要手工計(jì)算并求得特征值，不僅費(fèi)時(shí)費(fèi)力，而且手工計(jì)算在輸入過(guò)程中經(jīng)常發(fā)生錯(cuò)誤，準(zhǔn)確率得不到保證。

水質(zhì)資料整編中需要計(jì)算的特征值包括有每個(gè)斷面全年的樣品總數(shù)、檢出率、超標(biāo)率、實(shí)測(cè)范圍（包括最小值、最大值）、最大超標(biāo)倍數(shù)、最大值出現(xiàn)日期、年平均值等共7項(xiàng)8個(gè)參數(shù)，本文以化學(xué)需氧量項(xiàng)目為例，通過(guò)使用成果表（其行列分布見(jiàn)圖1）中的數(shù)據(jù)，求得特征值表（其行列分布見(jiàn)圖2），闡述Excel在求資料整編特征值中的應(yīng)用方法。

圖1 水質(zhì)資料整編成果表各參數(shù)在Excel中的行列分布圖

圖2 水質(zhì)資料整編特征值表各參數(shù)在Excel中的行列分布圖

1 所用Excel函數(shù)介紹

Excel是Microsoft office公司推出的辦公套件中的一個(gè)重要組件,使用它既可以制作電子表格,也可以進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計(jì)分析和輔助決策操作,被廣泛應(yīng)用于管理、統(tǒng)計(jì)、財(cái)經(jīng)、金融等眾多領(lǐng)域。Excel包含了大量的內(nèi)置函數(shù)，本文主要應(yīng)用COUNTA、COUNTIF、IF、MIN、MAX、MATCH、INDEX、LEFT、COLUMN 函數(shù)，來(lái)計(jì)算水質(zhì)資料整編中涉及的8個(gè)特征值參數(shù)。

1.1 COUNTA函數(shù)

COUNTA函數(shù)用于計(jì)算區(qū)域中所有不為空的單元格的個(gè)數(shù)。通過(guò)計(jì)算非空單元格數(shù)，即可統(tǒng)計(jì)出當(dāng)前項(xiàng)目當(dāng)年的樣品總數(shù)。

1.2 COUNIF函數(shù)

COUNTIF函數(shù)用于對(duì)單元格區(qū)域中滿足單個(gè)指定條件的單元格進(jìn)行計(jì)數(shù)。此函數(shù)可以項(xiàng)目的方法檢出限為條件，統(tǒng)計(jì)大于檢出限的個(gè)數(shù)；也可以目標(biāo)水質(zhì)類別為條件，統(tǒng)計(jì)超標(biāo)個(gè)數(shù)。

1.3 IF函數(shù)

IF函數(shù)來(lái)完成因指定的條件不同而需要返回不同結(jié)果的計(jì)算處理。以大于檢出限樣品個(gè)數(shù)為判斷條件，與樣品個(gè)數(shù)進(jìn)行比較，如果相等，則最小值為小于檢出限，否則調(diào)用MIN函數(shù)求最小值。

1.4 MAX函數(shù)

MAX函數(shù)用于計(jì)算某一組數(shù)據(jù)中的最大值。以項(xiàng)目的所有測(cè)定值為參數(shù)，計(jì)算其全年最大值。

1.5 MIN函數(shù)

與MAX函數(shù)的功能相反,MN函數(shù)用于計(jì)算一組數(shù)值中的最小值。以項(xiàng)目的所有測(cè)定值為參數(shù)，計(jì)算其全年最小值。當(dāng)最小值為小于檢出限，則與IF函數(shù)嵌套，求出全年最小值。

1.6 MATCH函數(shù)

MATCH函數(shù)用于在指定范圍單元格中搜索特定的項(xiàng)，然后返回該項(xiàng)在此區(qū)域中的相對(duì)位置。通過(guò)MATCH函數(shù)在成果表中查找最大值，并返回其所在位置。

1.7 INDEX函數(shù)

INDEX函數(shù)用于返回指定位置中的內(nèi)容。INDEX函數(shù)與LEFT、COLUMN、MATCH嵌套使用可以求出最大檢出日期。

1.8 COLUMN函數(shù)

Excel中默認(rèn)情況下以字母的形式表示列號(hào),可以使用COLUMN函數(shù)返回指定單元格引用的列號(hào)。通過(guò)COLUMN函數(shù)可以求出最大值出現(xiàn)的日期的列號(hào)。

1.9 LEFT函數(shù)

LEFT函數(shù)能夠從文本左側(cè)起提取文本中的第一個(gè)或前幾個(gè)字符。由于成果表中年月日處于一個(gè)單元格內(nèi)，使用LEFT函數(shù)截取最大值出現(xiàn)日，再通過(guò)字符串拼接，即可得到規(guī)范所要求的格式。

2 Excel函數(shù)的具體應(yīng)用

通過(guò)對(duì)上述函數(shù)的綜合運(yùn)用，可快速計(jì)算出其特征值，為簡(jiǎn)潔起見(jiàn)，下文中的引用均為成果表中的單元格，在使用時(shí)只需在所引用單元格前加上“成果表!”即可。

2.1 計(jì)算樣品總數(shù)

計(jì)算樣品總數(shù)，通過(guò)統(tǒng)計(jì)所有測(cè)次中非空單元格的個(gè)數(shù)，用COUNTA函數(shù)即可。在特征值表的I4單元格輸入=COUNTA(N4:N15)算出化學(xué)需氧量樣品總數(shù)為12。

2.2 計(jì)算檢出率

通過(guò)COUNTIF函數(shù)計(jì)算給定區(qū)域的單元格數(shù)目。計(jì)算化學(xué)需氧量檢出率，因《水質(zhì) 化學(xué)需氧量的測(cè)定 重鉻酸鹽法》（HJ 828-2017）規(guī)定化學(xué)需氧量的檢出限為 4 mg/L，即COUNTIF（N4:N15,”＞=4”）。然后，檢出數(shù)除以樣品總數(shù)乘以100%即可得到檢出率，即在特征值表的I5單元格中輸入=COUNTIF（N4:N15,”＞=4”）/COUNTA(N4:N15)*100，得到化學(xué)需氧量檢出率為75.0。

2.3 計(jì)算超標(biāo)率

計(jì)算超標(biāo)率，通過(guò)COUNTIF函數(shù)計(jì)算超標(biāo)數(shù)。根據(jù)《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》（GB 3838-2002），在整編中超過(guò)每個(gè)參數(shù)的Ⅲ類標(biāo)準(zhǔn)即為超標(biāo)，《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》（GB 3838-2002）化學(xué)需氧量的Ⅲ類標(biāo)準(zhǔn)限值為20 mg/L，即COUNTIF（N4:N15,”＞20”）。超標(biāo)數(shù)除以樣品總數(shù)乘以100%即可得到超標(biāo)率，即在表 2的 I6單元格中輸入 =COUNTIF（N4:N15,”＞20”）/COUNTA(N4:N15)*100，得到超標(biāo)率為0。當(dāng)檢出率為0時(shí)，根據(jù)《水環(huán)境監(jiān)測(cè)規(guī)范》（SL 219-2013）要求，超標(biāo)率單元格為空，此時(shí)刪除超標(biāo)率即可。當(dāng)檢出率不為0且小于100%時(shí)保留1位小數(shù)，等于100%是保留整數(shù)。經(jīng)計(jì)算化學(xué)需氧量的超標(biāo)率為0。

2.4 計(jì)算實(shí)測(cè)范圍

計(jì)算實(shí)測(cè)范圍包括最小值和最大值，其求法如下：

（1）求最小值：原理如下，使用IF函數(shù)判斷，當(dāng)大于等于檢出限樣品的個(gè)數(shù)等于樣品總數(shù)時(shí)，則表明全部檢出。使用MIN函數(shù)求出最小值，當(dāng)大于等于檢出限樣品的個(gè)數(shù)不等于樣品總數(shù)時(shí)，則表明有未檢出，則最小值為小于檢出限，即在表2的I7單元格中輸入=IF(COUNTIF(N8:N15,"＞=4")=COUNTA(N4:N15),MIN(N4:N15),"＜4")，得到最小值為＜4。經(jīng)計(jì)算化學(xué)需氧量實(shí)測(cè)范圍中最小值為為＜4。

（2）求最大值：使用用MAX函數(shù)求出最大值，即在表2的I8單元格中輸入=MAX(N4:N15)。經(jīng)計(jì)算化學(xué)需氧量最大值為16。

當(dāng)全部未檢出時(shí)，則最大值顯示＜檢出限，根據(jù)《水環(huán)境監(jiān)測(cè)規(guī)范》（SL219-2013）要求，全部未檢出時(shí)，最大值和最小值單元格為空，此時(shí)刪除最大值和最小值即可。

2.5 計(jì)算最大超標(biāo)倍數(shù)

當(dāng)超標(biāo)率不為0時(shí)，則用最大值除以Ⅲ類水標(biāo)準(zhǔn)值減1即可，即在I8單元格輸入“=MAX(N4:N15)/20-1”，得到最大值超標(biāo)倍數(shù)。若全部未檢出，此單元格為空。本例中化學(xué)需氧量未超標(biāo)，所以超標(biāo)倍數(shù)單元格為空。

2.6 最大值出現(xiàn)日期

通過(guò)MATCH函數(shù)求出最大值所在的行數(shù)，即最大值所在的月份，然后通過(guò)INDEX函數(shù)和MATCH函數(shù)的套用，求出最大值所對(duì)應(yīng)的的分析日期所在的單元格，并通過(guò)應(yīng)用LEFT函數(shù)截取分析日期的前一個(gè)字符，即最大值出現(xiàn)日，再后通過(guò)&字符，將最大值出現(xiàn)月和最大值出現(xiàn)日連接，即為最大值出現(xiàn)日期，在I9單元格中輸入=MATCH(MAX(N4:N15),N4:N15,0)&""&LEFT(INDEX(A4:BD15,MATCH(MAX(N4:N15),N4:N15,0),COLUMN(N4)),1)即可求得最大值出現(xiàn)日期。經(jīng)使用函數(shù)得出化學(xué)需氧量最大值出現(xiàn)日期為502。

2.7 計(jì)算年平均值

《水環(huán)境監(jiān)測(cè)規(guī)范》（SL 219-2013）中對(duì)年平均值的要求是：小于檢出限的按1/2檢出限參加計(jì)算；如果平均值小于方法檢出限但超過(guò)小數(shù)保留位數(shù)時(shí)，以1/2檢出限作為年平均值。

先用SUMIF函數(shù)求出檢出單元格之和，然后再用COUNTIF函數(shù)求出未檢出單元格個(gè)數(shù)后乘以1/2檢出限，與之前檢出單元格相加，除以非空單元格數(shù)即為平均值；再用IF函數(shù)判斷平均值是否大于檢出，如果大于檢出限，則此值為年平均值，否則以1/2檢出限作為平均值，因此在I9單元格中輸入=IF((SUMIF(N4:N15,"＞=4",N4:N5)+COUNTIF(N4:N15,"＜4")*2)/COUNTA(N4:N15)＞=4,(SUMIF(N4:N15,"＞=4",N4:N5)+COUNTIF(N4:N15,"＜4")*2)/COUNTA(N4:N15),2)。經(jīng)計(jì)算求得化學(xué)需氧量平均值為9。

3 方法改進(jìn)探討

3.1 關(guān)于方法檢出限

近幾年國(guó)家相關(guān)部門對(duì)環(huán)境保護(hù)日益重視，對(duì)監(jiān)測(cè)標(biāo)準(zhǔn)的修訂頻率加大，當(dāng)方法檢出限改變后，往往所有公式中都得改檢出限。因此，在工作簿中增加一個(gè)檢出限表，需要用到檢出限時(shí)直接調(diào)用檢出限表中的檢出限，需要用到1/2檢出限是直接用函數(shù)求其一半即可。當(dāng)檢出限改變時(shí)直接在檢出限表中修改檢出限。

3.2 關(guān)于各類水的標(biāo)準(zhǔn)限值

《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》（GB 3838-2002）對(duì)方法檢出限在未來(lái)也會(huì)修訂，因此各類水限值有可能變化，可以寫入檢出限表中調(diào)用，以便后期的修改。

3.3 關(guān)于需要清除內(nèi)容的單元格

《水環(huán)境監(jiān)測(cè)規(guī)范》（SL 219-2013）對(duì)水質(zhì)資料整編的規(guī)定有：檢出率為0時(shí)，超標(biāo)率、實(shí)測(cè)范圍、最大值超標(biāo)倍數(shù)、最大值出現(xiàn)日期均不填。因此，對(duì)VBA等熟悉者可以嘗試自動(dòng)對(duì)這些單元格進(jìn)行刪除。

4 結(jié)語(yǔ)

通過(guò)應(yīng)用Excel預(yù)置的函數(shù)計(jì)算求得化學(xué)需氧量項(xiàng)目的特征值計(jì)算方法，提高了水質(zhì)資料整編的效率和準(zhǔn)確度，節(jié)省了數(shù)據(jù)歸類和計(jì)算的時(shí)間。Excel函數(shù)在2018年水質(zhì)資料整編中使用，其成果被正式采納。此方法適用性強(qiáng)、操作快捷，為水質(zhì)資料整編提供了很好的計(jì)算分析途徑，對(duì)其它30余項(xiàng)水質(zhì)項(xiàng)目的特征值求法也適用。

此方法仍有不足之處，比如自動(dòng)清除部分單元格內(nèi)容、對(duì)方法標(biāo)準(zhǔn)規(guī)定需要保留若干位有效數(shù)字的項(xiàng)目進(jìn)行取舍還不能自動(dòng)完成，期望通過(guò)VBA編程等方法予以解決。