Извлечение верхних 5 максимальных значений в excel

У меня есть файл excel с одним столбцом, соответствующим имени игрока, а другой столбец соответствует статистике 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. Какую формулу я могу использовать для достижения этой цели?

Кроме того, учитывая, что они будут повторять значения OPS, мне нужно, чтобы выражение было устойчивым к связям.

Ответ 1

Учитывая настройку данных следующим образом:

Формула в ячейке D2 и скопированная вниз:

=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 среди игроков.

Ответ 2

Здесь три функции, которые вы хотите посмотреть здесь:

Я запустил образец в Excel с вашими значениями OPS в столбце B и игроками в столбце C, см. ниже:

В ячейках с A13 по A17 значения 1-5 были вставлены для указания n-го наивысшего значения.
В ячейке B13 была добавлена следующая формула: =LARGE($B$2:$B$11, A13)
В ячейке C13 была добавлена следующая формула: =INDEX($C$2:$C$11,MATCH(B13,$B$2:$B$11,0))
Эти формулы получают наивысший рейтинг OPS и Player на основе значения в A13.
Просто выберите и перетащите, чтобы скопировать эти формулы до следующих 4 ячеек, которые будут ссылаться на соответствующий рейтинг в столбце A.

Ответ 3

Поместите данные в сводную таблицу и сделайте на ней верхний n-фильтр

Ответ 4

=VLOOKUP(LARGE(A1:A10,ROW()),A1:B10,2,0)

Введите эту формулу в первой строке вашего листа, затем перетащите ее до пятой строки...

его простой vlookup, который находит значение large в массиве (A1:A10), функция ROW() дает номер строки (первая строка = 1, вторая строка = 2 и т.д.), а далее критерии поиска.

Примечание. Вы можете заменить ROW() на 1,2,3,4,5, как указано... если у вас есть эта формула, отличная от первой строки, то убедитесь, что вы вычитаете некоторые цифры из ROW() для получения точных результатов.

РЕДАКТИРОВАТЬ: проверить результаты связей

Это возможно, вам нужно добавить вспомогательный столбец на листе, вот ссылка . Дайте мне знать, если что-то кажется грязным....

Ответ 5

На мой взгляд, случай для ПТ (как @Nathan Fisher) является "без проблем", но я бы добавил колонку, чтобы упростить порядок по рангу (вверх или вниз):

OPS вводится как VALUES (сумма) дважды, поэтому я переименовал ярлыки столбцов, чтобы сделать более четким, что есть. PT находится на другом листе из данных, но может находиться на одном листе.

Ранг устанавливается щелчком правой кнопки мыши по точке данных, выбранной в этом столбце, и Show Values As... и Rank Largest to Smallest (есть другие опции) с полем Base как Player, а фильтр - Value Filters, Top 10... один:

Однажды в PT мощность этой функции очень легко может быть применена для просмотра данных многими другими способами без изменения формулы (ее нет!).

В случае галстука для последней позиции, включенной в фильтр, оба результата включены (Top 5 показывает шесть или более результатов). Связи для высшего ранга между двумя игроками будут отображаться как 1 1 3 4 5 для Top 5.