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

20171104xlVBA各人各科进退

作者:用户 来源:互联网 时间:2018-11-11 19:39:04

objdebdescsheetwithETHNALadecells

20171104xlVBA各人各科进退 - 摘要: 本文讲的是20171104xlVBA各人各科进退, Sub 各班个人各科进步幅度() Dim dRank As Object Set dRank = CreateObject("Scripting.Dictionary") Dim dStd As Object

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, _
            Header:=IIf(WithHeader, xlYes, xlNo), MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
    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, _
            Header:=IIf(WithHeader, xlYes, xlNo), MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
    End With
End Sub

  

20171104xlVBA各人各科进退

标签:with   desc   ade   nal   deb   cells   eth   sheet   obj   

原文地址:http://www.cnblogs.com/nextseven/p/7782343.html

以上是20171104xlVBA各人各科进退的全部内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有20171104xlVBA各人各科进退 的相关内容,欢迎继续使用右上角搜索按钮进行搜索obj , deb , desc , sheet , with , ETH , NAL , ade cells ,以便于您获取更多的相关知识。

mysql查询学生成绩及各科成绩的排名

问题描述 mysql查询学生成绩及各科成绩的排名 学生表student有主键id,学号stuNum,姓名stuName, 课程表course有课程名称主键id,cname 成绩表sc有主键id,学生表id,课程表id 班级表class有主键id...

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...

前三篇
后三篇