[VBA] 規劃求解的實際運用 (AI解)

問題：

前幾天，LINE Meiko微課頻道 社群，奇大哥 不吝分享某個 Facebook 社團內問題的神奇解法。雖然我對數字沒啥概念，只好請出【神隊友 AI】來幫忙~

前言：

在數據導向的商業環境中，規劃求解技術是解決複雜問題的關鍵工具之一。這類技術尤其在財務和營運策略中顯得重要，其中需確定一組變量的最佳組合以達成特定目標。Excel VBA（Visual Basic for Applications）提供了一個強大的平台，使非程式設計背景的專業人士也能夠實現複雜的數據處理和自動化任務。

本文將通過一個實際例子—找出一組數值之和最接近指定目標—來探討規劃求解在實際應用中的流程和技術。

問題說明：

在這個案例中，所面對的挑戰是從 Excel 表格的 A 欄（A1 到 A16）中挑選幾個數字，使得所選數字的總和最接近 D1 單元格中的目標值（30303）。這是一個典型的優化問題，常見於財務領域，比如說配置資產組合以達到最接近預期回報的目標。

程式設計：

步驟一：讀取數據

首先，程式需要從 Excel 工作表中讀取數據。這包括從 A2 到 A16 的數據（由於 A1 是標題），以及 D1 單元格中的目標值。

步驟二：設計算法

為了解決這個問題，使用了一種稱為「回溯算法」(Backtracking)，這是一種系統地遍歷所有可能組合的方法，以找到最佳解。這個算法從第一個數字開始，逐個檢查加入或不加入該數字的結果，並根據當前的組合計算總和，進而評估其接近目標值的程度。

步驟三：處理數據類型

在 VBA 中，數字處理的默認類型是 Integer，但當處理的數值或計算的範圍超過 Integer 所能表示的範圍時，就會導致溢位錯誤。為了避免這一問題，將所有的數字變數改用 Long 類型，它提供了更大的數值範圍。

步驟四：實現並優化

實現算法後，程式會逐一試驗所有可能的數字組合，計算每一組合的總和並與目標值比較，記錄下最接近的組合和相應的差異。為了提高效率，限制了組合的數量，避免不必要的計算。

步驟五：輸出結果

當找到最佳組合後，程式會將這些數值從 A 欄複製到 E 欄，讓結果數值能夠直觀地顯示出來。

程式碼實現：

下面是實現上述功能的 VBA 程式碼示例：

Sub FindClosestSum()

Dim target As Long
Dim nums As Range, cell As Range
Dim bestSum As Long, bestDiff As Long
Dim currSum As Long, currDiff As Long
Dim i As Long, combinations As Long
Dim bestCombination As String, currCombination As String
Dim eColumnIndex As Long

' 設定目標值和數字範圍
target = Sheets("Sheet1").Cells(1, 4).Value ' D1 單元格的值
Set nums = Sheets("Sheet1").Range("A2:A16") ' A欄的範圍

' 初始化最佳差異為一個很大的數
bestDiff = 99999999

' 初始化 E 欄索引
eColumnIndex = 1


' 計算所有可能的組合
combinations = 2 ^ nums.Cells.Count - 1
If combinations >= 2147483647 Then combinations = 2147483647 ' 限制組合數量以避免溢位


For i = 1 To combinations
currSum = 0
currCombination = ""
For Each cell In nums
If (i And 2 ^ (cell.Row - nums.Row)) <> 0 Then
currSum = currSum + cell.Value
currCombination = currCombination & cell.Row & ", "
End If
Next cell


' 計算當前組合的差異
currDiff = Abs(currSum - target)


' 更新最佳解
If currDiff < bestDiff Then
bestDiff = currDiff
bestSum = currSum
bestCombination = Left(currCombination, Len(currCombination) - 2)
End If
Next i


' 將最佳組合的數值寫入 E 欄
If bestCombination <> "" Then
Dim rowIndexes As Variant
rowIndexes = Split(bestCombination, ", ")
For i = LBound(rowIndexes) To UBound(rowIndexes)
Sheets("Sheet1").Cells(eColumnIndex + 1, 5).Value = Sheets("Sheet1").Cells(rowIndexes(i), 1).Value
eColumnIndex = eColumnIndex + 1
Next i
End If


' 輸出結果
MsgBox "Best sum: " & bestSum & vbNewLine & _
"Closest difference: " & bestDiff & vbNewLine & _
"Combination of rows: " & bestCombination

End Sub

檔案下載

結論：

這個案例不僅展示了如何使用 VBA 解決具體的規劃求解問題，還反映了計算技術如何幫助我們達成所要的目標。通過這樣的實踐，專業人士可以更好地理解並利用數據，進而在複雜的商業環境中做出更精確的決策。 VBA 的這種應用不僅限於財務規劃，其在各種業務過程中的潛力是巨大的，值得每一位專業人士深入學習和掌握。

參考文獻

• 1. OpenAI - ChatGPT 4
  https://chat.openai.com/
• 2. Hello算法 - 13.1 回溯算法
  https://www.hello-algo.com/chapter_backtracking/backtracking_algorithm/

以上就是這次的分享，請持續關注 我 和 Meiko微課頻道，謝謝大家 ~

有任何問題，請到【opa的沙龍】的【Excel VBA 情境學習】一起討論有關 VBA 的問題，或加入 LINE社群 Meiko微課小綠群(粉絲交流群)，

歡迎您的加入。