基于VB和Excel的水文頻率計(jì)算軟件的開發(fā)及應(yīng)用

秦國民

(中國電建集團(tuán)西北勘測(cè)設(shè)計(jì)研究院有限公司，西安 710065)

0 前 言

Microsoft Excel是微軟公司的辦公軟件Microsoft office的組件之一，具有強(qiáng)大的數(shù)據(jù)存儲(chǔ)、處理、分析能力，廣泛應(yīng)用于工程設(shè)計(jì)、管理、統(tǒng)計(jì)財(cái)經(jīng)、金融等眾多領(lǐng)域，也是專業(yè)技術(shù)人員常用的工具。用戶可以使用Excel VBA開發(fā)加載宏，或使用VB、C#等工具開發(fā)基于Excel的插件(COM加載項(xiàng))。加載宏較插件更容易開發(fā)，但在安全性方面不如插件。

水文頻率計(jì)算是工程水文分析計(jì)算中的重要內(nèi)容，隨著計(jì)算機(jī)技術(shù)的發(fā)展，水文工作者相繼采用VB6.0、Excel、Matlab等工具編制了一些水文頻率計(jì)算軟件[1-8]。為了改善設(shè)計(jì)手段，提高工作效率，參照最新設(shè)計(jì)洪水計(jì)算規(guī)范的要求，在總結(jié)以往生產(chǎn)實(shí)踐的基礎(chǔ)上，用VB6.0開發(fā)了適用于32位Excel的功能強(qiáng)大的水文頻率計(jì)算軟件，以插件的形式與Excel無縫接合，從而讓繁瑣的工作變得方便快捷。

1 基本原理

水文頻率計(jì)算是根據(jù)某水文現(xiàn)象的統(tǒng)計(jì)特性，利用現(xiàn)有水文資料，分析水文要素量值與出現(xiàn)頻率(或重現(xiàn)期)之間定量關(guān)系的工作過程。主要是利用現(xiàn)有水文資料組成樣本系列，選擇合適的頻率曲線線型(中國一般有P-Ⅲ型、極值Ⅰ型等)，采用一定的曲線擬合準(zhǔn)則確定頻率曲線參數(shù)，得到水文要素的統(tǒng)計(jì)規(guī)律，從而可根據(jù)需要查圖得到需要的值。

中國工程水文設(shè)計(jì)中，最常用的曲線擬合準(zhǔn)則是目估適線法。該方法首先依據(jù)經(jīng)驗(yàn)點(diǎn)據(jù)通過矩法或其他方法估計(jì)統(tǒng)計(jì)參數(shù)的初始值，然后根據(jù)該初始參數(shù)繪制頻率曲線，并目視判斷頻率曲線與經(jīng)驗(yàn)點(diǎn)據(jù)的擬合情況，必要時(shí)調(diào)整參數(shù)并繪制調(diào)整參數(shù)后的頻率曲線，直到得到滿意的點(diǎn)線擬合結(jié)果。根據(jù)最終確定的頻率曲線推求相應(yīng)于各種頻率(或重現(xiàn)期)的水文要素設(shè)計(jì)值。

上述目估適線過程中，主要的工作量集中在計(jì)算統(tǒng)計(jì)參數(shù)的初始值和根據(jù)統(tǒng)計(jì)參數(shù)初始值或調(diào)整值進(jìn)行頻率曲線的繪制，中間有較多重復(fù)的計(jì)算和繪圖工作，以便達(dá)到目視判斷擬合效果的目的，手工計(jì)算的話相當(dāng)繁瑣。針對(duì)水文頻率計(jì)算方法和內(nèi)容，基于Excel提供的接口，創(chuàng)建VB外接程序工程[9]，編制了通用計(jì)算軟件。軟件依托于Excel運(yùn)行，主要以自定義工具按鈕和必要的用戶窗體的形式實(shí)現(xiàn)各項(xiàng)功能。

2 軟件設(shè)計(jì)

2.1 主界面設(shè)計(jì)

為保持Excel主界面的簡潔，水文頻率計(jì)算的功能主要集中在“工程水文頻率適線”工具欄和頻率曲線統(tǒng)計(jì)參數(shù)調(diào)整窗口，如圖1、2所示。

圖1 “工程水文頻率適線”工具欄圖

圖2 頻率曲線統(tǒng)計(jì)參數(shù)調(diào)整窗口圖

“工程水文頻率適線”工具欄主要按鈕的功能說明如下。

(1) 分布線型選擇：選擇擬采用的分布線型，目前可供選擇的線型有P-Ⅲ型、對(duì)數(shù)P-Ⅲ型、極值-Ⅰ型(Gumbel型)；

(2) 有效數(shù)字位數(shù)：設(shè)置計(jì)算過程中的有效數(shù)字位數(shù)，對(duì)于流量系列，一般選3；

(3) 開始：點(diǎn)擊“開始”按鈕后，軟件根據(jù)Excel活動(dòng)工作表當(dāng)前選中項(xiàng)目自動(dòng)確定要執(zhí)行的功能，見表1。

表1 不同Excel當(dāng)前選中項(xiàng)目對(duì)應(yīng)的“開始”按鈕功能表

圖3 “優(yōu)選參數(shù)”設(shè)置窗口圖

2.2 功能模塊

軟件主要包括統(tǒng)計(jì)參數(shù)計(jì)算、高精度離均系數(shù)Φp值計(jì)算、三參數(shù)優(yōu)選、經(jīng)驗(yàn)適線、成果輸出等模塊。

(1) 統(tǒng)計(jì)參數(shù)計(jì)算模塊：根據(jù)用戶選中的含樣本系列的單元格，用矩法和線性矩法初估均值、Cv等統(tǒng)計(jì)參數(shù)，支持連序系列和不連序系列。

(2) 高精度離均系數(shù)Φp值計(jì)算模塊：參考Matlab統(tǒng)計(jì)工具箱中有關(guān)專用數(shù)學(xué)函數(shù)，編制高精度離均系數(shù)Φp值算法，解決了Excel 2003內(nèi)部函數(shù)GAMMAINV存在數(shù)值發(fā)散區(qū)的問題[10]。

(3) 三參數(shù)優(yōu)選模塊：以初估的統(tǒng)計(jì)參數(shù)為初始值，采用基于群體智能的自由搜索算法[11-12]，在離(殘)差平方和準(zhǔn)則或離(殘)差絕對(duì)值和準(zhǔn)則的適線準(zhǔn)則下，求解與經(jīng)驗(yàn)點(diǎn)據(jù)擬合最優(yōu)的頻率曲線的統(tǒng)計(jì)參數(shù)。

(4) 經(jīng)驗(yàn)適線模塊：用戶可以調(diào)整各統(tǒng)計(jì)參數(shù)，直到經(jīng)驗(yàn)點(diǎn)據(jù)和頻率曲線配合較好為止，也可在同一張頻率曲線圖添加多條頻率曲線，方便對(duì)比檢查成果。

(5) 成果輸出模塊：直接在Excel中輸出頻率曲線圖和水文要素設(shè)計(jì)成果表，輸出成果美觀大方，能滿足標(biāo)準(zhǔn)化、規(guī)范化的要求。

3 實(shí) 例

某水文站有1979—2013年,共35 a實(shí)測(cè)洪峰流量系列，實(shí)測(cè)最大值為4 890 m3/s。調(diào)查到1906年歷史洪水，洪峰流量6 500 m3/s，量級(jí)突出，作特大值處理。經(jīng)考證，重現(xiàn)期為81 a。

將原始數(shù)據(jù)輸入Excel工作表，擴(kuò)展選中調(diào)查考證參數(shù)區(qū)和原始數(shù)據(jù)所在單元格(2列：年份、數(shù)值)，點(diǎn)擊“工程水文頻率適線”工具欄的“開始”按鈕(見圖4)，程序計(jì)算完初估參數(shù)后彈出“優(yōu)選參數(shù)”設(shè)置窗口，根據(jù)地區(qū)規(guī)律，Cs/Cv的上限和下限均設(shè)為5，其他參數(shù)上限和下限取默認(rèn)值，即只優(yōu)選均值和Cv，點(diǎn)擊“開始優(yōu)選”，即得到頻率曲線圖(見圖5)。

圖4 數(shù)據(jù)輸入及開始計(jì)算界面圖

圖5 水文頻率計(jì)算成果輸出界面圖

4 結(jié) 論

軟件是用VB6.0編制的Excel插件，界面友好，功能齊全，并經(jīng)過長期測(cè)試和反復(fù)優(yōu)化，保證了其穩(wěn)定、高效的優(yōu)勢(shì)。較其他同類軟件，有以下特點(diǎn)：

(1) 集成在Excel中，數(shù)據(jù)輸入和輸出均在Excel的活動(dòng)工作表完成，不需要單獨(dú)建立數(shù)據(jù)輸入文件，避免了在資料輸入、輸出格式整理上占用大量時(shí)間的問題。

(2) 采用了高精度的數(shù)值算法和高效的智能優(yōu)化算法，保證計(jì)算結(jié)果的準(zhǔn)確、可靠。

軟件在生產(chǎn)實(shí)踐中得到很好的應(yīng)用效果，有較強(qiáng)的實(shí)用性。存在的一些不足，主要是目前支持線型較少，雖能滿足中國相關(guān)規(guī)范計(jì)算要求，但對(duì)國外常用線型、計(jì)算方法的支持方面有待進(jìn)一步研究。