本帖最后由 张雄友 于 2015-11-2 19:12 编辑
求多列数据交集,已有效果,但没有数据出来。- Option Explicit
- Sub 多列交集()
- Dim arr, brr, i&, j&, m&, n&, tms#, tmp$, s As Boolean, k&
- tms = Timer
- arr = Range("A2:G17")
- m = UBound(arr)
- n = UBound(arr, 2)
- ReDim brr(1 To m, 1 To 1)
- For i = 1 To m
- s = True
- tmp = arr(i, 1)
- For j = 2 To n
- If tmp <> arr(i, j) Then s = False: Exit For
- Next
- If s Then brr(i, 1) = tmp: k = k + 1
- Next
- [I2].Resize(Rows.Count - 1, 1).ClearContents: [I2].Resize(UBound(brr), 1) = brr
- MsgBox Format(Timer - tms, "0.00s ") & Chr(10) & "共找到" & Chr(10) & k & "个交集!"
- End Sub
复制代码
换个思路 - Sub 多列交集()
- Dim arr, i&, j&, tms#, k&, d, x
- tms = Timer
- arr = Range("A2:G17")
- Set d = CreateObject("scripting.dictionary")
- For j = 1 To UBound(arr, 2)
- For i = 1 To UBound(arr)
- x = arr(i, j)
- If Val(d(x)) = j - 1 Then d(x) = j '如果本列d(x)的值为列数-1,则d(x)取值为本列数
- Next
- Next
- For Each x In d.Keys
- If Len(x) = 0 Or d(x) < UBound(arr, 2) Then d.Remove x '如果d(x)不等于总列数(说明不是每列都出现过,去掉)
- Next
- [J2].Resize(Rows.Count - 1, 1).ClearContents
- [J2].Resize(d.Count) = Application.Transpose(d.Keys)
- MsgBox Format(Timer - tms, "0.00s ") & Chr(10) & "共找到" & Chr(10) & d.Count & "个交集!"
- End Sub
复制代码
|