|
在报表排名中,我需要在排名时不对“考试类型”的模拟一进行统计排名,请问需要如何修改代码:
Sub demo2()
Set cn = CreateObject("adodb.connection")
cn.Open "Provider=Microsoft.Ace.Oledb.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
SQL = "select Weekly,姓名,性别,班级,科目,sum(分数) from [成绩表$] group by Weekly,姓名,性别,班级,科目 order by sum(分数) desc"
[h2:m1000].ClearContents
[h2].CopyFromRecordset cn.Execute(SQL)
Set d = CreateObject("Scripting.Dictionary")
a = [h1].CurrentRegion
For i = 2 To UBound(a)
Key = a(i, 4) & a(i, 3) & a(i, 5)
d(Key) = d(Key) & " " & i
Next
b = [a1].CurrentRegion
For i = 2 To UBound(b)
Key = b(i, 2) & b(i, 3) & b(i, 4)
If Key <> p Then c = 0
c = c + 1: p = Key
If d.exists(Key) Then
r = Split(d(Key))(c)
b(i, 1) = a(r, 2): b(i, 5) = a(r, 6)
End If
Next
[a1].Resize(UBound(b), 5) = b
End Sub
成绩排名.zip
(26.59 KB, 下载次数: 2)
|
|