這篇文章以經典的求最大利潤問題，和大家分享如何使用 Excel 規劃求解。
目錄
前言開啟 Excel 規劃求解求解最大利潤問題題目簡述化成線性規劃方程組使用 Excel 求解總結
前言
如果問我去年學到什麼好用的技巧？Excel 規劃求解絕對是答案之一，尤其對去年剛開始學作業研究 (或稱運籌學、OR)，常常覺得自己怎麼解，都解不出參考答案的答案，懷疑參考答案有誤時，就可以用 Excel 檢驗到底誰是對的。(雖然事實證明多數時候都是我某個地方計算錯誤啦……)
以求解 OR 的效能或極限來說，AMPL 或 LINGO 之類的軟體可能更厲害，不過平常練習的題目通常要手算求解，所以不會太複雜，用 Excel 已足夠。而且 Excel 相對其他軟體，對一般沒有學過作業研究，但還是需要求某個狀況的最佳解(最大或最小)的人會更熟悉好用，也不用多花錢買其他軟體。
現在還記得第一次看老師示範用 Excel 求解最基礎的線性規劃問題的驚喜和恍然大悟，所以這次也想用那個問題和大家分享如何使用 Excel 的規劃求解，懂了這個範例，往後要用 Excel 處理其他的線性規劃問題都不是難事。
以下會用 Excel 2019 示範操作步驟，Excel 2016 或更早以前的版本的按鍵位置及操作方式和 2019 差不多。
開啟 Excel 規劃求解
規劃求解 (Solver) 是 Excel 的擴充功能，預設不會安裝此功能，建議先檢查自己的規劃求解功能打開了沒有，如果有，會在「資料」區右方「分析」的欄位看見「規劃求解」的按鈕；如果沒有，可以按照下方步驟開啟：
成功安裝後會看見「規劃求解」的按鈕：
這樣就可以用 Excel 的規劃求解功能了。
延伸閱讀：Add or remove add-ins in Excel
求解最大利潤問題：WYNDOR GLASS CO.
WYNDOR GLASS CO. 問題是 OR 的經典問題，聽說從我的老師學 OR 的年代以前就開始用這題向學生介紹線性規劃呢。
題目簡述
WYNDOR 玻璃公司有三個工廠，生產兩種產品，產品一每批需要經過工廠一加工 1 小時，還有工廠三加工 3 小時；產品二每批需要經過工廠二加工 2 小時，還有工廠三加工 2 小時。
已知每週工廠一到工廠三的生產上限分別是 4、12 和 18 小時，產品一每批的利潤是3000 元，產品二每批的利潤是 5000 元，試問公司每週該生產多少批產品一和產品二，以獲得最大利潤？
WYNDOR玻璃公司的資料
化成線性規劃方程組
依據題目，可以寫出下面的線性規劃方程組：
使用 Excel 求解
步驟一：輸入已知參數，保留儲存格
使用 Excel 規劃求解需要三個要素：目標式儲存格、變數儲存格和限制式。其中變數儲存格用來儲存變數的值，在本題指讓 Excel 存 和 的值的地方，剛開始輸入 0 或空白都沒有關係，如果空白表示該欄位為 0。目標式儲存格和限制式待步驟二進一步處理。
步驟二：使用 SUMPRODUCT 函數計算總和
目標式和限制式等號左邊都是一連串係數和變數的相乘相加，在使用規劃求解以前，先用 SUMPRODUCT 函數儲存計算的結果，例如下圖以 F8 儲存格代表第一個限制式 () 等號左邊的值。
如果覺得很難理解 SUMPRODUCT 函數的運算方式，也可以在 F8 儲存格輸入「=C8*C14+D8*D14」，這樣會得到一樣的結果，只是當變數和限制式數量大的時候，用 SUMPRODUCT 會方便許多。
依序把其他限制式等號左邊的值和還有目標式值也設好：
步驟三：設定規劃求解參數
先點目標式儲存格，再點「資料區」右方的規劃求解功能，畫面會跳出設定參數的視窗，這次要找最大利潤，所以選最大值，變數儲存格的則手動輸入，或選取綠色區域的部分。
接者按右方的「新增」設定限制式，以增加第一個限制式 () 為例：
輸入完限制式後，系統預設的求解方法是「GRG 非線性」，但這題想要以 Simplex Method 求解，所以改選「單純 LP」(名字翻譯得很奇怪)，再按右下方的求解。
步驟四：儲存結果
按下求解後，可以看見 Excel 算出的最佳答案是：每週生產 2 批產品一及 6 批產品二時，會有最大總獲利 36000 元，也可以看到在這個情況下三個工廠將生產 2、12 和 18 小時。
如果覺得答案沒問題，可以選取「保留規劃求解答案」，再按確定，就完成一次成功的規劃求解了！
總結
1. 「規劃求解」不是 Excel 預設的功能，第一次使用要先安裝此功能。2. Excel 規劃求解可以應用在許多地方，最常見於教科書的工廠產能分配或人員排班問題，生活中也可以用它來設計各種行銷、投資分配。
延伸閱讀：Using Solver to determine the optimal product mix
分享此文：
按一下以分享至 Facebook(在新視窗中開啟)
按一下即可分享至 X(在新視窗中開啟)
X
按一下即可以電子郵件傳送連結給朋友(在新視窗中開啟)
電子郵件