|
|
原帖在此，当时没有想到解决的办法，这几天有时间正好研究的一下，现把自己的想法与结果，发上来，大家讨论一下，也算是对自己的一个总结吧。
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)
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2014-7-22 11:02
楼主
