应用Excel软件实现海森概率格纸的绘制

邓 欢，代俊峰，方荣杰，白凯华

(桂林理工大学环境科学与工程学院，广西 桂林 541006)

从20世纪60年代中后期以来，我国水文频率分析工作中一直采用皮尔逊Ⅲ型曲线(简写P-III型)[1]。SL44—2006《水利水电工程设计洪水计算规范》中规定水文现象的概率分布模型一般应采用皮尔逊Ⅲ型分布模型[2]。P-III型分布模型的随机变量与其超过制累积频率呈非线性，水文频率曲线绘在纵横坐标都为均匀分格的常规坐标纸上呈“S”型，其两端陡峭，即上部急剧上升，下部急剧下降，中间平缓，而曲线两端正是工程设计频率所用的部位，同时这也不便于采用目估适线法判断其与经验点据拟合的好坏程度。为了方便和精确绘制曲线，以及分析计算，需要设计一种坐标纸，使频率曲线绘在上面成直线或近似直线，这种坐标纸称为概率格纸。为了解决常规坐标纸上频率曲线两端曲率大，曲线不便外延的问题，1913年美国水利工程师海森提出使用正态概率格纸，使正态分布频率曲线绘在其上能成为直线，因此正态概率格纸也被称为海森概率格纸[3]。若使P-III型分布曲线成直线，则需对每一个CS绘制一张专用概率格纸，比较繁琐，因此也采用海森概率格纸。虽然P-III型频率曲线绘在其上并不能成直线，但弯曲程度大为减缓近似直线，有利于曲线外延。

Excel软件具有简单易操作和兼容性强的特点，在水文水利计算中应用广泛，如在水能调节计算、渠均匀流水力计算和径流调节计算等方面[4- 6]。同时Excel也是绘制海森概率格纸的常用工具[7- 9]。利用Excel并在其中嵌套VisualBasic语言程序也是当前绘制水文频率分析曲线的重要手段之一[10- 12]。本文通过理解超过制概率和分位数等在海森概率格纸中的含义，正确处理其在Excel中的函数表达，主要突出设计的原理和有Excel中绘制的技巧。

1 设计原理

按照水文习惯，以随机变量值为纵坐标，与之对应的超过制累积频率为横坐标，在常规坐标纸上绘出标准正态分布曲线Q(x)，如图1A所示。

(1)

式中，x—随机变量值；Q(x)—标准化正态随机变量X的超过制概率，也称超过制累积频率，水文上简称频率，其值用P表示。

图1 海森概率格纸设计原理示意图

海森概率格纸具有以下特点：

(1)纵坐标没有变化，仍是分格均匀的常规坐标。

(2)横坐标分格关于P=50%处对称，中间较密，向两端逐渐变得稀疏，这是由于标准正态分布图形是关于P=50%处对称，中间弯曲程度小，向两端弯曲程度逐渐变大导致的。

(3)由于P=0及P=100%的标准正态变量值分别趋于+∞和-∞，在图上绘不出，同时水文频率在变量最小值和最大值处是趋向于0和100%，而不能等于0和100%，因此海森概率格纸横坐标一般是从0.01%到99.99%。

2 横坐标取值规律

海森概率格纸横坐标之间的间距可用公式(1)的反函数值或上侧分位数x来表示。由于P=50%对应的x值为0，而海森概率格纸令横坐标为P=0.01%时为0，现以P=0.01%作为横坐标原点，那么P=50%距P=0.01%的距离可表示为Q-1(0.01%)-Q-1(50%)=3.719，P=99.99%距P=0.01%的距离为Q-1(0.01%)-Q-1(99.99%)=7.438。同理，其他任意频率P的横坐标与P=0.01%的距离L为：

L=Q-1(0.01%)-Q-1(P)

(2)

对应Excel内置函数NORM.S.INV(probability)，就可表示为：

L= NORM.S.INV(1-0.01%)-NORM.S.INV(1-P)

(3)

海森概率格纸横坐标部分取值见表1中的系列③。

3 海森概率格纸的绘制方法

3.1 网格线的绘制

海森坐标格纸纵坐标是均匀分格的，则横向网格线为均匀分布，可由Excel软件的图表功能自动生成，然后根据需求设置坐标轴格式，纵坐标最小值设置为0，最大值可据水文变量的最大值确定。横坐标分格不均匀，纵向网格线就无法通过Excel软件自动生成，下面以图2为例进行纵向网格线的绘制，设置纵坐标最大值为3000，各条纵向网格线上、下两个端点的坐标列于图2中。

图2 海森概率格纸示例图

在Excel中新建sheet表，输入表1的数据，①表示纵向网格线的频率值，⑤和⑥表示纵向网格线下端点和上端点的x轴和y轴坐标值。在工具条上点击“插入”，选择“图表”中的“散点图”，生成图表区，选中图表区单击鼠标右键，从弹出的快捷菜单中点击“选择数据”选项，在弹出的对话框中选择“添加”，会弹出“编辑数据系列”对话框，用“x轴系列值”和“y轴系列值”文本框右边的“拾取器”按钮拾取所需数据，单击“确定”按钮，图表中就添加17条纵向线段即纵向网格线。

表1 海森概率格纸纵向网格线端点坐标

在添加数据时有两种方法，一种如图3A的连接方法，将每条网格线的数据当作一个系列，共有17个系列数据，通过在同一图表区“选择数据”中逐个添加各系列，绘制出17条纵向线段。通常为了美观，一般要绘制100多条纵向网格线，操作起来步骤多，且不利于后期图形的修改；另一种方法是把全部网格线的端点数据当作一个系列，在图表区“选择数据”中只需要添加一个系列，将各点顺次连接起来，如图3B的连接方法，这种方法操作和图形修改都比较便利，至于横线部分在绘横向网格线或绘图区边框时会被覆盖掉，不会影响后期添加水文频率曲线。

图3 纵向网格线连接方法

3.2 横坐标的标注

由于Excel绘图时默认坐标轴刻度是均匀等分的，无法直接实现制海森概率格纸横坐标频率刻度和重现期刻度的标注，因此要另外进行标注。在海森概率格纸横坐标上除了对频率刻度要进行标注外还要对重现期刻度进行标注，重现期和频率存在如下关系[15]：

(4)

在标注时通常不需要将所有横坐标都标注出来，而是选择性地进行标注，常见标注数据列于表2。表2中①表示的是要标注的频率值，系列③和④表示频率值对应的标注位置，②表示的是要标注的重现期值，系列③和⑤表示重现期对应的标注位置。

表2 海森概率格纸横坐标标注数据表

将表2的数据输入到Excel中，在绘制好网格线的同一图表中单击鼠标右键，点击“选择数据”选项，再选择“添加”，弹出“编辑数据系列”对话框，用“x轴系列值”和“y轴系列值”的“拾取器”按钮拾取系列③和④的数据，单击“确定”按钮，图中纵坐标为0处就添加了一条直线，用相同的方法拾取系列③和⑤的数据，以图中纵坐标为3000处再添加一条直线。选中其中一条直线，单击鼠标右键，点击“添加数据标签”，软件会给这条线上每个数据点添加数据标签，选中数据标签，再单击鼠标右键，选择“设置数据标签格式”，在弹出的对话框中根据需求修改“标签选项”，在“标签位置”选项中设置代表频率的标签靠下，代表重现期的标签靠上。请注意在“标签包括”选项中无论选择“x值”还是“y值”，发现所有的标签显示的是④和⑤的值，并不是①和②对应的值，因此要对显示出的数据标签进行修改。可以在某个数据标签上双击鼠标选中这个数据标签，手工输入对应的显示值或在编辑栏中输入“=‘拾取某单元格的值’”，“拾取某单元格的值”是指在工作表中单击鼠标拾取要显示的数据单元格，就可对各个数据标签进行逐一修改。Excel2013以上版本还可以对全部标签数据进行批量修改，操作步骤为：单击选中频率或重现期的全部标签数据，再单击鼠标右键，从弹出的快捷菜单中选择“设置数据标签格式”选项，打开“标签选项”对话框，在“标签包括”中勾选“单元格中的值”，弹出“数据标签区域”，通过“选择数据标签区域”文本框右边的“拾取器”按钮，在工作表中选中①列或②列的全部数据区域，单击“确定”按钮，所有数据标签就都进行了修改，以此方法能快捷完成频率和重现期的标注。

4 结语

标准正态分布在常规坐标纸上呈“S”型曲线，通过纵坐标不变，过曲线两端点连成直线，再将曲线上各点平移至该直线上，定出各点的横坐标位置，就形成了纵坐标均匀分布，横坐标关于P=50%处对称，越向两侧间距越大的海森概率格纸，P-III型频率曲线绘制在海森概率格纸上有利于曲线外延。用超过制概率和上侧分位数的概念刻画了海森概率格纸横坐标的位置，并利用Excel软件中的NORM.S.INV内置函数定量给出了横坐标位置的表达式，同时利用其数据计算和图表绘制功能强大的特点，解决了纵网格线绘制和横坐标标注难题。Excel软件操作方法简单易懂、应用性广泛，通过图表编辑可以方便快捷地输出理想图纸，易于进行水文频率曲线绘制和分析的教学活动以及工程实践应用。