运用EXCEL图表进行海森格纸绘制1

1、【关键词】Excel 函数 工程水文 频率曲线Microsoft Excel （简称Excel）是Microsoft Office中的一个重要组件，具有强大的计算功能，为工程水文及水利计算中的有关计算提供广阔的平台。利用Excel可以有效提高工程水文及水利计算的速度和精度，具有快速、简便、灵活的特点。特别是使用Excel的“拖动充填”、“排序”、“插入函数x”、“图表”等功能，充分体现其优越性，可以充分解决工程水文及水力计算的有关问题。本文可以通过其内置功能介绍如何比较轻松准确的绘制水文频率曲线。其具体绘制步骤如下：1.制作海森机率格纸主要运用EXCEL电子表格中的NORMSINV函数进行计算

2、。（详见图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单元格中输入公

3、式：=1/(1-A26/100) （5）拖动单元格右下角的填充柄，将公式复制到本列其他单元格中。 图1 海森机率格纸2.绘制海森机率格纸在图1表格中点击“插入”菜单中的“图表”命令，在“标准类型”的“图表类型”表框中选择“XY散点图”，在“子图表类型” 表框中选择“散点图”。单击“下一步”按钮。（详见图2） 图2单击“系列”标签，在“系列”栏下点击“添加”按钮，在“X值”文本框内选取数据区域“海森机率格纸！$C$2:$C49”， 在“Y值”文本框内选取数据区域“海森机率格纸！$D$2:$D49”。之后点击“完成”按钮。（详见图3）图3在图中将鼠标置于X轴数字“4”上单击右键，按“清除”按钮，清

4、除水平轴显示的“0 2 4 6 8”数字。将鼠标置于X轴圆点上单击右键，点击“数据系列格式”，在出现的对话框内点击“数据标志”选项卡，在“数据标签包括”栏内选择“X值”后，按下“确定”按钮。（详见图4）图4然后挨个点击在图内X轴上下出现的数字，将X轴下侧数字逐一修改为表内“频率”列相对应的数字，将X轴上侧数字逐一修改为表内“重现期”列相对应的数字。如将“0.62874179”改为“0.1”。 （详见图5）图5全部修改完后，右键单击图表空白处，在“图表选项”对话框点击“标题”选项卡，在“图表标题”文本框内输入图表标题。将鼠标置于图内X轴圆点上单击右键选择“数据系列格式”，在出现的对话框内点击“误

5、差线Y”，在“显示方式”栏选择“负偏差”，在“百分比”文本框输入100%，然后按“确定”按钮，图表内将显示X轴刻度线。在图中将鼠标置于Y轴数字“1600”上单击右键，选择“坐标轴格式”，在对话框内选择“刻度”选项卡，选择“最大值”并在文本框内输入“1600”。然后按“确定”按钮。将鼠标置于X轴圆点上单击右键，点击“数据系列格式”，点击“图案”选项卡，在“数据标记”栏选择“无”，按“确定”按钮。至此，一张完整的海森机率格纸图绘制完毕。（详见图6） 图63.系列经验频率的计算公式中华人民共和国行业标准水利水电工程设计洪水计算规范(SL44-93)中对频率曲线的线型要求一般应采用Pearson型，频

6、率曲线的统计参数采用均值，变差系数Cv，偏态系数Cs，计算连续和不连续洪水系列，Cv，Cs值。3.1 P曲线数学上常称分布，其概率密度函数为：f(x)= EXCEL中概率密度函数的计算公式如下： Xp=GAMMAINV（1-P%，）+a0 式中=4/（Cs2） =2/(CvCs) a0=(1-2Cv/Cs)3.2 系列经验频率的计算1.在n项连续洪水系列中，按大小顺序排位的第m项洪水的经验频率公式如下：Pm； 其中： m=1,2,3,.，n；2.在调查考证期N年内有特大洪水a个，其中有l个发生在n项连续洪水系列内，l可以等于0、1、a中的任一个值。不连续系列经验频率计算公式如下：a个特大洪水的

7、经验频率为：Pm = 其中： M=1,2,3,.，a；实测系列中剩余的（n-l）项的经验频率公式为：Pm=+(1-)； 式中：M特大洪水排位序号，M=1、2、a； N调查考证期，指调查或考证的最远的年份至样本资料最末一年的年数； n实测洪水项数； l实测洪水中提出作特大值处理的洪水个数； m实测一般洪水排位的序号，m=l+1, l+2, l+3、n； Pm实测洪水第项的经验频率。3.3 应用矩法进行统计参数初估3.3.1对n年连续系列，其计算公式如下：=S=Cv=3.3.2对n年不连续系列，其计算公式如下：=（+）Cv=特大洪水的洪峰流量或者洪量，j=1，2，a，n，m3/s或m3；实测一般洪

8、水的洪峰流量或洪量，i=+1，+2，n，m3/s或m3；其他符号含义同前。3.4 制作经验频率计算表格，计算出均值，变差系数Cv。（详见图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-A2%,4/POWER($I

9、$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”，点击“确定”按钮后，右

10、键单击在图形内出现的比较圆滑的散点，在出现的对话框内点击“图表类型”，在“子图标类型”栏内选择“无数据点平滑线散点图”，按“确定”按钮后，光滑散点就变成一条光滑曲线。自此一幅完整的频率曲线图就基本完成了。之后读者可根据自己的喜好，对图形进行必要的修改后完善。（详见图8） 图8 频率曲线图4.3 线型适线调整“频率曲线”表格中Cv值(H2单元格)、Cs值（I2单元格），直至图内光滑曲线与散点较好配合。5为图表设置动态源数据在Excel 中创建图表后，有时需要不断地向源数据中添加数据，如果每次都用手工更改源数据的方法来更新图表显得有些繁琐。我们可以使用“表”和公式的方法来为Excel图表设置一个动

11、态源数据，当向图表源数据中添加或删除数据后，图表会自动更新。5.1 定义源数据区域名称以“频率曲线”表为例，目前可用的源数据为A1:E72，以后不断添加，但不超出区域A1:E150。A列中数据定义的名称为“频率%”。单击菜单“插入名称定义”，在“定义名称”对话框中，在“在当前工作簿中的名称”下输入 “频率%”，然后在“引用位置”下输入公式： =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%的水平距离,频率曲线! 计算值