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-），男，助理工程师，主要从事水资源保护及评价工作。