|
使用分级字典进行重复统计,然后仅使用数组而不再逐行使用字典的输出统计结果方法代码。
当数据量较大时,节约时间的优势明显。
- Sub test2()
- Dim arr, d, kr, sr, tr, i&, j&, k&, m&, n&, t$, t1$, t2$
-
- m = [e65536].End(3).Row - 1
- arr = [e2].Resize(m)
-
- Set d = CreateObject("scripting.dictionary")
- For i = 1 To m
- t = arr(i, 1): t1 = Left(t, 6): t2 = Mid(t, 7)
- If Not d.Exists(t1) Then Set d(t1) = CreateObject("scripting.dictionary")
- d(t1)(t2) = d(t1)(t2) & "," & i '在相同身份证号码的二级字典项目中 记录各个行序号。
- Next
-
- kr = d.keys '读取分级字典keys 即按省市区码分类
- For i = 0 To UBound(kr)
- tr = d(kr(i)).items
- '读取同一个第1级字典key下面各个具体身份证对应的2级字典项目(实为重复项的行序号记录)
- For j = 0 To UBound(tr)
- sr = Split(tr(j), ",") '行序号记录tr拆分为数组sr
- n = UBound(sr) '此即为重复次数
- For k = 1 To n
- arr(sr(k), 1) = n '按照行序号记录写入对应重复次数
- Next
- Next
- ' kr(i) = kr(i) & " = " & UBound(tr) + 1 '统计分级字典使用情况
- Next
- ' MsgBox Join(kr, vbCr)
- '
- [f2].Resize(m) = ""
- [f2].Resize(m) = arr
- End Sub
复制代码
|
评分
-
查看全部评分
|