Excel裡面，有一個很有用，但是很少被大家重視的功能：規劃求解。這個功能很多人都還不太清楚，那這個功能究竟有什麼用？在什麼情況下使用？該怎麼用？我用九個案例給你答案。
規劃求解是MicrosoftExcel加載項程序，可用於模擬分析。使用“規劃求解”查找一個單元格（稱為目標單元格）中公式的優化（最大或最小）值，受限或受制於工作表上其他公式單元格的值。“規劃求解”與一組用於計算目標和約束單元格中公式的單元格（稱為決策變量或變量單元格）一起工作。“規劃求解”調整決策變量單元格中的值以滿足約束單元格上的限制，併產生您對目標單元格期望的結果。
上面是官方的解釋，雖然說了一大堆，但還是不明白這個工具能做什麼<strong>（官方的解釋，都是書面的，可以直接跳過）。接下來我用案例來給大家說說這個工具的強大。做好準備哦。
準備工作
規劃求解這個工具是默認未加載的，使用前需要先加載【規劃求解加載項】，操作如下：<strong>開發工具 → 加載項 → 勾選規劃求解加載項 → 切換到數據選項卡 → 規劃求解就在最右邊了。（見下圖）
加載規劃求解
案例一：湊數。給一個結果，然後在一堆數據中找哪些數字的和等於這個結果
比如下面這個案例，在A3:A17區域有很多數據，現在要計算哪些數據相加會得到336（大家可以想想這個問題用常規方法該怎麼解決呢）。下面我們看規劃求解的解題過程：
案例1解題演示
案例1：規劃求解的參數設置
下面給大家說說規劃求解中，各約束條件的含義（約束條件是對可變單元格的值進行約束）：
<strong>解析：案例1中，我們設置的約束條件為【bin】二進制，即符合條件的數據，在可變單元格中顯示1；不符合條件的數值，在可變單元格中顯示0。
案例2：解一元方程。公式【3^x+6^x＝8^x】，求 x 的值
接下來我們看看規劃求解的解題過程，在解題前，需要參照下面圖片，將相關數據錄入表格中。
案例2國畫求解解一元方程
案例2：規劃求解參數設置
<strong>解析：案例2中，用數學基礎知識，可以判斷出X的值在1和2之間，所以，設置的x≥0.01作為約束條件，然後由電腦自己去計算。當然有些題目的答案超出Excel的數值位數（Excel裡面最大支持15位數，超過了就算不出來，比如π），就會存在解不出來的情況。比如下圖這個題目，得到的x是一個對數，轉換成數值是一個無限小數，這種是不能轉換成具體數值的，即使計算出答案了，結果也不準確。
案例3：解二元方程
題目：今有雉（雞）兔同籠，上有三十五頭，下有九十四足。問雉兔各幾何？
假設雞有x只、兔有y只，那麼可以組成以下二元一次方程組
接下來我們看看如何用規劃求解解開答案的：
案例3：解二元一次方程組
案例3：規劃求解參數設置
<strong>解析：這裡把雞、兔的數量作為變量，腳的和做為目標值，頭的和作為約條件，雞、兔的數量約束條件為非零整數，即可完成解題。
案例4：趣味填數遊戲1，要求詳見下圖
案例3：題目
此題需要用到輔助單元格（輔助單元格設置，詳見下面的操作動畫），解題過程如下：
案例4：趣味填數字遊戲1
案例4：參數設置
<strong>解析：設置輔助單元格的目的，是在約束條件中，要設定一個不重複值，且這個不重複值需要是連續區域，如果按題目中各個單元格是分開的，就無法實現這個效果。本題用的三邊之和作為目標值，用的每邊之和作為約束條件。
案例5：趣味填數遊戲2，要求詳見下圖
案例5：題目
此題的解題過程如下：
案例5：趣味填數遊戲2
案例5：規劃求解參數設置
<strong>解析：此題的九宮格由於是連續區域，故不需要輔助單元格；目標值用的是各邊之和的和（120）；約束條件有兩條，一為九宮格區域為不重複值，二是用每邊的和等於15（正常情況下，應該還有一個約束條件，即九宮格區域額值＜=9，由於設置了和為15，所以這個條件可以忽略）。
案例6：取最優組合1，題目見下圖，要求從所有可能的方案中計算出最優組合
案例6：題目
接下來我們看下解題過程：
案例6：計算最優組合1
案例6：規劃求解參數設置
<strong>解析：此題設置了三個約束條件，一為目標產量是整數，二是目標產量大於等於最低要求產量，三是原料消耗總量小於等於現有原料。
案例7：取最優組合2。
某玩具廠有三個車間，計劃生產甲、乙、丙三種玩具，要求三個車間生產同一種玩具的數量相同，並且要按照規定的時間內完成，每個車間所給的時間不一樣。其他信息如下圖所示。求能獲得最大利潤的最佳生產方案。
案例7：題目
接下來我們看解題過程：
案例7：取最優方案2
案例7：規劃求解參數設置
<strong>解析：本題的約束條件有二，一為產量為整數，二最優限時要小於等於限時。
案例8：排班，員工值班排班（要求如下）
值班要求： 1、店內有4名員工；
2、每天的營業時間是早上8:30至晚上21:30，共13小時；
3、營業高峰期：中午11:30-13:00、下午17:00-19：00，至少要有3人上班；
4、每天早上8:30至9：30位準營業時間，至少要2名員工上班；
5、每個員工每天至少工作8小時，一週內工作時長為54小時；
我們把以上數據整理成表格
案例8：題目
接下來我們看解題過程：
案例8：值班人員排班
案例8：規劃求解設置
<strong>解析：用總工時等於224（8小時×4人×7天）作為目標；排班區域用二進制，安排值班的結果為1，安排休息的結果為0，然後設置每人的工時大於等於54，每個時間段實際的人數大於等於需要的人數。
案例9：批量處理規劃求解
如下圖所示，已知單價、已知目標金額，要求計算每個單價所對應的數量，讓單價與數量之和等於目標金額。
案例9：題目
此題解其中一個的過程：
案例9解其中一題
但是本題數據較多，如果一道題、一道題的解，很浪費時間。由於此題比較有規律，就可以用VBA來實現批量解題。在VBA中使用規劃求解，需要引用Solver
引用Slover
然後用以下代碼，就可以實現批量解題：
Sub 批量執行規劃求解()
Application.AlertBeforeOverwriting = False
Dim a As Integer, Arr
Arr = Range("F4:F" & Cells(Rows.Count, 1).End(xlUp).Row)
Dim Rng1, Rng2
For a = 4 To UBound(Arr, 1) + 3
Rng1 = Range("$E$" & a).Address
Rng2 = Range("$B$" & a & ":$D$" & a).Address
SolverReset
SolverOk SetCell:=Rng1, MaxMinVal:=3, ValueOf:=Arr(a - 3, 1), ByChange:=Rng2, Engine:=1, EngineDesc:="GRG Nonlinear"
If Arr(a - 3, 1) = VBA.Int(Arr(a - 3, 1)) Then
SolverAdd CellRef:=Rng2, Relation:=4, FormulaText:="整數"
Else
SolverAdd CellRef:=Rng2, Relation:=3, FormulaText:=0.1
End If
SolverSolve Userfinish = False
Next a
Application.AlertBeforeOverwriting = True
End Sub
批量解題
以上是我收集整理的9個規劃求解應用案例。耐心看完的朋友，或許會發現這個工具在實際應用中比較方便，需要用很複雜算法來計算的內容，用這個工具直接就可以解開。如果你們以前也有用過規劃求解的案例，也請在留言區留言討論，大家一起學習。
<strong>此文中案例、數據較多，有需要此文件的朋友，可以關注我，然後通過私信發送關鍵字【規劃求解】獲取文件。（還不太會發私信的朋友，可以參考我之前分享過的文章，裡面有詳細介紹）
閱讀更多 上班下班 的文章
