征求学生的成绩转换成等级的方法（大头、一剑给了很好的方法）

lhx120824 · 发表于 2010-8-5 15:02

征求学生的成绩转换成等级的方法
一个班级共有9门课程，包括总分需要转换10项，总分各不相同，有120、50、70、680
转换标准按权重计算，即分别按每项总分的0.9、0.8、0.7、0.6、0.5、0.3及以下转换成A+ A B+ B C+ C D
具体标准和成绩见下表和附件。可以用if函数，也可以用index、vlookup、lookup等等，既然是征求方法，那就请大家各抒己见。
因为不只是一个科目，所以本题有一定的难度和技巧。

科目总分等级转换标准
语文 120 满分 120
数学 120 权重开始(>=) 结束等级
英语 120 0.9 108 120 A+
政治 50 0.8 96 108 A
历史 50 0.7 84 96 B+
地理 50 0.6 72 84 B
生物 50 0.5 60 72 C+
物理 70 0.3 36 60 C
化学 50 0 36 D
总分 680

成绩的等级转换.rar (37.86 KB, 下载次数: 155)

[ 本帖最后由 lhx120824 于 2010-8-5 22:17 编辑 ]

lhx120824 · 发表于 2010-8-5 15:23

提示：
可以对
标准工作表进行改进，以方便引用查找，缩短公式长度。

lhx120824 · 发表于 2010-8-5 15:44

本人先给出一种方法：用 INDEX+MATCH
s3=INDEX(标准!$U$3:$U$9,MATCH(1,(D3>=标准!K$3:K$9)*(D3<=标准!K$2:K$8),0))
先右拉再双击填充柄自动向下填充。

成绩的等级转换用index.rar (45.67 KB, 下载次数: 129)

构造标准表格如下：
科目语文数学英语政治历史地理生物物理化学总分
满分 120 120 120 50 50 50 50 70 50 680 等级
0.9 108 108 108 45 45 45 45 63 45 612 A+
0.8 96 96 96 40 40 40 40 56 40 544 A
0.7 84 84 84 35 35 35 35 49 35 476 B+
0.6 72 72 72 30 30 30 30 42 30 408 B
0.5 60 60 60 25 25 25 25 35 25 340 C+
0.3 36 36 36 15 15 15 15 21 15 204 C
0 0 0 0 0 0 0 0 0 0 D

[ 本帖最后由 lhx120824 于 2010-8-5 15:52 编辑 ]

lzqlaj · 发表于 2010-8-5 16:13

学习了,
Sub dengji()
Application.ScreenUpdating = False '关闭屏幕刷新
  Dim ary1 As Range
Dim ary()
p = Sheets("成绩").[a65536].End(xlUp).Row
ReDim ary(1 To 10)
For j0 = 1 To 10
      ary(j0) = Sheets("标准").Cells(j0 + 1, 2)
Next j0
For j = 4 To 13
      mf = ary(j - 3)
      For i = 3 To p
         If Cells(i, j) >= 0.9 * mf And Cells(i, j) <= mf Then
            Cells(i, j + 15) = "A+"
         ElseIf Cells(i, j) >= 0.8 * mf And Cells(i, j) < 0.9 * mf Then
            Cells(i, j + 15) = "A"
         ElseIf Cells(i, j) >= 0.7 * mf And Cells(i, j) < 0.8 * mf Then
            Cells(i, j + 15) = "B+"
         ElseIf Cells(i, j) >= 0.6 * mf And Cells(i, j) < 0.7 * mf Then
            Cells(i, j + 15) = "B"
         ElseIf Cells(i, j) >= 0.5 * mf And Cells(i, j) < 0.6 * mf Then
            Cells(i, j + 15) = "C+"
         ElseIf Cells(i, j) >= 0.3 * mf And Cells(i, j) < 0.5 * mf Then
            Cells(i, j + 15) = "C"
         ElseIf Cells(i, j) >= 0 And Cells(i, j) < 0.3 * mf Then
            Cells(i, j + 15) = "D"
         End If
      Next i
Next j
Application.ScreenUpdating = True '关闭屏幕刷新
End Sub

lhx120824 · 发表于 2010-8-5 16:31

原帖由 lzqlaj 于 2010-8-5 16:13 发表
学习了,
Sub dengji()
Application.ScreenUpdating = False '关闭屏幕刷新
Dim ary1 As Range
Dim ary()
p = Sheets("成绩").[a65536].End(xlUp).Row
ReDim ary(1 To 10)
For j0 = 1 T ...

宏的代码经修改原始成绩后再执行宏，运行正确。

lzqlaj · 发表于 2010-8-5 16:42

你的公式很难，需要仔细研究学习。

lhx120824 · 发表于 2010-8-5 17:34

先学习两个函数的帮助，再在编辑栏中运用f9键查看你不理解的部分，看出现什么结果，公式的理解要慢慢来。

wangg913 · 发表于 2010-8-5 18:40

s3=INDEX(标准!$U$3:$U$9,MATCH(D3,标准!K$2:K$8,-1))

wangg913 · 发表于 2010-8-5 18:40

稍有不同，你验证一下对不对？

lhx120824 · 发表于 2010-8-5 18:48

完全正确，即简单易懂又不是数组公式，谢谢提供。你的其他的方法呢？

[ 本帖最后由 lhx120824 于 2010-8-5 18:55 编辑 ]

		自动登录	找回密码
密码			免费注册

[分享] 征求学生的成绩转换成等级的方法（大头、一剑给了很好的方法）