- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
17#
發表於 2011-7-30 10:11
| 只看該作者
回復 16# gctsai
那如果要統計的欄位不在旁邊呢
那你要跟電腦說阿 如圖
- Private Sub Ex()
- Dim D As Object, Rng As Range, f As Variant
- Set D = CreateObject("SCRIPTING.DICTIONARY") '設立字典物件
- Set Rng = Sheets("來源").[a2] '設立儲存格物件
- With Sheets("統計")
- f = Application.Match(.[b1].Text, Sheets("來源").Rows(1), 0) 'f: 在來源中尋找統計的欄位
- If IsError(f) Then MsgBox "統計的欄位不存在!!!": Exit Sub
- Do While Rng <> "" 'Rng的值為空白時不執行 Do的迴圈
- If Rng = .Range("A2") Then D(Rng.Offset(, f - 1).Value) = D(Rng.Offset(, f - 1).Value) + 1
- ' .[A2] ->Sheets("統計")[A2] '字典物件(KEY)=ITEM + 1
- Set Rng = Rng.Offset(1) 'Rng下移一列位
- Loop
- With .[B2:C2]
- .Resize(.CurrentRegion.Rows.Count, 2) = ""
- .Cells(1).Resize(D.Count) = Application.Transpose(D.KEYS)
- .Cells(2).Resize(D.Count) = Application.Transpose(D.ITEMS)
- .Resize(D.Count, 2).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
- End With
- End With
- Set D = Nothing
- Set Rng = Nothing
- End Sub
|
|
發表於 2011-8-1 22:47
| 
