Поиск в Excel по словам

Чтобы найти слово в таблице Excel, нажмите Ctrl+F, введите слово и нажмите «Найти все» — Excel покажет список всех ячеек, где оно встречается. Чтобы проверить формулой, есть ли в ячейке слово целиком, а не как часть другого слова, используйте =ЕЧИСЛО(ПОИСК(" кот ";" "&A2&" ")): пробелы вокруг слова и вокруг текста ячейки не дадут найти «кот» внутри «котлеты». Найти в ячейках любое или все слова из большого списка можно процедурами !SEMTools.

Чем отличается поиск по словам от простого поиска текста?

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

Поиск по словам предполагает показывать только ячейки, в которых слова совпадают целиком.

Поиск по слову в таблице Excel: Ctrl+F

1. Нажмите Ctrl+F (или «Главная → Найти и выделить → Найти»).
2. Введите слово в поле «Найти».
3. Нажмите «Найти далее», чтобы переходить по ячейкам по одной, или «Найти все» — внизу окна появится список всех найденных ячеек с адресами и значениями.
4. Чтобы выделить все найденные ячейки на листе, щёлкните в списке и нажмите Ctrl+A.

Кнопка «Параметры» открывает дополнительные настройки: «Искать: на листе / в книге» — искать во всех листах сразу; «Учитывать регистр»; «Ячейка целиком» — найти только ячейки, где кроме этого слова ничего нет; «Область поиска» — искать в формулах или в значениях. В поле «Найти» работают подстановочные знаки: * — любое количество символов, ? — один символ.

Важно: обычный поиск находит текст как фрагмент. По запросу «кот» Excel найдёт и «котлеты», и «скотч». Если нужны только целые слова, используйте фильтр или формулы ниже.

Поиск ячеек по одному слову целиком

Рассмотрим сначала простой случай — когда найти нужно одно слово.

Фильтрация по слову в Excel

Процедура фильтрации в Excel содержит 3 метода текстовой фильтрации, иными словами, фильтровать можно по 3 критериям вхождения слова:

1. ячейка содержит слово — тогда ставим пробелы перед и после слова;
2. начинается с него — пробел после;
3. заканчивается на него — пробел перед ним.

Проблема заключается в том, что в Excel нельзя фильтровать сразу по 3 критериям - можно только по двум. В этой ситуации есть простой лайфхак:

1. Сделать копию исходного столбца;
2. Удалить все символы, кроме текста и цифр (и пробелов между ними);
3. Добавить символы в конце и начале каждой ячейки столбца, например, символ "\";
4. Заменить оставшиеся пробелы на этот же символ;
5. После этого фильтровать по полученному столбцу уже наше слово с "\" перед и после него (пример - "\слово\").

Символ \ как раз и поможет отфильтровать целые слова.

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

Смотрите пример ниже:

Можно сделать иначе — добавить в начале и конце строк пробелы, но тогда при поиске и фильтрации слова перед пробелом слева и после пробела справа нужно использовать символ "*" (звездочку). Иначе Excel не учтет пробелы.

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

Поиск по слову в ячейке: формула

Идеальной функцией для формулы поиска слова будет функция ПОИСК.

Формула:

=ЕЧИСЛО(ПОИСК(" "&"вашеСлово"&" ";" "&A1&" "))

где вашеСлово — искомое слово, а A1 — ячейка, в которой мы его ищем.

ЕЧИСЛО нужна потому, что ПОИСК при отсутствии слова возвращает не ЛОЖЬ, а ошибку #ЗНАЧ!. Формула вида ПОИСК(…)>0 тоже выдаст #ЗНАЧ! вместо ЛОЖЬ, поэтому оборачиваем поиск в ЕЧИСЛО.

Однако нужно помнить, что пунктуацию нужно предварительно удалить.

Отфильтровать строки по слову формулой

В Excel 365 и 2021 функция ФИЛЬТР сразу выводит все строки, где встречается слово: =ФИЛЬТР(A2:A100;ЕЧИСЛО(ПОИСК(" кот ";" "&A2:A100&" "));"нет"). В старых версиях добавьте столбец с формулой проверки и отфильтруйте его по значению ИСТИНА.

Поиск по слову в Excel с помощью !SEMTools

Пожалуй, самое быстрое решение, доступное владельцам полной версии моей надстройки для Excel. Алгоритм простой — выделяем диапазон, жмем макрос, вводим слово, жмем «ОК».

Поиск ячеек по нескольким словам

Как выяснить для каждой ячейки большого диапазона, присутствует ли в ней хотя бы одно из списка слов? Да так, чтобы слово не просто содержалось внутри строки, в том числе внутри других слов, а находить именно целые слова? А если нужно найти пару сотен слов в десятках тысяч ячеек?

Найти любое слово из списка

Формулой: если список слов в D2:D4, проверка «есть хотя бы одно слово целиком» выглядит так:

=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" ")))>0

На сотнях слов и десятках тысяч строк такая формула заметно замедляет книгу — каждая ячейка проверяется против всего списка.

Надстройка !SEMTools с лёгкостью решает такого рода проблемы. Более того, практически вне зависимости от количества слов, распознавание их наличия происходит очень быстро даже в диапазоне из 10 000 ячеек и более.

Чтобы найти список слов диапазоне ячеек с помощью !SEMTools, нужно:

1. скопировать в соседний столбец диапазон, в котором мы хотим найти список слов. Это нужно для того, чтобы не стереть исходные данные,
2. вызвать макрос на панели настройки,
3. выбрать список слов, которые необходимо найти,
4. нажать OK.

Макрос даёт проверить, есть ли хотя бы одно слово из списка в ячейке.

Конкретные примеры использования

Данная процедура обычно полезна перед двумя другими — извлечь слова из списка и удалить из текста список слов. Почему не производить их сразу? Дело в том, что первый более медленный, а второй не даст понимания, какие ячейки затронула операция удаления.

Есть множество случаев применения данного макроса.
Специалисты по контекстной рекламе могут искать маркеры покупки, аренды, отзывы, многие хранят собственные длинные списки минус-слов, наличие которых в запросе означает, что его необходимо не включать в рекламную кампанию.

Если вас интересует, присутствуют ли в прайс-листе товары определенных производителей, список которых у вас уже имеется, или каковы остатки товара на складе по этим позициям, вам также может пригодиться данный макрос.

Найти все слова из списка

Данная процедура производит тот же поиск по словам, но с кардинальным отличием. Ключевое условие — чтобы ВСЕ слова содержались в ячейке, только тогда она возвращает ИСТИНА.

Формула для той же проверки: число найденных слов должно совпасть с числом слов в списке.

=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" ")))=ЧСТРОК($D$2:$D$4)

Почему слово не находится — частые ошибки

• Знаки препинания, прилипшие к слову. В «цена, доставка» после слова стоит запятая, а не пробел, и формула с пробелами его не увидит. Сначала удалите знаки препинания.
• Регистр. ПОИСК не различает регистр, а НАЙТИ различает: для неё «Цена» и «цена» — разные слова.
• Словоформы. Поиск слова «чехол» не найдёт «чехлы» — в этом и разница между поиском целых слов и поиском по паттерну.
• Неразрывные пробелы. В тексте, скопированном с сайтов, вместо обычных пробелов часто стоят неразрывные, и формула считает их частью слова. Перед поиском замените СИМВОЛ(160) на обычный пробел.
• Ошибка #ЗНАЧ! вместо ЛОЖЬ. ПОИСК не нашёл слово и вернул ошибку. Оберните его в ЕЧИСЛО или ЕСЛИОШИБКА.
• Ctrl+F не находит слово, которое точно есть. Включена галочка «Ячейка целиком» или «Учитывать регистр», выбрана «Область поиска: формулы», а текст получен формулой, или поиск идёт только по выделенному диапазону. Сбросьте выделение и проверьте параметры.

Поиск по слову в Excel — частые вопросы

Как найти слово в таблице Excel?

Нажмите Ctrl+F, введите слово и нажмите «Найти все». Чтобы искать во всех листах, в «Параметрах» выберите «Искать: в книге».

Как найти слово целиком, а не часть другого слова?

Формулой =ЕЧИСЛО(ПОИСК(" слово ";" "&A2&" ")). В Ctrl+F такой настройки нет: «Ячейка целиком» ищет ячейки, где кроме слова ничего нет.

Как отфильтровать строки, содержащие слово?

Автофильтр → «Текстовые фильтры» → «Содержит», или функцией ФИЛЬТР в Excel 365. Для поиска целых слов — по вспомогательному столбцу с формулой проверки.

Как найти ячейки с любым словом из списка?

Формулой =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" ")))>0 или процедурой !SEMTools «Найти любое слово из списка» — она быстрее на больших объёмах.

Итоги: как искать по слову в Excel

Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: извлечь слова из списка и удалить список слов из текста.