excel求解方程式

Excel是MicrosoftOffice家族成員之一，具有廣泛的用途。說它是「魔錶」，是因為它具有許多神奇的功能，在一張看似平常的表格上，通過Excel特殊的「工具」和專用「函數」，就可以解算各種複雜的數值問題。變化之奧妙，猶如演繹軍陣，變幻魔方，翻手為雲，覆手為雨，不由你不為之「走火入魔」。現在，Excel已列入計算機等級考試科目，有的省還列入職稱評定的必考內容，總之，Excel越來越被大家所接受。 本文專門介紹用Excel求解方程式的方法。 一、求解超越方程 不能用係數表達根的方程式稱為超越方程。求解超越方程通常有兩種方法，一種是圖解法，一種是迭代法。這兩種方法，手工計算都十分麻煩，利用Excel可以迅速獲得結果。 我們用

這個方程式作為例子，說明求解超越方程的一般方法。 1、圖解法 在A2單元格設定X的起始值（單位為弧度），在B2單元格設定X的步長（X每步的增加值）。在C2單元格輸入公式「=A2」，在C3單元格輸入公式「=C2+$B$2」（帶$表示B2單元格為絕對地址，複製公式時地址不發生變化），點住C3單元格右下角向下拖若干行，複製公式，完成各步的X值設置。在D2單元格輸入方程式公式「=3*C2-COS(C2)-1」，點住D2單元格右下角向下拖動，複製公式，完成方程式各步的計算工作。分析D列的計算結果，目的是尋找當方程式值為0時，X的變化區間。如果D列的數值不在0附近，可調整A2（X的起始值）；如果D列數值變化過大，可減小B2（X的增步長），直到滿意為止。調整A2和B2時，其它數值會自動變化。 以D列0為中心，選取上下附近C、D兩列的相應區域，點「插入」菜單的「圖表」，選區「XY散點圖」，按照「圖表嚮導」一步一步的做下去，調整好坐標軸的起止數值，增加X軸的「次要網格線」，以達到方程式曲線在穿越X軸（方程式值為0）時，能清晰辨認X的數值。從圖上可以看到，方程式曲線穿越X軸的數值約為6.07，這就是超越方程的解。圖解法求解超越方程，其優點是可以觀察方程式的變化趨勢，以及X值對方程式的影響情況，缺點是精度較低。 2、迭代法 用迭法求解一元方程的利器就是Excel的「單變數求解」工具。計算時需要將求解的公式輸入到某個單元格作為「目標單元格」。本例只需點D2單元格，點「複製」，再點F2單元格，點「粘貼」，即可將公式複製到F2單元格，這樣，F2就是「目標單元格」，E2自然成為X的變數單元格。 點F2單元格，點「工具」菜單的「單變數求解」。設「目標單元格」為F2，設「目標值」為0，設「可變單元格」為E2，點「確定」，即得計算結果：X=0.6071。 用「單變數求解」求解一元方程，優點是迅速、準確，缺點是不能了解函數的變化過程。

二、求解聯立方程 用Excel求解聯立方程有兩個方法，一個是使用矩陣函數，一個是使用「規劃求解」工具，兩個方法都十分簡便。 以下列3元聯立方程為例說明求解方法。

1、矩陣法 聯立方程一般可以用矩陣表示為AX=B，由此X=A^-1B。式中：A^-1為係數矩陣的逆矩陣，B為聯立方程右側的常量矩陣，取兩矩陣的積，即得聯立方程的解。 在A2：C4區域設置係數矩陣A，在D2：D4設置常數矩陣B。選取F2：F4，輸入公式「=MMULT(MINVERSE(A2:C4),D2:D4)」，同時按Shift鍵、Ctrl鍵和 Enter鍵，即得計算結果：X1=2、X2=3、X3=-2.5。 說明1，MMULT為矩陣積函數，用來計算矩陣的乘積。MINVERSE為逆矩陣函數，用來計算逆矩陣。這裡將兩個函數組合使用，可以減少計算步驟。 說明2，當在Excel上進行成組計算時，稱為數組操作。數組的計算必須同時按Shift 鍵、Ctrl鍵和Enter鍵，方才有效。數組操作的同時，將自動在公式兩側產生大括符，表示該運算為數組運算。2、規劃求解 規劃求解是Excel的特殊工具，主要用於系統規劃中的最優化問題，拿來求解常規的聯立方程，自然遊刃有餘。 設H2：H4為X1、X2、X3的變數區，在I2輸入公式「=2*H2-2*H3+4*H4」，在I3輸入公式「=2*H2+3*H3+2*H4」，在I4輸入公式「=-H2+H3-H4」。在I5輸入公式「=SUM（I2：I4）」，計算聯立方程常數項的和，其值為-12+8+3.5=-0.5，I5可設置成目標單元格，-0.5就是目標值。點I5，點「工具」菜單的「規劃求解」。「目標單元格」設為I5，「目標值」設為-0.5，「可變單元格」設為H2：H4。點「添加」，設置以下「約束」：I2=-12、I3=8、I4=3.5。點「求解」，完成計算，即得計算結果：X1=2、X2=3、X3=-2.5。

三、求解有約束的方程 所謂有約束的方程，就是除了主要方程式（稱作目標函數）外，還帶有若干附加條件。目標函數和這些附加條件未必都是「等於」的關係，它們可以是「大於」，可以是「小於」，也可以是「最大」或「最小」，方程式的數量也未必和未知變數相等，求解這類方程，顯然不是一般代數方法能夠奏效的。計算有約束方程的方法，在運籌學中稱為「最優化問題」，Excel為此設計了「規劃求解」專用工具。「規劃求解」可以解決社會、經濟、工程諸多實際問題，這裡我們只用一個數值例子，說明具體計算方法。 今有目標函數

，要求在以下約束下，獲得最大值。

這是一個求最大值的問題，方程式（不等式）的數量也超過變數的數目，所以只能用「規劃求解」方法計算。 我們在Excel表格中，設A2、B2、C2分別為X1、X2、X3三個變數（計算前為空格），在D2單元格輸入目標函數公式「=3*A2+B2+3*C2」,在E2單元格輸入第一約束條件「=3*A2+B2+C2」, 在F2單元格輸入第二約束條件「=A2+2*B2+3*C2」,在G2單元格輸入第三約束條件「=2*A2+2*B2+C2」。 點「工具」菜單的「規劃求解」，「目標單元格」設為D2，「目標值」設為「最大值」，「可變單元格」設為A2：C2。點「添加」，設置以下約束：E2≤2、F2≤5、G2≤6、A2:C2≥0。點「求解」，稍等片刻即得計算結果：X1=0.2、X2=0、X3=1.6，在此條件下，目標函數的最大值為5.4。

按Shift 鍵、Ctrl鍵和Enter鍵，即得計算結果：X1=2、X2=3、X3=-2.5。 說明1，MMULT為矩陣積函數，用來計算矩陣的乘積。MINVERSE為逆矩陣函數，用來計算逆矩陣。這裡將兩個函數組合使用，可以減少計算步驟。 說明2，當在Excel上進行成組計算時，稱為數組操作。數組的計算必須同時按Shift 鍵、Ctrl鍵和Enter鍵，方才有效。數組操作的同時，將自動在公式兩側產生大括符，表示該運算為數組運算。2、規劃求解 規劃求解是Excel的特殊工具，主要用於系統規劃中的最優化問題，拿來求解常規的聯立方程，自然遊刃有餘。 設H2：H4為X1、X2、X3的變數區，在I2輸入公式「=2*H2-2*H3+4*H4」，在I3輸入公式「=2*H2+3*H3+2*H4」，在I4輸入公式「=-H2+H3-H4」。在I5輸入公式「=SUM（I2：I4）」，計算聯立方程常數項的和，其值為-12+8+3.5=-0.5，I5可設置成目標單元格，-0.5就是目標值。點I5，點「工具」菜單的「規劃求解」。「目標單元格」設為I5，「目標值」設為-0.5，「可變單元格」設為H2：H4。點「添加」，設置以下「約束」：I2=-12、I3=8、I4=3.5。點「求解」，完成計算，即得計算結果：X1=2、X2=3、X3=-2.5。

三、求解有約束的方程 所謂有約束的方程，就是除了主要方程式（稱作目標函數）外，還帶有若干附加條件。目標函數和這些附加條件未必都是「等於」的關係，它們可以是「大於」，可以是「小於」，也可以是「最大」或「最小」，方程式的數量也未必和未知變數相等，求解這類方程，顯然不是一般代數方法能夠奏效的。計算有約束方程的方法，在運籌學中稱為「最優化問題」，Excel為此設計了「規劃求解」專用工具。「規劃求解」可以解決社會、經濟、工程諸多實際問題，這裡我們只用一個數值例子，說明具體計算方法。 今有目標函數

，要求在以下約束下，獲得最大值。

這是一個求最大值的問題，方程式（不等式）的數量也超過變數的數目，所以只能用「規劃求解」方法計算。 我們在Excel表格中，設A2、B2、C2分別為X1、X2、X3三個變數（計算前為空格），在D2單元格輸入目標函數公式「=3*A2+B2+3*C2」,在E2單元格輸入第一約束條件「=3*A2+B2+C2」, 在F2單元格輸入第二約束條件「=A2+2*B2+3*C2」,在G2單元格輸入第三約束條件「=2*A2+2*B2+C2」。 點「工具」菜單的「規劃求解」，「目標單元格」設為D2，「目標值」設為「最大值」，「可變單元格」設為A2：C2。點「添加」，設置以下約束：E2≤2、F2≤5、G2≤6、A2:C2
推薦閱讀：