Excel函數(shù)在水質(zhì)評(píng)價(jià)中的應(yīng)用

杜久芳 馮 峰

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

1 引言

在編制淮河流域水質(zhì)通報(bào)的過程中，需要對(duì)100 余處站點(diǎn)的水質(zhì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，并按照地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)，分多個(gè)區(qū)域進(jìn)行水質(zhì)評(píng)價(jià)，大量的數(shù)據(jù)需要計(jì)算、處理，手工逐個(gè)計(jì)算，不僅費(fèi)時(shí)費(fèi)力，而且輸入過程容易出錯(cuò)，準(zhǔn)確率得不到保證。

水質(zhì)評(píng)價(jià)中需要根據(jù)相應(yīng)類別標(biāo)準(zhǔn)，對(duì)PH、溶解氧、高錳酸鹽指數(shù)、化學(xué)需氧量等21 項(xiàng)指標(biāo)進(jìn)行單項(xiàng)水質(zhì)項(xiàng)目水質(zhì)類別評(píng)價(jià)，在此基礎(chǔ)上對(duì)各站點(diǎn)進(jìn)行水質(zhì)站水質(zhì)類別評(píng)價(jià)，得出評(píng)價(jià)結(jié)果，說明超標(biāo)項(xiàng)目和單項(xiàng)水質(zhì)項(xiàng)目超標(biāo)倍數(shù)。筆者通過總結(jié)前人經(jīng)驗(yàn)和自己的摸索實(shí)踐，針對(duì)水質(zhì)通報(bào)編制過程中數(shù)據(jù)處理費(fèi)時(shí)、費(fèi)力、出錯(cuò)率高的問題，闡述Excel 函數(shù)在水質(zhì)評(píng)價(jià)數(shù)據(jù)統(tǒng)計(jì)分析中的應(yīng)用。

2 Excel 函數(shù)介紹

Excel 是Microsoftoffice 公司推出的辦公套件中的一個(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)用其中的邏輯函數(shù)IF，查找和引用函數(shù)VLOOKUP，統(tǒng)計(jì)函數(shù)MAX、COUNTIF， 文本函數(shù)LEFT、CONCATENATE、LEN，計(jì)算水質(zhì)評(píng)價(jià)中涉及的水質(zhì)類別、超標(biāo)項(xiàng)目（倍數(shù)）、水質(zhì)類別比例、超標(biāo)和達(dá)標(biāo)比例等評(píng)價(jià)參數(shù)。

2.1 邏輯函數(shù)IF

如果指定條件的計(jì)算結(jié)果為TRUE，IF 函數(shù)將返回某個(gè)值；如果該條件的計(jì)算結(jié)果為FALSE，則返回另一個(gè)值。

2.2 查找和引用函數(shù)

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

2.3 統(tǒng)計(jì)函數(shù)

2.3.1 MAX 函數(shù)

返回一組值中的最大值。

2.3.2 COUNTIF 函數(shù)

COUNTIF 函數(shù)會(huì)統(tǒng)計(jì)某個(gè)區(qū)域內(nèi)符合指定的單個(gè)條件的單元格數(shù)量。

2.4 文本函數(shù)

2.4.1 LEFT 函數(shù)

LEFT 從文本字符串的第一個(gè)字符開始返回指定個(gè)數(shù)的字符。

2.4.2 CONCATENATE 函數(shù)

CONCATENATE 函數(shù)可將最多255 個(gè)文本字符串合并為一個(gè)文本字符串。聯(lián)接項(xiàng)可以是文本、數(shù)字、單元格引用或這些項(xiàng)的組合。

2.4.3 LEN 函數(shù)

LEN 函數(shù)返回文本字符串中的字符個(gè)數(shù)。

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

通過對(duì)上述函數(shù)的綜合運(yùn)用，可快速對(duì)水質(zhì)站和流域及區(qū)域的水質(zhì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析，完成水質(zhì)評(píng)價(jià)工作，其中水質(zhì)站水質(zhì)評(píng)價(jià)包括單項(xiàng)水質(zhì)項(xiàng)目水質(zhì)類別評(píng)價(jià)、單項(xiàng)水質(zhì)項(xiàng)目超標(biāo)倍數(shù)評(píng)價(jià)、水質(zhì)站水質(zhì)類別評(píng)價(jià)和水質(zhì)站主要超標(biāo)項(xiàng)目評(píng)價(jià)，流域及區(qū)域水質(zhì)評(píng)價(jià)應(yīng)包括達(dá)標(biāo)比例、超標(biāo)比例、各類水質(zhì)類別比例。

3.1 水質(zhì)站水質(zhì)評(píng)價(jià)

3.1.1 單項(xiàng)水質(zhì)項(xiàng)目水質(zhì)類別評(píng)價(jià)

單項(xiàng)水質(zhì)項(xiàng)目水質(zhì)類別根據(jù)該項(xiàng)目實(shí)測(cè)濃度值與《地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)》（GB3838-2002）中規(guī)定的限值的比對(duì)結(jié)果確定,以某水質(zhì)站的高錳酸鹽指數(shù)的水質(zhì)類別評(píng)價(jià)為例，Ⅰ類、Ⅱ類、Ⅲ類、Ⅳ類、Ⅴ類水的高錳酸鹽指數(shù)限值分別為2mg/L、4mg/L、6mg/L、10mg/L、15mg/L。

在計(jì)算單項(xiàng)水質(zhì)項(xiàng)目水質(zhì)類別的單元格中輸入公式：=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,""））））））），得到該站的高錳酸鹽指數(shù)項(xiàng)目水質(zhì)類別為2，即Ⅱ類水，公式中G8 表示該站的高錳酸鹽指數(shù)濃度值。

3.1.2 水質(zhì)站水質(zhì)類別評(píng)價(jià)

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

3.1.3 單項(xiàng)水質(zhì)項(xiàng)目超標(biāo)倍數(shù)評(píng)價(jià)

單項(xiàng)水質(zhì)項(xiàng)目濃度超過GB3838 Ⅲ類標(biāo)準(zhǔn)限值的稱為超標(biāo)項(xiàng)目。超標(biāo)項(xiàng)目的超標(biāo)倍數(shù)應(yīng)按式（1）計(jì)算。

式中：Bi—某水質(zhì)項(xiàng)目超標(biāo)倍數(shù)；

Ci—某水質(zhì)項(xiàng)目濃度，mg/L；

Si—某水質(zhì)項(xiàng)目的Ⅲ類標(biāo)準(zhǔn)限值，mg/L。

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

3.1.4 水質(zhì)站超標(biāo)項(xiàng)目及倍數(shù)評(píng)價(jià)

水質(zhì)站主要超標(biāo)項(xiàng)目及倍數(shù)采用兩個(gè)公式進(jìn)行計(jì)算。

單元格AZ8 中輸入第一個(gè)公式：=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 中計(jì)算的單項(xiàng)水質(zhì)項(xiàng)目超標(biāo)倍數(shù)進(jìn)行連接，在單元格BA8 中輸入第二個(gè)公式：=IF（BA8="","",LEFT（BA8,LEN（BA8）-1）），去掉最后的“、”，得出結(jié)果：高錳酸鹽指數(shù)（0.43）、化學(xué)需氧量（0.6）、總磷（1.95）。

3.2 流域及區(qū)域水質(zhì)評(píng)價(jià)

3.2.1 達(dá)標(biāo)比例、超標(biāo)比例

流域或區(qū)域的水質(zhì)站的達(dá)標(biāo)、超標(biāo)比例，利用條件統(tǒng)計(jì)函數(shù)COUNTIF 來計(jì)算，以達(dá)標(biāo)比例的計(jì)算為例，在單元格內(nèi)輸入公式：=COUNTIF（評(píng)價(jià)總表!$BD$8:$BD$100,"= 達(dá)標(biāo)"）/$N$1，其中$BD$8:$BD$100 為達(dá)標(biāo)情況所在列，$N$1 表示有監(jiān)測(cè)數(shù)據(jù)的水質(zhì)站數(shù)量（部分站因河干不監(jiān)測(cè)），公式為=COUNTIF（評(píng)價(jià)總表!$BD$8:$BD$100,"=達(dá)標(biāo)"）+COUNTIF（評(píng)價(jià)總表!$BD$8:$BD$100,"=不達(dá)標(biāo)"）。

3.2.2 各類水質(zhì)類別比例

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

另外，在區(qū)域水質(zhì)評(píng)價(jià)中，可以利用VLOOKUP函數(shù)，在總表中查找并匯總所需要的區(qū)域內(nèi)水質(zhì)站對(duì)應(yīng)的水質(zhì)數(shù)據(jù)和評(píng)價(jià)結(jié)果，公式為：=VLOOKUP（$C3,評(píng)價(jià)總表!$C$7:$BE$100,48,FALSE）。

4 結(jié)語

水質(zhì)評(píng)價(jià)需要對(duì)大量的數(shù)據(jù)進(jìn)行匯總、統(tǒng)計(jì)、分析，人工手動(dòng)統(tǒng)計(jì)耗時(shí)費(fèi)力，出錯(cuò)率高。本文充分使用Excel 函數(shù)，具有以下優(yōu)點(diǎn)：（1）Excel 函數(shù)的應(yīng)用，可以減少重復(fù)性人工計(jì)算過程，大大節(jié)約工作時(shí)間，提高工作效率；（2）該水質(zhì)評(píng)價(jià)數(shù)據(jù)表結(jié)構(gòu)框架搭建好之后，如果基礎(chǔ)數(shù)據(jù)改變，只需修改評(píng)價(jià)表中相應(yīng)的基礎(chǔ)數(shù)據(jù)部分，評(píng)價(jià)結(jié)果通過函數(shù)公式能自動(dòng)計(jì)算完成；（3）水質(zhì)站點(diǎn)增多時(shí)，進(jìn)行拖拽操作即可對(duì)所增加的站點(diǎn)進(jìn)行單站評(píng)價(jià)，進(jìn)行區(qū)域評(píng)價(jià)時(shí)只需調(diào)整公式中的數(shù)據(jù)起始行和結(jié)束行的行標(biāo)號(hào)即可；而監(jiān)測(cè)項(xiàng)目增加，只需將現(xiàn)有監(jiān)測(cè)項(xiàng)目的公式復(fù)制到新增加的監(jiān)測(cè)項(xiàng)目相應(yīng)的位置即可，監(jiān)測(cè)點(diǎn)和監(jiān)測(cè)項(xiàng)目越多，越能體現(xiàn)Excel 函數(shù)的優(yōu)越性■