|
不用字典的确提高一些。- Dim xs
- Sub tq()
- tt = Timer
- Set d = CreateObject("scripting.dictionary")
- 'arr = Range("a2:a" & [a65536].End(3).Row)
- arr = Range("a2:a2000")
- ReDim brr(1 To UBound(arr), 1 To 3)
- For i = 1 To UBound(arr) - 1
- x = arr(i, 1)
- For j = i + 1 To UBound(arr)
- y = arr(j, 1)
- If brr(i, 1) = "" And brr(j, 1) = "" Then '两原数均未匹配
- If ISOK(x, y) Then '看两原数是否相似数,有则匹配
- xrr = Split(xs, ",")
- brr(i, 1) = xrr(0): brr(i, 2) = xrr(1): brr(i, 3) = y
- brr(j, 1) = xrr(0): brr(j, 2) = xrr(2): brr(j, 3) = x
- i = i - 1 '指针上移,拿匹配数对照
- Exit For
- End If
- ElseIf brr(i, 1) <> "" And brr(j, 1) = "" Then '上数已有匹配数,拿匹配数对照
- p = brr(i, 1)
- If ISGN(p, y) Then brr(j, 1) = p: brr(j, 2) = xs: brr(j, 3) = x
- End If
- Next
- Next
- [b2].Resize(UBound(arr), 3) = brr
- MsgBox Timer - tt
- End Sub
- Function ISOK(x, y) As Boolean '两原数x,y是否相似,并返回x,y的相似数xs
- xs = ""
- For i = 2 To Len(x) 'x的各归纳数相连
- p = Left(x, i - 1) & Mid(x, i + 1)
- xs = xs & p
- Next
-
- For i = 2 To Len(y)
- p = Left(y, i - 1) & Mid(y, i + 1) 'y的各归纳数
- If InStr(xs, p) Then 'y的各归纳数在x的各归纳数相连中,说明匹配
- ISOK = True
- xx = (InStr(xs, p) - 1) / 5 + 2 '对应x去掉数的位置
- Exit For
- End If
- Next
-
- If ISOK Then
- xs = p & "," & Mid(x, xx, 1) & "," & Mid(y, i, 1) 'Like "T5689,3,7",第一位为相似数,第二位为第一个不同数,第三位为第二个不同数
- End If
- End Function
- Function ISGN(p, y) As Boolean '归纳数p是否区配原数y,并返回y中该去掉的数
- For i = 2 To Len(y)
- q = Left(y, i - 1) & Mid(y, i + 1)
- If p = q Then ISGN = True: Exit For
- Next
- If ISGN Then xs = Mid(y, i, 1)
- End Function
复制代码 随机取的数值,大多匹配度不高,2000条在7秒左右。如果匹配度高的话速度可以提高很多。 |
评分
-
查看全部评分
|