當前位置:成語大全網 - 新華字典 - excel vba橫向循環統計相同類型多區域匯總?

excel vba橫向循環統計相同類型多區域匯總?

實際上就是透視匯總,壹般使用字典編程,VBA代碼例子如下:

代碼文本:

Option Explicit

Sub x()

Dim o, arr, i&, j&, k, t

Set o = CreateObject("Scripting.Dictionary")

'統計數據

arr = Sheets(1).UsedRange

For i = 2 To UBound(arr)

For j = 1 To UBound(arr, 2) Step 3

If arr(i, j) <> "" And arr(i, j + 1) <> "" And arr(i, j + 2) <> "" Then

k = arr(i, j) & vbTab & arr(i, j + 1) & vbTab & arr(i, j + 2)

o(k) = o(k) + 1

End If

Next j

Next i

ReDim arr(1 To 1000, 1 To 4)

i = 0

For Each k In o.keys

t = Split(k, vbTab)

i = i + 1

arr(i, 1) = t(0)

arr(i, 2) = t(1)

arr(i, 3) = t(2)

arr(i, 4) = o(k)

Next k

'輸出結果

Sheets(2).Range("b2").Resize(i, 4) = arr

End Sub