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

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

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

Задача

Выведем в отдельный диапазон значения, которые удовлетворяют критерию, причем критерий задан с использованием подстановочных знаков (*, ?). Рассмотрим различные варианты поиска.

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

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

Перечень найденных значений будем помещать в отдельный столбец.

Найдем все названия инструментов, которые начинаются с фразы дрел и длина строки которых составляет 5 символов.

Критерий будет вводиться в ячейку С2 и выглядеть так: «дрел?». Вопросительный знак является подстановочным знаком.

Для реализации этого варианта поиска требуется функция позволяющая использовать подстановочные знаки: используем функцию ПОИСК(). Согласно критерию «дрел?» (длина 5 символов) - должны быть выведены 3 значения: Дрель, дрель, Дрели.

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

Часть формулы ПОИСК($C$2;Список)=1 определяет: начинается ли значение из диапазона A5:A13 с фразы «дрел?».
Часть формулы ДЛСТР($C$2)=ДЛСТР(Список) определяет: равна ли длина строки значения из диапазона A5:A13 5 символам?

Знак * (умножить) между частями формулы представляет условие И (значение должно начинаться с дрел и иметь такую же длину, как и критерий, т.е. 5 букв). Критерию также будут соответствовать такие несуразные значения как дрел5, дрелМ и т.п. (если они содержатся в списке).

Б. Найти значения, которые начинаются со слова дрель или дрели и содержат как минимум 6 букв

Критерий вводится в ячейку E2 и выглядит так: «дрел??». В этом случае будут выведены все значения, в которые начинаются с текста-критерия (со слова дрел) и длиной как минимум 6 символов.

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

Часть формулы ПОИСК($E$2;Список)=1 определяет: начинается ли значение из диапазона A5:A13 с фразы «дрел??». Критерию также будут соответствовать значения начинающиеся с фраз дрелью, дрел23 и т.п.

В. Найти значения, у которых слово дрель находится в середине строки

Критерий вводится в ячейку G2 и выглядит так: «?дрель?». В этом случае будут выведены все значения, содержащие слово дрель, и у которых есть перед ним и после него как минимум 1 символ.

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

Часть формулы ПОИСК($G$2;Список) определяет: содержит ли значение из диапазона A5:A13 фразу «?дрел?». Критерию также будут соответствовать значения содержащие фразы 5дрел7, Адрелу и т.п.

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

Критерий вводится в ячейку I2 и выглядит так: «дрел?». В этом случае будут выведены все значения, заканчивающиеся на слова дрель или дрели.

 

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

Часть формулы ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2))) определяет: совпадают ли последние 5 символов значений из диапазона A5:A13 с фразой «дрел?». Критерию также будут соответствовать значения заканчивающиеся на фразы дрела, дрел6 и т.п.

СОВЕТ:
О поиске текстовых значений с учетом РЕгиСТра читайте в статье Поиск текстовых значений в списках. Часть3. Поиск с учетом РЕГИСТРА.

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

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