根据复杂的团队规则在Excel中排名前十的订单
我有一个类似于以下格式的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，因为我们会得出哈里(190)，伯特(150)，吉姆(150)和西蒙(130)的得分。我们无法采用Fred(145)的评分，因为那只会给我们Gents。
我的问题是，可以通过一系列Excel公式轻松完成此操作，还是需要使用更具过程性的方法？
理想情况下，解决方案在团队选择中需要自动进行，我不想为每个团队创建单独的手工公式。我也不一定会列出每个俱乐部成员的排列整齐的清单。尽管我可能可以通过额外的计算表来生成列表。
解决方案
回答
使用数据透视表，该数据透视表将用作对我们所拥有的数据的数据库查询。进行数据透视，以便团队进入列，并且团队成员及其状态类型将遍历数据透视表。我不确定2003年是什么，但是Excel 2007可以让我们排序，以便将最高分显示在左侧。然后，第一笔款项就可以简单算出每个团队的前三个得分。但是，要获取最后一个人的总和，必须确定是否可以使用第4个得分，或者是否必须使用初中或者淑女类型的最大值。可以使用复杂的蛮力公式来完成，如下所示：
如果(位置1的类型是初中或者女士，或者... 2或者3 ...)，则使用位置4，否则，如果位置5是初级或者女士，则使用5 else，如果p 6是...，依此类推。
回答
用VBA编写解决方案将是我的第一选择，特别是如果规则有可能变得更加复杂的话。
回答
我认为除非表格以某种方式排序，否则无法做到这一点。 Excel的大多数查找功能都需要有序列表。当然可以使用VBA功能来完成。
回答
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"包含俱乐部字母。
回答
can this be done easily as a series of Excel formula
简短的回答，是的。 (取决于我们对"轻松"的定义)。
长答案...
(我认为这可行)
这是我的(简要)测试数据：
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
现在，如果我正确理解了规则，我们希望获得最高的四分，除非女士或者初中的最高分不在最佳四分之内，我们将使用该得分而不是第四高的得分。由于某种原因，我已经对其进行了某种程度的重述。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
好的。数组公式可助我们一臂之力！ (我希望)
甲队的最高分数应该是
{=LARGE(IF(B2:B16="a",D2:D16,0),1)}
其中{}表示使用Control-Shift-Enter输入公式创建的数组公式。前四名的创建方式与此类似。对于Lady / Junior位，我们需要更多的复杂性。以这位女士为例，我们需要这样做：
{=LARGE(IF($B:$B=$J3,IF($C:$C="Lady",$D:$D,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
俱乐部得分应该是前三名"任何人"得分，再加上最好的女士或者青年组(如果他们尚未进入前四名)。
因此，在第二季度，我将其放置：
=SUM(K2:M2)+MIN(MAX(O2,P2),N2)
MAX(O2，P2)告诉我最好的女士或者初级成绩，必须将其包括在内。如果它高于团队第四高的分数，那么它已经在列表中，而我们仅排名前四。否则，我们用最好的女士/初中生取代第四高的分数。
现在，我们可以将零件替换为最终公式，从而在一个公式中完成所有操作：
{=LARGE(IF($B:$B=$J3,$D:$D,0),1)+
LARGE(IF($B:$B=$J3,$D:$D,0),2)+
LARGE(IF($B:$B=$J3,$D:$D,0),3)+
MIN(LARGE(IF($B:$B=$J3,$D:$D,0),4),
MAX(LARGE(IF($B:$B=$J3,IF($C:$C="Lady",$D:$D,0),0),1),
LARGE(IF($B:$B=$J3,IF($C:$C="Junior",$D:$D,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
老鼠在(我认为)开始努力工作的激动中，我忘了提到
- 分数列表可以按任何顺序排列
- 我们可以使用RANK()获得俱乐部排名。
- 然后，我们可以使用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:$B) # what it says, with ties both getting the lower number
D: =B2-ROW()*0.00001 # score, modified slightly to ensure uniqueness
F: =SMALL($C:$C,E2) # first output column, ranks including ties
G: =INDEX($A:$A,MATCH(LARGE($D:$D,E2),$D:$D,0))
# club name for position, using the modified score in D
H: =INDEX($B:$B,MATCH(LARGE($D:$D,E2),$D:$D,0))
# as G, but indexes into scores
