Excel在湖泊水庫營養狀態評價及水華風險評估中的應用

郭玉雷，姜榮福

（海河水利委員會漳衛南運河管理局，山東德州253009）

Excel在湖泊水庫營養狀態評價及水華風險評估中的應用

郭玉雷，姜榮福

（海河水利委員會漳衛南運河管理局，山東德州253009）

按照“全國部分重點湖庫藻類試點監測工作成果報送表”的要求，詳細闡述了利用Excel2007軟件對湖泊、水庫水質富營養化狀態、水華風險進行評估的操作過程。整個過程簡單快捷、易于推廣，可大幅度提高工作效率。

富營養化；水華風險；湖泊水庫；Excel應用

富營養化是指生物所需的氮、磷等營養物質大量進入湖泊水庫等緩流水體，引起藻類及其他浮游生物迅速繁殖，水體溶氧量下降，魚類及其他生物大量死亡的現象。富營養化評價是通過與湖泊營養狀態有關的一系列指標及指標間的相互關系對湖泊的營養狀態作出準確的判斷。快速準確地對湖泊水庫進行富營養化評價，對維護湖庫水質安全具有重要意義。

1 研究目的

湖泊水庫營養狀態評價及水華風險評估是常規水質監測中的一項重要工作，但由于其評價方法的計算公式比較繁瑣，單純用計算器計算效率低，且極易出現錯誤。為方便評價工作的進行，筆者利用Excel表格的邏輯與運算功能，編制營養狀態及水華風險評估自動評價表，以期提高評價工作效率，保證評價結果數據的準確性。

2 湖泊（水庫）營養狀態評價和水華風險評估方法

2.1 營養狀態評價

按照《地表水資源質量評價技術規程》（SL395-2007）進行評價，評價指標包括葉綠素a、總磷、總氮、高錳酸鹽指數和透明度5項，湖庫營養狀態評價標準及方法見表1。營養狀態指數EI的計算公式如下：

表1 湖庫營養狀態評價標準及分級方法

式中：EI為營養狀態指數；En為評價項目賦分值；N為評價項目個數。

2.2 水華風險評估

依據國內相對認可的藻細胞密度對應水華風險判別的經驗值，為水華風險評估標準，見表2。

表2 藻細胞密度與水華風險評估參照標準

3 創建營養狀態評價和水華風險評估表

打開Excel工作薄文件，此時會出現Sheet1、Sheet2和Sheet3三張工作表。在Sheet1工作表中，按全國部分重點湖庫藻類試點監測工作成果報送表內容編輯表格，輸入所需信息。

再按下面步驟對Sheet1工作表內單元格輸入有關函數。在F5單元格輸入“=IF（ISNUMBER（F4），IF（OR（AND（F4＜6，F4＞0），F4＞9），"劣Ⅴ"，"Ⅰ"），""）”，再單擊“√”（輸入）按鈕，可得pH水質類別；在G5單元格內輸入“=IF（ISNUMBER（G4），IF（G4＜2，"劣Ⅴ"，IF（G4＜3，"Ⅴ"，IF（G4＜5，"Ⅳ"，IF（G4＜6，"Ⅲ"，IF（G4＜7.5，"Ⅱ"，"Ⅰ"））））），""）”，可得溶解氧（DO）水質類別；在H5單元格內輸入“=IF（ISNUMBER（H4），IF（H4＞15，"劣Ⅴ"，IF（H4＞10，"Ⅴ"，IF（H4＞6，"Ⅳ"，IF（H4＞4，"Ⅲ"，IF（H4＞2，"Ⅱ"，"Ⅰ"））））），""）”，可得高錳酸鹽指數（CODMn）水質類別；在I5單元格內輸入“=IF（ISNUMBER（I4），IF（I4＞0.2，"劣Ⅴ"，IF（I4＞0.1，"Ⅴ"，IF（I4＞0.05，"Ⅳ"，IF（I4＞0.025，"Ⅲ"，IF（I4＞0.01，"Ⅱ"，"Ⅰ"））））），""）”，可得總磷（TP）水質類別；在J5單元格內輸入“=IF（ISNUMBER（J4），IF（J4＞2，"劣Ⅴ"，IF（J4＞1.5，"Ⅴ"，IF（J4＞1，"Ⅳ"，IF（J4＞0.5，"Ⅲ"，IF（J4＞0.15，"Ⅱ"，"Ⅰ"））））），""）”，可得總氮（TN）水質類別；在H6單元格內輸入“=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），""）”，可得高錳酸鹽營養指數；在I6單元格內輸入“=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），""）”，可得總磷營養指數；在J6單元格內輸入“=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），""）”，可得總氮營養指數；在K6單元格內輸入“=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）營養指數；在L6單元格內輸入“=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）營養指數；在M4單元格內輸入“=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參評綜合水質類別；在N4單元格內輸入“=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不參評綜合水質類別；在O4單元格內輸入“=IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））=""，""，IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））＜=20，"貧營養"，IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））＜=50，"中營養"，IF（IF（COUNTIF（H6：L6，""）= 5，""，AVERAGE（H6：L6））＜=60，"輕度富營養"，IF（IF（COUNTIF（H6：L6，""）=5，""，AVERAGE（H6：L6））＜=80，"中度富營養"，"重度富營養"）））））”，可得湖泊、水庫營養狀態評價；在P4單元格內輸入“藻類的常見種或優勢種”，在Q4單元格內輸入“藻細胞密度”，在R4單元格內輸入“=IF（Q4=""，""，IF（Q4＜100，"不具條件"，IF（AND（Q4＞=100，Q4＜=1000），"初具條件"，"臨界狀態或水華發生"）））”，可得湖泊、水庫水華風險評估。

上述是一個監測斷面的水質評價、營養狀態評價和水華風險評估的制作過程。若需評價多個斷面，可同時選擇4—6行進行復制、粘貼，可得到多個斷面的評價表格，如圖1所示。

圖1 多斷面評價表格

4 結語

Excel具有統計計算、邏輯判斷及邏輯處理等功能。應用Excel對湖庫水質類別評價、營養狀態評價和水華風險評估，只需更新各評價參數的監測濃度值，即可自動給出計算結果和評價結論，操作簡單快捷、易于推廣，從而大幅度提高工作效率。

[1]GB3838—2002，地表水環境質量標準[S].

[2]SL395—2007，地表水資源質量評價技術規程[S].

TV213.3；X824

B

1004-7328（2014）06-0065-03

10.3969/j.issn.1004-7328.2014.06.024

2014-07-25

郭玉雷（1987-），男，助理工程師，主要從事水資源保護及評價工作。