如上图,根据姓名,统计每个姓名的加班次数即每个姓名出现的次数,每个姓名的加班时间即每个姓名每次加班时间之和。
本案例要统计的数据全部位于一列之中,所以我们称之为单列数据统计。
一、加班次数统计
思路:把姓名写入字典,如果字典中不存在该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
联系客服