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

更新於 發佈於 閱讀時間約 7 分鐘

問題:

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



前言:

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

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



問題說明:

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

從 A1 ~ A16 挑選幾個數字,放入 E欄 內並加總後,才會使得 F1 和 D1 的值相等或最接近 ?

從 A1 ~ A16 挑選幾個數字,放入 E欄 內並加總後,才會使得 F1 和 D1 的值相等或最接近 ?



程式設計:

步驟一:讀取數據

首先,程式需要從 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 的這種應用不僅限於財務規劃,其在各種業務過程中的潛力是巨大的,值得每一位專業人士深入學習和掌握。



參考文獻




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

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


歡迎您的加入。

內容總結
規劃求解
5
/5
avatar-img
131會員
45內容數
Meiko微課頻道主要以辦公室應用為出發點,針對上班族群所遇到的問題進行分享
留言0
查看全部
avatar-img
發表第一個留言支持創作者!
本文介紹如何使用 ChatGPT 4 計算兩個民國日期之間的差異。同時提到了使用函數 DATEDIF 和神奇的【R鍵】來解決問題。
5/5ChatGPT4的函數設計
本文介紹如何使用 萬金油 做出輔助列,以達成下拉選單的要求。將詳細討論如何處理資料範圍變動、萬金油公式和快速新增名稱的技巧。此外,也分享了三個參考影片以供學習。
5/5萬金油公式
INDIRECT 函數一般都使用【A1樣式】來做參照,本文將揭開 INDIRECT 函數,鮮為人知的【R1C1樣式】運用方法。
5/5INDIRECT【R1C1樣式】
這篇文章介紹了一個在 Excel 中將郵遞區號加上括號的公式,並分析了公式的原理。這是一篇對於 Excel 公式拆解感興趣的人有價值的文章。
過去,在Excel中使用核取方塊時,我們常受限於其大小,一直無法輕易調整。最近的E365版本解決了這個問題,但舊版本的Excel卻沒有這個新功能。因此,在這裡,Meiko將跟同學們分享如何在新舊版本中輕鬆放大核取方塊,而且不受數量的限制。 實際操作請參考影片教學,影片中提供的語法請參考本篇文章
本篇主要是設計,當【沒有任何數值】與【原本就有數值】這兩種情況結合在一起時的 VBA 解決方案。分享內容包括張忍大師的函數解決方法。文章中包含影片檔案下載以及參考文獻連結。
本文介紹如何使用 ChatGPT 4 計算兩個民國日期之間的差異。同時提到了使用函數 DATEDIF 和神奇的【R鍵】來解決問題。
5/5ChatGPT4的函數設計
本文介紹如何使用 萬金油 做出輔助列,以達成下拉選單的要求。將詳細討論如何處理資料範圍變動、萬金油公式和快速新增名稱的技巧。此外,也分享了三個參考影片以供學習。
5/5萬金油公式
INDIRECT 函數一般都使用【A1樣式】來做參照,本文將揭開 INDIRECT 函數,鮮為人知的【R1C1樣式】運用方法。
5/5INDIRECT【R1C1樣式】
這篇文章介紹了一個在 Excel 中將郵遞區號加上括號的公式,並分析了公式的原理。這是一篇對於 Excel 公式拆解感興趣的人有價值的文章。
過去,在Excel中使用核取方塊時,我們常受限於其大小,一直無法輕易調整。最近的E365版本解決了這個問題,但舊版本的Excel卻沒有這個新功能。因此,在這裡,Meiko將跟同學們分享如何在新舊版本中輕鬆放大核取方塊,而且不受數量的限制。 實際操作請參考影片教學,影片中提供的語法請參考本篇文章
本篇主要是設計,當【沒有任何數值】與【原本就有數值】這兩種情況結合在一起時的 VBA 解決方案。分享內容包括張忍大師的函數解決方法。文章中包含影片檔案下載以及參考文獻連結。
你可能也想看
Google News 追蹤
提問的內容越是清晰,強者、聰明人越能在短時間內做判斷、給出精準的建議,他們會對你產生「好印象」,認定你是「積極」的人,有機會、好人脈會不自覺地想引薦給你
Thumbnail
商業簡報不僅僅是呈現數據,更需要深入瞭解數據分析及有效的工具運用。本文探討於Excel中使用不同函數來改善數據處理效率,包括IF、IFS、VLOOKUP、XLOOKUP及INDEX與MATCH的結合,幫助商業人士更好地從數據中提取洞見,助力業務增值,學習優化數據分析過程,讓您的商業簡報更具影響力。
Thumbnail
本文提供了一個關於模擬法演算法的問題,介紹了操作指令的格式及其解析。透過程式碼模擬每條指令,找出回到根目錄所需的操作次數。本文詳細說明瞭模擬法的複雜度分析,能夠幫助讀者更好地理解這個問題。
Thumbnail
科技發達的現代,就要善用科技化產品,來處理店裡的大小事務!排假是每個企業都會遇到的問題,該如何安排才能同時符合員工以及現場的需求?
Thumbnail
Excel好好玩VBA-菜緒 (https://portaly.cc/ezyvba) VBE輔助工具一個針對Excel VBA撰寫的輔助工具 代碼對齊排版、排序、刪空行、刪註解、簡易VBA收集(可新增、刪除)..
Thumbnail
網友提出的一個問題,如影片。 當輸入關鍵字+數量,例:起司+10 下拉式選單自動產生有關起司的產品的清單供選擇並且帶出規格、數量、金額與小計 《為什麼要做這個功能呢?》 當資料很多的時候,如果每筆資料都是用篩選的方式來找出想要的產品,可能會耗掉非常多的時間。 所以如果可以藉由關鍵字,
Thumbnail
不會寫Excel VBA不再是問題了!如何讓數據說話,是職場必備的技巧!而Excel又是搜集數據的好工具,今天石頭哥帶你透過ChatGPT 來神救援!
Thumbnail
日前在LINE社群,有網友提出一個問題,要把資料進行分析,用日期來計算出將對應的資料。 原始資料,密密麻麻的數據,都看不清楚了 放大一點點 要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。 有興趣可以下載試著挑戰看看:檔案下載 作法有很多種,當然也可以用函數處
Thumbnail
在 Excel 中,VLOOKUP 函數是一個強大的工具,它可以幫助你快速找到並擷取特定值對應的相關資訊。這篇教學將向你展示如何使用 VLOOKUP 函數來搜索數據,並提供一個實際的範例。
Thumbnail
在Dcard有人求救一個問題:想要將layer與panel的資料提出出來,如下圖。 這個題目是很經典的需求,就是多條件查找,多條件查找有蠻多種不同的解決方法,甚至版本不同解法也是天壤之別哦。 準備動作 在寫函數之前,記得要先觀察一下我們想要提取的資料有什麼樣的規則,可以發現A欄中只
Thumbnail
解決電腦上遇到的問題、證明正確性、探討效率 並且很著重溝通,說服別人你做的事是正確且有效率的。 內容: 計算模型、資料結構介紹、演算法介紹、時間複雜度介紹。
提問的內容越是清晰,強者、聰明人越能在短時間內做判斷、給出精準的建議,他們會對你產生「好印象」,認定你是「積極」的人,有機會、好人脈會不自覺地想引薦給你
Thumbnail
商業簡報不僅僅是呈現數據,更需要深入瞭解數據分析及有效的工具運用。本文探討於Excel中使用不同函數來改善數據處理效率,包括IF、IFS、VLOOKUP、XLOOKUP及INDEX與MATCH的結合,幫助商業人士更好地從數據中提取洞見,助力業務增值,學習優化數據分析過程,讓您的商業簡報更具影響力。
Thumbnail
本文提供了一個關於模擬法演算法的問題,介紹了操作指令的格式及其解析。透過程式碼模擬每條指令,找出回到根目錄所需的操作次數。本文詳細說明瞭模擬法的複雜度分析,能夠幫助讀者更好地理解這個問題。
Thumbnail
科技發達的現代,就要善用科技化產品,來處理店裡的大小事務!排假是每個企業都會遇到的問題,該如何安排才能同時符合員工以及現場的需求?
Thumbnail
Excel好好玩VBA-菜緒 (https://portaly.cc/ezyvba) VBE輔助工具一個針對Excel VBA撰寫的輔助工具 代碼對齊排版、排序、刪空行、刪註解、簡易VBA收集(可新增、刪除)..
Thumbnail
網友提出的一個問題,如影片。 當輸入關鍵字+數量,例:起司+10 下拉式選單自動產生有關起司的產品的清單供選擇並且帶出規格、數量、金額與小計 《為什麼要做這個功能呢?》 當資料很多的時候,如果每筆資料都是用篩選的方式來找出想要的產品,可能會耗掉非常多的時間。 所以如果可以藉由關鍵字,
Thumbnail
不會寫Excel VBA不再是問題了!如何讓數據說話,是職場必備的技巧!而Excel又是搜集數據的好工具,今天石頭哥帶你透過ChatGPT 來神救援!
Thumbnail
日前在LINE社群,有網友提出一個問題,要把資料進行分析,用日期來計算出將對應的資料。 原始資料,密密麻麻的數據,都看不清楚了 放大一點點 要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。 有興趣可以下載試著挑戰看看:檔案下載 作法有很多種,當然也可以用函數處
Thumbnail
在 Excel 中,VLOOKUP 函數是一個強大的工具,它可以幫助你快速找到並擷取特定值對應的相關資訊。這篇教學將向你展示如何使用 VLOOKUP 函數來搜索數據,並提供一個實際的範例。
Thumbnail
在Dcard有人求救一個問題:想要將layer與panel的資料提出出來,如下圖。 這個題目是很經典的需求,就是多條件查找,多條件查找有蠻多種不同的解決方法,甚至版本不同解法也是天壤之別哦。 準備動作 在寫函數之前,記得要先觀察一下我們想要提取的資料有什麼樣的規則,可以發現A欄中只
Thumbnail
解決電腦上遇到的問題、證明正確性、探討效率 並且很著重溝通,說服別人你做的事是正確且有效率的。 內容: 計算模型、資料結構介紹、演算法介紹、時間複雜度介紹。