|
Sub 统计()
Dim d As Object
Dim ar As Variant
Dim br()
Dim i As Integer
Set d = CreateObject("scripting.dictionary")
With Sheet1
r = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("b3:g" & r).Sort Key1:=.Range("e3"), Order1:=xlAscending
ar = .Range("b3:g" & r)
End With
ReDim br(1 To UBound(ar), 1 To UBound(ar) * 2)
br(1, 1) = "序号": br(1, 2) = "姓名": br(1, 3) = "编号"
k = 2: y = 3
For i = 2 To UBound(ar)
If Trim(ar(i, 2)) <> "" Then
t = d(Trim(ar(i, 2)))
If t = "" Then
k = k + 1
d(Trim(ar(i, 2))) = k
t = k
br(k, 1) = k - 2
br(k, 2) = ar(i, 1)
br(k, 3) = ar(i, 2)
End If
zf = Trim(ar(i, 4)) & "|" & Trim(ar(i, 5))
If Not d.exists(zf) Then
y = y + 1
br(1, y) = ar(i, 4)
br(2, y) = ar(i, 5)
br(t, y) = ar(i, 6)
d(zf) = y
ElseIf Not d(zf) Then
m = d(zf)
br(t, m) = br(t, m) + ar(i, 6)
End If
End If
Next i
With Sheets("统计")
.UsedRange.Clear
.[a1].Resize(k, y) = br
.[a1].Resize(k, y).Borders.LineStyle = 1
End With
MsgBox "ok!"
End Sub |
|