Как в экселе найти нужное слово из таблицы

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

• Эти функции могут быть доступны не на всех языках.
• Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

• Искомый_текст Обязательный. Текст, который требуется найти.
• Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
• Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Замечание

• Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
• В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
• Если значение find_text не найдено, #VALUE! возвращается значение ошибки.
• Если аргумент начальная_позиция опущен, то он полагается равным 1.
• Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.
• Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Поиск или замена текста и чисел на листе

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

Совет: Для замены текста можно также использовать формулы. Чтобы узнать больше, проверка функции REPLACE или REPLACE, REPLACEB.

Поиск

Чтобы найти что-то, нажмите клавиши CTRL+F или перейдите в раздел Главная > Редактирование > найти & Выберите > Найти.

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

В поле Найти введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти, а затем выберите последний элемент поиска из списка.

• В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
• Используйте вопросительный знак (?), чтобы найти любой один символ, например s?t находит «sat» и «set».
• Используйте звездочку (*), чтобы найти любое количество символов, например s*d находит «sad» и «started».
• Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».

Совет: При выборе параметра Найти все в списке отображается каждое вхождение условий, которые вы ищете, а при выборе определенного вхождения в списке выделяется его ячейка. Результаты поиска Найти все можно отсортировать, выбрав заголовок столбца.

• В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
• Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
• Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите Формулы, Значения, Заметки или Примечания.

Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с элементом Формат, выберите Выбрать формат из ячейки, а затем выберите ячейку с форматированием, которое требуется найти.

Заменить

Чтобы заменить текст или цифры, нажмите клавиши CTRL+H или перейдите в раздел Главная > Редактирование > Найти & Выберите > Заменить.

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

В поле Найти введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти, а затем выберите последний элемент поиска из списка.

• В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
• Используйте вопросительный знак (?), чтобы найти любой один символ, например s?t находит «sat» и «set».
• Используйте звездочку (*), чтобы найти любое количество символов, например s*d находит «sad» и «started».
• Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».

Совет: При нажатии кнопки Заменить все каждое вхождение критерия, которое вы ищете, будет заменено, в то время как функция Replace обновляет одно вхождение за раз.

• В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
• Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
• Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите Формулы, Значения, Заметки или Примечания.

Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с элементом Формат, выберите Выбрать формат из ячейки, а затем выберите ячейку с форматированием, которое требуется найти.

Существует два разных метода поиска или замены текста или чисел на компьютере Mac. Во-первых, используется диалоговое окно Найти & Заменить . Во-вторых, используется панель поиска на ленте.

Диалоговое окно «Поиск & замена «

Панель поиска и параметры

Поиск ключевых слов в тексте

Поиск ключевых слов в исходном тексте — одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов — названия автомобильных марок — и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).

Способ 1. Power Query

На вкладке Добавление столбца выбираем команду Настраиваемый столбец (Add column — Custom column) и вводим формулу =Марки . После нажатия на ОК получим новый столбец, где в каждой ячейке будет вложенная таблица со списком наших ключевых слов — марок автопроизводителей:

Способ 2. Формулы

Если у вас версия Excel 2016 или новее, то нашу проблему можно весьма компактно и изящно решить с помощью новой функции ОБЪЕДИНИТЬ (TEXTJOIN) :

Логика работы этой формулы проста:

• Функция ПОИСК (FIND) ищет вхождение по очереди каждой марки в текущее описание запчасти и выдаёт либо порядковый номер символа, начиная с которого марка была найдена, либо ошибку #ЗНАЧ! если марки в описании нет.
• Затем при помощи функции ЕСЛИ (IF) и ЕОШИБКА (ISERROR) мы заменяем ошибки на пустую текстовую строку «», а порядковые номера символов — на сами названия марок.
• Полученный массив из пустых ячеек и найденных марок собирается в единую строку через заданный символ-разделитель с помощью функции ОБЪЕДИНИТЬ (TEXTJOIN) .

Сравнение быстродействия и буферизация запроса Power Query для ускорения

Для тестирования быстродействия возьмем в качестве исходных данных таблицу из 100 000 описаний запчастей. На ней получаем следующие результаты:

• Время пересчета формулами (Способ 2) — 9 сек. при первом копировании формулы на весь столбец и 2 сек. при повторном (сказывается буферизация, видимо).
• Время обновления запроса Power Query (Способ 1) гораздо хуже — 110 сек.

Само-собой, многое зависит от «железа» отдельно взятого ПК и установленной версии Office и обновлений, но общая картина, думаю, понятна.

Для ускорения запроса Power Query давайте буферизуем таблицу-справочник Марки, т.к. она у нас не меняется в процессе выполнения запроса и постоянно пересчитывать её (как это де-факто делает Power Query) не нужно. Для этого используем функцию Table.Buffer из встроенного в Power Query языка М.

Для этого откроем запрос Результаты и на вкладке Просмотр нажмём на кнопку Расширенный редактор (View — Advanced Editor) . В открывшемся окне добавим строку с новой переменной Марки2, которая будет буферизованной версией нашего справочника автопроизводителей и используем эту новую переменную далее в следующей команде запроса:

После такой доработки скорость обновления нашего запроса возрастает почти в 7 раз — до 15 сек. Совсем другое дело 🙂

Ссылки по теме

• Нечёткий текстовый поиск в Power Query
• Массовая замена текста формулами
• Массовая замена текста в Power Query функцией List.Accumulate

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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