Excel函數在水質評價中的應用

杜久芳 馮 峰

（淮河水利委員會水文局（信息中心）蚌埠 233001）

1 引言

在編制淮河流域水質通報的過程中，需要對100 余處站點的水質數據進行統計，并按照地表水環境質量標準，分多個區域進行水質評價，大量的數據需要計算、處理，手工逐個計算，不僅費時費力，而且輸入過程容易出錯，準確率得不到保證。

水質評價中需要根據相應類別標準，對PH、溶解氧、高錳酸鹽指數、化學需氧量等21 項指標進行單項水質項目水質類別評價，在此基礎上對各站點進行水質站水質類別評價，得出評價結果，說明超標項目和單項水質項目超標倍數。筆者通過總結前人經驗和自己的摸索實踐，針對水質通報編制過程中數據處理費時、費力、出錯率高的問題，闡述Excel 函數在水質評價數據統計分析中的應用。

2 Excel 函數介紹

Excel 是Microsoftoffice 公司推出的辦公套件中的一個重要組件，它既可以制作電子表格,也可以進行各種數據的處理、統計分析和輔助決策操作,被廣泛應用于管理、統計、財經、金融等眾多領域。

Excel 包含了大量的內置函數，本文主要應用其中的邏輯函數IF，查找和引用函數VLOOKUP，統計函數MAX、COUNTIF， 文本函數LEFT、CONCATENATE、LEN，計算水質評價中涉及的水質類別、超標項目（倍數）、水質類別比例、超標和達標比例等評價參數。

2.1 邏輯函數IF

如果指定條件的計算結果為TRUE，IF 函數將返回某個值；如果該條件的計算結果為FALSE，則返回另一個值。

2.2 查找和引用函數

VLOOKUP 函數用于搜索某個單元格區域的第一列，然后返回該區域相同行上任何單元格中的值。

2.3 統計函數

2.3.1 MAX 函數

返回一組值中的最大值。

2.3.2 COUNTIF 函數

COUNTIF 函數會統計某個區域內符合指定的單個條件的單元格數量。

2.4 文本函數

2.4.1 LEFT 函數

LEFT 從文本字符串的第一個字符開始返回指定個數的字符。

2.4.2 CONCATENATE 函數

CONCATENATE 函數可將最多255 個文本字符串合并為一個文本字符串。聯接項可以是文本、數字、單元格引用或這些項的組合。

2.4.3 LEN 函數

LEN 函數返回文本字符串中的字符個數。

3 Excel 函數的具體應用

通過對上述函數的綜合運用，可快速對水質站和流域及區域的水質數據進行統計、分析，完成水質評價工作，其中水質站水質評價包括單項水質項目水質類別評價、單項水質項目超標倍數評價、水質站水質類別評價和水質站主要超標項目評價，流域及區域水質評價應包括達標比例、超標比例、各類水質類別比例。

3.1 水質站水質評價

3.1.1 單項水質項目水質類別評價

單項水質項目水質類別根據該項目實測濃度值與《地表水環境質量標準》（GB3838-2002）中規定的限值的比對結果確定,以某水質站的高錳酸鹽指數的水質類別評價為例，Ⅰ類、Ⅱ類、Ⅲ類、Ⅳ類、Ⅴ類水的高錳酸鹽指數限值分別為2mg/L、4mg/L、6mg/L、10mg/L、15mg/L。

在計算單項水質項目水質類別的單元格中輸入公式：=IF（G8="","",IF（G8<=2,1,IF（G8<=4,2,IF（G8<=6,3,IF（G8<=10,4,IF（G8<=15,5,IF（G8>15,6,""））））））），得到該站的高錳酸鹽指數項目水質類別為2，即Ⅱ類水，公式中G8 表示該站的高錳酸鹽指數濃度值。

3.1.2 水質站水質類別評價

水質站水質類別按所評價項目中水質最差項目的類別確定，在單元格AW 中輸入公式=MAX（AA8:AG8,AI8:AV8），單元格AX 中輸入公式=IF（AW8=1,"Ⅰ",IF（AW8=2,"Ⅱ",IF（AW8=3,"Ⅲ",IF（AW8=4," Ⅳ",IF（AW8=5," Ⅴ"," 劣Ⅴ"）））），得出結果Ⅲ，表示該站水質為Ⅲ類水。

3.1.3 單項水質項目超標倍數評價

單項水質項目濃度超過GB3838 Ⅲ類標準限值的稱為超標項目。超標項目的超標倍數應按式（1）計算。

式中：Bi—某水質項目超標倍數；

Ci—某水質項目濃度，mg/L；

Si—某水質項目的Ⅲ類標準限值，mg/L。

在計算某水質站高錳酸鹽指數超標倍數的單元格BJ8 中輸入公式=IF（G8="","",IF（G8>BJ$4,BJ$1&"（"&ROUND（（G8-BJ$4）/BJ$4,2）&"）",""））， 其中G8 為該站的高錳酸鹽指數濃度值，BJ$1 為文本“高錳酸鹽指數”，BJ$4 為Ⅲ類水的高錳酸鹽指數限值6mg/L，得出結果：高錳酸鹽指數（0.43），其中0.43即為該站的高錳酸鹽指數超標倍數。

3.1.4 水質站超標項目及倍數評價

水質站主要超標項目及倍數采用兩個公式進行計算。

單元格AZ8 中輸入第一個公式：=CONCATENATE（BH8,BI8,BJ8,BK8,BL8,BM8,BN8,BP8,BQ8,BR8,BS8,BT8,BU8,BV8,BW8,BX8,BY8,BZ8,CA8,CB8,CC8）。

將3.1.3 中計算的單項水質項目超標倍數進行連接，在單元格BA8 中輸入第二個公式：=IF（BA8="","",LEFT（BA8,LEN（BA8）-1）），去掉最后的“、”，得出結果：高錳酸鹽指數（0.43）、化學需氧量（0.6）、總磷（1.95）。

3.2 流域及區域水質評價

3.2.1 達標比例、超標比例

流域或區域的水質站的達標、超標比例，利用條件統計函數COUNTIF 來計算，以達標比例的計算為例，在單元格內輸入公式：=COUNTIF（評價總表!$BD$8:$BD$100,"= 達標"）/$N$1，其中$BD$8:$BD$100 為達標情況所在列，$N$1 表示有監測數據的水質站數量（部分站因河干不監測），公式為=COUNTIF（評價總表!$BD$8:$BD$100,"=達標"）+COUNTIF（評價總表!$BD$8:$BD$100,"=不達標"）。

3.2.2 各類水質類別比例

各類水質類別比例為Ⅰ類、Ⅱ類、Ⅲ類、Ⅳ類、V 類及劣V 類的比例，以I 類水的比例，在單元格中輸入公式：=COUNTIF（$E$3:$E$95,"Ⅰ"）/$N$1，其中$E$3:$E$95 為水質類別所在列。

另外，在區域水質評價中，可以利用VLOOKUP函數，在總表中查找并匯總所需要的區域內水質站對應的水質數據和評價結果，公式為：=VLOOKUP（$C3,評價總表!$C$7:$BE$100,48,FALSE）。

4 結語

水質評價需要對大量的數據進行匯總、統計、分析，人工手動統計耗時費力，出錯率高。本文充分使用Excel 函數，具有以下優點：（1）Excel 函數的應用，可以減少重復性人工計算過程，大大節約工作時間，提高工作效率；（2）該水質評價數據表結構框架搭建好之后，如果基礎數據改變，只需修改評價表中相應的基礎數據部分，評價結果通過函數公式能自動計算完成；（3）水質站點增多時，進行拖拽操作即可對所增加的站點進行單站評價，進行區域評價時只需調整公式中的數據起始行和結束行的行標號即可；而監測項目增加，只需將現有監測項目的公式復制到新增加的監測項目相應的位置即可，監測點和監測項目越多，越能體現Excel 函數的優越性■