Найти функция эксель: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Функция НАЙТИ, НАЙТИБ (FIND, FINDB) — Справочник

Функция НАЙТИ находит вхождение одной текстовой строки в другую и возвращает начальную позицию искомой строки относительно первого знака второй строки.

Описание функции НАЙТИ

Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую и возвращают начальную позицию искомой строки относительно первого знака второй строки.

Функция НАЙТИ предназначена для языков с однобайтовой кодировкой, а функция НАЙТИБ — для языков с двухбайтовой кодировкой. Заданный на компьютере язык по умолчанию влияет на возвращаемое значение указанным ниже образом.

• Функция НАЙТИ при подсчете всегда рассматривает каждый знак, как однобайтовый, так и двухбайтовый, как один знак, независимо от выбранного по умолчанию языка.
• Функция НАЙТИБ при подсчете рассматривает каждый двухбайтовый знак как два знака, если включена поддержка языка с двухбайтовой кодировкой и такой язык установлен по умолчанию. В противном случае функция НАЙТИБ рассматривает каждый знак как один знак.

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

Синтаксис

=НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция])

Аргументы

искомый_текстпросматриваемый_текстнач_позиция

Обязательный аргумент. Текст, который необходимо найти.

Обязательный аргумент. Текст, в котором нужно найти искомый текст.

Необязательный аргумент. Знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер опущен, он полагается равным 1.

Замечания

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

Пример

Excel функция НАЙТИ (FIND) — Info Tech Steps

В этом учебном материале вы узнаете, как использовать Excel функцию НАЙТИ с синтаксисом и примерами.

Описание

Microsoft Excel функция НАЙТИ возвращает расположение подстроки в строке. При поиске учитывается регистр.
Функция НАЙТИ — это встроенная функция Excel, относящаяся к категории строковых / текстовых функций.
Её можно использовать как функцию рабочего листа (WS) в Excel.
Как функцию рабочего листа, функцию НАЙТИ можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции НАЙТИ в Microsoft Excel:

НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция])

Аргументы или параметры

искомый_текст

Подстрока, которую вы хотите найти.

просматриваемый_текст

Строка для поиска.

нач_позиция

Необязательно. Это позиция в просматриваемый_текст, с которой начнется поиск. Первая позиция — 1. Если нач_позиция не указана, функция НАЙТИ начнет поиск с начала строки.

Возвращаемое значение

Функция НАЙТИ возвращает числовое значение. Первая позиция в строке — 1. Если функция НАЙТИ не находит совпадения, она вернет ошибку #ЗНАЧ!.

Применение

• Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

• Функция рабочего листа (WS)

Пример (как функция рабочего листа)

Рассмотрим несколько примеров функции НАЙТИ чтобы понять, как использовать Excel функцию НАЙТИ как функцию рабочего листа в Microsoft Excel:

Hа основе электронной таблицы Excel, приведенной выше, будут возвращены следующие примеры функции НАЙТИ:

=НАЙТИ(B2;A2)
Результат: 1

=НАЙТИ(«E»;A2)
Результат: 1

=НАЙТИ(«e»;A3)
Результат: 4

=НАЙТИ(«Fi»;A4)
Результат: 14

=НАЙТИ(«B»;A5)
Результат: #ЗНАЧ!

=НАЙТИ(«F»; A2; 8)
Результат: 14

Примечание

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

Часто задаваемые вопросы

Вопрос: В Microsoft Excel у меня есть значение «Супермаркет» в ячейке A1 и 100 в ячейке A2.
Цель: если A1 содержит «Супер», тогда я хочу A3 = A2. В противном случае я хочу A3 = 0. Я не могу использовать функцию НАЙТИ, потому что, если ячейка A1 не содержит «Супер», функция НАЙТИ возвращает #ЗНАЧ! ошибка, которая не позволяет мне суммировать столбец A.

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

На основе электронной таблицы Excel, выше, будут возвращены следующие примеры функции НАЙТИ:

=ЕСЛИ(ЕОШИБКА(НАЙТИ(«Супер»;A1;1))=ИСТИНА;0;A2)
Результат: 100

В этом случае ячейка A1 действительно содержит значение «Супер», поэтому формула возвращает значение, найденное в ячейке A2, равное 100.

На основе приведенной выше электронной таблицы Excel будут возвращены следующие примеры функции НАЙТИ:

=ЕСЛИ(ЕОШИБКА(НАЙТИ(«Супер»;A1;1))=ИСТИНА;0;A2)
Результат: 0

В этом примере ячейка A1 НЕ содержит значения «Супер», поэтому формула возвращает 0.
Давайте просто быстро объясним, как работает эта формула. Если ячейка A1 содержит «Супер», функция НАЙТИ вернет числовую позицию значения «Супер». Таким образом, ошибка не произойдет (т.е.
Функция ЕОШИБКА оценивается как ЛОЖЬ), и формула вернет A2. Если ячейка A1 НЕ содержит «Супер», функция НАЙТИ вернет ошибку #ЗНАЧ!, из-за которой функция ЕОШИБКА оценивает значение ИСТИНА и возвращает 0.

Функции ПОИСК и ПОИСК в Excel с примерами формул

В учебном пособии объясняется синтаксис функций ПОИСК и ПОИСК в Excel и приводятся примеры формул для расширенного нетривиального использования.

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

• Функция ПОИСК Excel
• Функция ПОИСК Excel
• Различия между функциями НАЙТИ и ПОИСК
• Как использовать НАЙТИ и ПОИСК в Excel — примеры формул
  • Поиск строки, предшествующей заданному символу или следующей за ним
  • Найти N-е вхождение данного символа в ячейку
  • Извлечь N символов после определенного символа
  • Поиск и извлечение текста между скобками

Функция ПОИСК Excel

Функция НАЙТИ в Excel используется для возврата позиции определенного символа или подстроки в текстовой строке.

Синтаксис функции поиска Excel следующий:

НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

Первые 2 аргумента являются обязательными, последний необязателен.

• Find_text — символ или подстрока, которую вы хотите найти.
• Within_text — текстовая строка для поиска. Обычно он предоставляется как ссылка на ячейку, но вы также можете ввести строку непосредственно в формулу.
• Start_num — необязательный аргумент, указывающий, с какого символа начинать поиск. Если опущено, поиск начинается с 1 ^st символа строки внутри_текста.

Если функция НАЙТИ не находит символ(ы) find_text, возникает ошибка #ЗНАЧ! возвращается ошибка.

Например, формула =НАЙТИ("d", "найти") возвращает 4, потому что «d» является 4-й ^-й буквой в слове « найти «. Формула =НАЙТИ("а", "найти") возвращает ошибку, потому что в « найти » нет «а».

Функция ПОИСК в Excel — о чем нужно помнить!

Чтобы правильно использовать формулу НАЙТИ в Excel, имейте в виду следующие простые факты:

1. Функция НАЙТИ чувствительна к регистру . Если вы ищете совпадение без учета регистра, используйте функцию ПОИСК.
2. Функция НАЙТИ в Excel не позволяет использовать подстановочных знаков .
3. Если аргумент find_text содержит несколько символов, функция НАЙТИ возвращает позицию 9 символов.0040 первый символ . Например, формула НАЙТИ(«ап»,»счастливый») возвращает 2, потому что «а» в 2 ^и букве в слове «счастливый».
4. Если внутри_текста содержится несколько вхождений искомого текста, возвращается первое вхождение. Например, FIND(«l», «hello») возвращает 3, что является позицией первого символа «l» в слове «hello».
5. Если find_text представляет собой пустую строку «», формула Excel НАЙТИ возвращает первый символ в строке поиска.
6. Функция НАЙТИ в Excel возвращает значение #ЗНАЧ! ошибка , если происходит одно из следующих событий:
   • Find_text не существует в пределах_текста.
   • Start_num содержит больше символов, чем внутри_текста.
   • Start_num равен 0 (ноль) или отрицательному числу.

Функция ПОИСК в Excel

Функция ПОИСК в Excel очень похожа на НАЙТИ тем, что она также возвращает местоположение подстроки в текстовой строке. Синтаксис и аргументы аналогичны FIND: 9.0005

ПОИСК(найти_текст, внутри_текста, [начальный_номер])

В отличие от ПОИСК, функция ПОИСК нечувствительна к регистру и позволяет использовать подстановочные знаки, как показано в следующем примере.

А вот пара основных формул ПОИСКА в Excel:

=ПОИСК("рынок", "супермаркет") возвращает 6, потому что подстрока «рынок» начинается с 6-го ^-го символа слова «супермаркет».

=ПОИСК("e", "Excel") возвращает 1, так как «e» является первым символом в слове «Excel» без учета регистра.

Как и НАЙТИ, функция ПОИСК Excel возвращает #ЗНАЧ! ошибка, если:

• Значение аргумента find_text не найдено.
• Аргумент start_num больше, чем длина внутри_текста.
• Start_num равен или меньше нуля.

Далее в этом руководстве вы найдете еще несколько содержательных примеров формул, которые демонстрируют, как использовать функцию ПОИСК на листах Excel.

ПОИСК в Excel и ПОИСК в Excel

Как уже упоминалось, функции ПОИСК и ПОИСК в Excel очень похожи с точки зрения синтаксиса и использования. Однако у них есть пара отличий.

1. ПОИСК с учетом регистра и ПОИСК без учета регистра

Наиболее существенное различие между функциями ПОИСК и НАЙТИ в Excel заключается в том, что ПОИСК не учитывает регистр, а ПОИСК — с учетом регистра.

Например, ПОИСК(«e», «Excel») возвращает 1, поскольку игнорирует регистр «E», а ПОИСК(«e», «Excel») возвращает 4, поскольку учитывает регистр.

2. Поиск с подстановочными знаками

В отличие от НАЙТИ, функция ПОИСК в Excel принимает подстановочные знаки в аргументе find_text:

• Знак вопроса (?) соответствует одному символу, а
• Звездочка (*) соответствует любой последовательности символов.

Чтобы увидеть, как это работает на реальных данных, рассмотрим следующий пример:

Как видно на снимке экрана выше, формула ПОИСК(«функция*2013», A2) возвращает позицию первого символа («f» ) в подстроке, если текстовая строка, указанная в аргументе inside_text, содержит как «функция», так и «2013», независимо от того, сколько других символов находится между ними.

Совет. Чтобы найти вопросительный знак (?) или звездочку (*), введите тильду (~) перед соответствующим символом.

Примеры формул ПОИСК и ПОИСК в Excel

На практике функции ПОИСК и ПОИСК в Excel редко используются сами по себе. Как правило, вы должны использовать их в сочетании с другими функциями, такими как MID, LEFT или RIGHT, и следующие примеры формул демонстрируют некоторые примеры их использования в реальной жизни.

Пример 1. Найти строку, предшествующую или следующую за заданным символом

В этом примере показано, как можно найти и извлечь все символы в текстовой строке слева или справа от определенного символа. Чтобы упростить понимание, рассмотрим следующий пример.

Предположим, у вас есть столбец имен (столбец A), и вы хотите вывести Имя и Фамилию в отдельные столбцы.

Чтобы получить имя, вы можете использовать НАЙТИ (или ПОИСК) в сочетании с функцией ЛЕВЫЙ:

=ЛЕВЫЙ(A2, НАЙТИ(" ", A2)-1)

или

=ЛЕВЫЙ(A2,ПОИСК(" ",A2)-1)

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

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

=ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ(" ",A2))

или

=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(" ",A2))

следующий снимок экрана демонстрирует результат:

Более сложные сценарии, такие как извлечение отчества или разделение имен с суффиксами, см. в разделе Как разделить ячейки в Excel с помощью формул.

Пример 2. Найти N-е вхождение заданного символа в текстовую строку

Предположим, что у вас есть несколько текстовых строк в столбце A, скажем, список артикулов, и вы хотите найти положение 2 ^nd тире в строке. Следующая формула отлично работает:

=НАЙТИ("-", A2, НАЙТИ("-",A2)+1)

Первые два аргумента легко интерпретировать: найти тире («-«) в ячейке А2. В третий аргумент (start_num) вы встраиваете еще одну функцию НАЙТИ, которая сообщает Excel, что поиск нужно начинать с символа, следующего за первым вхождением тире (НАЙТИ(«-«,A2)+1).

Чтобы вернуть позицию 3 ^rd вхождения , вы вставляете приведенную выше формулу в аргумент start_num другой функции НАЙТИ и добавляете 2 к возвращаемому значению:

=НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-",A2 )+1) +2)

Другой и, вероятно, более простой способ найти N-е вхождение заданного символа — использовать функцию Excel НАЙТИ в сочетании с СИМВОЛ и ПОДСТАВИТЬ:

=НАЙТИ(СИМВОЛ(1), ПОДСТАВИТЬ(A2,"-",CHAR(1),3))

Где «-» — рассматриваемый символ, а «3» — N-е вхождение, которое вы хотите найти.

В приведенной выше формуле функция ПОДСТАВИТЬ заменяет третье вхождение дефиса («-«) на СИМВОЛ(1), который является непечатаемым символом «Начало заголовка» в системе ASCII. Вместо CHAR(1) вы можете использовать любой другой непечатаемый символ от 1 до 31. Затем функция НАЙТИ возвращает позицию этого символа в текстовой строке. Итак, общая формула выглядит следующим образом:

НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ( ячейка , символ ,СИМВОЛ(1), N-е вхождение ))

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

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

Пример 3. Извлечь N символов после определенного символа

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

В нашем списке SKU, предположим, вы хотите найти первые 3 символа после первого тире и вытащить их в другой столбец.

Если группа символов, предшествующая первому тире, всегда содержит одинаковое количество элементов (например, 2 символа), это будет тривиальной задачей. Вы можете использовать функцию MID для возврата 3 символов из строки, начиная с позиции 4 (пропуская первые 2 символа и тире):

=MID(A2, 4, 3)

В переводе на английский язык формула гласит: «Посмотрите в ячейку A2, начните извлечение с символа 4 и верните 3 символа».

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

Формула НАЙТИ для возврата позиции тире 1 ^st выглядит следующим образом:

=НАЙТИ("-",A2)

Поскольку вы хотите начать с символа, следующего за тире, добавьте 1 к возвращаемому значению и вставьте указанную выше функцию во второй аргумент (start_num) функции MID:

=MID(A2, FIND("-",A2)+1, 3)

В этом сценарии Функция ПОИСК в Excel работает одинаково хорошо:

=СРЕДН(A2, ПОИСК("-",A2)+1, 3)

Это здорово, но что, если группа символов после первого дефиса содержит другое количество символов? Хм… это может быть проблемой:

Как вы видите на скриншоте выше, формула отлично работает для строк 1 и 2. В строках 4 и 5 вторая группа содержит 4 символа, но только первые 3 символа возвращаются. В строках 6 и 7 во второй группе всего 2 символа, и поэтому наша формула поиска в Excel возвращает после них тире.

Если вы хотите вернуть все символы между 1 ^st и 2 ^nd появления определенного символа (тире в этом примере), как бы вы поступили? Вот ответ:

=СРЕД(A2, НАЙТИ("-",A2)+1, НАЙТИ("-", A2, НАЙТИ("-",A2)+1) - НАЙТИ("-", A2)-1)

Для лучшего понимания этой формулы MID рассмотрим ее аргументы один за другим:

• 1 ^st аргумент (текст). Это текстовая строка, содержащая символы, которые вы хотите извлечь, ячейка A2 в этом примере.
• 2 ^nd аргумент (start_position). Определяет позицию первого символа, который вы хотите извлечь. Вы используете функцию НАЙТИ, чтобы найти первый дефис в строке и добавить 1 к этому значению, потому что вы хотите начать с символа, следующего за дефисом: НАЙТИ(«-«,A2)+1.
• 3 ^rd аргумент (num_chars). Указывает количество символов, которое вы хотите вернуть. В нашей формуле это самая сложная часть. Вы используете две функции НАЙТИ (или ПОИСК), одна из которых определяет позицию первого тире: НАЙТИ («-«, A2). А другой возвращает позицию второго тире: НАЙТИ(«-«, A2, НАЙТИ(«-«,A2)+1). Затем вы вычитаете первое из второго, а затем вычитаете 1, потому что вы не хотите включать ни одно тире. В результате вы получите количество символов между 1 ^st и 2 ^nd тире, это именно то, что мы ищем. Итак, вы передаете это значение аргументу num_chars функции MID.

Аналогичным образом можно вернуть 3 символа после дефиса 2 ^nd :

=MID(A2, НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-", A2)+1) +2), 3)

Или извлеките все символы между тире 2 ^nd и 3 ^rd :

=MID(A2, FIND("-", A2, FIND ("-",A2)+1)+1, НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-",A2)+1) +2) - НАЙТИ("-", A2, НАЙТИ("-",A2)+1)-1)

Пример 4. Поиск текста в скобках

Предположим, у вас есть длинная текстовая строка в столбце A, и вы хотите найти и извлечь только текст, заключенный в скобки.

Для этого вам понадобится функция MID, чтобы вернуть желаемое количество символов из строки, и функция Excel FIND или SEARCH, чтобы определить, с чего начать и сколько символов нужно извлечь.

=СРЕДН(A2,ПОИСК("(",A2)+1, ПОИСК(")",A2)-ПОИСК("(",A2)-1)

Логика этой формулы аналогична тем, которые мы обсуждали в предыдущем примере. И снова самая сложная часть — это последний аргумент, который сообщает формуле, сколько символов нужно вернуть. Это довольно длинное выражение в аргументе num_chars делает следующее:

• Сначала вы находите положение закрывающей скобки: SEARCH(")",A2)
• После этого вы определяете позицию открывающей скобки: ПОИСК("(",A2)
• Затем вы вычисляете разницу между положениями закрывающей и открывающей круглых скобок и вычитаете 1 из этого числа, потому что вы не хотите, чтобы в результате были скобки: ПОИСК(")",A2)-ПОИСК("( ",А2))-1

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

Надеемся, что это руководство пролило свет на то, как использовать функции ПОИСК и НАЙТИ в Excel. В следующем уроке мы подробно рассмотрим функцию REPLACE, поэтому следите за обновлениями. Спасибо за чтение!

Загрузить учебное пособие

Примеры формул ПОИСК и ПОИСК

Вас также может заинтересовать

Как использовать функцию ПОИСК в Excel

Если вы хотите найти символ или группу символов в Excel, функция ПОИСК в Excel может быть первым вариантом, к которому вы инстинктивно тянетесь, и не без оснований.

НАЙТИ ищет одну текстовую строку в другой текстовой строке и возвращает номер позиции первого символа в искомой строке.

Ниже приведены несколько указаний, о которых следует помнить, и несколько советов по работе с функцией ПОИСК в Excel.

Синтаксис

Синтаксис функции НАЙТИ следующий:

  =НАЙТИ(найти_текст,внутри_текста,[начальный_номер]) 
 

• Find_text — это подстрока или символ, который вы хотите найти.
• Within_text — это ссылка на ячейку или текстовая строка, в которой вы будете искать символы.
• Start_num (необязательный аргумент) — это номер позиции символа, с которой должен начинаться ваш поиск. Если этот аргумент опущен, НАЙТИ начинает поиск с первого символа строки.

Примечания

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

Загрузите бесплатный файл практики!

Используйте этот бесплатный файл Excel, чтобы попрактиковаться вместе с учебным пособием.

Введите адрес электронной почты

Базовое приложение

Вот простой пример использования функции НАЙТИ.

В ячейках A2 и A3 подстроки «Word» и «word» встречаются в одной текстовой строке. Поскольку НАЙТИ чувствителен к регистру, аргумент find_text «слово» приводит к тому, что формула пропускает подстроку «Слово» и находит «слово» на 37-й позиции. При изменении find_text на «Word» Excel возвращает значение 11.

Вместо использования явной текстовой строки в качестве find_text , мы можем использовать ссылку на ячейку, чтобы определить, содержит ли ячейка в Excel определенное слово или группу символов.

  =НАЙТИ(B2,A2)  

Формула в ячейке C3:

  =НАЙТИ(B3,A3)  

Обратите внимание, что при использовании ссылок на ячейки мы не используем двойные кавычки, поскольку мы не пытаемся найти буквальное значение «B2» или «B3».

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

Сочетание НАЙТИ с другими функциями

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

Краткое описание этих функций показано ниже.

Замена подстроки, если она присутствует

ПОИСК часто встраивается в функцию ЗАМЕНИТЬ, чтобы найти подстроку внутри другой и затем заменить ее чем-то другим. Синтаксис функции ЗАМЕНИТЬ:

.
  =ЗАМЕНИТЬ(старый_текст, начальный_номер, число_знаков,новый_текст) 
 

• Старый_текст — это полная текстовая строка, содержащая заменяемую подстроку.
• Start_num — номер начальной позиции заменяемого текста.
• Num_chars — длина заменяемого текста.
• New_text — это текст, который заменит удаляемые символы.

Ниже мы можем найти слово «песня» и заменить его словом «стихотворение», если оно присутствует.

 =ЗАМЕНИТЬ(A2,НАЙТИ("песня",A2),4,"стихотворение")  

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

Синтаксис функции ЕСЛИОШИБКА:

  =ЕСЛИОШИБКА(значение, значение_если_ошибка)  

Используя комбинацию формул ЗАМЕНИТЬ/НАЙТИ в качестве аргумента значение функции ЕСЛИОШИБКА, Excel оценивает, приводит ли попытка найти подстроку к ошибке.

  =ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2,НАЙТИ("песня",A2),4,"стихотворение"),A2)  

Этот последний аргумент в приведенной выше функции установлен для возврата текста в ячейке столбца A, если нет совпадения находится с помощью аргумента НАЙТИ.

Извлечь часть текста из большей текстовой строки

Функция НАЙТИ часто сочетается с функциями ВЛЕВО, ВПРАВО и СРЕДНЯЯ, чтобы извлечь часть текста, используя номер его позиции в текстовой строке большего размера.

Извлечь крайние левые символы

Чтобы извлечь имена пользователей из следующих адресов электронной почты, мы можем использовать функцию НАЙТИ в качестве аргумента функции ЛЕВЫЙ.

Функция ВЛЕВО начинает отсчет с крайнего левого символа. Существует необязательный аргумент для извлечения нескольких символов за раз. Если этот аргумент опущен, извлекается только один символ. Синтаксис функции ВЛЕВО:

  =ЛЕВЫЙ(текст, [число_знаков])  

Поскольку функция НАЙТИ возвращает номер позиции символа или текстовой строки, результат функции НАЙТИ может использоваться в качестве аргумента число_знаков функции ЛЕВЫЙ. Обычно мы вычитаем из этого результата 1, чтобы получить «конечную» позицию строки, которую мы хотим извлечь.

  =ЛЕВОЕ(A2,НАЙТИ("@",A2)-1)  

Результат этой формулы НАЙТИ становится аргументом num_chars формулы ЛЕВАЯ.

Извлечь крайние правые символы

Чтобы извлечь доменное имя из адресов электронной почты, мы можем использовать вместе функции ПРАВО и ДЛСТР. Или, если проявить немного творчества, функцию MID. См. оба варианта ниже.

Вариант 1

Функция ДЛСТР подсчитывает количество символов в текстовой строке. Синтаксис:

  =ДЛСТР(текст) 
 

Функция ПРАВИЛЬНО начинает отсчет с крайнего правого символа и возвращает отсчет с крайнего правого символа. Синтаксис следующий:

  =ПРАВО(текст, [число_знаков])  

Следовательно, мы можем использовать предыдущую комбинацию формул ДЛСТР/НАЙТИ в качестве аргумента число_знаков формулы ПРАВИЛЬНО.

  =ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ("@",A2)) 
 

В приведенной выше формуле используется ПОИСК для получения номера позиции символа «@». Это вычитается из длины исходной строки, которая затем используется в качестве аргумента num_chars функции RIGHT.

Вариант 2

Второй вариант еще проще. Функция MID может захватывать символы с середины текстовой строки до конца. Синтаксис:

  =MID(text, start_num, num_chars)  

Требуются все аргументы. MID позволяет нам рассматривать подстроку из любого места текста до конца как среднюю строку, указав число, которое, как мы уверены, больше длины текстовой строки. В этом примере мы будем использовать 100. 

  =MID(A2,FIND("@",A2)+1,100)  

Добавление 1 к номеру позиции символа «@» сообщает нам начальную позицию (или start_num ) функции MID.

Определение наличия подстроки

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

Синтаксис функции ISNUMBER:

  =ЧИСЛО(значение)  

Таким образом, мы можем использовать формулу НАЙТИ в качестве аргумента формулы ЧИСЛО.

  =ЧИСЛО(НАЙТИ("xyz.net",A2))  

Приведенная выше формула определяет, содержит ли каждая текстовая строка подстроку «xyz.net». Если это не так, формула возвращает значение FALSE. Обратите внимание, что в приведенном выше примере формула, относящаяся к ячейкам A4 и A11, вернула ИСТИНА, а формула, относящаяся к ячейке A10, вернула ЛОЖЬ, поскольку НАЙТИ чувствителен к регистру. Когда регистр не имеет значения, лучше воспользоваться функцией ПОИСК.

Для простого подсчета количества экземпляров результата TRUE поставьте два знака минус перед функцией ISNUMBER. Это приведет к тому, что формула вернет 1 или 0 для ИСТИНА или ЛОЖЬ соответственно. Эти результаты можно использовать с функцией СУММ для добавления количества адресов с доменом «xyz.net».

  =--ISNUMBER(НАЙТИ("xyz.net",A11)) 
 

Мы можем сделать еще один шаг вперед, возвращая настраиваемые ответы с помощью функции ЕСЛИ.

Функция ЕСЛИ оценивает логический оператор и возвращает один ответ, если оператор имеет значение ИСТИНА, и другой ответ, если оператор оценивается как ЛОЖЬ. С помощью этого принципа мы можем сказать Excel, что он должен делать, если ISNUMBER возвращает ответ TRUE, и что он должен делать в противном случае.

Синтаксис функции ЕСЛИ:

  =ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])  

Итак, мы можем сделать нашу комбинацию формул ЕЧИСЛО/НАЙТИ первым аргументом формулы ЕСЛИ.

  =ЕСЛИ(ЧИСЛО(НАЙТИ(2,A2,5)),"Да","Нет")  

В приведенном выше примере мы использовали все три аргумента функции НАЙТИ. Это позволило нам найти значение «2», начиная с 5-го значения.

Необязательный аргумент start_num обычно используется, когда искомая подстрока встречается более одного раза, и мы хотели бы игнорировать определенное количество начальных вхождений. В этом случае, когда мы указываем начальный номер, мы заставляем НАЙТИ игнорировать цифры года.

Начиная с 5-го символа, если найдено число «2», функция НАЙТИ возвращает номер позиции, в результате чего функция ЕЧИСЛО возвращает ИСТИНА. Функция ЕСЛИ вложена в это, так что, если ячейка действительно содержит желаемый текст, то «Да» возвращается для ИСТИННЫХ результатов и «Нет» для ЛОЖНЫХ результатов.

Примечание : Поиск числового значения работает с двойными кавычками (или без них).

FIND и FINDB

Если вы хотите узнать больше о функции FINDB, обратите внимание, что единственная разница между FIND и FINDB заключается в том, что FINDB подсчитывает 2 байта на символ, когда установлен язык с двухбайтовым набором символов (DBCS). в качестве языка по умолчанию. Языки, поддерживающие DBCS, включают корейский, японский, китайский (упрощенный) и китайский (традиционный).

В противном случае FINDB ведет себя так же, как FIND, считая 1 байт для каждого символа. По этой причине в этом ресурсе мы обсудили только ПОИСК.

Другие способы поиска в Excel

Функция ПОИСК в Excel помогает искать что-либо в определенной текстовой строке. Но если вы хотите найти элемент в таблице или наборе данных Excel, функции ВПР или ГПР могут больше подойти для этой задачи.

Если у вас есть Excel 365, функция XLOOKUP еще более гибкая. Он может делать все, что могут предыдущие функции, и даже больше.

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

Изучите нашу библиотеку курсов, чтобы узнать о других инструментах Excel. Попробуйте курс Microsoft Excel — базовый и продвинутый курс .