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

我需要将此表转换为“前十”团队的列表。规则是

• 每支球队的得分取自该俱乐部四名成员的总分。
• 这些总分应该是最好的四个分数，除了......
  • 每支队伍必须至少包含一名青少年或女子

例如，在上表中，A 俱乐部的球队得分为 625 而不是 640，因为您会得到 Harry(190)、Bert(150)、Jim(150) 和西蒙(130)。您不能接受 Fred 的(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包含俱乐部字母。