Как сделать поиск в таблице Excel от А до Я

Друзья, в очередной статье нашего портала WiFiGid предлагаю посмотреть возможности поиска по словам в Excel. Причем разберем как методы обычного ручного поиска, привычного многим, так и поиск по формуле. Кратко, с разбивкой по способам, пошаговые инструкции с картинками – все в лучших традициях нашего сайта.

Сразу уточню: под «поиском по словам» в Excel можно понимать разные задачи. Иногда нужно просто найти ячейку с нужным текстом. Иногда нужно найти слово только в одном столбце. Иногда нужно проверить формулой, есть ли в ячейке нужный кусок текста. А иногда нужно не просто найти, а отфильтровать все строки, где встречается нужное слово. Поэтому ниже я покажу несколько вариантов, чтобы вы выбрали тот, который подходит именно под вашу ситуацию.

ПРИМЕЧАНИЕ. Если вы только начинаете работать с Excel и пока путаетесь в листах, ячейках, строках и формулах, можете дополнительно открыть наш базовый материал Excel для начинающих. В этой статье я не буду заново объяснять все основы таблиц, но постараюсь писать максимально простыми словами.

Способ 1 – Базовый ручной поиск

1. На вкладке «Главная» находим раздел «Редактирование». Щелкаем по кнопке «Найти и выделить» и жмем по кнопке «Найти». Это же окно можно запустить нажатием горячих клавиш Ctrl + F. На Mac обычно используется Command + F.

2. В поле «Найти» вводим нужное слово и нажимаем кнопку «Найти далее» или «Найти все». «Найти далее» ищет ваше слово последовательно одно за другим в документе – нашли первое, перешли далее, и так пока не найдете нужное или не закончится документ. «Найти все» сразу же предоставляет вам список всех найденных результатов.

Если вы нажали «Найти все», Excel покажет список совпадений в нижней части окна поиска. По этим строкам можно кликать мышью, и программа будет сразу переходить к найденной ячейке. Это удобно, когда совпадений много и вы хотите быстро пробежать глазами все варианты. Например, вы ищете фамилию клиента, название товара или часть адреса в большой таблице.

Сразу же обращаем внимание на базовые параметры поиска:

• Искать: на листе или в книге. Соответственно поиск ведется или на текущем листе, или по всему вашему файлу Excel.
• Просматривать: по строкам или по столбцам. Это не ограничивает поиск одним столбцом, а задает порядок обхода ячеек. Если нужно искать только в конкретном столбце, сначала выделяем этот столбец, и только потом запускаем поиск.
• Область поиска: формулы или значения. Если в вашей таблице есть формулы, а нужно найти конкретный результат вычисления, стоит покрутить эту опцию. Например, в ячейке может быть формула, а на экране отображается текст или число, которое вы ищете.
• Учитывать регистр. Если включить этот пункт, Excel будет различать большие и маленькие буквы. Например, «Мир» и «мир» будут считаться разными вариантами.
• Ячейка целиком. Ваше значение должно точно соответствовать всей ячейке. То есть если не ставить галочку здесь, то текст будет искаться даже в неполных вхождениях, а с этой галочкой соответствие должно быть полным.

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

Если Excel ничего не нашел, не спешите думать, что слова нет в таблице. Проверьте, не включены ли галочки «Учитывать регистр» и «Ячейка целиком». Также убедитесь, что поиск идет по всей книге или по нужному листу, а не только по случайно выделенному диапазону.

Способ 2 – Поиск по нужным ячейкам

Этот способ годится и для поиска в конкретном столбце или строке вашей таблицы.

На самом деле этот способ ничем не отличается от вышеизложенного.

1. Выделяем нужные нам ячейки. Можно выделить столбец или несколько столбцов. Ячейки могут быть смежными или несмежными. Суть – выделяйте то, что вам нужно, а поиск будет искать именно в этом диапазоне.
2. Пользуемся тем же самым поиском (Ctrl + F). Он будет работать исключительно по зоне выделения.

Пример: у вас есть таблица с клиентами, где в столбце A стоят имена, в столбце B телефоны, а в столбце C комментарии. Если вы ищете слово только в комментариях, выделите столбец C и нажмите Ctrl + F. Так Excel не будет прыгать по именам и телефонам, а покажет совпадения только в нужной части таблицы. Это намного быстрее и спокойнее, чем искать по всему листу.

Если нужно выделить несмежные области, зажмите Ctrl и мышью выделяйте нужные диапазоны. Но для новичков я бы рекомендовал начинать с одного столбца или одного прямоугольного диапазона. Так меньше шансов запутаться, где именно Excel сейчас ищет. Если таблица большая, можно сначала превратить ее в «умную таблицу» или хотя бы включить фильтр.

Способ 3 – Поиск с подстановочными знаками

В ручном поиске Excel можно использовать подстановочные знаки. Это удобно, если вы не помните точное слово, номер, артикул или часть текста. Самые полезные символы:

• * – заменяет любое количество символов.
• ? – заменяет один любой символ.
• ~ – помогает найти сам символ звездочки, вопросительного знака или тильды, если они реально есть в тексте.

Простой пример. Если в поиске написать Иван*, Excel может найти «Иван», «Иванов», «Иванова», «Иван Петрович» и другие варианты, которые начинаются с этого текста. Если написать товар?, программа будет искать варианты, где после слова «товар» стоит еще один символ. А если нужно найти обычную звездочку в ячейке, ищем ~*, иначе Excel воспримет звездочку как шаблон.

ПРИМЕР. У вас в таблице есть артикулы вида «AB-101», «AB-102», «AB-999». Если вы не помните точный номер, можно искать AB-*. Если номер всегда состоит из трех цифр, можно искать AB-???. Это особенно удобно в прайсах, складах и списках заказов.

Способ 4 – Поиск через фильтр

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

1. Кликните по любой ячейке внутри вашей таблицы.
2. Откройте вкладку «Данные» и нажмите «Фильтр». Можно также использовать сочетание Ctrl + Shift + L.
3. В заголовках столбцов появятся стрелочки. Нажмите стрелку в нужном столбце.
4. В строке поиска введите слово или выберите «Текстовые фильтры» – «Содержит».
5. Нажмите «ОК», и Excel покажет только строки с найденным словом.

Фильтр особенно полезен, когда вы работаете не с одной ячейкой, а с целыми строками. Например, нашли слово «оплачен» в столбце статуса – и сразу видите все заказы целиком: дату, клиента, сумму и комментарии. Если же использовать обычный Ctrl + F, Excel будет просто прыгать по совпадениям. Для полноценного отбора данных фильтр обычно удобнее.

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

Способ 5 – Поиск и замена

Иногда нужно не только найти слово, но и заменить его на другое. Для этого используется окно «Найти и заменить». Открывается оно сочетанием Ctrl + H. На вкладке «Заменить» в первое поле вводим то, что ищем, а во второе – новый текст.

Будьте внимательны с кнопкой «Заменить все». Она меняет сразу все совпадения, которые подходят под условия поиска. Если вы ошиблись в слове или забыли ограничить поиск нужным столбцом, можно испортить много ячеек сразу. Поэтому я советую сначала нажимать «Найти далее» и «Заменить», чтобы проверить несколько первых замен вручную. Только когда убедились, что все правильно, можно использовать «Заменить все».

Пример: в таблице везде написано «г.Москва», а вы хотите привести к виду «Москва». Открываем Ctrl + H, в поле поиска пишем г.Москва, в поле замены – Москва. Если в документе много подобных исправлений с точками, запятыми и пробелами, пригодится отдельный урок как заменить точку на запятую в Excel.

ВАЖНО! Перед массовой заменой лучше сохранить копию файла. Особенно если это рабочая таблица, прайс, база клиентов или финансовый отчет. Одна неудачная замена может поменять сотни ячеек, а вы заметите это не сразу.

Способ 6 – Функция ПОИСК

Функция ПОИСК уже используется непосредственно при построении формул. Она ищет позицию вашего вхождения в искомой строке. Например:

=ПОИСК(“w”;”wifigid”)

Из этой формулы вернется значение 1, т.к. буква «w» находится в первой позиции строки. Разумеется, можно делать поиск по ячейкам, заменив текстовые строки ссылками на ячейки.

Синтаксис у функции такой:

=ПОИСК(искомый_текст;просматриваемый_текст;[нач_позиция])

Первый аргумент – что ищем. Второй – где ищем. Третий аргумент необязательный: с какой позиции начать поиск. Например, если нужно искать не с первого символа, а после первых пяти знаков, можно указать начальную позицию 6. Если функция ничего не найдет, она вернет ошибку #ЗНАЧ!. Это нормально, потому что Excel не нашел указанное вхождение.

Функция ПОИСК не учитывает регистр ваших букв. Если вам нужно его учитывать, используем аналогичную функцию НАЙТИ. Также ПОИСК поддерживает подстановочные знаки * и ?, а НАЙТИ регистр учитывает и с такими шаблонами не работает.

На практике функцию ПОИСК редко используют просто ради номера позиции. Чаще ее комбинируют с другими функциями, чтобы понять, есть ли слово в ячейке. Например, если в ячейке A2 написано «Заказ оплачен картой», а мы ищем слово «оплачен», можно использовать формулу:

=ЕЧИСЛО(ПОИСК(“оплачен”;A2))

Если слово найдено, формула вернет ИСТИНА. Если слово не найдено, без дополнительной обработки может появиться ошибка. Поэтому для красивого результата часто используют связку с ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(“оплачен”;A2));ЛОЖЬ)

А если нужно вывести понятный текст, можно использовать функцию ЕСЛИ:

=ЕСЛИ(ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(“оплачен”;A2));ЛОЖЬ);”Найдено”;”Нет”)

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

Способ 7 – Функция НАЙТИ

Функция НАЙТИ очень похожа на ПОИСК, но есть главное отличие – она учитывает регистр. То есть «WiFiGid» и «wifigid» для нее уже разные варианты. Это полезно, когда регистр важен: артикулы, коды, логины, сокращения, технические обозначения.

Пример:

=НАЙТИ(“G”;”WiFiGid”)

Эта формула вернет позицию буквы «G». А вот если искать маленькую «g» в строке, где стоит большая «G», формула может вернуть ошибку. Поэтому НАЙТИ лучше использовать только тогда, когда вам действительно нужно отличать большие и маленькие буквы. В обычных таблицах с товарами, именами и комментариями чаще удобнее ПОИСК.

Как и ПОИСК, функция НАЙТИ возвращает номер позиции найденного текста. Если совпадения нет, будет ошибка #ЗНАЧ!. Чтобы не показывать ошибку пользователю, можно оборачивать формулу в ЕСЛИОШИБКА. Например:

=ЕСЛИОШИБКА(НАЙТИ(“ID”;A2);”Не найдено”)

Если вам нужно не только найти текст, но и вытащить часть строки, ПОИСК и НАЙТИ часто используют вместе с ЛЕВСИМВ, ПРАВСИМВ, ПСТР и ДЛСТР. Например, можно найти пробел, дефис или скобку и по этой позиции разделить текст. Похожую задачу я разбирал в статье как разделить текст в ячейке Excel по столбцам.

Способ 8 – Проверить, содержит ли ячейка слово

Частая задача: нужно не номер позиции, а простой ответ – есть слово в ячейке или нет. Например, в столбце A лежат комментарии клиентов, а вы хотите в столбце B вывести «Да», если в комментарии есть слово «срочно». Для этого используем связку ЕСЛИ, ЕЧИСЛО и ПОИСК:

=ЕСЛИ(ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(“срочно”;A2));ЛОЖЬ);”Да”;”Нет”)

Формула работает так. ПОИСК пытается найти слово «срочно» в ячейке A2. Если находит, возвращает номер позиции. ЕЧИСЛО проверяет, является ли результат числом. ЕСЛИ выводит «Да» или «Нет». ЕСЛИОШИБКА нужна, чтобы при отсутствии слова не вылезала ошибка #ЗНАЧ!.

Если вы хотите искать не конкретное слово, а значение из другой ячейки, замените текст в кавычках ссылкой. Например, в B1 у вас написано слово для поиска, а в A2 текст:

=ЕСЛИ(ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК($B$1;A2));ЛОЖЬ);”Да”;”Нет”)

Знак доллара в $B$1 нужен, чтобы при протягивании формулы вниз ссылка на поисковое слово не съезжала. Если вы не понимаете, почему ссылка меняется или не меняется при копировании формулы, посмотрите материал относительные и абсолютные ссылки в Excel. Это одна из базовых тем, без которой формулы часто ведут себя неожиданно.

Способ 9 – Поиск по нескольким словам

Иногда нужно проверить, есть ли в ячейке хотя бы одно из нескольких слов. Например, в комментарии нужно найти «срочно» или «важно». Самый простой вариант – использовать несколько ПОИСК внутри функции ИЛИ:

=ЕСЛИ(ИЛИ(ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(“срочно”;A2));ЛОЖЬ);ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(“важно”;A2));ЛОЖЬ));”Да”;”Нет”)

Формула длинная, но логика простая: если найдено первое слово или второе слово, выводим «Да». Если не найдено ничего, выводим «Нет». Для двух-трех слов такой способ еще терпимый. Если слов много, лучше делать отдельный справочник слов и уже строить формулу аккуратнее.

Если нужно, чтобы в ячейке были найдены сразу оба слова, используем функцию И вместо ИЛИ. Например, текст должен содержать и «оплачен», и «доставка». Логика такая: обе проверки должны вернуть ИСТИНА. Подробно про такие условия можно почитать в статье формула ЕСЛИ в Excel.

Почему Excel не находит слово?

Бывает так: глазами слово видно, а поиск его не находит. Причин несколько. Самая частая – лишние пробелы, неразрывные пробелы, переносы строк, другой регистр, скрытые символы или поиск не по той области. Например, между словами может стоять не обычный пробел, а неразрывный. Для глаза они похожи, а для Excel это разные символы.

Проверьте такие моменты:

• не включена ли галочка «Учитывать регистр»;
• не включена ли галочка «Ячейка целиком»;
• ищете ли вы на листе или по всей книге;
• не выделен ли случайно маленький диапазон;
• нет ли лишних пробелов в начале или конце текста;
• не спрятан ли текст внутри формулы, комментария или примечания;
• не применен ли фильтр, который скрывает часть строк.

Если проблема именно в пробелах, их нужно чистить отдельно. Иногда помогает функция СЖПРОБЕЛЫ, иногда ручная замена двойных пробелов, а иногда приходится удалять неразрывные пробелы. По этой теме у нас есть отдельная инструкция: как убрать пробелы в ячейках Excel.

ВАЖНО. Если вы ищете число, а Excel его не находит, проверьте формат ячейки. Число может быть записано как текст, а текст может визуально выглядеть как число. Из-за этого поиск, сортировка и формулы иногда дают неожиданный результат.

Частые вопросы

Как быстро найти слово в Excel?

Нажмите Ctrl + F, введите слово и нажмите «Найти далее» или «Найти все». Если нужно искать по всей книге, раскройте «Параметры» и выберите поиск не на листе, а в книге. Если нужно искать только в одном столбце, сначала выделите этот столбец.

 

Как найти слово только в одном столбце?

Выделите нужный столбец мышью или кликните по его букве сверху, например A или B. Потом нажмите Ctrl + F и выполните обычный поиск. Excel будет искать только в выделенной области.

 

Как найти все ячейки со словом сразу?

В окне поиска нажмите «Найти все». Внизу появится список всех найденных совпадений. По этому списку можно кликать, чтобы быстро переходить к нужной ячейке.

 

Как сделать поиск без учета регистра?

В обычном поиске не ставьте галочку «Учитывать регистр». В формулах используйте ПОИСК, потому что она не различает большие и маленькие буквы. Если регистр важен, используйте НАЙТИ.

 

Как проверить формулой, есть ли слово в ячейке?

Используйте связку ЕСЛИ, ЕЧИСЛО и ПОИСК. Например: =ЕСЛИ(ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(“срочно”;A2));ЛОЖЬ);”Да”;”Нет”). Формула выведет «Да», если слово найдено, и «Нет», если его нет.

 

Почему ПОИСК выдает #ЗНАЧ!?

Так происходит, когда искомый текст не найден. Это нормальное поведение функции. Чтобы вместо ошибки показывать нормальный текст или ЛОЖЬ, используйте ЕСЛИОШИБКА.

 

Чем ПОИСК отличается от НАЙТИ?

ПОИСК не учитывает регистр и поддерживает подстановочные знаки. НАЙТИ учитывает регистр и ищет текст более строго. Для обычного поиска по словам чаще удобнее ПОИСК, а для точных кодов и обозначений – НАЙТИ.

 

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

Используйте фильтр. Включите фильтр на вкладке «Данные», нажмите стрелку в нужном столбце и задайте текстовый фильтр «Содержит». Так Excel покажет не отдельные ячейки, а все строки, где найдено нужное слово.

Короткий итог: для обычного поиска используем Ctrl + F, для замены – Ctrl + H, для отбора строк – фильтр, а для проверки через формулу – ПОИСК, НАЙТИ, ЕСЛИ и ЕСЛИОШИБКА. Если Excel что-то не находит, сначала проверяем область поиска, регистр, полное совпадение, формат ячейки и лишние пробелы.