|
zhouxingyu 发表于 2016-4-23 23:20
比39楼那个有大幅提高,还是很明显的.
不用字典,速度应该更快一些……因为字典的速度效率没有纯数组高- Sub test3() 'by kagawa 2016/4/24
- Dim arr, ar1() As Boolean, ar2(), br(1 To 10000, 1 To 3), dr&(9999), sr1, sr2
- Dim i&, j&, k&, m&, n&, s$, t$, tms#
-
- tms = Timer
- ' m = Range("A1").End(4).Row - 1: arr = Range("A2").Resize(m) '读取A列原始数据
- m = Range("H1").End(4).Row - 1: arr = Range("H2").Resize(m) '或读取H列原始数据
- ReDim ar1(1 To m), ar2(1 To m, 1 To 2) 'ar1标记已匹配、ar2存放匹配结果
-
- For i = 1 To m '遍历数据各行
- ' s = px(arr(i, 1)): arr(i, 1) = s '读取6位字符
- s = arr(i, 1)
- For j = 0 To 4 '产生5种5位组合(按排列顺序区分不考虑合并相同组合 即 1234 和 4321 算不同的结果)
- t = Mid(s, 2, j) & Mid(s, j + 3) '拼接为4位数字的组合t
- n = dr(t): If n = 0 Then k = k + 1: dr(t) = k: n = k: br(n, 3) = Left(s, 1) & t
- '读取数组dr组合t对应序号的顺序n、如序号不存在则新增序号n=k+1
- br(n, 1) = br(n, 1) & "," & i: br(n, 2) = br(n, 2) & "," & j
- '在序号n对应数组br中存入行信息 i 、以及提取差异字符位置 j
- Next
- Next
-
- For n = 1 To k '遍历k个字典结果 (算排列至多1万种、算排序后组合则只有715种)
- sr1 = Split(br(n, 1), ",") '拆分还原为i的数组sr1
- sr2 = Split(br(n, 2), ",") '拆分还原为j的数组sr2
- If UBound(sr1) > 1 Then '如没有匹配到其它行则略过本组合
- For j = 1 To UBound(sr1) '遍历该字典中字符组合t对应的行
- i = sr1(j) '还原行i
- If Not ar1(i) Then '如尚未有匹配标记
- ar1(i) = True '标记为已匹配
- ar2(i, 1) = br(n, 3) '组合结果写入第1列
- ar2(i, 2) = Mid(arr(i, 1), sr2(j) + 2, 1) '差异字符写入第2列
- End If
- Next
- End If
- Next
-
- MsgBox Format(Timer - tms, "0.000s ") & k & "/" & m '计算耗时/字典得到组合个数k/检查数据行数m
- Range("I2").Resize(m, 2) = "" '清空输出区域
- Range("I2").Resize(m, 2) = ar2 '结果写入工作表
- End Sub
复制代码 |
评分
-
查看全部评分
|