这学期做助教,不过就忙了最近3天,统计学生成绩的时候用Excel,发现把学生的分数对应到A,B, C D制很麻烦,如某某人考了98.5,
最终成绩需要记成A+。幸好看过以前发在论坛上的教程,可以用VBA帮忙下(不过某有看过郭安定的高级教程,因为要米啊),否则以前根本不知道Excel
还可以这样用。好,废话不讲了,把这个转换成绩的VBA(就是用在Excel里的一个程序代码,应该在Excel的高级教程里会讲到)。
有兴趣的可以学一下,其实看懂了蛮简单的^_^
具体要看效果的话,下载
附件中的Excel文件,打开Excel文件,然后依次工具--》宏--》visual basic 宏编辑器, 把这段代码copy进去后,点击菜单栏下面的绿色的向右的三角形运行,到Excel查看,就可以看到效果啦, good luck!
http://www.blogjava.net/Files/fullfocus/VBA.rar
参考文档,使用的关键语句
1 Sub GenGPA()
2 Dim l As Long
3 Dim i As Long
4 Dim aplus As Long
5 Dim a As Long
6 Dim asub As Long
7 Dim bplus As Long
8 Dim b As Long
9 Dim bsub As Long
10
11 Application.DisplayAlerts = True
12 l = ActiveSheet.Range("A65535").End(xlUp).Row 'initial
13 aplus = 0
14 a = 0
15 asub = 0
16 bplus = 0
17 b = 0
18 bsub = 0
19
20 For i = 2 To l
21 If Cells(i, 7).Value >= 96 Then
22 Cells(i, 8).Value = "A+"
23 aplus = aplus + 1 'count the aplus number of students
24 End If
25 If Cells(i, 7).Value >= 90 And Cells(i, 7).Value <= 95 Then
26 Cells(i, 8).Value = "A"
27 a = a + 1
28 End If
29 If Cells(i, 7).Value >= 85 And Cells(i, 7).Value < 90 Then
30 Cells(i, 8).Value = "A-"
31 asub = asub + 1
32 End If
33 If Cells(i, 7).Value >= 80 And Cells(i, 7).Value < 85 Then
34 Cells(i, 8).Value = "B+"
35 bplus = bplus + 1
36 End If
37 If Cells(i, 7).Value >= 75 And Cells(i, 7).Value < 80 Then
38 Cells(i, 8).Value = "B"
39 b = b + 1
40 End If
41 If Cells(i, 7).Value >= 70 And Cells(i, 7).Value < 75 Then
42 Cells(i, 8).Value = "B-"
43 bsub = bsub + 1
44 End If
45 Next
46
47 Cells(2, 9).Value = "A+"
48 Cells(2, 10).Value = aplus
49 Cells(3, 9).Value = "A"
50 Cells(3, 10).Value = a
51 Cells(4, 9).Value = "A-"
52 Cells(4, 10).Value = asub
53 Cells(5, 9).Value = "B+"
54 Cells(5, 10).Value = bplus
55 Cells(6, 9).Value = "B"
56 Cells(6, 10).Value = b
57 Cells(7, 9).Value = "B-"
58 Cells(7, 10).Value = bsub
59
60
61 End Sub
62
63
posted on 2008-06-29 22:03
fullfocus 阅读(613)
评论(0) 编辑 收藏 所属分类:
其他