|
楼主 |
发表于 2016-9-27 22:38
|
显示全部楼层
只要不改变单元格S15:S22条件,结果正确U:Z列只有993行,多一行或者少一行都错误
这两个正确:zhjlgaojuan 这个数组最慢, excelvlookup这个字典最快
这两个错误: 雄鹰, grf1973
目前excelvlookup最快1.5秒 , 据excelvlookup大神说,不用数组最快0.7秒,
继续努力吧,雄鹰大侠你在看看错在哪里,再加速
Sub excelvlookup()
ti = Timer
ar = Range("a15:f" & [f65535].End(xlUp).Row)
br = Range("s15:s" & [s65535].End(xlUp).Row)
ReDim cr(1 To UBound(ar), 1 To 6)
ReDim arr(1 To UBound(br), 1 To 3)
For j = 1 To UBound(br)
t1 = Split(Split(br(j, 1), "/")(0), ",")
arr(j, 1) = t1
If Len(Split(br(j, 1), "/")(1)) > 1 Then
t = Split(Split(br(j, 1), "/")(1), "~")
tx = Val(t(0))
td = Val(t(UBound(t)))
Else
tx = Val(Split(br(j, 1), "/")(1))
td = Val(Split(br(j, 1), "/")(1))
End If
arr(j, 2) = tx: arr(j, 3) = td
Next
'析分条件
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(ar)
d.RemoveAll
For j = 1 To UBound(ar, 2)
d(Val(ar(i, j))) = 0
Next
'写入字典
fa = True
'没置判断条件
For j = 1 To UBound(br)
s = 0
For Each x In arr(j, 1)
r = Val(x)
If d(r) <> "" Then
s = s + 1
End If
Next
If s < arr(j, 2) Or s > arr(j, 3) Then fa = False: Exit For
Next
'分析数据是否满足
If fa = True Then
n = n + 1
For k = 1 To 6
cr(n, k) = ar(i, k)
Next
End If
'满足条件的写入数组
Next
[U15:Z65535].ClearContents
If n > 0 Then [U15].Resize(n, 6) = cr
MsgBox Timer - ti
'写入表格,统计耗时
End Sub
如果不用字典,耗时只要0.7秒左右。
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|