|
原帖在此,当时没有想到解决的办法,这几天有时间正好研究的一下,现把自己的想法与结果,发上来,大家讨论一下,也算是对自己的一个总结吧。
http://club.excelhome.net/forum.php?mod=viewthread&tid=1137754&page=1#pid7749431
表格内容精简了一下,保留必要的字段,其余的删除了。
表格的结构如上图所示,楼主的要求
1、先把表按“录取成绩”从高到低排序。
2、然后通过筛选户口所在省为“AAA省”;户口所在县为“AAA县”;考生类别栏为“A”类别的考生;第1志愿为“高中1校”的。(这里把省,县都清除了,如果需要的话,简单的办法是首先筛选一下,然后把筛选后的数据,复制到原表,如果不想筛选,在sql语句中也可以实现,也就是加几个where and 。。。。。。,在我上传的表格中,没有提现,如果需要可以自己添加。)
3、从高分到低分选16个在“录取学校”栏手工录取“高中1校”。即完成划线和录取,16人中最低的分即为录取分数线。
4、第1志愿所有招录学校都录取后,录取计划人数未满的再录第2志愿。
首先先来看看top语句的应用,
通过如上两张图,我想大家已经明白了top语句的用法,需要补充的是,楼主在说明中,漏了一点,录取学校是按照第一志愿进行录取的,如果第一志愿已经录够,那么不管你的分数多少,第二志愿学校是不会录取的。
举个简单的例子
成绩 | 第一志愿 | 第二志愿 | 99 | 1校 | 2校 | 98 | 1校 | 2校 | 97 | 2校 | | 96 | 2校 | |
1校录1人,2校录2人,成绩为98的,未被录取,原因是第一志愿都已经录够了,第二志愿根本就不需要看,虽然分数排名第二,但也未被录取。(自己的理解,不一定正确。)
下面是代码,我尽量解释的清楚一些。
Sub 查询()
Dim cnn, RS As Object
Dim Sql As String
Set cnn = CreateObject("adodb.connection") 建立对对象的引用,这种方法,属于前期绑定,就不用去引用控件了,好处是,你复制代码后就可以运行,缺点是,速度慢一些。
Set RS = CreateObject("adodb.Recordset")
Dim arr 建立一个数组,大小以后定
arr = Sheet1.[i2:l8] 对arr数组赋值
Dim i, j, k, M As Byte, lastrow As Integer 建立一些变量,后面要用到
Application.ScreenUpdating = False
Sheet3.Activate
Cells.Clear
[A1:C1] = Array("考号", "成绩", "录取学校") 对sheet3 表格写入标题,
[a2] = "t" 这里解释一下,如果没有这个t,当对not in的时候,会出现错误,所以这里写了一个和考号无关的字符串。
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName '07/10用 打开、建立的连接。
For i = 1 To 7 因为有7说学校,所以最少需要循环7次
M = 0 初始化变量
Sql = "select top " & arr(i, 2) & " 考号,成绩 from " & "(select * from [sheet1$a1:f] where 第1志愿='" & arr(i, 1) _
& "' and 考号 not IN (select 考号 from [SHEET3$A:A] )) ORDER BY 成绩 desc" 这条语句的意思是从sheet1表格中,选取第一志愿是高中1校,人数由arr(1,2)定,并且an成绩降序排列,也就是成绩高的在前面,并且,不包含sheet3表格中的考号,因为如果第一志愿已经被录取的话,再次循环的时候,第一志愿已经被录取的不需要参与循环了。
RS.Open Sql, cnn, 1, 1 打开连接,形成记录集,
M = M + RS.RecordCount RS.RecordCount是返回记录的条数。
If M < arr(i, 2) Then 如果返回的记录小于招生人数,那么就需要对第二、第三、第四志愿进行4次循环,直到录取够人数为止。
lastrow = [B65536].End(xlUp).Row 返回a列不为空的最后一个行号。
Range("A1").Offset(lastrow, 0).CopyFromRecordset RS copy 记录集中的记录
RS.Close 关闭记录集
For j = 2 To 4 对其余志愿进行循环。
Sql = "select top " & arr(i, 2) - M & " 考号,成绩 from " & "(select * from [sheet1$a1:f] where 第" & j & "志愿='" & arr(i, 1) _
& "' and 考号 not IN (select 考号 from [SHEET3$A:A] )) ORDER BY 成绩 desc" arr(i, 2) - M 意思是对第二志愿循环时需要减去第一志愿的人数,比如第一志愿招了10人,共计招12人,那么第二志愿只需要2人就够了。
RS.Open Sql, cnn, 1, 1
lastrow = [B65536].End(xlUp).Row
Range("A1").Offset(lastrow, 0).CopyFromRecordset RS
M = M + RS.RecordCount
RS.Close
If M >= arr(i, 2) Then Exit For 如果招够了人数就退出循环,也就是说第1+第二志愿,够了,第3就不需要循环了。
Next
Columns("A:C").SpecialCells(xlCellTypeBlanks) = arr(i, 1) 定位空值,填充学校名称。
Else 第一志愿就符合招生人数了,那么执行如下语句
lastrow = [B65536].End(xlUp).Row
Range("A1").Offset(lastrow, 0).CopyFromRecordset RS
Columns("A:C").SpecialCells(xlCellTypeBlanks) = arr(i, 1)
RS.Close
End If 结束判断,开始第二次循环,比如1校第一志愿招16人,在第一志愿已经够了,那么,开始2校招。
Next
Sheet1.Activate
Dim brr, crr, drr, err 再定义一些变量,其实这里直接用函数也是可以的,不过既然已经用到了sql,就一直用下去吧。
Sql = "select 录取学校,COUNT(*) from [sheet3$] GROUP BY 录取学校"
brr = cnn.Execute(Sql).GetRows 把查询结果返回到数组(这个数组是水平数组),第一列是学校名称,第二列是录取人数。
Sql = "select 录取学校,MIN(成绩) from [sheet3$] GROUP BY 录取学校" 返回录取学校的最小成绩,其实这里可以说是返回第一志愿是我校的最小成绩。
crr = cnn.Execute(Sql).GetRows
cnn.Close: Set cnn = Nothing
Set RS = Nothing
For i = 1 To UBound(arr) 对sheet1录取分数、录取人数进行循环赋值。
arr(i, 3) = Application.HLookup(arr(i, 1), brr, 2, 0) hlookup 函数不用解释了吧。
arr(i, 4) = Application.HLookup(arr(i, 1), crr, 2, 0)
Next
[i2].Resize(7, 4) = arr 返回数据
err = [a1].CurrentRegion
For i = 2 To UBound(err)
err(i, 7) = Application.VLookup(err(i, 1), Sheet3.[A:C], 3, 0) 返回录取学校名称
Next
[a1].Resize(UBound(err), 7) = err
[g:g].Replace "#N/A", "未录取" 因为用到了函数,没有值的会显示na错误,替换成,未录取。
Application.ScreenUpdating = True
End Sub
Sub t1()
Dim tim As Double, i As Byte
tim = Timer
For i = 1 To 10
Call 查询
Next
MsgBox Format(Timer - tim, "0.00秒")
End Sub
这个语句是测试速度用的,数据表中,大概1300条数据,循环10次就是13000条数据,在我的电脑大概是20秒,实际上时间应该更少一些,因为底下那些对sheet1赋值,实际使用中,只需要循环一次就可以了。
结果如下
学校 | 计划录取人数 | 实际录取人数 | 分数线 | 高中1校 | 16 | 17 | 735 | 高中2校 | 15 | 17 | 714.5 | 高中3校 | 15 | 15 | 663 | 高中4校 | 14 | 14 | 445.5 | 高中5校 | 16 | 16 | 341 | 高中6校 | 13 | 13 | 370.5 | 高中7校 | 12 | 12 | 622.5 |
可以看到1校多了一人,因为第16名,与第17名,成绩是一样的,按我的想法,都需要,除非楼主指明别的方法,如果只按成绩的话,就应该都录。
2校也一样
100778 | 734 | 高中1校 | 高中2校 | | | 未录取 | 100741 | 733.5 | 高中1校 | 高中1校 | | | 未录取 | 100852 | 732 | 高中1校 | | | | 未录取 | 100926 | 731.5 | 高中1校 | | | | 未录取 | 101111 | 731 | 高中1校 | 高中4校 | | | 未录取 | 101075 | 729.5 | 高中1校 | | | | 未录取 | 101149 | 729.5 | 高中1校 | 高中3校 | | | 未录取 |
可以看到成绩考的如此之高,竟然也未被录取,原因就在于,第一志愿各个学校都已经招够了,第二志愿没有意义。
中考划线录取工作.rar
(121.64 KB, 下载次数: 66)
|
|