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