錦子老師

本單元將列出了使用Excel中解多元一次方程式組的三種方法：矩陣解法、用克萊姆法則和用規劃求解的方法。

n  方法一：矩陣解法

原理：對於由n個未知數，n個方程式組成的多元一次方程式組：

寫成矩陣形式為Ax=b，其中A為係數n*n方陣，x為n個變數構成列向量，b為n個常數項構成列向量。當它的係數矩陣可逆，或者說對應的行列式|A|不等於0的時候，由Ax=b可得：x=b*A^(-1) ，A^(-1)為A的逆矩陣。

利用Excel提供的MDETERM、MINVERSE和MMULT等函數即可求解多元一次方程組。

MDETERM函數傳回一個陣列的矩陣行列式的值，可用其判斷矩陣是否可逆。

MINVERSE函數傳回矩陣的逆矩陣。

MMULT函數傳回兩個陣列的矩陣乘積。

範例及步驟：

假如在Excel工作表的A2:N5範圍中以下圖方法輸入了一個四元一次方程式組。

在P2:S5範圍用公式得到其係數矩陣，T2:T5的傳回值為常數項向量。如P2儲存格中的公式為：

=OFFSET($B$1,ROW(A1),COLUMN(A1)*3-3)*IF(OFFSET($A$1,ROW(A1),COLUMN(A1)*3-3)="-",-1,1)

將P2儲存格公式複製到P2~T5儲存格，結果如下圖所示。

由於「=MDETERM(P2:S5)」的值不等於「0」，如下圖所示，可知係數矩陣可逆。

框選一欄四個連續儲存格，本例為：Q11:Q14，輸入陣列公式：「=MMULT(MINVERSE (P2:S5),T2:T5)」後，再按Ctrl + Shift + Enter結束，即可在Q11:Q14得到方程組的解。

n  方法二：用克萊姆法則

對於上述四元一次方程組，框選P2:S5儲存格範圍，點取「常用 > 剪貼簿 > 複製」圖示，依本例共有4個未知數，故框選「P8:S11」、「P13:S17」、「P20:S23」、「P26:S29」四個範圍、點取「常用 > 剪貼簿 > 貼上下拉方塊 > 貼上連結」圖示，將其貼到4個不同的範圍。

框選T2:T5儲存格範圍，點取「常用 > 剪貼簿 > 複製」圖示，依本例共有4個欄位內容要更改成T2:T5內容，故框選「P8:P11」、「Q14:Q17」、「R20:R23」、「S26:S29」四個範圍、點取「常用 > 剪貼簿 > 貼上下拉方塊 > 貼上連結」圖示，將其貼到4個不同的範圍。

在P7、P13、P19及P25依次輸入A1、A2、A3、A4。

在Q7輸入公式：「MDETERM(P8:S11)」。

在Q13輸入公式：「MDETERM(P14:S17)」。

在Q19輸入公式：「MDETERM(P20:S23)」。

在Q25輸入公式：「MDETERM(P26:S27)」。

在W2~W4儲存格用MDETERM函數計算各矩陣的值，分別除以係數矩陣A的值，即可得到方程式組的解，如下圖所示。

n  方法三：用規劃求解

原理：規劃求解是Excel中的一種模擬分析工具，它通過調整變數儲存格的值來查找滿足所設定條件的最優值。

以上述四元一次方程組為例，在Excel 2016中的操作步驟如下。

點取「檔案 > 選項」指令，在「Excel選項」對話方塊，點取「增益集」項目。

點取「規劃求解增益集」項目，再點取「執行」鈕，如下圖所示。

在「增益集」對話方塊，點取「規劃求解增益集」核取方塊，使其打勾。

點取「規分析工具箱」核取方塊，使其打勾。

點取「確定」鈕，如下圖所示，回到工作表畫面。

本範例Q8:Q11為變數儲存格，規劃求解的結果將在該範圍產生。在S8:S11儲存格範圍設置公式，即以Q8:Q11為未知數代入方程左側。例如S8儲存格中的公式：「=P2*$Q$8+Q2*$Q$9+R2* $Q$10+S2*$Q$11」。

點取Q4儲存格輸入「20」。

點取「資料 > 分析 > 規劃求解」按鈕，彈出「規劃求解參數」對話方塊，進行如下設置：

點取「藉由變更變數儲存格」欄位，設定為「Q8:Q11」

點取「新增」鈕，彈出「新增限制式」對話方塊，點取「儲存格參照」欄位設置為S8儲存格，將運算符號下拉方塊選擇「=」，將「限制式」欄位為T2儲存格，如下圖所示，再點取「確定」鈕，這樣就添加了一個約束：S8=T2。

用同樣的方法添加其他幾個約束：S9=T3、S10=T4和S11=T5。

點取「將未設限的變數設為非負數」核取方塊，使其不打勾。

點取「選取求解方法」下拉方塊，選擇「單純LP」項目，如下圖所示。

點取「求解」按鈕，Excel將進行求解，彈出如下圖的「規劃求解結果」對話方塊。

點取「確定」鈕，即可在Q8:Q11儲存格得到方程式的解，如下圖所示。