Excel在水文水利分析与计算中的应用_别君霞.doc

Excel在水文水利分析与计算中的应用_别君霞 20091No14四川水利 #51# Excel在水文水利分析与计算中的应用 别君霞,刘力军,张 红 (四川省水利水电勘测设计研究院规划设计分院,四川德阳,618000) 统计、模拟计算等功能,Excel在水文水利计算中具有广阔的应 =摘 要MicrosoftExcel软件具有强大的数据分析、 用空间,本文通过关系曲线拟合、水库径流调节计算两个典型案例进行分析,使我们看到Excel在应对繁琐的水文水利分析与计算问题时,如何将冗繁的计算过程简单化、可视化、清晰化。 =关键词MicrosoftExcel 关系曲线拟合 规划求解 水库径流调节 迭代计算 MicrosoftExcel强大的分析计算功能、友好的设计界面、及时交互的人机对话场景,在水文水利计算工作中具有广阔的应用空间。MicrosoftEx2cel作为通用的计算机办公平台,经历了Ex2cel510、Excel97、Excel2000、Excel2002、Excel2003、Excel2007等版本的更新换代,目前广泛应用的2003、2007版本已经相当稳定成熟。本文将以关系曲线的拟合、水库径流调节计算两个典型案例,说明Excel在水文分析与计算中的应用。 石河堰山平塘工程类型 10 20 30 40 50 60 70 80 901 016601701720175017801850190195 表1某灌区不同典型年不同工程类型 复蓄系数调查成果 径流频率P(%) 01810170165016001550152015001450143 小型水库018301750172016801650163016001550153 112 曲线拟合 在Excel2007中,只要输入两组相关数据,点 取/插入0)))/图表0中选择/xy散点图0,根据提示就可以轻松地绘出频率~供水系数(P~K)关系的散点图(如图1)。在点汇P~K关系图后,可以根据散点的分布情况初步确定散点图函数图像的线型,如石河堰的图像为指数函数图像线型,函数的一般式为: y=Ae+ C BX 1 关系曲线图的绘制 111 问题引入 在水文水利计算中,利用有限的已知数据量去分析计算或预测未知的数据相当普遍,其关系曲线的绘制一般采用曲线拟合法进行。大型灌区的小型水利设施数量多、分布广,一般采用/复蓄系数0法计算可供水量。按照小型水库、山平塘、石河堰的有效容积,根据不同来水频率的典型调查情况,确定其相应的复蓄次数(小型水库、塘堰供水系数如表1)。根据调查的复蓄次数与径流频率的关系点绘供水系数曲线,可以反插其他频率的供水系数。采用手工方式在方格纸上点绘实测数据点,是一项非常繁杂的工作,而且插值受到人为因素的影响,精度也要打折扣。利用Ex2cel2007软件,可以比较轻松地进行曲线拟和。 图1 频率供水系数(P~K)相关图 第1/4页 #52# 别君霞,刘力军,张 红:Excel在水文水利分析与计算中的应用 20091No14 山平塘、小型水库的函数图像为幂函数图像线型,函数的一般式为: y=Ax+C 通过利用Excel2007,可以轻松实现对P~K关系曲线的拟合,找到最接近实际的函数参数,从而确定函数关系式。 以石河堰的关系曲线拟合为例,在Excel2007中应用规划求解的方法进行曲线拟合。/规划求解0是一组命令的组成部分,这些命令有时也称B 作假设分析工具。借助/规划求解0,可求得工作表上目标单元格中公式的最优值。/规划求解0将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整,最终在目标单元格公式中求得与期望值最为接近的结果。/规划求解0通过调整所指定的可变单元格中的值,从目标单元格公式中求得所需的结果。 曲线拟合实际上就是找一条曲线,使得所有点到拟合线的拟合插值的平方和最小,对于指数函数,即: n d=Ex 2 n Bx 2 i=0(yi-yi)=Ei=1 (Ae+C-yi)为最小。 在Excel中可以很直观的实现这一过程。首先在列A、B中分别输入调查的P、K数据,在计算参数单元格中分别输入参数的初值,本文给A、B、C输入的初值分别为-115、-0105、5,在列C中 按K=(AeBx+C)2 方程输入/=BS|3*EXP(CS|3*A7)+DS|30,在列D中输入/=(B7-C7)^20,在D列数据尾输入/=SUM(D7BD15)0(如图2所示) 。 图2 Excel计 算过程 图3 参数求解操作 单击/MicrosoftOffice按钮0,单击/Excel选项0,单击/加载项0,在/管理0框中,选择/Excel加载宏0,在/可用加载宏0框中,选中/规划求解加载项0复选框,然后单击/确定0。单击/数据/选项卡,选取0规划求解/,在/设置目标单元格0框中键入目标单元