|
因为是固定组合,所以就用了简单循环来做。- Sub test() 'by kagawa 2014/12/8
- Dim ar, i&, i1&, i2&, i3&, i4&, k&, s, tms#
- tms = Timer
-
- Dim a1&(1 To 11)
- Dim b1$(1 To 11)
- For i1 = 1 To 11
- b1(i1) = Right(0 & i1, 2)
- Next
-
- Dim a2&(1 To 10, 2 To 11)
- ReDim b2(1 To WorksheetFunction.Combin(11, 2), 1 To 2)
- k = 0
- For i1 = 1 To 10
- For i2 = i1 + 1 To 11
- k = k + 1: a2(i1, i2) = k: b2(k, 1) = b1(i1) & " " & b1(i2)
- Next i2, i1
-
- Dim a3&(1 To 9, 2 To 10, 3 To 11)
- ReDim b3(1 To WorksheetFunction.Combin(11, 3), 1 To 2)
- k = 0
- For i1 = 1 To 9
- For i2 = i1 + 1 To 10
- For i3 = i2 + 1 To 11
- k = k + 1: a3(i1, i2, i3) = k: b3(k, 1) = b1(i1) & " " & b1(i2) & " " & b1(i3)
- Next i3, i2, i1
-
- Dim a4&(1 To 8, 2 To 9, 3 To 10, 4 To 11)
- ReDim b4(1 To WorksheetFunction.Combin(11, 4), 1 To 2)
- k = 0
- For i1 = 1 To 8
- For i2 = i1 + 1 To 9
- For i3 = i2 + 1 To 10
- For i4 = i3 + 1 To 11
- k = k + 1: a4(i1, i2, i3, i4) = k: b4(k, 1) = b1(i1) & " " & b1(i2) & " " & b1(i3) & " " & b1(i4)
- Next i4, i3, i2, i1
- ar = Range("a1").CurrentRegion
- Dim c&(1 To 5)
- For i = 1 To UBound(ar)
- s = Split(ar(i, 2))
- Erase a1
- For i1 = 0 To UBound(s)
- a1(s(i1)) = 1
- Next
- k = 0
- For i1 = 1 To 11
- If a1(i1) Then k = k + 1: c(k) = i1
- Next
-
- For i1 = 1 To 4
- For i2 = i1 + 1 To 5
- k = a2(c(i1), c(i2)): b2(k, 2) = b2(k, 2) + 1
- Next i2, i1
-
- For i1 = 1 To 3
- For i2 = i1 + 1 To 4
- For i3 = i2 + 1 To 5
- k = a3(c(i1), c(i2), c(i3)): b3(k, 2) = b3(k, 2) + 1
- Next i3, i2, i1
-
- For i1 = 1 To 2
- For i2 = i1 + 1 To 3
- For i3 = i2 + 1 To 4
- For i4 = i3 + 1 To 5
- k = a4(c(i1), c(i2), c(i3), c(i4)): b4(k, 2) = b4(k, 2) + 1
- Next i4, i3, i2, i1
- Next
- ' Debug.Print Format(Timer - tms, "0.000s")
- Sheet2.Range("a1").Resize(UBound(b2), 2) = b2
- Sheet3.Range("a1").Resize(UBound(b3), 2) = b3
- Sheet4.Range("a1").Resize(UBound(b4), 2) = b4
- MsgBox Format(Timer - tms, "0.000s")
- End Sub
复制代码 |
|