|
- Sub 提速() 'by grf
- Dim SArr1, SArr, Flag As Boolean, t
- t = Timer
- SArr = Range("a15:f" & [f65535].End(xlUp).Row)
- SArr1 = Range("s15:s" & [s65535].End(xlUp).Row)
- [U15:Z65535].ClearContents
-
- Set d = CreateObject("scripting.dictionary") '第一步:把所有条件合并起来,得到每个数可以出现的全部次数
- For i = 1 To UBound(SArr1)
- tjrr = Split(Split(SArr1(i, 1), "/")(0), ",") '条件前半部分各数
- gs = Split(SArr1(i, 1), "/")(1) '条件后半部分 各数出现的次数
- For Each x In tjrr
- d(Val(x)) = d(Val(x)) & "~" & gs & "~" '每个数出现的全部次数用 "~" 相连
- Next
- Next
-
- 'For Each x In d.keys
- ' Debug.Print x, d(x)
- 'Next
-
- Set d1 = CreateObject("scripting.dictionary") '第二步:针对源数据每一行,比对每个数出现的次数是不是符合条件。
- For ii = 1 To UBound(SArr)
- d1.RemoveAll: Flag = True '默认符合条件
- For jj = 1 To UBound(SArr, 2)
- x = Val(SArr(ii, jj))
- d1(x) = d1(x) + 1
- Next
- For Each x In d1.keys
- gs = Val(d1(x))
- If InStr(d(x), "~" & gs & "~") = 0 Then '有一个条件不符合,退出循环
- Flag = False
- Exit For
- End If
- Next
- If Flag = True Then
- n = n + 1
- For jj = 1 To UBound(SArr, 2)
- SArr(n, jj) = SArr(ii, jj)
- Next
- End If
- Next
- If n > 0 Then [u15].Resize(n, 6) = SArr
- MsgBox Timer - t
- End Sub
复制代码 |
评分
-
查看全部评分
|