Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть3. Поиск с учетом РЕГИСТРА

Найдем текстовые значения, удовлетворяющие заданному пользователем критерию с учетом РЕгиСТРА. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.

Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне A5:A13 (см. Файл примера).

Задача

Выведем в отдельный диапазон значения, которые удовлетворяют критерию с учетом РЕгиСТРА. Рассмотрим различные варианты поиска.

А. Найти значения, которые содержат критерий

Для удобства написания формул создадим Именованный диапазон Список.

Выведем все значения, в которых содержится текст-критерий (например, слово дрель). Слово Дрель выведено не будет, т.к. сравнение производится с учетом РЕгиСТРА (буква Д - в верхнем регистре, а в критерии буква д находится в нижнем регистре). Критерий вводится в ячейку С2. Перечень найденных значений будем помещать в отдельный столбец.

Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(A:A;НАИМЕНЬШИЙ(
ЕСЛИ(ЕСЛИОШИБКА( НАЙТИ($C$2;Список);0)>0;СТРОКА(Список);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))

Для корректной работы этой формулы ячейка А30 должна быть пустой. Подробный разбор работы подобной формулы приведен в статье Поиск текстовых значений в списках. Часть1. Обычный поиск.

Б. Найти значения, которые совпадают с критерием (точное совпадение)

В этом случае будут выведены все значения, которые совпадают с критерием (с учетом РЕгистра). Критерий вводится в ячейку E2.

Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(A:A;НАИМЕНЬШИЙ(
ЕСЛИ(СОВПАД($E$2;Список);СТРОКА(Список);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))

B. Найти значения, которые начинаются с критерия

В этом случае будут выведены все значения, которые начинаются или совпадают с критерием (с учетом РЕгистра). Критерий вводится в ячейку G2.

Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(A:A;НАИМЕНЬШИЙ(
ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ($G$2;Список);0)=1;СТРОКА(Список);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))

Г. Найти значения, которые заканчиваются на критерий

В этом случае будут выведены все значения, которые заканчиваются или совпадают с критерием. Критерий вводится в ячейку I2.

Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(A:A;НАИМЕНЬШИЙ(
ЕСЛИ(СОВПАД($I$2; ПРАВСИМВ(Список;ДЛСТР($I$2)));СТРОКА(Список);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))

СОВЕТ:
О поиске текстовых значений с использованием подстановочных знаков читайте в статье Поиск текстовых значений в списках. Часть2. Подстановочные знаки. В статье Выделение слов, соответствующих критерию приведено решение аналогичной задачи с использованием Условного форматирования.

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

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