Поиск наименований по ключевому слову в Excel
Задача: формировать список значений, найденных по ключевому слову. Сразу продемонстрирую результат, который получится в итоге:
Начало работы
Что имеем: 1) список данных (В4:В30), предварительно очищенный от дубликатов, 2) ячейка для ввода ключевого слова (Е1) и 3) диапазон под вывод результатов (предварительно пронумерованный по всей длине списка):
1 шаг. Формирование нумерованного списка по поиску
В дополнительный столбец (например, А) вносим формулу:
=ЕСЛИ (ПОИСК($E$1;B4)>0; СЧЁТЕСЛИ($B$4:B4;"*"&$E$1&"*");0)
где
ПОИСК($E$1;B4) — выполняет проверку совпадений. Если совпадение есть, выводит порядковый номер первого совпадающего символа. На самом деле неважно, какое это будет числовое значение, главное, что оно числовое. Если совпадения нет, выходит #ЗНАЧ. Поэтому
ЕСЛИ (ПОИСК($E$1;B4)>0 следует понимать как «если совпадение найдено».
Часть СЧЁТЕСЛИ($B$4:B4;"*"&$E$1&"*") означает - при нахождении неточного совпадения («звездочки» (*) указывают, что совпадение неточное) выводи значение повтора - это будет 1,2,3...
В целом, это выглядит так:
На заметку! Функция ПОИСК выполняет поиск без учета регистра. Если нужен поиск с учетом регистра, вместо ПОИСК надо использовать функцию НАЙТИ.
2 шаг. Выводим список результатов
С помощью ВПР в столбце для результатов (Е) находим результаты сравнения номеров из столбца D с номерами из А. Через ЕСЛИОШИБКА скрываем Н/Д (''''):
3* шаг. Наводим красоту
Шаг, возможно, не обязательной, так как основная работа сделала, поэтому помечен *. Но лично я люблю, когда все красиво и понятно, поэтому про «красоту» тоже расскажу.
Во-первых, сейчас уже можно скрыть вспомогательный столбец А, он для показа не нужен.
Во-вторых, лучше скрыть ненужные порядковые номера из столбца D. Пусть здесь работает такой принцип: сколько результатов найдено, столько номеров и видно.
Исходно номеров проставлено столько, сколько всего позиций в списке (ну а вдруг?). Но чаще всего они в таком количестве не нужны, поэтому схитрим и скроем их через Условное форматирование:
1. Выделяем ячейки D4:D30, выбираем Главная — Условное форматирование — Создать правило, Использовать формулу…
Устанавливаем формулу: =$Е4='''', что значит: если ячейка столбца Е пустая.
Нажимаем кнопку Формат, выбираем вкладку Шрифт, цвет Белый.
Жмем ОК во всех окнах, наслаждаемся результатом :)
Если бы можно было приложить файл, тоже бы добавила. Если кто знает способ, подскажите.