Здравствуйте, друзья. Сегодня покажу вам, как найти ячейки, текст в которых содержит нужную вам строку. Почему я решил написать об этом отдельную статью? Для поиска чаще всего используют функции ВПР и ПОИСКПОЗ, которые определяют полное совпадение строк, либо ближайшее большее, меньшее значение. А чтобы найти частичное совпадение, придётся что-нибудь придумать.
Покажу на примере, чего я хочу добиться. Есть прайс, в котором названия товаров, цены и остатки на складе. В таблице «Заказ» нужно ввести часть названия и получить первую найденную строчку, в которой есть этот текст.
Решение проще, чем может показаться. Многие формулы поддерживают подстановочные символы:
- * — любое количество любых символов
- ? – один любой символ
Воспользуемся ими, чтобы сформировать нужный запрос в формуле. Искомое значение будет примерно таким: *значение*. То есть, любое количество символов до и после указанной строки. Если использовать такой подход, например, в ВПР, можно получить желаемое:
=ВПР(«*»&$ значение &»*»; массив ; столбец_для_вывода ; 0)
Смотрите на картинке, я ищу «pixel», и нахожу модель, у которой в названии есть это слово.
В приведенной формуле знак «&» — оператор конкатенации (объединения) строк. Больше про объединение строк — читайте здесь
Так же можно сделать, когда применяется поиск с помощью ПОИСКПОЗ и ИНДЕКС:
=ИНДЕКС( массив_для_вывода ; ПОИСКПОЗ(«*»& значение &»*»; массив_для_поиска ;0))
Результат аналогичен, всё работает, но есть и неожиданности. Если в любом из рассмотренных вариантов вообще не задать искомое значение:
Наша формула вернула не ошибку, а данные из первой строки таблицы. Это произошло, т.к. подстановочные символы командуют формуле искать любые символы. Не критично, но можно и убрать с помощью функции ЕСЛИ:
Смотрите на рисунке выше, нет искомого текста, нет и результата.
Кроме того, если совпадения не найдены, будет выведена ошибка #Н/Д. Как её обработать, я рассказывал в этой статье.
А у меня всё, жду ваших вопросов!
Скачать пример