1、第三章 EXCEL解方程組,本章主要內容,3.1 Excel矩陣計算 3.2 解線性聯(lián)立方程 3.2.1 行列式法 3.2.2 矩陣法 3.3 Newton-Raphson迭代法 解非線性方程組 3.4 方程組的規(guī)劃求解,3.1 Excel 矩陣運算,1. Excel的矩陣格式 矩陣是數(shù)據(jù)（數(shù)值和變量）按行和列排成矩形的陣列： Excel工作表中用數(shù)組表示矩陣，單元格的位置對應 矩陣元素的位置，但標記順序相反: 矩陣元素aij是先行 (i)后列(j)，而單元格A3是先列(A)后行(3)。,3.1 Excel 矩陣運算,1. Excel的矩陣格式 列矩陣或行矩陣在Excel工作表中放在同一列 或

2、同一行，稱為一維數(shù)組。 mn矩陣元素依次放在工作表的一個區(qū)域 中，稱為二維數(shù)組。,3.1 Excel 矩陣運算,2. 數(shù)組加（減）法 進行數(shù)組加（減）法運算時，一般要求數(shù)組 中單元格的個數(shù)相同。 示例：建立二維數(shù)組A1:D3，一維數(shù)組F1:I1。 一維數(shù)組加（減）法： 1）A1:D1一行數(shù)據(jù)與F1:I1另一行數(shù)組相加，得到的仍是含四個單元格的一行數(shù)組，因此必須選定加和結果數(shù)組的位置，本例選F3:I3。,3.1 Excel 矩陣運算,2. 數(shù)組加（減）法 2）鍵入“”，用鼠標選定A1:D1區(qū)域，被選區(qū)域出現(xiàn)閃動的虛線框，同時編輯框的等號后出現(xiàn)A1:D1。 3）鍵入“”，選中F1:I1區(qū)域。同樣被

3、選區(qū)域出現(xiàn)閃動虛線框，加號后出現(xiàn)F1:I1。 4）按CtrlShiftEnter鍵。F3:I3區(qū)域出現(xiàn)加和結果，編輯框的公式被大括號括住:=A1:D1+F1:I1。,3.1 Excel 矩陣運算,注意： 1）若開始未選定區(qū)域F3:I3，而只選中了F3單元格，則完成上述各步計算后在F3出現(xiàn)數(shù)值2。 2）數(shù)組公式是一個整體，無論是編輯、移動、清除都必須選取公式包括對所有單元格。 3）大括號不用輸入，按CtrlShiftEnter后自動產(chǎn)生，表明這是數(shù)組公式。如果輸入公式時自己加上，Excel將認為輸入的是文字。 4）若相加的兩個數(shù)組單元格數(shù)目不等，則出現(xiàn)錯誤信號#N/A。,3.1 Excel 矩陣

4、運算,注意： 5）若加一常數(shù)到一數(shù)組，Excel會自動將該常數(shù)加到數(shù)組的每個單元格中。 二維數(shù)組加法類似于一維數(shù)組，此外還有如下特點：二維數(shù)組可以與行單元格或列單元格數(shù)目相同的一維數(shù)組相加。,3.1 Excel 矩陣運算,示例結果：,3.1 Excel 矩陣運算,3. 數(shù)組乘以一常數(shù) 數(shù)組乘以一常數(shù)，得到與原數(shù)組大小相同的新數(shù)組，新數(shù)組的每個單元格的值等于原數(shù)組單元格的值乘以此常數(shù)。,3.1 Excel 矩陣運算,4. 矩陣乘法 矩陣A（mn）與B（np）相乘，得一矩陣C（mp）。僅當?shù)谝粋€矩陣的列數(shù)等于第二個矩陣的行數(shù)時，兩矩陣才能相乘。 矩陣相乘時，各矩陣元素按一定規(guī)則相乘再相加。Exce

5、l有矩陣相乘的工作表函數(shù)MMULT(array1，array2）。,3.1 Excel 矩陣運算,4. 矩陣乘法 計算規(guī)則： 其中i為行數(shù)，j為列數(shù)。 例1. MMULT(1,3;7,2,2,0;0,2) 例2. MMULT(3,0;2,0, 2,0;0,2) 例3. MMULT(1,3,0;7,2,0;1,0,0, 2,0;0,2),3.1 Excel 矩陣運算,注意： 1）輸入MMULT函數(shù)前要根據(jù)乘積矩陣大小（mp）選定它的單元格區(qū)域。 2）函數(shù)名前有等號，輸入完畢同時按CtrlShiftEnter。 3）數(shù)組乘法不等于矩陣乘法，它是兩個數(shù)組對應單元格各自相乘，數(shù)組相乘用*，公式寫作數(shù)組

6、1*數(shù)組2。,3.1 Excel 矩陣運算,5. 逆矩陣 行數(shù)等于列數(shù)的矩陣稱為方陣。若一方陣的主對角元素aii均為1，其他位置上的元素均為0，則該矩陣稱為單位矩陣I 。 設有一n階方陣A和另一n階方陣B，它們的乘積為：AB=BA=I 則B為A的逆陣，可記為A-1。,3.1 Excel 矩陣運算,5. 逆矩陣 Excel求逆矩陣的工作表函數(shù)為 MINVERSE(array） 例1. MINVERSE(4,-1;2,0) 例2. MINVERSE(1,2,1;3,4,-1;0,2,0) 例3. MINVERSE()為一矩陣，可參與其他矩陣運算。,3.1 Excel 矩陣運算,6. 行列式 行列式

7、是在研究線性方程組的解法中產(chǎn)生的概念。n階行列式可表示為：,3.1 Excel 矩陣運算,6. 行列式 行列式通常用D表示，其元素用豎直線而非圓括號括住。 方陣與行列式是兩個不同的概念：n階方陣是n2個數(shù)按一定方式排列成的數(shù)據(jù)表；n階行列式是按一定的運算規(guī)則得到的一個確定值。,3.1 Excel 矩陣運算,6. 行列式 Excel計算行列式值的函數(shù)： MDETERM(array) 例1 MDETERM(1,3,8,5;1,3,6,1;1,1,1,0;7,3,10,2),3.1 Excel 矩陣運算,7. 轉置矩陣 矩陣A的行和列的元素交換后得到它的轉置矩陣A：,3.1 Excel 矩陣運算,7

8、. 轉置矩陣 1）Excel轉置矩陣函數(shù)： TRANSPOSE(數(shù)組） 注：要先根據(jù)原矩陣選定轉置矩陣區(qū)域，且數(shù)組函數(shù)輸入同時按CtrlShiftEnter鍵。 2）選擇性粘貼轉置 注：粘貼時只需選定區(qū)域的左上單元格即可。,3.1 Excel 矩陣運算,7. 轉置矩陣,3.2 解線性聯(lián)立方程,線性聯(lián)立方程： 設一組含n個未知數(shù)的n個方程： 其中xi(x1, x2, x3,) 是實驗未知數(shù)，ci是實驗測量值，aij是系數(shù)。這些方程必須是線性獨立的，上式的矩陣表示為： AX=C,3.2 解線性聯(lián)立方程,A是系數(shù)矩陣： X和C分別為解矩陣和常數(shù)矩陣：,3.2.1 行列式法,根據(jù)Cramer法則，線性

9、聯(lián)立方程有唯一解的條件是其系數(shù)行列式為非零值： 此時方程的解為：,3.2.1 行列式法,是方程組的系數(shù)行列式，但其中的第i列元素被常數(shù)列陣C所取代。例如 是用常數(shù)C列陣取代系數(shù)矩陣的第一列元素所得的行列式：,3.2.1 行列式法,示例： Excel步驟： 1）輸入注釋文字和系數(shù)數(shù)值。 2）在E2輸入: =MDETERM(A2:C4)，得到系數(shù)行列式的值9，說明聯(lián)立方程有唯一解。,3.2.1 行列式法,3）復制聯(lián)立方程的系數(shù)矩陣A2:C4，粘貼到下邊A6:C8、A10:C12和A14:C16。再復制常數(shù)列數(shù)組D2:D4，將其分別粘貼到A6:A8、B10:B12、C14:C16。得到分子行列式。

10、4）分別解出三個分子行列式的值，再根據(jù)公式 解出x，y，z的值。,3.2.1 行列式法,3.2.2 矩陣法,線性方程的矩陣表示經(jīng)數(shù)學變換得： XA-1C 即解矩陣（X）等于系數(shù)矩陣的逆矩陣（A-1）乘于常數(shù)矩陣（C）。 示例：同前聯(lián)立方程 1）輸入標注、系數(shù)矩陣和常數(shù)矩陣 2）選中E2:E4，輸入公式：=MMULT(MINVERSE(A2:C4),D2:D4)，同時按CtrlShiftEnter，可得解。,3.2.2 矩陣法,3.3 Newton-Raphson迭代法,原理： N-R迭代法屬于間接解法，可用于非線性方程組。原理類似于一元方程求解。然而因有不止一個自變量，導數(shù)變?yōu)槠珜В匠?/p>

11、為迭代增量的線性方程組。 設一非線性方程組：,3.3 Newton-Raphson迭代法,原理： 對方程組的每一個方程在其近似解 處用Teller級數(shù)展開，只取線性項得：,3.3 Newton-Raphson迭代法,原理： 其中 是方程 在 處的一階偏導數(shù)。上式為xi的線性方程組：,3.3 Newton-Raphson迭代法,原理： 將初始值 代入函數(shù)fi和偏導數(shù) ，然后用解線性方程組的方法計算得到 ，從而得到自變量xi的第一次迭代值： 繼續(xù)迭代，可得：,3.3 Newton-Raphson迭代法,原理： 直至： 為一小正數(shù)。 即為滿足指定精度的原方程的解。,3.3 Newton-Raphso

12、n迭代法,示例： 方程組： 每個方程對每一自變量的偏導數(shù)為：,3.3 Newton-Raphson迭代法,示例： 于是有線性方程組（1）：,3.3 Newton-Raphson迭代法,Excel解方程組步驟： 1）A列標記迭代次數(shù)。在A2、A3、A4輸入x1:0、 x2:0、 x3:0后，選定A2:A4區(qū)域，拖拽填充柄到適當位置。冒號后的數(shù)字表示迭代次數(shù)。 2）B2、B3、B4分別輸入x1、x2、x3的初始值0.5。 3）C2:E4單元格區(qū)域輸入線性方程組（1）的系數(shù)矩陣。xi的系數(shù)是含xi的數(shù)學表達式。,3.3 Newton-Raphson迭代法,Excel解方程組步驟： 4）F2:F4區(qū)域

13、輸入常數(shù)列矩陣。根據(jù)式(1)，F(xiàn)2輸入=-(B22+B32+B42-1)/2，F(xiàn)3單元格輸入=-(2*B22+B32-4*B4)/2，F(xiàn)4輸入 =-(3*B22-4*B3+B42)/2。 5）用矩陣法解線性方程式（1）：選定單元格G2:G4，輸入公式：=MMULT(MINVERSE(C2:E4),F2:F4），同時按Ctrl+Shift+Enter，得到第一次迭代改變量xi1 。,3.3 Newton-Raphson迭代法,Excel解方程組步驟： 6）用矩陣加法在B5:B7填入第一次迭代后的自變量xi1 ：選定B5:B7，輸入公式=B2:B4+G2:G4，按CtrlShiftEnter。 7

14、）重復解線性方程組過程：選取C2:G4，將填充柄向下拖3行。在G4:G7得到第二次迭代改變量xi1 。 8）重復迭代：選B5:G7，向下拖填充柄直至兩次迭代改變量小于1015。,3.3 Newton-Raphson迭代法,3.4 規(guī)劃求解,規(guī)劃求解解方程組是單變量求解解一元方程的延伸。 目標單元格是方程組中任一方程所在，目標值是該方程的常數(shù)?？勺儐卧袷欠匠探M的自變量所在。 約束為方程組中其余方程。“規(guī)劃求解”改變可變單元格的值，使得目標值與約束均得到滿足。,3.4 規(guī)劃求解,3.4.1 規(guī)劃求解解線性方程組 以前面的線性方程組為例： x，y，z是可變單元格變量，將F1的常數(shù)作為目標單元格的數(shù)

15、值，F(xiàn)2和F3的常數(shù)作為約束條件。任意設置可變單元格的值，目標是尋找滿足約束條件(6,3)，并使目標值為0的x，y，z。,3.4 規(guī)劃求解,Excel步驟： 1）B2:D2輸入未知數(shù)初始值(任意數(shù))1,1,1。 2）根據(jù)F1式，在B4單元格輸入公式：2*B2+C2-D2；根據(jù)F2，在C4輸入：B2-C2+D2；根據(jù)F3，在D4輸入：=B2+2*C2+D2。 3）打開“工具”菜單，單擊“規(guī)劃求解”，出現(xiàn)“規(guī)劃求解參數(shù)”對話框。,3.4 規(guī)劃求解,Excel步驟： 4）“設置目標單元格”輸入方程F1所在位置：$B$4，“等于”欄設置目標值為0。“可變單元格”編輯框輸入未知數(shù)所在單元格：$B$2:$

16、D$2。,3.4 規(guī)劃求解,Excel步驟： 5）單擊“約束”選項區(qū)的“添加”按鈕，出現(xiàn)添加約束對話框，在“單元格引用位置”處輸入方程F2的位置：$C$4，用下拉箭頭選取等號“”，在“約束值”處輸入F2的常數(shù)6。單擊“添加”，給出新的添加約束對話框，繼續(xù)增“單元格引用位置”處輸入F3的位置：$D$4，選“”，約束值為3。單擊“確定”，回到“規(guī)劃求解參數(shù)”對話框。,3.4 規(guī)劃求解,3.4 規(guī)劃求解,6）約束條件將出現(xiàn)在“約束”列表區(qū)：$C$4=6和$D$4=3。若約束條件設置有誤，可通過“更改”或“刪除”對約束條件進行改變。 7）所有參數(shù)設置好后，按“求解”，得到相應結果：,3.4 規(guī)劃求解,

17、3.4.2 解非線性方程組 多元一次線性方程比較容易，對初始值設定無嚴格要求。若方程為非線性，則必須設置不同初始值，才能保證解的可信性。 例：在Cd(ClO4)2和NaCN的水溶液中有如下平衡：,3.4 規(guī)劃求解,根據(jù)上述三個解離平衡，由物料平衡和電荷平衡可得到含三個未知數(shù)CN、Cd2+和H+的三個方程：,3.4 規(guī)劃求解,CL是加入的NaCN總濃度，CM是加入的Cd(ClO4)2總濃度，KW是水的離子積（10-14）。 三個方程均為未知濃度的高次函數(shù)，用“規(guī)劃求解”步驟如下： 1）在B1:B3單元格輸入Cd2總濃度CM ，CN的總濃度CL，HCN的酸解離常數(shù)Ka。 2）D2:F2是要求解的未

18、知濃度CN、Cd2+和H+所在位置。,3.4 規(guī)劃求解,3）三個方程均有求和項，先將其分別算出。B6:B9是各級積累穩(wěn)定常數(shù) i。在C6、D6、E6分別輸入公式：=B6*$D$2A6、=A6*C6、=(2-A6)*C6。用自動填充得各項的值，然后用自動求和，在C10、D10、E10分別得到F1、F2、F3方程中的求和項。 4）在D4:F4單元格，根據(jù)三個方程用D2:F2單元格的值計算，分別輸入公式： E2+E2*C10-B1 =D2+D2*F2/B3+E2*D10-B2 =F2+B2-1.0E-14/F2-2*B1-D2+2*E2+E2*E10,3.4 規(guī)劃求解,5）解非線性方程組，初始值的設置很重要。由于Cd2與CN形成的配合物很穩(wěn)定