EXCEL在化工數值計算中的應用

趙宏策,高曉寧

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

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

1 EXCEL中求解方法的思路

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

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

1.1 邁步法

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

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

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

1.2 二分法

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

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

1.3 簡單迭代法

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

1.4 牛頓迭代法

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

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

1.5 弦截法

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

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

2 計算實例

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

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

(2)按照對流模型計算

積分整理得

數學模型已經建好，下面就是利用EXCEL求其解。

2.1 用邁步法求解

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

圖1 用邁步法計算結果

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

2.2 二分法

二分法一般是和邁步法結合起來使用的，在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)的正負標志，在B3單元格里輸入“=IF(E2*F2<0,B2,D2)”，在C3單元格里輸入“=IF(E2*F2<0,D2,C2)”，在G3單元格里輸入“=IF(ABS(D3-D2)<0.0001,“OK”,“繼續！”)”，分別拖動填充手柄，直到出現“OK”，如圖2所示。

圖2 用二分法計算結果

2.3 簡單迭代法

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

圖3 用簡單迭代法計算結果

2.4 牛頓法

與邁步法結合使用，我們在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”,“繼續！”)”，在B3單元格里輸入“=E2”。分別拖動填充柄可得如圖4所示效果。

圖4 用牛頓法迭代法計算結果

2.5 弦截法

與邁步法結合使用，我們在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”,“繼續！”)”，分別拖動填充柄可得如圖5所示的效果。

圖5 用弦截法計算的結果

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

3 結束語

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

表1 四種計算方法的比較