如何使用 Excel 2019 求解器

儘管 Excel 的數據表和目標查找命令適用於需要確定公式中輸入和結果之間直接關係的簡單問題，但在處理更複雜的問題時，您需要使用規劃求解加載項。例如，當您需要更改 Excel 模型中的多個輸入值並且需要對這些值和/或輸出值施加約束時，使用規劃求解找到最佳解決方案。

Solver 插件通過應用迭代方法來找到給定輸入、所需解決方案和您施加的約束的“最佳”解決方案。每次迭代時，程序都會應用試錯法（基於線性或非線性方程和不等式的使用），試圖接近最優解。

使用求解器加載項時，請記住許多問題，尤其是更複雜的問題，有很多解決方案。儘管求解器會返回最優解，考慮到起始值、可以更改的變量以及您定義的約束，但該解通常不是唯一可能的，事實上，它可能不是最適合您的解。為確保找到最佳解決方案，您可能需要多次運行求解器，每次解決問題時調整初始值。

在 Excel 工作表中為規劃求解加載項設置問題時，請定義以下項目：

• 目標單元格：工作表中的目標單元格，其值將被最大化、最小化或達到特定值。請注意，此單元格必須包含公式。
• 可變單元格：工作表中不斷變化的單元格，其值將被調整，直到找到答案。
• 約束單元格：包含您對變量單元格和/或目標單元格中的目標單元格中的變化值施加的限制的單元格。

使用這些參數定義問題並讓規劃求解插件解決問題後，程序會通過修改工作表中的值來返回最佳解決方案。此時，您可以選擇保留工作表中的更改或將原始值恢復到工作表中。您還可以將解決方案保存為場景，以便稍後在恢復原始值之前查看。

您可以將求解器插件與場景管理器一起使用來幫助設置要解決的問題或保存解決方案，以便您可以在以後查看它。當您選擇此命令時，您為場景管理器定義的變化單元會被規劃求解自動選取和使用，反之亦然。此外，您可以將求解器對問題的解決方案保存為方案（通過單擊求解器對話框中的保存方案按鈕），然後您可以使用方案管理器查看該方案。

在 Excel 2019 中設置和定義問題

設置求解器要處理的問題的第一步是創建工作表模型，您將為其定義目標單元格、變量單元格和約束單元格。

請記住，求解器是一個附加實用程序。這意味著，在您可以使用它之前，您需要確保規劃求解插件程序仍處於加載狀態，如功能區數據選項卡末尾分析組中規劃求解按鈕的外觀所示。如果缺少此按鈕，您可以通過打開 Excel 選項對話框 (Alt+FTAA) 的加載項選項卡，然後在確保 Excel 加載項顯示在管理下拉列表中後單擊執行按鈕來加載規劃求解列錶框緊鄰其左側。然後，在“加載項”對話框中選中“求解器加載項”複選框以在其中放置複選標記，然後單擊“確定”關閉對話框並重新加載加載項。

要在加載加載項並創建工作表模型後使用規劃求解加載項定義和解決問題，請執行以下步驟：

單擊功能區數據選項卡末尾分析組中的求解器命令按鈕。

Excel 打開求解器參數對話框。

在求解器參數對話框中指定要應用於模型的參數。

單擊工作表中的目標單元格或在“設置目標”文本框中輸入其單元格引用或範圍名稱。

接下來，您需要選擇 To 設置。當您希望目標單元格的值盡可能大時，單擊“最大”選項按鈕。如果您希望目標單元格的值盡可能小，請單擊“最小值”選項按鈕。當您希望目標單元格的值達到特定值時，單擊“值”選項按鈕，然後在關聯的文本框中輸入一個值。

單擊對話框的“收件人”部分中的相應選項按鈕選項。如果選擇 Value Of 選項​​按鈕，請在關聯的文本框中輸入要匹配的值。

接下來，指定變量單元格 - 即求解器可以更改以達到您的等於目標的單元格。

單擊通過更改變量單元格文本框，然後在工作表中選擇要更改的單元格或在文本框中輸入其單元格引用或區域名稱。

請記住，要選擇工作表中不相鄰的單元格，您需要在單擊選擇中的每個單元格時按住 Ctrl 鍵。要讓 Excel 根據您選擇的目標單元格為您選擇更改的單元格，請單擊此文本框右側的“猜測”按鈕。

在讓規劃求解調整您的模型之前，您可以為目標單元格或任何在調整值時確定其限制的變化單元格添加約束。

（可選）單擊“求解器參數”對話框中“約束對象”列錶框右側的“添加”按鈕。

此操作將打開“添加約束”對話框。定義約束時，選擇要約束其值的單元格或選擇工作表中的單元格或在單元格引用文本框中輸入其單元格引用。然後從右側的下拉列錶框中選擇關係（=、<=、>= 或int表示整數或bin表示二進制），並（除非您選擇int或bin）在約束文本框。

要繼續為求解器使用的其他單元格添加約束，請單擊添加按鈕添加約束並清除添加約束對話框中的文本框。然後，重複步驟 5 以添加新約束。為目標單元定義約束並更改模型中的值後，單擊確定關閉添加約束對話框並返回求解器參數對話框（現在在約束條件列錶框中列出您的約束）。

（可選）如果要在變量單元格不受約束時允許負值，請取消選中使不受約束的變量變為非負值複選框。
默認情況下，求解器插件使用 GRG（廣義縮減梯度）非線性方法來求解您正在設置其參數的模型，這是解決平滑非線性問題的一種非常有效的方法。要使用 LP 單純形方法（用於遵循單純形算法的線性規劃）或進化引擎來解決非光滑問題，您需要執行步驟 7。

（可選）從“選擇求解方法”下拉列表中選擇 LP Simplex 或 Evolutionary 以使用這些方法之一解決非光滑問題。

單擊求解按鈕讓求解器按照您在求解器參數對話框中定義的方式求解問題。

用 Excel 的 Solver 解決問題

單擊“求解”按鈕時，“求解器參數”對話框消失，狀態欄指示求解器正在設置問題，然後通過顯示中間（或試驗）編號來通知您解決問題的進度嘗試的解決方案。若要在 Excel 計算最後一次迭代之前隨時中斷求解過程，請按 Esc 鍵。Excel 然後顯示“顯示試用解決方案”對話框，通知您解決方案過程已暫停。要繼續求解過程，請單擊“繼續”按鈕。要中止求解過程，請單擊停止按鈕。

當 Excel 完成求解過程時，將出現求解器結果對話框。此對話框會通知您求解器是否能夠在給定目標單元格、更改單元格和為問題定義的約束條件下找到解決方案。要保留求解器在工作表模型中所做的更改，請選中保留求解器解決方案選項按鈕，然後單擊確定關閉求解器結果對話框。要將原始值返回到工作表，請改為單擊“恢復原始值”選項按鈕。要在恢復原始值之前將更改保存為方案，請單擊“保存方案”按鈕並在單擊“恢復原始值”選項和“確定”按鈕之前為當前方案指定一個名稱。

“規劃求解結果”對話框顯示規劃求解找到了問題的解決方案。

與使用 Goal Seek 命令不同，在單擊 Solver Results 對話框中的 Keep Solver Solution 選項按鈕後，您無法使用快速訪問工具欄上的 Undo 命令按鈕將原始值恢復到工作表中。如果您希望能夠在工作表的“之前”和“之後”視圖之間切換，您必須使用“保存場景”按鈕保存更改，然後選擇“恢復原始值”選項按鈕。這樣，您可以保留原始工作表中的“之前”視圖，並使用場景管理器顯示求解器創建的“之後”視圖。

更改 Excel 的求解器選項

對於大多數問題，求解器使用的默認選項就足夠了。但是，在某些情況下，您可能希望在開始求解過程之前更改某些求解器選項。要更改求解選項，請單擊求解器參數對話框中的選項按鈕。Excel 然後打開選項對話框，選擇所有方法選項卡，您可以在其中進行所有必要的更改。

修改選項對話框中的解決方案選項。

Excel 2019 求解器選項設置

更改選項後，單擊確定返回求解器參數對話框；從這裡，您可以單擊“求解”按鈕以使用您剛剛更改的新解決方案設置開始求解過程。

當您使用默認的 GRG（廣義縮減梯度）非線性或演化方法時，您可以使用選項對話框的 GRG 非線性和演化選項卡上的選項設置其他求解器設置。這些選項包括更改這些特定方法之一的收斂、種群大小和隨機種子設置。

在 Excel 2019 中保存和加載模型問題

當您單擊快速訪問工具欄 (Ctrl+S) 上的保存按鈕時，您最近使用的目標單元格、變量單元格、約束單元格和求解器選項將保存為 Excel 工作表的一部分。當您為要保存的同一工作表定義其他問題時，您必須單擊“求解器選項”對話框中的“保存模型”按鈕，並在活動工作表中指明您希望問題參數所在的單元格引用或範圍名稱插入。

單擊“加載/保存”按鈕時，Excel 將打開“加載/保存模型”對話框，其中包含“選擇模型區域”文本框。此文本框包含一個區域的單元格引用，該區域大到足以容納所有問題的參數，從活動單元格開始。要將問題的參數保存在此範圍內，請單擊“確定”。如果此範圍包括具有現有數據的單元格，則需要在單擊“確定”之前修改此文本框中的單元格引用，以防止 Excel 替換現有數據。

單擊“確定”後，Excel 將在指定範圍內復制問題的參數。下次保存工作簿時，這些值將作為工作表的一部分保存。要在解決問題時重複使用這些問題參數，您只需打開求解器選項對話框，單擊加載/保存按鈕打開加載/保存模型對話框，單擊加載按鈕，然後選擇包含保存的範圍問題參數。當您在“加載模型”對話框中單擊“確定”時，Excel 會將此單元格範圍內的參數加載到“求解器參數”對話框中的相應文本框中。然後您可以通過單擊確定關閉求解器選項對話框，您可以通過單擊求解命令按鈕使用這些參數來求解問題。

請記住，只要您想清除為上一個問題定義的所有參數並將求解器選項恢復為默認值，就可以使用“全部重置”按鈕。

在 Excel 2019 中創建求解器報告

您可以使用規劃求解創建三種不同類型的報告：

• 答案報告：列出目標單元格和更改單元格的原始值和最終值，以及用於解決問題的約束。
• 敏感度報告：指示最佳解決方案對計算目標單元格和約束的公式更改的敏感度。該報告顯示了變化的單元格及其最終值和每個單元格的降低梯度。（減小的梯度測量變化單元格中每單位增加的目標。）如果您定義了約束，靈敏度報告將列出它們及其最終值和每個約束的拉格朗日乘數。（拉格朗日乘數衡量出現在約束方程右側的每單位增加的目標。）
• 限制報告：顯示目標單元格和變化的單元格及其值、下限和上限以及目標結果。下限表示在固定所有其他單元格的值並仍滿足約束條件的情況下，更改單元格可以具有的最低值。上限表示將執行此操作的最高值。

Excel 將您為求解器問題生成的每個報告放在工作簿中的單獨工作表中。要生成這些報告中的一個（或全部），請從求解器結果對話框的報告列錶框中選擇報告類型（答案、靈敏度或限制）。要選擇多個報告，只需單擊報告名稱。

單擊“確定”關閉“求解器結果”對話框（在“保留求解器解決方案”和“恢復原始值”選項之間進行選擇後）時，Excel 會生成您在新工作表中選擇的報告（或多個報告），並將其添加到工作簿的開頭. （報告表選項卡按報告類型命名，如Answer Report 1、Sensitivity Report 1和Limits Report 1。）