好久的贴子了,再发点字典与数组的学习吧: 设当前表的A列为姓名,B列为部门,C到F列为数值,数据在3-12行。现需按部门进行分类汇总,有以下两种写法(方法二为模仿oobird): Sub test1() '示字典的item为数组的用法: Dim d As New Dictionary, arr1(), i As Integer For i = 3 To 12 If d.Exists(Cells(i, 2).Value) Then arr1 = d(Cells(i, 2).Value) End If ReDim Preserve arr1(1 To 4) arr1(1) = arr1(1) + Cells(i, 3) '这四行也可用循环,不过据说循环次数不多从速度上来说还不如不用 arr1(2) = arr1(2) + Cells(i, 4) arr1(3) = arr1(3) + Cells(i, 5) arr1(4) = arr1(4) + Cells(i, 6) d(Cells(i, 2).Value) = arr1 Erase arr1 Next [a20].Resize(d.Count, 4) = Application.Transpose(d.Keys) '写入部门 [b20].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.Items)) '写入汇总结果 End Sub Sub test2() '字典起到记录一部门在数组中位于哪列的作用 Dim d As New Dictionary, arr1(), i As Integer, m As Integer m = 0 For i = 3 To 12 If Not d.Exists(Cells(i, 2).Value) Then m = m + 1 d(Cells(i, 2).Value) = m ReDim Preserve arr1(1 To 4, 1 To m) End If arr1(1, d(Cells(i, 2).Value)) = arr1(1, d(Cells(i, 2).Value)) + Cells(i, 3) arr1(2, d(Cells(i, 2).Value)) = arr1(2, d(Cells(i, 2).Value)) + Cells(i, 4) arr1(3, d(Cells(i, 2).Value)) = arr1(3, d(Cells(i, 2).Value)) + Cells(i, 5) arr1(4, d(Cells(i, 2).Value)) = arr1(4, d(Cells(i, 2).Value)) + Cells(i, 6) Next [a24].Resize(d.Count, 4) = Application.Transpose(d.Keys) [b24].Resize(d.Count, 4) = Application.Transpose(arr1) End Sub
以上两种写法中,如果从速度上来说,均可先将表中数据存入一数组,然后从数组中取数,而不是从表中取数还要快得多
[此贴子已经被作者于2008-1-21 14:32:10编辑过] |