基于Excel VBA自定义函数的河湖水质与富营养化一体化评价工具

Integrated Evaluation Tool of River-Lake Water Quality and Eutrophication Based on Excel VBA Custom Functions

摘要: 针对水资源质量年报编制过程中，河湖的水质状况、富营养化状态批量评价计算时，采用Excel多层嵌套公式存在逻辑冗长、维护难度大、人工计算误差突出等问题，本文开发一套内嵌国标限值的Excel VBA自定义函数评价工具。工具内置GB 3838-2002、SL 395-2007评价规则，集成单指标水质判定、超标统计、综合营养状态指数TLI计算、水源地达标判别六大函数，可自动区分河流、湖库差异化评价标准。以湖北安陆解放山水库2021年逐月监测数据开展实例验证，从调用复杂度、运算效率、复核难度与传统嵌套公式法对比测试。结果表明：函数单元格调用语句仅10~25字符，百站点批量计算耗时降低70%以上，无需配套标准对照表，计算精度与传统方法完全一致。该轻量化工具可显著减少基层水文人员整编工作量，为水资源质量年报自动化编制提供技术支撑。

Abstract: Aiming at the problems including cumbersome logic, difficult maintenance and prominent manual calculation errors when adopting multi-layer nested Excel formulas for batch calculation of water quality status and eutrophication assessment of rivers and lakes in the compilation of annual water resource quality reports, this paper develops an assessment tool of Excel VBA custom functions embedded with national standard limit values. The tool integrates six core functions including single-index water quality classification, over-standard statistics, comprehensive trophic state index (TLI) calculation and water sources compliance judgment, and automatically distinguishes differentiated evaluation criteria for rivers and lakes with built-in standard values of GB 3838-2002 and SL 395-2007. Monthly monitoring data of Jiefangshan Reservoir in Anlu, Hubei Province in 2021 are adopted for case verification. Comparative tests are conducted between the proposed tool and traditional nested formula method from the dimensions of call complexity, calculation efficiency and review difficulty. The results show that each function call only contains 10~25 characters, and the time consumption of batch calculation for 100 monitoring sites is reduced by more than 70%. No auxiliary comparison table is required, and the calculation accuracy is completely consistent with the traditional method. As a lightweight tool, it can effectively cut down data processing workload for grassroots hydrological departments, and provide technical support for automatic compilation of annual water resource quality reports.