Индекс N-го наибольшего значения в диапазоне

Question

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

    A B C D
  ----------
1  23 1 
2  12 2
3  23 3
4  36 4
5  19 5

Я хочу отобразить 3 самых больших значения, но я хочу отобразить соответствующее значение из B, а не значение в A. Я могу найти 3 самых больших значения с помощью

=LARGE(A1:A5,1)   # returns 36, I need it to return 4
=LARGE(A1:A5,2)   # returns 23, I need it to return 1
=LARGE(A1:A5,3)   # returns 23, I need it to return 3

Есть какой-либо способ сделать это? Я рассмотрел что-то на основе функции RANK, но RANK возвращает одинаковый ранг для одинаковых предметов, поэтому я не уверен, что делать.

Answer 1

Как насчет использования фильтра? Вы можете отсортировать от наибольших к наименьшим (поддерживается соответствие между элементами A и B), а затем легко прочитать значения из первых трех ячеек в столбце B

Answer 2

В D2:D4 используйте ваши 3 LARGE формулы, чтобы получить 36 , 23 , 23 а затем в E2 используйте эту "формулу массива", чтобы получить соответствующие значения из B1:B5, с учетом возможных дубликатов:

=INDEX(B$1:B$5,SMALL(IF(A$1:A$5=D2,ROW(A$1:A$5)-ROW(A$1)+1),COUNTIF(D$2:D2,D2)))

подтверждено с помощью CTRL+SHIFT+ENTER