P_III型分布频率分析在Excel中的实现及应用

# x =
1 n
xi
( 2)
Cv =
# 1
n- 1
( ki - 1) 2
( 3)
收稿日期: 2006- 03- 13 作者简介: 黄继文, 男, 山东省水利科学研究院, 工程师。
# Cs =
( ki - 1) 3 ( n - 3) Cv 3
( 4)
ki =
xi x
2. 3 配 线
xi 系列不同频率值的计算公式为: x % = x ( 1+ Cv )
( 3) 用经验频率公 式计算出各年份经验频率。 ( 4) 输入计算 X 的公式 ( = x - b) 。 ( 5) 调 用 GAMMADIST 函数 求出 该 系列 的累 积 概率 ( 或 频 率) , 其 超 过 概 率 形 式 为: 1 - GAMMADIST ( X, alpha, beta, cumulative ) , 其中 alpha 为 初 步 确 定 的 ; beta 为 初 步 确 定 的 1 ; cumulative 为( TRUE) ( 返回 累积概率分布) 。函数计算 频率 的公式: ( = 1- GAMMADIST( x , , 1 , TRUE)) , 见 图 1。 ( 6) 输入 计算 频率 与经 验 频率 的相 对 误差 平 方及 求和 公 式, 计算公式见图 2。
其中不同频率 值可 根据 Cs 值和 P- III 型分 布 值 表查 得, 进而求出不同频率 x % 值。将求出的 x % 值按照( p m , x % ) 也 点在几率格纸上, 再用光滑曲线将该组点据联接, 检查这条曲线 与经验点据的配合 情况, 如配合 不好, 则调整 Cv 和 Cs 值, 再画 一条曲线, 直到曲线与经验点据拟合良好为止。
图 1 GAMMADIST 函数计算示意
图 2 相对误差计算示意 ( 7) 利用 Excel 中的 规 划求 解功 能 优化 配 线, 反 求 、 、b 值。可采取最小二乘法 准则以 偏差平 方和最 小为目 标, 但 为了 照顾频率值较小的点以相对误 差平方和最小为目标较好。调用 Excel 中规划求解功能, 以 b 、 、1 值为变量, 计算方法见图 3、图 4。如图所示, 规划求解( 目标 单元格) 为 相对误差 平方 和, 目标 值选( 最小), 可变单元格为 b 、 、1 值, 然后点 击( 求 解)。 最终 求出的结果 b、 、 1 值分别为 167. 27、6. 05 和 77. 86。 ( 8) 根据确定的 b 、 、1 值利用 GAMMAINV 函 数就可 以计 算出不同频率下的降 水量值, 计算方法见图 5。
( 3) 在 P- III 分布的概率密度函数中 b < x < % , 我 们通 常应用时 x 代表的是降 水量、径流量 等, 因 此应有 b !0。 若计 算结果中出现 b < 0 的情况, 可令 b = 0, 仅将 、1 作为变 量进 行规划求解, 求得相应的 、1 值。
( 4) 如果在规划 求解 中出现 目标 单元格 错误 或不 收敛, 可 降低计算精度要求重 新求解。
时间 年
1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003
降水量 mm 667 703 924 810 586 621 450 472 933 561 844
具体方法步骤如下: ( 1) 估计系列样本参数。根据式( 2) 、式( 3) 、式( 4) , 求出算 术平均值 EX , 并初 步确定变差系数 Cv 和偏态系数 Cs 。对上述
∋9∋
水资源研究 第 27 卷 第 4 期( 总第 101 期) 2006 年 12 月
P- III 型分布频率分析在 Excel 中的实现及应用
黄继文
( 山东省水利科学研究院, 山东 济南 250013)
摘 要: 探讨没有其他专业绘图 软件帮助的情况下, 在 Excel 中 利用规划求解、GAMMADIST 和 GAMMAINV 函数 实现用 P- III 型曲线进行水文频率计算, 为广大基层水 文工作者 提供一个既 简单又便 捷的水文 频率分 析计算 的方法。 关键词: Ex cel; P- III 型分布; 频 率分析; GAMMADIST 函数
b=
EX ( 1 -
2 Cv Cs
)
( 6)
=
4 Cs 2
( 7)
2
= EXCv Cs
( 8)
x 的分布函数为:
x
& F( x ) = ( ) ( x - b ) - 1 e- ( x- b) dx
Байду номын сангаас( 9)
b
∋7∋
其超过概率形式为:
F1( x) = 1- F( x) = 令 X = x - b , 则:
计算频率与经验 频率 拟合 情况 见图 6。 由图 6 可以 看出, 计算频率和经验频率 拟合情况是比较好的。
图 3 规划求解过程示意
图 6 计算频率与经验频率拟合情况示意
( 9) 计算不同频率 的降雨量。输入采用 GAMMAINV 函数计 算 x 的公 式:( = b + GAMMAINV ( 1 - P, , 1 ) ) , 由此 计算 出 不同频率下的降雨量, 并与 传统适线 法计算 的设计 值比较 如表 3。表中结果显示, 两种方 法计算 成果非 常接 近。因此, 在 没有
( 1) P- III 型分布的几 个参数 、 、b 的取值范围 可有较大 变动, 一般应采用经验值作为其初始值。
( 2) 本文在使用规划求解的 方法对计算频 率和经验频 率进 行拟合时, 选取的目标为计 算频率与 经验频 率相对 误差平 方和 最小, 主要是为照顾频率 值较小 的点。可能 在频率 值较大 的点 误差较大。
在 Excel 中有多种函数模块, 其中 在统计类 函数中 有 GAM
MADIST 函数, 即伽马分布函数。在其帮助说明中给出 的密度函
数表达式为:
f( x) =
1 1(
)
x
-
1 e-
x 1
( 12)
比较式( 5) 、( 11) 、( 12) 可知 1 = 1 。
该 函 数 的 语 法 结 构 为: GAMMADIST ( x , alpha, beta, cumulative ) , 其中 x 为用来计算伽马分布的数 值; alpha、beta 为 分布 参 数; cumulative 为 一 逻 辑 值, 决 定 函 数 的 形 式, 如 果 为 ( TRUE) 函数值返回累积分布函数, 如为( FALSE), 则返 回概率密 度函数。GAMMAINV 函 数 为 GAMMADIST 函数 的 反 函数, 可计 算给定概率的 ! 累积分布的区间点, 其语法结构 为: GAMMAINV ( p robability , alp ha, beta ) , 其中 p robability 为概率值; alp ha、beta 为参数。
在处理 降雨和径流等基 础资料 时, 往 往会对 其进行频 率分 析, 最常 用的方法是 适线法, 在没有软 件帮助的 情况下, 这种方 法往往比较 繁琐, 大大 降低了 工作效 率。本文旨 在探讨 如何利 用 Excel 进行适线法频率分析。
2 适线法简介
2. 1 经验频率计算
设有一个数值系 列 x i , 将 该系 列按从 大到 小的 顺序排 列, 得到:
x 1 ! x 2 ! ∀ ! xm ! ∀ ! xn 然后采用如下的经验频率公式:
m
pm = n + 1
m= 1~ n
( 1)
对每一个 x m 求出一个 p m , 称为经验频率, 并将其数值点在 几率格纸上, 称为经验点据。
2. 2 参数估计
上述数值系列可采用统计参数均值 EX 、变差系数 Cv 和偏 态系数 Cs 描述, 采用矩法计算, 其公式为:
降水量 mm 839 461 731 707 822 788 367 607 686 588 342
时间 年
1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992
降水量 mm 525 385 446 920 432 547 534 432 818 589 693
显然, 可以在 Excel 中利 用 GAMMADIST 函数 和 GAMMAINV 函数进行频率分析。
5 应用实例
根据 P- III 型分布密度函数和 GAMMADIST 函数的关系, 在 对某一系列( 降雨、径流或其他 系列) 采用 适线法 进行频 率分析 时, 可以在 Excel 中进行。
现有某站 1960~ 2003 年降水量系列资料, 见表 1。试 用 PIII 型分布进行适线分析, 并计算频率为 P = 50% 、P = 10% 、P = 1% 、P = 0. 1% 时的降雨量。
∋8∋
降水系列按照从大到小地 顺序进 行排列, 求出 其统计 参数, EX = 630 mm, Cv = 0. 28, Cs = 0. 43。
( 2) 根据式( 6) 、( 7) 、( 8) , 用 EX 、Cv 、Cs 计算出 、1 和 b 值 ( 注意 GAMMADIST 函数中的 beta 值应为 1 ) , 并将其数值 复制 作为计算频率的初始 参数。
%
& ( ) ( x - b) - 1 e- ( x- b) dx ( 10) x
F ( x ) = F( X + b) =
X
& ( ) ( X ) - 1 e- X dX 0
(0 ∃ X < % )
( 11)
此式即为 X 的 !分布函数。
4 Excel 中 GAMMADIST 和 GAMMAINV 函数语 法结构
1. 39
图 4 规划求解过程示意
图 5 不同频率降水量计算示意
详细计算过程及结果见表 2。 表 2 计算过程及结果
序号 时间 年 降水 x
经验
ki
X
频率 %
1
1964
1111 1. 763 2
2. 2
944
2
2001
933
1. 480 8
4. 4
766
∀
∀
∀
∀
∀
∀
43
1981
342
0. 542 5
3 P- III 型分布函数简介
P- III 型分布 ( 皮 尔逊 III 型分 布) 具 有如 下的 概率 密度 函 数:
f ( x ) = ( ) ( x - b) - 1 e- ( x- b)
(b∃ x< % )
( 5)
式中 , > 0。
3 个原始参数 , , b 可以用基本参数EX , Cv , Cs 表示如下:
95. 6
175
44
1968
313
0. 496 0
97. 8
145
合计
计算 频率 %
2. 0 7. 6 ∀ 97. 5 98. 9
相对 误差平方 0. 015 5 0. 175 0
∀ 0. 000 4 0. 000 1 0. 3735
6 问题讨论
在 Excel 中采用 P- III 型曲线进行频率分析时, 要注意以下 几个问题:
其他专业绘图软件帮助的情况 下, 采用此方法初估参数, 计算各 种频率下的设计值, 可以得到较高精度的设计成果。
表 3 两种方法计算成果比较
频率 %
本方法 mm 传统适线法 mm 百分比 %
0. 1
1 455
1 447
0. 59
1
1 194
1 185
0. 71
10
894
886
0. 96
50
613
604
表 1 某站 1960~ 2003 年降水量
时间 年
1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970
降水量 mm 674 674 624 528 1111 515 650 455 313 577 770
时间 年
1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981
1 概述
Excel 是美国微软公 司 Office 办 公软件 的组 件之 一, 是 一个 强大的电子表格 软件。 它可 以对 大量 数据 的表 格进 行各 种处 理, 用各种类型的图表形象地表示数据, 具有强有力的数据库管 理功能, 且方便直观、易学 易用。正因 为如此, 它已 应用于 众多 领域, 成为一般办公人员所必不可少的应用软件之一, 并受到了 越来越多用户的喜爱。