hello大家好，我是城鄉(xiāng)經(jīng)濟(jì)網(wǎng)小晟來(lái)為大家解答以上問(wèn)題，線性規(guī)劃問(wèn)題的圖解，規(guī)劃求解究竟有多好用）很多人還不知道，現(xiàn)在讓我們一起來(lái)看看吧！

Excel里面，有一個(gè)很有用，但是很少被大家重視的功能：規(guī)劃求解。這個(gè)功能很多人都還不太清楚，那這個(gè)功能究竟有什么用？在什么情況下使用？該怎么用？我用九個(gè)案例給你答案。

(相關(guān)資料圖)

規(guī)劃求解是MicrosoftExcel加載項(xiàng)程序，可用于模擬分析。使用“規(guī)劃求解”查找一個(gè)單元格（稱為目標(biāo)單元格）中公式的優(yōu)化（最大或最?。┲?，受限或受制于工作表上其他公式單元格的值?！耙?guī)劃求解”與一組用于計(jì)算目標(biāo)和約束單元格中公式的單元格（稱為決策變量或變量單元格）一起工作?！耙?guī)劃求解”調(diào)整決策變量單元格中的值以滿足約束單元格上的限制，并產(chǎn)生您對(duì)目標(biāo)單元格期望的結(jié)果。

上面是官方的解釋，雖然說(shuō)了一大堆，但還是不明白這個(gè)工具能做什么（官方的解釋，都是書面的，可以直接跳過(guò)）。接下來(lái)我用案例來(lái)給大家說(shuō)說(shuō)這個(gè)工具的強(qiáng)大。做好準(zhǔn)備哦。

規(guī)劃求解這個(gè)工具是默認(rèn)未加載的，使用前需要先加載【規(guī)劃求解加載項(xiàng)】，操作如下：開(kāi)發(fā)工具 → 加載項(xiàng) → 勾選規(guī)劃求解加載項(xiàng) → 切換到數(shù)據(jù)選項(xiàng)卡 → 規(guī)劃求解就在最右邊了。（見(jiàn)下圖）

加載規(guī)劃求解

比如下面這個(gè)案例，在A3:A17區(qū)域有很多數(shù)據(jù)，現(xiàn)在要計(jì)算哪些數(shù)據(jù)相加會(huì)得到336（大家可以想想這個(gè)問(wèn)題用常規(guī)方法該怎么解決呢）。下面我們看規(guī)劃求解的解題過(guò)程：

案例1解題演示

案例1：規(guī)劃求解的參數(shù)設(shè)置

下面給大家說(shuō)說(shuō)規(guī)劃求解中，各約束條件的含義（約束條件是對(duì)可變單元格的值進(jìn)行約束）：

解析：案例1中，我們?cè)O(shè)置的約束條件為【bin】二進(jìn)制，即符合條件的數(shù)據(jù)，在可變單元格中顯示1；不符合條件的數(shù)值，在可變單元格中顯示0。

接下來(lái)我們看看規(guī)劃求解的解題過(guò)程，在解題前，需要參照下面圖片，將相關(guān)數(shù)據(jù)錄入表格中。

案例2國(guó)畫求解解一元方程

案例2：規(guī)劃求解參數(shù)設(shè)置

解析：案例2中，用數(shù)學(xué)基礎(chǔ)知識(shí)，可以判斷出X的值在1和2之間，所以，設(shè)置的x≥0.01作為約束條件，然后由電腦自己去計(jì)算。當(dāng)然有些題目的答案超出Excel的數(shù)值位數(shù)（Excel里面最大支持15位數(shù)，超過(guò)了就算不出來(lái)，比如π），就會(huì)存在解不出來(lái)的情況。比如下圖這個(gè)題目，得到的x是一個(gè)對(duì)數(shù)，轉(zhuǎn)換成數(shù)值是一個(gè)無(wú)限小數(shù)，這種是不能轉(zhuǎn)換成具體數(shù)值的，即使計(jì)算出答案了，結(jié)果也不準(zhǔn)確。

題目：今有雉（雞）兔同籠，上有三十五頭，下有九十四足。問(wèn)雉兔各幾何？

假設(shè)雞有x只、兔有y只，那么可以組成以下二元一次方程組

接下來(lái)我們看看如何用規(guī)劃求解解開(kāi)答案的：

案例3：解二元一次方程組

案例3：規(guī)劃求解參數(shù)設(shè)置

解析：這里把雞、兔的數(shù)量作為變量，腳的和做為目標(biāo)值，頭的和作為約條件，雞、兔的數(shù)量約束條件為非零整數(shù)，即可完成解題。

案例3：題目

此題需要用到輔助單元格（輔助單元格設(shè)置，詳見(jiàn)下面的操作動(dòng)畫），解題過(guò)程如下：

案例4：趣味填數(shù)字游戲1

案例4：參數(shù)設(shè)置

解析：設(shè)置輔助單元格的目的，是在約束條件中，要設(shè)定一個(gè)不重復(fù)值，且這個(gè)不重復(fù)值需要是連續(xù)區(qū)域，如果按題目中各個(gè)單元格是分開(kāi)的，就無(wú)法實(shí)現(xiàn)這個(gè)效果。本題用的三邊之和作為目標(biāo)值，用的每邊之和作為約束條件。

案例5：題目

此題的解題過(guò)程如下：

案例5：趣味填數(shù)游戲2

案例5：規(guī)劃求解參數(shù)設(shè)置

解析：此題的九宮格由于是連續(xù)區(qū)域，故不需要輔助單元格；目標(biāo)值用的是各邊之和的和（120）；約束條件有兩條，一為九宮格區(qū)域?yàn)椴恢貜?fù)值，二是用每邊的和等于15（正常情況下，應(yīng)該還有一個(gè)約束條件，即九宮格區(qū)域額值＜=9，由于設(shè)置了和為15，所以這個(gè)條件可以忽略）。

案例6：題目

接下來(lái)我們看下解題過(guò)程：

案例6：計(jì)算最優(yōu)組合1

案例6：規(guī)劃求解參數(shù)設(shè)置

解析：此題設(shè)置了三個(gè)約束條件，一為目標(biāo)產(chǎn)量是整數(shù)，二是目標(biāo)產(chǎn)量大于等于最低要求產(chǎn)量，三是原料消耗總量小于等于現(xiàn)有原料。

某玩具廠有三個(gè)車間，計(jì)劃生產(chǎn)甲、乙、丙三種玩具，要求三個(gè)車間生產(chǎn)同一種玩具的數(shù)量相同，并且要按照規(guī)定的時(shí)間內(nèi)完成，每個(gè)車間所給的時(shí)間不一樣。其他信息如下圖所示。求能獲得最大利潤(rùn)的最佳生產(chǎn)方案。

案例7：題目

接下來(lái)我們看解題過(guò)程：

案例7：取最優(yōu)方案2

案例7：規(guī)劃求解參數(shù)設(shè)置

解析：本題的約束條件有二，一為產(chǎn)量為整數(shù)，二最優(yōu)限時(shí)要小于等于限時(shí)。

值班要求： 1、店內(nèi)有4名員工；

2、每天的營(yíng)業(yè)時(shí)間是早上8:30至晚上21:30，共13小時(shí)；

3、營(yíng)業(yè)高峰期：中午11:30-13:00、下午17:00-19：00，至少要有3人上班；

4、每天早上8:30至9：30位準(zhǔn)營(yíng)業(yè)時(shí)間，至少要2名員工上班；

5、每個(gè)員工每天至少工作8小時(shí)，一周內(nèi)工作時(shí)長(zhǎng)為54小時(shí)；

我們把以上數(shù)據(jù)整理成表格

案例8：題目

接下來(lái)我們看解題過(guò)程：

案例8：值班人員排班

案例8：規(guī)劃求解設(shè)置

解析：用總工時(shí)等于224（8小時(shí)×4人×7天）作為目標(biāo)；排班區(qū)域用二進(jìn)制，安排值班的結(jié)果為1，安排休息的結(jié)果為0，然后設(shè)置每人的工時(shí)大于等于54，每個(gè)時(shí)間段實(shí)際的人數(shù)大于等于需要的人數(shù)。

如下圖所示，已知單價(jià)、已知目標(biāo)金額，要求計(jì)算每個(gè)單價(jià)所對(duì)應(yīng)的數(shù)量，讓單價(jià)與數(shù)量之和等于目標(biāo)金額。

案例9：題目

此題解其中一個(gè)的過(guò)程：

案例9解其中一題

但是本題數(shù)據(jù)較多，如果一道題、一道題的解，很浪費(fèi)時(shí)間。由于此題比較有規(guī)律，就可以用VBA來(lái)實(shí)現(xiàn)批量解題。在VBA中使用規(guī)劃求解，需要引用Solver

引用Slover

然后用以下代碼，就可以實(shí)現(xiàn)批量解題：

Sub 批量執(zhí)行規(guī)劃求解()

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:="整數(shù)"

Else

SolverAdd CellRef:=Rng2, Relation:=3, FormulaText:=0.1

End If

SolverSolve Userfinish = False

Next a

Application.AlertBeforeOverwriting = True

End Sub

批量解題

以上是我收集整理的9個(gè)規(guī)劃求解應(yīng)用案例。耐心看完的朋友，或許會(huì)發(fā)現(xiàn)這個(gè)工具在實(shí)際應(yīng)用中比較方便，需要用很復(fù)雜算法來(lái)計(jì)算的內(nèi)容，用這個(gè)工具直接就可以解開(kāi)。如果你們以前也有用過(guò)規(guī)劃求解的案例，也請(qǐng)?jiān)诹粞詤^(qū)留言討論，大家一起學(xué)習(xí)。

此文中案例、數(shù)據(jù)較多，有需要此文件的朋友，可以關(guān)注我，然后通過(guò)私信發(fā)送關(guān)鍵字【規(guī)劃求解】獲取文件。（還不太會(huì)發(fā)私信的朋友，可以參考我之前分享過(guò)的文章，里面有詳細(xì)介紹）

本文就為大家講解到這里，希望對(duì)大家有所幫助。