如何使用 Excel 97 中的 Excel Solver 建立 Visual Basic 巨集

文章翻譯 文章翻譯
文章編號: 843304 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

在此頁中

結論

本文將告訴您如何使用 Microsoft Excel 97 中的 Microsoft Excel Solver 來建立 Microsoft Visual Basic 巨集。Microsoft Excel Solver 是一個 Microsoft Excel 增益集。

此外,本文還包含如何建立巨集、如可設計巨集,以及如何處理巨集之條件約束的相關資訊。本文也會討論 Microsoft Excel Solver 所使用的演算法和方法。以下清單包含本文中討論的所有主題。

簡介

本文包含 Microsoft Excel Solver 的相關資訊。

其他相關資訊

Microsoft Excel Solver 的說明

Microsoft Excel Solver 是一個 Microsoft Excel 增益集。Microsoft Excel Solver 會幫助您決定 Microsoft Excel 工作表上,特定目標儲存格內之公式的最佳值。Microsoft Excel Solver 會使用方程式,來調整與目標儲存格相關之其他儲存格的值。您建立好方程式,並定義方程式中之變數的一組參數或條件約束之後,Microsoft Excel Solver 就會嘗試各種解法,以得到滿足所有條件約束的答案。Microsoft Excel Solver 會使用下列元素來「解」方程式:
  • 目標儲存格 - 目標儲存格是最終目的。它是工作表模型中要最小化、最大化或設定為特定值的儲存格。
  • 變數儲存格 - 變數儲存格是做出判斷的變數。這些儲存格會影響目標儲存格的值。Microsoft Excel Solver 會變更這些儲存格,以尋找目標儲存格的最佳解法。
  • 條件約束 - 條件約束是對儲存格內容的限制。例如,工作表模型中某個儲存格可能限制為整數值,而另一個儲存格可能限制為小於某個指定值。
您可以使用 Microsoft Visual Basic for Applications (VBA) 巨集,以自動執行 Microsoft Excel Solver 模型的建立與操作。本文將告訴您如何使用 VBA 巨集語言,在 Microsoft Excel 97 中使用 Microsoft Excel Solver 函數。本文假設您已熟悉 VBA 語言和 Microsoft Excel 97 的 Microsoft Visual Basic 編輯器。其中使用的範例可以從下列 Microsoft 網站下載:
http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me/EN-US/SolverEx.exe
注意 本文中說明的巨集與範例,也可以在 Microsoft Excel 5.0 與 7.0 版中使用。

如何在 VBA 巨集中使用 Microsoft Excel Solver 函數

若要在 VBA 巨集中使用 Microsoft Excel Solver 增益集函數,您必須從包含巨集之活頁簿的 VBA 專案參照增益集。如果未參照 Microsoft Excel Solver 增益集,則在嘗試執行巨集時,將會收到下列編譯錯誤:
Compile Error:Sub or Function not defined (編譯錯誤:Sub 或 Function 未定義)。
若要在活頁簿中參照巨集的 Microsoft Excel Solver 增益集,請依照下列步驟執行:
  1. 開啟活頁簿。
  2. [工具] 功能表上,指向 [巨集],然後按一下 [Visual Basic 編輯器]
  3. 按一下 [工具] 功能表上的 [設定引用項目]
  4. [可引用的項目] 清單中,按一下以選取 [Solver.xls] 核取方塊,然後按一下 [確定]

    注意 如果在 [可引用的項目] 清單中看不到 Solver.xls,請按一下 [瀏覽]。在 [新增引用項目] 對話方塊中,找出並選取 Solver.xla 檔案,然後按一下 [開啟]。Solver.xla 檔案通常位於 C:\Program Files\Microsoft Office\Office\Library\Solver 子資料夾下。
您現在已經可以在 VBA 巨集中使用 Microsoft Excel Solver 函數。

如何設計可建立並解決簡單 Microsoft Excel Solver 模型的 VBA 巨集

雖然 Microsoft Excel Solver 提供許多函數,不過下列這三個函數是建立和解決模型的基礎:
  • SolverOK 函數
  • SolverSolve 函數
  • SolverFinish 函數

SolverOK 函數

SolverOK 函數會定義基本的 Microsoft Excel Solver 模型。SolverOK 函數通常是建立 Microsoft Excel Solver 模型時,所使用的第一個函數。SolverOK 函數等於按一下 [工具] 功能表上的 [規劃求解],然後指定 [Solver Parameters] 對話方塊中的選項。以下是 SolverOK 函數的語法:
SolverOK(SetCell, MaxMinVal, ValueOf, ByChange)
以下資訊說明 SolverOK 函數的語法:
  • SetCell 指定目標儲存格。
  • MaxMinVal 對應至您要將目標儲存格解到最大值 (1)、最小值 (2) 或特定值 (3)。
  • ValueOf 指定目標儲存格要符合的值。如果將 MaxMinVal 設定為 3,就必須指定這個引數。如果將 MaxMinVal 設定為 1 或 2,則可以省略這個引數。
  • ByChange 指定要變更的一個儲存格或某個範圍內的儲存格。
[圖 1] 顯示 SolverOK 函數的引數與 [Solver Parameters] 對話方塊中的參數之間的關聯性。

圖 1. 與 SolverOK 引數關聯的參數

摺疊此圖像展開此圖像
 圖 1. 與 SolverOK 引數關聯的參數


SolverSolve 函數

SolverSolve 函數會使用您以 SolverOK 函數指定的參數來解決模型。執行 SolverSolve 函數等於按一下 [Solver Parameters] 對話方塊中的 [解決]。以下是 SolverSolve 函數的語法:
SolverSolve(UserFinish, ShowRef)
下列資訊說明 SolverSolve 函數的語法:
  • UserFinish 指出您是否要使用者完成解決模型。

    若要傳回結果但不顯示 [Solver Results] 對話方塊,請將這個引數設定為 TRUE。若要傳回結果並顯示 [Solver Results] 對話方塊,請將這個引數設定為 FALSE
  • ShowRef 識別 Microsoft Excel Solver 傳回中繼解法時呼叫的巨集。

    唯有傳遞 TRUE 到 SolverOptions 函數的 StepThru 引數時,才應該使用 ShowRef 引數。

SolverFinish 函數

SolverFinish 函數指示要對結果採取什麼動作,以及解法程序完成之後要建立的報告種類。以下是 SolverFinish 函數的語法:
SolverFinish (KeepFinal, ReportArray)
下列資訊說明 SolverFinish 函數的語法:
  • KeepFinal 指示要如何處理最終結果。如果 KeepFinal 是 1,最終解答值會保留在變數儲存格中,取代其中的值。如果 KeepFinal 是 2,則會捨棄最終解答值,並還原先前的值。
  • ReportArray 指定一個陣列,陣列指示得到解答時 Microsoft Excel 會建立的報告類型。如果 ReportArray 設定為 1,Microsoft Excel 會建立答案報告。如果設定為 2,Microsoft Excel 會建立敏感度報告,若設定為 3,Microsoft Excel 會建立限制報告。如需這些報告的詳細資訊,請參閱<如何產生解法的報告>一節。
圖 2. 與 SolverFinish 引數關聯的 Microsoft Excel Solver 結果選項

摺疊此圖像展開此圖像
 圖 2. 與 SolverFinish 引數關聯的 Solver 結果選項


本文說明如何互動地建立簡單的 Microsoft Excel Solver 模型。第一步是建立模型的工作表。工作表會包含一些資料儲存格,而至少有一個儲存格會包含公式。這個公式會依賴工作表中其他的儲存格。設定好工作表之後,按一下 [工具] 功能表上的 [規劃求解]。在 [Solver Parameters] 對話方塊中指定目標儲存格、您要解答的值、將要變更之儲存格的範圍,以及條件約束。按一下 [解決] 開始解答程序。Microsoft Excel Solver 找到解答之後,會在您的工作表中顯示結果,而且 Microsoft Excel Solver 會顯示一個訊息方塊,提示您決定要保留最終結果,或者要捨棄結果。您按一下其中一個選項之後,Microsoft Excel Solver 即告完成。

[圖 3] 說明可以使用這些步驟建立的簡單模型。

圖 3. 簡單模型:平方根模型

摺疊此圖像展開此圖像
 圖 3. 簡單模型:平方根模型


在這個範例中,將包含公式 =A1^2 的儲存格 A1 變更為一個值,這個值會讓儲存格 A2 的值等於 50。也就是尋找 50 的平方根。平方根模型中沒有條件約束。Find_Square_Root 巨集會完成下列工作:
  • 設定一個模型,這個模型會變更儲存格 A1 的值,求出會使 A2 的值等於 50 的值。
  • 它會解決模型。
  • 將最終結果儲存到工作表,不顯示 [Solver Results] 對話方塊。
這個簡單的巨集會建立 Microsoft Excel Solver 模型,而且不需使用者介入就能解決模型。下列程式碼說明 Find_Square_Root 巨集:
    Sub Find_Square_Root()

    ' Set up the parameters for the model.
    ' Set the target cell A2 to a value of 50 by changing cell A1.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
         ByChange:=Range("A1")

    ' Solve the model but do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub
Find_Square_Root2 巨集是 Find_Square_Root 巨集的修改版本。如果使用 InputBox 函數,Find_Square_Root2 巨集會提示您輸入想要為目標儲存格解決的值。您輸入值之後,Find_Square_Root2 巨集會將這個參數設定為 SolverOKvalueof 引數的值,解決問題,將結果儲存在變數平方根中,然後捨棄解答並將工作表中的值還原為原始狀態。Find_Square_Root2 巨集基本上說明您可以如何將結果儲存在一個或多個變數中,然後將變數儲存格還原為原始值。

下列程式碼說明 Find_Square_Root2 巨集:
    Sub Find_Square_Root2()

    Dim val
    Dim sqroot

    ' Request the value for which you want to obtain the square root.
    val = Application.InputBox( _
         prompt:="Please enter the value for which you want " & _
         "to find the square root:", Type:=1)

    ' Set up the parameters for the model.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
         ByChange:=Range("A1")

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Save the value of cell A1 (the changing cell) before you discard 
    ' the results.
    sqroot = Range("a1")

    ' Finish and discard the results.
    SolverFinish KeepFinal:=2

    ' Show the result in a message box.
    MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

    End Sub

如何產生解法的報告

Microsoft Excel Solver 提供許多類型的報告,這些報告說明如何變更結果,以及如何關閉已達到關鍵值的條件約束。每一份報告都會放在活頁簿中單獨的一份工作表上。以下是 Microsoft Excel Solver 提供的報告類型:
  • 答案報告 - 答案報告會列出目標儲存格和變數儲存格,以及它們的對應原始與最終值、條件約束,還有條件約束的相關資訊。
  • 敏感度報告 - 敏感度報告會提供目標儲存格之解答對公式中,對於微小變化之敏感度的相關資訊。
  • 限制報告 - 限制報告會列出目標儲存格與變數儲存格,以及它們個別的值、下限與上限和目標值。
若要建立模型的報告,請為 SolverFinish 函數的 ReportArray 引數指定值陣列。如需 ReportArray 引數的詳細資訊,請參閱<SolverFinish (KeepFinal, ReportArray) >一節。例如,您若是想要產生 Find_Square_Root2 巨集建立及解決之模型的限制報告,請修改巨集中的 SolverFinish 函數,使其類似以下的範例程式碼:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
若要產生多個報告,請修改 SolverFinish 函數,使其類似以下的範例程式碼:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

如何在迴圈巨集中使用 Microsoft Excel Solver 函數

在許多情況下,讓 Microsoft Excel Solver 解決目標儲存格的多個值,是很好的做法。這通常可以使用 VBA 提供的迴圈結構之一來完成。

Create_Square_Root_Table 巨集示範 Microsoft Excel Solver 在迴圈巨集中作用的情形。Create_Square_Root_Table 巨集會在新的工作表中建立一個表格。它會插入一到十的數字,以及每個數字的對應平方根。Create_Square_Root_Table 巨集會使用 For 迴圈建立表格,從 1 到 10 反覆運算,並解答平方根模型中目標儲存格的值,以求出符合反覆運算數目的值。下列程式碼說明 Create_Square_Root_Table 巨集:
    Sub Create_Square_Root_Table()

    ' Add a new worksheet to the workbook.
    Set w = Worksheets.Add

    ' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
    w.Range("C1").Value = 2
    w.Range("C2").Formula = "=C1^2"

    ' A loop that will make 10 iterations, starting with the number 1, 
    ' and finishing at the number 10.
    For i = 1 To 10

        ' Set the Solver parameters that indicate that Solver should
        ' solve the cell C2 for the value of i (where i is the number
        ' of the iteration) by changing cell C1.
        SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
            MaxMinVal:=3, ValueOf:=i

        ' Do not display the Solver Results dialog box.
        SolverSolve UserFinish:=True

        ' Save the value of i in column A and the results of the 
        ' changing cell in column B.
        w.Cells(i, 1) = i
        w.Cells(i, 2) = Range("C1")

        ' Finish and discard the final results.
        SolverFinish KeepFinal:=2

    Next

    ' Clear the range C1:C2
    w.Range("C1:C2").Clear

    End Sub

Create_Square_Root_Table 巨集會產生 [圖 4] 中說明的表格。

圖 4. Create_Square_Root_Table 巨集產生的輸出

摺疊此圖像展開此圖像
圖 4. Create Square Root Table 巨集產生的輸出


如何處理條件約束

條件約束是對一個或多個儲存格之內容的限制。一個模型可以有一個或多個條件約束。條件約束集是一組不等式或一組等式,會從解答中移除決定變數的特定值組合。例如,條件約束可能要求某個儲存格必須大於零,而另一個儲存格只能包含整數值。

我們到目前為止一直討論的平方根模型是一個簡單模型,並未包含任何條件約束。[圖 5] 說明使用條件約束的模型。這個模型的目的,是要尋找獲利最高之產品的最佳組合。

圖 5. 漸減利潤率產品矩陣

摺疊此圖像展開此圖像
圖 5. 漸減利潤率產品矩陣


例如,某家公司若是製造電視機、立體聲音響和揚聲器,而且使用電源供應器、揚聲器錐盆...等等的共同零件庫存。零件的供應有限。您的目標,是要決定所製造之產品的最佳獲利組合。數量增加,每一單位的利潤就會減少,因為填滿銷售通路需要更多的價格優惠。漸減利潤指數為 0.9。這個指數用於在 G11:I11 的範圍中依產品計算利潤。

您的目標是要找出最大的利潤 (儲存格 G14)。要尋找最大利潤所要變更的值,是您製造的單位數目。範圍 G9:G11 代表這個模型中的變數儲存格。您唯一的條件約束,是所使用的零件數目不能超過庫存的零件數目。使用 Microsoft Excel Solver,這個條件約束應為 E3:E7<=B3:B7。如果您是要互動地建立這個 Microsoft Excel Solver 模型,Microsoft Excel Solver 參數應類似 [圖 6] 中的參數。

圖 6. 使用漸減利潤率產品矩陣模型的 Microsoft Excel Solver 參數

摺疊此圖像展開此圖像
圖 6. 使用漸減利潤率產品矩陣模型的 Microsoft Excel Solver 參數


若要建立及解決「漸減利潤率產品矩陣」模型,除了先前討論過的 Microsoft Excel Solver VBA 函數之外,還要使用新的 SolverAdd 函數。SolverAdd 函數會新增條件約束到模型。執行 SolverAdd 函數等於按一下 [Solver Parameters] 對話方塊中的 [新增] 按鈕。SolverAdd 函數的語法如下:
SolverAdd (CellRef, Relation, FormulaText)
下列資訊說明 SolverAdd 函數的語法:
  • CellRef 參考構成條件約束左邊的一個或多個儲存格。
  • Relation 是條件約束左邊和右邊之間的算術關係。
  • Relation 可以是 1 到 5 之間的值,如以下範例中所示:
    • 值 1 是小於或等於 (<=)。
    • 值 2 是等於 (=)。
    • 值 3 是大於或等於 (>=)。
    • 值 4 是整數。
    • 值 5 是二進位數 (零或一的值)。
  • FormulaText 參考構成條件約束右邊的一個或多個儲存格。**
** 您指定 SolverAdd 函數之 FormulaText 引數的儲存格範圍時,請注意參考是相對或絕對參考。通常必須為 FormulaText 引數指定絕對參考。但是,如果為 FormulaText 引數指定相對參考,應了解參考是與目標儲存格相對的,而非與作用儲存格相對。

注意 在 Microsoft Excel 5.0 與 7.0 版中,使用 FormulaText 引數指定一個儲存格或一個範圍內的儲存格時,使用的是 R1C1 表示法。而在 Microsoft Excel 97 中,則是使用 A1 表示法指定 FormulaText 引數。

圖 7. 與 SolverAdd 引數相關的欄位

摺疊此圖像展開此圖像
 圖 7. 與 SolverAdd 引數相關的欄位


產生「漸減利潤產品矩陣」模型之模型的 Maximum_Profit 巨集。這個巨集執行下列函數或引數:

  • SolverOK 函數會設定目標儲存格的最大值,並指定要變更的儲存格。
  • SolverAdd 函數會新增條件約束到模型。
  • SolverSolve 函數會尋找解答,但不會顯示 [Solver Results] 對話方塊。
  • SolverFinish 函數會將最終結果傳回工作表。
下列程式碼說明 Maximum_Profit 巨集:
    Sub Maximum_Profit()

    ' Set up the parameters for the model.
    ' Determine the maximum value for the sum of profits in cell G14
    ' by changing the number of units to build in cells G9:I9.
    Solverok setcell:=Range("G14"), maxminval:=1, _
        bychange:=Range("G9:I9")

    ' Add the constraint for the model. The only constraint is that the
    ' number of parts used does not exceed the parts on hand-- 
    ' E3:E7<=B3:B7
    SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub

注意 在 Microsoft Excel 5.0 與 7.0 版中,使用 FormulaText 引數指定儲存格或儲存格範圍時,使用的是 R1C1 表示法。而在 Microsoft Excel 97 中,則是使用 A1 表示法指定 FormulaText 引數。

您執行 Maximum_Profit 巨集時,Microsoft Excel Solver 會尋找製造 160 台電視機、200 台立體聲音響及 80 組揚聲器,最大利潤為 14,917 美元的解答。

如何變更及刪除條件約束

模型中的條件約束可以程式設計的方式變更或刪除。條件約束是利用其 CellRefRelation 引數識別。

若要利用程式變更現有的條件約束,請使用 SolverChange 函數。以下是 SolverChange 函數的語法:
SolverChange (CellRef, Relation, FormulaText)
請注意,SolverChange 函數的引數和用於 SolverAdd 函數的引數相同。

如果想要變更「漸減利潤產品矩陣」模型中的條件約束,要使用 SolverChange 函數。例如,目前指定的條件約束,是使用的零件數目小於或等於庫存零件數目 (E3:E7 <= B3:B7)。如果想要變更這個條件約束,讓使用的零件數目小於或等於預計的零件數目 (庫存零件數目加上訂購的零件數目)。這個新條件約束將會是 E3:E7 <= D3:D7。以下巨集會將現有的條件約束 E3:E7<=B3:B7 變更為 E3:E7 <= D3:D7 並求出解答。

下列程式碼說明 Change_Constraint_and_Solve 巨集:
    Sub Change_Constraint_and_Solve()

    ' Change the constraint.
    SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
       FormulaText:="$D$3:$D$7"

    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False

    End Sub

由於條件約束是利用 CellRefRelation 引數識別,因此只能使用 SolverChange 函數來變更條件約束的 FormulaText 引數。如果 CellRefRelation 值與現有的條件約束不符,您必須刪除條件約束,再新增修改過的條件約束。若要刪除條件約束,請使用 SolverDelete 函數。以下是 SolverDelete 函數的語法:

SolverDelete (CellRef, Relation, FormulaText)

請注意,SolverDelete 函數的引數和配合 SolverAddSolverChange 函數使用的引數相同。

以下巨集說明如何刪除及新增條件約束。在這個範例中,Change_Constraint_and_Solve2 巨集會從「漸減利潤產品陣列」模型移除條件約束 E3:E7<=B3:B7,再新增新的條件約束。新條件約束只是原始條件約束的修改,將條件約束的左右兩邊對調。

下列程式碼說明 Change_Constraint_and_Solve2 巨集:
    Sub Change_Constraint_and_Solve2()

    ' Reverse the left and right sides of the constraint...
    ' Delete the constraint E3:E7<=B3:B7 and add the
    ' constraint B3:B7>=E3:E7.
    SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"
    SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
        FormulaText:="$E$3:$E$7"
    
    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False
    
    End Sub

注意 在 Microsoft Excel 5.0 與 7.0 版中,使用 FormulaText 引數指定儲存格或儲存格範圍時,使用的是 R1C1 表示法。而在 Microsoft Excel 97 中,則是使用 A1 表示法指定 FormulaText 引數。

如何載入及儲存模型

您儲存活頁簿時,在 [Solver Parameters] 對話方塊中指定的最後參數會隨活頁簿一起儲存。因此,當您開啟活頁簿時,參數是和您上次儲存活頁簿時的參數相同。

您可以為工作表定義一個以上的問題。每個問題都由您在 [Solver Parameter][Solver Options] 對話方塊中,所輸入的儲存格和條件約束構成。由於只有最後一個問題會隨工作表一起儲存,因此除非您明確地儲存,否則將會遺失其他所有的問題。若要儲存這些問題,請按一下 [Solver Options] 對話方塊中的 [Save Model]。同樣地,當您要還原先前儲存的參數時,請按一下 [Solver Options] 對話方塊中的 [Load Model]

Solver 模型會儲存在工作表上某個範圍的儲存格中。範圍中的第一個儲存格包含目標儲存格的公式。範圍中的第二個儲存格包含識別模型中之變數儲存格的公式。範圍中最後一個儲存格包含代表 [Solver Options] 對話方塊中設定之選項的陣列。第二個儲存格和最後一個儲存格之間的儲存格,則包含代表模型中之條件約束的公式。

[圖 8] 說明員工排程的模型。假設您在一家小型製造商工作。這個表格顯示每一位員工的時薪、排定的時數,以及每一位員工每一小時可以生產的預計單位數。您的目標是符合製造單位數的特定配額,同時要降低人工成本。

圖 8. 員工排程模

摺疊此圖像展開此圖像
 圖 8. 員工排程模型


您必須考慮另外兩個因數 (或條件約束),一個是任一員工可以工作的時數下限/上限,另一個是您想要生產的單位數。如果您在指定的某一週必須生產 3975 個單位,而您希望每一位員工工作 30 到 45 小時,那麼 Microsoft Excel Solver 參數應類似下表中列出的參數:

摺疊此表格展開此表格
參數儲存格範圍描述
目標儲存格$D$12人工成本。
變數儲存格$C$2:$C$8每一位員工的工作時數。
條件約束$C$2:$C$8 <= 45每一位員工的時數上限為 45。
$C$2:$C$8 >= 30每一位員工的時數下限為 35。
$G$12 = 3975單位數是 3975。


您的目標是求出每週的最佳人工成本、每週儲存每一個模型,以及在需要時能夠載入任一個每週模型。

在巨集中,可以分別使用 SolverSaveSolverLoad 函數,來儲存及載入模型的 Microsoft Excel Solver 參數。SolverSaveSolverLoad 函數的語法如下:

SolverSave (SaveArea)

SolverLoad (LoadArea)

SolverSaveSolverLoad 函數都各自只有一個引數,分別是 SaveAreaLoadArea 引數。這些引數會指定工作表上,用於儲存模型資訊的範圍。

以下的 New_Employee_Schedule 巨集,將示範如何根據使用者輸入來建立、解決及儲存模型。使用者必須提供模型的日期、生產的單位數,以及每一員工的時數下限與上限。然後使用這些資料建立模型。模型會解決,然後和使用者輸入一起儲存。

下列程式碼說明 New_Employee_Schedule 巨集:
    Sub New_Employee_Schedule()
    
    ' Prompt the user for the date of the model, the units to produce,
    ' and the maximum and minimum number of hours per employee.
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model:", Type:=2)
    Units = Application.InputBox( _
       Prompt:="Projected Number of Units:", Type:=1)
    MaxHrs = Application.InputBox( _
       Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
    MinHrs = Application.InputBox( _
       Prompt:="Minimum Number of Hours Per Employee:", Type:=1)
    
    ' Clear any previous Solver settings.
    SolverReset
    
    ' Set the target cell, D12, to a minimum value by changing
    ' the range, C2:C8.
    SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
       ByChange:=Range("C2:C8")
    
    ' Add the constraint that number of hours worked <= MaxHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs
    
    ' Add the constraint that number of hours worked >=MinHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs
    
    ' Add the constraint that number of units produced = Units.
    SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units
    
    ' Solve the model and keep the final results.
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    
    ' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
    ' in columns I:L.
    Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
       Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
    ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
       Units, MaxHrs, MinHrs)
    
    ' Save the model parameters to the range M:R in the worksheet.
    SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)
    
    End Sub
注意 在 Microsoft Excel 5.0 與 7.0 版中,使用 FormulaText 引數指定儲存格或儲存格範圍時,使用的是 R1C1 表示法。而在 Microsoft Excel 97 中,則是使用 A1 表示法指定 FormulaText 引數。

[圖 9] 說明儲存的模型資訊在工作表上顯示的情形。

圖 9. New_Employee_Schedule 巨集所儲存的模型資訊

摺疊此圖像展開此圖像
圖 9. New Employee Schedule 巨集所儲存的模型資訊


New_Employee_Schedule 巨集會將每個新模型儲存到工作表。Load_Employee_Schedule 巨集可以載入這些儲存模型中的一個。巨集會提示使用者輸入要載入的模型,然後搜尋欄 I 以尋找模型日期。如果找到模型日期,Load_Employee_Schedule 巨集會載入對應的模型,解決模型,然後保留最終結果。

下列程式碼說明 New_Employee_Schedule 巨集:
    Sub Load_Employee_Schedule()
    
    ' Prompt for the date of the model. 
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model to Load:", Type:=2)
    
    ' Locate the date in column I.
    Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
    r = Application.Match(ModelDate, DateRange, 0)
    
    If IsError(r) Then
        ' Display a message if the model date is not found
        MsgBox "Cannot find a model with the date " & ModelDate
    Else
        ' If the model date is found, load the model into Solver,
        ' solve the model, and keep the final results.
        SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
    End If
    
    End Sub
New_Employee_Schedule 巨集會導入 SolverReset 函數。SolverReset 函數可以用來刪除 [Solver Parameters] 對話方塊中所有的儲存格選取範圍和條件約束,以及重設所有的設定。SolverReset 函數沒有引數。

如何尋找 Microsoft Excel Solver 的詳細資訊

下列資源提供如何使用 Microsoft Excel Solver 增益集的相關資訊。

  • 如需特定規劃求解訊息的說明,請參閱 Frontline Systems
  • 如需建立容易閱讀、方便管理之模型的提示,請參閱 Frontline Systems
  • 如需 Solver 之條件約束限制的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
    75714 Solver limits for constraints
  • 如需在 Microsoft Excel 中使用 Microsoft Excel Solver 增益集的許多範例,請參閱 Solvsamp.xls 範例檔案。
  • 以下是 Microsoft Excel 97 內含之範例檔案的預設位置:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • 以下是 Microsoft Excel 7.0 內含之範例檔案的預設位置:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • 以下是 Microsoft Excel 5.0 內含之範例檔案的預設位置:
    \Excel\Examples\Solver\SolvSamp.xls

如何深入了解 Microsoft Excel Solver 使用的演算法與方法

Microsoft Excel Solver 使用德州大學奧斯丁分校的 Leon Lasdon 和克里夫蘭州立大學的 Allan Waren 所開發的 Generalized Reduced Gradient (GRG2) 非線性最佳化程式碼。

如需 Microsoft Excel Solver 使用之演算法的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
82890 Solver Uses Generalized Reduced Gradient Algorithm


線性與整數問題使用限定變數的單純方法,以及 Frontline Systems, Inc. 的 John Watson 與 Dan Fylstra 提供的分支定限法 (Branch-and-Bound)。如需 Solver 使用之內部解決程序的詳細資訊,請連絡:

Frontline Systems, Inc. P.O. Box 4288 Incline Village, NV 89450-4288 (702) 831-0300 網站:http://www.frontsys.com 電子郵件:info@frontsys.com 


Microsoft Excel Solver 程式碼的選集 Copyright 1990, 1991, 1992, and 1995 by Frontline Systems, Inc.。有些部分 Copyright 1989 by Optimal Methods, Inc.。

注意 本文中討論的 Microsoft Excel Solver 增益集以其呈現的情形為準,並不保證適用於所有的情況。雖然 Microsoft 專業支援人員可以協助安裝增益集以及說明增益集的現有功能,但是不會修改增益集以提供新的功能。

不提供任何擔保。軟體係依「現況」提供,不提供任何的保證,請自行承擔使用本軟體產品的所有風險。

屬性

文章編號: 843304 - 上次校閱: 2007年11月20日 - 版次: 1.2
這篇文章中的資訊適用於:
  • Microsoft Excel 97 Standard Edition
關鍵字:?
kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo KB843304
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。
依現狀不再更新的知識庫內容免責聲明
本文旨在說明 Microsoft 不再提供支援的產品。因此,本文係依「現狀」提供,不會再更新。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com