|
|
楼主 |
发表于 2023-6-8 11:43
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 yuk_yu 于 2023-6-8 11:46 编辑
我写了一个，但不是太满意，一定不是最优方案。
Option Explicit
Sub TryTest()
Dim packingQty() As Variant
Dim ShipQty() As Variant
Dim shipAddress() As Variant
packingQty = Array(100, 100, 100, 100, 100, 100, 100, 20, 50, 30, 10, 15, 25, 5, 5, 10, 10, 15, 20, 25, 30, 35)
ShipQty = Array(230, 135, 40, 110, 120, 116, 141, 35, 30)
shipAddress = Array("shanghai", "Shenzhen", "Wuhan", "Guangzhou", "ttttt", "Wuhan1", "Guangzhou1", "ttttt1", "ttttt2")
Dim result() As Variant, i As Integer
result = PackingCombinationRecursion(packingQty, ShipQty, shipAddress)
For i = 0 To UBound(result)
Debug.Print result(i)
Next i
End Sub
Sub QuickSort(ByRef arr As Variant, ByVal left As Long, ByVal right As Long)
Dim i As Long, j As Long
Dim pivot As Variant
Dim temp As Variant
i = left
j = right
pivot = arr((left + right) \ 2)
While i <= j
While arr(i) > pivot And i < right
i = i + 1
Wend
While pivot > arr(j) And j > left
j = j - 1
Wend
If i <= j Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
i = i + 1
j = j - 1
End If
Wend
If left < j Then QuickSort arr, left, j
If i < right Then QuickSort arr, i, right
End Sub
Function PackingCombinationRecursion(packingQty() As Variant, ShipQty() As Variant, shipAddress() As Variant) As Variant
Dim i As Integer, j As Integer
Dim temp As Integer
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
QuickSort packingQty, 0, UBound(packingQty)
For i = 0 To UBound(packingQty)
If (packingQty(i)) Then
dict(packingQty(i)) = dict(packingQty(i)) + 1
Else
dict.Add packingQty(i), 1
End If
Next i
Dim result() As Variant
ReDim result(0 To UBound(ShipQty))
For i = 0 To UBound(ShipQty)
temp = ShipQty(i)
Dim factors() As Variant
ReDim factors(0 To 0)
Dim k As Integer
factors = FindFactors(temp, packingQty)
If i <= UBound(shipAddress) Then
If Evaluate(Join(factors, "+")) = ShipQty(i) Then
result(i) = shipAddress(i) & ": " & Join(factors, "+") & " = " & ShipQty(i)
Else
result(i) = shipAddress(i) & ": " & Join(factors, "+") & " = " & ShipQty(i) & " is short " & ShipQty(i) - Evaluate(Join(factors, "+"))
End If
End If
Next i
PackingCombinationRecursion = result
End Function
Function FindFactors(ByVal temp As Integer, ByRef packingQty() As Variant) As Variant
Dim i As Integer, j As Integer
Dim max_factor As Integer
max_factor = 0
Dim k As Integer
For j = UBound(packingQty) To 0 Step -1
If packingQty(j) > max_factor And packingQty(j) <= temp Then
max_factor = packingQty(j)
k = j
End If
Next j
If max_factor = 0 Then
FindFactors = Array()
Exit Function
End If
temp = temp - max_factor
packingQty(k) = 0
Dim factors() As Variant
ReDim factors(0 To 0)
factors = FindFactors(temp, packingQty)
ReDim Preserve factors(0 To UBound(factors) + 1)
factors(UBound(factors)) = max_factor
FindFactors = factors
End Function
|
|
楼主: yuk_yu
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2023-6-5 11:17
