- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 97
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-4-26
              
|
回復 1# john2006168
樞紐分析表最快
若純粹討論VBA- Sub Ex()
- Set d = CreateObject("Scripting.Dictionary")
- d("ref no") = Array("ref no", "Sum of ctr", "Sum of gw", "remark")
- With Sheet1
- For Each a In .Range(.[A2], .[A65536].End(xlUp))
- If Not d.exists(a & a.Offset(, 3)) Then
- d(a & a.Offset(, 3)) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value)
- Else
- ar = 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
- End If
- Next
- End With
- Sheet2.Columns("A:D") = ""
- Sheet2.[A1].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
- End Sub
 樞紐.zip (11.75 KB)
|
|
發表於 2010-6-5 14:35
| 