基于Excel VBA水文频率计算程序的开发应用

西北水电・２０１　１年・第５期　文章编号：１００６－－２６１０（２０１　１）０５—０ｏ０１—Ｏ３　基于Ｅｘｃｅｌ　ＶＢＡ水文频率计算程序的开发应用　袁超　（中国水电顾问集团西北勘测设计研究院，西安摘７１００６５）　要：基于Ｅｘｃｅｌ　ＶＢＡ二次开发功能，进行５种频率线型水文频率计算程序开发；根据水文频率计算方法，采用ＶＢＡ　语言编写程序，建立用户界面进行参数的输入和计算成果的输出。经检验，本程序计算结果正确，计算效率高，实用　性强。　关键词：Ｅｘｃｅｌ；ＶＢＡ；ＧＵＩ；水文频率；计算程序　中图分类号：ＴＰ３１１．１１：Ｐ３３３．９　文献标识码：Ａ　Ａｐｐｌｉｃａｔｉｏｎ　ｏｆ　ｈｙｄｒｏｌｏｇｉｃａｌ　ｆｒｅｑｕｅｎｃｙ　ｃａｌｃｕｌａｔｉｏｎ　ｐｒｏｇｒａｍ　ｂａｓｅｄ　ｏｎ　Ｅｘｃｅｌ　ＶＢＡ　ＹＵＡＮ　Ｃｈａｏ　（Ｈｙｄｒｏｃｈｉｎａ　Ｘｉｂｅｉ　Ｅｎｇｉｎｅｅｒｉｎｇ　Ｃｏｒｐｏｒａｔｉｏｎ，ＣＨＥＣＣ，Ｘｉ＂ａｎ　７１００６５，Ｃｈｉｎａ）　Ａｂｓｔｒａｃｔ：Ｔｈｅ　ｐａｐｅｒ　ｐｒｅｓｅｎｔｓ　ｔｈｅ　ｄｅｖｅｌｏｐｍｅｎｔ　ｏｆ　ｌｉｎｅａｒ　ｈｙｄｒｏｌｏｇｉｃａｌ￣ｅｑｕｅｎｃｙ　ｃａｌｃｕｌａｔｉｏｎ　ｐｒｏｇｒａｍ　ｆｏｒ　ｆｉｖｅ　ｆｒｅｑｕｅｎｃｉｅｓ　ｂａｓｅｄ　ｏｎ　ｔｈｅ　ｓｅｅ—　ｏｎｄ　ｄｅｖｅｌｏｐｍｅｎｔ　ｆｕｎｃｔｉｏｎ　ｏｆ　Ｅｘｃｅｌ　ＶＢＡ．Ａｃｃｏｒｄｉｎｇ　ｔｏ　ｔｈｅ　ｃａｌｃｕｌａｔｉｏｎ　ｍｅｔｈｏｄ　ｏｆ　ｈｙｄｒｏｌｏｇｉｃａｌ　ｆｒｅｑｕｅｎｃｙ，ＶＢＡ　ｌａｎｇｕａｇｅ　ｉｓ　ｕｓｅｄ　ｔｏ　ｐｒｅｐａｒｅ　ｔｈｅ　ｐｒｏｇｒａｍ　ａｎｄ　ｅｓｔａｂｌｉｓｈ　ｔｈｅ　ｕｓｅｒ　ｉｎｔｅｒｆａｃｅ　ＳＯ　ａｓ　ｔｏ　ｉｎｐｕｔ　ｔｈｅ　ｐａｒａｍｅｔｅｒｓ　ａｎｄ　ｏｕｔｐｕｔ　ｔｈｅ　ｃａｌｃｕｌａｔｉｏｎ　ｒｅｓｕｌｔｓ．Ｔｅｓｔｓ　ｓｈｏｗ　ｔｈａｔ　ｔｈｅ　ｐｒｏｇｒａｍ　ｈａｓ　ｃｏｒｒｅｃｔ　ｃａｌｃｕｌａｔｉｏｎ　ｒｅｓｕｌｔｓ，ｈｉｇｈ　ｃａｌｃｕｌａｔｉｏｎ　ｅｆｆｉｃｉｅｎｃｙ　ａｎｄ　ｐｒａｃｔｉｃａｌ　ａｐｐｌｉｃａｂｉｌｉｔｙ．　Ｋｅｙ　ｗｏｒｄｓ：Ｅｘｃｅｌ；ＶＢＡ；ＧＵＩ；ｈｙｄｒｏｌｏｇｉｃａｌ　ｆｒｅｑｕｅｎｃｙ；ｃａｌｃｕｌａｔｉｏｎ　ｐｒｏｇｒａｍ　０引　言　在水资源分析及工程水文频率计算中，水文频　率计算是一个非常重要的环节。在进行水文频率计　１水文频率计算的基本原理　５种频率线型的水文频率分析如下。　（１）Ｐ—Ｈ１分布　Ｐ—ｍ分布概率分布函数为：　算时，中国水文学者普遍认为水文资料系列概率分　布符合Ｐ—ｍ型分布，因此在水文频率计算中普遍采　用Ｐ—ｍ型分布曲线，以推求各种频率下的设计值。　目前，水文频率计算程序多采用ＶＢ６．０、Ｍａｔｌａｂ以及　Ｅｘｃｅｌ软件为平台开发¨　Ｊ，其中Ｅｘｃｅｌ软件具有良　好的数据存储、计算和绘图功能，在水利工程设计计　算中应用比较广泛。　ｆ（ｘ）＝　（　）ａ－１ｅｘｐ［一／３（　＞０　＝）］（１）　（２）　（３）　４／Ｃ　＝２／（ｘＣ　Ｃ　）　（４）　（５）　本文借助Ｅｘｃｅｌ　ＶＢＡ二次开发技术，采用面向对　象程序设计思路，构建良好的人机交互界面，利用Ｅｘ—　ａ。＝ｘ（１—２Ｃ　／Ｃ　）　式中：Ｆ（　）为ＯＬ的伽玛函数；ＯＬ，卢，ａ。分别为Ｐ—ｍ　分布的形状、尺度和位置参数；ａ＞０，ｘ为均值；Ｃ　为　变差系数；Ｃ　为偏态系数。　ｃｅｌ宏技术和菜单方式，加载和运行程序，同时对国外　河流水文资料，本程序增加了正态、对数正态、对数Ｐ　一Ⅲ、耿贝尔分布以满足非Ｐ—ｍ分布设计需要。　在水文分析计算中需要绘制理论频率曲线，要　根据指定的频率Ｐ求出相应的设计值　，即要解决　超过频率Ｐ时的分布，即：　收稿日期：２０１１－０６－２５　作者简介：袁超（１９８３一），男，陕西省宝鸡市人，助理工程师，主　要从事水文水资源工作．　Ｐ＝　ａｏ）ａ－１ｅｘｐ　ａｏ）］ｄ　）　令ｔ＝一／３（ｘ—ａ。），则：　２　袁超．基于Ｅｘｃｅｌ　ＶＢＡ水文频率计算程序的开发应用　（５）Ｇｕｍｂｅ１分布　（７）　ｔＰ＝卢（　一０ｏ）　Ｐ＝　唧　一　唧㈠　Ｇｕｍｂｅ１分布又称双指数型分布，其密度甬数为：　）＝ｏ￣ｅｘｐ｛一　（　—　０）一ｅｘｐ［一　（．１５一　（））］｝　（２１）　（８）　（９）　设计值　。求解：　（１＋ｃ　）ｘ　＝式中：ＯＬ，‰分别为２个分布参数。　设计值　求解：　＝　＝　＋苦　（１０　。（１＋　Ｃ　）　（２２）　＝一０．４５００５３２—０．７７９６９６８１ｎ［一ｉｎ（１一Ｐ）ｊ　（２３）　ｔｐ＝ＧＡＭＭＡＩＮＶ（１　，１）　…）　式中：　为离均系数。　＝孚ＧＡＭＭＡ　ＮＶ（　一ｐ，　，　）一　２（１２）　式中：　为离均系数；ＧＡＭＭＡＩＮＶ为伽玛累积分布　函数的反函数。　（２）Ｎｏｒｍａ１分布　正态分布概率密度函数为：　ｆ（ｘ）＝　１　ｅｘｐ［－　（　］（１３）　式中：　为均值；　为标准差。　设计值　求解：　＝ＮＯＲＭＩＮＶ（１一Ｐ，　，ｇｒ）　（１４）　式中：ＮＯＲＭＩＮＶ为正态累积分布函数的反函数。　（３）Ｌｏｇ—Ｎｏｒｍａ１分布　当随机变量　的对数值服从正态分布时，称　的分布为对数正态分布。对于２参数对数正态分布　而言，变量　的对数ｙ＝ｌｎ（ｘ）服从正态分布，其均值　０　与标准差　计算采用下式：　Ⅱ　＝Ｉｎ（ｘ）一０．５ｏｒ　（１５）　＝√　＋１　（１６）　式中：ｘ，Ｃ　分别为　系列均值与变差系数。　设计值　求解：　Ｙｐ　＝ＮＯＲＭＩＮＶ（１一Ｐ，ｎ　，Ｏ－　）　（１７）　＝ｅｘｐ（Ｙｐ）　（１８）　（４）Ｌｏｇ—Ｐ—ｍ分布　当随机变量　的对数值Ｙ＝ｌｎｘ服从Ｐ一１１１分布　时，称　的分布为对数Ｐ一Ⅲ分布，其密度函数为：　）＝　（ｉｎ（　）　￣ｑｅｘｐ［　１ｎ（　）　）］　（１９）　设计值　求解：　。＝ｅｘｐ（Ｙ　）　（２０）　式中：　采用式（９）求解。　２　程序运行　（１）打开Ｅｘｃｅｌ，选择“工具”下拉菜单“加载　宏”选项，在“加载宏”界面选择“浏览”按钮，找到本　程序存放目录，加载本程序；　（２）Ｅｘｃｅｌ主菜单中“数据”菜单项后面出现　“水文”下拉菜单项（见图１），在Ｆ拉菜单项巾选择　“水文频率分析”子项目“水文频率适线分析”即可　运行本程序；　（３）在“水文”下拉菜单项中，选择“删除菜单”　即可退出“加载宏”。　图１　程序以粟单形式出现在Ｅｘｃｅｌ粟单栏中图　３　功能简介　本程序主要功能分为以下几部分：　（１）连续系列和非连续系列频率（多个考　期）计算；　（２）５种频率线型（Ｐ—ｌｌＩ，Ｎｏｒｍａｌ，Ｌｏｇ—Ｎｏｒ—　ｍａｌｌ，Ｌｏｇ－Ｐ－ｍ，Ｇｕｍｂｅ１）选择；　（３）手动调节参数取值；　（４）设置数据有效数位；　（５）若对现有适线成果不满意可以重新适线；　（６）不同线型和不同参数取值，求取各设计频　率设计值；　（７）在频率曲线图上可以添加数据点据（如　问数据）；　（８）同一张频率曲线罔上绘制多条频率曲线和　多个数据系列；　（９）设置频率曲线图（Ｙ坐标轴格式：最大值、　最小值、刻度单位；网格线格式：无网格、简网格、令　网格；数据点据格式：大小、形状、颜色；曲线格式：粗　西北水电・２０１１年・第５期　３　细、形状、颜色；图释：图名、参数取值、设计成果）；　（１０）设计成果（原始数据、经验频率、设计成　果、频率曲线图）以Ｅｘｃｅｌ表格输出或直接打印。　４　实　例　某水文站调查到１９０４年历史洪水，洪峰流量为　５　９００～６　３００　ｍ　／ｓ，为１８１ｌ＿－２０００年最大洪水，重　现期７１为１９１　ａ；实测系列中，１９８１年洪水作特大值　处理，洪峰流量为５　３７０　Ｉｎ　／ｓ，为１８１１－２０００年相　当第二大洪水，重现期　为７６～９６　ａ。　将原始数据输入Ｅｘｃｅｌ表格，运行程序并选择　相应输入参数（见图２），按“确定”按钮出现调参界　面（见图３），调整相应参数适线并设置图形格式（见　图４），按“保存”按钮设计成果保存为Ｅｘｃｅｌ表格　（见图５）。　平　１９０４　女｝６ｉ　∞　ｒ　～　１９鼢　∞　ｉ９０　２　ｌ一一立…｛　啦。０　㈣　３６∞　舞　ｉｓｈ．ｔｔｌＩｆｒ耵：耵蝴　尊　Ⅻ　麓鲫Ｉ豁黼龋　　－。　∞　薅港　３　秘鞭豁鹄；　相毒替ｌ蔺　赫｜　麓　－３舯０　３ｌ∞　辱嘲畦瓣ｔ　畦鞲｜ｘ　壤啦３　嘲　韵　瓢　ｍ　ｒ　｛疆　秘　警　妊　｜　鼻　Ⅻ１Ｏ　　帮　—　≯嚣　；　Ⅻ　卵舫　蒹沌　√　ｌｉ　她　２　鸯１疰瑚　蘧　ｔＩｌｌｍ　ｒ￥蛳　警　４。　糟蝴　鳓：ｉ　¨ｌ　瞄啪　２７∞　溉艇懈　々　ｊ　Ｓｈｃｔｔ１　１蚰搴５：ｌ嘴　嚣　２６９０　２　＿　　。－　确彦　。＿　癣簿ｔ＿“　一　ｌ镫瓣　ｊ｜ｊ　Ｈ　　图２程序基本数据输入界面图　图３　程序调参并适线界面图　５结论　本文程序具有界面良好，功能丰富，操作简单；　成果以图形与数据表格输出，图形美观，打印效果　好；５种频率分布可供选择，增强了程序的适用性；　计算结果正确，满足水电工程相关规范和标准，计算　效率高，实用性强。　鹧　曼　ｌ　Ｔ。。　＿柚“瑟话谴话　订螭　孔船　图４程序适线成果界面图　图５　程序适线成果输出界面图　本文程序在计算Ｐ一Ⅲ分布离均系数　值　ＧＡＭＭＡＩＮＶ函数采用Ｅｘｃｅｌ内置函数，如文献［１］，　［２］，［７］所述偏态系数Ｃ　取较大值时，　值发散，　建议Ｃ　取值在０．１１～５．９范围内使用本程序，同　时，为了提高　计算精度，需重新编写ＧＡＭＭＡ—　ＩＮＶ函数算法。　参考文献：　王双银，向又珍，朱晓群，等．基本ＥＸＣＥＬ的水文频率计算软　件开发［Ｊ］．西北农林科技大学学报，２００６，３４（４）：１１３一ｌ１６．　李智，衣起超，李艳杰，等．Ｅｘｃｅｌ在水文频率计算中的应用　［Ｊ］．水利与建筑工程学报，２００７，５（２）：９５—９７．　耿鸿江．ＥＸＣＥＬ在Ｐ一Ⅲ型分布频率计算中的应用研究［Ｊ］．水　电能源科学，２００２，２０（３）：４１—４３．　王超，伍立群，郭有安．Ｖｉｓｕａｌ　Ｂａｓｉｃ及Ｅｘｃｅｌ在水文频率计算中　的应用研究［Ｊ］．云南地理环境研究，２００５，１７（４）：１１—１４．　林莺，李世才．水文频率曲线简捷计算和绘图技巧［¨．水利水　电技术，２００２，３３（７）：５２—５３．　王剑峰，李宏伟，宋松柏，等．基于ＭＡＴＬＡＢ　ＧＵＩ技术的水文频　率计算［Ｊ］．人民黄河，２０１０，３２（１Ｏ）：４２—４３．　王正发．ＭＡＴＬＡＢ在Ｐ—ｍ型分布离均系数　值计算［Ｊ］．西　北水电，２００７，（４）：１—４．　许议和，魏晓妹．基于Ｍａｔｌａｂ的Ｐ一Ⅲ型曲线绘制软件的研发　与应用［Ｊ］．水电能源科学，２０１０，２８（７）：１５—１７．　叶守泽，詹道江．工程水文学［Ｍ］．北京：中国水利水电出舨　社，２０００：１８８—１９３．　¨　ｉ