化工計算機(jī)數(shù)據(jù)與圖形處理

1、第二章第二章 EXCELEXCEL解方程解方程 本章主要內(nèi)容本章主要內(nèi)容2.1 EXCEL的迭代用法2.2 逐步逼近法解方程2.3 Newton-Raphson法解方程2.4 單變量求解解方程2.5 求微分方程數(shù)值解2.1 EXCEL的迭代用法的迭代用法1. EXCEL迭代： A2=F(A1) 或 B1=F(A1) F(A1)為含引用單元格A1的公式，地址為相對引用。向下或向右拖曳填充柄時，EXCEL將自動更新每一步。即： A3=F(A2), A4=F(A3), C1=F(B1), D1=F(C1), 一般是向下拖曳。2.1 EXCEL的迭代用法的迭代用法2. 循環(huán)引用 公式引用自己所在的單元

2、格： A1=F(A1) 無論是直接引用還是間接引用。只要打開的工作簿中有一個包含循環(huán)引用，Excel 都將無法自動計算所有打開的工作簿。 2.1 EXCEL的迭代用法的迭代用法2. 循環(huán)引用 但有些化工計算需要用到循環(huán)引用，計算此類公式時，EXCEL必須使用前一次迭代的結(jié)果計算循環(huán)引用中的每個單元格。使用循環(huán)引用的過程如下：a) 打開“工具”菜單，選擇其中的“選項”指令。2.1 EXCEL的迭代用法的迭代用法2. 循環(huán)引用b)在“選項”對話框中，選擇“重新計算”標(biāo)簽。c)選“迭代計算”，在此設(shè)置循環(huán)引用時最多引用次數(shù)。EXCEL默認(rèn)的最多迭代次數(shù)是100次，最大誤差0.001。2.1 EXCE

3、L的迭代用法的迭代用法3. 循環(huán)引用舉例：計算弱酸氫離子濃度 弱酸解離平衡：由此得： Ka是酸解離常數(shù)，C是總酸濃度。+-+-H A HAH +AHAaK +H (H )aK C2.1 EXCEL的迭代用法的迭代用法3. 計算弱酸氫離子濃度 上式為一元二次方程，有現(xiàn)成求解公式，用“循環(huán)引用”也可以方便獲解。例：已知有一0.2000mol/L的二氯乙酸（HAD)溶液，其中HAD的解離常數(shù)為0.050。用循環(huán)引用求其氫離子濃度。2.1 EXCEL的迭代用法的迭代用法3. 計算弱酸氫離子濃度 具體求解：1）打開“工具”“選項”“重新計算”，選中“迭代計算”，在“最大誤差”欄內(nèi)填入1E-12。2）A1

4、、B1、C1單元格分別寫入： HAD、Ka、5.0E-22.1 EXCEL的迭代用法的迭代用法3. 計算弱酸氫離子濃度 具體求解：3）A2、B2、C2單元格分別寫入： HAD(始值）、HAD（終值）、H+.4) A3輸入數(shù)值：0.2000， B3輸入公式：A3-C3， C3輸入公式：SQRT($C$1*(A3-C3)。 2.1 EXCEL的迭代用法的迭代用法3. 計算弱酸氫離子濃度2.2 逐步逼近法解方程逐步逼近法解方程1. 原理原理 對于一元方程F（x）=0，可以在工作表上用減小x取值間隔的辦法逐步逼近準(zhǔn)確解。 其原理是縮小使函數(shù)yF（x）變號的x區(qū)間，從而找到一個x值，使得yF（x）0達(dá)到

5、一個精度。2.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：舉例：碳酸鈣在純水中的溶解度 CaCO3是難溶鹽，在純水中有如下平衡： 同時由物料平衡和電荷平衡關(guān)系得：2+2-2+2-333CaCO (s)Ca+CO=CaCO =2.9E(-9)spK 2-323CO +H OOH +HCO=2.1E(-4)bK 2+ 22+1/2Ca -(Ca)-=0bspspK KK2.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：舉例：碳酸鈣在純水中的溶解度 鈣離子的濃度即為CaCO3的溶解度，用x表示并將兩個K值代入，得： 將x按遞增序列逐漸變化，求得相應(yīng)的y值。當(dāng)y值變號時，表明它通過0。然后在y變

6、號的x區(qū)間內(nèi)，減少x的增值量，重復(fù)上述過程，直至達(dá)到要求精度的x值。2-71/2-9-7.8 10-2.9 10 =0 xx2.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：舉例：碳酸鈣在純水中的溶解度 x的起始值可以是任意的，最終都能得到方程的解。但應(yīng)該將方程作一初步分析，對x值的范圍有一個大致的了解，可以較快的確定方程的解。比如對此例： 因此x必然大于 。所以可用其作為x的起始值，x的增量定為 。-71/2-9= 7.8 10+2.9 10 xx-952.9 105.385 1051 102.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：舉例：Excel操作步驟操作步驟1) A1單元格

7、鍵入x，B1單元格鍵入y。在A2和A3單元格分別填入5E-5和6E-5。2) 在B2單元格輸入公式： 隨后用自動填充功能得到B3的值。再選中A2:B3這四個單元格，拖填充柄直至y變號。 A22-7.8E-7*SQRT(A2)-2.9E-92.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：舉例：Excel操作步驟操作步驟3) 重復(fù)上述過程，x起始值設(shè)為1.01E-4,增值量減為1E-6。在A10 和A11分別輸入 1.01E-4和1.02E-4。拖B8單元格至B11。選中A10:B11，拖動至y變號。以此重復(fù)進(jìn)行，可得到任意精度的解。2.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：迭代法快

8、速求解舉例：迭代法快速求解 將F(x)0可以改寫成： xf(x) 設(shè)定初始值x0，將其代入上式，得到改進(jìn)值x1 ： x1f(x0)如此重復(fù)，可逐漸逼近真實解x。2.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：迭代法快速求解舉例：迭代法快速求解Excel具體步驟：1）將D1和E1單元格分別標(biāo)為x和x，代表迭代求得的解及連續(xù)兩次迭代獲得方程根之差值。2）D2單元格輸入SQRT(2.9E-9)，在D3輸入SQRT(7.8E-7*SQRT(D2)+2.9E-9),在E3單元格輸入=D3-D2。2.2 逐步逼近法解方程逐步逼近法解方程2. 舉例：迭代法快速求解舉例：迭代法快速求解3）選中D3:E3，

9、用自動填充功能下拉填充柄，得不同迭代次數(shù)下x值及其精度。2.3 Newton-Raphson法法1. 原理：原理： 逐步逼近法有時速度較慢， Newton- Raphson法快得多。 N-R法是一種迭代過程，迭代形式為：Si是第i步迭代函數(shù)yi的斜率（一階導(dǎo)數(shù)）1/iiiixxyS2.3 Newton-Raphson法法1. 原理：原理： 基本思想：先在函數(shù)可能為0的x值范圍內(nèi)確定一初始值x1，函數(shù)曲線在x1的斜率S1=y1/(x1-x2) ，因此由函數(shù)y在x1的斜率得到改進(jìn)了的根x2： x2x1-y1/S1 如此重復(fù)，直至xi+1-xi等于0，或小于某一值。2.3 Newton-Raphso

10、n法法1. 原理：原理： 如圖：2.3 Newton-Raphson法法2. Excel 步驟：以步驟：以y=x3-3x2-6x+8為例為例1) 在A1，B1，C1分別輸入：x，y，S。2) 在A2輸入x的初始值5，在B2輸入公式：A23-3*A22-6*A2+8，在C2輸入導(dǎo)數(shù)公式：=3*A22-6*A2-6。3) 在A3輸入公式：=A2-B2/C2，得到改進(jìn)了的解。 2.3 Newton-Raphson法法2. Excel 步驟：以步驟：以y=x3-3x2-6x+8為例為例4) 選中B2:C2，下拉填充柄到B3:C3。5）選中A3:C3，下拉填充柄直至x值幾乎不變，即得x的真實解4。注：若

11、方程有多個解，則N-R法的解有賴于初始值選擇。因此需要先對函數(shù)圖象有一大致了解，才能獲得需要的解。（如初始值10,1E5,-10,-1E5,1E12）2.4 單變量求解法單變量求解法1. 原理原理 Excel “工具”菜單中的“單變量求解”是用 Newton-Raphson迭代法求近似解的程序。 “單變量求解”通過改變一選定單元格（可變單元格, x）的值，使得另一單元格（目標(biāo)單元格，y）的值達(dá)到預(yù)定值。 對于解方程，目標(biāo)單元格為方程所在，目標(biāo)值為0，可變單元格為使方程等于0的自變量值（即x）。2.4 單變量求解法單變量求解法1. 原理原理 2.4 單變量求解法單變量求解法2. 舉例：求舉例：求

12、CaCO3溶解度溶解度1) A1, B1內(nèi)分別輸入x，y。2) A2輸入單元格1作為初始值，B2輸入公式: =A22-7.8E-7*SQRT(A2)-2.9E-9。3) 打開“工具”“單變量求解”對話框，光標(biāo)停在“目標(biāo)單元格”內(nèi)，再單擊B2單元格?！澳繕?biāo)值”輸入0，光標(biāo)停在“可變單元格”內(nèi)，單擊A2單元格。點擊“確定”。 2.4 單變量求解法單變量求解法 2.4 單變量求解法單變量求解法3）最大誤差設(shè)置）最大誤差設(shè)置 從上例可以看出，所得解x0.0222并不精確，需要重新設(shè)定最大誤差： 打開“工具”“選項”“重新計算”。在該對話框的“最大誤差”里填入想要的設(shè)置，如1.00E-12。2.4 單變

13、量求解法單變量求解法3）最大誤差設(shè)置）最大誤差設(shè)置 2.4 單變量求解法單變量求解法3）最大誤差設(shè)置）最大誤差設(shè)置 分別將最大誤差設(shè)置為1.00E-5, 1.00E-9, 1.00E-12, 1.00E-15, 1.00E-99，用上述單變量求解方法求得相應(yīng)的x和y值。2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理原理 化學(xué)反應(yīng)動力學(xué)方程一般用一階微分方程表示，其通式為： a, b是自變量的定義域，f(x, y)為已知函數(shù)。d( , ) , (1)dyf x ya bx2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理原理 若一函數(shù)y=F(x)代入微分方程，使得式(1)成立，即: 則該函數(shù)

14、就是微分方程的解。 d ( )( ,( )dF xf x F xx2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理原理 解微分方程用到積分，得到的解析式是含一個任意常數(shù)C的通解。若有初始條件（初值）： y0F (x0) 則通解的常數(shù)C可以確定，得到特解。2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理原理 例如對于一級反應(yīng)：A B，其速率方程為： At是反應(yīng)物A在時間t時的濃度，其通解為：ttdAAdtk tAektC2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理原理 若已知t0時A的濃度為A0，則在初始條件下的特解為：0AA ektt2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理

15、原理 并非所有微分方程都有解析解。事實上除了一些簡單的基元反應(yīng)，大多數(shù)反應(yīng)動力學(xué)難以得到解析解或解析式很復(fù)雜，甚至不存在解析解。此時必須求助于數(shù)值解。 另一方面反應(yīng)動力學(xué)關(guān)心的問題是在t時刻反應(yīng)體系內(nèi)各物質(zhì)的濃度At , Bt , ，有足夠精度的近似解即可。2.5 求微分方程數(shù)值解求微分方程數(shù)值解1. 原理原理 數(shù)值解在化工“三傳一反”的模型化中都廣泛應(yīng)用。 數(shù)值解有普適性，可用于復(fù)雜的微分方程體系及任何初始條件。2.5 求微分方程數(shù)值解求微分方程數(shù)值解2. Euler法法 常微分方程數(shù)值解采用離散方法，即找出一種有效的數(shù)值計算方法，計算自變量的離散點：x0，x1，x2，xn以及對應(yīng)的y近似值

16、y0，y1，y2，yn。最簡單的是Euler法，通常取等間距的x值： x1-x0 =x2-x1= xn-xn-1=h h稱為步長。2.5 求微分方程數(shù)值解求微分方程數(shù)值解2. Euler法法 以初始條件(x0, y0)代入微分方程式(1)，得到初始點P0 (x0, y0)的斜率：以差商近似微商：000d(,)dx xyf xyx0100010d(,)dx xyyyyf xyxxxx2.5 求微分方程數(shù)值解求微分方程數(shù)值解2. Euler法法 因此有： 即x向前移動h，在x1處得到y(tǒng)1。 同樣可以由x1, y1代入式(1)得到y(tǒng)2。依此類推，可得到所有x0，x1，x2，xn對應(yīng)的y近似值y0，y

17、1，y2，yn。這些數(shù)值點連成一個折線函數(shù)，用以代替原來的曲線函數(shù)。1000(,)yyhf xy2.5 求微分方程數(shù)值解求微分方程數(shù)值解2. Euler法法2.5 求微分方程數(shù)值解求微分方程數(shù)值解2. Euler法法 Euler法簡單易懂，幾何意義明確，但誤差太大。由圖可知，由于誤差的累加，隨著x向前推進(jìn)，折線越來越偏離原來的曲線。2.5 求微分方程數(shù)值解求微分方程數(shù)值解3. Euler法示例法示例例：例：對一級反應(yīng)動力學(xué)方程 設(shè)定：初始濃度A0=0.2000mol/L，反應(yīng)速率常數(shù)k=0.01s-1。由上式可推得其求濃度近似值的遞推公式為：ttdAAdtk AAAt htthk2.5 求微分

18、方程數(shù)值解求微分方程數(shù)值解3. Euler法示例法示例具體解法如下：1）A列輸入時間，間隔為20s，直到140s。2）B列用Euler法計算在時間t時的濃度At。在B5單元格輸入初始濃度0.2000mol/L。B6輸入遞推公式:=B5-B5*$D$1*$D$2。3）選定B6單元格，向下拖拽到B12。由于B5單元格為相對引用，每下一格，濃度值更新一次，得到各個時刻由Euler法計算的濃度。 2.5 求微分方程數(shù)值解求微分方程數(shù)值解3. Euler法示例法示例4）C列是根據(jù) 計算的解析濃度值。在C5單元格輸入公式： =$B$5*EXP(-$D$1*A5)。 然后自動填充得其余值。5）D列為Eule

19、r法計算值與解析值的相對誤差: =100(解析值-Euler值)/解析值。0AA ektt2.5 求微分方程數(shù)值解求微分方程數(shù)值解3. Euler法示例法示例6) 減小步長h，可改進(jìn)精度。如步長分別為5和1時，在140s的誤差分別降為3.56和0.70。 2.5 求微分方程數(shù)值解求微分方程數(shù)值解4. Runge-Kutta法法 Euler法產(chǎn)生較大誤差的原因是f(x,y)為曲線，用Teller公式展開： Euler公式只取了線性項(斜率），忽略了高次項。2()()()().2nnnnhy xhy xhy xyx2.5 求微分方程數(shù)值解求微分方程數(shù)值解4. Runge-Kutta法法 R-K法則包括了Teller展開式的高次項，其中最常用的是四階R-K公式。在遞推公式里x取值為：xi, xi +h/2, xi +h。則微分方程通式的四階R-K公式為：2.5 求微分方程數(shù)值解求微分方程數(shù)值解4. Runge-Kutta法法微分