(完整版)Excel绘制海森机率格纸

1、Excel绘制海森机率格纸注：现成的Excel文档，在这里。本文主要介绍利用Excel内置函数和图表功能绘制海森机率格纸的方法。水文频率计算(适线法)中，采用的海森机率格纸应用的是一种特殊的坐标系统一一纵坐标为等间距 数学坐标，横坐标为与频率值(下侧概率)的标准正态分布分位数有关。标准正态分布分位数在？?= 50%处为零，而海森机率格纸在 ？?= 0.01%时横坐标为零。因此，海森机率格纸横坐标值计算公式可表示为：?= ?- ?.01%其中，？?为海森机率格纸中的频率 P对应的横坐标值；？?内频率P对应的标准正态分布分位数；？?).01%为频率？?= 0.01%对应的标准正态分布分位数。标准正

2、态分布分位数可用 Excel内置函数NORM.S.INV(P)(返回标准正态累积分布函数的反函数，精度 ±3X10-7)直接计算。在Excel中绘制海森机率格纸的要点：海森机率格纸的横向网格线(即纵坐标)均匀分布，可直接由Excel的图表功能自动生成，而纵向网格线(横坐标)需要向图表中添加2或3个系列的XY散点图来完成一一实际是使用该系列的值产生矩形波形图，即利用该波形图的“上升沿”和“下降沿”作为纵向网格线，而“高电平”和“低电平”部分与图形 边框线重合来实现的。下面以某站流量频率计算为例，介绍海森机率格纸在Excel中的绘制方法。、数据准备(1) .新建Excel工作簿一将工作表

3、【Sheet1】重命名为【流量机率格纸数据点】一按以下格式布局表 格：(1)合并A1:N1区域，并输入标题：“海森机率格纸参数配置”；(2) 合并A2:B2区域，并输入栏目：“纵坐标范围”；(3) C2 中输入栏目：“最小值”，D2中输入参数：0;(4) F2中输入栏目：“最大值”，G2中输入参数：1000 ;(5)合并A3:D3区域，并输入主列标题：“机率格纸网格线”；(6) 在A4、B4、C4、D4中分别输入子列标题：“频率”、“至P=50%处水平距离”、“X坐标”、“丫坐标”；(7) 按照(5)、(6)的操作在设置F3:K4的格式及参数，如图1所示。ABCDE FGH1K1海森机率格纸参

4、数配置2纵坐标范围最小值0最大值10003机率格纸网格线机率格纸频率、重现期刻度4频率至P二5吃处 水平距离X坐标丫坐标频率至P=5兆处 水平距离X坐标Y坐标1Y坐标2重现期50.01-3.7190 0000 0.01-3.7190.000010001000060 01-3.7190.0001000f 0.05-3.2910.42801000200070 02-3.5400.1791000 1T o.i-3.0900.62901000100080.02-3.5400.1790 102-2.8780.SA10100050090.03-3.43202670 105-2.5761.143010002

5、00100 03-3.4320.2B710001-2.3261.39301000100图1注：应根据研究对象选择恰当的纵坐标最大值。(8) 算LP (如图1所示)海森机率格纸中频率P对应的横坐标值LP的计算及填写步骤如下：(1) 在【流量机率格纸数据点】的 A5、A6中分别输入0.01 - A7、A8中分别输入0.02 一依此类推，在A列后续单元格中输入海森机率格纸的纵向网格线对应的频率值，直至在A233、A234中分别输入99.99 ;(2)在【流量机率格纸数据点】的 B5中输入“ =NORM.S.INV(A5%) ” 一选中B5 ,使用填充柄向下填 充至B234 ,则得到频率P对应的标准正

6、态分布分位数；(3)在【流量机率格纸数据点】的C5中输入“ =B5-$B$5 ” 一选中C5,使用填充柄向下填至 C234 ,则得到海森机率格纸中频率P对应的横坐标值LP (即网格线的横坐标值)；(4) 在【流量机率格纸数据点】的 D5:D8中分别输入“ 二$D$2 "、“二$G$2 "、“二$G$2 "、“二$D$2 ” 一 选中D5:D8 ,使用填充柄向下填充至D234 ,则得到海森机率格纸网格线的纵坐标值。3.刻度计算按以下步骤进行海森机率格纸频率刻度的计算：(1)在【流量机率格纸数据点】的肝$5:$F$36 中分别输入0.01 , 0.05 ,一依此类推，

7、在 F列后续单元格中输入海森机率格纸频率刻度对应的频率值，直至在 F36单元格中输入99.99 ;(2)根据Lp的计算方法计算$G$5:$H$36中各单元格的值(即计算 X坐标值)；(3) 在$I$5:$I$36各单元格中输入0 (即Y1坐标，以使曲线与 X轴重叠)；(4)在$J$5:$J$36各单元格中输入1000 (即Y2坐标，以使曲线与边框线重叠)；(5) $K$5:$K$36各单元格中暂不填写值，后续由程序按以下公式计算：重现期标签值由以下公式计算：1 一 ，P (P < 50%)N = P1(2)市(P > 50%)其中，N为重现期，P为【流量机率格纸数据点】的 $K$5

8、:$K$36中各单元格的值(频率)。、绘制网格线在工作簿(不是工作表)中插入图表并重命名为【流量频率曲线】。1 .图表样式设置(1) 坐标轴：仅显示【主要纵坐标轴】一【更多轴选项】:【线条】：颜色一一深灰色，宽度一一 0.5磅；【坐标轴选项】：最小彳1 0：最大彳1 1000 ：单位一一(主要100 ,次要20);【数字】：小数位数一一0;(2)轴标题：显示【主要横坐标轴】和【主要纵坐标轴】，标题分别修改为：“频率P(%) ”、“流量(单位)”(根据实际情况命名)；(3)图表标题：选择【图表上方】，并重命名为“海森机率格纸”(根据实际情况命名)；(4)网格线：选择【主轴主要水平网格线】一【更多

9、网格线选项】线条】 ：颜色一一深灰色，宽度一一0.5磅；(5)图例：选择【无】。(5) 在图表区右键一【设置图表区域格式】颜色：黑色；线宽：0.5磅；(6)在绘图区(即图形区，选择时边界出现圆形控制点)右键一【设置绘制区格式】一边框：无线条绘图区格式：边框一一黑色，选择合适的线型样式、粗细，区域【无】；(2)纵坐标轴格式：刻度最大值一一1800 ：刻度最小值一一0：主要刻度单位一一100 ,次要刻度单位一一20 ：字号一一6 (选择纵坐标轴刻度标签后设置字号)。注：应根据研究对象选择恰当的坐标轴格式设置值；(3)横向次要网格线格式：选择合适的线型样式、颜色与粗细；(4)横坐标轴格式：坐标轴与刻

10、度线的标签一一无，刻度最大值一一7.438 ：刻度最小值一一0。在2.设置频率刻度【添加】一【编辑数据(1)在图表区右键一【选择数据】一【选择数据源】对话框一图例项(系列) 系列】对话框：系列名称：="频率刻度"X轴系列值：=流量机率格纸数据点！$H$5:$H$36Y轴系列值：=流量机率格纸数据点！$I$5:$I$36一【确定】一【选择数据源】对话框一【确定】(2) 在刚才添加的曲线(即与 X轴重叠的曲线，对应于 "频率刻度”的线条，选择时会出现许多带 4个 圆角点的方框一一即数据点)上右键一【设置数据系列格式】一【线条】：【实线】；颜色：深灰色；宽度：0.5磅；

11、(3) 选择数据标签(即“频率刻度”的数据点)右键一【设置数据标签格式】：【标签包括】：X值；【标签位置】：靠下；【对齐方式】：垂直对齐方式一一中部剧中，自定义角度一一-45度(以避免标签一一刻度重叠)；一【开始】选项卡一字号：6;(4) 置重现期刻度(1) 在图表区右键一【选择数据】一【选择数据源】对话框一图例项(系列)【添加】一【编辑数据系列】对话框：系列名称：="重现期刻度"X轴系列值：=流量机率格纸数据点！$H$5:$H$36Y轴系列值：=流量机率格纸数据点！$J$5:$J$36一【确定】(2) 在刚才添加的曲线(即与绘图区上边框线重叠的曲线，对应于 "重

12、现期刻度”的线条)上右键一【设置数据系列格式】一【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3) 选择数据标签(即“频率刻度”的数据点)右键一【设置数据标签格式】：【标签包括】：X值；【标签位置】：靠上；【对齐方式】：垂直对齐方式一一中部剧中，自定义角度一一-45度；一【开始】选项卡一字号：6;4 .绘制网格线(1) 在图表区右键一【选择数据】一【选择数据源】对话框一图例项(系列)【添加】一【编辑数据系列】对话框：系列名称：="纵向网格线”X轴系列值：=流量机率格纸数据点！$C$5:$C$234Y轴系列值：=流量机率格纸数据点！$D$5:$D$234一【确定】(2) 在刚才添

13、加的曲线(对应于 "纵向网格线”的线条)上右键一【设置数据系列格式】一【线条】【实线】；颜色：深灰色；宽度：0.5磅；(3)【设计】选项卡一【数据标签】：无；5 .修改刻度值打开VBE环境(快捷键【Alt】+【F11，或【开发工具】选项卡一【 Visual Basic ,若没有启用【开发工具】 选项卡，则需要打开Excel【选项】一【自定义功能区】一【主选项卡】一勾选【开发工具】即可)，添加如下代码：Private Sub Chart_Activate()Dim i%, k# 一For i = 5 To 36k = Sheet1.Cells(i, "F")If i

14、 < 21 Then k = 100 / k Else k = 1 / (1 - k / 100)'1/P%If k - Int(k) > 0 ThenSheet1.Cells(i, "K") = Format(k, "0.#0") ElseSheet1.Cells(i, "K") = Format(k, "0") End If NextFor i = 1 To SeriesCollection(2).DataLabels.CountSeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange.Characters.TextSheet1.Cells(i + 4, "F")SeriesCollection(2).DataLabels(i).Format.TextFrame2.TextRange.Characters.TextSheet1.Cells(i