Поиск нужной строки эксель
Обновлено: 05.07.2024
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Описание
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы "n" в слове "printer", можно использовать следующую функцию:
Эта функция возвращает 4, так как "н" является четвертым символом в слове "принтер".
Можно также находить слова в других словах. Например, функция
возвращает 5, так как слово "base" начинается с пятого символа слова "database". Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
Эти функции могут быть доступны не на всех языках.
Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
Искомый_текст Обязательный. Текст, который требуется найти.
Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Замечание
Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Если аргумент начальная_позиция опущен, то он полагается равным 1.
Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой "МДС0093.МужскаяОдежда". Чтобы найти первое вхождение "М" в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — "МДС0093"). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».
Классический поиск «MS Office».
Условное форматирование (выделение нужных ячеек цветом)
Настройка фильтров по одному или нескольким значениям.
Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.
1) Классический поиск (обыкновенный).
Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).
Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».
Классический поиск в Excel
В параметрах поиска можно указать, где искать текст, искать ли слово в ячейке целиком или вхождение слова в предложения, учитывать ли регистр или нет.
Условное форматирование для искомых ячеек.
2) Еще один вариант поиска заключается в выделении всех ячеек содержащих искомое слово каким-нибудь цветом.
Такой поиск реализуется через опцию условное форматирование.
Для форматирования ячеек следует выделить диапазон ячеек, которых нужно найти слово. Далее на вкладке «Главная» нажать кнопку «Условное форматирование».
Настройка форматирования для выделения искомых слов
В открывшемся окне указать искомое слово и цвет заливки ячейки. Нажать «ОК». Все ячейки, содержащие нужное слово будут окрашены.
Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L.
Настройка фильтра для поиска слов
После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.
В зависимости от назначения и условий использования макрос может иметь разные конфигурации, но основная часть цикла перебора VBA макроса приведена ниже.
Sub Poisk()
Dim keyword As String
При работе с таблицами в Excel, особенно если эти таблицы очень большие и не помещаются на одном экране монитора, трудно визуально найти слово или фразу. Но в Excel есть специальная команда, которая позволяет выполнить поиск символов по всему тексту книги или на отдельных листах. Давайте рассмотрим пару способов, как можно это сделать.
Способ 1
Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов.
Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).
Вместо клавиатурной комбинации можно использовать кнопку поиска на панели Главная — Найти и выделить — Найти.
По умолчанию открывается маленькое окно, в которое нужно вписать искомое слово и нажать клавишу Найти все или Найти далее.
- Найти все — выполнит поиск всех совпадений с указанной фразой. В окне ниже появится список, в котором будет указана фраза, содержащая искомые символы, а также место в документе, где символы были найдены.
Для удобства работы это окно можно увеличить, потянув за маркер в правом нижнем углу.
Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.
В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.
- Найти далее — выделит и перейдет к следующей ячейке, в которой найдены искомые символы. Таким образом можно последовательно переходить от одной найденной ячейке к другой.
Дополнительные параметры поиска слов и фраз
Здесь можно указать дополнительные параметры поиска.
- на листе — только на текущем листе;
- в книге — искать во всем документе Excel, если он состоит из нескольких листов.
- по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
- по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.
Выбор варианта, как просматривать, актуален, если в таблице много данных и есть какая-то необходимость просматривать по строкам или столбцам. Пользователь увидит, как именно просматривается таблица, когда будет нажимать кнопку Найти далее для перехода к следующему найденному совпадению.
Область поиска — определяет, где именно нужно искать совпадения:
- в формулах;
- в значениях ячеек (уже вычисленные по формулам значения);
- в примечаниях, оставленных пользователями к ячейкам.
А также дополнительные параметры:
- Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.
Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.
Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».
- Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
- Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.
Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки.
Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.
Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.
Способ 2
Второй способ, который позволяет найти искомое слово в таблице Excel, не является именно поиском, но также может быть удобен для работы. Это фильтр по фразе (символам), который позволяет вывести на экране только те строки, которые содержат искомые символы.
Для этого нужно щелкнуть мышкой по любой ячейке, среди которых нужно искать, нажать на вкладке Главная — Сортировка и фильтры — Фильтр.
В первой строке у заголовков ячеек появятся стрелочки выпадающего меню.
Нужно нажать на стрелочку в том столбце, в котором будет выполняться фильтр. В нашем случае нажимаем стрелочку в столбце Слова и пишем символы, которые мы будем искать — «замок». То есть мы выведем только те строки, в которых есть слово «замок».
Результат будет таков.
Таблица до применения фильтра и таблица после применения фильтра.
Фильтрация не изменяет таблицу и не удаляет строки, она просто показывает искомые строки, скрывая не нужны. Чтобы удалить фильтр, нужно нажать на стрелочку в заголовке — Удалить фильтр с слова…
Также можно нажать на стрелочку и выбрать Текстовые фильтры — Содержит и указать искомые символы.
И далее ввести искомую фразу, например «Мюнхен».
Результат будет таков — только строки, содержащие слово «Мюнхен».
Этот фильтр сбрасывается также, как и предыдущий.
Таким образом, у пользователя есть варианты поиска слова в Excel — собственно сам поиск и фильтр.
Видеоурок по теме
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
Девиз: поднемите руки выше!
по
Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.
Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.
О работе с сайтом
Мы используем cookie.
Публикуя материалы на сайте (комментарии, статьи, разработки и др.), пользователи берут на себя всю ответственность за содержание материалов и разрешение любых спорных вопросов с третьми лицами.
При этом редакция сайта готова оказывать всяческую поддержку как в публикации, так и других вопросах.
Если вы обнаружили, что на нашем сайте незаконно используются материалы, сообщите администратору — материалы будут удалены.
| Раздел функций | Текстовые |
| Название на английском | SEARCH |
| Волатильность | Не волатильная |
| Похожие функции | НАЙТИ (функция), поиск и замена (процедура) |
Что делает функция ПОИСК?
Эта функция аналогична функции НАЙТИ и так же ищет подстроку в строке. Когда искомое найдено, отображается его позиция в тексте в виде числа.
Отличие от функции НАЙТИ в том, что ПОИСК не принимает в расчет регистр текста. Как искомого, так и того, в котором мы ищем.
Есть также процедура Найти и Заменить в Excel — у нее есть свои преимущества, такие, как подстановочные операторы.
Синтаксис
- ИскомыйТекст — символ или сочетание, которое ищем
- СтрокаВКоторойИщем — ячейка, текстовое значение или любое возвращаемое другой функцией выражение.
- Стартовая позиция — опциональный параметр, при отсутствии поиск происходит с первого символа
Если текст содержит более одного вхождения, возвращается позиция первого.
Третий (опциональный) параметр используется для поиска с определенной позиции в тексте и по умолчанию равен 1.
Форматирование
При поиске дат функция ПОИСК, как и все текстовые функции, воспринимает их как числа, поэтому для корректного поиска может понадобиться функция ТЕКСТ.
При этом логические значения ИСТИНА и ЛОЖЬ конвертируются в текст, соответствующий их написанию.
Поиск символа в ячейке
Наиболее простой пример использования функции — осуществление поиска определенного символа в ячейке.
Логика проста — если поиск позиции символа не возвращает ошибку, значит, символ в ячейке присутствует:
Извлечь первое слово
В этом простейшем примере извлекаем первое слово из ячейки с помощью комбинации — функция ЛЕВСИМВ + функция ПОИСК. Поскольку пробел — регистронезависимый символ, для этого случая можно использовать и функцию НАЙТИ.
Таблица выше была использована для извлечения имени из строки с именем и фамилией.
- ПОИСК возвращает позицию пробела между именем и фамилией.
- Длина имени вычисляется как позиция пробела минус 1. извлекает имя на основе его длины.
Другие примеры использования
Найти первую цифру в ячейке:
Найти первую цифру в ячейке и вернуть все, что перед ней:
Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ»:
Функция ПОИСК в формуле массива
Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.
И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:
Отличие этой формулы массива от предыдущих — ее нужно вводить без фигурных скобок, они появятся при вводе формулы сочетанием Ctrl + Shift + Enter (вместо обычного Enter ). В формуле выше, где явно прописаны все буквы, фигурные скобки вводятся вручную — это явное указание строкового массива.
Что происходит в этой формуле?
- Функция СТРОКА с численным аргументом «65:90» возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы; возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
- Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
- Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был
Аналогичная формула для кириллицы:
Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:
Есть еще множество комбинаций функции ПОИСК с другими функциями Excel, смотрите разделы:
Функция ИЛИ
Функция И
Функция ЗНАЧЕН
Удалить первое слово в ячейке Excel
Читайте также: