|
- Sub 提取()
- '源数据,结果数组,记录个数,临时数组
- Dim arr, result(1 To 65536, 1 To 1), lCount&, arrTemp
-
- '字典数组
- Dim a, b, arrCount()
- '取源数据
- arr = Range("g3").CurrentRegion
- '定义字典数组
- ReDim arrCount(1 To UBound(arr, 2))
- '临时字典
- Dim dic As Object
- 'Set dic = CreateObject("scripting.dictionary")
- 'Set dic2 = CreateObject("scripting.dictionary")
- For i = LBound(arr, 2) To UBound(arr, 2)
- '每列数据一个字典
- Set arrCount(i) = CreateObject("scripting.dictionary")
- Set dic = arrCount(i)
- '取指定列数据
- arrTemp = WorksheetFunction.Index(arr, 0, i)
- '统计重复个数
- For Each a In arrTemp
- If Len(a) = 0 Then Exit For
- dic(a) = dic(a) + 1
- Next
- Set dic = Nothing
- Next
- '根据重复次数统计每列符合条件的数据,另外再统计达到5次要求的。
- '临时字典,求有5次重复的数据
- For j = 2 To 6
- Set dic = CreateObject("scripting.dictionary")
- '遍历每列统计结果
- For Each a In arrCount
- For Each b In a.keys
- If a(b) = j Then
- lCount = lCount + 1
- result(lCount, 1) = "'" & b
- dic(b) = dic(b) + 1
- End If
- Next
- Next
- '遍历所有列相同数据的重复次数
- For Each a In dic.keys
- If dic(a) >= 5 Then
- lCount = lCount + 1
- result(lCount, 1) = "'" & a
- End If
- Next
- Set dic = Nothing
- Next
-
- Range("a1").Resize(lCount) = result
- MsgBox "统计完成"
- End Sub
复制代码 |
|