使用Excel Solver進行最佳化

求解器 (Solver) 是一個Microsoft Excel載入項程式，可用於假設分析中的最佳化。

根據O'Brien和Marakas的說法，最佳化分析是目標求解分析的一個更復雜的擴充套件。它不是為變數設定特定的目標值，而是要在某些約束條件下找到一個或多個目標變數的最佳值。然後，在滿足指定約束條件的情況下，重複更改一個或多個其他變數，直到找到目標變數的最佳值。

在Excel中，您可以使用求解器 (Solver) 為一個稱為目標單元格的單元格中的公式找到最優值（最大值或最小值，或某個特定值），該單元格受工作表上其他公式單元格的值的某些約束或限制。

這意味著求解器與一組稱為決策變數的單元格一起工作，這些單元格用於計算目標單元格和約束單元格中的公式。求解器調整決策變數單元格中的值以滿足約束單元格的限制併產生目標單元格所需的計算結果。

您可以使用求解器來找到各種問題的最優解，例如：

確定製藥廠的每月產品組合，以最大化盈利能力。
安排組織中的員工。
解決運輸問題。
財務計劃和預算。

啟用求解器載入項

在使用求解器解決問題之前，請確保已在Excel中啟用求解器載入項，方法如下：

單擊功能區上的“資料”選項卡。求解器命令應顯示在“分析”組中，如下所示。

如果您找不到求解器命令，請按以下步驟啟用它：

單擊“檔案”選項卡。
在左側窗格中單擊“選項”。將出現“Excel選項”對話方塊。
在左側窗格中單擊“載入項”。
在“管理”框中選擇“Excel載入項”，然後單擊“轉到”。

將出現“載入項”對話方塊。選中求解器載入項，然後單擊“確定”。現在，您應該能夠在功能區上的“資料”選項卡下找到求解器命令。

求解器使用的求解方法

根據問題的型別，您可以從Excel求解器支援的以下三種求解方法中選擇一種：

線性規劃單純形法 (LP Simplex)

用於線性問題。在以下情況下，求解器模型是線性的：

目標單元格是透過將（可更改單元格）*（常數）形式的項加在一起計算的。
每個約束都滿足線性模型要求。這意味著每個約束都是透過將（可更改單元格）*（常數）形式的項加在一起並將其與常數進行比較來計算的。

廣義約簡梯度 (GRG) 非線性

用於平滑的非線性問題。如果目標單元格、任何約束或兩者都包含對不是（可更改單元格）*（常數）形式的可更改單元格的引用，則您具有非線性模型。

進化演算法 (Evolutionary)

瞭解求解器評估

求解器需要以下引數：

決策變數單元格
約束單元格
目標單元格
求解方法

求解器評估基於以下內容：

決策變數單元格中的值受約束單元格中的值限制。
目標單元格中值的計算包括決策變數單元格中的值。
求解器使用所選的求解方法得出目標單元格中的最優值。

定義問題

假設您正在分析一家生產和銷售某產品的公司的利潤。您需要確定未來兩個季度在廣告上可以花費的金額，前提是最高為20,000。每個季度的廣告水平會影響以下方面：

銷售數量，間接決定銷售收入。
相關費用，以及
利潤。

您可以繼續將問題定義為：

找到單位成本。
找到每單位廣告成本。
找到單位價格。

接下來，設定如下所示的所需計算的單元格。

如您所見，計算結果針對的是正在考慮的第1季度和第2季度，包括：

第1季度的可售單位數為400，第2季度的可售單位數為600（單元格 - C7和D7）。
廣告預算的初始值為每個季度10000（單元格 - C8和D8）。
銷售數量取決於每單位廣告成本，因此等於該季度的預算/每單位廣告成本。請注意，我們使用了MIN函式來確保銷售數量<=可售單位數。（單元格 - C9和D9）。
收入計算為單位價格*銷售數量（單元格 - C10和D10）。
費用計算為單位成本*可售單位數+該季度的廣告成本（單元格 - C11和D12）。
利潤等於收入-費用（單元格C12和D12）。
總利潤等於第1季度的利潤+第2季度的利潤（單元格 - D3）。

接下來，您可以如下設定求解器的引數：

如您所見，求解器的引數為：

目標單元格是D3，其中包含您要最大化的總利潤。
決策變數單元格是C8和D8，其中包含兩個季度（第1季度和第2季度）的預算。
有三個約束單元格 - C14、C15和C16。
- 包含總預算的單元格C14設定為20000的約束（單元格D14）。
- 包含第1季度銷售數量的單元格C15設定為<=第1季度可售單位數的約束（單元格D15）。
- 包含第2季度銷售數量的單元格C16設定為<=第2季度可售單位數的約束（單元格D16）。

解決問題

下一步是使用求解器找到解決方案，方法如下：

步驟1 - 轉到功能區上的“資料”>“分析”>“求解器”。將出現“求解引數”對話方塊。

步驟2 - 在“設定目標單元格”框中，選擇單元格D3。

步驟3 - 選擇“最大化”。

步驟4 - 在“透過更改可變單元格”框中選擇範圍C8:D8。

步驟5 - 接下來，單擊“新增”按鈕以新增您已識別的三個約束。

步驟6 - 將出現“新增約束”對話方塊。如下設定總預算的約束，然後單擊“新增”。

步驟7 - 如下設定第1季度總銷售數量的約束，然後單擊“新增”。

步驟8 - 如下設定第2季度總銷售數量的約束，然後單擊“確定”。

“求解引數”對話框出現，其中在“受約束條件”框中添加了三個約束。

步驟9 - 在“選擇求解方法”框中，選擇“線性規劃單純形法”。

步驟10 - 單擊“求解”按鈕。將出現“求解結果”對話方塊。選擇“保持求解器解”，然後單擊“確定”。

結果將顯示在您的工作表中。

如您所見，在給定約束條件下產生最大總利潤的最優解如下：

總利潤 - 30000。
第1季度廣告預算 - 8000。
第2季度廣告預算 - 12000。

逐步瀏覽求解器試用解

您可以逐步瀏覽求解器試用解，檢視迭代結果。

步驟1 - 在“求解引數”對話方塊中單擊“選項”按鈕。

將出現“選項”對話方塊。

步驟2 - 選中“顯示迭代結果”框，然後單擊“確定”。

步驟3 - 將出現“求解引數”對話方塊。單擊“求解”。

步驟4 - 將出現“顯示試用解”對話方塊，顯示訊息 - “求解器已暫停，當前解值顯示在工作表上”。

如您所見，當前迭代值顯示在您的工作單元格中。您可以停止求解器接受當前結果，也可以繼續使用求解器在後續步驟中查詢解。

步驟5 - 單擊“繼續”。

在每個步驟中都會出現“顯示試用解”對話方塊，最後在找到最優解後，將出現“求解結果”對話方塊。您的工作表在每個步驟中都會更新，最後顯示結果值。

儲存求解器選擇

對於使用求解器解決的問題，您有以下儲存選項：

您可以透過儲存工作簿將“求解引數”對話方塊中的上次選擇與工作表一起儲存。
工作簿中的每個工作表都可以有自己的求解器選擇，並且在儲存工作簿時，所有這些選擇都將被儲存。
您還可以在一個工作表中定義多個問題，每個問題都有自己的求解器選擇。在這種情況下，您可以使用“求解引數”對話方塊中的“載入/儲存”分別載入和儲存問題。
- 單擊“載入/儲存”按鈕。將出現“載入/儲存”對話方塊。
- 要儲存問題模型，請輸入要放置問題模型的垂直空單元格範圍的第一個單元格的引用。單擊“儲存”。

問題模型（求解器引數集）從您指定為參考的單元格開始顯示。

要載入問題模型，請輸入包含問題模型的整個單元格範圍的引用。然後，單擊“載入”按鈕。

列印頁面