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

喬雙全，伏世紅，孟祥國(guó)

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

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

喬雙全1，伏世紅1，孟祥國(guó)2

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

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

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

0 引言

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

1 求解方法

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

1.1 直接迭代法

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

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

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

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

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

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

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

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

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

1.2 直接迭代加速法

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

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

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

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

1.3 隱式迭代法

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

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

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

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

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

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

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

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

2 結(jié)論

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

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

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

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

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

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

TV31

B

1007－7596(2011)05－0029－02

2011－05－12

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