Excel 在水文计算中的应用之研究

1

Excel 在水文计算中的应用

摘要：本文提出了用Excel 软件解决水文计算中的涉及到特殊函数计算的方法，如P －Ⅲ型频率曲线、瞬时单位线和海森机率格纸等。实践证明，该途径计算准确、快捷和规范，有较高的实用价值。 关键词：Excel 软件；P －Ⅲ型频率曲线；离均系数Φp ；瞬时单位线；S （t ）曲线；海森机率格纸

水文计算涉及到一些特殊函数，如P －Ⅲ型频率曲线和瞬时单位线均是以不完全Г函数表示的，海森机率格纸是建立在正态分布基础上的。在使用这些特殊函数进行计算时，习惯上是查该函数或中间参数的数表，如P －Ⅲ型频率曲线离均系数Ф值表、纳须瞬时单位线S 曲线表和海森机率格纸横坐标分格表等。查表不仅会因数值截断产生舍入误差和由参数内插产生方法误差，而且会使计算机的快速连续计算因此而中断，所以传统水文计算方法精度低，速度慢。最近许多学者探讨水文计算中这些特殊函数的解法问题，以期提高水文电算的精度和速度，但这些解法基本上是某理论方法的近似或逼近，相应的计算机算法较为复杂，不易被生产单位所掌握和运用，笔者经过深入研究发现，目前社会上广为普及的微软办公集成软件Office 的组件之一Excel 电子表格软件，可以很好地解决上述水文计算中所遇到的问题。实践证明，该途径计算准确、快捷，并满足标准化和规范化的要求。

1.P-Ⅲ型频率曲线计算的问题

1. 1 Φp 值的计算

P-Ⅲ型频率密度曲线方程式如下：

()

)(100

)(a x e a x y ----Γ=βαα

αβ (1)

式中， 2

/4S C =α (2)

S V C C x /2=β (3) ()S V C C x a /210-= (4) 水文计算中需要的是超过频率P ，即：

2

()

dx e a x P p

x a x ?∞----Γ=)

(100

)(βαααβ (5) 令)(0a x t -=β，则：

()dt e t dt e t P p p

t t

t t ??--∞

--Γ-

=Γ=

011)

(111

αααα (6) 式中，

)(0a x t p p -=β (7)

由式(3)、(4)、(7)可得离均系数：

s

p S V

p p C t C C x x x 2

2-=

-=

Φ (8)

由式(8)有：

x C x P V p )1(Φ+= (9)

可见，Φp 值的计算是P-Ⅲ型频率曲线计算的核心。由式(6)和(8)可知，Φp 值与C S 和

P 有关，当C S 一定时，Φp 值仅是P 的函数，福斯特于1924年用直接积分法及近似积分法算出了部分Φp 值，之后有人对福斯特表进行了扩展和补充。八十年代后，为了研制频率计算计算机程序，一些学者试图通过近似求解不完全Г函数来解决计算Φp 值的问题，但算法较复杂，精度也不十分理想。

在Excel 软件中，函数GAMMAINV 为返回Γ偏态分布的累积函数的逆函数，即： GAMMAINV(P 、α、β) = x (10)

式中，P 为变量小于或等于x 的频率；α为分布参数，2

/4S C =α；β为分布参数，

2/S V C C x =β，当β=1时，为标准Γ分布。

函数GAMMAINV 使用迭代法来计算。给定一个频率值，函数GAMMAINV 开始迭代，直到结果精确到3×10－7

。如果经过100次迭代后，函数GAMMAINV 仍未收敛，软件返回错误值＃N/A ，如果计算结果溢出，则返回错误值＃NUM ！。

对照式(6)、(8)，有：

)1,4

,

1(2S

p C P GAMMAINV t -= (11)

S S

S p C C P GAMMAINV C 2

)1,4,1(22-

-=

Φ (12)

3

式中的P 为超过频率，给定某一P 和C S ，用式(12)就可求得相应的Φp 值。 1. 2 Φp 值的检验

用式（12）重新计算Φp 值表，当Cs=0时，改用标准正态分布函数的逆函数NORMSINV 计算。C S 取值范围：0~10，P 的取值范围：0.0001%~99.99%。具体计算结果如表1所示。

表1 用Excel 软件计算Φp 值的有效范围表

在表1的正常区内的Φp 值与文献[1] 、[２]完全一致，事实上发散区的Φp 值已不随P 的变化而变化，均和正常区的最后一个值相同。由于水文计算中，Cs 基本都在0.1~7.3之内，P<0.005%的情况很少见，所以用Excel 软件计算Φp 值完全能够满足生产上的要求。从公式的完整性考虑，本文的方法在P<0.005%，07.4的范围尚需进一步的探讨。

2.瞬时单位线计算的问题

河槽汇流的加里宁瞬时单位线和流域汇流的纳须瞬时单位线均是用不完全Г函数表示的，其一般形式如下：

k

t

n e k

t n k t u --Γ=1)()(1)( （13）

式中,u(t)为单位线的纵高；t 为时间；k 为特征河长或线性水库的传播时间，常数；n 为特征河长或线性水库的个数，常数。在应用瞬时单位线时，需通过瞬时单位线的累积曲线（即S 曲线）把瞬时单位线转换成时段单位线，再进行汇流计算。

可见，由参数n 、k 确定的瞬时单位线及S 曲线的计算是瞬时单位线法汇流计算的关键，瞬时单位线的S 曲线如下：

4

)()()(1)()(1

1/00k

t

d e k t n dt t u t S k n k t t

--??Γ== （14） 式（14）已制成S(t/k ，n)关系表，供生产部门查用。

在Excel 软件中，函数GAMMADIST 是用来研究具有Γ偏态分布的变量。

语法： GAMMADIST(x ，α，β，cumulative)

(15)

式中，x 为用来计算Γ分布的数值；α、β为分布参数；如果β=1，函数GAMMADIST 返回标准Γ分布；cumulative 为一逻辑值，决定函数的形式。如果为FALSE ，则返回概率密度函数：

βαααββαx

e x x

f --Γ=1)

(1),,( （16）

如果cumulative 为TRUE ，函数GAMMADIST 返回累积分布函数：

dx e x dx x f X F x

x

x

βαα

αββαβα--Γ==

?

?

1

)

(1),,(),,( （17） 式(15)中的x 、α、β用瞬时单位线的t 、n 、k 替换，当cumulative 为FALSE 时，得到的是瞬时单位线u(t)；当cumulative 为TRUE 时，得到的是瞬时单位线的S 曲线。 经校核，函数GAMMADIST 算得的各种n 、k 的S(t)值与文献[1] 、[２]中纳须瞬时单位线S 曲线表的结果完全一致。

3.海森机率格纸的问题

为了解决普通坐标纸上频率曲线两端曲率大，曲线不便外延的问题，1913年海森提出使用正态机率格纸，即以x 为纵坐标，以

?

∞

-x

x dx e 2

2

21π

为横坐标，使正态曲线在这种图纸上呈直线形式。尽管水文变量大多不呈正态分布，绘在海森机率格纸上的频率曲线仍是曲线，但曲率已大为减缓，所以生产上一直使用已印制好的海森机率格纸进行频率计算。八十年代以后，随着计算机的普及，水文频率计算由手工绘图到电脑绘示意图，由电脑示意图到自动绘制频率曲线，其中一个制约因素就是如何使电脑默认的普通坐标转换为海森机率坐标这一难题。而Excel 软件却能很好地解决绘制海森机率格纸以及在其上标注频率点据，并绘制频率曲线的问题。

取P 0为坐标原点，用下式将频率P 的直角坐标即可转换成海森机率格纸的横坐标D ： D=－NORMSINV （P 0）＋NORMSINV （P ） （18） 式中，函数NORMSINV 为返回累积标准正态分布对应的自变量。

用式（18）对频率P的坐标进行转换，即能方便地绘制海森机率格纸，也能把水文变量的经验频率点据和理论频率曲线自动绘制在海森机率格纸上，调整统计参数和选配理论频率曲线“所见即所得”，还可直接输出设计值和规范的频率计算图纸。

目前，Excel软件的应用越来越广泛，但大多停留在制作表格和简单的图形处理，这些应用尚不及Excel软件功能的20％。本文仅对水文计算中的一些常用的关键算法做了深入的探讨，事实上，Excel软件在相关分析、回归计算、方差分析、水位流量及水位库容等曲线的拟合和检验、时间序列分析、建模与参数估计等方面都具有其独到之处，此处不再赘述。

5