1. 云栖社区>
2. 技术文集>
3. 列表>
4. 正文

# 20171104xlVBA各人各科进退

```Sub 各班个人各科进步幅度()
Dim dRank As Object
Set dRank = CreateObject("Scripting.Dictionary")
Dim dStd As Object
Set dStd = CreateObject("Scripting.Dictionary")
Dim dSbj As Object
Set dSbj = CreateObject("Scripting.Dictionary")

em = Array("月考2", "期中考")
For n = LBound(em) To UBound(em) Step 1
Set sht = ThisWorkbook.Worksheets("成绩表_" & em(n))
With sht
EndRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
EndCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column

For i = 2 To EndRow
Key = CStr(.Cells(i, 1).Value)
dStd(Key) = Array(CStr(.Cells(i, 1).Value), CStr(.Cells(i, 2).Text), CStr(.Cells(i, 3).Text))
For J = 1 To EndCol
If .Cells(1, J).Text Like "*排" Then
dSbj(.Cells(1, J).Text) = ""
End If

Key = CStr(.Cells(i, 1).Value) & ";" & em(n) & .Cells(1, J).Text
‘Debug.Print Key
dRank(Key) = .Cells(i, J).Value

Next J
Next i
End With
Next n

For Each K In dSbj.Keys
Set sht = CreateSheet(ThisWorkbook, K & "_飞跃进步_我^_^了")
With sht
.Range("a1").Resize(1, 6).Value = Array("考号", "姓名", "班级", em(0), em(1), "进退")
EndCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column

i = 1
For Each std In dStd.Keys
i = i + 1
Ar = dStd(std)
.Cells(i, 1).Value = Ar(0)
.Cells(i, 2).Value = Ar(1)
.Cells(i, 3).Value = Ar(2)

Key = CStr(Ar(0)) & ";" & .Cells(1, 4).Text & Split(.Name, "_")(0)
.Cells(i, 4).Value = dRank(Key)
Key = CStr(Ar(0)) & ";" & .Cells(1, 5).Text & Split(.Name, "_")(0)
.Cells(i, 5).Value = dRank(Key)
.Cells(i, 6) = Val(.Cells(i, 4).Value) - Val(.Cells(i, 5).Value)

Next std

Sort_Rank .UsedRange, True
.Columns.AutoFit
End With
Next K

Set dSbj = Nothing
Set dStd = Nothing
Set dRank = Nothing

End Sub
Public Sub Sort_ClassRank(ByVal Rng As Range, Optional WithHeader As Boolean = True)
With Rng
.Sort _
Key1:=Rng.Cells(1, 3), Order1:=xlAscending, _
Key2:=Rng.Cells(1, 6), Order2:=xlDescending, _
End With
End Sub
Public Sub Sort_Rank(ByVal Rng As Range, Optional WithHeader As Boolean = True)
With Rng
.Sort _
Key1:=Rng.Cells(1, 6), Order1:=xlDescending, _
End With
End Sub
```

20171104xlVBA各人各科进退

#### sql 语句练习与答案_MsSql

...='数学'); --5 90分以上的为优秀 90到85为良好，60分 不及格 各人平均成绩 select s#, c, case when c>=90 then '优秀' when c=60 then '及格' else '不及格' end as jige from (select s#,avg(nvl(score,0)) as c from ze_score group by s# ) order by jige desc; --6 求同月出生的...

#### 程序员如何快速提升自己？有的人工作多年还是老样子的原因是什么？最后谈谈PDCA

...为只要做好本职工作就好了，其他的就不用管了。抱着“各人自扫门前雪 莫管他人瓦上霜”心态，处理工作的事。结果眼睁睁的羡慕人家升职加薪，自己却原地踏步。B小姐能把琐事做到如此精致，我想这是她成功的原因之一。 ...

#### mysql 分表实现方法详解

...ash的方式来获得，可以用求余的方式来获得，方法很多，各人想各人的吧。下面用hash的方 法来获得表名： 1

#### 【mongoDB查询进阶】聚合管道(四) -- 累加器(Accumulators)

...e: 'kent', score: 30, class: 'b', subject: 'B' },]示例一 需求：获取各科目的最高分获取各科目的最低分获取各科目的平均分操作： db.collection.aggregate( [ { \$group: { _id: "\$subject", maxScore: { \$max: '\$score' }, minScore: { \$min: '\$score' }, avgScore: { \$avg: '\$scor...

40+云计算产品，6个月免费体验