Posts Tagged With: 規劃求解

[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 輸入命題
VBNET WinForm 輸入命題

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

VBNET WinForm 輸出結果
VBNET WinForm 輸出結果

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

廣告
Categories: Office, 工作點滴, 技術分享 | 標籤: | 發表留言

在WordPress.com寫網誌.

%d 位部落客按了讚: