[VBA] 規劃求解

程式語言：VBA
Package：工具 -> 增益集 -> 規劃求解
微軟官網解釋

功能：最佳化求解

範例前提假設：

• 已有 13 張優惠券，有以下種類
1. 消費   500 以上，可折抵   50 元 X 2
2. 消費   599 以上，可折抵   60 元 X 4
3. 消費   999 以上，可折抵 100 元 X 1
4. 消費 1000 以上，可折抵 100 元 X 2
5. 消費 1199 以上，可折抵 120 元 X 1
6. 消費 1499 以上，可折抵 150 元 X 1
7. 消費 2000 以上，可折抵 200 元 X 1
8. 消費 2099 以上，可折抵 210 元 X 1
• 欲講買 24 張餐券，單價 199 元

請問最好的優惠券組合是什麼？

整理一下，可以得到如圖所示的部分

• 餐券需要張數：表示需買到此數量，才能得到折扣
• 優惠券使用：表示預計使用的量，為規劃求解的變數
• 折扣：可以得到的折扣，下方為總數，公式：扣抵 x 優惠券使用
• 得到張數：可以得到的餐券數，下方為總數，公式：餐券需要張數 x 優惠券使用
• 折扣數：總共折扣 % 數，為目標值，希望為最大值

開啟 工具 -> 規劃求解 得到如圖所示

• 設定目標儲存格 => 折扣數 ( $J$5 )
• 變數儲存格 => 優惠券使用 ( $G$5:$G$13 )
• 點選新增，加入限制式：int 表示整數，bin 表示 0 或 1

• 優惠券張數( $C$5:$C$13 ) ≧ 優惠券使用( $G$5:$G$13 ) ≧ 0
• 優惠券使用 ( $G$5:$G$13 ) 需是 整數 (int)
• 得到張數總數( $I$14 ) = 所需張數 ($B$1)
• 點選求解，即可得到最佳解
• 可微調選項的值，得到更好的解 ( 通常無需調整 )

VBA 的使用方法 (可用巨集記錄上面的動作，再微調)

' 全部重設
SolverReset

'SolverAdd (CellRef, 關係, FormulaText)
'1 值是否小於或等於 <=
'2 vaue 等於 (=)。
'3 值是否大於或等於 >=
'4 值是整數
'5 是二進制（值是零或一個）
'FormulaText 引用一個或多個單元格構成右邊的 constraint
SolverAdd CellRef:=.Cells(5, 8), Relation:=3, FormulaText:=.Range("$C$6")

'規劃求解參數設置 Solverok setcell=設置目標單元格:
'MaxMinVal 對應於是否要解決目標單元對最大值 (1)、 最小值 (2), 或特定值 (3)
'ValueOf 指定要匹配目標單元值。 如果您設置 MaxMinVal 為 3, 必須指定該參數。
'如果將設置為 1 或 2, MaxMinVal 才能省略該參數。
'ByChange 指定單元格或區域的單元格， 將更改
SolverOk SetCell:="$J$5", MaxMinVal:=1, ValueOf:="0", ByChange:="$G$5:$G$13"
'可指定值，如下
'SolverOk SetCell:=.Range("$J$5"), MaxMinVal:=3, ValueOf:=.Range("$A$2").Value, ByChange:=.Range("B11:L11")

SolverOptions MaxTime:=10000, Iterations:=10000, Precision:=0.000001, _
    AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, _
    SearchOption:=1, IntTolerance:=0.5, Scaling:=True, Convergence:=0.0001, _
    AssumeNonNeg:=False

'若指定為 True，則傳回結果時不顯示 [規劃求解結果] 對話方塊。
'若指定為 False 或省略，則傳回結果的同時還顯示 [規劃求解結果] 對話方塊。
SolverSolve (True)

'如果 KeepFinal 為 1 或被省略，則最後求解的值將保留在可變儲存格中，並取代原有的任何值。
'如果 KeepFinal 為 2，則放棄最後求解的值，並復原儲存格原先的值。
SolverFinish (1)