Excel用于水文频率计算
Excel在水文频率计算中的应用
Excel在水文频率计算中的应用
李智;衣起超;李艳杰;张德秋
【期刊名称】《水利与建筑工程学报》
【年(卷),期】2007(005)002
【摘要】针对现有频率计算软件中P-Ⅲ型频率曲线离均系数计算复杂和正态机率格纸绘制困难,本次充分利用Excel软件内置函数和强大图表功能完成了频率计算软件的制作,同时软件对正偏P-Ⅲ型频率曲线离均系数处理得以在负偏频率曲线中应用.软件操作简便,应用广泛,计算准确,制图美观,输出方便.
【总页数】3页(P95-97)
【作者】李智;衣起超;李艳杰;张德秋
【作者单位】黑龙江农垦勘测设计研究院,黑龙江,佳木斯,154002;黑龙江农垦勘测设计研究院,黑龙江,佳木斯,154002;黑龙江农垦勘测设计研究院,黑龙江,佳木斯,154002;黑龙江省佳木斯市市政设施管理处,黑龙江,佳木斯,154002
【正文语种】中文
【中图分类】P333.9
【相关文献】
1.Excel在水文频率计算中的应用 [J], 王春霞
2.VB与Excel在水文频率计算中的应用 [J], 张堃一
3.Excel在水文频率计算中的应用 [J], 吴明官;任中海;周庆欣;吴跃红
4.Visual Basic及Excel在水文频率计算中的应用研究 [J], 王超;伍立群;郭有安
5.基于VB和Excel的水文频率计算软件的开发及应用 [J], 秦国民
因版权原因，仅展示原文概要，查看原文内容请购买。
运用EXCEL图表进行水文频率计算
应用Excel绘制水文频率曲线杨柏桢王志红（安图县水利局133600）【摘要】本文对运用EXCEL表格绘制水文频率曲线，既简单又实用，完全可以将它直接应用到工程水文计算和频率曲线绘制上。
【关键词】Excel 函数工程水文频率曲线Microsoft Excel （简称Excel）是Microsoft Office中的一个重要组件，具有强大的计算功能，为工程水文及水利计算中的有关计算提供广阔的平台。
利用Excel可以有效提高工程水文及水利计算的速度和精度，具有快速、简便、灵活的特点。
特别是使用Excel的“拖动充填”、“排序”、“插入函数ƒx”、“图表”等功能，充分体现其优越性，可以充分解决工程水文及水力计算的有关问题。
本文可以通过其内置功能介绍如何比较轻松准确的绘制水文频率曲线。
其具体绘制步骤如下：1.制作海森机率格纸主要运用EXCEL电子表格中的NORMSINV函数进行计算。
（详见图1）在表中B2单元格中输入公式：=NORMSINV(A2%) （1.1）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
在表中C6单元格中输入公式：=-$B$4-(-B6) （1.2）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
在表中D2单元格中输入最小值，如“0”；在D3单元格中输入最大值，如“1600”；在D4单元格输入“=D2”；在D5单元格输入“=D3”。
拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
在表中E2单元格中输入公式：=1/A2*100 （1.3）拖动单元格右下角的填充柄，将公式复制至本列E25单元格止。
在表中E26单元格中输入公式：=1/(1-A26/100) （5）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
图1 海森机率格纸2.绘制海森机率格纸在图1表格中点击“插入”菜单中的“图表”命令，在“标准类型”的“图表类型”表框中选择“XY散点图”，在“子图表类型”表框中选择“散点图”。
Excel基础计算功能在水文报表的运用
Excel基础计算功能在水文报表的运用为解决水文作业中的繁琐的计算，本文结合实践，运用Excel的计算功能，解决水位差计算。
标签：函数；语句；四舍六入；表格链接1、Excel擁有强大的计算与宏命令功能在水文行业，数据的计算整理是人工计算耗时大的项目。
运用Excel计算功能可解决重复性的计算，简单的判断语句即可解决四舍六入的难点。
2、原理及方法Excel有强大的数据自动处理和计算功能，数据源结合链接功能，在利用其的计算和编程功能进行计算操作。
3、运用Excel的链接功能解决数据源的转换3.1 在“数据录入”中通过链接已有表格或手工录入某时期的数据。
3.2 日期的省略编程：O4=IF（B4=B3，” “，B4）。
3.3 时间的计算和字符的提取：Q3=IF（D3=””，” “，IF（D3>0，IF（V3>=59.5，D3+1，D3），” “））判断出小时数；R3= （G3-D3）*60+（I3-F3）计算出总分钟数；再除2加上开始分钟数减60秒得出平均分钟数U3=IF（T3>=60，T3-60，T3）。
运用函数V3=round（u3，0）进行其的四舍五入；最右（末）位数提取X3=right（v3，1）。
（图1）3.4 分钟的四舍六入：①运用or函数进行位数奇偶的判断，若返回值为奇数显示值为TURE，Y3=OR（X3=RIGHT（1，1），X3=RIGHT（3，1），X3=RIGHT （5，1），X3=RIGHT（7，1），X3=RIGHT（9，1））；②对U3是否有进行四舍五入进行判断表达式如下：Z3=IF（U3=V3-0.5，TRUE）；③对X3与Y3进行真假判断：AA3=AND（Y3=TRUE，Z3=TRUE）；④秒的判断AB3=IF（T3=59.5，0，IF（D3=““，”“，IF（D3>0，IF（Z3=TRUE，IF（AA3=TRUE，W3，V3），U3），” “））），59.5秒为0秒，如果为四舍五入且末尾为奇数，则用四舍五入的数值，否则用减0.5的值U3；⑤高砂水位的四舍六入：按时间内差完保留两位小数F2=ROUND（E2，2），读取小数点倒第二位G2=LEFT（RIGHT（E2，2），1），用四舍五入值减0.005再与内差值相比，如相等判断G2数值的奇偶并进行判断计算。
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 。
谈如何巧用Excel提高水文水利分析计算效率
谈如何巧用Excel提高水文水利分析计算效率摘要：在水文水利计算中，数据处理的工作量往往很多，传统的数据人工处理方法效率慢，而且容易出现计算错误。
excel作为一种常用的办公软件，有强大的数据运算、分析和统计能力，将excel引入到水文水利计算中能有效提高水利水文计算效率，提高计算准确性。
本文通过excel中的公式、函数与vba编程等功能，对如何巧用excel来提高水文水利计算效率这一课题进行分析。
关键词：excel 水文水利分析计算效率一、前言在水文水利工作中，一个非常重要的部分就是处理水文水利的相关数据，将数据进行分析、统计和整合、分类是水文水利工作中的重要部分。
由于数据分析和整理工作较为繁琐，而且富有变化性，因此，建立一个可以同时处理所有相关数据的程序是不现实的，而若是每一个工作都单独编写程序又会浪费太多时间，降低工作的效率，提高工作的复杂性。
因此，就需要一个易于操作又功能强大的软件对水文水利的相关数据进行处理，excel就能够很好地满足水文水利日常数据处理的要求。
将excel软件与水利工作结合在一起，不仅是水利工作数字化、信息化建设的要求，也是科技发展的必然趋势。
如今，excel软件已经成为水文水利中重要的日常工作软件，通过科学、合理地使用excel可以提高工作效率和计算结果的准确度，工作人员通过掌握excel中的公式运用和vba编程，能够快速而又针对性的解决绝大部分水文水利数据分析计算问题。
二、excel的相关数据处理在水文水利工作中，经常要处理大量原始数据，这也是水文水利数据分析计算工作的前期必要准备工作。
通过excel软件，能够实现这些数据的分类筛选、排列组合等基本操作处理。
在这些基础操作的帮助下，工作人员能够迅速查找到需要的数据资料进行下一步的分析整合。
除了基本的数据筛选、排序和分列功能，excel还可以帮助工作人员对缺失的数据进行补充或插补。
在水文水利工作中，常常需要准备长期的水文数据作基础，这其中的时间跨度可能会达到数十年。
基于EXCEL的水文频率计算软件开发
基于EXCEL的水文频率计算软件开发水文频率计算是水文学中的重要内容，可以帮助工程师和研究人员了解水文事件的规律性，并为水资源规划和管理提供可靠的参数。
在过去，水文频率计算通常是使用手动计算方法进行的，这种方法耗时且容易出错。
随着计算机技术的发展，开发基于EXCEL的水文频率计算软件可以提高计算的精确性和效率。
1.界面设计：软件的界面应该直观、简洁，并提供用户友好的导航和操作方式。
可以采用EXCEL的表格和图表功能来展示数据和计算结果。
用户可以通过输入数据、选择计算方法等方式与软件进行交互。
2.数据输入：软件应该支持用户方便地输入水文数据，包括降雨量、径流量等。
可以采用表格模式，用户可以手动输入数据，也可以从外部文件导入数据。
3.数据处理：软件需要对输入数据进行预处理，包括数据清洗、缺失值处理、数据转换等。
可以利用EXCEL的数据处理功能进行这些操作，如筛选、填充、函数计算等。
4.频率计算：软件应该支持常见的水文频率计算方法，如经验公式法、理论分布法、参数估计法等。
可以利用EXCEL的统计函数和公式进行计算，或者集成一些常用的水文计算工具。
5.结果展示：软件应该能够将计算结果直观地展示给用户，包括频率曲线、设计洪水位、洪水容积等。
可以利用EXCEL的图表功能和数据处理功能来生成图表和表格，突出计算结果中的关键信息。
6.结果输出和存储：软件应该支持将计算结果导出为EXCEL文件或其他常见格式，方便用户进一步分析和使用。
同时，软件需要提供保存计算结果的功能，以便用户在后续操作中访问和使用这些结果。
7.错误处理和提示：软件应该能够有效地处理用户输入错误或计算错误，并给出相应的提示和建议。
例如，如果用户输入的数据有缺失或错误，软件可以提示用户进行修正或补充；如果计算过程中出现错误，软件可以提醒用户检查输入数据或选择其他计算方法。
8.软件优化：为了提高软件的运行效率和用户体验，可以对软件进行优化。
例如，可以设计适当的算法和数据结构，避免不必要的计算和重复操作；合理使用EXCEL的函数和公式，避免复杂和低效的计算过程。
Excel在水文频率计算中的应用
论曲线为皮尔逊 ! 型曲线， 简称 7’! 型曲线。 （ !） 7’! 型分布。概率密度函数为：
# (+)-" " +)’%#-#’!* ’" +)’% $+%-
# 的分布函数：
#
8
"（ # !)"-" (+)-+)
)"
&
8
$+%-" , * +, + 伽玛函数）
#’! ’,
#
&
%" /* $ "" * $##")+!’*-. 0 -/’ -9 ) -.-/ ’ 式中： 为均值； ) -. 为变差系数； -/ 为偏态系数。
图形处理的人也越来越多， 深受用户欢迎， 而且操作简单 因此， 大力推 易学， 所以 &’()* 的操作命令也很容易掌握， 广应用 &’()* 水文频率计算软件具有广阔的前景。 为了防止误操作而导致成果出现错误，故 &’()* 工 作表 和 工 作 簿 均 设 置 密 码 进 行 了 保 护 ， 因此， 应用该软 件安全可靠。
办公室， 还是在家庭中， 12345 的使用越来越广泛。 12345 是美国微软公司推出的 677834 办公组件之一，是一个电 子表格处理软件。使用 12345 作表格处理，不仅方便快 捷、 效率高， 而且还可使报表图文并茂， 直观大方。 虽然 12345 具有很强的表格处理及常规图形处理能 力， 但是仍不能直接应用于水文频率计算。因为水文频 率计算 中 采 用 的 海 森 机 率 格 纸 （ 正态分布） 是特殊的坐 标系统，故利用 12345 现有功能的简单组合无法解决水 文频率计算中的复杂运算与转换、 绘图等问题。 因此，本次我们紧密结合水文频率计算的实际， 巧 妙地应用 12345 表格及图形处理功能， 首次开发了 12345 水文频率计算软件。 该软件既发扬了过去水文频率计算 软件的 优 点 ， 又克服了现有软件存在的不足， 实现了水 文频率计算软件成果的标准化、 规范化目标。
运用ecel图表进行水文频率计算修订稿
运用E X C E L图表进行水文频率计算Coca-cola standardization office【ZZ5AB-ZZSYT-ZZ2C-ZZ682T-ZZT18】应用Excel绘制水文频率曲线杨柏桢王志红（安图县水利局 133600）【摘要】本文对运用EXCEL表格绘制水文频率曲线，既简单又实用，完全可以将它直接应用到工程水文计算和频率曲线绘制上。
【关键词】Excel 函数工程水文频率曲线Microsoft Excel （简称Excel）是Microsoft Office中的一个重要组件，具有强大的计算功能，为工程水文及水利计算中的有关计算提供广阔的平台。
利用Excel可以有效提高工程水文及水利计算的速度和精度，具有快速、简便、灵活的特点。
特别是使用Excel的“拖动充填”、“排序”、“插入函数x”、“图表”等功能，充分体现其优越性，可以充分解决工程水文及水力计算的有关问题。
本文可以通过其内置功能介绍如何比较轻松准确的绘制水文频率曲线。
其具体绘制步骤如下：1.制作海森机率格纸主要运用EXCEL电子表格中的NORMSINV函数进行计算。
（详见图1）在表中B2单元格中输入公式：=NORMSINV(A2%) （）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
在表中C6单元格中输入公式：=-$B$4-(-B6) （）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
在表中D2单元格中输入最小值，如“0”；在D3单元格中输入最大值，如“1600”；在D4单元格输入“=D2”；在D5单元格输入“=D3”。
拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
在表中E2单元格中输入公式：=1/A2*100 （）拖动单元格右下角的填充柄，将公式复制至本列E25单元格止。
在表中E26单元格中输入公式：=1/(1-A26/100) （5）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第二讲 Excel 用于水文频率计算
一、 随机变量的统计参数
1． 算术平均值
设某一随机变量的样本系列为x 1,x 2,…,x n ,把它们的总和除以项数n 即得算
术平均数，简称均值，计算公式如下：
∑==+++=n
i i n x n n x x x 1
3211x x 2． 均方差σ与变差系数C v
此二参数用来反映随机变量分布得离散程度。
()11K 12--∑=n C n i i
v ＝
3． 偏态系数C s
此参数衡量随机变量的分布在均值两边是否对称以及不对称（偏态）程度的参数：
()
()31331v n i i s C n K C --=∑=
日常工作中，经常要对一些样本系列进行排频并绘制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Ⅲ曲线数学上常称γ分布，其概率密度函数为
()()()()0
10a x a a
e a x a x
f ----Γ=ββ （1） 式中 Γ（α）——α的γ函数；
α，β，a0——三个参数。
这三个参数与总体的三个统计参数x 、Cv 、Cs 具有下列关系： 24s C a =；s v C C x 2=β；⎪⎪⎭
⎫ ⎝⎛-=s v C C x a 210 （2）
2）P 与xp 间的关系式为
()()()()dx e a x a x x P P p x a x a a P ⎰∞----Γ=>=010ββ （3）
3）EXCEL 中γ概率密度函数的计算公式如下：
()()βββx
a a e x a a x f --Γ=11,, （4） 通过对比，可以看出（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 列的数据量加大后，图表将自动更新。