Поиск ячеек по содержимому и вывод нужных значений Поиск ячеек по содержимому и вывод нужных значений Поиск ячеек по содержимому и вывод нужных значений

Чаще всего в Эксель я использую набор формул, призванный по тексту находить соответствие в ячейке и отмечать их либо копировать в соседний столбец.

Очень удобно использовать для сортировки столбцов данных, когда нужно сделать её быстро без загрузки в Power Query или какой-то дополнительной обработки.

Расскажу подробнее про это и в конце отдам шаблон.

Формула для поиска значения ячейки Эксель

На скрине выше, видно, что в столбец с фильтром попадают только ячейки, содержащие упоминание цены. А в столбец без фильтра, все прочие — это сделано для того, чтобы в дальнейшем удобно было забирать найденные значения и использовать в работе.

Формула для поиска по содержимому ячеек Excel

Не пугайтесь, всё намного проще, чем кажется. Сейчас всё расскажу.

Центральная формула состоит из следующих значений и будет выглядеть так:

=if(isnumber(find("цены",А3)),А3,"")

1. Функция find ищет заданный нами текст “цена” в выбранной ячейке А3. Далее если находит, то она возвращает номер символа, с которого начинается совпадение. Если не находит, то далее весь цикл формул не работает и на выходе получаем пустое значение в скобочках “” от функции if;
2. Функция isnumber принимает на вход только числа, соответственно, если find нашла искомое значение и отдала цифру, то функция срабатывает и продолжает цикл if, если нет, то опять получаем пустое значение в скобочках “” от функции if;
3. Функция if, если предыдущая функция isnumber срабатывает, возвращает нам значение ячейки A3, если нет, то пустое значение “”.

Таким образом, там, где есть упоминание “цена” мы получаем обратно эти ячейки. Для обычного поиска совпадения этой формулы достаточно. Но в нашем примере нужно найти все коммерческие запросы. Именно для этого мы используем перебор разных значений, как на этом скрине.

Перебор разных совпадений в исходной ячейке Эксель

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

Шаблон поиска совпадений по значению в ячейке

Чтобы вам не искать и не копировать формулу, я сделал шаблон для этой формулы. Он лежит в Гугл Таблице с двумя вариантами работы, совсем просто, где нужно ввести искомое значение в ячейку, вставить список слов. Всё работает.

  Шаблон лежит тут, пользуйтесь на здоровье.