淺談Excel函數在水質資料整編中的應用

李海帆

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

0 引言

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

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

圖1 水質資料整編成果表各參數在Excel中的行列分布圖

圖2 水質資料整編特征值表各參數在Excel中的行列分布圖

1 所用Excel函數介紹

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

1.1 COUNTA函數

COUNTA函數用于計算區域中所有不為空的單元格的個數。通過計算非空單元格數，即可統計出當前項目當年的樣品總數。

1.2 COUNIF函數

COUNTIF函數用于對單元格區域中滿足單個指定條件的單元格進行計數。此函數可以項目的方法檢出限為條件，統計大于檢出限的個數；也可以目標水質類別為條件，統計超標個數。

1.3 IF函數

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

1.4 MAX函數

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

1.5 MIN函數

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

1.6 MATCH函數

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

1.7 INDEX函數

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

1.8 COLUMN函數

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

1.9 LEFT函數

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

2 Excel函數的具體應用

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

2.1 計算樣品總數

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

2.2 計算檢出率

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

2.3 計算超標率

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

2.4 計算實測范圍

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

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

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

當全部未檢出時，則最大值顯示＜檢出限，根據《水環境監測規范》（SL219-2013）要求，全部未檢出時，最大值和最小值單元格為空，此時刪除最大值和最小值即可。

2.5 計算最大超標倍數

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

2.6 最大值出現日期

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

2.7 計算年平均值

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

先用SUMIF函數求出檢出單元格之和，然后再用COUNTIF函數求出未檢出單元格個數后乘以1/2檢出限，與之前檢出單元格相加，除以非空單元格數即為平均值；再用IF函數判斷平均值是否大于檢出，如果大于檢出限，則此值為年平均值，否則以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)。經計算求得化學需氧量平均值為9。

3 方法改進探討

3.1 關于方法檢出限

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

3.2 關于各類水的標準限值

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

3.3 關于需要清除內容的單元格

《水環境監測規范》（SL 219-2013）對水質資料整編的規定有：檢出率為0時，超標率、實測范圍、最大值超標倍數、最大值出現日期均不填。因此，對VBA等熟悉者可以嘗試自動對這些單元格進行刪除。

4 結語

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

此方法仍有不足之處，比如自動清除部分單元格內容、對方法標準規定需要保留若干位有效數字的項目進行取舍還不能自動完成，期望通過VBA編程等方法予以解決。