问题描述
我有一个 excel 文件,其中一列对应于球员的姓名,另一列对应于棒球统计数据 OPS.
I have an excel file with one column corresponding to the player's name and the other column corresponding to the baseball statistic OPS.
OPS Player
1.000 player 1
5.000 player 2
3.000 player 3
1.000 player 4
--- player 5
4.000 player 6
1.000 player 7
--- player 8
1.000 player 9
--- player 10
1.333 player 11
1.000 player 12
2.000 player 13
--- player 14
--- player 15
--- player 16
1.500 player 17
3.500 player 18
1.500 player 19
--- player 20
1.000 player 21
1.000 player 22
0.000 player 23
0.000 player 24
0.500 player 25
0.000 player 26
0.667 player 27
现在,在 excel 中,我需要弄清楚如何创建一个公式,该公式返回具有前 5 个 OPS 值的玩家姓名的列.因此,我希望查询在 excel 中返回一个 5 x 1 列向量.我可以使用什么单元格公式来实现这一点?
Now, in excel, I need to figure out how to create a formula that returns a column of the names of the players with the top 5 OPS value. Thus, I would like for the query to return a 5 x 1 column vector in excel. What cell formula could I use to achieve this?
此外,鉴于他们将重复 OPS 的值,我需要表达式对关系具有鲁棒性.
Also, given that their will be repeating values of OPS, I need the expression to be robust against ties.
推荐答案
鉴于这样的数据设置:
单元格D2中的公式复制下来是:
The formula in cell D2 and copied down is:
=INDEX($B$2:$B$28,MATCH(1,INDEX(($A$2:$A$28=LARGE($A$2:$A$28,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,$B$2:$B$28)=0),),0))
即使玩家之间的 OPS 分数相同,这个公式也能奏效.
This formula will work even if there are tied OPS scores among players.
这篇关于提取excel中的前5个最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!