在excel中提取前5个最大值 [英] Extracting the top 5 maximum values in excel

问题描述

我有一个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个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！