公式不通用,数据多了,就失效了!- Sub ek_sky()
- Dim arr1, arr2(1 To 10000, 1 To 6), PK
- Dim i&, j&
- Dim d1 As New Dictionary
- With Sheets("成绩统计表")
- arr1 = .Range("A3:F" & .Cells(3, 1).End(4).Row)
- End With
- Application.ScreenUpdating = False
- For i = 1 To UBound(arr1)
- PK = arr1(i, 2) & arr1(i, 3) & arr1(i, 4) & arr1(i, 5)
- If d1.Exists(PK) Then
- arr2(d1(PK), 5) = arr2(d1(PK), 5) + arr1(i, 6)
- arr2(d1(PK), 6) = arr2(d1(PK), 6) & "," & arr1(i, 1)
- Else
- j = j + 1
- d1.Add PK, j
- arr2(j, 1) = arr1(i, 2): arr2(j, 2) = arr1(i, 3)
- arr2(j, 3) = arr1(i, 4): arr2(j, 4) = arr1(i, 5)
- arr2(j, 5) = arr1(i, 6): arr2(j, 6) = arr1(i, 1)
- End If
- Next i
- Application.ScreenUpdating = True
- With Sheets("成绩查询")
- .Range("A:F").ClearContents
- .Range("A1:F1") = Array("专业类", "姓名", "性别", "来源", "总分", "行号")
- .Range("A2").Resize(d1.Count, 6) = arr2
- End With
- End Sub
复制代码
求助.zip
(11.45 KB, 下载次数: 4)
|