Hsieh

回復 1# john2006168


    樞紐分析表最快
若純粹討論VBA

1. Sub Ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. d("ref no") = Array("ref no", "Sum of ctr", "Sum of gw", "remark")
   
4. With Sheet1
   
5. For Each a In .Range(.[A2], .[A65536].End(xlUp))
   
6.   If Not d.exists(a & a.Offset(, 3)) Then
   
7.      d(a & a.Offset(, 3)) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value)
   
8.      Else
   
9.      ar = d(a & a.Offset(, 3))
   
10.      ar(1) = ar(1) + a.Offset(, 1): ar(2) = ar(2) + a.Offset(, 2)
    
11.      d(a & a.Offset(, 3)) = ar
    
12.   End If
    
13. Next
    
14. End With
    
15. Sheet2.Columns("A:D") = ""
    
16. Sheet2.[A1].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
    
17. End Sub

複製代碼

樞紐.zip (11.75 KB)

下載次數: 17

2010-6-5 14:34

Hsieh

回復 4# john2006168


If Not d.exists(a & a.Offset(, 3)) Then'假如字典物件找不到a & a.Offset(, 3)就是d.(a & a.Offset(, 3)) 還沒建立時

d(a & a.Offset(, 3)) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value)'就賦予d.(a & a.Offset(, 3)) 的內容為一陣列

Else'否則

ar = d(a & a.Offset(, 3))'取出d.(a & a.Offset(, 3))

ar(1) = ar(1) + a.Offset(, 1): ar(2) = ar(2) + a.Offset(, 2)'改變取出的陣列元素值

d(a & a.Offset(, 3)) = ar'存回改變後的陣列給d.(a & a.Offset(, 3))