Excel VBA 学生成绩排名/班级年级双排名/SQL查询Excel表

☆本期总结☆

大家好，我用冷水泡茶。 昨天在EXCELHome论坛上看到一个帖子：

光是看着就觉得头晕。 先下载附件看一下。 数据来源是这样的：

我的理解是这样的：

1.先做一个排名。 您可以按特定科目（例如中文）或总分排名。

2. 筛选出排名在一定范围内的记录，并将其写入表中。

乍一看，要求并不复杂。 对于排名，Excel有RANK函数英语在线培训排名，按年级排名非常简单。 然而，海报要求在班级内排名。 可以使用RANK吗？ 我还没有找到好的方法，所以我必须寻找另一种排名方法。

然后，我想到了SQL，查询的时候可以排序，好像很有用。

我是这样想的，查询所有记录，按照某个字段（比如总分）降序排序，然后输出到临时表中。 对这个表进行排序，然后在其中添加一列英语在线培训排名，按类别填写序号，最后，再查询这个临时表中的记录，过滤掉一定排名范围内的记录，输出到排名表中。

我想得很仔细，但是到了实际操作的时候，我又感到头疼了。 幸运的是，有 ChatGPT。 如果你把问题丢给它，它就会不知疲倦地写代码：

好吧英语在线培训排名，我不再经历这个过程了。 最后，我明白了自己想要的是什么。 我们来感受一下：

我们看一下关键代码：

1、查询排序的SQL语句：

strSQL = "SELECT 学号, 姓名, 班别, 语文, 数学, 英语, 政治, 物理, 化学, 历史, 地理, 生物, 体育, 总分, " & _
        "(SELECT  COUNT(*) + 1 FROM [成绩表$] AS B WHERE B." & RankingBasis & " > A." & RankingBasis & " AND B.班别 = A.班别) AS 班内排名, " & _
        "(SELECT  COUNT(*) + 1 FROM [成绩表$] AS C WHERE C." & RankingBasis & " > A." & RankingBasis & ") AS 年级排名 " & _
        "FROM [成绩表$] AS A " & _
        "ORDER BY 班别 ASC, " & RankingBasis & " DESC"

代码分析：

（1）查询“成绩表”中的所有记录，添加字段“班内排名”和“年级排名”

（2）RankingBasis是一个变量，排序的依据，可以是不同科目的成绩，也可以是总成绩。

(3)“班内排名”，这里直接得到结果，逻辑其实很简单：班内所有高于当前学生成绩+1的记录数就是其排名，类似于“年级排名” 。

接下来，需要将查询结果保存到一个表中，这里是“Sheet_Rank”：

'将查询结果写入 "Sheet1_Rank" 工作表
For i = 1 To rs.Fields.Count
    rankSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
    If rs.Fields(i - 1).Name = RankingBasis Then
        p = i
    End If
Next
rankSheet.Range("A2").CopyFromRecordset rs

代码分析：

首先，通过循环记录集字段名称来填充“Sheet_Rank”的第一行。 然后判断是哪个排序字段，记录它的位置p，稍后给这一列上色。 最后，通过 CopyFromRecordset 方法将记录写入工作表。

接下来，我们来查询“SheetRank”表：

'选择排序类型，班级或年级
If Me.CombType <> "年级" Then
    strSQL = "SELECT 学号, 姓名, 班别," & RankingBasis & ",班内排名,年级排名  from [Sheet_Rank$] " _
        & "where 班内排名>=" & Me.CombBegin & " and 班内排名<=" & Me.CombEnd
Else
    strSQL = "SELECT 学号, 姓名, 班别," & RankingBasis & ",班内排名,年级排名  from [Sheet_Rank$] " _
        & "where 年级排名>=" & Me.CombBegin & " and 年级排名<=" & Me.CombEnd
End If

代码分析：根据我们选择的类型英语在线培训排名，分别构造不同的SQL查询语句。 主要区别在于 where 条件语句。

这几乎就是核心代码。 让我们简单解释一下其余部分：

1、插入一个表格，上面放一些复合框，选择输入查询条件：

Private Sub UserForm_Activate()
    Dim arrSubject(), arrNumber()
    Dim iRow As Integer, iCol As Integer
    Sheets("成绩表").Activate
    With ActiveSheet
        iRow = .UsedRange.Rows.Count
        iCol = .UsedRange.Columns.Count
        For i = 4 To iCol
            ReDim Preserve arrSubject(k)
            arrSubject(k) = Cells(1, i)
            k = k + 1
        Next
    End With
    Me.CombSubject.List = arrSubject
    ReDim arrNumber(iRow - 2)
    For i = 0 To iRow - 2
        arrNumber(i) = i + 1
    Next
    Me.CombBegin.List = arrNumber
    Me.CombEnd.List = arrNumber
    Me.CombType.List = Array("班级", "年级")
    Sheets("排名").Activate
End Sub

代码分析：这里主要是通过数组给复合框的List赋值，使用的方法不同。 有关详细信息，请参阅代码块。

2. 在“Ranking”表上插入一个命令按钮来启动“UserForm1”

Private Sub CmdRank_Click()
    UserForm1.Show
End Sub

3. 检查指定名称的工作表是否存在。 如果存在Excel VBA 学生成绩排名/班级年级双排名/SQL查询Excel表，清除内容和格式。 如果不存在，则创建它：

On Error Resume Next
Set rankSheet = ThisWorkbook.Worksheets("Sheet_Rank")
On Error GoTo 0
If rankSheet Is Nothing Then
    '创建新的工作表
    Set destSheet = ThisWorkbook.Worksheets.Add
    destSheet.Name = "Sheet_Rank"
    Set rankSheet = destSheet
Else
    '清除内容与格式
    rankSheet.Cells.Clear
End If

关于判断EXCEL工作表是否存在，可能我们之前分享过很多案例，记不太清了。 使用的方法应该是循环遍历工作表并比较工作表名称。 和这里的代码相比，如果Sheets很多的话，效率可能会差一点。

没有别的了。 完整的代码我会放在第三篇文章中。 有兴趣的朋友可以参考研究一下。

好的，今天就这样。 欢迎点赞、评论和分享。 谢谢你们。 下次见。