ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Описание
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:
Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».
Можно также находить слова в других словах. Например, функция
возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
Эти функции могут быть доступны не на всех языках.
Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
Искомый_текст Обязательный. Текст, который требуется найти.
Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Замечание
Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак ( ?) и звездочку ( *). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.
Если аргумент начальная_позиция опущен, то он полагается равным 1.
Если Нач_позиция не больше 0 или больше, чем длина аргумента просматриваемый_текст , #VALUE! возвращено значение ошибки.
Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Поиск слов в Excel
В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк, столбцов и позиций. Отыскать какие-то конкретные данные в таком массиве бывает трудно. А иногда — вообще невозможно. Эту задачу можно упростить. Разберитесь, как в Экселе найти нужное слово. Так вам легче будет ориентироваться в документе. И вы сможете быстро перейти к искомой информации.
В редакторе таблиц существует несколько способов поиска
Поиск ячеек
Чтобы отобразились адреса всех ячеек, в которых есть то, что вы ищите, сделайте следующее:
- Если у вас Office 2010, перейдите в Меню — Правка — Найти.
- Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
- В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
- Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
- В поле напечатайте слово, фразу или цифры, которые надо отыскать.
- Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
- Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
- Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
- При клике на каждую строчку будет выделяться соответствующая ячейка.
- Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
- Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
- Эти столбцы также «растягиваются».
Параметры поиска
Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:
- Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
- Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
- Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П. », отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
- Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».
Также вы можете зайти в настройки:
- В окне «Найти» нажмите «Параметры».
- В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
- Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
- Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.
Параметры формата ячеек
Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:
- В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
- Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
- Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.
Поиск нескольких слов
В Excel можно отыскать клетки по целым фразам. Но если вы ввели ключ «Синий шар», система будет работать именно по этому запросу. В результатах не появятся значения с «Синий хрустальный шар» или «Синий блестящий шар».
Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:
- Напишите их в строке поиска.
- Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
- Этим способом можно задать ключ даже с отдельными буквами.
Фильтр
Вот как искать в Экселе, используя фильтр:
- Выделите какую-нибудь заполненную ячейку.
- Нажмите Главная — Сортировка — Фильтр.
- В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
- В текстовом поле введите запрос и нажмите «OK».
- В столбце будут отображаться только ячейки, содержащие искомую фразу.
- Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
- Чтобы отключить фильтр, повторно нажмите на него в сортировке.
Этот способ не подойдёт, если вы не знаете, в каком ряду нужное вам значение.
Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.
Формула СОВПАД для точного поиска текста в столбце Excel
Функция ВПР и другие поисковые функции выборки в Excel не учитывают большинства символов в просматриваемых значениях. Например, для значений «Оплата» и «оплата» функция ВПР будет возвращать один и тот же результат. Если большая или маленькая буква имеют значения воспользуйтесь функцией СОВПАД.
Как выполнить точный поиск текстового значения в столбце Excel
Ниже на рисунке представлен список идентификаторов MAC-адресов сетевых карт компьютеров и даты последнего входа пользователя в панель администрирования. Идентификаторы на строках листа Excel 3 и 10 выглядят идентичными за исключением двух символов по средине (Ef и eF). Допустим нам необходимо найти идентификатор MAC-адреса сетевой карты с учетом регистра символов (большая или маленькая буква). Формула:
На выше приведенном рисунке отображены результаты вычислений сразу двух формул. В первой формуле использована функция ВПР в ячейке D4: =ВПР(D3;A2:B11;2;ЛОЖЬ), а во второй СОВПАД. Хоть в ячейке D3 введен идентификатор MAC-адреса с 10-ой строки листа, функция возвращает в результате вычислений значения с 3-й строки.
Вторая формула, приведенная ниже с функцией СОВПАД возвращает правильный результат:
Теперь вы узнаете, как необходимо использовать функции СУММПРОИЗВ и СТРОКА с целью определения номера строки для функции ИНДЕКС составив простую формулу. Функция СОВПАД содержит 2 аргумента и сравнивает их значения между собой. В результате вычисления возвращает логическое значение ИСТИНА если оба ее аргументы идентичны (с учетом верхнего и нижнего регистра).
Принцип работы формулы для точного совпадения при поиске по столбцам
Функция СОВПАД умещенная в функцию СУММПРОИЗВ либо в формулу массива может быть использована для сравнения диапазонов текстовых значений с другими типами значений. В результат будет возвращена таблица массива с логическими значениями ИСТИНА и ЛОЖЬ. В данном примере функция СОВПАД возвращает значение ИСТИНА только при сравнении значений ячеек A10 и D3. При арифметической операции внутри функции умножения таблицы массива на таблицу, возвращенную функцией СТРОКА значение ИСТИНА заменяется на число 1, а ЛОЖЬ на 0. В результате чего функция СУММПРИОЗВ суммирует все значения и возвращает число 10 для функции ИНДЕКС. Ведь все остальные значения равны ЛОЖЬ=0, а все что умножаем на ноль =0. Схематически принцип работы функции СУММПРОИЗВ можно изобразить на такой таблице:
Число 10 возвращено через функцию СУММПРОИЗВ используется как аргумент с номером строки в функции ИНДЕКС, которая возвращает содержимое смежной ячейки справа B10 на 10-ой строке листа в диапазоне B1:B11.
Найти в ячейке любое слово из списка
Предположим, вы получаете от поставщика/заказчика/клиента заполненную таблицу с перечнем продукции: 
и эту таблицу необходимо сравнить с артикулами/кодами товара в имеющемся у вас каталоге продукции: 
Как видно — в нашем каталоге только артикулы без наименований. У заказчика же помимо артикулов еще и название товара, т.е. много лишнего. И вам надо понять какие товары присутствуют в вашем каталоге, а какие нет: 
Стандартных формул в Excel для подобного поиска и сравнения нет. Конечно, можно попробовать применить ВПР с подстановочными символами сначала к одной таблице, а затем к другой. Но если подобную операцию необходимо проделывать раз за разом, то прописывать по несколько формул к каждой таблице прямо скажем — не комильфо.
Поэтому я и решил сегодня продемонстрировать формулу, которая без всяких доп. манипуляций поможет такое сравнение сделать. Чтобы разобраться самостоятельно рекомендую просмотреть файл:
просмотреть файл:
Tips_All_AnyoneOfArray.xls (49,5 KiB, 15 987 скачиваний)
На листе «Заказ» в этом файле таблица, полученная от заказчика, а на листе «Каталог» наши артикулы.
Сама формула на примере файла будет выглядеть так:
=ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2 );Каталог!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)
эта формула вернет название артикула, если в тексте есть хоть один артикул из каталога и #Н/Д (#N/A) если артикул не найден в каталоге.
Прежде чем облагородить эту формулу всякими дополнениями(вроде виде убирания ненужных #Н/Д ) давайте разберемся как она работает.
Функция ПРОСМОТР (LOOKUP) ищет заданное значение( 2 ) в указанном диапазоне(массиве — второй аргумент). В качестве диапазона обычно приводится массив ячеек, но функция ПРОСМОТР имеет первую нужную нам особенность — она старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом. Иными словами она вычисляет выражение в этом аргументе, чем мы и пользуемся, подставив в качестве второго аргумента выражение: 1/ПОИСК(Каталог!$A$2:$A$11;A2) . Часть ПОИСК(Каталог!$A$2:$A$11;A2) ищет поочередно каждое значение из списка Каталога в ячейке A2 (наименование из таблицы Заказчика). Если значение найдено, то возвращается номер позиции первого символа найденного значения. Если значение не найдено — возвращается значение ошибки #ЗНАЧ!(#VALUE!). Теперь вторая особенность: функция требует расположения данных в массиве в порядке возрастания. Если данные расположены иначе — функция будет просматривать массив до тех пор, пока не найдет значение больше искомого, но максимально к нему приближенное(хотя если данные позволяют — для более точного поиска все же лучше отсортировать список по возрастанию). Поэтому сначала мы 1 делим на выражение ПОИСК(Каталог!$A$2:$A$11;A2) , чтобы получить массив вида: <0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!>
Ну а в качестве искомого значения мы подсовываем функции число 2 — заведомо большее число, чем может вообще встретиться в массиве(т.к. единица, поделенная на любое число будет меньше двух). И как результат мы получим позицию в массиве, в которой встречается последнее совпадение из каталога. После чего функция ПРОСМОТР запомнит эту позицию и вернет значение из массива Каталог!$A$2:$A$11 (третий аргумент), записанное в этом массиве для этой позиции.
Вы можете просмотреть этапы вычисления функции самостоятельно для каждой ячейки, я здесь просто приведу этапы чуть в расширенном для понимания виде:
Теперь немного облагородим функцию и сделаем еще пару реализаций
Реализация 1:
Вместо артикулов и #Н/Д выведем для найденных позиций «Есть» , а для отсутствующих «Не найден в каталоге» :
=ЕСЛИ(ЕНД(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2)));»Не найден в каталоге»;»Есть»)
=IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),»Не найден в каталоге»,»Есть»)
работа функции проста — с ПРОСМОТР(LOOKUP) разобрались, поэтому остались только ЕНД и ЕСЛИ.
ЕНД (ISNA) возвращает ИСТИНА (TRUE) если выражение внутри неё возвращает значение ошибки #Н/Д (#N/A) и ЛОЖЬ (FALSE) если выражение внутри не возвращает значение этой ошибки.
ЕСЛИ (IF) возвращает то, что указано вторым аргументом если выражение в первом равно ИСТИНА (TRUE) и то, что указано третьим аргументом, если выражение первого аргумента ЛОЖЬ (FALSE) .
Реализация 2:
Вместо #Н/Д выведем «Не найден в каталоге» , но при этом если артикулы найдены — выведем названия этих артикулов:
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);»Нет в каталоге»)
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),»Нет в каталоге»)
Про функция ЕСЛИОШИБКА (IFERROR) я подробно рассказывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0.
Если вкратце, то если выражение, заданное первым аргументом функции, возвращает значение любой ошибки, то функция вернет то, что записано вторым аргументом(в нашем случае это текст «Не найден в каталоге» ). Если же выражение не возвращает ошибку, то функция ЕСЛИОШИБКА запишет то значение, которое было получено выражением в первом аргументе(в нашем случае это будет наименование артикула).
Реализация 3
Надо не просто определить какому артикулу соответствует, но и вывести цену для наименования по этому артикулу(сами цены должны быть расположены в столбце B листа Каталог):
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);»»)
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),»»)
Пара важных замечаний:
- данные на листе с артикулами не должны содержать пустых ячеек . Иначе с большой долей вероятности формула будет возвращать значение именно пустой ячейки, а не то, которое подходит под условия поиска
- формула осуществляет поиск таким образом, что ищется любое совпадение. Например, в качестве артикула записана цифра 1, а в строке наименований может встречаться помимо целой 1 еще и 123, 651123, FG1412NM и т.п. Для всех этих наименований может быть подобран артикул 1, т.к. он содержится в каждом наименовании. Как правило это может произойти, если артикул 1 расположен в конце списка
Поэтому желательно перед использованием формулы отсортировать список по возрастанию(от меньшего к большему, от А до Я).
В приложенном в начале статьи примере вы найдете все разобранные варианты.
Если же вам понадобится выводить все наименования, то можно воспользоваться функцией СОДЕРЖИТ_ОДНО_ИЗ из моей надстройки MulTEx.
Статья помогла? Поделись ссылкой с друзьями!