Excel在水文分析中的应用

摘要：利用excel中的绘图及规划求解功能，解决水文分析与计算中常用图形的绘制、水位~流量关系曲线拟合的问题。拟合的曲线用函数表达更利于计算机处理，而且对于一些实测点据的定线，还能减少徒手绘制因人而异所带来的任意性。

关键词：Excel 曲线拟合 规划求解

Excel作为一个通用的计算机软件，在各行业得到比较普遍的应用，在水利行业，不仅在计算上有很大用途，其图表功能也为我们进行水利水文分析提供很大的便利，本文通过嘉积水文站实测水位~流量资料，说明Excel在水文分析与计算中的应用。

一、水位~流量关系图形绘制

在水文分析与计算中，水位~流量关系的分析是最基本的，但是，采用手工方式在方格纸上点绘实测数据点是一项非常繁杂的工作，特别是当实测数据量比较多的时候。而在excel中，只要输入相关数据（在A列中输入流量，在B列中输入相应水位），点取“插入”-“图表”中选择“xy散点图”，根据提示就可以轻松的绘出水位~流量关系的散点图，如图1

图1

二、曲线拟合

水位~流量关系的散点图绘出后，根据散点分布，一般采用目估的方法通过点群中心绘制一条光滑的曲线，建立水位~流量关系曲线，以便进行相关的水文分析与计算的工作。该曲线也可以利用函数关系来描述，用近似函数表达比人工查图快速和准确，更利于计算机的处理，而且对于一些实测点据的定线，还能减少徒手绘制因人而异所带来的任意性。水文水利计算中，如库容曲线（Z~F），泄流曲线（Z~q），河道基面曲线(Z~F)，水位流量关系曲线（Z~Q）等都可以采用曲线拟合的方法来求其函数表达式。目前，在曲线拟合中采用的线型及计算方法有很多种，本文采用三参数幂函数（y=axb+c）线型，在excel中应用规划求解的方法进行曲线拟合。

“规划求解”是一组命令的组成部分，这些命令有时也称作假设分析工具。借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式 的最优值。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。

对于三参数幂函数，按照H=aQb+c建立方程，其中a、b、c为待定参数，以差值平方和最小作为拟合目标函数，即：

在A、B列中分别输入流量、水位数据，在C列中输入参数a、b、c的初始值，在D列中按H=aQb+c方程输入“=$C$2*b2^$C$3+$C$4”，在E列中输入“=（B2-D2）^2”，然后用拖动填充的方法填充D、E列，在E列数据尾输入“=SUM（E2:E7）”，如图2所示。

图2

单击工具栏中的加载宏，选取“规划求解”，在“设置目标单元格”编辑框中键入目标单元格的位置，在本例中是“$E$8”，并且根据目标函数的要求SMin，在“等于”栏选择“最小值”选项，接下来设置可变单元格，a、b、c值是要进行率定的，故可变单元格应填入a、b 、c参数所在的位置“$C$2:$C$4”。（图3）。单击“求解”，则率定结果立即显示（如图4所见），整个过程就是要求计算满足单元格“$E$8”的值最小时对应的参数a、b、c的值。

图3

图4

应用该方法率定出的加积水文站（二）水位~流量关系如图5，从图5结果来看，拟合的效果非常好。该段面的水位~流量关系式为：

H=0.2034Q0.4495+1.753

（三）注意的问题

1.从函数H=aQb+C来看，当Q=0时，H=C，此时的C值实际上就是河底高程，因此，在参数率定时，对于C值，可以直接输入河底高程，即参数C不参与曲线拟合。或者参与曲线率定，但是要添加必要的约束条件，即C大于等于河底高程（在图3中点击“添加”按钮可以添加若干约束条件），否则，率定的结果可能在数学上是最优的但是其物理含义无法解释的现象。a、b的起始值一般取0.5，这样可以减少函数迭代的次数。

2.率定好的数学公式在应用时应注意使用环境，在低水时由于受河底高程的限制，计算结果不会有太大的偏差，但是高水位时必须注意，在高水外延时不应与参与率定的原始数据中最高水位差别太多，否则可能会带来较大的误差。

图5