[Excel]白做工了，規劃求解與 VBA

很久沒有做分析了，都在 code …

上個月老闆交代我做個分析，想來想去，想要最佳化還是用數學規劃，線性規劃是個快速解決問題的方法，只要問題不複雜，直接用線性規劃求解就可以了。

問題是離開學校後，沒有好的軟體可以算線性規劃，想起來還有 Excel 的規劃求解可以用，就翻開 Excel 玩。

話說 2005 年在新加坡 MVP亞洲年會中，跟當時負責中文化的洪士吉老師與這個巨集的澳洲原作者聊過，當時聽洪士吉老師有原始碼跟密碼，我那時自己也用 VBScript 寫一個線性規劃的 ASP 線上網頁，所以主要討論的是卡曼卡法的加速解跟 Lindo，但是我論文不是搞這方向，離開學校後就甩到邊邊角角了。

我平常為了加速 Excel 開啟，所有的增益集都關閉，Office 2003 年代短暫的玩過規劃求解，但主要還是玩 Lindo ，開啟規劃求解增益集後，發覺設定方式完全不一樣了，看了一下線上手冊，上面寫 Excel 2010 後改成這樣，只好重新學習。

首先，先把自己寫的線性規劃翻出來，用相同的模型建構 Excel ，自己會比較容易上手：

線性規劃命題範例

按下 [進行最佳化分析] 後，可得到計算結果：

線性規劃計算結果

這個命題是在做教材範例，假定一個有閘門的滯洪池或水庫，在已知預報進水量下，受到滯洪池蓄洪量、放水路放水等限制，目標是穩定洩水減低洪害，各限制式的依據參閱上面網頁擷圖右側文字說明。

下面是我喜歡的規劃求解表格樣式，規劃求解仍然使用 Excel 公式，所以並沒有需要排得整整齊齊，但是為了人類容易閱讀，還是排成表格方式比較不會錯，詳細個位置說明，放到最後，先看操作步驟：

在 Sheet 上建立規劃求解命題

從 選單 點選 [資料] > [規劃求解] ，逐項設定：

規劃求解設定

規劃求解計算約 1 秒內可完成：

規劃求解完成

計算結果與設定對話盒連結註記說明：

規劃求解計算完成

1. 圖上數字對應到 [規劃求解參數] 對話盒的數字，並不對應到這邊說明的項次。
2. 變數名我選擇放在第一列，C1 ~ J1 ，這種架構大家都容易看得懂。
3. 變數計算結果我選擇放在第二列，C2 ~ J2，預設可以不輸入，參見前面建立命題的圖，不輸入，就代表起始解為 {0} 集合。
4. 目標函數的係數我放在 C3 ~ J3，也就是 MIN C。
5. 限制式左側的係數我放在 C4 ~ J19 ，不等號我放在 M4 ~ M19，常數項我放在 N4 ~ N19。
6. 最佳化可以在 [規劃求解參數] 輸入 Excel 公式，但這樣會造成每次設定的複雜化，比如說目標函數一變，或是限制式調整時， [規劃求解參數] 的設定就要跟著變，所以為了方便起見，建議像我這樣做，在 K3 輸入陣列公式，讓係數乘變數，就可以降低 [規劃求解參數] 的設定問題，K3 正確設定完成後，複製到 K4 ~ K19 ，讓限制式的係數一樣可以乘變數。注意，陣列公式輸入完成後，要按下 {Shift}+{Ctrl}+{Enter} 三個鍵，才能正確變成陣列公式。
7. 最後在 [規劃求解參數] 對話盒 [載入/儲存] 按鈕，點下 [儲存] ，選擇 N2 。

這個計算結果，可以透過 K/L/M 三欄檢查限制式是否成立，或是直接觀看 N 欄，限制式成立的邏輯公式就是使用 K/L/M 三欄。

在說明項目 6 中，使用了陣列公式，實務上會增加計算量，也就是係數 0 x 變數，但卻可以讓設定參數簡易許多，就如同本例。

做到這，可以發現 L 欄用不到，因為在 [規劃求解參數] 對話盒 [新增] 限制式時，都要手動輸入，我的目的是要解決老闆交付的任務，所以針對此命題範例建立了測試的 VBA 程式碼，細節到正式命題再修改：

用 VBA 建立規劃求解參數

為了方便起見，我在 VBA 內直接建立陣列公式，也就是說上面建立規劃求解的途中，K 欄不用輸入，其他 [規劃求解參數] 對話盒的輸入，都用 VBA 自動產生。

規劃求解不支援直接輸入不等式，所以用 GetRelationIndex 函數將不等式轉成索引值輸入。

如果不想自己打太多程式碼，可以用錄製的再進行修改，不過這張圖上的程式碼已經很精簡了，錄製會很亂，不如直接打，再改欄位值即可。

規劃求解的函數使用說明參考微軟官方網址：https://docs.microsoft.com/zh-tw/office/vba/excel/concepts/functions/using-the-solver-vba-functions

好了，終於開始建立正式命題，先把正式命題縮小為一天24小時的時資料進行連續量分析，建個小小的模型，測試看看是不是能正常運作：

規劃求解超過上限

不得不爆粗口…

「規劃求解限定為 200 個變數儲存格和 100 個限制式，加上變數儲存格的限值。」

100 個限制式？這個規格限制下，根本是玩具吧… 規劃求解，我只好繼續把你放生了…

[2019/06/05]

花了點時間，把網頁版 ASP 的 VBScript 轉成 VBNET 語法，順便把命題轉成類別，測試還算成功。

VBNET WinForm 輸入命題

輸入完線性規劃命題後，點選 [限制式] 頁籤 下方工具列那個圖示 (測試程式沒有管 Icon 的美觀度) ，即可得到輸出結果，輸出改用 StringBuilder 產生 HTML 語法用 WebBrowser Control 輸出，這樣之後若有需要，可以在工具列放預覽列印的按鈕，呼叫 WebBrowser Control 的預覽列印。

VBNET WinForm 輸出結果

不過裡面還有一些 Bug ，大型命題解不出來，這就造成除錯困難，大概還要加一頁來輸出線性規劃各步驟階段的計算表格，才好除錯。