【20171117】- VBA可以用來規劃求解？

最近群里新加入了好幾個新夥伴，諮詢的問題都很有意思，今天為大家介紹的是位成都的會計諮詢的規劃求解問題，當然她諮詢的問題並沒有標題那麼複雜。問題截圖如下：

圖中小夥伴的信息我都塗抹了，因為有小夥伴反映我泄露他們的信息了。後面諮詢的問題截圖我都會加以修飾，除非小夥伴同意無碼展示。

其實該小夥伴的問題很簡單，是由於設置的規劃求解參數有誤。既然有小夥伴用到規劃求解，今天就和大家分享一下如何使用規劃求解。首先第一個問題要解決的就是規劃求解從哪裡能夠找到？請跟隨Steven一起設置吧，詳見如下動態圖：

因為Excel默認是關閉規劃求解的，需要的時候可以參照如上動態圖進行開啟；Excel 2003開始就已經有了規劃求解，如果沒有找到該載入項，請在Office 2003安裝的時候或控制面板添加Office組件時勾選，如果選擇全部功能，那肯定會安裝的哦。

通過設置，可以從【數據】選項卡的最後找到【規劃求解】按鈕，如下圖所示：

為了更加形象的演示規劃求解過程，我引用了成都小夥伴的一個在有限資源內使邊際貢獻最大的問題。問題數據截圖如下：

其實實際問題只是上圖中的左側三列，右側四列數據是為了讓大家更好的理解問題而填寫上去的，把朱爾斯生產數設置為X1，朱古力生產數設置為X2；最終要求的目標函數：MAXF=1.00*X1+2.00*X2；因為生產數不可能為負數且為整數，故X1、X2必須大於等於零；因資源有限，故又有2個約束條件，分別為0.02*X1+0.05*X2<=700和0.20*X1+0.25*X2<=5000。

上面的問題是個非常典型的案例，很多小夥伴可能都已經接觸過，有了變數、目標函數和約束條件，再結合Excel的規劃求解，那麼該問題就非常容易了。規劃求解前，對數據和公式進行梳理就變得更加重要了，我的梳理如下：

通過和前面一張圖右側四列對比，應該就很容易理解為啥上圖需要這樣設定，有了這樣的設定，就可以點擊【數據】選項卡中的【線性規劃】，在彈出的對話框中設置如下參數：

規劃求解參數的設定和Excel表格中E1:H5的描述相對應，就應該很容易理解為啥參數需要這樣設定了。

因為每次設定參數比較繁瑣，故編寫了一段VBA代碼，用來快速規劃求解，具體代碼如下：

Sub sutSolver()n Range("B9:C9") = 0n Range("D9").Formula = "=B9*1+C9*2"n SolverResetn SolverOk SetCell:="$D$9", MaxMinVal:=1, ValueOf:=0, byChange:="$B$9:$C$9", Engine:=1, EngineDesc:="GRG Nonlinear"n Solveradd CellRef:=Range("$B$9:$C$9"), Relation:=3, formulaText:="0"n Solveradd CellRef:=Range("$B$9:$C$9"), Relation:=4, formulaText:="整數"n Solveradd CellRef:="$D$10", Relation:=1, formulaText:=Range("B5")n Solveradd CellRef:="$D$11", Relation:=1, formulaText:=Range("C5")n SolverSolve UserFinish:=Truen SolverFinish KeepFinal:=1nEnd Subn

如果希望上面的代碼能夠正常運行，記得在VBE窗口中依次選擇【工具】→【引用】裡面勾選Solver.xlam（文件路徑為C:Program Files (x86)Microsoft OfficerootOffice16LibrarySOLVERSOLVER.XLAM），使用Everything搜索截圖如下圖所示：

正常勾選Solver.xlam後，如下圖所示：

萬事俱備，只欠動態演示，動態演示如下：

通過今天規劃求解的學習，想必大家對規劃求解有了一定的了解，以後工作中遇到類似的問題可以立刻行動起來了哦，如果在日常工作運用中遇到任何問題，歡迎加群（QQ群：615356012）進行交流~Written by Steven in 20171117^_^

微信公眾號：SaveUTime

SUT學習交流群：615356012，入群審核人：Steven

關注公眾號，提高效率，節約您的時間！