利用 EXCEL 工具率定典型流量数学模型方法探讨

黄清涛，王光义，王建兴

（1.湖北省随州市水文水资源勘测局，湖北 随州 441300；

2.湖北省襄阳市水文水资源勘测局，湖北 襄阳 441003；

3.湖北省黄石市水文水资源勘测局，湖北 黄石 435002）

利用 EXCEL 工具率定典型流量数学模型方法探讨

黄清涛1，王光义2，王建兴3

（1.湖北省随州市水文水资源勘测局，湖北 随州 441300；

2.湖北省襄阳市水文水资源勘测局，湖北 襄阳 441003；

3.湖北省黄石市水文水资源勘测局，湖北 黄石 435002）

介绍 EXCEL【规划求解】功能拟合流量数学模型的操作方法和步骤，并与 EXCEL 内置图形函数拟合的流量数学模型进行对比分析，说明该功能拟合流量数学模型的优越性。分别以天然河道、水库堰闸、水电站实测流量系列资料为实例，应用该功能拟合不同类型的流量数学模型，结果表明【规划求解】功能拟合流量数学模型均能满足水文资料整编规范要求。

流量模型；规划求解；曲线拟合；EXCEL

0 引言

水位和流量要素是防汛抗旱、水资源开发利用及水利水电工程调度运用最重要的水文信息。随着计算机、网络和通讯技术的快速发展，水位要素已经实现了自动采集、存储和处理，而流量要素因为影响因素复杂，信息采集困难成为水情信息化、自动化的难点。EXCEL 软件具有强大的数据分析处理、表格和图形绘制功能[1]。通过对湖北省若干水文站长期监测的天然河道、水工建筑物、水电站等不同类型系列流量资料进行相关分析拟合、计算、误差统计和评估[2-3]，优选配置不同的流量模型计算公式，将流量模型置入水情系统，水位、电站发电功率等要素实现在线监测，江河实时流量的计算可全部由计算机自动完成。

1 流量模型率定实例研究

1.1 天然河道水位流量关系率定函数比选

1.1.1 利用 EXCEL【规划求解】功能拟合曲线

用数学模型来拟合单一水位流量关系曲线的工作过去作过不少尝试。水文资料整编规范[2]规定：单一曲线法推流，应结合测站特性，应用插值法或通过选用适当的数学模型拟合曲线，用水位推算流量。以万店水文站 2010 年实测流量资料[4]为例，说明利用 EXCEL【规划求解】功能拟合曲线方法，成果如表1 所示。

表1 万店水文站实测流量成果

利用 EXCEL 工具点绘的万店水文站水位～流量关系节点点据如图1 所示。

通过观察，水位流量呈幂指数关系，于是构造函数 Z = a Qb+ c，式中：Z 为水位；Q 为流量；a，b，c 为待定系数。

利用 EXCEL【规划求解】功能进行曲线拟合步骤如下：首先按照表2 形式输入实测水位、流量成果，在 D2～D4 列分别输入任意假定的数据；在单元格 E2 按照公式 Z = a Qb+ c 输入公式“＄D2*C2^＄3 +＄D＄4”确定并下拉填充至 E26；在单元格 F2输入公式“=（E2-B2）^2”填充至 F26，将 F2～F26 之和存放至单元格 F27 中。

图1 万店水文站水位～流量关系曲线

表2 规划求解过程（1）

其次单击 EXCEL 工具栏中的【加载宏】，选取【规划求解】，在“设置目标单元格”编辑框中键入目标单元格的位置（“$F$27”）。接下来根据目标函数——水位误差平方和最小的要求，在“等于”栏选择【最小值】选项，设置可变单元格。参数 a，b，c 值是要进行率定的，故可变单元格应填入 a，b，c 参数所在的位置“$D$2:$D$4”，规划求解过程如图2 所示。单击【求解】，则立即显示率定结果，整个过程就是要求计算满足单元格“$F$27”的值最小时对应的参数 a，b，c 的值。

图2 规划求解过程（2）

单击【求解】按钮并选择【保存规划求解结果】，将参数 a，b，c 求解结果代入预先构造函数，则可写出方程 Z = 0.277 1Q0.4287+ 67.808 3，将流量作为因变量则数学表达式可写为 Q = exp(1/0.428 7*ln ((Z - 67.808 3)/0.277 1)。利用该方程及表1 中实测流量求出相应水位点绘曲线见图1，可见拟合的关系与实测点据十分吻合。通过“三种检验”（符号、适线和偏离数值检验）和标准差计算[2]，完全符合水文资料整编规范要求。

1.1.2 利用 EXCEL 内置图形函数拟合曲线

EXCEL 内置图形函数有线性、对数、多项式、乘幂、指数及移动平滑等。将万店水文站 2010 年实测流量成果分别选择 EXCEL 内置图形函数不同的线型进行率定，均不能获得满意的结果，其中采用三次多项式虽然率定线与实测点据匹配较好，但因为水位基数大，水位的指数次方对函数贡献过大，淹没了有效水位从而使方程不能回归。采用其他内置图形函数拟合的方程也均不能满足“三种检验”的要求，系统和偶然误差过大，如表3 所示。

在实际工作中，当采用多项式率定水位～流量关系时，为避免水位基数过大造成方程不能回归，可以将观测水位减除断流水位使得成为有效水位，再率定相关关系，中高水位曲线拟合较好，但因为断流水位的推算存在任意性，故方案不如三参数幂指数方程 Z = a Qb+ c。

通过以上拟合分析，对天然河道稳定的水位～流量关系采用三参数指数函数 Z = a Qb+ c 并利用EXCEL【规划求解】功能拟合的方程为最优解。水文测站特性差异性大，有的站水流关系难以用 1 条曲线描述，可以分水位级率定关系。

表3 EXCEL 内置图形函数率定万店站水位～流量关系成果表

1.2 水库闸孔出流关系率定

表4 先觉庙（水库）站溢洪道实测流量成果表

图3 先觉庙（水库）站～µ 相关图

1.3 水电站出流关系率定

水电站流量通用方程为 Q = Ns/9.8 η h，

式中：Q 为流量；Ns为电功率；h 为实测水头，对冲击式水能机 h 由库水位减水能机轴中心高程而得；η 为包括水能机、发电机、变压器、传动装置和水力效率等在内的合并效率系数[5]。

为建立水电站发电单机功率 N 与效率系数 η 相关关系，按照不同功率级记录开机台数及总功率，观测库水位，测定相应发电流量，计算相应的单机效率系数。在 EXCEL 表格中点绘 N 与 η 相关点据，可知呈对数关系，利用 EXCEL 内置图形函数率定其N 与 η 关系（采用 EXCEL【规划求解】功能拟合流量数学模型与内置图形函数率定结果完全相同）。三道河站 2013 年实测电功率与效率系数相关图如图4所示。

图4 三道河站电功率与效率系数相关曲线

根据率定结果可写出效率系数公式：η = 0.248* ln (N) -1.042 3，“三种检验”合格，标准差计算很小，完全符合水文资料整编规范要求。

2 结语

江河流量的影响因素及其复杂，在实现水利水文信息化时，应选择主要影响因素建立相关模型。对不同的流量模型，有的可以利用 EXCEL 内置图形函数直接建立关系，有的则需构造函数利用 EXCEL【规划求解】功能求解参数。模型建立后应进行误差统计和评估，在使用时应注意模型率定的范围，当天然河道测站控制发生转移、水工建筑物尺寸改变、水电站（含电力抽水站）出力发生变化要重新率定新的参数[5]。

[1] 林祚顶.水文现代化与水文新技术[M].北京：中国水利水电出版社，2008: 507-551.

[2] 中华人民共和国水利部.SL247-2012 水文资料整编规范[S].北京：中国水利水电出版社，2012: 14-27.

[3] 水利部水文局.水工建筑物测流[M].北京：中国科学技术出版社，1994: 11.

[4] 水利部水文局.中华人民共和国水文年鉴（第 6 卷第16 册）[M].郑州：黄河水文勘测设计院，2010: 47-50.

[5] 中华人民共和国水利部.SL207-92 水工建筑物测流规范[S].北京：水利水电出版社，1992: 30-57.

Research on Method of Calibrating Typic Discharge Mathematic Model Using Excel Solver

HUANG Qingtao1, WANG Guangyi2, WANG Jianxing3

(1.Suizhou Hydrology and Water Resources Survey Bureau of Hubei Province, Suizhou 441300, China;
2.Xiangyang Hydrology and Water Resources Survey Bureau of Hubei Province, Xiangyang 441003, China；
3.Huangshi Hydrology and Water Resources Survey Bureau of Hubei Province, Huangshi 435002, China)

The article introduces the operating methods and steps of excel solver fit discharge mathematic model, compares it with the inner graphics function in excel, and proves the superiority of excel solver in fitting discharge mathematic model.Respectively based on the observed discharge data from natural river, reservoir and weir, and hydropower station, it applies excel solver to fit different kinds of discharge mathematic model, and the results prove that it can meet the code requirement of hydrologic data processing.

discharge model; solver; curve fitting; excel

P337；O212

A

1674-9405(2014)04-0028-04

2014-03-07

黄清涛（1963－），男，湖北随州人，教授级高工，从事水文情报预报和水资源监测分析研究工作。