利用EXCEL电子表格开发洪水预报系统

□周军亭（平顶山水文水资源勘测局）

利用EXCEL电子表格开发洪水预报系统

□周军亭（平顶山水文水资源勘测局）

利用EXCEL电子表格开发洪水预报系统，解决了一维插值、二维插值等计算难题，系统易学易用，提高了工作效率，也为洪水预报系统开发提供了新的途径，具有很强的独创性、推广性。

EXCEL电子表格；开发；洪水预报系统

在日常的水情工作中，进行洪水预报作业时，需要查很多图表，还需要进行很多复杂繁琐的计算，比如产流计算、汇流计算等等。如果所有这些都要人工完成，费时费力，而且容易出错。通过编程开发洪水预报系统方便快捷，但对于一般的水情工作者又太难，有没有一种更为简便的方法呢？答案是肯定的。

1.EXCLE电子表格在洪水预报中应用

现在，EXCLE电子表格在日常学习、工作中得到了广泛应用，它可以输入输出、显示数据，并能把运算后的结果显示为需要的图表，大大提高了工作效率，因而完全可以利用EXCEL电子表格开发出一套洪水预报系统。以沙河昭平台水库的洪水预报系统为例，详细介绍一下如何利用EXCEL电子表格进行洪水预报调度系统的开发。

昭平台水库洪水预报系统组主要包括：产流、汇流两大部分。产流采用降雨径流相关模型，汇流采用单位线推流。

1.1 先建立一个工作簿，取名“昭平台水库洪水预报系统”，在该工作簿里建立基本资料表、净雨计算表、降雨净雨查算表、水位库容查算表、汇流计算表、水位预报表、预报成果表。

1.2 基本资料表（见表1），主要是录入预报作业需要用到的一些基本的数据：前期影响雨量Pa、△t=2h的实时雨量、预报起始水位等。

表1 基本资料表

1.3 净雨计算表（见表2），降雨时间、降雨量、Pa是对表1的引用，在C4单元格输入：“=C3+B4”，是累计降雨的一个推求，累计净雨牵涉到对降雨径流查算表的一个引用。

表2 昭平台水库净雨计算表

如何制作降雨径流查算表呢？其实这是一个二维线性插值的问题，什么是二维线性插值呢？对于一个二维数组r，我们假设对于任意一个浮点数i,r(a,i)到r(a+1,i)之间是线性变化的,r(i,b)到r(i,b+1)之间也是线性变化的(a,b都是整数)，那么对于浮点数的坐标(x,y)满足(a<=x

现在已经知道r(x,b)和r(x,b+1)了，而根据假设r(x,b)到r(x,b+1)也是线性变化的，所以:

这就是二维线性插值，在数学上，二维线性插值是有两个变量的插值函数的线性插值扩展，其核心思想是在两个方向分别进行一次线性插值。在人工查询降雨径流关系线的时候，其实是在图上判断给定条件降雨量P、前期影响雨量Pa在图上的位置，用二维线性插值进行模拟计算，返回需要的径流深R。

打开“P～Pa～R”表，查看预报方案图册，从昭平台水库的降雨径流关系线图（是一组以Pa分级的曲线）摘录节点数据，输入到表格在e列到m列输入）中。

对于表中的节点数据，一定要再三核对，因为这牵涉到以后查算结果的准确性。在“F3”单元格里输入以下公式“=TREND(IF(A4=LASTY,OFFSET($E$3,WY,WX-(B4=LASTX),,2),CHOOSE({1,2},TREND(OFFSET($E$3,WY,WX,2),OFFSET($E$3,WY,,2),A4),TREND(OFFSET($E$3,WY,WX+(B4<>LASTX),2),OFFSET($E$3,WY,,2),A4))),OFFSET($E$3,,WX-(B4=LASTX),,2),B4)”，鼠标移到单元格右下角，下拉自动填充公式。这里用到几个函数，TREND函数是返回线性回归拟合线的纵坐标，OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用;choose函数从参数列表中选择并返回一个值;还有“LASTX、LASTY、WY、WX”几个自定义的名称。对于引用过来的一组累计雨量数据，我们先判定给定的查询条件“P”与“Pa”值在数据表格中的位置，返回一组净雨的数据到C列，通过数据引用，净雨计算表的累计净雨列里就自动填充了净雨数据，然后通过简单的公式在时段净雨列里计算出每个时段的净雨。

1.4 汇流计算

打开汇流计算表，制作一表格（如表3）。

表3 汇流计算表

因为昭平台水库的单位线是3条（根据净雨深不同选择不同的单位线），制作汇流计算表的难点在于：如何让表格根据时段净雨的大小自动选择单位线进行汇流计算。在F3列输入一个公式“=OFFSET($B$2,IF(ROW(B2)-COLUMN(B2)+1>0,ROW(B2)-COLUMN(B2)+1,4^7),MATCH(1,FREQUENCY(F$2,{20,40}),))*F$2/10”。与以上相同的函数不再赘述，这里FREQUENCY函数的作用是：计算数值在某个区域内的出现频率，然后返回一个垂直数组。也就是说先判断净雨深的大小，然后根据给定条件的选择单位线数据列进行演算。

1.5 水位预报

对于一个水库，如何预报最高水位呢？如果不考虑泄流，可以根据求得的净雨计算出来水量，由起始的水位与库容，推出入库后的总库容，然后由库容曲线查出水位。打开水位预报表。制作洪水演算表（见表4）。B3是对初始水位的一个引用，起始库容是表格自动算出来的，“入库流量”列是对入库流量表格最后一列“合计”的引用，在E3列里输入公式“=(E3+E4)/2×(A4-A3)×24×3600”反推入库的水量，泄水量由资料表里的泄流数据计算而得，在C4单元格里输入“=(F4-G4)/100000000+C3”，由入库水量、泄水量推出实时的蓄量，由于要知道最高水位，可以先找出最大的蓄量，在i4输入“=INDEX(C:C,MATCH(MAX($C:$C),$C:$C,))”，这样就找出了最大蓄量的最大值。

表4 昭平台水库洪水演算表

如何由蓄量自动查出对应水位呢？这里主要牵涉到一维线性插值问题，什么是一维插值呢？已知离散点上的数据集，即已知在点集X上对应的函数值Y，构造一个解析函数（其图形为一曲线）通过这些点，并能够求出这些点之间的值，这一过程称为一维插值。也就是说，从一组试验观测数据（Xi，Yi），i=0,1,2，……，n之中找到自变量X与因变量Y之间的函数关系，一般可用一个近似函数y=f（x）来表示。

打开“水位库容查算”表，从已知的昭平台水库的水位库容关系线摘录节点数据，为了计算的方便，要按照两列输入，一列水位数据，一列库容数据。在E2单元格（也可以是别的单元格）中输入“Z”，把F2单元格链接到初始水位，在F3单元格中输入公式“=IF(F2=OFFSET(A4,LAST,),OFFSET(B4,LAST,),TREND(OFFSET(B4,MATCH(F2,OFFSET(A4,1,,LAST)),,2),OFFSET(A4,MATCH(F2,OFFSET(A4,1,,LAST)),,2),F2)”。这里公式的意义就是：通过判定给定数据在数据表中的位置，通过线性拟合，返回一个需要的值，这样水位库容查算就算做好了。

2.洪水预报系统的应用

打开预报成果表，把预报的洪峰时间、最高水位、最大流量都引用过来，这样一个完整的洪水预报系统就做好了，作业预报时，输入时段净雨、前期影响雨量、起始水位，系统会自动预报出结果。

3.结论

本预报系统利用EXCEL电子表格开发，技术成熟，适用范围广，拓展性好。洪水预报方案中有许多关系线，径流深——峰量关系线等，都可以用上面的插值计算解决。利用EXCEL电子表格开发洪水预报系统，方便快捷，是在洪水预报作业时，节省了人力、物力，提高了工作效率，为防汛调度决策赢得了宝贵的时间。其实，可以利用EXCEL强大的计算功能完成很多工作。

周军亭，（1975-），男，汉族，工程师，研究方向：水文水资源，从事水情工作。

2012-07-06