Sub 统计()
Dim d As Object
Dim ar As Variant
Dim br()
Set d = CreateObject("scripting.dictionary")
ar = Sheets("交易明细").[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To 11)
For i = 2 To UBound(ar)
If Trim(ar(i, 5)) <> "" Then
t = d(Trim(ar(i, 5)))
If t = "" Then
k = k + 1
d(Trim(ar(i, 5))) = k
t = k
br(k, 1) = k
br(k, 2) = ar(i, 5)
br(k, 3) = ar(i, 6)
End If
If Trim(ar(i, 7)) = "买入" Then
br(t, 4) = br(t, 4) + ar(i, 9)
br(t, 7) = br(t, 7) + ar(i, 11)
ElseIf Trim(ar(i, 7)) = "卖出" Then
br(t, 5) = br(t, 5) + ar(i, 9)
br(t, 8) = br(t, 8) + ar(i, 11)
End If
End If
Next i
For i = 1 To k
br(i, 6) = br(i, 4) - br(i, 5)
br(i, 9) = (br(i, 7) / br(i, 4)) * br(i, 6)
br(i, 10) = br(i, 7) - br(i, 9) - br(i, 8)
br(i, 11) = (br(i, 10) / br(i, 7))
Next i
With Sheets("统计表")
.UsedRange.Offset(3).Clear
.[a4].Resize(k, 11) = br
End With
MsgBox "ok!"
End Sub
|