|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
2楼代码修改如下:
- Sub 统计数()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("A1").CurrentRegion
- a = IIf(UBound(arr) - 50 < 0, 1, UBound(arr) - 49)
- For i = a To UBound(arr)
- For j = 1 To UBound(arr, 2) - 2
- For j2 = j + 1 To UBound(arr, 2) - 1
- For j3 = j2 + 1 To UBound(arr, 2)
- ss = arr(i, j) & "," & arr(i, j2) & "," & arr(i, j3)
- If arr(i, j) <> "" And arr(i, j2) <> "" And arr(i, j3) <> "" Then
- d(ss) = d(ss) + 1
- End If
- Next
- Next
- Next
- Next
- k = d.keys
- t = d.items
- For Each n In t: d1(n) = 1: Next '次数去重
- x = Application.Max(t) '最多
- y = Application.WorksheetFunction.Large(d1.keys, 2) '次多
- Z = Application.WorksheetFunction.Large(d1.keys, 3) '三多
-
- ReDim brr(1 To d.Count, 1 To 3)
- n1 = 1: n2 = 1: n3 = 1
- For i = 0 To UBound(t)
- If t(i) = x Then
- n1 = n1 + 1
- brr(n1, 1) = k(i)
- End If
- If t(i) = y Then
- n2 = n2 + 1
- brr(n2, 2) = k(i)
- End If
- If t(i) = Z Then
- n3 = n3 + 1
- brr(n3, 3) = k(i)
- End If
- Next
- brr(1, 1) = "出现" & x & "次,共" & n1 - 1 & "组": brr(1, 2) = "出现" & x - 1 & "次,共" & n2 - 1 & "组": brr(1, 3) = "出现" & x - 2 & "次,共" & n3 - 1 & "组"
- Columns("X:Z").ClearContents
- [x1].Resize(n3, 3) = brr
- End Sub
复制代码 |
|