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

history

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


Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне 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. Поиск с учетом РЕГИСТРА .


Комментарии

Только для авторизованных пользователей

Аноним, 13 апреля 2017 г.
нет возможности копировать!
Михаил, 16 апреля 2017 г.
Файл примера внизу статьи, должно помочь.
Ольга, 22 декабря 2020 г.
спасибо огромное!
(только для авторизованных пользователей)

© Copyright 2013 - 2021 Excel2.ru. All Rights Reserved