Excel迭代功能在水力計算中的應(yīng)用

喬雙全，伏世紅，孟祥國

(1.齊齊哈爾市水利勘測設(shè)計研究院，黑龍江齊齊哈爾 161006;2.嫩江尼爾基水利水電有限責任公司，黑龍江齊齊哈爾 161005)

Excel迭代功能在水力計算中的應(yīng)用

喬雙全1，伏世紅1，孟祥國2

(1.齊齊哈爾市水利勘測設(shè)計研究院，黑龍江齊齊哈爾 161006;2.嫩江尼爾基水利水電有限責任公司，黑龍江齊齊哈爾 161005)

介紹了Excel的迭代功能，并應(yīng)用于水力學(xué)公式求解，提出了直接迭代法、直接迭代加速法和隱式迭代法等3種應(yīng)用Excel迭代功能進行水力計算的方法，并給出工程計算實例。

Excel;迭代;水力學(xué);數(shù)值方法;收縮水深;求解應(yīng)用

0 引言

在水力計算中經(jīng)常需要求解非線性方程的根，非線性方程數(shù)值求解方法主要有迭代法、二分法等，由于計算復(fù)雜，工作量大，需要借助計算機編程加以解決。目前，水利行業(yè)水力計算軟件數(shù)量少，工作中常常不能滿足需要。Excel作為常見的數(shù)據(jù)分析工具，具有強大的數(shù)值處理功能。采用Excel表格形式進行水力計算，無須編程，計算過程直觀明了，便于閱讀和改正，非常適合專業(yè)編程人員以外的工程設(shè)計人員理解和掌握。然而，在運用Excel進行水力計算的過程中，很多工程設(shè)計人員不熟悉Excel迭代功能，而是直接列表手動試算，增加了工作量，降低了成果精度;另外，Excel只能進行顯式函數(shù)關(guān)系試算，水力計算中很多公式函數(shù)關(guān)系是隱式的，很難轉(zhuǎn)換為顯式，不能直接利用其迭代功能進行試算。本文擬利用Excel的迭代功能，設(shè)計直觀的方程迭代算法，提出了迭代的加速算法和隱式函數(shù)關(guān)系迭代的算法，滿足常見的水力計算求解。

1 求解方法

使用Excel迭代計算，應(yīng)首先設(shè)定迭代計算選項，Excel 2003操作方法:點擊菜單工具→選項，選中重新計算選項卡，選中“迭代計算”選擇框，設(shè)定最多迭代次數(shù)和迭代誤差。Excel迭代計算通過調(diào)整“可變單元格”的數(shù)值，使“目標單元格”達到一個特定值，“可變單元格”最終數(shù)值即為方程的解，求解精度由最多迭代次數(shù)和迭代誤差設(shè)定。

1.1 直接迭代法

當方程可用顯式x=g(x)表達時，可用直接迭代法。迭代法是一種逐次逼近的方法，其基本思路就是將隱式函數(shù)方程歸結(jié)為一組顯式的計算公式，其過程是一個逐步顯示化的過程。顯式迭代很容易在Excel表格中實現(xiàn)，以收縮水深hc計算為例，說明直接迭代在Excel中如何實現(xiàn)。

收縮水深hc計算公式形式，見式(1)，將其轉(zhuǎn)化為x=g(x)的顯式形式，見式(2)。

式中:T0為總勢能，m;q為單寬流量，m3/(s·m);hc為收縮水深，m;α為水流動能校正系數(shù)，1.0;φ為流速系數(shù)，0.95;g為重力加速度，9.81 m/s2。

用Excel進行直接迭代計算的基本步驟和說明如下:

1)在Excel中建立如表1的新工作表，表中第1行為各個輸入?yún)?shù)或計算公式說明單元格，第2行為輸入的初始值或計算值;D1單元格g(x)代表式(2)的左邊部分，H1單元格f(x)代表式(1)的左邊部分。

2)在A2、B2單元格中輸入?yún)?shù)初始值，C2單元格可輸入hc試算的一個初始值，例1.00m，其余各單元格按照第1行參數(shù)說明輸入相應(yīng)的計算公式，計算結(jié)果列于行2。

3)將hc試算初始值改為等于迭代公式單元格，即將C2單元格等于D2，啟動迭代運算，直到滿足設(shè)定的最多迭代次數(shù)或迭代誤差的計算要求，計算結(jié)果列于行3。

4)由表1計算成果，hc=0.793m，單元格D2等于單元格C2，單元格H2等于0，即滿足式(2)和式(1)函數(shù)關(guān)系，計算成果無誤。上述計算結(jié)果是在最多迭代次數(shù)為100，迭代誤差為0.001情況下的成果，計算速度較快。

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

1.2 直接迭代加速法

直接迭代法只要迭代足夠多次，就可以使結(jié)果達到任意的精度，但有時迭代過程收斂緩慢，從而使計算量變得很大，為了提高計算效率，可以采用迭代收斂的加速法。常用迭代收斂的加速法有埃特金(Aitken)加速法和斯蒂芬森(Steffensen)迭代法等。以埃特金(Aitken)加速法為例，介紹一下在Excel中的實現(xiàn)方法。埃特金公式形式為:

仍以收縮水深hc計算為例，埃特金加速法在原直接迭代法的基礎(chǔ)上增加3個計算單元格:校正、再校正和改進，其他參數(shù)和單元格同表1。C2單元格可輸入hc試算的一個初始值，例hc=1.00 m，計算結(jié)果列于行2。將hc試算初始值改為等于迭代公式單元格，即將C2單元格等于F2，啟動迭代運算，計算結(jié)果列于行3。

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

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

1.3 隱式迭代法

水力學(xué)中的很多公式很多難以轉(zhuǎn)化為顯式x=g(x)表達式，因此隱式f(x)=0求解顯得更為實用。隱式迭代法主要有牛頓(Newton)法、弦截法和拋物線法等。牛頓法具有更快的收斂速度，實現(xiàn)簡單，以牛頓(Newton)法為例，介紹一下在Excel中的實現(xiàn)方法。牛頓迭代法公式形式為:

如果函數(shù)比較復(fù)雜，求導(dǎo)有困難，則采用差分代替導(dǎo)數(shù)的近似牛頓法，迭代公式為:

式中:Δh是數(shù)值導(dǎo)數(shù)的半步長，其大小需要合理選定。

以明渠均勻流正常水深h計算為例，說明牛頓法的實現(xiàn)方法。明渠均勻流計算公式見式(8)。

用Excel采用牛頓法進行迭代計算的基本步驟和說明如下:

3)將h試算初始值改為等于迭代公式單元格，即將G4單元格等于H4單元格，啟動迭代運算，計算結(jié)果列于表4。

表3 牛頓迭代法求解明渠均勻流正常水深h(初始值設(shè)定)

表4 牛頓迭代法求解明渠均勻流正常水深h(迭代結(jié)果)

2 結(jié)論

1)利用Excel迭代功能，可方便的實現(xiàn)水力學(xué)公式求解，計算過程直觀，便于檢查，易學(xué)掌握。用Excel迭代功能進行計算，即克服了繁瑣的手工試算，又避免了編寫專業(yè)的計算程序，在水力計算乃至其他計算中都值得推廣、采用。

2)Excel迭代工具實際采用的是弦截法，迭代過程中可能會出現(xiàn)錯過根值的情況，在試算時，可初估實際根值，若試算初始值小于實際根值無法得到試算結(jié)果，可將試算初始值設(shè)為大于實際根值。

3)Excel數(shù)值分析和計算功能強大，本文只涉及其迭代功能，其他尚有許多功能和技巧值得研究，如:規(guī)劃求解、矩陣功能等，值得在水力計算進一步應(yīng)用和開發(fā)。

［1］ 李慶揚，王能超，易大義.數(shù)值分析(第四版)［M］.北京:清華大學(xué)出版社，2001.

［2］ 李煒.水力計算手冊(第二版)［K］.北京:中國水利水電出版社，2006.

［3］ 熊啟鈞.灌區(qū)建筑物的水力計算與結(jié)構(gòu)計算［M］.北京:中國水利水電出版社，2007.

TV31

B

1007－7596(2011)05－0029－02

2011－05－12

喬雙全(1974－)，男，黑龍江齊齊哈爾人，工程師;伏世紅(1984－)，男，甘肅天水人，助理工程師;孟祥國(1983－)，男，吉林伊通人，助理工程師。