Excel在湖泊水庫(kù)營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)及水華風(fēng)險(xiǎn)評(píng)估中的應(yīng)用

郭玉雷，姜榮福

（海河水利委員會(huì)漳衛(wèi)南運(yùn)河管理局，山東德州253009）

Excel在湖泊水庫(kù)營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)及水華風(fēng)險(xiǎn)評(píng)估中的應(yīng)用

郭玉雷，姜榮福

（海河水利委員會(huì)漳衛(wèi)南運(yùn)河管理局，山東德州253009）

按照“全國(guó)部分重點(diǎn)湖庫(kù)藻類試點(diǎn)監(jiān)測(cè)工作成果報(bào)送表”的要求，詳細(xì)闡述了利用Excel2007軟件對(duì)湖泊、水庫(kù)水質(zhì)富營(yíng)養(yǎng)化狀態(tài)、水華風(fēng)險(xiǎn)進(jìn)行評(píng)估的操作過(guò)程。整個(gè)過(guò)程簡(jiǎn)單快捷、易于推廣，可大幅度提高工作效率。

富營(yíng)養(yǎng)化；水華風(fēng)險(xiǎn)；湖泊水庫(kù)；Excel應(yīng)用

富營(yíng)養(yǎng)化是指生物所需的氮、磷等營(yíng)養(yǎng)物質(zhì)大量進(jìn)入湖泊水庫(kù)等緩流水體，引起藻類及其他浮游生物迅速繁殖，水體溶氧量下降，魚類及其他生物大量死亡的現(xiàn)象。富營(yíng)養(yǎng)化評(píng)價(jià)是通過(guò)與湖泊營(yíng)養(yǎng)狀態(tài)有關(guān)的一系列指標(biāo)及指標(biāo)間的相互關(guān)系對(duì)湖泊的營(yíng)養(yǎng)狀態(tài)作出準(zhǔn)確的判斷。快速準(zhǔn)確地對(duì)湖泊水庫(kù)進(jìn)行富營(yíng)養(yǎng)化評(píng)價(jià)，對(duì)維護(hù)湖庫(kù)水質(zhì)安全具有重要意義。

1 研究目的

湖泊水庫(kù)營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)及水華風(fēng)險(xiǎn)評(píng)估是常規(guī)水質(zhì)監(jiān)測(cè)中的一項(xiàng)重要工作，但由于其評(píng)價(jià)方法的計(jì)算公式比較繁瑣，單純用計(jì)算器計(jì)算效率低，且極易出現(xiàn)錯(cuò)誤。為方便評(píng)價(jià)工作的進(jìn)行，筆者利用Excel表格的邏輯與運(yùn)算功能，編制營(yíng)養(yǎng)狀態(tài)及水華風(fēng)險(xiǎn)評(píng)估自動(dòng)評(píng)價(jià)表，以期提高評(píng)價(jià)工作效率，保證評(píng)價(jià)結(jié)果數(shù)據(jù)的準(zhǔn)確性。

2 湖泊（水庫(kù)）營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)和水華風(fēng)險(xiǎn)評(píng)估方法

2.1 營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)

按照《地表水資源質(zhì)量評(píng)價(jià)技術(shù)規(guī)程》（SL395-2007）進(jìn)行評(píng)價(jià)，評(píng)價(jià)指標(biāo)包括葉綠素a、總磷、總氮、高錳酸鹽指數(shù)和透明度5項(xiàng)，湖庫(kù)營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)標(biāo)準(zhǔn)及方法見表1。營(yíng)養(yǎng)狀態(tài)指數(shù)EI的計(jì)算公式如下：

表1 湖庫(kù)營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)標(biāo)準(zhǔn)及分級(jí)方法

式中：EI為營(yíng)養(yǎng)狀態(tài)指數(shù)；En為評(píng)價(jià)項(xiàng)目賦分值；N為評(píng)價(jià)項(xiàng)目個(gè)數(shù)。

2.2 水華風(fēng)險(xiǎn)評(píng)估

依據(jù)國(guó)內(nèi)相對(duì)認(rèn)可的藻細(xì)胞密度對(duì)應(yīng)水華風(fēng)險(xiǎn)判別的經(jīng)驗(yàn)值，為水華風(fēng)險(xiǎn)評(píng)估標(biāo)準(zhǔn)，見表2。

表2 藻細(xì)胞密度與水華風(fēng)險(xiǎn)評(píng)估參照標(biāo)準(zhǔn)

3 創(chuàng)建營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)和水華風(fēng)險(xiǎn)評(píng)估表

打開Excel工作薄文件，此時(shí)會(huì)出現(xiàn)Sheet1、Sheet2和Sheet3三張工作表。在Sheet1工作表中，按全國(guó)部分重點(diǎn)湖庫(kù)藻類試點(diǎn)監(jiān)測(cè)工作成果報(bào)送表內(nèi)容編輯表格，輸入所需信息。

再按下面步驟對(duì)Sheet1工作表內(nèi)單元格輸入有關(guān)函數(shù)。在F5單元格輸入“=IF（ISNUMBER（F4），IF（OR（AND（F4＜6，F(xiàn)4＞0），F(xiàn)4＞9），"劣Ⅴ"，"Ⅰ"），""）”，再單擊“√”（輸入）按鈕，可得pH水質(zhì)類別；在G5單元格內(nèi)輸入“=IF（ISNUMBER（G4），IF（G4＜2，"劣Ⅴ"，IF（G4＜3，"Ⅴ"，IF（G4＜5，"Ⅳ"，IF（G4＜6，"Ⅲ"，IF（G4＜7.5，"Ⅱ"，"Ⅰ"））））），""）”，可得溶解氧（DO）水質(zhì)類別；在H5單元格內(nèi)輸入“=IF（ISNUMBER（H4），IF（H4＞15，"劣Ⅴ"，IF（H4＞10，"Ⅴ"，IF（H4＞6，"Ⅳ"，IF（H4＞4，"Ⅲ"，IF（H4＞2，"Ⅱ"，"Ⅰ"））））），""）”，可得高錳酸鹽指數(shù)（CODMn）水質(zhì)類別；在I5單元格內(nèi)輸入“=IF（ISNUMBER（I4），IF（I4＞0.2，"劣Ⅴ"，IF（I4＞0.1，"Ⅴ"，IF（I4＞0.05，"Ⅳ"，IF（I4＞0.025，"Ⅲ"，IF（I4＞0.01，"Ⅱ"，"Ⅰ"））））），""）”，可得總磷（TP）水質(zhì)類別；在J5單元格內(nèi)輸入“=IF（ISNUMBER（J4），IF（J4＞2，"劣Ⅴ"，IF（J4＞1.5，"Ⅴ"，IF（J4＞1，"Ⅳ"，IF（J4＞0.5，"Ⅲ"，IF（J4＞0.15，"Ⅱ"，"Ⅰ"））））），""）”，可得總氮（TN）水質(zhì)類別；在H6單元格內(nèi)輸入“=IF（ISNUMBER（H4），ROUND（IF（H4＞60，100，IF（H4＞40，（（（100-90）*（H4-40）+90*（60-40））/（60-40）），IF（H4＞25，（（（90-80）*（H4-25）+80*（40-25））/（40-25）），IF（H4＞10，（（（80-70）*（H4-10）+70*（25-10））/（25-10）），IF（H4＞=8，（（（70-60）*（H4-8）+60*（10-8））/（10-8）），IF（H4＞=8，""，IF（H4＞4，（（（60-50）*（H4-4）+50*（8-4））/（8-4）），IF（H4＞2，（（（50-40）*（H4-2）+40*（4-2））/（4-2）），IF（H4＞1，（（（40-30）*（H4-1）+30*（2-1））/（2-1）），IF（H4＞0.4，（（（30-20）*（H4-0.4）+20*（1-0.4））/（1-0.4）），IF（H4＞0.15，（（（20-10）*（H4-0.15）+10*（0.4-0.15））/（0.4-0.15）），（（（10-0）*（H4-0）+0*（0.15-0））/（0.15-0））））））））））））），1），""）”，可得高錳酸鹽營(yíng)養(yǎng)指數(shù)；在I6單元格內(nèi)輸入“=IF（ISNUMBER（I4），ROUND（IF（I4＞1.3，100，IF（I4＞0.9，（（（100-90）*（I4-0.9）+90*（1.3-0.9））/（1.3-0.9）），IF（I4＞0.6，（（（90-80）*（I4-0.6）+80*（0.9-0.6））/（0.9-0.6）），IF（I4＞0.2，（（（80-70）*（I4-0.2）+70*（0.6-0.2））/（0.6-0.2）），IF（I4＞=0.1，（（（70-60）*（I4-0.1）+60*（0.2-0.1））/（0.2-0.1）），IF（I4＞=0.1，""，IF（I4＞0.05，（（（60-50）*（I4-0.05）+50*（0.1-0.05））/（0.1-0.05）），IF（I4＞0.025，（（（50-40）*（I4-0.025）+ 40*（0.05-0.025））/（0.05-0.025）），IF（I4＞0.01，（（（40-30）*（I4-0.01）+30*（0.025-0.01））/（0.025-0.01）），IF（I4＞0.004，（（（30-20）*（I4-0.004）+20*（0.01-0.004））/（0.01-0.004）），IF（I4＞0.001，（（（20-10）*（I4-0.001）+10*（0.004-0.001））/（0.004-0.001）），（（（10-0）*（I4-0）+ 0*（0.001-0））/（0.001-0））））））））））））），1），""）”，可得總磷營(yíng)養(yǎng)指數(shù)；在J6單元格內(nèi)輸入“=IF（ISNUMBER（J4），ROUND（IF（J4＞16，100，IF（J4＞9，（（（100-90）*（J4-9）+90*（16-9））/（16-9）），IF（J4＞6，（（（90-80）*（J4-6）+80*（9-6））/（9-6）），IF（J4＞2，（（（80-70）*（J4-2）+70*（6-2））/（6-2）），IF（J4＞=1，（（（70-60）*（J4-1）+60*（2-1））/（2-1）），IF（J4＞=1，""，IF（J4＞0.5，（（（60-50）*（J4-0.5）+50*（1-0.5））/（1-0.5）），IF（J4＞0.3，（（（50-40）*（J4-0.3）+40*（0.5-0.3））/（0.5-0.3）），IF（J4＞0.1，（（（40-30）*（J4-0.1）+30*（0.3-0.1））/（0.3-0.1）），IF（J4＞0.05，（（（30-20）*（J4-0.05）+20*（0.1-0.05））/（0.1-0.05）），IF（J4＞0.02，（（（20-10）*（J4-0.02）+10*（0.05-0.02））/（0.05-0.02）），（（（10-0）*（J4-0）+0*（0.02-0））/（0.02-0））））））））））））），1），""）”，可得總氮營(yíng)養(yǎng)指數(shù)；在K6單元格內(nèi)輸入“=IF（ISNUMBER（K4），ROUND（IF（K4＜0.12，100，IF（K4＜0.2，（（（90-100）*（K4-0.2）+90*（0.2-0.12））/（0.2-0.12）），IF（K4＜0.3，（（（80-90）*（K4-0.3）+80*（0.3-0.2））/（0.3-0.2）），IF（K4＜0.4，（（（70-80）*（K4-0.4）+70*（0.4-0.3））/（0.4-0.3）），IF（K4＜0.5，（（（60-70）*（K4-0.5）+60*（0.5-0.4））/（0.5-0.4）），IF（K4＜0.5，""，IF（K4＜1，（（（50-60）*（K4-1）+50*（1-0.5））/（1-0.5）），IF（K4＜1.5，（（（40-50）*（K4-1.5）+40*（1.5-1））/（1.5-1）），IF（K4＜3，（（（30-40）*（K4-3）+30*（3-1.5））/（3-1.5）），IF（K4＜5，（（（20-30）*（K4-5）+20*（5-3））/（5-3）），IF（K4＜10，（（（10-20）*（K4-10）+10*（10-5））/（10-5）），（（（0-10）*（K4-20）+0*（20-10））/（20-10））））））））））））），1），""）”，可得透明度（SD）營(yíng)養(yǎng)指數(shù)；在L6單元格內(nèi)輸入“=IF（ISNUMBER（L4），ROUND（IF（L4＞1，100，IF（L4＞0.4，（（（100-90）*（L4-0.4）+90*（1-0.4））/（1-0.4）），IF（L4＞0.16，（（（90-80）*（L4-0.16）+80*（0.4-0.16））/（0.4-0.16）），IF（L4＞0.064，（（（80-70）*（L4-0.064）+70*（0.16-0.064））/（0.16-0.064）），IF（L4＞=0.026，（（（70-60）*（L4-0.026）+60*（0.064-0.026））/（0.064-0.026）），IF（L4＞=0.026，""，IF（L4＞0.01，（（（60-50）*（L4-0.01）+50*（0.026-0.01））/（0.026-0.01）），IF（L4＞0.004，（（（50-40）*（L4-0.004）+40*（0.01-0.004））/（0.01-0.004）），IF（L4＞0.002，（（（40-30）*（L4-0.002）+30*（0.004-0.002））/（0.004-0.002）），IF（L4＞0.001，（（（30-20）*（L4-0.001）+20*（0.002-0.001））/（0.002-0.001）），IF（L4＞0.0005，（（（20-10）*（L4-0.0005）+10*（0.001-0.0005））/（0.001-0.0005）），（（（10-0）*（L4-0）+0*（0.0005-0））/（0.0005-0））））））））））））），1），""）”，可得葉綠素（Chl_a）營(yíng)養(yǎng)指數(shù)；在M4單元格內(nèi)輸入“=IF（COUNTIF（F5：L5，"劣Ⅴ"）＞0，"劣Ⅴ"，IF（COUNTIF（F5：L5，"Ⅴ"）＞0，"Ⅴ"，IF（COUNTIF（F5：L5，"Ⅳ"）＞0，"Ⅳ"，IF（COUNTIF（F5：L5，"Ⅲ"）＞0，"Ⅲ"，IF（COUNTIF（F5：L5，"Ⅱ"）＞0，"Ⅱ"，IF（COUNTIF（F5：L5，"Ⅰ"）＞0，"Ⅰ"，""））））））”，可得TN、TP參評(píng)綜合水質(zhì)類別；在N4單元格內(nèi)輸入“=IF（M4= ""，""，IF（AND（F5=""，G5=""，H5=""，I5=""，J5= ""），""，IF（COUNTIF（F5：H5，"劣Ⅴ"）＞0，"劣Ⅴ"，IF（COUNTIF（F5：H5，"Ⅴ"）＞0，"Ⅴ"，IF（COUNTIF（F5：H5，"Ⅳ"）＞0，"Ⅳ"，IF（COUNTIF（F5：H5，"Ⅲ"）＞0，"Ⅲ"，IF（COUNTIF（F5：H5，"Ⅱ"）＞0，"Ⅱ"，IF（COUNTIF（F5：H5，"Ⅰ"）＞0，"Ⅰ"，""））））））））”，可得TN、TP不參評(píng)綜合水質(zhì)類別；在O4單元格內(nèi)輸入“=IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））=""，""，IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））＜=20，"貧營(yíng)養(yǎng)"，IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））＜=50，"中營(yíng)養(yǎng)"，IF（IF（COUNTIF（H6：L6，""）= 5，""，AVERAGE（H6：L6））＜=60，"輕度富營(yíng)養(yǎng)"，IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））＜=80，"中度富營(yíng)養(yǎng)"，"重度富營(yíng)養(yǎng)"）））））”，可得湖泊、水庫(kù)營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)；在P4單元格內(nèi)輸入“藻類的常見種或優(yōu)勢(shì)種”，在Q4單元格內(nèi)輸入“藻細(xì)胞密度”，在R4單元格內(nèi)輸入“=IF（Q4=""，""，IF（Q4＜100，"不具條件"，IF（AND（Q4＞=100，Q4＜=1000），"初具條件"，"臨界狀態(tài)或水華發(fā)生"）））”，可得湖泊、水庫(kù)水華風(fēng)險(xiǎn)評(píng)估。

上述是一個(gè)監(jiān)測(cè)斷面的水質(zhì)評(píng)價(jià)、營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)和水華風(fēng)險(xiǎn)評(píng)估的制作過(guò)程。若需評(píng)價(jià)多個(gè)斷面，可同時(shí)選擇4—6行進(jìn)行復(fù)制、粘貼，可得到多個(gè)斷面的評(píng)價(jià)表格，如圖1所示。

圖1 多斷面評(píng)價(jià)表格

4 結(jié)語(yǔ)

Excel具有統(tǒng)計(jì)計(jì)算、邏輯判斷及邏輯處理等功能。應(yīng)用Excel對(duì)湖庫(kù)水質(zhì)類別評(píng)價(jià)、營(yíng)養(yǎng)狀態(tài)評(píng)價(jià)和水華風(fēng)險(xiǎn)評(píng)估，只需更新各評(píng)價(jià)參數(shù)的監(jiān)測(cè)濃度值，即可自動(dòng)給出計(jì)算結(jié)果和評(píng)價(jià)結(jié)論，操作簡(jiǎn)單快捷、易于推廣，從而大幅度提高工作效率。

[1]GB3838—2002，地表水環(huán)境質(zhì)量標(biāo)準(zhǔn)[S].

[2]SL395—2007，地表水資源質(zhì)量評(píng)價(jià)技術(shù)規(guī)程[S].

TV213.3；X824

B

1004-7328（2014）06-0065-03

10.3969/j.issn.1004-7328.2014.06.024

2014-07-25

郭玉雷（1987-），男，助理工程師，主要從事水資源保護(hù)及評(píng)價(jià)工作。