Поиск в Excel по словам
Чем отличается поиск по словам от простого поиска текста?
В первую очередь тем, что, если искать короткие слова обычным вхождением, мы можем найти слова, находящиеся внутри других слов. В результат фильтрации попадут ячейки, которые нам не нужны.
Поиск по словам предполагает показывать только ячейки, в которых слова совпадают целиком.
Поиск по одному слову
Рассмотрим сначала простой случай — когда найти нужно одно слово.
Фильтрация по слову в Excel
Процедура фильтрации в Excel содержит 3 метода текстовой фильтрации, иными словами, фильтровать можно по 3 критериям вхождения слова:
- ячейка содержит слово — тогда ставим пробелы перед и после слова
- начинается с него — пробел после
- заканчивается на него — пробел перед ним.
Проблема заключается в том, что в Excel нельзя фильтровать сразу по 3 критериям — можно только по двум. В этой ситуации есть простой лайфхак:
- Сделать копию исходного столбца
- Удалить все символы, кроме текста и цифр (и пробелов между ними).
- Добавить символы в конце и начале каждой ячейки столбца, например, символ «\».
- Заменить оставшиеся пробелы на этот же символ
- После этого фильтровать по полученному столбцу уже наше слово с «\» перед и после него (пример — «\слово\»).
Символ \ как раз и поможет отфильтровать целые слова.
Как вы заметили, шагов целых пять, поэтому, если вам нужно найти только одно слово один раз, лучше просто 3 раза отфильтровать. Но если нужно будет искать несколько слов, это существенно ускорит процесс.
Смотрите пример ниже:
Фильтр столбца по слову, даже самому короткому
Можно сделать иначе — добавить в начале и конце строк пробелы, но тогда при поиске и фильтрации слова перед пробелом слева и после пробела справа нужно использовать символ «*» (звездочку). Иначе Excel не учтет пробелы.
Итак, чтобы сделать поиск или фильтрацию по слову в Excel, нужно добавить символ пробела справа и слева ко всем ячейкам столбца и после этого осуществлять поиск и фильтрацию по слову вместе с пробелами перед и после него.
Поиск по слову в ячейке: формула
Идеальной функцией для формулы поиска слова будет функция ПОИСК.
где вашеСлово — искомое слово, а A1 — ячейка, в которой мы его ищем.
Однако нужно помнить, что пунктуацию нужно предварительно удалить.
Найти слово с помощью формулы
Поиск по слову в Excel с помощью !SEMTools
Пожалуй, самое быстрое решение, доступное владельцам полной версии моей надстройки для Excel. Алгоритм простой — выделяем диапазон, жмем макрос, вводим слово, жмем «ОК».
Поиск по нескольким словам
Как выяснить для каждой ячейки большого диапазона, присутствует ли в ней хотя бы одно из списка слов? Да так, чтобы слово не просто содержалось внутри строки, в том числе внутри других слов, а находить именно целые слова? А если нужно найти пару сотен слов в десятках тысяч ячеек?
Найти любое слово из списка
Настройка !SEMTools с лёгкостью решает такого рода проблемы. Более того, практически вне зависимости от количества слов, распознавание их наличия происходит очень быстро даже в диапазоне из 10 000 ячеек и более.
Чтобы найти список слов диапазоне ячеек с помощью !SEMTools, нужно:
- скопировать в соседний столбец диапазон, в котором мы хотим найти список слов. Это нужно для того, чтобы не стереть исходные данные,
- вызвать макрос на панели настройки,
- выбрать список слов, которые необходимо найти,
- нажать OK.
Макрос дает проверить, есть ли хотя бы одно слово из списка в ячейке.
Конкретные примеры использования
Данная процедура обычно полезна перед двумя другими — извлечь слова из списка и удалить из текста список слов. Почему не производить их сразу? Дело в том, что первый более медленный, а второй не даст понимания, какие ячейки затронула операция удаления.
Есть множество случаев применения данного макроса.
Специалисты по контекстной рекламе могут искать маркеры покупки, аренды, отзывы, многие хранят собственные длинные списки минус-слов, наличие которых в запросе означает, что его необходимо не включать в рекламную кампанию.
Если вас интересует, присутствуют ли в прайс-листе товары определенных производителей, список которых у вас уже имеется, или каковы остатки товара на складе по этим позициям, вам также может пригодиться данный макрос.
Найти все слова из списка
Данная процедура производит тот же поиск по словам, но с кардинальным отличием. Ключевое условие — чтобы ВСЕ слова содержались в ячейке, только тогда она возвращает ИСТИНА.
Поиск или замена текста и чисел на листе
В этом курсе:
- Перемещение и копирование ячеек и их содержимого
Статья - Изменение ширины столбцов и высоты строк в Excel
Статья - Поиск или замена текста и чисел на листе
Статья - Объединение и отмена объединения ячеек
Статья - Применение проверки данных к ячейкам
Статья - Импорт и экспорт текстовых файлов (в формате TXT или CSV)
Статья
Поиск или замена текста и чисел на листе
Функции поиска и замены в Excel используются для поиска в книге необходимой информации, например определенного числа или текстовой строки. Вы можете найти элемент поиска для справки или заменить его другим элементом. В условия поиска можно включать под вопросицы, тильды и звездочки, а также числа. Искать можно по строкам и столбцам, в примечаниях или значениях, а также на листе или во всей книге.
Поиск
Чтобы найти что-то, нажмите CTRL+Fили перейдите на главная > редактирования> Найти & Выберите > Найти.
Примечание: В следующем примере мы нажали кнопку Параметры >>, чтобы отдемонстрировать все диалоговое окно Найти. По умолчанию он будет отображаться со скрытыми параметрами.
В поле Найти что: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Найти: и выберите последний элемент поиска из списка.
Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?),звездочки (*), тильды (
Используйте знак вопроса (?), чтобы найти любой отдельный символ. Например, если найти "м?т" и "множество", то будут отспросюмы.
Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д", "г" и "начало".
), за которой следует ?, *или
, чтобы найти вопросии, звездочки или другие символы тильды, например fy91
Нажмите кнопку Найти все или Найти далее, чтобы выполнить поиск.
Совет: Если нажать кнопку Найтивсе, будут перечислены все вхождения ищемого условия, а при щелчке определенного вхождения в списке будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все, щелкнув заголовок столбца.
Нажмите кнопку>>, чтобы при необходимости определить поиск.
В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.
Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения,заметки или примечания.
Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти. На вкладке Заменить доступны только формулы.
Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.
Совпадение всего содержимого ячеек. Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти: .
Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формат исделайте выбор в диалоговом окне Найти формат.
Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.
Заменить
Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на главная > редактирования> Найти & Выберите > Заменить.
Примечание: В следующем примере мы нажали кнопку Параметры >>, чтобы отдемонстрировать все диалоговое окно Найти. По умолчанию он будет отображаться со скрытыми параметрами.
В поле Найти что: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Найти: и выберите последний элемент поиска из списка.
Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?),звездочки (*), тильды (
Используйте знак вопроса (?), чтобы найти любой отдельный символ. Например, если найти "м?т" и "множество", то будут отспросюмы.
Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д", "г" и "начало".
), за которой следует ?, *или
, чтобы найти вопросии, звездочки или другие символы тильды, например fy91
В поле Заменить на: введите текст или числа, которые вы хотите использовать для замены текста поиска.
Нажмите Заменить все или Заменить.
Совет: При нажатиикнопки Заменить все вхождения ищемого условия заменяются, а при этом заменяются по одному вхождению за раз.
Нажмите кнопку>>, чтобы при необходимости определить поиск.
В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.
Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения,заметки или примечания.
Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти. На вкладке Заменить доступны только формулы.
Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.
Совпадение всего содержимого ячеек. Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти: .
Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формат исделайте выбор в диалоговом окне Найти формат.
Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.
Существует два разных способа поиска или замены текста или чисел на компьютере Mac. Первый из них — использование диалогового & поиска. Второй вариант — использование панели поиска на ленте.