[規劃求解] 是可用於模擬分析的 Microsoft Excel 增益集程式。 [規劃求解] 可以用來尋找儲存格 (又稱為目標儲存格) 中公式的最佳 (最大或最小) 值,但必須遵守工作表上其他公式儲存格之值的限制式或限制。 [規劃求解] 運用一組儲存格 (稱為決策變數或直接稱為變數儲存格) 來計算目標儲存格與限制式儲存格中的公式。 [規劃求解] 會調整決策變數儲存格中的值,以符合限制式儲存格的限制並產生您期望的目標儲存格結果。
簡單來說,您可以透過變更其他儲存格,使用規劃求解來判斷一個儲存格的最大值或最小值。 例如,您可以變更預計的廣告預算金額,並查看對預計利潤金額的影響。
在下列範例中,每一季中廣告層級會影響單位數目銷售量,間接決定銷售收益、相關支出和利潤。 [規劃求解] 可以變更每季的廣告預算 (決策變數儲存格 B5:C5),總預算上限為 $20,000 (儲存格 F5),直到利潤總和 (目標儲存格 F7) 到達可能的最大額。 變數儲存格中的值是用來計算每一季的利潤,以便與公式目標儲存格 F7,=Sum (Q1 Profit:Q2 Profit) 相關。
1. 變數儲存格
2. 限制儲存格
3. 目標儲存格
執行 [規劃求解] 之後,求得的新值如下。
-
在 [資料] 索引標籤上,按一下 [分析] 群組中的 [規劃求解]。
附註: 如果無法使用 [規劃求解] 命令或 [分析] 群組,您必須啟動規劃求解增益集。 請參閱: 如何啟用規劃求解載宏。
-
在 [設定目標式] 方塊中,輸入目標儲存格的儲存格參照或名稱。 目標儲存格必須包含公式。
-
請執行下列其中一項動作:
-
如果希望目標儲存格的值越大越好,請按一下 [最大值]。
-
如果希望目標儲存格的值越小越好,請按一下 [最小值]。
-
如果希望目標儲存格為特定的值,請按一下 [值],然後在方塊中輸入值。
-
在 [藉由變更變數儲存格] 方塊中,輸入每個決策變數儲存格範圍的名稱或參照。 使用逗號分隔不相鄰的參照。 變數儲存格必須直接或間接與目標儲存格相關。 您最多可以指定 200 個變數儲存格。
-
-
在 [設定限制式] 方塊中,執行下列動作以輸入要套用的任何限制式。
-
在 [規劃求解參數] 對話方塊中,按一下 [新增]。
-
在 [儲存格參照] 方塊中,輸入要限制值的儲存格參照或儲存格範圍名稱。
-
單擊所參照單元格與限制式之間所要的關聯 (< =、=、 >=、int、bin 或 dif ) 。如果您按兩下 int,[限制式] 方塊中會出現整數。 如果您按下 bin, 二進位 會出現在 [ 限制 式] 方塊中。 如果您按兩下 dif,[限制式] 方塊中會出現所有差異。
-
如果您在 [限制式] 方塊中選擇 <=、= 或 >= 的關係,請輸入數字、儲存格參照或名稱,或是公式。
-
請執行下列其中一項動作:
-
如果要接受限制式,並新增另一個限制式,請按一下 [新增]。
-
如果要接受限制式,並回到 [規劃求解參數] 對話方塊中,請按一下 [確定]。
注意 您只能在決策變數儲存格的限制式中套用 int、bin 及 dif 關聯。您可以執行下列動作以變更或刪除現有限制式:
-
-
在 [規劃求解參數] 對話方塊中,按一下要變更或刪除的限制式。
-
按一下 [變更] 然後再做變更,或是按一下 [刪除]。
-
-
按一下 [求解] 並執行下列其中一項動作:
-
若要保存工作表中求解的值,請按一下 [規劃求解結果] 對話方塊中的 [保留規劃求解解答]。
-
若要還原按一下 [求解] 之前的初值,請按一下 [還原初值]。
-
您可以按 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) 相關。
可變單元格
受限制的儲存格
目標儲存格
執行 [規劃求解] 之後,求得的新值如下。
-
按兩下 [規劃 求解 > 數據 ]。
-
在 [設定目標] 中,輸入目標儲存格的 儲存格參照 或名稱。
附註: 目標儲存格必須包含公式。
-
請執行下列其中一項動作:
若要
執行此動作
盡可能讓目標儲存格的值變大
按兩下 [最大值]。
盡可能小化目標儲存格的值
按兩下 [最小分鐘]。
將目標儲存格設定為特定值
按兩下 [值],然後在方塊中輸入值。
-
在 [藉由變更變數儲存格] 方塊中,輸入每個決策變數儲存格範圍的名稱或參照。 以逗點來分隔不相鄰的參照。
變數儲存格必須直接或間接與目標儲存格相關。 您最多可以指定 200 個變數儲存格。
-
在 [ 受限於限制式] 方塊 中,新增您要套用的任何限制式。
若要新增限制式,請遵循下列步驟:
-
在 [規劃求解參數] 對話方塊中,按一下 [新增]。
-
在 [儲存格參照] 方塊中,輸入要限制值的儲存格參照或儲存格範圍名稱。
-
在 [<= 關聯] 快捷功能表上,選取所參照單元格與限制式之間的關聯。如果您選擇 =、=或 >=<,請在 [限制式] 方塊中輸入數位、單元格參照或名稱或公式。
附註: 您只能在決策可變單元格的限制式中套用 int、bin 和 dif 關聯。
-
執行下列其中一個動作:
若要
執行此動作
接受限制式並新增另一個限制式
按一下 [新增]。
接受限制式並返回 [ 規劃求解參數] 對話框
按一下 [確定]。
-
-
按兩下 [求解],然後執行下列其中一項操作:
若要
執行此動作
將解決方案值保留在工作表上
按兩下 [規劃求解結果] 對話框中的 [保留規劃求解解決方案]。
還原原始數據
按兩下 [還原原始值]。
附註:
-
若要中斷解決方案程式,請按 ESC 鍵。 Excel 會使用可調整儲存格的上一個值重新計算工作表。
-
若要在 [規劃求解] 找到解答後根據解答建立報表,您可以在 [報表] 方塊中按一下報表類型,然後按一下 [確定]。 報表會建立在活頁簿中的新工作表上。 如果 [規劃求解] 找不到解決方案,則無法使用建立報表的選項。
-
若要將調整儲存儲存為日後可顯示的分析藍本 ,請按 兩下 [ 規劃求解結果 ] 對話方塊中的 [儲存分析藍本],然後在 [分析藍本名稱] 方塊中輸入分析藍本 的名稱 。
-
按兩下 [規劃 求解 > 數據 ]。
-
定義問題之後,請在 [ 規劃求解參數] 對話框中,按兩下 [ 選項]。
-
選取 [ 顯示反覆運算結果 ] 複選框以查看每個試用解決方案的值,然後按兩下 [ 確定]。
-
在 [規劃求解參數] 對話方塊中按一下 [求解]。
-
在 [ 顯示試用解決方案 ] 對話框中,執行下列其中一項操作:
若要
執行此動作
停止解決方案程式,並顯示 [ 規劃求解結果 ] 對話方塊
按兩下 [停止]。
繼續解決方案程式並顯示下一個試用解決方案
按一下 [繼續]。
-
按兩下 [規劃 求解 > 數據 ]。
-
按兩下 [選項],然後在 [ 選項 ] 或 [ 規劃求解選項 ] 對話框中,選擇下列一或多個選項:
若要
執行此動作
設定解決方案時間和反覆運算
在 [ 所有方法] 索 引標籤的 [ 求解限制] 底下,於 [ 最大時間 (秒) ] 方塊中,輸入您要允許的解決時間秒數。 然後在 [ 反覆運算] 方 塊中,輸入您要允許的反覆運算數上限。
附註: 如果解決方案程式達到規劃求解找到解決方案之前的最大時間或反覆運算次數,規劃求解會顯示 [ 顯示試用解決方案 ] 對話方塊。
設定精確度
在 [ 所有方法] 索 引標籤的 [ 限制式精確 度] 方塊中,輸入所要的精確度。 數位越小,精確度越高。
設定合併程度
在 [GRG 非線性] 或 [進化] 索引卷標的 [合併匯算] 方塊中,輸入您想要在最後五次反覆運算中允許的相對變更量,然後再使用解決方案停止規劃求解。 數位越小,允許的相對變更就越少。
-
按一下 [確定]。
-
在 [ 規劃求解參數] 對話框中,按兩下 [ 求解 ] 或 [ 關閉]。
-
按兩下 [規劃 求解 > 數據 ]。
-
單擊 [載入/儲存],輸入模型區域的單元格範圍,然後按 兩下 [儲存 ] 或 [ 載入]。
儲存模式時,請輸入您要放置問題模式之空白儲存格垂直範圍的第一個儲存格參照。 當您載入模式時,請輸入包含問題模式之完整儲存格範圍的參照。
提示: 您可以儲存活頁簿,將 [ 規劃求解參數 ] 對話框中的最後一個選取專案儲存為工作表。 活頁簿中的每個工作表可能都有自己的 [規劃求解] 選項,而且會儲存所有工作表。 您也可以按下 [ 載入/儲存 ] 個別儲存問題,為工作表定義多個問題。
-
按兩下 [規劃 求解 > 數據 ]。
-
在 [ 選取求解方法 ] 快捷功能表上,選取下列其中一項:
求解方法 |
描述 |
---|---|
GRG (一般化降低漸層) 非線性 |
使用 IF、CHOOSE、LOOKUP 和其他「步驟」函數以外的大部分 Excel 函數的模型預設選項。 |
Simplex LP |
使用此方法解決線性程序設計問題。 您的模型應該在相依於可變單元格的公式中使用SUM、SUMPRODUCT、+ 和 *。 |
進化 (Evolutionary) |
當您的模型使用 IF、CHOOSE 或 LOOKUP 搭配相依於可變單元格的自變數時,此方法是根據演算法的最佳方法。 |
附註: 部分規劃求解程式代碼的著作權為 1990-2010,由一線 Systems, Inc. 部分著作權 1989 By Optimal Methods, Inc.
由於 Excel 網頁版不支援載入宏程式,因此您將無法使用規劃求解載宏對數據執行模擬分析,以協助您找到最佳解決方案。
如果您有 Excel 桌面應用程式,可以使用 [ 在 Excel 中開 啟] 按鈕來開啟活頁簿以 使用規劃求解載宏。
更多規劃求解使用說明
如需規劃求解聯繫人更詳細的說明:
線性系統,Inc.規劃求解說明]。
P.O. 方塊 4288 Incline Village, NV 89450-4288 (775) 831-0300 網站:http://www.solver.com 電子郵件:info@solver.com www.solver.com部分規劃求解程式碼版權所有 1990-2009 Frontline Systems, Inc.。部分版權所有 1989 Methods, Inc.。
需要更多協助嗎?
您可以隨時詢問 Excel 技術社群中的專家,或在社群中取得支援。