Excel绘制海森机率格纸.doc

Excel绘制海森机率格纸注：现成的Excel文档，在这里。本文主要介绍利用Excel内置函数和图表功能绘制海森机率格纸的方法。水文频率计算（适线法）中，采用的海森机率格纸应用的是一种特殊的坐标系统纵坐标为等间距数学坐标，横坐标为与频率值（下侧概率）的标准正态分布分位数有关。标准正态分布分位数在P=50%处为零，而海森机率格纸在P=0.01%时横坐标为零。因此，海森机率格纸横坐标值计算公式可表示为：LP=UP-U0.01%(1)其中，LP为海森机率格纸中的频率P对应的横坐标值；UP为频率P对应的标准正态分布分位数；U0.01%为频率P=0.01%对应的标准正态分布分位数。标准正态分布分位数可用Excel内置函数NORM.S.INV(P)（返回标准正态累积分布函数的反函数，精度310-7）直接计算。在Excel中绘制海森机率格纸的要点：海森机率格纸的横向网格线（即纵坐标）均匀分布，可直接由Excel的图表功能自动生成，而纵向网格线（横坐标）需要向图表中添加2或3个系列的XY散点图来完成实际是使用该系列的值产生矩形波形图，即利用该波形图的“上升沿”和“下降沿”作为纵向网格线，而“高电平”和“低电平”部分与图形边框线重合来实现的。下面以某站流量频率计算为例，介绍海森机率格纸在Excel中的绘制方法。一、数据准备1新建Excel工作簿将工作表【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坐标”、“Y坐标”；(7) 按照(5)、(6)的操作在设置F3:K4的格式及参数，如图1所示。图1注：应根据研究对象选择恰当的纵坐标最大值。2计算LP（如图1所示）海森机率格纸中频率P对应的横坐标值LP的计算及填写步骤如下：(1) 在【流量机率格纸数据点】的A5、A6中分别输入0.01A7、A8中分别输入0.02依此类推，在A列后续单元格中输入海森机率格纸的纵向网格线对应的频率值，直至在A233、A234中分别输入99.99；(2) 在【流量机率格纸数据点】的B5中输入“=NORM.S.INV(A5%)”选中B5，使用填充柄向下填充至B234，则得到频率P对应的标准正态分布分位数；(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) 在【流量机率格纸数据点】的$F$5:$F$36中分别输入0.01，0.05，依此类推，在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各单元格中暂不填写值，后续由程序按以下公式计算：重现期标签值由以下公式计算：N=&1P (P50%)&11-P (P50%)(2)其中，N为重现期，P为【流量机率格纸数据点】的$K$5:$K$36中各单元格的值（频率）。二、绘制网格线在工作簿（不是工作表）中插入图表并重命名为【流量频率曲线】。1图表样式设置(1) 坐标轴：仅显示【主要纵坐标轴】【更多轴选项】：【线条】：颜色深灰色，宽度0.5磅；【坐标轴选项】：最小值0，最大值1000，单位（主要100，次要20）；【数字】：小数位数0；(2) 轴标题：显示【主要横坐标轴】和【主要纵坐标轴】，标题分别修改为：“频率P(%)”、“流量(单位)”（根据实际情况命名）；(3) 图表标题：选择【图表上方】，并重命名为“海森机率格纸”（根据实际情况命名）；(4) 网格线：选择【主轴主要水平网格线】【更多网格线选项】【线条】：颜色深灰色，宽度0.5磅；(5) 图例：选择【无】。(5) 在图表区右键【设置图表区域格式】颜色：黑色；线宽：0.5磅；(6) 在绘图区（即图形区，选择时边界出现圆形控制点）右键【设置绘制区格式】边框：无线条绘图区格式：边框黑色，选择合适的线型样式、粗细，区域【无】；(2) 纵坐标轴格式：刻度最大值1800，刻度最小值0，主要刻度单位100，次要刻度单位20，字号6（选择纵坐标轴刻度标签后设置字号）。注：应根据研究对象选择恰当的坐标轴格式设置值；(3) 横向次要网格线格式：选择合适的线型样式、颜色与粗细；(4) 横坐标轴格式：坐标轴与刻度线的标签无，刻度最大值7.438，刻度最小值0。在2设置频率刻度(1) 在图表区右键【选择数据】【选择数据源】对话框图例项（系列）【添加】【编辑数据系列】对话框：系列名称：=频率刻度X轴系列值：=流量机率格纸数据点!$H$5:$H$36Y轴系列值：=流量机率格纸数据点!$I$5:$I$36【确定】【选择数据源】对话框【确定】(2) 在刚才添加的曲线（即与X轴重叠的曲线，对应于频率刻度的线条，选择时会出现许多带4个圆角点的方框即数据点）上右键【设置数据系列格式】【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3) 选择数据标签（即“频率刻度”的数据点）右键【设置数据标签格式】：【标签包括】：X值；【标签位置】：靠下；【对齐方式】：垂直对齐方式中部剧中，自定义角度-45度（以避免标签刻度重叠）；【开始】选项卡字号：6；3设置重现期刻度(1) 在图表区右键【选择数据】【选择数据源】对话框图例项（系列）【添加】【编辑数据系列】对话框：系列名称：=重现期刻度X轴系列值：=流量机率格纸数据点!$H$5:$H$36Y轴系列值：=流量机率格纸数据点!$J$5:$J$36【确定】(2) 在刚才添加的曲线（即与绘图区上边框线重叠的曲线，对应于重现期刻度的线条）上右键【设置数据系列格式】【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3) 选择数据标签（即“频率刻度”的数据点）右键【设置数据标签格式】：【标签包括】：X值；【标签位置】：靠上；【对齐方式】：垂直对齐方式中部剧中，自定义角度-45度；【开始】选项卡字号：6；4绘制网格线(1) 在图表区右键【选择数据】【选择数据源】对话框图例项（系列）【添加】【编辑数据系列】对话框：系列名称：=纵向网格线X轴系列值：=流量机率格纸数据点!$C$5:$C$234Y轴系列值：=流量机率格纸数据点!$D$5:$D$234【确定】(2) 在刚才添加的曲线（对应于纵向网格线的线条）上右键【设置数据系列格式】【线条】：【实线】；颜色：深灰色；宽度：0.5磅；(3) 【设计】选项卡【数据标签】：无；5修改刻度值打开VBE环境（快捷键【Alt】+【F11】，或【开发工具】选项卡【Visual Basic】，若没有启用【开发工具】选项卡，则需要打开Excel【选项】【自定义功能区】【主选项卡】勾选【开发工具】即可），添加如下代码：Private Sub Chart_Activate() Dim i%, k# For i = 5 To 36 k = Sheet1.Cells(i, F) If i 0 Then Sheet1.Cells(i, K) = Format(k, 0.#0) Else Sheet1.Cells(i, K) = Format(k, 0) End If Next For i = 1 To SeriesCollection(2).DataLabels.Count SeriesCollection(1).DataLabels(i).Format.TextFrame2.TextRange.Characters.Text = Sheet1.Cells(i + 4, F) SeriesCollection(2).DataLabels(i).Format.Tex