|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
yangyangzhifeng 发表于 2013-4-25 12:34
略微提速
下面算法
【字典转换序号定位数组比对】方法速度比你快3倍。- Sub kagawa()
-
- Dim i&, j&, k&, m&, n&, r&, s$, t, cnt&, tms#
- tms = Timer
-
- arr = Sheet1.[a1].CurrentRegion
- m = UBound(arr): n = UBound(arr, 2)
-
- ReDim brr&(2 To m, 2 To n)
- Set d = CreateObject("Scripting.Dictionary")
- For j = 2 To n
- For i = 2 To m
- t = d(CStr(arr(i, j)))
- If t = "" Then k = k + 1: d(CStr(arr(i, j))) = k: t = k
- brr(i, j) = t
- Next
- d.RemoveAll
- Next
- Set d = Nothing
-
- ReDim crr$(m, 1)
- For i = 2 To m
- For k = i + 1 To m
- cnt = 0
- For j = 2 To n
- If brr(i, j) - brr(k, j) Then cnt = cnt + 1: If cnt > 4 Then Exit For
- Next
- If cnt < 5 Then s = s & " " & k - 1 & "(" & 19 - cnt & ")"
- Next
- If Len(s) Then crr(r, 0) = i - 1: crr(r, 1) = s: s = "": r = r + 1
- Next
-
- Sheet2.[a1].CurrentRegion.Offset(1) = ""
- Sheet2.[a2].Resize(r, 2) = crr
-
- MsgBox Format(Timer - tms, "0.000s ") & "kagawa"
- End Sub
复制代码 |
|