本帖最后由 一把小刀闯天下 于 2020-8-2 07:13 编辑
'有点问题修改了一下,感谢wodewan 老师的提醒,,,
'--------------------------
Option Explicit
Sub test()
Dim arr, dic(2), i, j, m, key, t
For i = 0 To UBound(dic)
Set dic(i) = CreateObject("scripting.dictionary")
Next
arr = Range("a3:c" & Cells(Rows.Count, "a").End(xlUp).Row).Value
ReDim brr(1 To 10 ^ 4, 1 To 3)
For i = 1 To UBound(arr, 1)
dic(2)(arr(i, 2)) = arr(i, 3)
Next
For i = 1 To UBound(arr, 1)
If dic(2).exists(arr(i, 1)) Then
dic(1)(arr(i, 1)) = dic(1)(arr(i, 1)) & Space(1) & arr(i, 2)
Else
dic(0)(arr(i, 1)) = dic(0)(arr(i, 1)) & Space(1) & arr(i, 2)
End If
Next
For Each key In dic(0).keys
t = Split(dic(0)(key))
For i = 1 To UBound(t)
If dic(1).exists(t(i)) Then
Call dfs(dic, key, t(i), brr, m, dic(2)(t(i)))
Else
m = m + 1
brr(m, 1) = key: brr(m, 2) = t(i): brr(m, 3) = dic(2)(t(i))
End If
Next
Next
[j3].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
End Sub
Function dfs(dic, key, s, brr, m, sum)
Dim i, t, lastvalue
t = Split(dic(1)(s))
For i = 1 To UBound(t)
If dic(1).exists(t(i)) Then
lastvalue = sum
Call dfs(dic, key, t(i), brr, m, sum * dic(2)(t(i)))
sum = lastvalue
Else
m = m + 1
brr(m, 1) = key: brr(m, 2) = t(i): brr(m, 3) = sum * dic(2)(t(i))
End If
Next
End Function
|