|
▼コロネ さん:
>ピボットテーブルを使わずに、集計する方法が
>分かりません。
>ピボットテーブルがベストなのでしょうか?
多分
で配列の処理です。
Sheet1にデータでSheet2に出力です。
Sub LoopSum()
Dim v1 As Variant
Dim v2 As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim col As Long
Dim eR As Long
Dim eC As Long
With Worksheets("Sheet1")
v1 = .Range("A1").CurrentRegion.Value
End With
ReDim v2(1 To UBound(v1), 1 To UBound(v1))
v2(1, 1) = v1(1, 1)
v2(1, 2) = v1(1, 2)
For i = 2 To UBound(v1)
For j = 3 To UBound(v2, 2)
If v2(1, j) = "" Then
v2(1, j) = v1(i, 3)
col = j
eC = j
Exit For
Else
If v1(i, 3) = v2(1, j) Then
col = j
Exit For
End If
End If
Next
For k = 2 To UBound(v2)
If v2(k, 1) = "" Then
v2(k, 1) = v1(i, 1)
v2(k, 2) = "計"
v2(k, col) = v1(i, 4)
eR = k
Exit For
Else
If v1(i, 1) = v2(k, 1) Then
v2(k, col) = v2(k, col) + v1(i, 4)
Exit For
End If
End If
Next
Next
With Worksheets("Sheet2")
.Cells.ClearContents
.Range("A1").Resize(eR, eC).Value = v2
End With
End Sub
|
|