'你这还有行限定,加了个字典处理了一下,输出结果确实为12个,,,
Option Explicit
Sub test()
Dim arr, brr, i, j, k, m, n, cnt, s, t, dic, key, flag As Boolean
Set dic = CreateObject("scripting.dictionary")
arr = [d2:j8].Value
ReDim brr(1 To UBound(arr, 1), 1 To UBound(arr, 2)), pos(1 To 3, 1 To UBound(arr, 2))
For j = 2 To UBound(arr, 2)
If arr(1, j) > 0 Then
m = 0: n = n + 1
For i = 2 To UBound(arr, 1)
If arr(i, 1) > 0 And Len(arr(i, j)) > 0 Then
m = m + 1
brr(m, n) = arr(i, j)
End If
Next
pos(1, n) = arr(1, j): pos(2, n) = m
End If
Next
For i = 2 To UBound(arr, 1)
If arr(i, 1) > 0 Then
For j = 2 To UBound(arr, 2)
If arr(1, j) > 0 Then
s = s & "," & arr(i, j)
End If
Next
dic(s) = arr(i, 1): s = vbNullString
End If
Next
ReDim arr(1 To 10 ^ 3, 1 To n)
cnt = 1
For i = 1 To n
m = 0
Call comb(brr, arr, i, pos(1, i), pos(2, i), m)
pos(1, i) = m: pos(2, i) = 1: cnt = cnt * m
Next
ReDim brr(1 To cnt, 1 To 1) As String
cnt = 0
For i = 1 To UBound(brr, 1)
For j = 1 To n
s = s & arr(pos(2, j), j)
Next
s = s & ",": flag = True
For Each key In dic.keys
t = Split(key, ","): m = 0
For j = 1 To UBound(t)
If InStr(s, "," & t(j) & ",") Then m = m + 1
If m > dic(key) Then Exit For
Next
If m <> dic(key) Then flag = False: Exit For
Next
If flag Then
cnt = cnt + 1
brr(cnt, 1) = Mid(s, 2)
End If
s = vbNullString
If pos(2, n) < pos(1, n) Then
pos(2, n) = pos(2, n) + 1
Else
For j = n - 1 To 1 Step -1
If pos(2, j) < pos(1, j) Then
pos(2, j) = pos(2, j) + 1
For k = j + 1 To n
pos(2, k) = 1
Next
Exit For
End If
Next
End If
Next
With [s3]
.Resize(UBound(brr, 1)).ClearContents
If cnt > 0 Then .Resize(cnt) = brr
End With
End Sub
Function comb(arr, brr, p, a, b, m)
Dim i, j, n
ReDim crr(1 To UBound(brr, 1), 1 To 2)
crr(2, 1) = "," & arr(1, p)
crr(2, 2) = 1: n = 2
If crr(2, 2) = a Then m = m + 1: brr(m, p) = "," & arr(1, p)
For i = 2 To b
For j = n + 1 To 2 * n
crr(j, 1) = crr(j - n, 1) & "," & arr(i, p)
crr(j, 2) = crr(j - n, 2) + 1
If crr(j, 2) = a Then
m = m + 1: brr(m, p) = crr(j, 1)
End If
Next
n = n * 2
Next
End Function |