Скачать примеры использования функции НАЙТИ в Excel

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

Практическая работа функции НАЙТИ в Excel

Как работает функция НАЙТИ? Схематически, её синтаксис выглядит следующим образом:

Аргумент нач_позиция является необязательным. Если пользователь не укажет его, тогда автоматически он принимается за 1. Если искомый_текст не будет найден, возвратится значение ошибки #ЗНАЧ!. Она очень похожа на функцию ПОИСК, у них одинаковый синтаксис. Однако их различия в том, что НАЙТИ обращает внимание на регистр букв. На картинке мы видим текст, где используется одно слово в разных регистрах. В ячейке В2 мы ищем слово «найти», а в ячейке В3 – то же слово в верхнем регистре:

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

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

Также эта функция не допускает использование подстановочных знаков (звездочка «*», знак вопроса «?», тильда «~»):

Однако, это не распространяется на ПОИСК, который возвращает нам корректный результат.



Практические примеры работы формул чтобы НАЙТИ значение

Как использовать формулы НАЙТИ с другими функциями ЕСЛИОШИБКА, ЗАМЕНИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР, ДЛСТР, СУММ, ЕЧИСЛО в Excel? Рассмотрим пример, в котором будем комбинировать НАЙТИ и ЗАМЕНИТЬ. Ниже попробуем заменить слово «песня» на «поэма»:

Строка с текстом – это ячейка с предложением, которое содержит заменяемое слово. На месте второго аргумента вкладываем НАЙТИ для определения позиции слова «песня». Это нам потребовалось, поскольку неизвестно точное расположение подстроки «песня» (с какого по счету символа начинается «песня»). Число 5 указывает на количество символов, которые будем заменять (количество букв в слове «песня»). В этом примере у нас количество символов искомого и заменяемого текста совпало, но это не обязательное условие, разница может быть любой.

Скопируем формулу до конца столбца:

В ячейке В21 мы так же заменили значение, а ниже у нас возвратились ошибки, поскольку в искомых текстах не было найдено заданного условием слова. Для корректного отображения результата укажем, что при ошибке пусть отображается то же предложение из колонки А (без изменений). Для этого используем ЕСЛИОШИБКА. Первым её аргументом будет вся предыдущая формула, а вторым – ячейка из колонки А:

В столбце В на месте ошибки получены идентичные предложения из столбца А.

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

Сначала формула НАЙТИ возвратила нам значение «4» - это позиция искомого символа. НАЙТИ – 1 = 3. Затем ЛЕВСИМВ извлек три символа в левую сторону – rit. Копируем формулу до конца столбца:

Теперь определим следующую задачу: нужно извлечь имя домена. Для её реализации используем дополнительно функцию ПСТР. ПСТР позволит рассмотреть подстроку из любого места до конца. Приставка +1 укажет, что извлечение происходит со следующего символа от @. При этом нам нужно указать на месте третьего аргумента число, которое, как мы уверенны, будет точно больше длины текстовой строки. В этом примере используем 50 (вряд ли существует электронная почта, имя которой содержит более 50 знаков):

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

Копируем формулу до конца столбца и получаем результат:

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

Для того чтобы определить присутствие подстроки, можно использовать дополнительно функцию ЕЧИСЛО. Принцип работы следующий: если НАЙТИ возвращает номер позиции символа, тогда ЕЧИСЛО указывает значение «ИСТИНА», а если возвращает ошибку – указывает «ЛОЖЬ». Для примера возьмем список с электронной почтой и проверим, есть ли среди них почта gmail.com:

Скопируем формулу до конца столбца и проверим результат работы:

Как видим, напротив текста, где было искомое значение «gmail.com», мы получили результат ИСТИНА. Формула, приведенная выше, оценивает содержимое ячейки. У некоторых пользователей, возможно, промелькнула мысль использовать логическую ЕСЛИ, но она ищет идентичное совпадение заданного искомого текста, «gmail.com» равно «gmail.com», а нам нужно «gmail.com равно «номер позиции». Тогда ЕЧИСЛО определит наличие или отсутствие числа, и возвратит результат.

Можно трансформировать функцию, приведенную выше, добавив перед ЕЧИСЛО две черточки. Таким образом, она подсчитает количество результатов: для ИСТИНА будет возвращено единицу, а для ЛОЖЬ – ноль:

Можно сделать формулу еще сложнее, использовав дополнительно ЕСЛИ и настроив нужные ответы. В примере в таблице предоставлены год модели и номер стиля. ЕСЛИ оценивает логический оператор и возвратит ответ «Да» в случае возвращения ИСТИНА и «Нет» в случае возвращения ЛОЖЬ:

На этот раз мы добавили третий аргумент – число 5, которое указывает с какой позиции начинается поиск. Поскольку год модели также содержит искомое значение «2», мы уточнили, что поиск должен происходить точно с номера стиля. Копируем формулу до конца столбца и проверим, как сработала формула. Фраза «Да» появилась напротив тех ячеек, номер стиля которых равен 2:

Теперь рассмотрим пример, где нам надо подсчитать сумму найденных значений одновременно. Для этого потребуется добавить СУММ. В ячейке А100 у нас три элемента – буквенный код, числовой код, слово. Нам нужно узнать, есть ли среди первого элемент BS, среди второго число 5 и среди третьего слово “new”. Поскольку искомый текст теперь содержит три значения, а не один, как ранее, он является массивом, который нам нужно поместить в скобки {}:

Сейчас содержимое в скобках – это массив из трёх элементов. То есть, нам нужно найти эти значения в ячейке А100. Формула три раза произведет поиск элементов по кругу по каждом из них. Затем добавляем адрес ячейки для поиска А100. Построенную формулу вкладываем в ЕЧИСЛО. На этом этапе возвращаемым результатом будут «ЛОЖЬ,ИСТИНА,ИСТИНА». А нам нужно преобразовать их в числа. Для этого к ЕЧИСЛО добавляем приставку «*1».ИСТИНА*1 равно 1, ЛОЖЬ*1 равно 0:

Теперь нам осталось лишь сосчитать данные. Для этого вкладываем то, что у нас получилось в СУММ, нажимаем Enter:

Применяем построенную формулу по всему списку:

Скачать примеры использования функции НАЙТИ в Excel

В ячейке А101 совпадение только по одному элементу BS, сумма элементов равна 1.