使用規劃求解定義和解決問題

[規劃求解] 是可用於模擬分析的 Microsoft Excel 增益集程式。 [規劃求解] 可以用來尋找儲存格 (又稱為目標儲存格) 中公式的最佳 (最大或最小) 值，但必須遵守工作表上其他公式儲存格之值的限制式或限制。 [規劃求解] 運用一組儲存格 (稱為決策變數或直接稱為變數儲存格) 來計算目標儲存格與限制式儲存格中的公式。 [規劃求解] 會調整決策變數儲存格中的值，以符合限制式儲存格的限制並產生您期望的目標儲存格結果。

簡單來說，您可以透過變更其他儲存格，使用規劃求解來判斷一個儲存格的最大值或最小值。例如，您可以變更預計的廣告預算金額，並查看對預計利潤金額的影響。

Windows macOS 網頁版



附註: Excel 2007 之前版本的 [規劃求解] 將「標的儲存格」(Objective Cell) 稱為「目標儲存格」(Target Cell)，並將「決策變數儲存格」(Decision Variable Cell) 稱為「變數儲存格」(Changing Cell) 或「可調整儲存格」(Adjustable Cell)。我們針對 Excel 2010 的規劃求解載入宏做了許多改進，因此如果您使用的是 Excel 2007，您的體驗將會稍有不同。

在下列範例中，每一季中廣告層級會影響單位數目銷售量，間接決定銷售收益、相關支出和利潤。 [規劃求解] 可以變更每季的廣告預算 (決策變數儲存格 B5:C5)，總預算上限為 $20,000 (儲存格 F5)，直到利潤總和 (目標儲存格 F7) 到達可能的最大額。變數儲存格中的值是用來計算每一季的利潤，以便與公式目標儲存格 F7，=Sum (Q1 Profit:Q2 Profit) 相關。

使用 [規劃求解] 評估之前

1. 變數儲存格

2. 限制儲存格

3. 目標儲存格

執行 [規劃求解] 之後，求得的新值如下。

使用 [規劃求解] 評估之後

在 [資料] 索引標籤上，按一下 [分析] 群組中的 [規劃求解]。

附註: 如果無法使用 [規劃求解] 命令或 [分析] 群組，您必須啟動規劃求解增益集。請參閱：如何啟用規劃求解載宏。
在 [設定目標式] 方塊中，輸入目標儲存格的儲存格參照或名稱。目標儲存格必須包含公式。
請執行下列其中一項動作：
- 如果希望目標儲存格的值越大越好，請按一下 [最大值]。
- 如果希望目標儲存格的值越小越好，請按一下 [最小值]。
- 如果希望目標儲存格為特定的值，請按一下 [值]，然後在方塊中輸入值。
- 在 [藉由變更變數儲存格] 方塊中，輸入每個決策變數儲存格範圍的名稱或參照。使用逗號分隔不相鄰的參照。變數儲存格必須直接或間接與目標儲存格相關。您最多可以指定 200 個變數儲存格。
在 [設定限制式] 方塊中，執行下列動作以輸入要套用的任何限制式。
1. 在 [規劃求解參數] 對話方塊中，按一下 [新增]。
2. 在 [儲存格參照] 方塊中，輸入要限制值的儲存格參照或儲存格範圍名稱。
3. 單擊所參照單元格與限制式之間所要的關聯 (< =、=、 >=、int、bin 或 dif ) 。如果您按兩下 int，[限制式] 方塊中會出現整數。如果您按下 bin， 二進位 會出現在 [ 限制式] 方塊中。如果您按兩下 dif，[限制式] 方塊中會出現所有差異。
4. 如果您在 [限制式] 方塊中選擇 <=、= 或 >= 的關係，請輸入數字、儲存格參照或名稱，或是公式。
5. 請執行下列其中一項動作：
  - 如果要接受限制式，並新增另一個限制式，請按一下 [新增]。
  - 如果要接受限制式，並回到 [規劃求解參數] 對話方塊中，請按一下 [確定]。
    注意您只能在決策變數儲存格的限制式中套用 int、bin 及 dif 關聯。
    
    您可以執行下列動作以變更或刪除現有限制式：
6. 在 [規劃求解參數] 對話方塊中，按一下要變更或刪除的限制式。
7. 按一下 [變更] 然後再做變更，或是按一下 [刪除]。
按一下 [求解] 並執行下列其中一項動作：
- 若要保存工作表中求解的值，請按一下 [規劃求解結果] 對話方塊中的 [保留規劃求解解答]。
- 若要還原按一下 [求解] 之前的初值，請按一下 [還原初值]。
- 您可以按 Esc 來中斷求解程序。 Excel 會根據決策變數儲存格找到的結束值來重新計算工作表。
- 若要在 [規劃求解] 找到解答後根據解答建立報表，您可以在 [報表] 方塊中按一下報表類型，然後按一下 [確定]。報表會建立在活頁簿的新工作表上。如果 [規劃求解] 沒有找到解答，則只能使用特定報表，或無法使用任何報表。
- 若要將決策變數儲存格值儲存為分析藍本以供稍後顯示，請按一下 [規劃求解結果] 對話方塊中的 [儲存分析藍本]，然後在 [分析藍本名稱] 方塊中輸入分析藍本的名稱。

定義問題後，按一下 [規劃求解參數] 對話方塊中的 [選項]。
在 [選項] 對話方塊中，選取 [顯示反覆運算結果] 核取方塊，以檢視每一個試驗值結果的值，然後按一下 [確定]。
在 [規劃求解參數] 對話方塊中按一下 [求解]。
在 [顯示試驗值結果] 對話方塊中，執行下列其中一項：
- 若要停止求解程序，並顯示 [規劃求解結果] 對話方塊，請按一下 [停止]。
- 若要繼續求解程序，並顯示下一個試驗值結果，請按一下 [繼續]。

按一下 [規劃求解參數] 對話方塊中的 [選項]。
在對話方塊的 [所有方法]、[GRG Nonlinear] 及 [Evolutionary] 索引標籤上的任何選項中選擇或輸入值。

在 [規劃求解參數] 對話方塊中，按一下 [載入/儲存]。
輸入模式區的儲存格範圍，然後按一下 [儲存] 或 [載入]。

儲存模式時，請輸入您要放置問題模式之空白儲存格垂直範圍的第一個儲存格參照。當您載入模式時，請輸入包含問題模式之完整儲存格範圍的參照。

提示: 您可以儲存活頁簿，將 [ 規劃求解參數 ] 對話框中的最後一個選取專案與工作表一併儲存。活頁簿中的每個工作表可能都有自己的 [規劃求解] 選項，而且會儲存所有工作表。您也可以按下 [ 載入/儲存 ] 個別儲存問題，為工作表定義多個問題。

您可以在 [規劃求解參數] 對話方塊中，選擇下列三種演算法或求解方法之一：

一般化縮減梯度 (Generalized Reduced Gradient，GRG) 非線性 用於平滑非線性的問題。
LP 單形法 (LP Simplex) 用於線性的問題。
進化 (Evolutionary) 用於非平滑的問題。

重要: 您應該先啟用規劃求解載宏。如需詳細資訊，請參閱載入規劃求解載入宏。

在下列範例中，每一季中廣告層級會影響單位數目銷售量，間接決定銷售收益、相關支出和利潤。規劃求解可以變更廣告的季度預算 (決策可變單元格 B5：C5) ，總預算限制式為 $20,000 (單元格 D5) ，直到目標單元格 D7 () 的總利潤達到可能的最大金額為止。可變單元格中的值是用來計算每一季的利潤，因此它們與公式目標單元格 D7、=SUM (Q1 Profit：Q2 Profit) 相關。

「範例規劃求解」評估

圖說文字 1 可變單元格

圖說文字 2 受限制的儲存格

圖說文字 3 目標儲存格

執行 [規劃求解] 之後，求得的新值如下。

有新值的「範例規劃求解」評估

在 Mac 版 Excel 2016 中：按兩下 [規劃求解 > 數據]。

在 Mac 版 Excel 2011 中：按兩下 [數據] 索引標籤，在 [分析] 底下，按兩下 [規劃求解]。
在 [設定目標] 中，輸入目標儲存格的儲存格參照或名稱。

附註: 目標儲存格必須包含公式。

請執行下列其中一項動作：

若要	執行此動作
盡可能讓目標儲存格的值變大	按兩下 [最大值]。
盡可能小化目標儲存格的值	按兩下 [最小分鐘]。
將目標儲存格設定為特定值	按兩下 [值]，然後在方塊中輸入值。

在 [藉由變更變數儲存格] 方塊中，輸入每個決策變數儲存格範圍的名稱或參照。以逗點來分隔不相鄰的參照。

變數儲存格必須直接或間接與目標儲存格相關。您最多可以指定 200 個變數儲存格。

在 [ 受限於限制式] 方塊 中，新增您要套用的任何限制式。

若要新增限制式，請遵循下列步驟：

在 [規劃求解參數] 對話方塊中，按一下 [新增]。
在 [儲存格參照] 方塊中，輸入要限制值的儲存格參照或儲存格範圍名稱。
在 [<= 關聯] 快捷功能表上，選取所參照單元格與限制式之間的關聯。如果您選擇 =、=或 >=<，請在 [限制式] 方塊中輸入數位、單元格參照或名稱或公式。

附註: 您只能在決策可變單元格的限制式中套用 int、bin 和 dif 關聯。
執行下列其中一個動作：

若要	執行此動作
接受限制式並新增另一個限制式	按一下 [新增]。
接受限制式並返回 [ 規劃求解參數] 對話框	按一下 [確定]。

按兩下 [求解]，然後執行下列其中一項操作：

若要	執行此動作
將解決方案值保留在工作表上	按兩下 [規劃求解結果] 對話框中的 [保留規劃求解解決方案]。
還原原始數據	按兩下 [還原原始值]。

附註:

若要中斷解決方案程式，請按 ESC 鍵。 Excel 會使用可調整儲存格的上一個值重新計算工作表。
若要在 [規劃求解] 找到解答後根據解答建立報表，您可以在 [報表] 方塊中按一下報表類型，然後按一下 [確定]。報表會建立在活頁簿中的新工作表上。如果 [規劃求解] 找不到解決方案，則無法使用建立報表的選項。
若要將調整儲存儲存為日後可顯示的分析藍本 ，請按 兩下 [ 規劃求解結果 ] 對話方塊中的 [儲存分析藍本]，然後在 [分析藍本名稱] 方塊中輸入分析藍本 的名稱 。

在 Mac 版 Excel 2016 中：按兩下 [規劃求解 > 數據]。

在 Mac 版 Excel 2011 中：按兩下 [數據] 索引標籤，在 [分析] 底下，按兩下 [規劃求解]。
定義問題之後，請在 [ 規劃求解參數] 對話框中，按兩下 [ 選項]。
選取 [ 顯示反覆運算結果 ] 複選框以查看每個試用解決方案的值，然後按兩下 [ 確定]。
在 [規劃求解參數] 對話方塊中按一下 [求解]。

在 [ 顯示試用解決方案 ] 對話框中，執行下列其中一項操作：

若要	執行此動作
停止解決方案程式，並顯示 [ 規劃求解結果 ] 對話方塊	按兩下 [停止]。
繼續解決方案程式並顯示下一個試用解決方案	按一下 [繼續]。

在 Mac 版 Excel 2016 中：按兩下 [規劃求解 > 數據]。

在 Mac 版 Excel 2011 中：按兩下 [數據] 索引標籤，在 [分析] 底下，按兩下 [規劃求解]。

按兩下 [選項]，然後在 [ 選項 ] 或 [ 規劃求解選項 ] 對話框中，選擇下列一或多個選項：

若要	執行此動作
設定解決方案時間和反覆運算	在 [ 所有方法] 索引標籤的 [ 求解限制] 底下，於 [ 最大時間 (秒) ] 方塊中，輸入您要允許的解決時間秒數。然後在 [ 反覆運算] 方塊中，輸入您要允許的反覆運算數上限。附註: 如果解決方案程式達到規劃求解找到解決方案之前的最大時間或反覆運算次數，規劃求解會顯示 [ 顯示試用解決方案 ] 對話方塊。
設定精確度	在 [ 所有方法] 索引標籤的 [ 限制式精確度] 方塊中，輸入所要的精確度。數位越小，精確度越高。
設定合併程度	在 [GRG 非線性] 或 [進化] 索引卷標的 [合併匯算] 方塊中，輸入您想要在最後五次反覆運算中允許的相對變更量，然後再使用解決方案停止規劃求解。數位越小，允許的相對變更就越少。

按一下 [確定]。
在 [ 規劃求解參數] 對話框中，按兩下 [ 求解 ] 或 [ 關閉]。

在 Mac 版 Excel 2016 中：按兩下 [規劃求解 > 數據]。

在 Mac 版 Excel 2011 中：按兩下 [數據] 索引標籤，在 [分析] 底下，按兩下 [規劃求解]。
單擊 [載入/儲存]，輸入模型區域的單元格範圍，然後按 兩下 [儲存 ] 或 [ 載入]。

儲存模式時，請輸入您要放置問題模式之空白儲存格垂直範圍的第一個儲存格參照。當您載入模式時，請輸入包含問題模式之完整儲存格範圍的參照。

提示: 您可以儲存活頁簿，將 [ 規劃求解參數 ] 對話框中的最後一個選取專案儲存為工作表。活頁簿中的每個工作表可能都有自己的 [規劃求解] 選項，而且會儲存所有工作表。您也可以按下 [ 載入/儲存 ] 個別儲存問題，為工作表定義多個問題。

在 Mac 版 Excel 2016 中：按兩下 [規劃求解 > 數據]。

在 Mac 版 Excel 2011 中：按兩下 [數據] 索引標籤，在 [分析] 底下，按兩下 [規劃求解]。
在 [ 選取求解方法 ] 快捷功能表上，選取下列其中一項：

求解方法	描述
GRG (一般化降低漸層) 非線性	使用 IF、CHOOSE、LOOKUP 和其他「步驟」函數以外的大部分 Excel 函數的模型預設選項。
Simplex LP	使用此方法解決線性程序設計問題。您的模型應該在相依於可變單元格的公式中使用SUM、SUMPRODUCT、+ 和 *。
進化 (Evolutionary)	當您的模型使用 IF、CHOOSE 或 LOOKUP 搭配相依於可變單元格的自變數時，此方法是根據演算法的最佳方法。