上传附件要审核,先把代码附上
Sub test()
Dim mrow, mcol
Dim arr, i&, i2&, j&, pxarr, njarr, bjarr, bjbrr, bjstr, pxfs, k
tms = Timer
Application.ScreenUpdating = False
Application.EnableEvents = False
mrow = Cells(Rows.Count, 1).End(xlUp).Row
mcol = Cells(1, Columns.Count).End(xlToLeft).Column
arr = Range("a1").Resize(mrow, mcol).Value
ReDim njarr(1 To UBound(arr), 4 To UBound(arr, 2)), bjarr(1 To UBound(arr), 4 To UBound(arr, 2))
ReDim pxarr(2 To UBound(arr), 1 To 2)
For i = 4 To UBound(arr, 2)
ReDim bjbrr(1 To 100, 1 To 3)
njarr(1, i) = arr(1, i) & "年级名次"
bjarr(1, i) = arr(1, i) & "班级名次"
For i2 = 2 To UBound(pxarr) '把要排序的分数列导入并编行号
pxarr(i2, 1) = i2
pxarr(i2, 2) = arr(i2, i)
Next
Call qsort(pxarr, 2, UBound(pxarr), 1, 2, 2)
pxfs = 0
For i2 = 2 To UBound(pxarr)
If pxfs <> pxarr(i2, 2) Then
pxfs = pxarr(i2, 2)
k = i2 - 1
End If
njarr(pxarr(i2, 1), i) = k
If left(arr(pxarr(i, 1), 3), 1) = "0" Then
bjstr = Mid(arr(pxarr(i, 1), 3), 2, 1)
Else
bjstr = Mid(arr(pxarr(i, 1), 3), 1, 2)
End If
bjbrr(bjstr, 1) = bjbrr(bjstr, 1) + 1
If bjbrr(bjstr, 2) <> pxfs Then
bjbrr(bjstr, 2) = pxfs
bjbrr(bjstr, 3) = bjbrr(bjstr, 1)
End If
bjarr(pxarr(i2, 1), i) = bjbrr(bjstr, 3)
Next
Next
Range("x1").Resize(mrow, mcol - 3).Value = njarr
Range("ar1").Resize(mrow, mcol - 3).Value = bjarr
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "合成完毕!共用了" & Format(Timer - tms, "0.000秒")
End Sub
Function qsort(arr, first, last, left, right, key)
Dim i As Long, j As Long, k As Long, x As String, t As String
i = first: j = last: x = arr(Int((first + last) / 2), key)
While i <= j
While arr(i, key) > x: i = i + 1: Wend
While x > arr(j, key): j = j - 1: Wend
If i <= j Then
For k = left To right
t = arr(i, k): arr(i, k) = arr(j, k): arr(j, k) = t
Next
i = i + 1: j = j - 1
End If
Wend
If first < j Then qsort arr, first, j, left, right, key
If i < last Then qsort arr, i, last, left, right, key
End Function |