如上图,根据姓名,统计每个姓名的加班次数即每个姓名出现的次数,每个姓名的加班时间即每个姓名每次加班时间之和。 本案例要统计的数据全部位于一列之中,所以我们称之为单列数据统计。 一、加班次数统计 思路:把姓名写入字典,如果字典中不存在该key,那就直接把该key写进去,item值为1,如果字典中存在该key,那就把字典中该key对应的值加1,这样得到的字典的kesy就是不重复的名字,items就是每个名字对应的加班次数。 代码如下: Sub 加班次数() Dim d As Object, arr, i% Set d = CreateObject('scripting.dictionary') arr = Range('b2', [b2].End(xlDown)) For i = 1 To UBound(arr) If Not d.exists(arr(i, 1)) Then d(arr(i, 1)) = 1 Else d(arr(i, 1)) = d(arr(i, 1)) 1 End If Next Range('e3').Resize(d.Count, 1) = Application.Transpose(d.Keys) Range('f3').Resize(d.Count, 1) = Application.Transpose(d.items) End Sub 思路很清晰,代码很好理解。 当然还可以这样写: Sub 加班次数() Dim d As Object, arr, rng, i arr = Range('b2', [b2].End(xlDown)) Set d = CreateObject('scripting.dictionary') For Each rng In arr i = d(rng) d(rng) = d(rng) 1 i = d(rng) Next [e3].Resize(d.Count, 1) = Application.Transpose(d.Keys) [f3].Resize(d.Count, 1) = Application.Transpose(d.items) End Sub 二、加班时间: 思路:把姓名和加班时间写入字典,遇到重复的就把字典中该key对应的item拿出来加上本次对应的时间即可。 代码如下: Sub 加班时间() Dim d As Object, arr, rng, i arr = Range('b2', [c2].End(xlDown)) Set d = CreateObject('scripting.dictionary') For i = 1 To UBound(arr) d(arr(i, 1)) = d(arr(i, 1)) arr(i, 2) Next [e14].Resize(d.Count, 1) = Application.Transpose(d.Keys) [f14].Resize(d.Count, 1) = Application.Transpose(d.items) End Sub |
|