运用EXCEL图表进行海森格纸绘制.docx
《运用EXCEL图表进行海森格纸绘制.docx》由会员分享，可在线阅读，更多相关《运用EXCEL图表进行海森格纸绘制.docx（11页珍藏版）》请在冰点文库上搜索。
运用EXCEL图表进行海森格纸绘制
【关键词】Excel函数工程水文频率曲线
MicrosoftExcel（简称Excel）是MicrosoftOffice屮的一个重要组件，具有强大的计算功能，为工程水文及水利计算中的有关计算提供广阔的平台。
利用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单元格屮输入公式：
=l/A2*100（1.3）
拖动单元格右下角的填充柄，将公式复制至本列E25单元格止。
在表屮E26单元格屮输入公式：
=l/（l-A26/100）（5）
拖动单元格右下角的填充柄，将公式复制到本列其他单元格
中。
图1海森机率格纸
2.绘制海森机率格纸
在图1表格中点击“插入”菜单中的“图表”命令，在“标准类型”的“图表类型”表框屮选择“XY散点图”，在“子图表类型”表框屮选择“散点图”。
单击“下一步”按钮。
（详见图2）
散点踐。
比较成对的数值
按下不放可查看示洌®
取消|＜上一步（。
||下一步完成⑧
图2
单击“系列”标签，在“系列”栏下点击“添加”按钮，在
“X值”文本框内选取数据区域“海森机率格纸！
$C$2:
$C49”,在“Y值”文本框内选取数据区域“海森机率格纸！
$D$2:
$D49”。
之后点击“完成”按钮。
（详见图3）
图3
在图屮将鼠标置于X轴数字“4”上单击右键，按“清除”按钮，清除水平轴显示的“02468”数字。
将鼠标置于X轴圆点上单击右键，点击“数据系列格式”，在出现的对话框内点击“数据标志”选项卡，在“数据标签包括”栏内选择“X值”后，按下“确定”按钮。
（详见图4）
因案丨坐标轴丨误差线x|误差线Y数据标志丨系列次序丨选项丨
禺据标签包括厂茶列名称（S）rrwi
厂Y值⑦
0佰分比（P）
r气泡尺寸ob）
分隔符⑧：
|二|
厂圉例项标示①）
数齬系列格式
2d
确定|取^I
然后挨个点击在图内X轴上下出现的数字，将X轴下侧数字逐一修改为表内“频率”列相对应的数字，将X轴上侧数字逐一修改为表内“重现期”列相对应的数字。
如将“0.62874179”改为“0.1”。
（详见图5）
图5
全部修改完后，右键单击图表空白处，在“图表选项”对话框点击“标题”选项卡，在“图表标题”文本框内输入图表标题。
将鼠标置于图内X轴圆点上单击右键选择“数据系列格式”,在出现的对话框内点击“误差线Y”，在“显示方式”栏选择“负偏差”，在“百分比”文本框输入100%,然后按“确定”按钮,图表内将显示X轴刻度线。
在图屮将鼠标置于Y轴数字“1600”上单击右键，选择“坐标轴格式”，在对话框内选择“刻度”选项卡，选择“最大值”并在文本框内输入“1600”。
然后按“确定”按钮。
将鼠标置于X轴圆点上单击右键，点击“数据系列格式”，点击“图案”选项卡，在“数据标记”栏选择“无”，按“确定”按钮。
至此，一张完整的海森机率格纸图绘制完毕。
（详见图6）
图6
3.系列经验频率的计算公式
屮华人民共和国行业标准《水利水电工程设计洪水计算规范》（SL44-93）屮对频率曲线的线型要求一般应采用Pearson-III型，频率曲线的统计参数采用均值讥变差系数Cv,偏态系数Cs,计算连续和不连续洪水系列戸，Cv,Cs值。
3」PIII曲线数学上常称Y分布，其概率密度函数为：
P（v\（a-l）q-0〈x-a°）
f（x）=F7^（x_a（,）e
EXCEL中Y概率密度函数的计算公式如下：
丄
Xp=GAMMAINV（1-P%,a,0）+ao
式中a=4/（Cs2）
B=2/（乂CvCs）
a（）=X（l-2Cv/Cs）
3.2系列经验频率的计算
1.在n项连续洪水系列中，按大小顺序排位的第m项洪水的经验频率公式如下：
m
Pm=〃+1；其中：
m=l,2,3,...,n；
2.在调查考证期N年内有特大洪水a个，其中有1个发生在n项连续洪水系列内，1可以等于0、1、・・・a中的任一个值。
不连续系列经验频率计算公式如下：
®a个特大洪水的经验频率为：
Pm=N+l其中：
M=l,2,3,...,a；
②实测系列屮剩余的（n・l）项的经验频率公式为：
aam一I
Pm=N+l+（l-N+l）〃-/+l；
式中：
M——特大洪水排位序号，M=K2、…、a；
N——调查考证期，指调查或考证的最远的年份
至样本资料最末一年的年数;
1+3、…、n；
D——实测洪水项数；
1一一实测洪水屮提出作特大值处理的洪水个数；
m——实测一般洪水排位的序号，m=l+l,1+2,
Pm——实测洪水第m项的经验频率。
3.3应用矩法进行统计参数初估
3.3.1对n年连续系列，其计算公式如下:
X=ni=l
fa-尊
s=
£Jga'~1）2
Cv=z=»n-1
3.3.2对n年不连续系列，其计算公式如下:
胡（卸+倚热）
Q——特大洪水的洪峰流量或者洪量，j=L2,……，a,n,m*s或
/实测一般洪水的洪蜂流量或洪虽:
，i=/+l,，+2,m¥s或m3
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）
FT口E3
曰・bu”0fiExctl-运用丈rtit
色丈滋劇Wfil）腹I必»>0）格式①）工口（D中口⑴芹功QP
].A：
.1>；9":
./,」…、•丨战>・；.'•“卜，
▼20
h累挣
«Zl：
ll
。
开馆|0^1*"丄创晤瞬目沁拥|回运和TEg弼浙j*|任而图8频率曲线图
4.3线型适线
调整“频率曲线”表格屮Cv值（H2单元格）、Cs值（12单元格），直至图内光滑曲线与散点较好配合。
5.为图表设置动态源数据
在Excel中创建图表后，有时需要不断地向源数据屮添加
数据，如果每次都用手工更改源数据的方法来更新图表显得有些
繁琐。
我们可以使用“表''和公式的方法来为Excel图表设置一个
动态源数据，当向图表源数据屮添加或删除数据后，图表会自动
更新。
5.1定义源数据区域名称
以“频率曲线”表为例，目前可用的源数据为A1:
E72,以后不断添加，但不超出区域Al:
E150oA列屮数据定义的名称为“频率％”o单击菜单“插入一名称一定义”，在“定义名称”对话框中，在“在当前工作簿中的名称”下输入“频率％”，然后在“引用位置”下输入公式：
二0FFSET（$A$2,0,0,COUNTA（$A$2:
$A$150）,1）
然后按“添加”按钮，用同样的方法将其他列定义名称并输入公式。
例如：
B例名称为“至P二50%的水平距离”，其公式为：
二0FFSET（$B$2,0,0,C0UNTA（$B$2:
$B$150）,1）
完成后，按“确定”按钮。
5.2设置动态源数据。
521直接编辑系列公式：
直接在图表中单击“计算值XP"系列，在编辑栏屮看到该系列的公式为：
=SERIES（,频率曲线!
$C$2:
$C$72,频率曲线!
$E$2:
$E$72,4）将上述公式中$C$2:
$C$72改为“距0.01%的水平距离”，
$E$2:
$E$72改为“计算值XP3即
=SERIES（,频率曲线！
距0.01%的水平距离濒率曲线！
计算
值XP,4）
然后按回车键。
以后在该区域下一行中添加数据，如
本例屮的第12行，图表将自动更新。
以上是用Excel绘制水文频率计算的全过程，跟手工计算和专业软件计算比较起来，具有方便、规范、减少计算工程量等显著优点。
参考文献
[1]《水利水电工程设计洪水讣算规范》（SL44-93）,中华人民共和国水利部能源部
[2]《高等数学》，高等教育出版社
[3]《工程水文学及水利计算》，中国水利水电出版社
升级会员 