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

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

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

Windows macOS Web



附註: 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]， alldifferent會出現在 [限制] 方塊中。
4. 如果您在 [限制式] 方塊中選擇 <=、= 或 >= 的關係，請輸入數字、儲存格參照或名稱，或是公式。
5. 請執行下列其中一項動作：
  - 如果要接受限制式，並新增另一個限制式，請按一下 [新增]。
  - 如果要接受限制式，並回到 [規劃求解參數] 對話方塊中，請按一下 [確定]。
    注意您只能在決策變數儲存格的限制式中套用 int、bin 及 dif 關聯。
    
    您可以執行下列動作以變更或刪除現有限制式：
6. 在 [規劃求解參數] 對話方塊中，按一下要變更或刪除的限制式。
7. 按一下 [變更] 然後再做變更，或是按一下 [刪除]。
按一下 [求解] 並執行下列其中一項動作：
- 若要保存工作表中求解的值，請按一下 [規劃求解結果] 對話方塊中的 [保留規劃求解解答]。
- 若要還原按一下 [求解] 之前的初值，請按一下 [還原初值]。
- 您可以按 Esc 來中斷求解程序。 Excel 會根據決策變數儲存格找到的結束值來重新計算工作表。
- 若要在 [規劃求解] 找到解答後根據解答建立報表，您可以在 [報表] 方塊中按一下報表類型，然後按一下 [確定]。報表會建立在活頁簿的新工作表上。如果 [規劃求解] 沒有找到解答，則只能使用特定報表，或無法使用任何報表。
- 若要將決策變數儲存格值儲存為分析藍本以供稍後顯示，請按一下 [規劃求解結果] 對話方塊中的 [儲存分析藍本]，然後在 [分析藍本名稱] 方塊中輸入分析藍本的名稱。

逐步執行規劃求解試驗值結果

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

變更規劃求解尋找解決方案的方式

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

儲存或載入問題模式

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

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

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

規劃求解所使用的求解方法

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

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

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

規劃求解評估範例

在下列範例中，每一季中廣告層級會影響單位數目銷售量，間接決定銷售收益、相關支出和利潤。規劃求解可以變更廣告的季度預算（在 $20000 （儲存格 D5）中，最多為總預算限制（儲存格 D5），直到總利潤（客觀蜂窩）達到可能的最大值。變數儲存格中的值是用來計算每個季度的利潤，因此它們與公式目標儲存格（即 SUM （Q1 利潤：第2季度利潤）有關。

「範例規劃求解」評估

圖說文字 1 變數儲存格

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

圖說文字 3 目標儲存格

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

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

定義及求解問題

在 Mac 版 Excel 2016 中：按一下 [資料>規劃求解]。

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

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

執行下列其中一項操作：

若要	執行動作
使目標儲存格的值盡可能大	按一下 [最大值]。
盡可能縮小目標儲存格的值	按一下 [最小值]。
將目標儲存格設定為特定值	按一下 [值]，然後在方塊中輸入值。

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

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

在 [服從限制] 方塊中，新增任何您想要套用的限制。

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

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

附註: 您只能在決策變數儲存格的限制式中套用 int、bin 及 dif 關聯。
執行下列其中一項操作：

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

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

若要	執行動作
在工作表上保留解決方案值	按一下 [規劃求解結果] 對話方塊中的 [保留規劃求解求解]。
還原原始資料	按一下 [還原原始值]。

附註:

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

逐步執行規劃求解試驗值結果

在 Mac 版 Excel 2016 中：按一下 [資料>規劃求解]。

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

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

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

變更規劃求解尋找解決方案的方式

在 Mac 版 Excel 2016 中：按一下 [資料>規劃求解]。

在 2011 Mac 版 Excel 中：按一下 [資料] 索引標籤，然後按一下 [分析] 底下的 [規劃求解]。

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

若要	執行動作
設定方案時間與反覆運算	在 [所有方法] 索引標籤的 [求解限制] 底下，于 [最大時間（秒） ] 方塊中，輸入您要允許的方案時間（秒數）。然後在 [反覆運算] 方塊中，輸入您要允許的最大反覆運算數。附註: 如果方案程式在規劃求解找不到方案之前達到最大時間或反覆運算次數，則 [規劃求解] 會顯示 [顯示試解] 對話方塊。
設定精確度度	在 [所有方法] 索引標籤的 [限制式精確度] 方塊中，輸入您想要的精確度度。數位越小，精確度就越高。
設定收斂度	在 [ Generalized grg 非線性] 或 [進化] 索引標籤上的 [收斂] 方塊中，輸入您要在規劃求解停止使用方案前的最後五次反覆運算中所允許的相對變更量。數位越小，就可以減少相對較少的變更。

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

儲存或載入問題模式

在 Mac 版 Excel 2016 中：按一下 [資料>規劃求解]。

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

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

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

規劃求解所使用的求解方法

在 Mac 版 Excel 2016 中：按一下 [資料>規劃求解]。

在 2011 Mac 版 Excel 中：按一下 [資料] 索引標籤，然後按一下 [分析] 底下的 [規劃求解]。
在 [選取求解方法] 快顯功能表上，選取下列其中一項：

求解方法	描述
GENERALIZED GRG （泛化減少梯度）非線性	預設選項（適用于使用除 IF 以外的大多數 Excel 函數的模型），請選擇、查閱及其他 "step" 函數。
單工 LP	針對線性程式設計問題使用這個方法。您的模型應該在依賴變數儲存格的公式中使用 SUM、SUMPRODUCT、+ 和 *。
進化 (Evolutionary)	根據遺傳演算法，這種方法最適合您的模型使用 IF、選擇或查閱的引數，而這些引數會依賴變數儲存格。