Как задать формулу в экселе найти

НАЙТИ, НАЙТИБ (функции НАЙТИ, НАЙТИБ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

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

Описание

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

Важно: 

  • Эти функции могут быть доступны не на всех языках.

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

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

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

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

Синтаксис

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

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

Аргументы функций НАЙТИ и НАЙТИБ описаны ниже.

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

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

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

Замечания

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

  • Если в качестве аргумента “искомый_текст” задана пустая строка (“”), функция НАЙТИ выводит значение, равное первому знаку в строке поиска (знак с номером, соответствующим аргументу “нач_позиция” или 1).

  • Искомый_текст не может содержать подстановочные знаки.

  • Если find_text не отображаются в within_text, find и FINDB возвращают #VALUE! значение ошибки #ЗНАЧ!.

  • Если start_num не больше нуля, то найти и найтиБ возвращает значение #VALUE! значение ошибки #ЗНАЧ!.

  • Если start_num больше, чем длина within_text, то поиск и НАЙТИБ возвращают #VALUE! значение ошибки #ЗНАЧ!.

  • Аргумент “нач_позиция” можно использовать, чтобы пропустить нужное количество знаков. Предположим, например, что для поиска строки “МДС0093.МесячныеПродажи” используется функция НАЙТИ. Чтобы найти номер первого вхождения “М” в описательную часть текстовой строки, задайте значение аргумента “нач_позиция” равным 8, чтобы поиск в той части текста, которая является серийным номером, не производился. Функция НАЙТИ начинает со знака 8, находит искомый_текст в следующем знаке и возвращает число 9. Функция НАЙТИ всегда возвращает номер знака, считая от левого края текста “просматриваемый_текст”, а не от значения аргумента “нач_позиция”.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Владимир Егоров

Формула

Описание

Результат

=НАЙТИ(“В”;A2)

Позиция первой “В” в ячейке A2

1

=НАЙТИ(“в”;A2)

Позиция первой “в” в ячейке A2

6

=НАЙТИ(“и”;A2;3)

Позиция первой “и” в строке А2, начиная с третьего знака

8

Пример 2

Данные

Керамический изолятор №124-ТД45-87

Медная пружина №12-671-6772

Переменный резистор №116010

Формула

Описание (результат)

Результат

=ПСТР(A2;1;НАЙТИ(” №”;A2;1)-1)

Выделяет текст от позиции 1 до знака “№” в строке (“Керамический изолятор”)

Керамический изолятор

=ПСТР(A3;1;НАЙТИ(” №”;A3;1)-1)

Выделяет текст от позиции 1 до знака “№” в ячейке А3 (“Медная пружина”)

Медная пружина

=ПСТР(A4;1;НАЙТИ(” №”;A4;1)-1)

Выделяет текст от позиции 1 до знака “№” в ячейке А4 (“Переменный резистор”)

Переменный резистор

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

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

Пример формулы НАЙТИ, ДЛСТР и ПРАВСИМВ в Excel

Допустим у нас имеется прайс-лист с кодами товаров, как получить часть символов после дефиса с каждого кода, если дефис каждый раз находиться на новой позиции символов?

  • PTR-422
  • CORPO-6755
  • SVCCALL-56532

Функция ЛЕВСИМВ нам сразу не подходит, так как необходимо получить последнюю часть каждого кода. Функция ПРАВСИМВ так же не справиться с данной задачей, ведь в ее аргументах следует указать точное количество символов возвращаемого текста из всех разных кодов разной длины. Если в аргументе будет указано фиксированное числовое значение, то для некоторых кодов сработает, а для большинства будет слишком много или мало количество символов, возвращаемых функцией ПРАВСИМВ.

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

Для реализации данной задачи следует использовать формулу с комбинацией функций ПРАВСИМВ, ДЛСТР и НАЙТИ:

ПРАВСИМВ.

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



Пример использования НАЙТИ и ПСТР в формуле Excel

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

ПСТР.

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

Функция НАЙТИ требует заполнить минимум 2 из 3-х аргументов:

аргументы функции выборки средних чисел в тексте.

  1. Искомый_текст – здесь необходимо указать текст, который следует найти и получить его порядковый номер (позицию) в исходной текстовой строке.
  2. Просматриваемый_текст – тут указываем ссылку на ячейку с исходной строкой, которая содержит искомый символ или текст.
  3. Нач_позиция – это не обязательный аргумент. Здесь можно указать номер позиции символа в строке, с которого следует начинать поиск. Если строка содержит более одного найденного искомого символа, то с помощью данного необязательного аргумента можно указать номер символа с которого будет просматриваться остальная часть строки. Если он не указан в этом аргументе, то по умолчанию он равен = 1, то есть с первого, а значит целая строка.

На пример, в примере функция находит первый дефис в строке «PWR-16-Small». В результате своего вычисления она по умолчанию возвращает число 4. Так как первый дефис в и сходной строке находится на четвертой позиции.

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

Но если мы воспользуемся третьим необязательным аргументом и укажем в нем число 5. То есть просматривать первую не целиком, а начиная после первого дефиса, четвертого символа. Тогда функция будет возвращать нам порядковую позицию второго «-», то есть число – 7.

3 аргумент.

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

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

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

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

НАЙТИ НАЙТИ.

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

Пример из практического опыта офисного сотрудника. Необходимо получить часть кода товаров, которая начинается от второго дефиса. Для этого создаем динамическую формулу:

Пример функции ПРАВСИМВ НАЙТИ ДЛСТР.

Здесь мы использовали автоматический поиск первого дефиса. Номер позиции послужил третьим необязательным опциональным аргументом функции НАЙТИ для автоматического поиска каждого второго дефиса в каждом коде товара. Далее используя функцию ДЛСТР, мы определяем длину исходной строки и вычитаем от нее число позиции второго символа. Другими словами, от длины кода вычитаем количество символов до второго дефиса (включительно с ним, о чем свидетельствует сложение +1). Таким образом мы динамически определяем второй аргумент для функции ПРАВСИМВ чтобы вырезать разной величины фрагмент текста из строк. К том уже все строки с разной длиной, да еще и в разном месте находится второй дефис. Но умная формула справилась полностью в автоматическом режиме.


Функция

НАЙТИ(

)

, английский вариант FIND(),

находит первое вхождение одной текстовой строки в другой строке и возвращают начальную позицию найденной строки.

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


НАЙТИ

(

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

;

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

;[нач_позиция])


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

— текст, который требуется найти.


Просматриваемая_строка

— текст, в которой ищется

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

.


Нач_позиция

— позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент

нач_позиция

опущен, то предполагается значение 1.

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

Функция

НАЙТИ()

учитывает РЕгиСТР

букв, т.е. результат поиска в строке ”

ПЕРВЫЙ первый

” будет разным для формул

=НАЙТИ(“ПЕРВЫЙ”;”ПЕРВЫЙ первый”)

и

=НАЙТИ(“первый”;”ПЕРВЫЙ первый”)

. Первая вернет 1, вторая 8.

Примеры

Формула

=НАЙТИ(“к”;”Первый канал”)

вернет 8, т.к. буква

к

находится на 8-й позиции слева. Формула

=НАЙТИ(“К”;”Первый канал”)

вернет значение ошибки #ЗНАЧ!, т.к. с учетом регистра строка

Первый канал

не содержит букву

К

.

Пусть в ячейке

А2

введена строка

Первый канал – лучший

. Формула

=НАЙТИ(СИМВОЛ(32);A2)

вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Чтобы найти позицию второго вхождения буквы “а” в строке “мама мыла раму” используйте формулу

=НАЙТИ(“а”;”мама мыла раму”;НАЙТИ(“а”;”мама мыла раму”)+1).

Чтобы определить есть ли третье вхождение буквы “м” в строке “мама мыла раму” используйте формулу

=ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(“мама мыла раму”;”м”;””;3))=ДЛСТР(“мама мыла раму”);”Нет третьего вхождения”;”Есть третье вхождение”)

.

Функция

НАЙТИ()

vs

ПОИСК()

Функция

НАЙТИ()

учитывает РЕгиСТР

букв и не допускает использование

подстановочных знаков

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

подстановочных знаков

пользуйтесь функцией

ПОИСК()

.

Связь с функциями

ЛЕВСИМВ()

,

ПРАВСИМВ()

и

ПСТР()

Функция

НАЙТИ()

может быть использована совместно с функциями

ЛЕВСИМВ()

,

ПРАВСИМВ()

и

ПСТР()

.

Например, в ячейке

А2

содержится фамилия и имя “Иванов Иван”, то формула

=ЛЕВСИМВ(A2;НАЙТИ(СИМВОЛ(32);A2)-1)

извлечет фамилию, а

=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(СИМВОЛ(32);A2))

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

СЖПРОБЕЛЫ()

.

Функция НАЙТИ, примеры использования

Что делает эта функция?

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

Когда искомое найдено, отображается его позиция в тексте в виде числа. Если текст содержит более одного вхождения, выводится позиция первого.

Третий опциональный параметр – чтобы найти фрагмент c определенной позиции в тексте.

Если искомое не найдено в тексте, функция возвращает ошибку #ЗНАЧ.

Синтаксис

=НАЙТИ(ИскомыйТекст,СтрокаВКоторойИщем,СтартоваяПозиция)

Форматирование

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

Функция НАЙТИ – примеры

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

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

Найти заглавные буквы (кириллицы и латиницы)

=СЧЁТ(НАЙТИ({"A":"B":"C":"D":"E":"F":"G":"H":"I":"J":"K":"L":"M":"N":"O":"P":"Q":"R":"S":"T":"U":"V":"W":"X":"Y":"Z":"А":"Б":"В":"Г":"Д":"Е":"Ё":"Ж":"З":"И":"Й":"К":"Л":"М":"Н":"О":"П":"Р":"С":"Т":"У":"Ф":"Х":"Ц":"Ч":"Ш":"Щ":"Ы":"Э":"Ю":"Я"};A1))>0

Найти заглавные английские буквы

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

Помогут составить такую формулу функция СЧЕТ и функция СИМВОЛ:

{=СЧЁТ(НАЙТИ(СИМВОЛ(СТРОКА(65:90));A1))>0}

Формула также вернет ИСТИНА или ЛОЖЬ.

Важно: формула массива вводится без фигурных скобок сочетанием клавиш Ctrl+Shift+Enter.

Найти заглавные русские буквы

Формула аналогична предыдущей, разница лишь в диапазоне символов – от 192 до 223:

{=СЧЁТ(НАЙТИ(СИМВОЛ(СТРОКА(192:223));A1))>0}

Смотрите также:

Найти определенные символы в ячейках Excel

Найти числа/цифры в тексте Excel

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

Excel. Формулы. Поиск формул

В большинстве случаев Excel отображает в ячейках результаты вычисления формулы, а не саму формулу. Поэтому внешне ячейка с введенным значением 5 и ячейка с формулой =КОРЕНЬ(25) выглядят одинаково. Есть несколько способов найти на листе именно те ячейки, которые содержат формулы.

Способ 1. Выделение ячеек с формулами

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

Выделите нужный диапазон и выберите команду “Найти и выделить” – “Формулы”.

Команда на ленте
Команда на ленте

После выбора этой команды в выделенном перед ее нажатием диапазоне будут выделены все ячейки, в которых есть формулы. Если же до выбора команды Вы не выделите какой-нибудь конкретный диапазон (выделена будет одна ячейка), то данная команда сработает на весь активный лист.

Помимо вызова с ленты, воспользоваться этой командой можно и с помощью горячих клавиш. Комбинация Ctrl+G вызовет окно “Переход” .

Окно "Переход"
Окно “Переход”

После появления окна, нужно нажать кнопку “Выделить…”. Откроется окно “Выделить группу ячеек”. В нем нужно выбрать вариант “формулы” и нажать ОК. В отличие от предыдущего способа, это окно позволяет указать, какие именно формулы надо выделить:

  • те, которые возвращают числа;
  • те, которые возвращают текст;
  • те, которые возвращают булево значение (ИСТИНА/ЛОЖЬ);
  • те, которые возвращают ошибки.
Окно "Выделить группу ячеек"
Окно “Выделить группу ячеек”

Способ 2. Отображение формул в ячейках

Пользователь может переключить режим отображения формул на листе. Вместо установленного по умолчанию отображения значений можно попросить Excel показывать сами формулы, введенные в ячейки. Команда для переключения находится на вкладке “Формулы” и называется “Показать формулы” (активируется также сочетанием горячих клавиш Ctrl+’).

Команда на ленте
Команда на ленте

Это команда переключатель (то есть кнопка может находиться в одном из двух состояний: включено или отключено). Результат ее включения – на рисунке ниже.

Активирована команда "Показать формулы"
Активирована команда “Показать формулы”

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

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel – подписывайтесь на наш канал в Telegram Excel Everyday

Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) – на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту – @KillOfBot

По заказам и предложениям обращайтесь к нам на сайте tDots.ru

С уважением, команда tDots.ru

Добавить комментарий