Excel 求解器：優化結果、添加約束和將解決方案保存為方案

您可以使用“規劃求解參數”對話框設置Excel 規劃求解模型。您使用“設置目標”框指定目標單元格，並使用“至”組告訴 Excel Solver 您希望從目標單元格獲得什麼：最大可能值；可能的最小值；或特定值。最後，您使用通過更改變量單元格框指定規劃求解可用於插入值以優化結果的單元格。

優化 Excel 求解器結果

當規劃求解找到解決方案時，您可以選擇保留規劃求解或恢復原始值。如果選擇保留求解器解決方案，Excel 將永久更改工作表。您無法撤消更改。

準備好您的求解器就緒工作表模型後，以下是使用求解器為您的模型找到最佳結果的步驟：

選擇數據 → 求解器。
Excel 打開求解器參數對話框。

在設置目標框中，輸入模型目標單元格的地址。
請注意，如果單擊單元格進行輸入，Solver 會自動輸入絕對單元格地址（例如，$B$14 而不是 B14）。無論哪種方式，求解器都可以正常工作。

在收件人組中，選擇一個選項：

• Max：返回可能的最大值。
• Min：返回可能的最小值。
• 值：輸入一個數字以將目標單元格設置為該數字。

  對於示例模型，已選擇 Value Of 並在文本框中輸入0。

在通過更改變量單元格框中，輸入您希望規劃求解在查找解決方案時更改的單元格地址。
在示例中，變化的單元格是 B4 和 C4。下圖顯示了完成的求解器參數對話框。

完成的求解器參數對話框。

單擊解決。
求解器進入正題。在求解器處理問題時，您可能會看到“顯示試驗解決方案”對話框出現一次或多次。

在出現的任何“顯示試用解決方案”對話框中，單擊“繼續”以繼續進行。
優化完成後，Excel 將顯示求解器結果對話框。

“求解器結果”對話框和盈虧平衡問題的解決方案。

選擇保留求解器解決方案選項。
如果您不想接受結果，請改為選擇“恢復原始值”選項。

單擊確定。

您可以要求規劃求解顯示一個或多個報告，為您提供有關結果的額外信息。在求解器結果對話框中，使用報告列表選擇要查看的每個報告：

• 答案：顯示有​​關模型的目標單元格、變量單元格和約束的信息。對於目標單元格和變量單元格，Solver 顯示原始值和最終值。
• 敏感性：嘗試顯示解決方案對模型公式變化的敏感程度。靈敏度報告的佈局取決於您使用的模型類型。
• 限制：顯示目標單元格及其值，以及變量單元格及其地址、名稱和值。

Excel 求解器可以使用多種求解方法之一。在求解器參數對話框中，使用選擇求解方法列表選擇以下選項之一：

• Simplex LP：如果您的工作表模型是線性的，請使用。用最簡單的術語來說，線性模型是其中變量沒有被提升到任何冪並且沒有使用任何所謂的超越函數（例如 SIN 和 COS）的模型。
• GRG 非線性：如果您的工作表模型是非線性和平滑的，請使用。一般而言，平滑模型是這樣一種模型，其中所用方程的圖形不顯示銳利的邊緣或中斷。
• 進化：如果您的工作表模型是非線性和不平滑的，請使用。

你需要擔心這些嗎？幾乎可以肯定不是。Excel Solver 默認使用 GRG Nonlinear，這幾乎適用於您使用 Solver 執行的任何操作。

向 Excel 求解器添加約束

現實世界對公式施加了限制和條件。一家工廠的最大產能可能是每天 10,000 件，公司的員工人數不能為負數，您的廣告費用可能會限制在總費用的 10%。

同樣，假設您正在對兩種產品進行盈虧平衡分析。如果您在沒有任何限制的情況下運行優化，Solver 可能會通過將一種產品設置為輕微虧損而另一種產品設置為微盈利來達到總利潤為 0，其中虧損和利潤相互抵消。事實上，如果你仔細看看上一張圖片，這正是 Solver 所做的。要獲得真正的收支平衡解決方案，您可能更願意將兩個產品利潤值都視為 0。

此類限制和條件是 Solver 稱為約束的示例。添加約束告訴規劃求解找到一個解決方案，以便不違反這些條件。

以下是在優化中添加約束的情況下運行求解器的方法：

選擇數據 → 求解器。
Excel 打開求解器參數對話框。

使用“設置目標”框、“至”組和“通過更改變量單元格”框來設置求解器，如上所述。

單擊添加。
Excel 顯示添加約束對話框。

在“單元格引用”框中，輸入要約束的單元格的地址。
您可以鍵入地址或選擇工作表上的單元格。

在下拉列表中，選擇要使用的運算符。
大多數情況下，您使用比較運算符，例如等於 (=) 或大於 (>)。當您需要約束（例如員工總數）為整數值而不是實數（即帶有小數部分的數字；您不能有 10.5 名員工！）時，請使用 int（整數）運算符。當您的約束必須為 TRUE 或 FALSE（或 1 或 0）時，請使用 bin（二進制）運算符。

如果您在步驟 5 中選擇了比較運算符，請在約束框中輸入要限制單元格的值。
此圖顯示了一個完整的“添加約束”對話框的示例。在示例模型中，此約束告訴求解器找到一個解決方案，使得充氣飛鏢板（單元格 B12）的產品利潤等於 0。

完成的添加約束對話框。

要指定更多約束，請單擊添加並根據需要重複步驟 4 到 6。

例如，您添加了一個約束，要求 Dog Polisher 產品利潤（單元格 C12）為 0。

單擊確定。

Excel 返回到求解器參數對話框並在約束條件列錶框中顯示您的約束條件。

單擊解決。

在出現的任何“顯示試用解決方案”對話框中，單擊“繼續”以繼續進行。
下圖顯示了添加了約束的收支平衡解決方案示例。請注意，不僅總利潤單元格 (B14) 設置為 0，兩個產品利潤單元格（B12 和 C12）也設置為 0。

“求解器結果”對話框和收支平衡問題的最終解決方案。

選擇保留求解器解決方案選項。
如果您不想接受結果，請改為選擇“恢復原始值”選項。

單擊確定。

您最多可以添加 100 個約束。此外，如果您需要在開始求解之前更改約束，請在約束約束列錶框中選擇約束，單擊更改，然後在出現的更改約束對話框中進行調整。如果要刪除不再需要的約束，請選擇該約束，然後單擊刪除。

將 Excel 求解器解決方案另存為場景

每當您擁有使用一組連貫輸入值（稱為更改單元格）的電子表格模型時，您就擁有了 Excel 所謂的場景。使用 Solver，這些不斷變化的單元格是它的可變單元格，因此 Solver 解決方案相當於Excel 中的一種場景。但是，Solver 並沒有為您提供保存和重新運行特定解決方案的簡單方法。要解決此問題，您可以將解決方案另存為方案，然後您可以使用 Excel 的方案管理器功能調用該方案。

按照以下步驟將求解器解決方案保存為場景：

選擇數據 → 求解器。
Excel 打開求解器參數對話框。

使用 Set Objective 框、To 組、By Change Variable Cells 框和 Subject to the Constraints 列表來設置求解器，如上所述。

單擊解決。

只要出現“顯示試用解決方案”對話框，請選擇“繼續”。
優化完成後，Excel 將顯示求解器結果對話框。

單擊保存場景。
Excel 將顯示“保存方案”對話框。

在“場景名稱”對話框中，鍵入場景的名稱，然後單擊“確定”。
Excel 將返回到求解器結果對話框。

選擇保留求解器解決方案選項。
如果您不想接受結果，請改為選擇“恢復原始值”選項。

單擊確定。