Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом

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

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

Задача

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

Решение

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

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

Примечание. Решение этой задачи аналогично решению приведенному в статье Поиск третьего (n-го) повтора в списках с Числами.

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

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

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

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

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

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)