代码之家 › 专栏 › 技术社区 › greycat

vba排序循环需要秩

rank sorting loops vba

greycat · 技术社区 · 6 年前

我有一个电子表格,上面有学生每周的考试成绩。它有5个字段,名称(A列)、年份(B列)、级别(C列)、周(D列)和分数(ColE)。每个学生在工作表上都有自己的区块,每一行区块由和空行分隔。行块的大小会有所不同。(请参见下文)。
我有代码可以将分数(E列)从高到低排序(参见排序前和排序后)
我想做的是在week列和score列之间插入另一列,该列给出排序后每个分数的位置,如下图3所示。我认为这需要某种排序过程和循环。还要注意的是,有时某个学生在某些周的成绩可能是相同的,因此会出现联合排名(或第二名或第三名等),就像约翰·埃利斯(John Ellis)和菲尔·西姆(phil simm)的联合排名一样,前者有两组54分,后者有一组1分,后者有一组4分。
希望这有意义。非常感谢您的帮助
在spreadshhet图的底部,我还放置了用于循环和排序列E(分数列)的代码。

BEFORE SORT(Fig1)                   
name       year     level   week    score   
jill evans  5         2      10       56    
jill evans  5         2      11       49    
jill evans  5         2      12       77    
jill evans  5         2      13      84 
empty   empty   empty   empty   empty   
john ellis  3   4   10  45  
john ellis  3   4   11  54  
john ellis  3   4   12  54  
john ellis  3   4   13  29  
john ellis  3   4   14  66  
empty   empty   empty   empty   empty   
phil simm   4   6   10  89  
phil simm   4   6   11  76  
phil simm   4   6   12  41  
phil simm   4   6   13  41  
phil simm   4   6   14  56  
phil simm   4   6   15  59  
phil simm   4   6   16  61  
phil simm   4   6   17  61  




AFTER SORT(Fig2)                    

name        year    level   week        score
jill evans  5        2       11         49
jill evans  5        2       10         56
jill evans  5        2       12         77
jill evans  5        2       13         84
empty   empty   empty   empty       empty
john ellis  3   4   13      29
john ellis  3   4   10      45
john ellis  3   4   11      54
john ellis  3   4   12      54
john ellis  3   4   14      66
empty   empty   empty   empty       empty
phil simm   4   6   12      41
phil simm   4   6   13      41
phil simm   4   6   14      56
phil simm   4   6   15      59
phil simm   4   6   16      61
phil simm   4   6   17      61
phil simm   4   6   11      76
phil simm   4   6   10      89

    FIG3 with the position row included between week col and score       col                    
name          year  level   week    position    score
jill evans       5   2       11         1       49
jill evans       5   2       10         2       56
jill evans       5   2       12         3       77
jill evans       5   2       13         4       84
empty   empty   empty   empty   empty   empty
john ellis  3   4   13  1   29
john ellis  3   4   10  2   45
john ellis  3   4   11  3   54
john ellis  3   4   12  3   54
john ellis  3   4   14  4   66
empty   empty   empty   empty   empty   empty
phil simm   4   6   12  1   41
phil simm   4   6   13  1   41
phil simm   4   6   14  2   56
phil simm   4   6   15  3   59
phil simm   4   6   16  4   61
phil simm   4   6   17  4   61
phil simm   4   6   11  5   76
phil simm   4   6   10  6   89

因此,“位置”列反映排序后分数的新位置。
如果两个得分相同,那么这将是一个联合位置,就像约翰·埃利斯(John Ellis)以两组54分的成绩并列第四,菲尔·西姆(phil simm)以第一和第四的成绩并列第四。
希望这没有什么意义。非常感谢您的帮助

Sub sortone()                   

Application.ScreenUpdating = False                  
Dim Area As Range, sr As Long, er As Long                   
For Each Area In Range("A2", Range("E" &   Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas                   
  With Area                 
    sr = .Row                   
    er = sr + .Rows.Count - 1                   
    Range("A" & sr & ":E" & er).Sort key1:=Range("E" & sr),   order1:=1                 
  End With                  
Next Area                   
Application.ScreenUpdating = True                   
End Sub

非常感谢

3 回复 | 直到 6 年前

ashleedawg 6 年前

使用子集的“RankIf”条件秩 `SUMPRODUCT` :

另一种排名方式,有点像 RANKIF 功能,用途 SUMPRODUCT公司 要执行条件列组,请执行以下操作:

公式输入 D5 :

=1+SUMPRODUCT((A$4:A$21=A5)*($B$4:$B$21>B5))

。。。设置绝对/相对单元格引用以允许复制或填充公式(&R);正当

更多信息:

办公室com: SUMPRODUCT Function (Excel)
办公室com: RANK Function (Excel)
MSDN: WorksheetFunction.Rank Method (VBA/Excel)
办公室com: Excel statistical functions: Representing ties by using RANK

DisplayName 6 年前

您可以尝试此代码

Option Explicit

Sub sortone()
    Dim Area As Range

    Application.ScreenUpdating = False

    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Columns(5).Insert
        .Cells(1, 5).Value = "position"

        For Each Area In .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeConstants).Areas
            With Area
                .Resize(, 6).Sort key1:=.Range("F1"), order1:=1
                .Offset(, 4).FormulaR1C1 = "=RANK(RC[1]," & .Offset(, 5).Resize(, 1).Address(, , xlR1C1) & ",1)"
            End With
        Next Area
    End With

    Application.ScreenUpdating = True
End Sub

greycat 6 年前

name        year    level   week    position    score
john ellis   3        4     13       1           29
phil simm    4        6     12       2           41
phil simm    4        6     13       2           41
john ellis   3        4     10       4           45
jill evans   5        2     11       5          49
john ellis   3        4     11       6           54
john ellis   3        4     12       6           54
jill evans   5        2     10       8           56
phil simm    4        6     14       8           56
phil simm    4        6     15      10           59
phil simm    4        6     16      11          61
phil simm    4        6     17      11           61
john ellis   3        4     14      13           66
phil simm    4        6     11      14           76
jill evans   5        2     12      15           77
jill evans   5        2     13      16           84
phil simm    4        6     10      17           89
empty   empty   empty   empty   #VALUE! empty
empty   empty   empty   empty   #VALUE! empty

vba排序循环需要秩

使用子集的“RankIf”条件秩 SUMPRODUCT :

更多信息:

使用子集的“RankIf”条件秩 `SUMPRODUCT` :