Excel在适线法中的应用

水文资料一般都比较短, 工程上所需要的稀遇频率的设计值 (如百年一遇) 往往在经验频率曲线之外, 常通过在海森概率纸上将曲线外延的方法来求得。为减少定线和外延的任意性, 下面以皮尔逊Ⅲ型频率曲线为例, 通过Microsoft Excel的内置函数、图表处理功能及VBA编程优选频率曲线的统计参数, 求得指定频率的设计值。

1 适线准则的选择

设某一随机变量X服从皮尔逊Ⅲ型频率曲线分布, 它的3个统计参数分别为均值Xa、离势系数Cv和偏态系数Cs, 适线法的特点是在一定的适线准则下, 求解与经验点据拟合最优的频率曲线。

一般可根据洪水系列的误差规律, 选定适线准则。当系列中各项洪水的误差方差比较均匀时, 可考虑采用离差 (为经验频率点据与相同频率的皮尔逊Ⅲ型频率曲线的纵坐标之差) 平方和准则;当绝对误差比较均匀时, 可考虑采用离差绝对值和准则;当各项洪水 (尤其是历史洪水) 误差差别比较大时, 宜采用相对离差平方和准则。

本文采用离差平方和准则, 来阐述皮尔逊Ⅲ型频率曲线统计参数的优选过程, 其目标函数为

式中:Xi为样本系列中第i项的取值;Pi为Xi的经验频率, 可根据样本的特性, 按连序系列或不连序系列公式计算[1];XPi为皮尔逊Ⅲ型频率曲线上与经验频率Pi相应的纵坐标值。使上式最小的统计参数即为所求。

2 基于Excel的适线参数优选

2.1 计算经验频率及其相应的皮尔逊Ⅲ型频率曲线上的值

(1) 计算经验频率。在Excel工作表的第l列, 输入样本系列值, 并按由大到小排序;在第2列计算样本各项的经验频率Pi, i=1, 2, …, n。为便于在海森几率格纸上绘图, 在第3列计算经验频率Pi在海森几率格纸上的横坐标值li, 其计算公式为

(2) 初估统计参数。在Excel工作表的第4、5列, 分别计算样本系列的模比系数Ki和 (Ki-1) 2;选取两个单元格 (比如I20和I21) , 利用Excel的统计功能, 根据样本系列资料情况, 按连序系列或不连序系列的矩法估计值公式[2]分别计算样本的均值Xa, 离势系数Cv, 并根据研究对象的特点, 选取Cs值, 可取Cs=2Cv, 填人另一单元格中 (比如I22) 。

(3) 计算皮尔逊Ⅲ型曲线上与经验频率相应的Xp值。在工作表的第6、7列按以下公式计算离均系数Φp及Xp的值:

(4) 计算离差平方和。在工作表的第8列, 计算同一经验频率Pi下, 经验频率点纵坐标与皮尔逊Ⅲ型曲线上纵坐标差值的平方, 即 (Xi-Xpi) 2, 并统计离差平方和, 其结果放在单元格中 (比如I25) 。

2.2 利用Excel的VBA, 实现统计参数优选

离差平方和是统计参数Xa, Cv, Cs的函数, 因此可通过步长逐步调整Xa, Cv, Cs的值 (主要调整Cv, Cs值) , 来寻求目标函数的最小值。

在工作表中, 插入命令按钮, 如CommandButton1, 并编写如下代码:

单击该命令按钮, 可以自动优选出随机变量的三个统计参数, 放在TextBox1中。

参见文献[2], 在Excel中绘制海森几率格纸、经验频率点的散点子图及皮尔逊Ⅲ型分布曲线折线子图。

3 实例分析

现用本文介绍的方法对表1年最大流量系列进行适线, 优先后的统计参数结果为:Xa=8856.8 m3/s, Cv=0.46, Cs=0.95。

4 结语

本文通过Microsoft Excel的内置功能完成频率曲线的统计参数优选, 不需查表, 编程简单, 特别适合广大技术工作者使用。

摘要：推求设计洪水时中常采用图解适线法, 但计算绘图工作量大, 计算结果也因人而异, 为此, 本文通过Microsoft Excel的内置函数、图表处理及脚本编程, 实现了参数优选的程序化和规范化。

关键词：适线法,皮尔逊Ⅲ型频率曲线,统计参数,Excel

参考文献

[1] 王燕生.工程水文学[M].北京:水利电力出版社, l995.

[2] 林莺, 李世才.水文频率曲线简捷计算和绘图技巧[J].水利水电技术, 2002, 33 (7) :52～53.