Excel在桥梁水文分析计算中的应用

将式（8）变换为：
1− P =
tp 1 t α −1 e − t dt Γ (α ) ∫0
tp 称为标准 Γ 分布分位数。Excel 软件中的内置函数 GAMMAINV 返回 Γ 累积分布函数的反函数(分位数)，则 tp
4 可用下式计算：tp = GAMMAINV (1 − P, C 2 ,1) S
P=
1 ∞ α −1 −t 1 t p α −1 −t t e dt = 1 − t e dt ∫ t Γ (α ) p Γ (α ) ∫0
χp
(8) (9)
Microsoft Excel 10.0 运算结果报告 目标单元格 (最小值) 单元格 名字 初值 终值 $S$7 Y轴（ 925350.9 925350.8733 可变单元格 单元格 名字 初值 终值 $B$3 CV 0.323776 0.323776132 $B$5 CS 0.572759 0.572758875 约束 单元格 名字 单元格值 公式 状态 型数值 $B$5 CS 0.572759 $B$5<=5*$B$3 未到限制值 1.0461 $B$5 CS 0.572759 $B$5>=$B$3 未到限制值 0.249
上式中：tp＝ β ( χ p − α 0 ) 在水文计算中，随机变量 χ p = (1 + Φ p C v ) χ
Φ
p
可用下式计算： (10)
p
如表 4 结果，经优化后水文变量统计参数为： χ = 3364m 3 / s ， Cv = 0.324 ， C s = 0.573 。按照 3.2 节方法，用 优化后统计参数绘制 P-Ⅲ型理论频率曲线。如图 1 所示。从 3 而得出该大桥百年一遇设计流量 Q1% = 6344m / s 。
116
中 国 水 运 据系列绘制理论频率曲线。 表3 理论频率曲线部分数据表
样本计算值 3 36 3 . 98 0 0 . 4 27 适线参数值 均值 3 36 3 . 98 0 . 33 CV 2 C S/ C V 0 . 66 CS 序号 1 2 3 4 理论频率P （％） 0 . 01 0 . 02 0 . 03 0 . 04
)
(3) (4) (5) (6) 的伽玛函数。 出现的累
∑ x i − x i'
(
)
2
= min ， χ‘ i
χp
表4
规划求解运算结果表
≥χ
α
P
),对上式（3）进行积分得：
0
α −1 ∞ β ( χ − α ) e − β ( χ −α ) dx 0 P = Γ(α ) ∫χ
P
(7)
令 tp＝ β ( χ − α 0 ) ，则有：
(15)
2．P-Ⅲ型理论频率曲线的绘制 根据式(15)计算各频率 P 对应的水文变量设计值 χ p ， 在 E8 单元格中输入“=(1+B$3*((B$5/2)*GAMMAINV((1B8%),4/B$5^2,1)-2/B$5))*B$2” ， 其它单元格用填充柄向 下填充完成，如表 3 中的 Y 轴；根据式(1)计算各频率 P 对应 的横坐标值 LP,如表 3 中的 X 轴； 在“流量频率曲线”工作表的图中设置源数据．以 P 对 应的横坐标值 LP 为 X 值， 以对应的设计值 χ p 为 Y 值添加数
=
χ
− χ
变换为：
Φp =
CS 2 tp − 2 Cs
(12)
流量Q(m3/s)
6500 6000 5500 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0 0.01 0.1 0.5 1 2 5 10 20 30 40 50 60 70 80 90 95 98 99 99.5 99.9 99.99
频率 （％） 0.01 0.01 0.02 0.02 0.03
海森机率格纸部分数据点
海森机率格纸参数设置
最大值 8000 0 机率格纸频率与重现期刻度 Y轴 0 8000 8000 0 0 频率 （％） 0.01 0.01 0.05 0.05 0.1 至中值 50%的水 平距离 -3.719 -3.719 -3.291 -3.291 -3.090 X轴 0.000 0.000 0.428 0.428 0.629 Y轴 0 8000 8000 0 0
第 08 卷 2008 年
第2期 2月
中 国 水 运 China Water Transport
Vol.6 February
No.2 2008
Excel 在桥梁水文分析计算中的应用
朱 俊 ，付义祥 ，刘
1 2
杰
3
（1 江西省交通科学研究院，江西 330038；2 武汉理工大学 交通学院，湖北 武汉 430063； 3 中交二航局，湖北 武汉 430014） 摘 要：跨河桥梁设计中，水文分析是进行大中桥孔设计的重要依据。而在确定桥梁设计洪水流量的过程中进行的
重现期（年）
10000 8000 7500 7000 经验频率点据 优化理论曲线 1000 200 100 50 20 10 5 2 5 10 20 50 100 200 1000 10000
Cvχ (11) 则离均系数 Φ p 为： 根据（5） 、 （6） 、 （9） 、 （11）进行数学推导，将式（11）
纵坐标Q（m 3/s）
文献标识码：A
盱江属长江流域抚河水系，流经南丰县长约 54 公里， 县境以上流域控制面积 3376 平方公里。拟修建的南丰县曾 巩大桥横跨盱江，西接南丰县城的曾巩大道，东接桥背乡。 该桥设计洪水频率为百年一遇，桥址上游设有南丰水文站。 由于桥址距水文站较近，因此桥位与水文站有相关关系，可 推算桥址处流量。水文统计计算中，常常需要绘制经验频率 曲线，常见的为皮尔逊Ⅲ型曲线。在配线时，需要调整 Cv， Cs 参数进行计算。传统方法采用目估法，观察拟合效果，除 此，可以采用适线准则判断最佳的配线， 借助 Excel 的功能， 可以较简便的完成配线过程。 一、绘制海森机率格纸 1．绘制基本原理和方法 海森机率格纸的横坐标与频率值的标准正态分布分位数 有 关 。 标 准 正 态 分 布 分 位 数 可 用 Excel 软 件 的 函 数 NORMSINV(P) 直 接 计 算 。 由 于 标 准 正 态 分 布 分 位 数 在 P=50％时为零。而海森机率格纸在 P=0.01％时的横坐标值 为零。因此海森机率格纸横坐标值计算公式可表示为： Lp＝－NORMSINV(0.01%)＋NORMSINV(P) 2．海森机率格纸网格线的绘制 海 森 机 率 格 纸的 横 向 网 格 线为 均 匀 分 布 ，可 直 接 由 Excel 软件的图表功能自动生成，而纵向网格线的绘制是通 过向图表中添加一个系列的 XY 散点图或折线图来完成的， 见表 1 中 C、D 两列，即为 X 系列值和 Y 系列值。从点 (0.000,0)→(0.000,8000)→(0.179,8000)→(0.179,0)可绘 出锯齿状折线， 第 1 和第 2 点以及第 3 和第 4 点间构成纵向 网格线，第 2 和第 3 点间与次横坐标轴重合。由于不同样本 系列的 Y 轴最小和最大刻度是不同的，为了增强其适应性， 可在单元格 D6、D9 中输入“=$D3” ，在单元格 D7、D8 中输入“=$D$2” ，以此类推，见表 1。 3．频率与重现期刻度的标注 收稿日期：2007-11-30 作者简介：朱 俊 江西省交通科学研究院 （330038） 付义祥 武汉理工大学交通学院 （430063） (1)
最小值 机率格纸网格线 至中值 50%的水 平距离 -3.719 -3.719 -3.540 -3.540 -3.432 X轴 0.000 0.000 0.179 0.179 0.287
在表 1 的工作表$F$6:$F$53 单元格中输入机率格纸频 率刻度对应的频率值 P；采用 1.2 节计算方法计算$G$6: $G$53 单元格的值，求出 X 值并输入 Y 值；向“流量频率 曲线”工作表中添加 XY 的散点数据系列；设置该系列数据 标志格式，修改数据标签为频率刻度值和重现期刻度值。 二、计算样本统计参数并绘制经验频率点据 1．经验频率的计算 根据经验频率计算公式。计算相应的经验频率值，然后 采用公式(1)求得相应的经验频率横坐标(X 轴)，如表 2 所示。 表2 经验频率部分点据数据表
对应横 坐标值 1. 65 7 1 1. 95 9 2 2. 15 4 3 2. 30 3 3 实测经验频率点据计算 经验频率 流量按大到小排 P= m/(n +1)* 10 0 列 6 22 5 5 70 8 5 17 4 4 85 2 2.0 3.9 5.9 7.8
序号 1 2 3 4
2．样本统计参数的计算 对于连序的样本系列。变差系数 CV 用下式计算： σ （2） Cv= χ 算系列的 χ 和 CV 值。对于 CS 值一般直接取 CS=KCV。 3．点绘经验频率点据 对于不连序的样本系列。可用水文计算中的相应公式计
γ 分布函数，其反函数 GAMMAINV γ 可返回具有给定概率的 累积分布的区间点。但是我们并不
能直接应用 GAMMAINV，因为 GAMMADIST 与 PⅢ曲线 两者的
至 P ＝ 50 ％ 处 X轴 （ 设 计 水平距离 频率） - 3 . 71 9 0 16 5 0 - 3 . 54 0 0 83 8 0 . 17 8 9 33 - 3 . 43 1 6 14 4 0 . 28 7 4 02 - 3 . 35 2 7 94 8 0 . 36 6 2 22
第 08 卷
据表 2 在“流量频率曲线”工作表图中设置源数据。以 经验频率对应的横坐标值为如图 1 所示。 三、绘制 P-Ⅲ型理论频率曲线及适线拟合 GAMMADIST 为 1．用 GAMMAINV 函数计算对应于 P 的 χ p 值
式(13)右边的积分式称为标准化 Γ 分布函数；