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

李海帆

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

0 引言

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

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

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

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

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

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

1.1 COUNTA函數(shù)

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

1.2 COUNIF函數(shù)

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

1.3 IF函數(shù)

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

1.4 MAX函數(shù)

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

1.5 MIN函數(shù)

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

1.6 MATCH函數(shù)

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

1.7 INDEX函數(shù)

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

1.8 COLUMN函數(shù)

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

1.9 LEFT函數(shù)

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

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

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

2.1 計算樣品總數(shù)

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

2.2 計算檢出率

通過COUNTIF函數(shù)計算給定區(qū)域的單元格數(shù)目。計算化學(xué)需氧量檢出率，因《水質(zhì) 化學(xué)需氧量的測定 重鉻酸鹽法》（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 計算超標(biāo)率

計算超標(biāo)率，通過COUNTIF函數(shù)計算超標(biāo)數(shù)。根據(jù)《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》（GB 3838-2002），在整編中超過每個參數(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時，根據(jù)《水環(huán)境監(jiān)測規(guī)范》（SL 219-2013）要求，超標(biāo)率單元格為空，此時刪除超標(biāo)率即可。當(dāng)檢出率不為0且小于100%時保留1位小數(shù)，等于100%是保留整數(shù)。經(jīng)計算化學(xué)需氧量的超標(biāo)率為0。

2.4 計算實測范圍

計算實測范圍包括最小值和最大值，其求法如下：

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

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

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

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

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

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

通過MATCH函數(shù)求出最大值所在的行數(shù)，即最大值所在的月份，然后通過INDEX函數(shù)和MATCH函數(shù)的套用，求出最大值所對應(yīng)的的分析日期所在的單元格，并通過應(yīng)用LEFT函數(shù)截取分析日期的前一個字符，即最大值出現(xiàn)日，再后通過&字符，將最大值出現(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 計算年平均值

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

先用SUMIF函數(shù)求出檢出單元格之和，然后再用COUNTIF函數(shù)求出未檢出單元格個數(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)計算求得化學(xué)需氧量平均值為9。

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

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

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

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

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

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

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

4 結(jié)語

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

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