實際上就是透視匯總,壹般使用字典編程,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