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

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

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

簡言之,您可以使用 「 規劃求解 」 來判斷一個儲存格的最大或最小值,變更其他儲存格。例如,您可以變更您的計劃的廣告預算數量,並查看利潤的效果。

附註: 版本的 Excel 2007 之前的規劃求解稱為 「 目標儲存格 」,並決策變數儲存格,為 「 變數儲存格 」 或 「 可調整儲存格 」 目標儲存格。許多改善規劃求解增益集的 Excel 2010 中,因此如果您使用 Excel 2007 您的體驗會稍有不同。

附註: 

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

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

1. 變數儲存格

2. 限制儲存格

3. 目標儲存格

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

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

  1. 在 [資料] 索引標籤的 [分析] 群組中,按一下 [規劃求解
    Excel 功能區影像

    附註: 如果 [規劃求解] 命令或 [分析] 群組中無法使用,您需要啟動規劃求解增益集。請參閱:如何啟動規劃求解增益集

    Excel 2010 + 規劃求解] 對話方塊的圖像
  2. [設定目標式] 方塊中,輸入目標儲存格的儲存格參照或名稱。目標儲存格必須包含公式。

  3. 請執行下列其中一項動作:

    • 如果希望目標儲存格的值越大越好,請按一下 [最大值]

    • 如果希望目標儲存格的值越小越好,請按一下 [最小值]

    • 如果希望目標儲存格為特定的值,請按一下 [值],然後在方塊中輸入值。

    • 在 [藉由變更的變數儲存格] 方塊中,輸入的名稱或參照的每個決策變數儲存格範圍。以逗號分隔的非相鄰的參照。變數儲存格必須目標儲存格直接或間接相關。您可以指定最多 200 個變數儲存格。

  4. [設定限制式] 方塊中,執行下列動作以輸入要套用的任何限制式。

    1. [規劃求解參數] 對話方塊中,按一下 [新增]

    2. [儲存格參照] 方塊中,輸入要限制值的儲存格參照或儲存格範圍名稱。

    3. 按一下您在參照的儲存格與限制式之間所要的關係 ( [<=177][=][>=][int][bin][dif] )。如果您按一下 [int][限制式] 方塊中就會顯示 [整數]。如果您按一下 [bin][限制式] 方塊中會顯示 [二進制]。如果您按一下 [dif][限制式] 方塊中會顯示 [Alldifferent]

    4. 如果您在 [限制式] 方塊中選擇 <=、= 或 >= 的關係,請輸入數字、儲存格參照或名稱,或是公式。

    5. 請執行下列其中一項動作:

      • 如果要接受限制式,並新增另一個限制式,請按一下 [新增]

      • 如果要接受限制式,並回到 [規劃求解參數] 對話方塊中,請按一下 [確定]
        注意    您只能在決策變數儲存格的限制式中套用 intbindif 關聯。

        您可以執行下列動作以變更或刪除現有限制式:

    6. [規劃求解參數] 對話方塊中,按一下要變更或刪除的限制式。

    7. 按一下 [變更] 然後再做變更,或是按一下 [刪除]

  5. 按一下 [求解] 並執行下列其中一項動作:

    • 若要保存工作表中求解的值,請按一下 [規劃求解結果] 對話方塊中的 [保留規劃求解解答]

    • 若要還原按一下 [求解] 之前的初值,請按一下 [還原初值]

    • 您可以按 Esc 來中斷求解程序。Excel 會根據決策變數儲存格找到的結束值來重新計算工作表。

    • 若要在 [規劃求解] 找到解答後根據解答建立報表,您可以在 [報表] 方塊中按一下報表類型,然後按一下 [確定]。報表會建立在活頁簿的新工作表上。如果 [規劃求解] 沒有找到解答,則只能使用特定報表,或無法使用任何報表。

    • 若要將決策變數儲存格值儲存為分析藍本以供稍後顯示,請按一下 [規劃求解結果] 對話方塊中的 [儲存分析藍本],然後在 [分析藍本名稱] 方塊中輸入分析藍本的名稱。

  1. 定義問題後,按一下 [規劃求解參數] 對話方塊中的 [選項]

  2. [選項] 對話方塊中,選取 [顯示反覆運算結果] 核取方塊,以檢視每一個試驗值結果的值,然後按一下 [確定]

  3. [規劃求解參數] 對話方塊中按一下 [求解]

  4. [顯示試驗值結果] 對話方塊中,執行下列其中一項:

    • 若要停止求解程序,並顯示 [規劃求解結果] 對話方塊,請按一下 [停止]

    • 若要繼續求解程序,並顯示下一個試驗值結果,請按一下 [繼續]

  1. 按一下 [規劃求解參數] 對話方塊中的 [選項]

  2. 在對話方塊的 [所有方法][GRG Nonlinear][Evolutionary] 索引標籤上的任何選項中選擇或輸入值。

  1. [規劃求解參數] 對話方塊中,按一下 [載入/儲存]

  2. 輸入模式區的儲存格範圍,然後按一下 [儲存][載入]

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

    提示: 您可以儲存的工作表的 [規劃求解參數] 對話方塊中的最後一組選項來儲存活頁簿。活頁簿中的每個工作表可能會有自己的規劃求解選項,然後全部都儲存。您也可以按下 [載入/儲存至個別儲存問題,以定義多個工作表的問題。

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

  • 一般化縮減梯度 (Generalized Reduced Gradient,GRG) 非線性    用於平滑非線性的問題。

  • LP 單形法 (LP Simplex)    用於線性的問題。

  • 進化 (Evolutionary)    用於非平滑的問題。

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

在下列範例中,在每一季的廣告的層級會影響銷售數量,間接決定的銷售營收與相關聯的費用,利潤數量。規劃求解可以變更的通知 (決策變數儲存格 b5: c5),季預算最總預算限制式的 $20000 (儲存格 D5),直到最大利潤總和 (目標儲存格 D7)。變數儲存格的值用來計算利潤每一季中,讓他們相關資料的目標儲存格 D7,= SUM (Q1 Profit: Q2 Profit)。

「範例規劃求解」評估

圖說文字 1 變數儲存格

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

圖說文字 3 目標儲存格

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

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

  1. 在 Mac 版 Excel 2016 中: 按一下 [資料] > [規劃求解

    規劃求解

    Excel for Mac 2011 中: 按一下 [分析] 底下的 [資料] 索引標籤,按一下 [規劃求解

    [資料] 索引標籤、[分析] 群組、[規劃求解] 增益集

  2. 設定目標中, 輸入儲存格參照或目標儲存格的名稱。

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

  3. 執行下列其中一項操作:

    若要

    執行此動作

    讓目標儲存格的值越大越

    按一下 [最大值]。

    讓目標儲存格的值越小越

    按一下 [最小值]。

    設定目標儲存格為特定的值

    按一下 [值的,然後在方塊中輸入值。

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

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

  5. 在 [限制式] 方塊中,新增您想要套用的任何限制式。

    若要新增限制式,請遵循下列步驟:

    1. [規劃求解參數] 對話方塊中,按一下 [新增]

    2. [儲存格參照] 方塊中,輸入要限制值的儲存格參照或儲存格範圍名稱。

    3. < =關聯性] 快顯功能表中,選取您要參照的儲存格的限制式之間的關聯性。如果您選擇< ==,或> =,在 [限制式] 方塊中輸入數字、 儲存格參照或名稱或公式。

      附註: 您只能套用 int、 bin 及 dif 關聯在決策變數儲存格的限制式中。

    4. 執行下列其中一項操作:

    若要

    執行此動作

    接受限制式,並新增另一個

    按一下 [新增]。

    接受限制式,並回到 [規劃求解參數] 對話方塊

    按一下 [確定]。

  6. 按一下 [求解,,然後執行下列其中一項:

    若要

    執行此動作

    在工作表上保留解決方案值

    按一下 [規劃求解結果] 對話方塊中的保留規劃求解解答]。

    還原原始資料

    按一下 [還原初值]。

附註: 

  1. 若要中斷求解程序,請按 esc 鍵。Excel 會重新計算的最後一個值可調整儲存格所找到的工作表。

  2. 若要建立報表的規劃求解尋找解決方案之後,會根據您的方案,您可以按一下 [報表] 方塊中的報表類型,然後按一下 [ [確定]。在 [新活頁簿中的工作表上建立報表。如果 [規劃求解找不到解決方案,[建立報表] 選項無法使用。

  3. 若要儲存您調整儲存格的值為您稍後可以顯示的情況,按一下 [規劃求解結果] 對話方塊中的儲存分析藍本,然後輸入此案例的名稱案例名稱] 方塊中。

  1. 在 Mac 版 Excel 2016 中: 按一下 [資料] > [規劃求解

    規劃求解

    Excel for Mac 2011 中: 按一下 [分析] 底下的 [資料] 索引標籤,按一下 [規劃求解

    [資料] 索引標籤、[分析] 群組、[規劃求解] 增益集

  2. 在 [規劃求解參數] 對話方塊中定義的問題之後,請按一下 [選項]。

  3. 選取 [顯示反覆運算結果] 核取方塊,以查看每一個試驗值,然後再按一下[確定]。

  4. [規劃求解參數] 對話方塊中按一下 [求解]

  5. 在 [顯示試驗值結果] 對話方塊中,執行下列其中一項:

    若要

    執行此動作

    停止求解程序,並顯示 [規劃求解結果] 對話方塊

    按一下 [停止]。

    繼續求解程序,並顯示下一個試驗值結果

    按一下 [繼續]。

  1. 在 Mac 版 Excel 2016 中: 按一下 [資料] > [規劃求解

    規劃求解

    Excel for Mac 2011 中: 按一下 [分析] 底下的 [資料] 索引標籤,按一下 [規劃求解

    [資料] 索引標籤、[分析] 群組、[規劃求解] 增益集

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

    若要

    執行此動作

    設定解決方案時間和高次數

    所有方法] 索引標籤的 [解決限制] 底下的 [最大時間 (秒) ] 方塊中,輸入您想要允許解決方案時間的秒數。然後,在高次數] 方塊中,輸入您想要允許的最大次數。

    附註: 如果求解程序達到最大時間或反覆運算次數,規劃求解尋找解決方案之前,求解會顯示 [顯示試驗值結果] 對話方塊。

    設定的精確度

    所有方法] 索引標籤限制式精確度方塊中,輸入您想要的精確程度。較小的數字,較高的精確度。

    設定交集的角度

    Generalized 非線性Evolutionary ] 索引標籤上,在 [交集] 方塊中,輸入您想要規劃求解停駐點解決方案之前,讓末五反覆項目中的相對變更數量。小的數字,相對較不允許變更。

  3. 按一下 [確定]。

  4. 在 [規劃求解參數] 對話方塊中,按一下 [求解] 或 [關閉]。

  1. 在 Mac 版 Excel 2016 中: 按一下 [資料] > [規劃求解

    規劃求解

    Excel for Mac 2011 中: 按一下 [分析] 底下的 [資料] 索引標籤,按一下 [規劃求解

    [資料] 索引標籤、[分析] 群組、[規劃求解] 增益集

  2. 按一下 [載入/儲存]、 [模型] 區域中,輸入儲存格範圍,然後按一下 [儲存] 或 [載入

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

    提示: 您可以儲存在工作表的 [規劃求解參數] 對話方塊中的最後一組選項來儲存活頁簿。活頁簿中的每個工作表可能會有自己的規劃求解選項,然後全部都儲存。您也可以按一下 [載入/儲存至個別儲存問題,以定義多個工作表的問題。

  1. 在 Mac 版 Excel 2016 中: 按一下 [資料] > [規劃求解

    規劃求解

    Excel for Mac 2011 中: 按一下 [分析] 底下的 [資料] 索引標籤,按一下 [規劃求解

    [資料] 索引標籤、[分析] 群組、[規劃求解] 增益集

  2. 在 [選取的解決方法快顯功能表中,選取下列其中一項:

解決方法

描述

Generalized (一般化縮減梯度) 非線性

預設選項,除了使用大多數的 Excel 函數,如果模型、 選擇、 查閱及其他 」 步驟 」 函數。

LP simplex

使用這個方法的線性程式設計的問題。模型應該使用加總],SUMPRODUCT,+-和 * 取決於變數儲存格的公式中。

進化 (Evolutionary)

如果,會使用您的模型時,最適合這種方法,根據起源演算法,選擇或使用相依於變數儲存格的引數的查閱。

附註: 規劃求解的程式碼的部分會以 Frontline Systems 提供之的著作權 1990年 2010年、 Inc.缺漏: 會依 Optimal 方法國廣兌著作權 1989

更多規劃求解使用說明

在 [規劃求解連絡人的詳細說明:

Frontline Systems 提供之國廣兌
郵政 4288
傾斜度村落,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 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

請參閱

使用 [規劃求解的大寫字母預算

使用 [規劃求解的財務計劃

使用 [規劃求解決定最佳化產品集合

執行模擬分析,使用 [規劃求解] 工具

模擬分析簡介

在 Excel 中的公式概觀

如何避免中斷的公式

使用錯誤檢查來偵測公式中的錯誤

在 Windows 版 Excel 2016 中的鍵盤快速鍵

在 Mac 版 Excel 2016 中的鍵盤快速鍵

Excel 函數 (依英文字母)

Excel 函數 (依類別)

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×