基于复杂团队规则的Excel十大排序

Question

我有一个excel电子表格的格式类似于以下..。

| NAME  | CLUB | STATUS | SCORE |
| Fred  |  a   | Gent   | 145   |
| Bert  |  a   | Gent   | 150   |
| Harry |  a   | Gent   | 195   |
| Jim   |  a   | Gent   | 150   |
| Clare |  a   | Lady   | 99    |
| Simon |  a   | Junior | 130   |
| John  |  b   | Junior | 130   |
   :
   :
| Henry |  z   | Gent   | 200   |

我需要把这张表转换成“十大”团队的名单。规则是

• 每个队的分数是从该俱乐部的四个成员的总和中取出来的。
• ，这些总数应该是最好的四分，除了.每个团队必须至少由一名少年或Lady

组成

​

例如，在上面的表格中，A俱乐部的得分是625 ，而不是 640，因为你会拿哈里(190)、伯特(150)、吉姆(150)和西蒙(130)的分数。你不能拿弗雷德的(145)分，因为那只会给你加恩特。

我的问题是，这是否可以很容易地作为一系列Excel公式来完成，或者我是否需要使用更多的程序？

理想情况下，解决方案需要在团队选择中自动完成，我不想为每个团队创建单独的手工公式。我也不一定会有一个排列整齐的名单，每个俱乐部的成员。虽然我可能可以通过额外的计算表生成列表。

Answer 1

Public Function TopTen(Club As String, Scores As Range)

    Dim i As Long
    Dim vaScores As Variant
    Dim bLady As Boolean
    Dim lCnt As Long
    Dim lTotal As Long

    vaScores = FilterOnClub(Scores.Value, Club)
    vaScores = SortOnScore(vaScores)

    For i = LBound(vaScores, 2) To UBound(vaScores, 2)
        If lCnt = 3 And Not bLady Then
            If vaScores(3, i) <> "Gent" Then
                lTotal = lTotal + vaScores(4, i)
                bLady = True
                lCnt = lCnt + 1
            End If
        Else
            lTotal = lTotal + vaScores(4, i)
            lCnt = lCnt + 1
            If vaScores(3, i) <> "Gent" Then bLady = True
        End If

        If lCnt = 4 Then Exit For
    Next i

    TopTen = lTotal

End Function

Private Function FilterOnClub(vaScores As Variant, sClub As String) As Variant

    Dim i As Long, j As Long
    Dim aTemp() As Variant

    For i = LBound(vaScores, 1) To UBound(vaScores, 1)
        If vaScores(i, 2) = sClub Then
            j = j + 1
            ReDim Preserve aTemp(1 To 4, 1 To j)
            aTemp(1, j) = vaScores(i, 1)
            aTemp(2, j) = vaScores(i, 2)
            aTemp(3, j) = vaScores(i, 3)
            aTemp(4, j) = vaScores(i, 4)
        End If
    Next i

    FilterOnClub = aTemp

End Function

Private Function SortOnScore(vaScores As Variant) As Variant

    Dim i As Long, j As Long, k As Long
    Dim aTemp(1 To 4) As Variant

    For i = 1 To UBound(vaScores, 2) - 1
        For j = i To UBound(vaScores, 2)
            If vaScores(4, i) < vaScores(4, j) Then
                For k = 1 To 4
                    aTemp(k) = vaScores(k, j)
                    vaScores(k, j) = vaScores(k, i)
                    vaScores(k, i) = aTemp(k)
                Next k
            End If
        Next j
    Next i

    SortOnScore = vaScores

End Function

在包含俱乐部字母的=TopTen(H2,$B$2:$E$30)中使用H2。

Answer 2

可以很容易地作为一系列的Excel公式来完成

简短的回答是的。(取决于您对“轻松”的定义)。

长话短说..。

(我认为这个可行)

以下是我的(简短)测试数据：

    A          B    C        D
 1 NAME      CLUB STATUS  SCORE
 2 Kevin    a   Gent    145
 3 Lyle     a   Gent    150
 4 Martin   a   Gent    195
 5 Norm     a   Gent    150
 6 Oonagh   a   Lady    100
 7 Arthur   b   Gent    200
 8 Brian    b   Gent    210
 9 Charlie  b   Gent    190
10 Donald   b   Gent    220
11 Eddie    b   Junior  150
12 Quentin  c   Gent    145
13 Ryan     c   Gent    150
14 Sheila   c   Lady    195
15 Trevor   c   Gent    150
16 Ursula   c   Junior  200

现在，如果我正确地理解了规则，我们想要最好的4分，但如果女士或小三的最高分不是在最好的4分，我们用的是这个，而不是第四个最高的分数。我已经有所重申了，原因可能会变得明显.

好的。阵列公式到救援！(我希望)

甲队的最高分应该是

{=LARGE(IF(B2:B16="a",D2:D16,0),1)}

其中，{}指示使用Control Enter输入公式所创建的数组公式.前四名也同样被创造出来。对于女士/小女孩来说，我们需要更多的复杂性。带上女士，我们需要这个：

{=LARGE(IF($B$2:$B$16=$J3,IF($C$2:$C$16="Lady",$D$2:$D$16,0),0),1)}

我希望小三可以安全地留给学生做练习。

我现在正在看一张表格，表中的"a“俱乐部的布局如下

     J    K      L      M      N      O      P
 1 Club    1      2      3      4   Lady  Junior
 2 a     195    150    150    145    100      0

俱乐部的得分应该是前三名的“任何人”分数加上最好的女士或少年，如果他们还没有进入前四。

因此，在Q2中，我要这样说：

=SUM(K2:M2)+MIN(MAX(O2,P2),N2)

麦克斯(O2，P2)告诉我最好的女士或低年级的分数，这必须包括在内。如果它高于第四最高的球队得分，那么它已经在名单上了，我们只占前四。否则，我们把第四高分换成最好的女士/低年级的分数。

现在，我们可以在一个公式中完成这一切，方法是将这些部分替换为最后的公式：

{=LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),1)+
LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),2)+
LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),3)+
MIN(LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),4),
MAX(LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Lady",$D$2:$D$18,0),0),1),
LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Junior",$D$2:$D$18,0),0),1)))}

但我不推荐..。

因此，对于上述数据，我最后得出如下结论：

            Anyone                                          Lady        Junior                
Club        1           2           3           4           1           1           Total     
a           195         150         150         145         100         0           595       
b           220         210         200         190         0           150         780       
c           200         195         150         150         195         200         695       

老鼠。在我兴奋地(我想)工作时，我忘了提到这一点。

，

• ，分数列表可以是任意顺序的
• ，您可以获得排名()
• 的俱乐部排名，然后使用MATCH()和INDEX()

将前10名拉到另一张表中。

    A               B       C       D           E       F       G               H    
1   club            Sc      Rank    UniqRk              Pos     Club            Score
2   third-equal#1   80      3       79.999980   1       1       best            100  
3   second          90      2       89.999970   2       2       second          90   
4   third-equal#2   80      3       79.999960   3       3       third-equal#1   80   
5   best            100     1       99.999950   4       3       third-equal#2   80   
6   worst           70      5       69.999940   5       5       worst           70   

A栏和B栏是我们计算的分数，E栏是最后表格中俱乐部输出的顺序。其他公式如下：

C: =RANK(B2,$B$2:$B$6)      # what it says, with ties both getting the lower number
D: =B2-ROW()*0.00001        # score, modified slightly to ensure uniqueness
F: =SMALL($C$2:$C$6,E2)     # first output column, ranks including ties
G: =INDEX($A$2:$A$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))
                            # club name for position, using the modified score in D
H: =INDEX($B$2:$B$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))
                            # as G, but indexes into scores

Answer 3

我做的事很蹩脚，但很管用。

只需创建一个新的列，然后插入这个公式=If(a1=N,b1,0)，其中A1是条件列，N是条件，B1在试图从其中获取较大值的列中。然后我就在另一列做大公式。

有时候，我变得非常喜欢，而不是推出一个N，我会让它说$C$1，然后在那个单元格中详细说明标准。

最好的答案是让微软加入一个largeifs (请阅读这个微软)。