2024年4月30日发(作者：)
第二讲 Excel用于水文频率计算
一、 随机变量的统计参数
1． 算术平均值
设某一随机变量的样本系列为x
1
，x
2
,…,x
n
,把它们的总和除以项数n即得算术平均数，简称均值，计算公式
如下：
x
1
x
2
x
3
x
n
1
n
x
x
i
nn
i1
2． 均方差σ与变差系数C
v
此二参数用来反映随机变量分布得离散程度。
C
v
＝
K
i1
n
i
1
2
n1
3． 偏态系数C
s
此参数衡量随机变量的分布在均值两边是否对称以及不对称(偏态)程度的参数:
C
s
3
K1
i
i1
n
n3
C
v
3
日常工作中，经常要对一些样本系列进行排频并绘制PⅢ频率曲线,现有的程序在适用性和经济上都不尽如
人意,而常用的EXCEL函数功能强大，经过一些简单设置，完全可以胜任此项工作.
绘制PⅢ频率曲线的步骤:首先用矩法、权函数法等求出样本系列总体的三个统计参数 、Cv、Cs，然后求
出与不同P对应的各个xp值，并作为一个系列，最后用XY散点图在EXCEL图表中绘出。本文对以上过程中
的一般方法不再赘述，仅就绘制图形作一简介。
频率格纸的制作PⅢ频率曲线是绘制在频率格纸上的，其X轴上的刻度为对数刻度，绘有纵向网格线，而
EXCEL缺省的图表都不具备以上功能，必须要进行一些设置.
1.1 X轴对数刻度向线性刻度的转换
我们在绘制PⅢ频率曲线的同时,也要将同一系列的经验频率数据点绘出，以检验数据点的拟合情况.虽然一
些水文书中给出了频率格纸横坐标分格表，但此表不可能准确地给出对应于经验频率数据点的横坐标。下面以
图1为例,说明用NORMSINV函数求线性横坐标的方法。
图1
在单元格B2中输入“=NORMSINV(A2％)”，在单元格C2中输入“=—＄B＄2+B2"，对于B、C两列
其它单元格可通过向下填充完成设置.
1。2 纵向网格线的绘制
纵向网格线的绘制是通过向图表中添加一个系列的XY散点图来完成的，见图1中C、D两列.在（0.000,0）
→（0。000,7000）→（0。179，7000）→（0。179，0）过程线中,第1和第2点、第3和第4点间构成纵
向网格线，第2和第3点间与次横坐标轴重合。不同样本系列的Y轴起始和终止刻度是不同的，为了增强其适
应性,我们在单元格D2、D5中输入“=＄E＄2”，在单元格D3、D4中输入“=$F＄2”,见图1。
1。3 X轴对数刻度的标注
向2中的数据系列添加数据标签,通过移动标签位置,修改标签内容,完成主次X轴对数刻度的标注。
用GAMMAINV函数计算对应于P的XP值
GAMMADIST为γ分布函数,其反函数GAMMAINV可返回具有给定概率的γ累积分布的区间点。但是我
们并不能直接应用GAMMAINV，因为GAMMADIST与PⅢ曲线两者的γ分布函数关系式不尽相同.
1)PⅢ曲线数学上常称γ分布，其概率密度函数为
f
x
a
a
xa
0
a1
e
xa
0
(1）
式中 Γ（α)—-α的γ函数；
α,β，a0—-三个参数。
这三个参数与总体的三个统计参数
x
、Cv、Cs具有下列关系：
a
2C
v
2
4
ax1
0
C
s
xC
v
C
s
;
C
s
2
；
（2）
2)P与xp间的关系式为
PP
xx
P
a1
xa
xaedx
0
a
x
(3）
0
p
a
3)EXCEL中γ概率密度函数的计算公式如下：
1
a1
f
x,a,
a
xe
a
（4)
x
通过对比，可以看出（1）式与（4）式的参数与变量间有如下对应关系：
α-α；β- ；x—a0—x
总结以上分析，可得求xp的方法和步骤:首先由
可。
、Cv、Cs根据（2）式求出α,β，a0后代人（5）式即
xp=GAMMAINV(1—P,α, ）+ a0 （5）
样本系列容量加大后图表的自动更新
一般情况下,不同的样本系列其容量也不相同，当数据量加大后，就要修改图表的数据范围。下面介绍的方
法，可在工作表中的数据量加大后,自动更新图表的数据范围，步骤如下.
1）创建如图2所示的工作表。
2）选择“插入”→“名称"→“定义”命令,弹出“定义名称”对话框，在“当前工作簿中的名称”框中输
入X,在“引用位置"框中输入公式：=OFFSET（频率曲线表！$C＄2，0,0，COUNT(频率曲线表！＄C：＄C)）
3)单击“添加”按钮。请注意,OFFSET函数引用第一个数据点（单元格C2）,并且使用COUNT函数得到
列中数据点的数量。
4)在“当前工作簿中的名称”框中输入YA，在“引用位置”框中输入公式：=OFFSET(频率曲线表！＄D
＄2,0,0，COUNT(频率曲线表！＄C:＄C))
5） 单击“添加”按钮和“确认”按钮关闭对话框。
6) 激活图表选择系列数据。
7) 使用步骤2和步骤4定义的名称重新设置范围引用。公式为:=SERIES(”YA"，PⅢ频率曲线.xls！X，PⅢ
频率曲线.xls！YA,3）
执行完以上步骤后，当C列和D列的数据量加大后，图表将自动更新。
发布评论