基于VBA和Excel的水库调洪计算程序开发与应用.pdf

DOI:1016617/jcnki11-5543/TK20240112基于 VBA 和 Excel 的水库调洪计算程序开发与应用张跃驰 庞金龙(中水北方勘测设计研究有限责任公司,天津 300222)【摘 要】在 Excel 的基础上,结合 VBA 实现了列表试算法调洪计算的程序化,针对调洪计算的特点,利用二分法实现迭代计算,不需要人工键入公式即完成计算,且易于拓展,可通过循环计算实现多组方案同时计算,显著提高了调洪计算的效率和准确性。通过云南清水河水库工程实际应用,证实本程序计算成果与手动计算成果一致,可满足工程应用要求。【关键词】水库;调洪;VBA;二分法中图分类号:TV122 3 文献标识码:B 文章编号:1673-8241(2024)01-062-04Development and Application of Flood Control Calculation Programfor Reservoirs Based on VBA and ExcelZHANG Yuechi,PANG Jinlong(China Water Resources Beifang Investigation,Design and Research Co,Ltd,Tianjin 300222,China)收稿日期:2023-10-26作者简介:张跃驰(1990),男,硕士,工程师,从事水利水电工程规划工作。Abstract:Using VBA in conjunction with Excel,a program has been developed to automate flood control calculationsbased on the list trial algorithm method.Taking into account the characteristics of flood control calculations,iterativecalculations are achieved using the binary search method.This program eliminates the need for manual input of formulas,making calculations straightforward,and allows for easy scalability.Multiple scenarios can be simultaneously calculatedthrough looping,significantly improving the efficiency and accuracy of flood control calculations.Through practicalapplication in Qingshuihe Reservoir Project in Yunnan,it has been confirmed that the results obtained by this program areconsistent with manual calculations,meeting the requirements for engineering applications.Key words:reservoir;flood control;VBA;binary search method 在水库规划设计阶段,开展水库调洪计算,主要是为了配合枢纽布置中泄洪建筑物形式、尺寸的选择及确定满足工程安全的防洪特征水位、最大下泄流量等指标。目前水库调洪计算主要利用 Excel 及其迭代计算功能实现,需要手动输入公式并人工查找起蓄时刻和最高水位时刻,操作烦琐,易于出错且计算效率26低。为提高调洪计算效率和准确性,本文在 Excel 开发环境的基础上,利用 VBA 编程语言开发调洪计算自动化程序,仅需输入基础参数即可实现多套方案的快速计算。1 调洪计算基本原理和方法调洪计算属于入库和出库水量平衡的计算问题,其计算原理可用圣维南方程组表示。但圣维南方程组在应用于水库调洪计算中,常需要做一定的简化,一般简化为以下公式进行计算:12(Q1+Q2)-12(q1+q2)=V2-V1t(1)q=f(V)(2)式中Q1、Q2 计算时段初、末的入库流量,m3/s;q1、q2 计算时段初、末的出库流量,m3/s;V1、V2 计算时段初、末的水库蓄水量,m3;t 计算时段,s。式(1)实际是水量平衡方程,即在一个计算时间段中,入库水量与下泄水量之差即为该时段中水库蓄水量的变化。式(2)为水库下泄流量与库容关系的曲线,取决于泄水建筑物的布置型式和尺寸等。调洪计算方法有很多种,我国常用的是列表试算法和半图解法。半图解法通过与水库水位建立函数关系,利用图解代替试算,可有效降低计算量,是手动调洪计算的一种有效方法。随着计算机技术的发展,虽然列表试算法计算量较大,但便于通过计算机编程实现,是目前水库规划设计阶段最普遍采用的计算方法。列表试算法基本步骤如下:a 引用某一场次设计洪水过程线,选取计算时段Dt 和入库流量 Q1,Q2,Q3,。b 确定调洪起调水位 Z0、起调库容 V0、泄流曲线 q泄=f(Z)、库容曲线 Z=f(V)等基础参数。c 试算出库流量 qt对水量平衡方程进行求解,判断其与入库流量 Qt和下泄能力 q泄t的关系。d 不断试算 qt直至得到满足设定条件的解,同时计算蓄变量DVt时段末水位 Zt、时段末库容 Vt等。e 继续计算下一时段,直至计算完整场洪水,累加DVt即得到调洪所需库容,过程最高水位即为相应场次洪水最高坝前水位。2 基于VBA 和Excel 程序开发的特点及优势目前,水库调洪计算普遍采用 Excel 软件及其迭代计算功能实现,由于设计洪水过程线一般以小时为时段,为保证计算精度,水库水位起涨时刻和最高水位时刻需线性插值得到,此步骤需要手动操作,耗时较长,且每次只能完成单独一组基础参数的调洪计算,对于需要多个起调水位、多个频率洪水及多条泄流曲线比选时,此方法耗时较长,且容易出错。VBA 是由 Visual Basic 发展而来的一种宏语言,一般依附于如 Excel 等 Office 的应用程序中,用于拓展应用程序的功能,得益于 VBA 与主应用程序的这种依附关系,其与主程序之间的通信简单且高效。并且 VBA 提供了面向对象的程序设计方法,提供了相当完整的程序设计语言,结合其强大的宏录制功能,利用 VBA 可以在 Excel 内轻松开发出功能强大的自动化计算程序。与仅利用 Excel 迭代功能进行调洪计算相比,利用 VBA 开发通用的调洪计算程序,可结合调洪计算的特点开发更有针对性的计算函数,并通过循环实现多套组合方案的快速调洪计算,同时输出计算成果图表到 Excel 中,操作简单,基本不需要额外的学习成本即可掌握,可有效提高调洪计算的效率和准确性。3 调洪计算程序设计调洪计算程序开发以 Excel 和 VBA 开发环境为基础,采用面向对象的编程方法。基础类包括插值函数类和基本参数类,其中插值函数类主要解决二元曲线线性插值问题。新编迭代函数,利用二分法解决迭代计算问题。利用 Excel 操作界面和新编绘图函数完成参数的输入和成果的输出。调洪计算程序总体框图见图 1。36经验交流Experience Exchange/ExcelVBAVBAExcel图 1 调洪计算程序总体以单次洪水调洪过程为例,以入库流量和泄流能力为判别条件,逐时段进行调洪计算,对于典型单峰洪水,水库起蓄时刻为入库流量与最大泄流能力相等的时刻,此后入库流量大于最大泄流能力,水库水位上涨,直至入库流量与最大泄流能力再次相等时,水库达到最高水位 Zmax,此后水库水位开始回落。单次调洪计算流程见图 2。在单次调洪计算基础上,增加循环,可实现多个起调水位、多条泄流曲线和多场设计洪水等组合调洪计算。二分法即一分为二的方法,其基本原理是每次将区间从中间分成两部分,通过求解区间中间值判断函数解所处子区间,然后在对应的子区间中继续查找直至找到目标元素。采用二分法进行迭代计算可提高迭代计算效率。调洪计算涉及迭代计算分为两类:水位上涨时段,此时出库流量 qt等于最大下泄能力 qmax,此时需要试算满足条件的 qt,计算流程见图 3。水库最高水位时刻,此时入库流量 Qt=qt=qmax,需在i=1qt=Qtqt=Qtt=t+1t=t+1j=1Qtt=0Qtqt=t+1图 2 单次调洪计算流程Qt1aQtaqt1aVt1qtup=max(Qt1Qtqt1qt=(qtup+qtdown2qtmax=f(Qt1aQtaqt1aVt1qt=qtmaxqt qtmaxqtup=qtqtdown=qtqtdown=min(Qt1Qtqt1图 3 二分法迭代计算流程46经验交流Experience Exchange的基础上嵌套试算 Qt=qt的迭代计算。4 工程应用实例云南省清水河水库的工程任务以供水和灌溉为主,兼顾发电。根据清水河水库规模和坝型,校核洪水标准为 P=0 05%。水库溢洪道共 2 孔,净宽 5m,堰顶高程 1386m。调洪计算采用淤积 50 年库容曲线,根据淹没赔偿标准和水库防洪标准要求,本次对 P=20%、P=5%、P=1%和 P=0 05%四个洪水频率分别进行调洪计算。调洪计算原理是基于水量平衡,时段入库水量减时段出库水量等于时段水库蓄水变化量。采用静库容调洪,利用列表试算法进行洪水调节计算。清水河水库不承担下游防洪任务,泄流设施为闸门控制溢洪道,水库起调水位为正常蓄水位 1392m。库水位 1392m 时水库最大泄流能力为 302m3/s,当入库洪水流量小于 302m3/s 时,按入库流量控泄,库水位保持 1392m;当入库洪水流量大于 302m3/s 时,水库全部敞泄,水库开始滞洪,库水位上升。退水期入库流量小于水库泄流能力,水库又转入控泄阶段,避免对下游形成人造洪峰,库水位逐步降至 1392m。利用调洪计算程序进行调洪计算,其校核洪水调洪过程见表 1 和图 4,各频率洪水调洪成果见表 2。表 1 校核洪水调洪计算过程时段/h入库流量/(m3/s)出库流量/(m3/s)最大泄流能力/(m3/s)蓄变量/万 m3时段末库容/万 m3水位/m02302303020103601392 000103601392 0092922923020103601392 009 663023023020103601392 00103073023020103601392 00113223033034103651392 01123373043049103741392 03424624594592112401393 9442 574594594590112411393 94434584594590112401393 9444453459459-1112391393 9345449459459-3112361393 93图 4 校核洪水调洪计算过程表 2 不同频率洪水调节计算成果对比洪水频率入库洪峰流量/(m3/s)最大泄量/(m3/s)坝前最高水位/m相应原始库容/万 m3P=20%1741741392 0011717P=5%2532531392 0011717P=1%(设计洪水)3423061392 0511739P=0 05%(校核洪水)6064591393 9412625 采用基于 VBA 和 Excel 的水库调洪计算程序,单次调洪计算时长约 2s,经对比,计算(下转第 70 页)56经验交流Experience Exchange已建直立式、斜坡式硬质护岸的河段因地制宜考虑对其进行软化及绿化改造。根据河道现有防护工程建设情况、沿河居民区及生态建设要求,共布置岸坡整治工程 33 51km。4 2 5 水源涵养与水土保持玉龙喀什河流域属于新疆水土保持分区的塔里木盆地南部防风固沙重点治理区。根据当地的实际情况,所处的地理位置、地形地貌、气候特征、河流特征,以及土壤、植被和周围环境特点等,项目区实施水土保持工程后项目建设区内原有和新增水土流失得到有效控制;防治责任范围内的生态得到最大限度的保护,环境得到明显改善;水土保持设施安全有效。5 结 语和田地区和田市水系连通及水美乡村建设项目通过恢复农村河湖基本功能,修复河道空间形态,提升河湖水环境质量,建设河畅水清岸绿景美的水美乡村,提升居民人居环境,具有良好的社会效益、生态效益和经济效益。项目实施后对实现和田市实现农村水系整体提升,促进乡村全面振兴,具有积极且良好的作用。参考文献1 刘昱,徐晶菁,徐昕,等.当阳市水系连通及水美乡村建设试点经验与思考J.水利规划与设计,2023(3):54-57,81.2 廖强,员明,胡惠方.南方低山丘陵区水系连通及水美乡村建设经验与思考J.水利规划与设计,2023(2):1-4,22.3 刘福生,王光秀.广饶县水系连通及水美乡村建设实践J.山东水利,2023(1):15-17.4 张春杰.加强农村水系治理加快水美乡村建设N.中国水利报,2023-01-10(6).5 谭大伟,秦守田,崔笑庚,等.雅安市名山区水系连通及水美乡村建设研究J.东北水利水电,2022,40(12):25-27.6 林君,谢锦霞,叶国平.衢州市柯城区水系连通及水美乡村建设方案思考J.水利规划与设计,2022(8):22-24,63.7 陈炜,刘文胜,刘草原.安徽广德水系连通河清渠畅 水美乡村全域开花J.中国水利,2022(12):16-19.8 霍军军,施文婧,国俊宝.水系连通及水美乡村建设试点经验初探J.中国水利,2022(12):21-22,29.9 韩东旭.水系连通及水美乡村试点县建设工作要点探索J.水利发展研究,2022,22(4):25-28.(上接第 65 页)成果与手动计算成果一致。通过循环计算,可实现多套组合方案快速计算,极大程度提高调洪计算效率。5 结 语a 与目前普遍采用的基于 Excel 的列表试算法调洪计算相比,本计算程序针对调洪计算的特点采用二分法进行迭代计算,可将原单次调洪计算的时间从5 10min 降低 2s,计算效率显著提高。b 本计算程序不需要手动设置公式,可有效避免人为错误,且易于拓展,通过循环计算可同时计算多套组合方案,通过 Excel 可直观便捷地输出成果图表。c 通过云南清水河水库等工程实际应用,证实本程序计算成果与手动计算成果一致,可满足工程应用要求。参考文献1 程时宏,袁东风,胡本健.在 Excel 中用试算法实现水库调洪演算J.四川水力发电,2010,29(1):77-78.2 王新华,张慧颖,黄辉曦,等.基于 Excel 的半图解法在水库调洪计算中的应用J.人民黄河,2021,43(11):76-80,158.3 雷静思,郭纯青.VB 程序与 Excel 结合在水库调洪演算中的应用J.广西水利水电,2015(4):48-51.4 张国辉,郭晓丽,贾鹏生.基于 VBA 的水库调洪演算程序编制J.水资源与水工程学报,2012,23(4):190-192.5 季山,周倜.水利计算及水利规划M.北京:中国水利水电出版社,1998.6 张树茂.基于 VB 语言的调洪演算计算程序的开发与应用J.现代矿业,2021,37(5):252-254.7 贾志峰,富飞.水库兴利调节及调洪计算的程序设计与应用J.水利科技与经济,2010,16(12):1415-1418.8 赵经华,岳春芳,张磊,等.用 VB 进行调洪演算J.水电站设计,2009,25(3):43-45.9 李永和,李国颖.VBA 在调洪演算中的应用J.黑龙江科技信息,2011(33):31.10 杨随柱.Excel VBA 在水库调洪试算中的应用J.山西建筑,2014,40(29):285-286.11 曹宏,葛跃平.小型水库调洪演算方法探讨J.浙江水利科技,2000(3):29-31.07经验交流Experience Exchange