Большая функция Excel с дубликатами

Question

Я пытаюсь применить большую функцию, чтобы получить 5 самых больших значений из столбца, игнорируя дубликаты. Я немного искал и нашел несколько предложенных решений, но они, похоже, не работают для меня. В моем реальном приложении диапазон находится по формуле массива, поэтому я не уверен, что здесь будут работать формулы без массивов. Моя первоначальная функция была {= LARGE(IF($ A:$ A = $ A2, $ B:$ B), 2)}, перетаскиваемая вниз по столбцу. Замените 2 на 3 для третьего по величине, 4 для четвертого по величине и т.д. Это сработало, за исключением дубликатов.

Например:

Данные находятся в столбце 1, а желаемый результат - в столбце 2:

1    5 
2    4
2    3
3    2 
3    1
3
4
4
5

Вещи, которые я пробовал:

1. {= LARGE(ЕСЛИ (A:A <LARGE(A:A, i-1), A:A), 1)} для i-го наибольшего значения.

   -Ввод этого дает 3 для i = 3 и i = 4.

2. = БОЛЬШОЙ (А: А, СЧЕТЕСЛИ (А: А, MAX (А: А))+1)

   -Это, кажется, ничем не отличается, чем просто БОЛЬШОЙ (A:A, 2)

3. {= МАКС. (ЕСЛИ (A:A <LOOKUP (9.99999999999999E+307, A:A), A:A))}

   -Это возвращает значение 4, но я не уверен, как обобщить его до k-го наибольшего значения.

Решения VBA также хороши. Я сам пытался его кодировать, но не знал, как кодировать в формуле массива IF($ A:$ A = $ A2, $ B:$ B).

Answer 1

В B1 введите:

=LARGE($A$1:$A$9,1)

В B2 введите формулу массива:

=MAX(IF(A$1:A$9<B1,A$1:A$9))

и скопировать вниз. Формулы массива должны вводиться с помощью Ctrl + Shift + Enter, а не только клавиша Enter .

По сути, мы исключаем ранее найденные элементы из диапазона Large() / Max() .