英语b级成绩查询官方网站 英语六级成绩查询入口
本文于2023年6月3日首发于本人同名公众号:Excel活学活用,更多文章案例请搜索关注!
☆本期内容概要☆
- 学生成绩排序
- 用户窗体
- SQL语句
- 插入指定名称的工作表
大家好,我是冷水泡茶,昨天在EXCELHome论坛上看到一个贴子:
看着就头晕,先下载附件看看吧,数据源是这样的:
我的理解是这样的:
1、先做一个排名,可以按具体科目,比如语文进行排名,也可以按总分进行排名。
2、把排名在一定范围内的记录筛选出来,写到一张表里。
初看一下需求并不复杂,排名的话,Excel有RANK函数,对于按年级排名那就很简单了,不过楼主要求在班级内进行排名,RANK能不能做呢?我暂时没有发现好办法,所以排名次还得另想办法。
然后,我就想到了SQL,它在查询的时候是可以排序的啊,看来有戏。
我是这样想的,查询所有记录,按一个字段降序排序,比如总分,然后输出到一张临时表里,这个表是排好序的,然后再给它加一列,按班级填充序号,最后,再查询这张临时表记录,筛选出在一定名次数字范围内的记录,输出到排名表里。
考虑得八九不离十了,但到实际操作的时候又感到头疼了,好在有ChatGPT,把问题扔给它,它会不知疲倦地写代码:
好吧,过程就不再推演了,最终是实现了我所想,来感受一下:
我们来看一下关键代码:
1、查询排序的一条SQL语句:
strSQL = &34;SELECT 学号, 姓名, 班别, 语文, 数学, 英语, 政治, 物理, 化学, 历史, 地理, 生物, 体育, 总分, &34; & _
&34;(SELECT COUNT(*) + 1 FROM [成绩表$] AS B WHERE B.&34; & RankingBasis & &34; > A.&34; & RankingBasis & &34; AND B.班别 = A.班别) AS 班内排名, &34; & _
&34;(SELECT COUNT(*) + 1 FROM [成绩表$] AS C WHERE C.&34; & RankingBasis & &34; > A.&34; & RankingBasis & &34;) AS 年级排名 &34; & _
&34;FROM [成绩表$] AS A &34; & _
&34;ORDER BY 班别 ASC, &34; & RankingBasis & &34; DESC&34;
代码解析:
(1)查询“成绩表”所有记录,并新增字段“班内排名”、“年级排名”
(2)RankingBasis 是一个变量,排序依据,可以是不同的科目或总分。
(3)“班内排名”,这里直接得出结果,逻辑其实很简单:在本班内,比当前学生的成绩大的所有记录数+1,就是它的排名,“年级排名”类似。
接着,要把查询结果存到一张表里,这里是“Sheet_Rank”:
&39;将查询结果写入 &34;Sheet1_Rank&34; 工作表
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(&34;A2&34;).CopyFromRecordset rs
代码解析:
首先,通过循环记录集字段名,把它们填到“Sheet_Rank”的第一行。接着判断排序字段是哪一个,记下它的位置p,后面会把这一列标色。最后,通过CopyFromRecordset的方法把记录写入工作表。
接下来,我们再去查询“SheetRank”这张表:
&39;选择排序类型,班级或年级
If Me.CombType <> &34;年级&34; Then
strSQL = &34;SELECT 学号, 姓名, 班别,&34; & RankingBasis & &34;,班内排名,年级排名 from [Sheet_Rank$] &34; _
& &34;where 班内排名>=&34; & Me.CombBegin & &34; and 班内排名<=&34; & Me.CombEnd
Else
strSQL = &34;SELECT 学号, 姓名, 班别,&34; & RankingBasis & &34;,班内排名,年级排名 from [Sheet_Rank$] &34; _
& &34;where 年级排名>=&34; & Me.CombBegin & &34; and 年级排名<=&34; & Me.CombEnd
End If
代码解析:根据我们选择的类型,分别构建不同的SQL查询语句,区别主要是where条件语句。
核心代码差不多就这些,其他的我们简要地说明一下:
1、插入一个窗体,上面放一些复合框,选择输入查询条件:
Private Sub UserForm_Activate()
Dim arrSubject(), arrNumber()
Dim iRow As Integer, iCol As Integer
Sheets(&34;成绩表&34;).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(&34;班级&34;, &34;年级&34;)
Sheets(&34;排名&34;).Activate
End Sub
代码解析:这里主要是通过数组给复合框的List列表赋值,采用不同的方法,详见代码块。
2、在“排名”表上插入一个命令按钮,用来启动“UserForm1”
Private Sub CmdRank_Click()
UserForm1.Show
End Sub
3、检查是否存在指定名称的工作表,如果存在,就清除内容与格式,如果不存在,就创建:
On Error Resume Next
Set rankSheet = ThisWorkbook.Worksheets(&34;Sheet_Rank&34;)
On Error GoTo 0
If rankSheet Is Nothing Then
&39;创建新的工作表
Set destSheet = ThisWorkbook.Worksheets.Add
destSheet.Name = &34;Sheet_Rank&34;
Set rankSheet = destSheet
Else
&39;清除内容与格式
rankSheet.Cells.Clear
End If
关于判断EXCEL工作表是否存在,我们前面分享的很多案例中可能有,我也记不太清楚了,采用的方法应该是循环遍历工作表,比较工作表名,跟这里的代码相比,如果Sheets很多的话,效率可能差一点。
其他也没有什么了,完整的代码我放到第三条文章,感兴趣的朋友可以参照研究。
好,今天就到这吧,欢迎点赞、留言、分享,谢谢大家,我们下期再会。
☆猜你喜欢☆
【重磅】Excel VBA 应用分享/中医诊所收费系统/Excel+ListBox版 | Excel VBA 动态添加控件/学生成绩筛选 |
Excel VBA 这样酷炫的日期控件,你不想要吗? | Excel 公式函数/数据透视表/固定资产折旧计提表! |
Excel VBA 自定义函数/数组字段定位/数组字段排序 | Excel 功能/公式函数/VBA/多种姿势处理重复值 |
Excel VBA 最简单的收发存登记系统 | Excel 公式函数/查找函数之LOOKUP |
Excel VBA 文件批量改名 | Excel 公式函数/数据验证/动态下拉列表 |
Excel VBA 输入逐步提示/TextBox+ListBox | Excel 基础功能【数据验证】,你会怎么用? |
本文于2023年6月3日首发于本人同名公众号:Excel活学活用,更多文章案例请搜索关注!