Поиск ТЕКСТовых значений в 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 букв

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

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

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

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

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

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

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

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

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

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

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

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