|
Sub 多条件汇总()
Application.ScreenUpdating = False
Dim ar As Variant
Dim i As Long, r As Long, rs As Long
Dim br()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("动态工作量查询")
r = .Cells(Rows.Count, 4).End(xlUp).Row
If r < 9 Then MsgBox "动态工作量查询为空!": End
ar = .Range("d8:o" & r)
End With
ReDim br(1 To UBound(ar), 1 To 100)
br(1, 1) = "序号"
br(1, 2) = "器具" & Chr(10) & " 姓名"
k = 1: y = 2
For i = 2 To UBound(ar)
If ar(i, 1) <> "" And ar(i, 2) <> "" Then
t = d(ar(i, 1))
If t = "" Then
k = k + 1
d(ar(i, 1)) = k
t = k
br(k, 1) = k - 1
br(k, 2) = ar(i, 1)
End If
lh = d(ar(i, 2))
If lh = "" Then
y = y + 1
d(ar(i, 2)) = y
lh = y
br(1, y) = ar(i, 2)
End If
br(t, lh) = br(t, lh) + ar(i, 7)
End If
Next i
y = y + 1
br(1, y) = "实物工作量合计"
br(1, y + 1) = "系数工作量合计"
br(1, y + 2) = "姓名"
br(1, y + 3) = "系数排名"
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
t = d(ar(i, 1))
br(t, y) = br(t, y) + ar(i, 7)
br(t, y + 1) = br(t, y + 1) + ar(i, 8)
br(t, y + 2) = ar(i, 1)
End If
Next i
With Sheet4
.UsedRange.Offset(5) = Empty
.[d6].Resize(k, y + 3) = br
For i = 7 To k + 5
.Cells(i, y + 6).FormulaR1C1 = "=RANK(RC[-2],R7C10:R" & k + 5 & "C10)"
Next i
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|