EXCEL規劃求解的簡明教程
在知乎上查了查EXCEL規劃求解的文章，的確有一些很好的應用實例，但我就有點納悶了，EXCEL的規劃求解模塊難道不是用來求解運籌學問題的專業工具嗎？怎麼沒有一篇文章提及這一點呢？
一、背景介紹
《運籌學》研究的問題一般是在若干資源有限的情況下如何找到最優的決策，比如費用最小的方案，或者花費時間最短的方案，或者利潤最大的方案等等。《運籌學》在經濟、管理、交通運輸、物流等領域得到廣泛使用，也是這些行業管理決策的核心技術。
EXCEL的規劃求解模塊是一款以可選載入項的方式隨微軟Office軟體一同發行的求解《運籌學》問題的專業軟體的免費版本，內置單純型法、對偶單純型法、分支定界演算法、廣義既約梯度演算法和演化演算法，能夠用於求解線性規劃、整數規劃和非線性規劃問題，操作簡單，求解迅速。
EXCEL的規劃求解模塊是Frontline Systems公司為通用電子表格軟體提供的插件，其產品名稱為Fontline Solvers。其目標是幫助普通用戶對優化模型進行快速求解。
官方網站為：solver
商業版的規劃求解模塊功能強大，能夠求解包含成千上萬個決策變數和約束條件的多種類型的數學模型，性能也不錯。
而免費版本在決策變數和約束條件的數量和求解時間上有如下限制：
- 最多200個決策變數
- 最多100個約束條件（包括變數上下界約束在內）
- 求解時間不超過30秒
二、載入規劃求解模塊
EXCEL的規劃求解模塊默認是不載入的，要使用規劃求解必須先使之成為默認載入，這樣每次啟動EXCEL，都會載入這個模塊。下面以EXCEL 2013版本為例，簡單演示載入的方法，其他版本大同小異。
第1步，單擊文件菜單，然後單擊左側最下面的選項按鈕。
第2步，彈出如下Excel選項對話框，然後單擊左側下面倒數第二項的載入項：
第3步，在下面的對話框中，單擊轉到按鈕
第4步，在彈出的對話框中，勾選規劃求解載入項，然後點確定按鈕，就完成了。
此時，如果你在EXCEL的工具欄上單擊數據，就可以看到如下界面。最右側出現了紅色方框中的規劃求解按鈕。這就表示載入成功了。
三、實例演示
下面我以一個簡單實例來說明如何運用EXCEL的規劃求解模塊求解線性規劃問題。
設甲產品應該生產 件，乙產品應該生產
件。這就是決策變數。
顯然，總利潤 ，
要使總利潤最大，所以目標函數就是：
對於設備A來說，每生產1件甲產品需要在設備A上加工2h，每生產1件乙產品也需要在設備A上加工2h，但設備A全天可以的加工時間最多隻有12h（即設備A的生產能力）。顯然，在生產甲乙兩個產品時設備A的實際加工時間不能超過它的最大可用加工時間，這一約束條件可以表達為：
依次類推，設備B、設備C、設備D也應當滿足這樣的約束條件。
所以完整的數學模型為：
最後一個約束表示甲乙兩種產品生產的數量要麼是0，要麼是正數，反正不能是負數。
要想用EXCEL規劃求解來求解這個數學模型，首先必須在規劃求解對話框中把決策變數、目標函數和約束條件一一輸入，然後才能求解。
第1步：輸入常數
如上圖所示，其中黃色背景的B4和C4兩個單元格是用於存放決策變數，也就是未知數，將來由規劃求解模塊來確定變數的值。
第2步：輸入目標函數的計算公式
在E4單元格輸入目標函數的計算公式如下圖所示：
計算公式為：=SUMPRODUCT(B4:C4,B5:C5)，它等價於：= B4*B5 + C4*C5
由於決策變數單元格是空白，在EXCEL中轉換成數值默認為0，所以回車之後，結果如下：
其中，SUMPRODUCT( )函數是規劃求解中使用率最高的函數，必須深入理解和牢牢掌握。下面一一詳細解釋。
在公式SUMPRODUCT(B4:C4,B5:C5)中，
B4:C4是Excel中表示單元格範圍的標準格式，可以用來表示多個單元格。範圍是一個矩形區域，而冒號前面的單元格地址應當是這個矩形區域左上角單元格的地址，冒號後面的單元格地址應當是這個矩形區域右下角單元格的地址。例如：B6:E9表示如下單元格範圍：
本題中的B4:C4是一個一行兩列的向量，存放兩種產品的生產數量，可稱為生產數量向量，用 表示。
B5:C5也是一個一行兩列的向量，存放兩種產品的單位利潤，可稱為單位利潤向量，用 表示。即：
，
，顯然總利潤
也就是說，如果兩個參數都是形狀相同的向量，SUMPRODUCT函數求出的是兩個向量的內積。如果參數超過兩個，SUMPRODUCT函數求出的給定的這些向量的內積。
如果給SUMPRODUCT的兩個參數是形狀相同的矩陣，那麼求出的是兩個矩陣的內積。不知道線性代數中是否有這個概念，但在EXCEL中就實現了這個功能，用數學公式表示如下：
假定矩陣 ，矩陣
，那麼：
熟悉線性規劃的同學，一眼就可以看出右側的式子正是常見的目標函數的形式。事實上在目標函數和約束條件的表達中，SUMPRODUCT函數確實是使用頻率最高的函數。
第3步：逐一輸入每個約束條件左側的計算公式
在單元格D6中輸入：=SUMPRODUCT($B$4:$C$4,B6:C6)
在單元格D7中輸入：=SUMPRODUCT($B$4:$C$4,B7:C7)
在單元格D8中輸入：=SUMPRODUCT($B$4:$C$4,B8:C8)
在單元格D9中輸入：=SUMPRODUCT($B$4:$C$4,B9:C9)
如果你會使用拖拽方式來複制公式，那就只需要在D6單元格中輸入公式後，將單元格D6右下角的拖放柄拖拽到單元格D9即可。這一步結束後，結果如下圖所示：
第4步：啟動規劃求解模塊，設置相關參數
首先，用滑鼠在工具欄上單擊「數據」，然後點最右側的「規劃求解」按鈕，然後在彈出的對話框中設置目標函數所在的單元格是$E$4，目標函數是求最大值，決策變數所在的單元格為$B$4:$C$4。
接下來，單擊規劃求解對話框右側的添加按鈕來添加約束條件。
這是第一個約束條件，即設備A的實際加工時間不超過它的最大可用加工時間，輸入完畢後，單擊添加按鈕，可以添加下一個約束。全部約束添加完畢後，單擊確定按鈕，結果如下：
勾選使無約束變數為非負數，即表示： ，
選擇求解方法為：單純線性規劃（由於軟體中文化得不夠專業，所以有些用詞與運籌學專業術語有一定差別）。
第5步：求解
單擊上圖對話框中的求解按鈕，即可得到如下結果：
單擊確定按鈕，即可得到最優解： ,
時，目標函數取到最大值14元。也就是說甲產品生產4件，乙產品生產2件的時候，總利潤最大，為14元。
四、總結
用EXCEL的規劃求解模塊可以輕鬆求解運籌學問題，步驟簡單，求解快速。是運籌學初學者的優秀工具，也可以幫助運籌學高手解決比較複雜，規模相對較大的實際問題，只要決策變數不超過200個，約束條件不超過100個。
EXCEL的規劃求解可以設置五種約束：不等式約束、等式約束、一般整數約束、0-1整數約束和互異整數約束。運用不等式約束和等式約束可以解決線性規劃問題，而使用一般整數約束和0-1整數約束可以分別求解整數規劃（包括混合整數規劃）、0-1整數規劃問題（例如揹包問題和指派問題）。互異整數約束是Frontline Systems公司的一個創造，用來表達若干元素的全排列形成的所有方案，然後在這些方案中尋求最優解。
所以，規劃求解模塊是求解線性規劃、整數規劃和非線性規劃的專業軟體。該軟體輕鬆易學，值得每個學習運籌學的初學者來學習，不論你是學習經濟管理、交通運輸、工程管理還是物流管理專業。該軟體操作方便，求解迅速，也適合不同行業需要使用運籌學解決實際問題的技術人員來學習。
我錄製了一個完整的EXCEL規劃求解教程，總共由8節組成，共17個視頻，總時長為244分鐘。有興趣的同學可以通過下面的鏈接來系統的學習。
EXCEL規劃求解教程-學習視頻教程-培訓課程-騰訊課堂
推薦閱讀：
