问题描述
我有一个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值的玩家名字的公式。因此,我希望查询返回一个5 x 1列向量在Excel中。我可以使用什么细胞公式来实现这一点?
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.
推荐答案
给出一个这样的数据设置:
Given a data setup like this:
单元格D2中复制的公式为: / p>
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个最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!