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

Question

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

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

Я могу найти наибольшее значение с помощью=МАКС(A1:A100) Я могу найти наименьшее значение с помощью=НАИМЕНЬШИЙ(A1:A100,1)

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

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

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

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

Answer 1

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

=LARGE(D4:D9,3)

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

Answer 2

С данными в столбцеАнравиться:

ВВ1входить:

=MAX(A:A)

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

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

Затем скопируйтеБи 2вниз настолько, насколько вам нужно.

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

Answer 3

При всей той крутой помощи, которую я получил, я задумался, смогу ли я сделать то, что делает сводная таблица, и я придумал это: (даже самостоятельно LOL)

=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

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