1198编号运用EXCEL图表进行海森格纸绘制1.pdf
13页
1 【【关键词】】Excel 函数 工程水文 频率曲线 Microsoft Excel （简称 Excel）是 Microsoft Office 中的一个 重要组件，具有强大的计算功能，为工程水文及水利计算中的有 关计算提供广阔的平台利用 Excel 可以有效提高工程水文及水 利计算的速度和精度，具有快速、简便、灵活的特点特别是使 用 Excel 的“拖动充填” 、 “排序” 、 “插入函数 x” 、 “图表”等功 能，充分体现其优越性，可以充分解决工程水文及水力计算的有 关问题 本文可以通过其内置功能介绍如何比较轻松准确的绘制水文 频率曲线其具体绘制步骤如下： 1.制作海森机率格纸 主要运用 EXCEL 电子表格中的 NORMSINV 函数进行计算 （详见图 1） 在表中 B2 单元格中输入公式： =NORMSINV(A2%) （1.1） 拖动单元格右下角的填充柄， 将公式复制到本列其他单元格 中 在表中 C6 单元格中输入公式： =-$B$4-(-B6) （1.2） 拖动单元格右下角的填充柄， 将公式复制到本列其他单元格 中 2 在表中 D2 单元格中输入最小值，如“0” ；在 D3 单元格中 输入最大值，如“1600” ；在 D4 单元格输入“=D2” ； 在 D5 单 元格输入“=D3” 。
拖动单元格右下角的填充柄，将公式复制到 本列其他单元格中 在表中 E2 单元格中输入公式： =1/A2*100 （1.3） 拖动单元格右下角的填充柄， 将公式复制至本列 E25 单元格 止 在表中 E26 单元格中输入公式： =1/(1-A26/100) （5） 拖动单元格右下角的填充柄， 将公式复制到本列其他单元格 中 3 图 1 海森机率格纸 2.绘制海森机率格纸 在图 1 表格中点击“插入”菜单中的“图表”命令，在“标 准类型”的“图表类型”表框中选择“XY 散点图” ，在“子图 表类型” 表框中选择“散点图” 单击“下一步”按钮 （详见 图 2） 图 2 单击 “系列” 标签， 在 “系列” 栏下点击 “添加” 按钮， 在 “X 值”文本框内选取数据区域“海森机率格纸！$C$2:$C49” ， 在 “Y 值”文本框内选取数据区域“海森机率格纸！$D$2:$D49” 之后点击“完成”按钮 （详见图 3） 4 图 3 在图中将鼠标置于 X 轴数字“4”上单击右键，按“清除” 按钮，清除水平轴显示的“0 2 4 6 8”数字 将鼠标置于 X 轴圆点上单击右键，点击“数据系列格式” ， 在出现的对话框内点击 “数据标志” 选项卡， 在 “数据标签包括” 栏内选择“X 值”后，按下“确定”按钮。
（详见图 4） 5 图 4 然后挨个点击在图内 X 轴上下出现的数字，将 X 轴下侧数 字逐一修改为表内“频率”列相对应的数字，将 X 轴上侧数字 逐一修改为表内“重现期”列相对应的数字如将“0.62874179” 改为“0.1” （详见图 5） 图 5 全部修改完后，右键单击图表空白处，在“图表选项”对话 框点击“标题”选项卡，在“图表标题”文本框内输入图表标题 将鼠标置于图内 X 轴圆点上单击右键选择 “数据系列格式” ， 在出现的对话框内点击“误差线 Y” ，在“显示方式”栏选择 “负偏差” ，在“百分比”文本框输入 100%，然后按“确定”按 钮，图表内将显示 X 轴刻度线 6 在图中将鼠标置于 Y 轴数字“1600”上单击右键，选择 “坐 标轴格式” ， 在对话框内选择 “刻度” 选项卡， 选择 “最大值” 并在 文本框内输入“1600” 然后按“确定”按钮 将鼠标置于 X 轴圆点上单击右键，点击“数据系列格式” ， 点击“图案”选项卡，在“数据标记”栏选择“无” ，按“确定” 按钮 至此，一张完整的海森机率格纸图绘制完毕 （详见图 6） 图 6 3.系列经验频率的计算公式 中华人民共和国行业标准水利水电工程设计洪水计算规 范(SL44-93)中对频率曲线的线型要求一般应采用 Pearson 型， 频率曲线的统计参数采用均值 ， 变差系数 Cv， 偏态系数 Cs， X 7 计算连续和不连续洪水系列，Cv，Cs 值。
X 3.1 P曲线数学上常称分布，其概率密度函数为： f(x)= ）（）（ ）（ ）（ 0 ax1a 0 a eax a EXCEL 中 概 率 密 度 函 数 的 计 算 公 式 如 下 ： Xp=GAMMAINV（1-P%，，）+a0 1 式中=4/（Cs2） =2/(CvCs) X a0=(1-2Cv/Cs) X 3.2 系列经验频率的计算 1.在 n 项连续洪水系列中， 按大小顺序排位的第 m 项洪水的 经验频率公式如下： Pm； 其中： m=1,2,3,...，n； 1n m 2.在调查考证期 N 年内有特大洪水 a 个， 其中有 l 个发生在 n 项连续洪水系列内，l 可以等于 0、1、a 中的任一个值不连 续系列经验频率计算公式如下： a 个特大洪水的经验频率为： Pm = 其中： M=1,2,3,...，a； 1N M 实测系列中剩余的（n-l）项的经验频率公式为： Pm=+(1-)； 1N a 1N a 1 ln lm 式中：M特大洪水排位序号，M=1、2、、a； 8 N调查考证期，指调查或考证的最远的年份 至样本资料最末一年的年数； n实测洪水项数； l实测洪水中提出作特大值处理的洪水个数； m实测一般洪水排位的序号，m=l+1, l+2, l+3、、n； Pm实测洪水第项的经验频率。
3.3 应用矩法进行统计参数初估 3.3.1 对 n 年连续系列，其计算公式如下： = Xn 1 n xi 1i S= 1-n ) 1i 2 n i xx（ Cv== S 1-n ) 1 1i 2 n i k（ 3.3.2 对 n 年不连续系列，其计算公式如下： =（+） XN 1 a 1j j x n 1i i -n a-N l l Cv= 1 ））（）（（ 2 n 1i i 2 1j j -n N 1-N 1 l l 特大洪水的洪峰流量或者洪量，j=1，2，，a，n，m3/s 或 m3； j 实测一般洪水的洪峰流量或洪量，i= +1， +2，，n，m3/s 或 m3； i ll 9 其他符号含义同前 3.4 制作经验频率计算表格， 计算出均值 ， 变差系数Cv详 X 见图 7） 图 7 经验频率计算 4. 绘制频率曲线 4.1 建立频率曲线表格 将 “经验频率计算”表中计算出的与实测洪水相对应经验 频率逐一填至“频率曲线”表中的 A 列（详见图 7 中 A 列红色 数字） ，将实测洪水值相应填至表中 D 列；另外应在 A 列中按顺 序适当添加一些建立频率曲线所必须的频率数值， 如 0.01%、 1%、 99%、99.9%等（详见图 7 中 A 列红色数字） 。
在表中 E2 单元格中输入公式： =$F$2*(1-2*$H$2/$I$2)+GAMMAINV(1- 10 A2%,4/POWER($I$2,2),$F$2*$H$2*$I$2/2) 拖动单元格右下角的填充柄， 将公式复制到本列其他单元格 中 4.2绘制频率曲线 将海森机率格纸图复制粘贴至“频率曲线”表，右键单击图 中空白区域，点击“原数据” ，在对话框内点击“系列”按钮 在“系列”栏下点击“添加”按钮，在“系列 2”栏“x 值” 文本框内选取数据区域“频率曲线！$C$2:$C72”, 在“Y 值” 文本框内选取数据区域“频率曲线！$D$2:$D72” 继续点击 “添加”按钮，在“系列 3”栏“x 值” 文本框内选取数据区 域“频率曲线！$C$2:$C72”, 在“Y 值” 文本框内选取数据 区域“频率曲线！$E$2:$E72” ，点击“确定”按钮后，右键单击 在图形内出现的比较圆滑的散点，在出现的对话框内点击“图表 类型” ，在“子图标类型”栏内选择“无数据点平滑线散点图” ， 按“确定”按钮后，光滑散点就变成一条光滑曲线自此一幅完 整的频率曲线图就基本完成了之后读者可根据自己的喜好，对 图形进行必要的修改后完善。
（详见图 8） 11 图 8 频率曲线图 4.3线型适线 调整“频率曲线”表格中 Cv 值(H2 单元格)、Cs 值（I2 单 元格） ，直至图内光滑曲线与散点较好配合 5为图表设置动态源数据 在 Excel 中创建图表后，有时需要不断地向源数据中添加 数据， 如果每次都用手工更改源数据的方法来更新图表显得有些 繁琐我们可以使用“表”和公式的方法来为 Excel 图表设置一个 动态源数据，当向图表源数据中添加或删除数据后，图表会自动 更新 5.1定义源数据区域名称 12 以“频率曲线”表为例，目前可用的源数据为 A1:E72，以 后不断添加，但不超出区域 A1:E150A 列中数据定义的名称为 “频率%”单击菜单“插入名称定义”，在“定义名称” 对话框中，在“在当前工作簿中的名称”下输入 “频率%”，然 后在“引用位置”下输入公式： =OFFSET($A$2,0,0,COUNTA($A$2:$A$150),1) 然后按“添加”按钮，用同样的方法将其他列定义名称并输 入公式 例如：B 例名称为“至 P=50%的水平距离”，其公式为： =OFFSET($B$2,0,0,COUNTA($B$2:$B$150),1) 完成后，按“确定”按钮。
5.2 设置动态源数据 5.2.1 直接编辑系列公式： 直接在图表中单击“计算值 XP”系列，在编辑栏中看到该系列 的公式为： =SERIES(,频率曲线!$C$2:$C$72,频率曲线!$E$2:$E$72,4) 将上述公式中$C$2:$C$72 改为“距 0.01%的水平距离”， $E$2:$E$72 改为“计算值 XP”，即 =SERIES(,频率曲线! 距 0.01%的水平距离,频率曲线! 计 算值 XP,4) 13 然后按回车键 以后在该区域下一行中添加数据，如 本例中的第 12 行，图表将自动更新 以上是用 Excel 绘制水文频率计算的全过程，跟手工计算和 专业软件计算比较起来，具有方便、规范、减少计算工程量等显 著优点 参考文献 1水利水电工程设计洪水计算规范 （SL44-93） ，中华人民共和国水利部能源 部 2高等数学 ，高等教育出版社 3工程水文学及水利计算 ，中国水利水电出版社 。
