Поиск в EXCEL третьего (n-го) повтора в списках с ЧИСЛАми

Рассмотрим поиск чисел в списке с повторами. Задав в качестве критерия для поиска нужное значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а также выделим его и другие повторы этого числа с помощью Условного форматирования.

Пусть исходный список содержит числовые значения от 1 до 4 и эти значения повторяются в хаотическом порядке в диапазоне A7:A17 .

Задача

Найдем в какой строке находится третий дубликат числа 2 (далее будем его называть повтором).

Решение

Разместим в ячейке С7 число, повтор которого хотим найти, т.е. 2. В ячейке D7 - номер повтора, т.е. 3 (см. Файл примера ).

Сначала для удобства создадим Динамический диапазон ИсхСписок , который будет содержать наш Исходный список. Теперь при добавлении в него новых значений, они будут автоматически учитываться формулами. Формула для Динамического диапазона выглядит так: =СМЕЩ(Лист1!$A$7;;;СЧЁТ(Лист1!$A$7:$A$21))

Решает эту задачу формула массива =НАИМЕНЬШИЙ(ЕСЛИ(ИсхСписок=C7;СТРОКА(ИсхСписок);"");D7)

Не забудьте нажать CTRL+SHIFT+ENTER после ввода формулы массива .

В файле примера также настроены правила Условного форматирования для отображения всех повторов (серый фон ячеек) и искомого повтора (зеленый).

Для того, чтобы у пользователя не было возможности ввести в ячейке С7 число не принадлежащее Исходному диапазону, в файле примера создан Выпадающий список .

Для того, что у пользователя не было возможности ввести в ячейке D7 номер повтора превышающий общее количество заданного в ячейке С7 числа, в файле примера создан еще один Выпадающий список .

СОВЕТ: Для поиска повторов в списках содержащих текстовые значения читайте статью Поиск третьего (n-го) повтора в списках с текстовыми значениями .