Excel 迭代功能在水力计算中的应用

Excel 迭代功能在水力计算中的应用

乔双全1伏世红1孟祥国2

（1齐齐哈尔市水利勘测设计研究院，齐齐哈尔，161006；

2 嫩江尼尔基水利水电有限责任公司，齐齐哈尔，161005）

摘要：介绍了Excel的迭代功能，并应用于水力学公式求解，提出了直接迭代法、直接迭代加速法和隐式迭代法等3种应用Excel迭代功能进行水力计算的方法，并给出工程计算实例。关键词：Excel；迭代；水力学；数值方法

0 引言

在水力计算中经常需要求解非线性方程的根，非线性方程数值求解方法主要有迭代法、二分法等，由于计算复杂，工作量大，需要借助计算机编程加以解决。目前，水利行业水力计算软件数量少，工作中常常不能满足需要。Excel 作为常见的数据分析工具，具有强大的数值处理功能。采用Excel表格形式进行水力计算，无须编程，计算过程直观明了，便于阅读和改正，非常适合专业编程人员以外的工程设计人员理解和掌握。然而，在运用Excel 进行水力计算的过程中，很多工程设计人员不熟悉Excel迭代功能，而是直接列表手动试算，增加了工作量，降低了成果精度；另外，Excel只能进行显式函数关系试算，水力计算中很多公式函数关系是隐式的，很难转换为显式，不能直接利用其迭代功能进行试算。本文拟利用 Excel的迭代功能，设计直观的方程迭代算法，提出了迭代的加速算法和隐式函数关系迭代的算法，满足常见的水力计算求解。

1 求解方法

使用Excel迭代计算，应首先设定迭代计算选项，Excel2003操作方法：点击菜单工具→选项，选中重新计算选项卡，选中“迭代计算”选择框，设定最多迭代次数和迭代误差。Excel迭代计算通过调整“可变单元格”的数值，使“目标单元格”达到一个特定值，“可变单元格”最终数值即为方程的解，求解精度由最多迭代次数和迭代误差设定。

直接迭代法

当方程可用显式x=g(x)表达时，可用直接迭代法。迭代法是一种逐次逼近的方法，其基本思路就是将隐式函数方程归结为一组显式的计算公式，其过程是一个逐步显示化的过程。显式迭代很容易在 Excel 表格中实现，以收缩水深hc计算为例，说明直接迭代在Excel

中如何实现。

收缩水深hc 计算公式形式，见式（1），将其转化为x=g(x)的显式形式，见式（2）。

（1） 022

2

2

03=+-ϕαg q h T h c

c

）

（20

2

2

3

2 c c h T g q h =+

ϕα 式中：T 0—总势能，m ；q —单宽流量，m 3

/（s •m ）；hc —收缩水深，m ；α—水流动能校正系数，；φ—流速系数，；g —重力加速度，9.81m/s 2

。

用Excel 进行直接迭代计算的基本步骤和说明如下：

（1）在Excel 中建立如表1的新工作表，表中第1行为各个输入参数或计算公式说明单元格，第2行为输入的初始值或计算值；D1单元格g(x)代表式(2)的左边部分，H1单元格f(x)代表式(1)的左边部分。

（2）在A2、B2单元格中输入参数初始值，C2单元格可输入hc 试算的一个初始值，例1.00m ，其余各单元格按照第1行参数说明输入相应的计算公式，计算结果列于行2。

（3）将hc 试算初始值改为等于迭代公式单元格，即将C2单元格等于D2，启动迭代运算，直到满足设定的最多迭代次数或迭代误差的计算要求，计算结果列于行3。

（4）由表1计算成果，hc =0.793m ，单元格D2等于单元格C2，单元格H2等于0，即满足式(2)和式(1)函数关系，计算成果无误。上述计算结果是在最多迭代次数为100，迭代误差为情况下的成果，计算速度较快。

表1 直接迭代法求解收缩水深hc

直接迭代加速法

直接迭代法只要迭代足够多次，就可以使结果达到任意的精度，但有时迭代过程收敛缓慢，从而使计算量变得很大，为了提高计算效率，可以采用迭代收敛的加速法。常用迭代收敛的加速法有埃特金(Aitken)加速法和斯蒂芬森(Steffensen)迭代法等。以埃特金(Aitken)加速法为例，介绍一下在Excel 中的实现方法。埃特金公式形式为：

校正 )(x g x k =

(3)

再校正 )(k k x g x =+1 (4)

改进 )(x x x x x x x k k k k k +---=+++212

11

(5)

仍以收缩水深hc 计算为例，埃特金加速法在原直接迭代法的基础上增加3个计算单元格：校正、再校正和改进，其他参数和单元格同表1。C2单元格可输入hc 试算的一个初始值，例hc =1.00m ，计算结果列于行2。将hc 试算初始值改为等于迭代公式单元格，即将C2单元格等于F2，启动迭代运算，计算结果列于行3。

由表2计算成果，hc =0.793m ，同直接迭代法，埃特金加速法加快收敛，一般迭代1～2次即可达到计算精度。

表2直接迭代埃特金收敛加速法求解收缩水深hc

隐式迭代法

水力学中的很多公式很多难以转化为显式x=g(x)表达式，因此隐式f(x) =0 求解显得更为实用。隐式迭代法主要有牛顿(Newton)法、弦截法和抛物线法等。牛顿法具有更快的收敛速度，实现简单，以牛顿(Newton)法为例，介绍一下在Excel 中的实现方法。牛顿迭代法公式形式为：

)

(')(1k k k k x f x f x x -=+ (6)

如果函数比较复杂，求导有困难，则采用差分代替导数的近似牛顿法，迭代公式为： )( )

()(21k k k k x f h xk f h x f h

x x ∆--∆+∆-

=+ (7)

式中Δh 是数值导数的半步长，其大小需要合理选定。

以明渠均匀流正常水深h 计算为例，说明牛顿法的实现方法。明渠均匀流计算公式见式(8)。

Ri AC Q = (8)

式中：Q －流量(m 3

/s) ；A －过水断面面积（m 2

），A=(b+mh)h ；c －谢才系数，C=R 1/6

/n ；R －水力半径（m ），R=X/A ；X －湿周 (m)，

212m h b X ++=；i －水力比降；m －边坡系数；