利用Excel规划拟合水位流量关系曲线

11水文张超嘉水位-流量关系曲线拟合是河流水文预报中的最重要的工作之一。研究Excel规划求解水位-流量关系曲线拟合方法，可以极大地提高河流水文预报的质量、精度和效率，加快水文预报科技进步的步伐。以2009年某河流水位-流量关系曲线拟合为实例，详细阐述水位-流量关系曲线拟合的Excel规划求解的方法和步骤。Excel规划求解水位-流量关系曲线一、H-Q散点图的绘制依据实测的原始“水位－流量”资料，利用Excel绘制“水位－流量”关系的散点图的方法比较容易。首先在Excel中选取要绘制的“水位－流量”关系散点图的数据区域，此数据区域应包括数据的列标题。然后点取Excel“插入”菜单中的“图表”工具，在弹出的“图表向导”窗体中选择“标准类型”选项卡的“ｘｙ散点图”，根据提示就可以轻松的绘出“水位－流量”关系的散点图某河流某水文站２００９年“水位－流量”关系散点图二、“水－流量”关系曲线拟合1、人工拟合人工拟合的不足“水位－流量”关系的散点图绘出后，可以根据散点分布情况，采用目估的方法手工绘制一条通过散点群中心的光滑曲线，这条曲线就是此河流在此水文站的“水位－流量”关系曲线。有了这条人工拟合的“水位－流量”关系曲线，便可以利用查图的方法进行大致的水文分析和计算工作了。人工拟合的“水位－流量”关系曲线在应用时受人为因素的影响较大，因为不同的人绘制的“水位－流量”关系图不可能完全一样，因此水文分析和计算的结果也不可能完全一致，也就是说不同的人得出的结论也不同。正是由于这样的原因，人工拟合的“水位－流量”关系曲线只适合一些不重要河流的临时或应急的水文分析和计算，不具备推广性。2、Excel“规划求解”拟合分析根据散点图人工拟合的“水位－流量”关系曲线，我们可以看出其线型符合幂函数的曲线类型，因此我们可以利用幂函数来描述“水位－流量”的关系。用近似的函数表达“水位－流量”关系，便于利用计算机进行相关的水文分析与计算，具有统一性和推广性。（1）加载“规划求解”规划求解加载宏是一个Excel中的加载项程序，在用Excel作“规划求解”之前，必须先进行加载。加载“规划求解”的方法如下：ａ．在“工具”菜单上，单击“加载宏”；ｂ．在“可用加载宏”框中，选中“规划求解”旁边的复选框，然后单击“确定”；ｃ．如果出现一条消息，指出您的计算机上当前没有安装规划求解，请单击“是”进行安装。加载规划求解后，“规划求解”命令就会添加到“工具”菜单中。（２）三参数幂函数三参数幂函数是标准的幂函数表达式。按照Ｈ＝ａＱｂ＋ｃ建立函数模型，其中ａ、ｂ、ｃ为待定参数，以水位差值平方和最小作为拟合目标函数，其反应“水位－流量”关系的近似程度最为理想。计算机曲线拟合可以采用的线型和计算方法有很多，在Excel中应用规划求解的方法进行“水位－流量”关系曲线拟合宜采用三参数幂函数（ｙ＝ａｘｂ＋ｃ）线型。（３）拟合计算拟合过程共需要５列来输入和计算数据，在第一列第二列中输入实测的流量、水位数据，在第三列中输入参数ａ、ｂ、ｃ的初始值，在第四列中按Ｈ＝ａＱｂ＋ｃ函数模型计算对应的拟合水位，在第五列中计算实测的水位与拟合水位的差值平方，在第五列数据尾部的单元格中计算差值平方的合计值（计算结果见表１的右部）。（4）“规划求解”“水位－流量”关系近似函数在“设置目标单元格”编辑框中选中目标单元格的位置。根据拟合函数对水位差值平方和应该最小的要求，在“等于”栏选择“最小值”选项。在可变单元格编辑框中选中要进行率定的ａ、ｂ、ｃ参数所在的位置。（4）“规划求解”“水位－流量”关系近似函数“约束”应该根据实际情况设置，从函数Ｈ＝ａＱｂ＋Ｃ可以看出，当Ｑ＝0时，Ｈ＝Ｃ，此时的Ｃ值实际上就是河底高程，因此，Ｃ值可以直接输入河底高程。如果Ｃ值参与曲线率定，就要对其添加必要的约束条件，即Ｃ值大于等于河底高程。约束条件的设置是在图２所示的设置窗体中的“约束”项下点选“添加”，然后在弹出的图３中的“改变约束”对话框中完成（本实例的河底高程为96.45）。规划求解参数设置完成后，单击图２中的“求解”，在弹出的图４所示的“规划求解结果”对话框中选中“保存规划求解结果”复选框，点击确定即可完成“水位－流量”关系近似函数的拟合。（4）“规划求解”“水位－流量”关系近似函数“水位－流量”关系近似函数拟合的结果及对应的参数ａ、ｂ、ｃ值如图５所示。ａ＝0.61、ｂ＝0.48、ｃ＝96.45，“水位－流量”关系近似函数为：Ｈ＝0.61Ｑ0.48＋96.453、Excel规划求解的H-Q拟合曲线根据Excel规划求解得到的水位-流量关系近似函数H=0.61Q0.48+96.45，我们就可以利用Excel的绘图功能，方便地绘制的出该河流在此测站的水位-流量关系拟合曲线了，如图结语大量的实践已证明，通过Excel规划求解得到水位-流量关系拟合曲线相当切合实际河流的水位-流量关系，拟合结果可以广泛地应用于水文工作的各个方面，极具推广价值，值得深入研究。Excel规划求解方法不仅仅能够用于水位-流量关系曲线（H—Q）的拟合，还可以拟合诸如水位-流速关系曲线（H-V）、水位-库容关系曲线（H-F）、河道基面曲线(Z-F)、泄流曲线（H-q）等许多类似曲线。