EXCEL在化工數(shù)值計算中的應(yīng)用

趙宏策,高曉寧

(1.山東雙一科技股份有限公司,山東 德州 253000; 2.德州凱盛晶華玻璃有限公司,山東 德州 253000)

化工計算是整個化工設(shè)計的基礎(chǔ)部分，為生產(chǎn)過程中各種操作參數(shù)的調(diào)節(jié)提供了定量的依據(jù)，因此化工計算必須在設(shè)備設(shè)計或選型之前完成。在實際的生產(chǎn)中，以化工計算的結(jié)果為依據(jù)，規(guī)定某些操作參數(shù)的允許變化范圍，使生產(chǎn)得以穩(wěn)定、均衡地進(jìn)行。然而在實際化工計算中常常會碰到一些數(shù)學(xué)問題的精確解(解析解)較難得到或無法直接求解。這時進(jìn)行計算卻找不到合適的軟件，如果為此編寫專門的程序來實現(xiàn)往往又不太現(xiàn)實，如果采用手工計算的方式，則工作量又相當(dāng)?shù)拇螅抑虚g過程易出現(xiàn)錯誤。為解決此問題，我們想到，EXCEL作為OFFICE辦公自動化軟件的一部分，是功能強大的電子表格管理軟件，有著強大的數(shù)據(jù)處理和計算功能，如何將化工中的數(shù)學(xué)問題直接采用EXCEL來處理，以解決這些繁瑣的計算問題，并能獲取計算結(jié)果。因此本文主要講的是用EXCEL在非線性方程的數(shù)值解法中的應(yīng)用，對計算工程中的數(shù)學(xué)問題起到拋磚引玉的作用。

1 EXCEL中求解方法的思路

用EXCEL求解非線性問題的方法很多，我們這里只介紹二分法、簡單的迭代法、牛頓迭代法、弦截法這四種求解法，結(jié)合EXCEL里IF()函數(shù)的應(yīng)用。四種方法各有不同，各有特點，望大家在解決實際問題時仔細(xì)甄別，挑選出適合自己的解題方法。

這四種方法雖然計算簡便，如果這些方法和邁步法方法結(jié)合起來用，能發(fā)揮更大的作用，也可減少運算次數(shù)，下面我給大家一一介紹一下各種方法的求解思路和計算步驟。

1.1 邁步法

邁步法的基本思想：先求出一個比較粗糙的初近似值a；再設(shè)法找到另一個值b，使得在區(qū)間[a,b]中恰好只含有方程f(x)=0的一個實根，判斷f(x)在區(qū)間有實根的依據(jù)是f(a)*f(b)<0。這個步驟叫做“實根的隔離”，這樣的區(qū)間叫做“隔根區(qū)間”。若隔根區(qū)間適當(dāng)小，則該區(qū)間內(nèi)的任一點都可作為根的初始近似值。

邁步法進(jìn)行根隔離的具體步驟：首先選定一個步長h，然后分別計算x=a，x=a+h，x=a+2h，……時的函數(shù)值f(a)，f(a+h)，f(a+2h)，……，直至兩個相鄰數(shù)值異號，不難理解，所求的根必在某相鄰的兩個x之間。每邁一步，檢查起點a+ih和終點a+(i+1)h的函數(shù)值是否同號，如不同號，即f(a+ih)*f(a+(i+1)h)≤0,則所求的根必在a+ih和a+(i+1)h之間。

用邁步法進(jìn)行根的隔離時，h要選擇適度，如果h過大，則相鄰的兩個x之間可能含有兩個或兩個以上的實根。如果h太大，則相鄰的兩個x之間含有偶數(shù)個實根時會因f(a+ih)與f[a+(i+1)h]同號將這偶數(shù)個實根漏掉。顯然，步長縮小，精度提高。當(dāng)精度要求較高時，步長要求很小，計算機循環(huán)計算的次數(shù)將會很大，所耗時間較長，不合算。因此此法不是求精確解的好辦法，只用于求根的初始近似值，是幫助其它解法的輔助方法。

1.2 二分法

二分法解方程的基本思想：函數(shù)f(x)=0在所定區(qū)間[a,b]上連續(xù)，在區(qū)間的兩個端點上的f(x)值f(a)與f(b)互為異號，即f(a)* f(b)<0，且在[a,b]內(nèi)只有一個實根，則此根就是f(x)與X軸的交點，求x=(a+b)/2，求f(x)，檢查f(x)與f(a)是否同號，如果是同號，把x當(dāng)成新的a，否則把x當(dāng)成新的b，得到新的區(qū)間，重復(fù)求a和b的中點的值，判斷與f(a)是否同號，不斷循環(huán)下去，直到達(dá)到精度為止，滿足一定精度的近似值。

具體步驟是取區(qū)間[a,b]的中點c=(a+b)/2，判斷點c是否為方程滿足一定精度的根，如果f(c)=0，則結(jié)束二分法過程；否則，根一定在兩個半?yún)^(qū)間[a,c]或[c,b]之中。求出函數(shù)值f(c)，若f(c)· f(b)<0，則根必在區(qū)間[c,b]內(nèi)，令a1=c，b1=b，再把新的含根區(qū)間[a1,b1]二等分，得c1=(a1+b1)/2，比較f(c1)與f(a1)或f(b1)是否異號，若f(c1)* f(b1)<0，則根必在[c1,b1]。重復(fù)上述過程，直至區(qū)間長度小到一定程度，使f(cn)的絕對值小于一個很小的正數(shù)，則方程的根為x=(an+bn)/2。

1.3 簡單迭代法

簡單迭代法設(shè)計思想最簡單，這種方法需要選定解的某一粗糙近似值，然后由迭代公式反復(fù)校正根的近似值，使之逐步精確化，最后得到滿足精度要求的結(jié)果。

1.4 牛頓迭代法

牛頓法本質(zhì)上仍是迭代法，但由于其特定的迭代格式，決定了此法比普通的迭代法具有更高的收斂速度，從而成為方程求解更加有效的方法。

牛頓法是把非線性方程線性化的一種近似方法。設(shè)方程f(x)=0，在初值x0點附近對方程進(jìn)行泰勒級數(shù)展開，得：

1.5 弦截法

弦截法的基本思想與牛頓法相似，即將非線性函數(shù)f(x)線性化后求解。兩者的差別在于弦截法實現(xiàn)函數(shù)線性化的手段采用兩點間的弦線，而不是某一點的切線。

弦截法的幾何意義就是通過兩點的弦割線與X軸的交點xk+1不斷逼近真實根的過程，它體現(xiàn)的也是極限的思想。弦截法需要給出兩個初值，因此必須與邁步法結(jié)合起來，則最后搜索的區(qū)間的兩個端點值就可以作為弦截法的兩個初值。

2 計算實例

二級反應(yīng)A→B在管式反應(yīng)器中進(jìn)行。用活塞流模型計算時，得轉(zhuǎn)化率xA=96%，需要的管長為12m。但是反應(yīng)流體粘度很大，呈層流，采用對流模型能更好地描述其流動狀態(tài)。為保證xA仍為96%，問反應(yīng)管長為多少？

(1)按照活塞流模型計算，由

(2)按照對流模型計算

積分整理得

數(shù)學(xué)模型已經(jīng)建好，下面就是利用EXCEL求其解。

2.1 用邁步法求解

利用邁步法求方程根的近似區(qū)間。在EXCEL表格里，用K行表示計算次數(shù)，x行表示x的取值，f(x)表示所取x的函數(shù)值，“判定”表示計算結(jié)果是否滿足條件。在單元格B2中輸入初值20，在C3單元格中輸入22，說明所選擇的步長為2，在單元格B3中輸入計算公式“=B2*(1-B2*LN(1+2/B2)/2)-0.96”，在C3單元格里輸入“=IF(C3*B3<0,“OK”,“繼續(xù)！”)”。利用填充柄拖動手柄直至出現(xiàn)，如圖1所示符合要求的解。說明當(dāng)k=7時，x值在(30,32)區(qū)間里有解。

圖1 用邁步法計算結(jié)果

步長的選擇一般從大到小，使區(qū)間盡可能小。當(dāng)然步長也可以更小，也能達(dá)到想要的精度，只不過花費的時間就要長一些，所以說用邁步法只求求方程根的近似區(qū)間。

2.2 二分法

二分法一般是和邁步法結(jié)合起來使用的，在B2單元格中輸入30，在C2單元格里輸入32，在D2輸入“=(B2+C2)/2”，在E2單元格里輸入方程式“=D2*(1-D2*LN(1+2/D2)/2)-0.96”，在F2單元格里輸入“=IF(B2*(1-B2*LN(1+2/B2)/2)-0.96>0,1,-1)”，以判定f(a)的正負(fù)標(biāo)志，在B3單元格里輸入“=IF(E2*F2<0,B2,D2)”，在C3單元格里輸入“=IF(E2*F2<0,D2,C2)”，在G3單元格里輸入“=IF(ABS(D3-D2)<0.0001,“OK”,“繼續(xù)！”)”，分別拖動填充手柄，直到出現(xiàn)“OK”，如圖2所示。

圖2 用二分法計算結(jié)果

2.3 簡單迭代法

我們在B2單元格里輸入0.5，在C2單元格里輸入“=(B2^3+1)/3”，在C3單元格里輸入“=IF(ABS((C2-B2)/C2)<0.0001,“OK”，“繼續(xù)”)”。拖動填充柄可得如圖3所示效果。可見當(dāng)k>5時，x已趨于穩(wěn)定，因此在保留三圍有效數(shù)字的基礎(chǔ)上，我們可得方程在區(qū)間(0,1)內(nèi)的解。

圖3 用簡單迭代法計算結(jié)果

2.4 牛頓法

與邁步法結(jié)合使用，我們在B2單元格里輸入50，在C2單元格里輸入“=B2*(1-B2*LN(1+2/B2)/2)-0.96”，在D2單元格里輸入“=1-B2*LN(1+2/B2)+B2/(B2+2)”，在E2單元格里輸入“=B2-C2/D2”，在F2理輸入“=IF(ABS((B2-E2)/B2)<=0.0001,“OK”,“繼續(xù)！”)”，在B3單元格里輸入“=E2”。分別拖動填充柄可得如圖4所示效果。

圖4 用牛頓法迭代法計算結(jié)果

2.5 弦截法

與邁步法結(jié)合使用，我們在B2單元格里輸入30，在C2單元格里輸入35，在D2單元格里輸入“=B2*(1-B2*LN(1+2/B2)/2)-0.96”，在E2單元格里輸入“=C2*(1-C2*LN(1+2/C2)/2)-0.96”，在F2單元格里輸入“=C2-E2*(C2-B2)/(E2-D2)”，在G2單元格里輸入“=F2*(1-F2*LN(1+2/F2)/2)-0.96”，在B3單元格里輸入“=IF(D2*G2<0,F2,B2)”，在C3里輸入“=IF(G2*D2<0,C2,B2)”，在H3里輸入“=IF(ABS((F3-F2)/F3)<0.0001,“OK”,“繼續(xù)！”)”，分別拖動填充柄可得如圖5所示的效果。

圖5 用弦截法計算的結(jié)果

由以上計算結(jié)果可以看出，此題的x取值可以為31.84，當(dāng)然也可以更精確一些，因為再精確，對于此題沒有太大的實際意義，所以我們可以根據(jù)具體的問題保留有效數(shù)字。

3 結(jié)束語

通過以上的計算和對比表明(如表1示)：如果把EXCEL電子表格與復(fù)雜的化工計算相結(jié)合，可以減小計算量，提高計算效率和計算精度。用EXCEL能夠使用較少的計算步數(shù)得到精度較高的解，有著較快的收斂速度。但在實際的化工問題并不全是非線性方程的求解問題，還有許多諸如線性方程組、插值、曲線擬合等問題，這些問題中的很大一部分是可以通過EXCEL來完成的。只是在實現(xiàn)之前，需要對各類數(shù)學(xué)問題進(jìn)行較詳細(xì)的分析，然后對EXCEL加以靈活應(yīng)用，這樣就可以在不借助專門軟件或編寫特定程序的基礎(chǔ)上求解化工中的一些數(shù)學(xué)問題，以更好地方便化工中的實際計算需要。

表1 四種計算方法的比較