|
虽然是之前的帖子,但用来练手不错!- Sub Test()
- Dim dic As Object, d, arr, brr(), r&, n%, LastRow&
- Set dic = CreateObject("Scripting.Dictionary")
- Rem 先遍历字典汇总
- arr = ActiveSheet.UsedRange
- For r = 2 To UBound(arr)
- dic(arr(r, 1)) = dic(arr(r, 1)) + 1
- Next r
- [H2].Resize(dic.Count, 2) = Application.Transpose(Array(dic.Keys, dic.Items))
- Rem 将相同行政属地的年龄加入同一行政属地的字典中
- For Each d In dic
- For r = 2 To UBound(arr)
- If d = arr(r, 1) Then
- n = n + 1
- ReDim Preserve brr(1 To n)
- brr(n) = arr(r, 3)
- End If
- Next r
- dic(d) = brr
- n = 0: Erase brr()
- Next d
- Rem 将H列的行政地名作为字典关键字遍历其键值
- LastRow = Cells(Rows.Count, "H").End(xlUp).Row '定位合计行
- arr = Range("H1:M" & LastRow)
- For r = 2 To dic.Count + 1
- For Each d In dic(arr(r, 1))
- Select Case d
- Case 0 To 14
- arr(r, 3) = arr(r, 3) + 1
- Case 15 To 64
- arr(r, 4) = arr(r, 4) + 1
- Case 65 To 79
- arr(r, 5) = arr(r, 5) + 1
- Case Else
- arr(r, 6) = arr(r, 6) + 1
- End Select
- Next d
- arr(LastRow, 2) = arr(LastRow, 2) + arr(r, 2)
- arr(LastRow, 3) = arr(LastRow, 3) + arr(r, 3)
- arr(LastRow, 4) = arr(LastRow, 4) + arr(r, 4)
- arr(LastRow, 5) = arr(LastRow, 5) + arr(r, 5)
- arr(LastRow, 6) = arr(LastRow, 6) + arr(r, 6)
- Next r
- Range("H1:M" & LastRow) = arr
- End Sub
复制代码 |
|