本文于2023年9月23日首发于本人同名其他平台！
内容提要
学生成绩统计分析
大家好，我是冷水泡茶，今天在EXCELHOME论坛上看到一个求助贴：
如何根据原始表中分数和参数表中项目设置学科分数段生成目标表分数从高到低？
看到这个标题，都感觉头晕，先下来看看再说吧：
原始表：记录有500多条，当然，记录多与少基本没什么影响。
参数表：参数有点多啊。
目标表：标题也很复杂。
关于排名，我们前面分享过一期【Excel VBA 学生成绩排名（更新）/SQL循环查询/嵌套查询】，感觉应该能参考着用，于是就动手做了起来，基本完工，分享给大家：
基本思路
1、基本方法还是采用SQL查询来做。
2、我们把“参数表”装入数组arrRef。
3、循环arrRef，根据条件，循环参数表G列班级，再根据E列、F列的分数区间，编写查询语句。
4、执行查询语句，依次把每个项目、每个班级的分数统计情况明细写入目标表。
5、在循环班级的过程中，把每个班级的人数连成一个字符串，写入“参数表”I列。
6、把“参数表”I列文本中，括号内的数字标红。
VBA代码
1、在模块1里，process过程：
Sub process() Dim wsRef As Worksheet, wsTarg As Worksheet Dim arrRef(), arr() Dim lastRow As Integer, lastCol As Integer, iRow As Integer Dim startScore As Single, endScore As Single Dim strClass As String, arrStr() As String Dim Subject As String Dim conn As Object Dim rs As Object, rsCount As Integer Dim strSQL As String Dim dataFile As String Dim rng As Range, text As String, starePos As Integer, endPos As Integer On Error Resume Next Set wsRef = Sheets("参数表") Set wsTarg = Sheets("目标表") wsTarg.Cells.Clear With wsRef lastRow = .UsedRange.Rows.Count lastCol = .UsedRange.Columns.Count .Range("I2:J" & lastRow).ClearContents arrRef = .Range(.Cells(2, 1), .Cells(lastRow, lastCol)).Value End With dataFile = ThisWorkbook.FullName connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataFile & ";Extended Properties=""Excel 12.0;HDR=Yes;""" Set conn = CreateObject("ADODB.Connection") Set rs = CreateObject("Adodb.recordset") conn.Open connString For i = 1 To UBound(arrRef) If arrRef(i, 8) = "是" Then Subject = arrRef(i, 2) strClass = arrRef(i, 7) startScore = arrRef(i, 5) endScore = arrRef(i, 6) strClass = Replace(strClass, "，", ",") arrStr = Split(strClass, ",") arrRef(i, 9) = "" For m = LBound(arrStr) To UBound(arrStr) strClass = arrStr(m) strSQL = "SELECT null as 序号,班级, 学号, 姓名, " & Subject & " as 分数, " & _ "(SELECT COUNT(*) + 1 FROM [原始表$] AS B WHERE B." & Subject & " > A." & Subject & " AND B.班级 = A.班级) AS 班排, " & _ "(SELECT COUNT(*) + 1 FROM [原始表$] AS C WHERE C." & Subject & " > A." & Subject & ") AS 级排 " & _ "FROM [原始表$] AS A " & _ "ORDER BY " & Subject & " DESC" strSQL = "select * from (" & strSQL & ") where 班级=" & strClass & " and 分数 between " & startScore & " and " & endScore Set rs = conn.Execute("select count(*) from (" & strSQL & ")") arr = rs.getrows rsCount = arr(0, 0) rs.Close arrRef(i, 9) = arrRef(i, 9) & strClass & "（" & rsCount & "），" arrRef(i, 10) = "已完成" wsRef.Cells(i + 1, 10) = arrRef(i, 10) If rsCount > 0 Then Title = "项目（" & arrRef(i, 1) & "）班级（" & strClass & "）学科（" & Subject & "）分数段（" & startScore & "-" & endScore & "）人数（" & rsCount & "）" Set rs = conn.Execute(strSQL) iRow = iRow + 1 With wsTarg .Activate .Cells(iRow, 1).Resize(, 7).Select With Selection .Merge .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Font.Size = 12 End With .Cells(iRow, 1) = Title iRow = iRow + 1 For j = 1 To rs.Fields.Count .Cells(iRow, j).Value = rs.Fields(j - 1).Name Next iRow = iRow + 1 .Range("A" & iRow).CopyFromRecordset rs For n = 1 To rsCount .Cells(iRow + n - 1, 1) = n Next Set rng = .Range(.Cells(iRow - 1, 1), Cells(iRow - 1 + rsCount, 7)) rng.Font.Size = 10 With rng.Borders .LineStyle = xlContinuous .Weight = xlThin End With iRow = iRow + rsCount + 1 End With End If Next arrRef(i, 9) = Left(arrRef(i, 9), Len(arrRef(i, 9)) - 1) Set rng = wsRef.Cells(i + 1, 9) rng.Value = arrRef(i, 9) text = rng.Value startPos = InStr(text, "（") + 1 endPos = InStr(text, "）") - 1 Do While startPos > 0 And endPos >= startPos rng.Characters(startPos, endPos - startPos + 1).Font.Color = RGB(255, 0, 0) startPos = InStr(startPos + 2, text, "（") + 1 endPos = InStr(endPos + 2, text, "）") - 1 Loop Else arrRef(i, 9) = "" arrRef(i, 10) = "" End If Next wsRef.Activate rs.Close conn.Close Set rs = nothine Set conn = Nothing MsgBox "Done!"End Sub
代码解析：
（1）Line2~12，定义一批变量，数组、单元格区域、工作表对象、数据库连接对象、记录集对象等。
（2）line16，把“目标表”数据清除。
（3）line17~22，把“参数表”装入数组arrRef，把“参数表”的I~J列数据清除。
（4）line23，把当前工作表完整路径赋值给dataFile字符串变量。
（5）line24，建立数据库连接字段。
（5）line27，打开数据库连接。
（6）line28~100，循环查询数据，写入目标工作表。
（A）line30~33，把参数数组arrRef中的相关元素赋值给相关变量，便于后面引用。
（B）line34~36，处理需要统计的班级字段，把逗号统一为英文标点符号，然后分列到数组arrStr（）
（C）line37，开始循环arrStr。
（D）line39~44，SQL查询语句strSQL。
（E）line45~48，查询strSQL中的记录数，赋值给rsCount。
（F）line49~51，把班级与人数连接起来，存入数组。把参数表对应第10列单元格改为“已完成”。
（G）line52，判断记录数，如果大于0，则接下来查询数据并写入工作表。
（H）line53~65，标题项目，按照要求格式文本连接字符串，写入工作表，设置格式合并居中等。
（I）line66~69，表头字段，根据查询的记录集字段写入工作表。
（J）line70~81，明细数据，采用CopyFromRecordset方法，写入工作表，设置格式。第一列序号通过循环来填充。
（K）line85~95，把班级与人数字段写入参数表第9列，并把括号中的人数标红。
（L）line97~98，如果参数表第8列不等于“是”，则把数组arrRef第9、10列请空。这两句有点多余，原来是准备把arrRef再回写到工作表的，后来因为要设置单元格部分字符为红色，需要再做循环，感觉有点烦。后来在完成一个项目的查询后，直接把结果写入单元格（代码第87行），接着就对它进行格式设置。
（5）line101~107，返回到“参数表”，释放变量。
2、在工作表“参数表”里，两个命令按钮
Private Sub CmdProcess_Click() Call processEnd SubPrivate Sub CmdClear_Click() Dim ws As Worksheet Set ws = Sheets("参数表") ws.Range("I2:J" & ws.UsedRange.Rows.Count).ClearContents Set ws = Sheets("目标表") ws.Cells.ClearEnd Sub
代码解析：
（1）Line2~3，调用process过程。
（2）line4~10，清除“参数表”，“目标表”的数据。主要是为了演示需要。
总结
1、SQL查询语句，可以完成非常复杂的数据统计。不过，速度方法略有不足，因为需要多次执行查询，比在数组中循环提取要稍稍慢一点。
2、单元格部分字符设置格式。
3、楼主后来又有新的需求，要把结果以不同的方式，分别存入新的工作表，由于时间关系，就此作罢。
~~~~~~End~~~~~~
喜欢就点个赞、点在看、留言评论、分享一下呗！感谢支持！