Найти наибольшее значение, N-е по величине и другие до него? (игнорируя дубликаты)

Question

Итак, у меня есть столбец «А», который выглядит так:

I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0

Я могу найти самое большое значение с = MAX(A1:A100) Я могу найти самое маленькое значение с = SMALL(A1:A100,1)

Как мне найти тот, что до самого большого, и тот, что до этого. Я могу использовать Large для вызова N-го значения, но тогда как мне узнать, сколько N-значений считает функция, чтобы я мог узнать, какие позиции она использует, ссылаясь на последние 3 (наибольшее и 2 ранее)?

Игнорирование дубликатов - в примере наибольшее значение равно 21, предыдущему - 15, а перед - 8. Если я сделаю = LARGE(A1:A100,3), он вернет 15, поскольку в столбце есть два em. И я ищу 8 как третье по величине значение.

Ниже вы найдете полезных людей из Интернета, которые предоставили сводную таблицу, решение Array Formula, и я наконец смог создать простую формулу один.

Вы можете предоставить VBA для работы, если вы настаиваете, что у нас есть все, в противном случае уже достаточно.

Answer 1

Используйте сводную таблицу, чтобы быстро получить недопустимую версию вашего списка (просто добавьте столбец к меткам строк), затем используйте функции LARGE и SMALL в недопустимом списке:

=LARGE(D4:D9,3)

Если хотите, вы можете привести в порядок сводную таблицу, удалив общий итог и отключив заголовки полей, чтобы в ней просто отображался ваш дублированный список.

Answer 2

С данными в столбце A, как:

В B1 введите:

=MAX(A:A)

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

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

Затем скопируйте B2 так далеко, как вам нужно.

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

Answer 3

Со всей той классной помощью, которую я получил, я подумал, смогу ли я сделать то, что делает сводная таблица, и я придумал это:

=IF(COUNTIF($A$1:A1,A1)<=1,A1,"")

Создание параллельного столбца с интересующими данными решает это. (например, вставьте в C1 и скопируйте вниз)

Он предоставляет начальный дедуплицированный список, из которого вы можете основывать большие и маленькие. Как простое решение, оно не должно мешать каким-либо изменчивым вещам, которые могут иметь место в ваших документах. И я, например, могу жить без VBA. Любые неожиданные повороты должны быть сдерживаемыми с IFERROR, если существуют смешанные типы данных.

Answer 4

@ helena4 Попробуйте эту формулу массива

=LARGE(IF(A1:A15 < LARGE(A1:A15,1),A1:A15),3)

Затем нажмите Ctrl+Shift+Enter

он будет игнорировать дубликаты.