|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
这种问题,vba是最佳选择,公式即使可以实现,那么多班级,yoga数组公式,不卡就奇怪了
Sub test()
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
p = InputBox("请输入要提取的名次", , "10")
If p = "" Then Exit Sub
Application.DisplayAlerts = False
For Each sh In Sheets
If sh.Index > 2 Then sh.Delete
Next sh
Application.DisplayAlerts = True
ar = Sheets("全部考生成绩汇总").[a1].CurrentRegion
For i = 4 To UBound(ar)
If Trim(ar(i, 2)) <> "" Then
d(Trim(ar(i, 2))) = ""
End If
Next i
For Each k In d.keys
n = 0
ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
m = 0: y = 1
ReDim cr(1 To UBound(ar), 1 To 33)
For i = 4 To UBound(ar)
If Trim(ar(i, 2)) = k Then
n = n + 1
For j = 3 To UBound(ar, 2)
br(n, j - 2) = ar(i, j)
Next j
End If
Next i
For j = 3 To UBound(br, 2) Step 2
m = 0
For i = 1 To n
If Trim(br(i, j)) <> "" And br(i, j) <= Val(p) Then
m = m + 1
cr(m, y) = br(i, 1)
cr(m, y + 1) = br(i, j - 1)
cr(m, y + 2) = br(i, j)
End If
Next i
y = y + 3
Next j
Sheets("模板").Copy after:=Sheets(Sheets.Count)
Set sht = Sheets(Sheets.Count)
sht.Name = k
sht.[b1] = k
sht.[d1] = p
sht.Cells(5, 1).Resize(p + 10, UBound(cr, 2)) = cr
Next k
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
评分
-
1
查看全部评分
-
|