В этой статье объясняется синтаксис функций Excel ПОИСК и НАЙТИ, а также приводятся примеры формул для более сложного и нестандартного их использования.
Во многих ситуациях вам может потребоваться, чтобы Excel автоматически находил и извлекал данные из других ячеек на основе ваших критериев. Итак, давайте подробнее рассмотрим, что могут предложить нам функции поиска Excel.
Функция НАЙТИ
Функция НАЙТИ в Excel используется для получения номера позиции определенного символа или подстроки в тексте.
Синтаксис ее следующий:
НАЙТИ(искомый_текст; просматриваемый_текст; [начальная_позиция])
Первые два аргумента являются обязательными, последний — необязательным.
Искомый_текст — символ или подстрока, которую вы хотите найти.
Просматриваемый_текст — текст, в котором происходит поиск. Обычно это ссылка на ячейку, но вы также можете ввести текст непосредственно в формулу.
Начальная_позиция — необязательный аргумент, указывающий, с какого символа следует начинать поиск. Если этот параметр опущен, поиск начинается с первого.
Параметр Начальная_позиция можно использовать для пропуска указанного количества символов. Например, если функция ПОИСК будет использоваться для работы с текстовой строкой «ТЛВ1500.Телевизоры», установка для параметра Начальная_позиция значения 8 будет гарантировать, что поиск происходит только в описательной части текстовой строки, а не в части с серийным номером (в данном случае «ТЛВ1500»), позволяя найти первое вхождение буквы «Т». Функция ПОИСК начинает поиск с восьмого символа, находит символ, указанный в аргументе Просматриваемый_текст, и возвращает число 9.
Если функция НАЙТИ не находит ничего, то возвращается ошибка #ЗНАЧ!.
Например, формула НАЙТИ("р"; A2) возвратит 3, потому что «р» — третья по счету в слове «Барабан». А вот формула НАЙТИ("А"; A2) выдаст ошибку, поскольку в слове «Барабан» она не находит букву «А» (с учетом регистра).
Функция НАЙТИ в Excel – что нужно помнить!
Чтобы правильно использовать формулу НАЙТИ в Excel, помните следующее:
- Функция НАЙТИ чувствительна к регистру. Поэтому формула =НАЙТИ("б"; A2) возвратит не первую, а пятую позицию в слове, как вы видите на скриншоте выше. Если вы ищете совпадение без учета регистра, используйте функцию ПОИСК.
- Функция НАЙТИ в Excel не позволяет использовать подстановочные знаки.
- Если аргумент Искомый_текст содержит несколько символов, которые вы ищете, функция НАЙТИ возвращает позицию первого из этих символов. Например, формула =НАЙТИ("раб"; A2) возвращает 3, потому что, когда было найдено совпадение, первая буква «р» из слова «раб» была третьей буквой в слове "Барабан".
- Если в тексте содержится несколько вхождений Искомый_текст, то возвращается первое вхождение. Например, формула =НАЙТИ("а"; A2) возвращает 2, потому что первое найденное "а" является второй буквой в слове (см. скриншот выше).
- Если Искомый_текст — пустая строка «», формула НАЙТИ возвращает первый символ из строки поиска.
- Функция Excel НАЙТИ возвращает ошибку #ЗНАЧ!, если происходит любое из следующих событий:
- Искомый_текст не найден в Просматриваемый_текст.
- Начальная_позиция указывает на число, которое больше, чем количество символов в Просматриваемый_текст.
- Начальная_позиция — 0 (ноль) или отрицательное число.
Функция ПОИСК
Функция ПОИСК в Excel очень похожа на НАЙТИ, поскольку она также возвращает местоположение подстроки в тексте. Синтаксис и аргументы аналогичны синтаксису НАЙТИ:
ПОИСК(искомый_текст, просматриваемый_текст, [начальная_позиция])
В отличие от НАЙТИ, функция ПОИСК не учитывает регистр и позволяет использовать подстановочные знаки, как мы увидим в примерах чуть ниже.
А вот несколько самых простых примеров формулы ПОИСК в Excel, которые вы видите на скриншоте ниже:
=ПОИСК("раб"; A2) возвращает 3, поскольку подстрока «раб» начинается с третьего символа слова «Барабан».
=ПОИСК("б"; A2) возвращает 1, поскольку «б» — первый символ, регистр игнорируется.
=ПОИСК("А"; A2) возвращает 2, так как первая из двух букв «А» в слове находится на второй позиции (регистр игнорируется).
Как и функция НАЙТИ, формула ПОИСК в Excel возвращает ошибку #ЗНАЧ!, если:
- Значение аргумента искомый_текст не найдено.
- Начальная_позиция больше, чем количество символов в просматриваемый_текст.
- Начальная_позиция равна или меньше нуля.
Далее вы найдете еще несколько полезных примеров формул, показывающих, как можно использовать функцию ПОИСК в таблицах Excel.
Сравнение функций НАЙТИ и ПОИСК
Как уже упоминалось, функции НАЙТИ и ПОИСК в Excel очень похожи с точки зрения синтаксиса и использования. Однако у них есть пара отличий.
1. НАЙТИ с учетом регистра или ПОИСК без учета регистра
Наиболее существенное различие между функциями Excel ПОИСК и НАЙТИ заключается в том, что ПОИСК не чувствителен к регистру, а НАЙТИ — учитывает регистр.
Например, =ПОИСК("б"; A2) в примере на скриншоте ниже возвращает 1, поскольку игнорирует регистр буквы "б", а выражение НАЙТИ("б"; A2) вернет 5, поскольку учитывает регистр.
2. Функция ПОИСК с использованием подстановочных знаков.
В отличие от НАЙТИ, функция ПОИСК Excel может использовать подстановочные знаки в аргументе искомый_текст:
- Знак вопроса (?) соответствует одному символу,
- Звездочка (*) соответствует любой серии символов.
Чтобы увидеть, как это работает, рассмотрим следующий пример:
Как вы видите на скриншоте выше, формула ПОИСК("функц*2021"; A3) находит позицию первого символа искомого текста ("ф") в подстроке, если текстовая строка, указанная в аргументе просматриваемый_текст, содержит как "функц", так и «2021», независимо от того, сколько других символов находится между ними.
Совет. Чтобы найти настоящий вопросительный знак (?) или звездочку (*), введите тильду (~) перед соответствующим символом.
Примеры формул НАЙТИ и ПОИСК в Excel
На практике функции Excel НАЙТИ и ПОИСК редко используются сами по себе. Обычно вы используете их в сочетании с другими текстовыми функциями, такими как ПСТР, ЛЕВСИМВ или ПРАВСИМВ. Далее примеры формул показывают такие варианты совместного использования.
Пример 1. Получить текст до или после заданного символа
В этом примере нам необходимо найти и извлечь все символы текстовой строки слева или справа от определенного символа. Чтобы облегчить понимание, рассмотрим следующий пример.
Предположим, у вас есть столбец имен (столбец A), и вы хотите разделить имя и фамилию на отдельные столбцы.
Чтобы получить имя, вы можете использовать НАЙТИ (или ПОИСК) в сочетании с функцией ЛЕВСИМВ:
=ЛЕВСИМВ(A2; НАЙТИ(" "; A2)-1)
или
=ЛЕВСИМВ(A2; ПОИСК(" "; A2)-1)
Как вы, вероятно, знаете, функция Excel ЛЕВСИМВ возвращает указанное количество крайних левых символов в тексте. И вы используете функцию НАЙТИ, чтобы определить положение пробела (" "), чтобы функция ЛЕВСИМВ знала, сколько символов нужно извлечь. При этом вы вычитаете 1 из найденной позиции пробела, потому что не хотите, чтобы возвращаемое значение включало этот пробел.
Для извлечения фамилии используйте комбинацию функции ПРАВСИМВ, НАЙТИ/ПОИСК и ДЛСТР. Функция ДЛСТР нужна для получения общего количества символов в строке, из которого вы вычитаете номер позиции пробела. В итоге получается число символов после пробела:
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2))
Следующий скриншот демонстрирует результат:
Или используем функцию ПСТР:
=ПСТР(A2;НАЙТИ(" ";A2)+1;50)
Здесь мы находим позицию пробела при помощи функции НАЙТИ, и затем даем команду ПСТР – начиная со следующего за пробелом символа извлечь 50 символов. Что практически означает: извлечь всё до конца текста.
Для более сложных сценариев, таких как извлечение еще и отчества или нескольких слов, см. статью «Как разделить текст из одной ячейки в Excel в несколько».
Пример 2. Найти N-е вхождение символа в тексте
Предположим, у вас есть несколько текстовых строк в столбце A, скажем, список кодов товаров или артикулов, и вы хотите найти положение второго тире. Следующая формула без проблем сделает это:
=НАЙТИ("-"; A2; НАЙТИ("-";A2)+1)
Первые два аргумента означают: найдите тире («-») в ячейке A2. В третьем аргументе (начальная позиция) вы используете еще одну функцию НАЙТИ, которая указывает Excel начать поиск, начиная с символа, который идет сразу после первого появления тире (НАЙТИ("-",A2)+1). Таким образом мы будем искать позицию второго тире.
Чтобы вернуть позицию третьего тире, вы встраиваете приведенную выше формулу в аргумент начальная позиция второй функции НАЙТИ и добавляете 2 к полученному значению:
=НАЙТИ("-";A2; НАЙТИ("-"; A2; НАЙТИ("-";A2)+1) +2)
Посмотрите, как это выглядит на примере:
Другой и, вероятно, более простой способ найти N-е вхождение данного символа — использовать функцию Excel НАЙТИ в сочетании с СИМВОЛ и ПОДСТАВИТЬ:
=НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(A2;"-";СИМВОЛ(1);3))
Где «-» — это символ, о котором идет речь, а «3» — это N-е вхождение, которое вы хотите найти.
В приведенной выше формуле функция ПОДСТАВИТЬ заменяет третье появление тире («-») на результат выражения СИМВОЛ(1), который является непечатаемым символом «Начало заголовка» в системе ASCII. Вместо СИМВОЛ(1) вы можете использовать любой другой непечатаемый символ с кодом от 1 до 31. Затем функция НАЙТИ возвращает позицию этого символа в текстовой строке. Итак, формула в общем виде выглядит следующим образом:
НАЙТИ(СИМВОЛ(1) ; ПОДСТАВИТЬ( ячейка ; символ ; СИМВОЛ(1) ; N-е вхождение ))
На первый взгляд может показаться, что приведенные выше формулы не имеют практической ценности, но следующий пример покажет, насколько они полезны при решении реальных задач.
Примечание. Помните, что функция Excel НАЙТИ чувствительна к регистру. В нашем примере это не имеет значения, но, если вы работаете с буквами и хотите найти совпадение без учета регистра, используйте функцию ПОИСК вместо НАЙТИ.
Пример 3. Получить N символов после определенного символа
Чтобы найти подстроку заданной длины в любом тексте, используйте НАЙТИ или ПОИСК в сочетании с функцией ПСТР. Следующий пример демонстрирует, как можно использовать такие формулы.
Предположим, в нашем списке артикулов вы хотите найти первые 3 символа после первого тире и перенести их в другой столбец.
Если группа символов, предшествующая первому тире, всегда содержит одинаковое количество элементов (например, также 3 символа), это будет очень простой задачей. Вы можете использовать функцию ПСТР для возврата 3 символов из строки, начиная с позиции 5 (пропуская первые 3 символа и тире):
=ПСТР(A2;5;3)
Эта формула предписывает: «Посмотрите в ячейку А2, начиная с символа 5 верните 3 символа».
Однако в реальных таблицах подстрока, которую необходимо извлечь, может начинаться в любом месте текста. В нашем примере вы можете не знать, сколько символов предшествует первому тире. Чтобы справиться с этой задачей, используйте функцию НАЙТИ, чтобы определить начальную точку подстроки, которую вы хотите получить.
Формула НАЙТИ для получения номера позиции первого тире выглядит следующим образом:
=НАЙТИ("-";A2)
Поскольку вы хотите начать с символа, следующего за тире, то добавьте 1 к полученной позиции и вставьте указанную выше функцию во второй аргумент (начальная_позиция) функции ПСТР:
=ПСТР(A2;НАЙТИ("-";A2)+1;3)
В этом сценарии формула ПОИСК в Excel также работает хорошо:
=ПСТР(A2;ПОИСК("-";A2)+1;3)
Это здорово, но что, если группа символов, следующая за первым тире, содержит разное количество символов – от 3 до 6? Да... это может быть проблемой, как вы видите на скриншоте выше.
Как вы видите, формула отлично работает для строк 2 и 6. В остальных строках вторая группа содержит от 4 до 6 символов, но возвращаются только первые 3 символа.
Если бы вы хотели вернуть все символы между первым и вторым вхождением определенного символа (в данном примере тире), как бы вы поступили? Вот решение:
=ПСТР(A2; НАЙТИ("-";A2)+1; НАЙТИ("-"; A2; НАЙТИ("-";A2)+1)-НАЙТИ("-";A2)-1)
или
=ПСТР(A2; ПОИСК("-";A2)+1; ПОИСК("-"; A2; ПОИСК("-";A2)+1)-ПОИСК("-";A2)-1)
Аналогичным образом вы можете получить 3 символа после второго тире:
=ПСТР(A2; НАЙТИ("-"; A2; НАЙТИ("-";A2)+2)+1;3)
Или извлечь все символы между вторым и третьим тире:
=ПСТР(A2; НАЙТИ("-"; A2; НАЙТИ("-";A2)+1)+1; НАЙТИ("-";A2; НАЙТИ("-"; A2; НАЙТИ("-";A2)+1) +2) - НАЙТИ("-"; A2; НАЙТИ("-";A2)+1)-1)
Наглядно вы можете увидеть это в примере на скриншоте ниже:
Конечно, вы можете использовать и формулу ПОИСК:
=ПСТР(A2; ПОИСК("-"; A2; ПОИСК("-";A2)+1)+1; ПОИСК("-";A2; ПОИСК("-"; A2; ПОИСК("-";A2)+1) +2) - ПОИСК("-"; A2; ПОИСК("-";A2)+1)-1)
Пример 4. Извлечь текст в скобках
Предположим, у вас есть длинная текстовая строка в столбце A, и вы хотите найти и извлечь только текст, заключенный в (круглые скобки).
Для этого вам понадобится функция ПСТР, чтобы вернуть нужное количество символов из текста, а также функция Excel НАЙТИ или ПОИСК, чтобы определить, с какой позиции начать и сколько символов извлечь.
=ПСТР(A2;ПОИСК("(";A2)+1; ПОИСК(")";A2)-ПОИСК("(";A2)-1)
Логика этой формулы ПОИСК аналогична той, которую мы обсуждали в предыдущем примере. И снова самая сложная часть — это последний аргумент, который сообщает формуле, сколько символов нужно вернуть. Это довольно длинное выражение делает следующее:
- Сначала вы находите положение закрывающей скобки: ПОИСК("(";A2)
- После этого вы определяете положение открывающей скобки: ПОИСК(")";A2)
- А затем вы вычисляете разницу между позициями закрывающей и открывающей скобок и вычитаете 1 из этого числа, потому что вам не нужна ни одна из скобок в извлекаемом тексте: ПОИСК(")";A2)-ПОИСК("(";A2)-1
Результат вы видите ниже:
Естественно, ничто не мешает вам использовать в Excel функцию НАЙТИ вместо ПОИСК, поскольку в этом примере чувствительность к регистру не имеет значения.
Если нам нужно найти и извлечь текст, заключённый между второй парой скобок, то формула будет несколько сложнее.
Найдем позицию второй открывающей скобки “(“, используя формулы из Примера 2 выше.
=ПОИСК("("; A2; ПОИСК("(";A2)+1)
Аналогично позиция второй закрывающей скобки “)” может быть найдена так:
=ПОИСК(")"; A2; ПОИСК(")";A2)+1)
Осталось только использовать это в формуле ПСТР, чтобы получить нужный текст:
=ПСТР(A2; ПОИСК("("; A2; ПОИСК("(";A2)+1)+1;ПОИСК(")"; A2; ПОИСК(")";A2)+1)-ПОИСК("("; A2; ПОИСК("(";A2)+1)-1)
Применение этой формулы поиска текста вы видите на скриншоте ниже:
Пример 5. Найти текст между двумя разными символами
Разобравшись, как можно найти текст между двумя скобками, думаю, несложно будет извлечь текст между любыми другими символами. Рассмотрим небольшой пример, чтобы получить текст между тире и двоеточием.
Находим позиции указанных символов при помощи функции НАЙТИ или ПОИСК, а затем извлекаем нужный фрагмент текста формулой ПСТР.
=ПСТР(A2;ПОИСК("–";A2)+1;ПОИСК(":";A2)-ПОИСК("–";A2)-1)
Другие примеры использования функции ПОИСК
- Найти первую цифру в ячейке:
=МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))
- Найти первую цифру в ячейке и вернуть все, что перед ней:
=ЛЕВСИМВ(A1,МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))-1)
- Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ» (формула массива):
=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0
Поясним, как работает эта формула. Функция СТРОКА(65:90) возвращает массив номеров строк с 65 по 90 включительно.
В таблице ASCII находятся числовые коды латинских букв. Функция СИМВОЛ возвращает для каждого числового кода соответствующую ему букву, таким образом создавая массив латинских букв.
Функция ПОИСК последовательно перебирает этот массив, ищет каждую из этих букв в тексте и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок.
Функция СЧЁТ считает числа в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если результат – ноль, то латинских букв нет.
- Аналогично можно узнать, содержит ли ячейка кириллицу. Формула вернет «ИСТИНА» или «ЛОЖЬ» (формула массива):
=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0
- Определить, содержит ли текст ключевые слова.
Если у вас есть массив текстовых значений, и вы хотите узнать, содержат ли они определенные ключевые слова, формула ПОИСК поможет это сделать. Вы можете использовать ее в сочетании с функцией ЕСЛИ, чтобы создать условие, которое проверяет наличие ключевого слова в тексте.
Формула для определения присутствия ключевого слова в тексте:
=ЕСЛИ(ЕСЛИОШИБКА(ПОИСК(«ключевое_слово»; A1); 0) > 0; «Содержит»; «Не содержит»)
где A1 — ячейка с текстом, а «ключевое_слово» — искомое слово.
Использование функции ПОИСК с другими функциями Excel
Для достижения большей гибкости и эффективности в работе с текстовыми данными, функцию ПОИСК можно комбинировать с другими функциями Excel. Вот несколько примеров:
Использование ПОИСК с ИНДЕКС+ПОИСКПОЗ
Если вам нужно найти определенную информацию на основе значения, содержащего искомую подстроку, вы можете использовать функцию ПОИСК в сочетании с функциями ИНДЕКС ПОИСКПОЗ. Это позволит вам найти подходящую строку в таблице и вернуть соответствующее значение из определенной колонки. Это так называемый приблизительный поиск, когда мы ищем неполное совпадение текстовых значений.
Например, для поиска нужного товара мы можем не вводить его длинное наименование, а указать только его основную часть.
Формула для использования ПОИСК и ИНДЕКС ПОИСКПОЗ:
=ИНДЕКС(B2:B10;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(D1;A2:A10));0))
где:
A2:A10 — диапазон ячеек, в которых осуществляется поиск;
B1:В10 — диапазон, в котором нужно найти соответствующую строку;
D1 – ячейка со значением поиска
Пример поиска по части текста вы видите на скриншоте ниже.
В списке товаров мы нашли наименование, которое содержит слово «бананы», определили его порядковый номер в списке при помощи ПОИСКПОЗ, и из соседнего столбца при помощи ИНДЕКС получили соответствующее количество.
Использование ПОИСК с СУММПРОИЗВ
Предположим, у вас есть таблица с данными о продажах, и вы хотите посчитать общую величину продаж для определенной категории товаров. В этом случае вы можете использовать функцию ПОИСК в сочетании с функцией СУММПРОИЗВ для фильтрации данных и подсчета суммы.
Формула для совместного использования ПОИСК и СУММПРОИЗВ:
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(D1;A2:A10));B2:B10)
где:
A2:A10 — диапазон ячеек с полными наименованиями товаров;
B1:B10 — диапазон ячеек с количеством продаж.
D1 – ячейка с ключевым словом для подсчета.
Как видите, мы находим и суммируем те ячейки в столбце B, для которых соответствующие наименования в столбце А содержат слово «бананы».
Выражение --ЕЧИСЛО(ПОИСК(D1;A2:A10)) возвращает массив нулей и единиц, где единица соответствует наименованию, в котором присутствует искомое слово «бананы». Умножая этот массив на массив количества и суммируя эти произведения, получаем итоговую сумму по бананам.
Надеемся, что это руководство пролило свет на то, как использовать функции ПОИСК и НАЙТИ в Excel. Спасибо за чтение!
