|
学习学习
- Sub test1() '循环加入字典,然后循环得到结果
- Set d = CreateObject("scripting.dictionary")
- arr = [a1].CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 1) & "|" & arr(i, 2)) = d(arr(i, 1) & "|" & arr(i, 2)) & " " & arr(i, 3)
- Next
- ReDim ar(1 To d.Count, 1 To 3)
- For Each x In d.keys
- n = n + 1
- ar(n, 1) = Split(x, "|")(0)
- ar(n, 2) = Split(x, "|")(1)
- ar(n, 3) = Mid(d(x), 2)
- Next
- [h11].Resize(d.Count, 3) = ar
- End Sub
- Sub test2() '一次循环得到结果数组
- Set d = CreateObject("scripting.dictionary")
- arr = [a1].CurrentRegion
- ReDim ar(1 To UBound(arr), 1 To 3)
- For i = 2 To UBound(arr)
- t = arr(i, 1) & "|" & arr(i, 2)
- If d.exists(t) Then
- ar(d(t), 3) = ar(d(t), 3) & " " & arr(i, 3)
- Else
- n = n + 1
- d(t) = n
- ar(n, 1) = arr(i, 1)
- ar(n, 2) = arr(i, 2)
- ar(n, 3) = arr(i, 3)
- End If
- Next
- [h21].Resize(n, 3) = ar
- End Sub
- Sub test3() '非字典,数组循环,默认打卡日期是升序
- arr = [a1].CurrentRegion
- ReDim ar(1 To UBound(arr), 1 To 3)
- For i = 2 To UBound(arr)
- If arr(i, 2) = arr(i - 1, 2) Then
- ar(n, 3) = ar(n, 3) & " " & arr(i, 3)
- Else
- n = n + 1
- ar(n, 1) = arr(i, 1)
- ar(n, 2) = arr(i, 2)
- ar(n, 3) = arr(i, 3)
- End If
- Next
- [h31].Resize(n, 3) = ar
- End Sub
复制代码
|
|