|
在放代码之前,先看下前面的两个参数:
存放首行: 4
成绩次数: 5
班级 姓名 成绩 语文 数学 英语 物理 道法 历史 地理 生物 总分 bc jc
11 秦淑婷 成绩1 100 98 115 69 44 50 49 50 575 1 1
11 秦淑婷 成绩2 103 115 116 98 99 90 100 97 818
11 秦淑婷 成绩3 100 98 115 69 44 50 49 50 575 1 1
11 秦淑婷 成绩4 102 120 117 96 86 100 96 96 813 17 2
11 秦淑婷 成绩5 99 108 107 58 44 49 50 48 563
班级 姓名 成绩 语文 数学 英语 物理 道法 历史 地理 生物 总分 bc jc
12 赵梦婕 成绩1 95 95 117 69 50 48 50 50 574 1 2
12 赵梦婕 成绩2 105 104 117 98 96 98 98 96 812
12 赵梦婕 成绩3 95 95 117 69 50 48 50 50 574 1 2
12 赵梦婕 成绩4 104 111 115 92 97 98 100 93 810 24 1
12 赵梦婕 成绩5 104 112 107 66 47 47 50 48 581
1、统计表需要新建一个工作表,名字随便取,但要放在最后面,代码也要放在这个工作表里;
2、最前面有两个参数:
存放首行:我设置的是4,意思是从第4行开始存放统计数据,这个参数可以自己改,但也不要小于4。如果是10,那么第三行到第9行之间全是空白,数据从第10行开始;
成绩次数:我设定的是5。本来是6个表,但最后一个不能用,所以只能统计前面5个。如果你有10个能用的表,那就把参数设定为10就行了。有一个要求:这10个能用的表都需要放在最前面,因为代码里默认就是从第一个工作表开始读取数据,然后依次读取“成绩次数”设定的表单数量;
3、代码如下:
Application.ScreenUpdating = False
x1 = Cells(1, 2)
x2 = Cells(2, 2)
Dim zd As Object
Set zd = CreateObject("scripting.dictionary")
[a4:q10000].ClearContents
For i = 1 To x2
hs = 0
For k = 2 To Worksheets(i).[a10000].End(3).Row
s1 = Worksheets(i).Cells(k, 1)
s2 = Worksheets(i).Cells(k, 2)
s3 = Worksheets(i).Cells(k, 6)
s4 = Worksheets(i).Cells(k, 7)
s5 = Worksheets(i).Cells(k, 8)
s6 = Worksheets(i).Cells(k, 9)
s7 = Worksheets(i).Cells(k, 10)
s8 = Worksheets(i).Cells(k, 11)
s9 = Worksheets(i).Cells(k, 12)
s10 = Worksheets(i).Cells(k, 13)
s11 = Worksheets(i).Cells(k, 14)
s12 = Worksheets(i).Cells(k, 15)
s13 = Worksheets(i).Cells(k, 16)
If zd.exists(s2) Then
hs = zd.Item(s2)
Else
zd(s2) = zd.Count + 1
hs = zd.Count
End If
Cells(x1 + (hs - 1) * (x2 + 1), 1) = Worksheets(1).Cells(1, 1)
Cells(x1 + (hs - 1) * (x2 + 1), 2) = Worksheets(1).Cells(1, 2)
Cells(x1 + (hs - 1) * (x2 + 1), 3) = "成绩"
Cells(x1 + (hs - 1) * (x2 + 1), 4) = Worksheets(1).Cells(1, 6)
Cells(x1 + (hs - 1) * (x2 + 1), 5) = Worksheets(1).Cells(1, 7)
Cells(x1 + (hs - 1) * (x2 + 1), 6) = Worksheets(1).Cells(1, 8)
Cells(x1 + (hs - 1) * (x2 + 1), 7) = Worksheets(1).Cells(1, 9)
Cells(x1 + (hs - 1) * (x2 + 1), 8) = Worksheets(1).Cells(1, 10)
Cells(x1 + (hs - 1) * (x2 + 1), 9) = Worksheets(1).Cells(1, 11)
Cells(x1 + (hs - 1) * (x2 + 1), 10) = Worksheets(1).Cells(1, 12)
Cells(x1 + (hs - 1) * (x2 + 1), 11) = Worksheets(1).Cells(1, 13)
Cells(x1 + (hs - 1) * (x2 + 1), 12) = Worksheets(1).Cells(1, 14)
Cells(x1 + (hs - 1) * (x2 + 1), 13) = Worksheets(1).Cells(1, 15)
Cells(x1 + (hs - 1) * (x2 + 1), 14) = Worksheets(1).Cells(1, 16)
Cells(x1 + (hs - 1) * (x2 + 1), 15) = Worksheets(1).Cells(1, 17)
For j = 1 To x2
If IsEmpty(Cells(x1 + (hs - 1) * (x2 + 1) + j, 1)) Then
Cells(x1 + (hs - 1) * (x2 + 1) + j, 1) = s1
Cells(x1 + (hs - 1) * (x2 + 1) + j, 2) = s2
Cells(x1 + (hs - 1) * (x2 + 1) + j, 3) = "成绩" & i
Cells(x1 + (hs - 1) * (x2 + 1) + j, 4) = s3
Cells(x1 + (hs - 1) * (x2 + 1) + j, 5) = s4
Cells(x1 + (hs - 1) * (x2 + 1) + j, 6) = s5
Cells(x1 + (hs - 1) * (x2 + 1) + j, 7) = s6
Cells(x1 + (hs - 1) * (x2 + 1) + j, 8) = s7
Cells(x1 + (hs - 1) * (x2 + 1) + j, 9) = s8
Cells(x1 + (hs - 1) * (x2 + 1) + j, 10) = s9
Cells(x1 + (hs - 1) * (x2 + 1) + j, 11) = s10
Cells(x1 + (hs - 1) * (x2 + 1) + j, 12) = s11
Cells(x1 + (hs - 1) * (x2 + 1) + j, 13) = s12
Cells(x1 + (hs - 1) * (x2 + 1) + j, 14) = s13
Exit For
End If
Next j
Next k
Next i
Application.ScreenUpdating = True
4、上楼我说过,如果要达到你截图的效果,在正确源数据的基础上,需要修改代码。要修改的就是读取的列数数量,你截图有化学,但源数据却是道法、历史、地理之类的。改起来也容易,就是把读取的列数适当增减就够了,需要稍微懂点代码才能改; |
|