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

Содержание

Инструмент найти и заменить в Excel и Word

Как найти и удалить повторы и дубликаты в Excel

Как быстро найти файл в папке?

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

Для начала, разберемся как вызвать эту функцию и как ее настроить.

Содержание

  • Найти и заменить в Excel. Настройка
  • Найти и заменить. Нюансы
  • Как найти и заменить в Excel формулой
  • Похожие статьи

Найти и заменить в Excel. Настройка

Для начала выделите диапазон ячеек, с которым нужно поработать (если выделена одна ячейка, Excel проводит поиск во всем листе). Перейдите по вкладке Главная — группа Редактирование, выберите Найти и выделить — нажмите Найти (или нажмите сочетание клавиш Ctrl + F), заменить доступно по горячим клавишам Ctrl + H.

Введите тест для поиска, например Иван (в поле Найти:) — т.е. вы хотите найти слово Иван во всех ячейках

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

Хитрость. Если при этом выделить ячейки в результатах поиска прямо в окне (ctrl+a) все найденные ячейки выделятся и их можно подкрасить цветом или отредактировать, как вам нравиться.

Найти и заменить. Нюансы

Соответственно, чтобы заменить ячейки в поле Замена: нужно ввести, то на что заменяете. Т.е. если вы заменяете точку на запятую, в поле Найти — ставим точку, а в поле Заменить — на запятую

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

  • ? — заменяет любой 1 символ;
  • * — заменяет любые символы.

В итоге, введите *Иван*Иванов в поле Найти, а затем нажмите кнопку Найти все. В результатах поиска выпадут все значения, где встречается Иван_какие-то символы_ Иванов.

Можно использовать эти возможности и для поиска чисел. Если в строке поиска записать 7*, в результате отобразятся все ячейки, содержащие значения, начинающееся с 7. Если ввести 3?5*, то получите все четырехзначные и более записи, которые начинаются с 3, второй символ будет любой, а после 5 может любое количество символов.

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

Если что-то найдено неправильно, проверьте настройки:

  • Флажок Учитывать регистр — значит поиск учитывает регистр больших и маленьких букв, т.е. если написать «иван» с маленькой буквы, то поиск не даст результаты в нашем случае.
  • Флажок Ячейка целиком — т. е. в ячейки нет других символов кроме тех, которые вы ищите.

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

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

Для альтернативного поиска ячеек существует функции Найти (FIND) и Поиск (SEARCH). Обе функции возвращают номер символа, с которого начинается первое слово искомой строки. Эти две функции работают почти одинаково, но функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона

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

=ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

Если вы ищете имя Иван (искомый_текст), в тексте Петров Иван (просматриваемый_текст), нач_позиция можно не заполнять. Excel вернет номер символа в строке просматриваемый_текст, т.е. в нашем примере это будет число 8.

 

Как найти и удалить повторы и дубликаты в Excel

Как быстро найти файл в папке?

Текстовые функции Excel | Blog ManyProg

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

Текстовая функция ДЛСТР

При помощи функции ДЛСТР можно подсчитать количество текстовых символов в ячейке (длину строки). При использовании ее следует учитывать, что в это количество входит не только число букв во всех словах, но и пробелы между словами и даже пробелы до и после этих слов.
Для того, чтобы использовать эту функцию, щелкните мышкой по ячейке, где будет отображаться длина строки. В главном меню Excel откройте вкладку «Формулы» и выберите пункт «Текстовые». В появившемся списке функций выберите функцию ДЛСТР.
В окне «Аргументы функции» укажите адрес ячейки, в которой будет подсчитываться количество символов.
После нажатия кнопки «ОК» в ячейке с результатом работы функции появится количество символов в указанной строке.

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

Текстовые функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Функция ЗАМЕНИТЬ, судя по названию, меняет текст в ячейке на указанный текст. Например, нужно в столбце с паролями одинаковой длины указать только первые и последние знаки, а остальные пометить звездочками. Как это сделать?

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

Функция ЗАМЕНИТЬ используется редко, но в некоторых случаях применение ее вполне оправданно. Например, когда в строку нужно вставить новый текст.

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

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

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

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ

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

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

  1. Установите курсор в первой ячейке столбца, в котором будут находиться коды валют. Во вкладке «Формулы» выберите пункт «Текстовые» и в появившемся списке функций выберите функцию ПРАВСИМВ.
  2. В открывшемся окне «Аргументы функции» укажите адрес первой ячейки столбца, из которого нужно извлечь код валюты, и укажите количество знаков, которые нужно скопировать, начиная с последнего знака строки.
  3. Нажмите «ОК».
  4. Потяните за правый нижний угол первой ячейки столбца с валютами, чтобы скопировать формулу для остальных ячеек.

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

Текстовые функции НАЙТИ и ПОИСК

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

Рассмотрим пример. В строке «Компьютерный стол» нужно найти номер позиции, с которой начинается слово «стол». Для этой цели будем использовать функцию «НАЙТИ».

  1. Установите курсор в ячейке, где будет отображаться результат поиска. Во вкладке «Формулы» зайдите в раздел «Текстовые» и в появившемся списке функций выберите функцию НАЙТИ.
  2. Введите аргументы функции:
    • Искомый_текст – «стол».
    • Просматриваемый_текст – А1 (адрес ячейки с текстовой строкой).
    • Нач_позиция – можно не указывать. В этом случае поиск будет производиться, начиная с первого символа. Номер позиции нужно указывать, когда в тексте строки искомый текст встречается несколько раз.
  3. Нажмите «ОК».
  4. В результате этих действий в ячейке с результатом появится номер позиции, с которой начинается слово «стол».

Если указанный текст не будет найден, в ячейке появится сообщение об ошибке «#ЗНАЧ!».

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

В то же время, функция ПОИСК, в отличие от функции НАЙТИ, позволяет использовать подстановочные знаки. К ним относятся, например, звездочка (*) и знак вопроса(?). Первый знак обозначает любой тест, а второй знак – любой символ. То есть, в данном примере для аргумента «с??л» в первой ячейке функция ПОИСК выдаст значение 14, в то время как функция НАЙТИ укажет, что значение не было найдено.

Текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ

Функции СЖПРОБЕЛЫ и ПЕЧСИМВ предназначены для очистки текста от ненужных символов. Лишние пробелы и непечатаемые знаки зачастую возникают при копировании текста из других программ. Проявляются непечатаемые символы, например, в виде квадратиков или черточек. Для того, чтобы их удалить, установите курсор в ячейку, в которой будет находиться исправленный текст, и выберите функцию ПЕЧСИМВ. В качестве аргумента функции укажите адрес ячейки с непечатаемыми символами.
После нажатия кнопки «ОК» в свободной ячейке появится текст без ненужных символов.

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

Текстовая функция ПОВТОР

Функция ПОВТОР отображает заданный текст или символ указанное количество раз. Эту функцию можно использовать как в составе сложных формул, так и отдельно. Зачастую ее применяют для того, чтобы скрыть заданный текст и заменить его набором символов.

Рассмотрим пример, когда в таблице с данными аккаунтов пользователей нужно заменить пароли, заданные в явном виде, символом звездочка (*).

  1. Установите курсор в первую ячейку колонки, где будут находиться скрытые пароли. На вкладке «Формулы» в разделе «Тестовые» выберите функцию ПОВТОР.
  2. В окне «Аргументы функции» в поле «Текст» укажите символ звездочки (*). В поле «Число повторений» укажите формулу ДЛСТР(B1), которая будет подсчитывать количество знаков в каждом пароле и отображать столько звездочек, сколько символов в пароле.
  3. Нажмите кнопку «ОК» и скопируйте полученную формулу в нижние ячейки столбца.

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

Текстовая функция ПСТР

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

Удобство использования этой функции можно наглядно увидеть на следующем примере. В таблице имеется колонка с названиями телефонов Samsung. Необходимо часть названия, расположенную после слов «Samsung Galaxy» вынести в отдельную колонку.

  1. Установите курсор мыши в колонке, где будут размещаться результаты работы функции, и в списке текстовых функций выберите функцию ПСТР.
  2. В появившемся окне аргументов функции в поле «Текст» укажите адрес ячейки, из которой будет извлекаться текст. В поле «Начальная позиция» укажите позицию, с которой начинается извлекаемый текст (учитывая пробелы между словами). В поле «Количество знаков» укажите количество символов, которые нужно извлечь.
  3. После нажатия кнопки «ОК» нужный фрагмент текста будет помещен в указанную ячейку.

К сожалению, чаще всего количество извлекаемых знаков в ячейках столбца бывает неодинаковым, поэтому функцию ПСТР рекомендуется использовать совместно с функцией НАЙТИ и осуществлять поиск по какому-нибудь критерию, например, по наличию пробела между словами.
В этом примере формула для первой ячейки столбца с результатами будет иметь вид: =ПСТР(A1;НАЙТИ(” “;A1)+1;255). В этой формуле «+1» означает, что позицию искомого текста тоже нужно учитывать, но возвращать ее не следует.

Текстовая функция СЦЕПИТЬ

При помощи функции СЦЕПИТЬ можно соединить несколько текстовых строк в одну строку. Соединяемых строк может быть очень много – до 255. В качестве аргументов функции можно использовать текст, цифры или адреса ячеек.

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

  1. Установите курсор мыши в ячейке, где будет находиться результат работы функции.
  2. Зайдите в раздел текстовых функций и выберите функцию СЦЕПИТЬ.
  3. В открывшемся окне аргументов функции поочередно укажите адреса соединяемых ячеек. Не забудьте о пробелах между словами. Пробелы указываются в кавычках.
  4. После нажатия кнопки «ОК» в ячейке с результатом работы функции появится текст, состоящий из имени, отчества и фамилии.

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

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

Category: Файлы Excel |
Tags: Таблицы Excel, Текстовые функции, Функции Excel, Функции НАЙТИ и ПОИСК, Функция ПОВТОР, Функция ПСТР, Функция СЦЕПИТЬ

Функция поиска Excel – как использовать

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

Если функции НАЙТИ не удается найти текст, она возвращает ошибку #ЗНАЧ. Обратите внимание, что функция НАЙТИ в Excel будет выполнять поиск с учетом регистра.

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

Содержание

Синтаксис функции ПОИСК Excel

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

Аргументы:

900 13 ‘find_text’ – Текст/подстрока, которую вы хотите найти.
'within_text' – Этот аргумент представляет собой строку, в которой вы хотите выполнить поиск. Вы можете указать ссылку на ячейку или ввести строку в формулу.
'start_num' — это необязательный аргумент, в котором вы указываете символ, с которого должен начинаться ваш поиск. Если вы опустите этот аргумент, функция будет считать этот параметр равным 1, т. е. поиск начнется с 1-го символа 'внутри_текста'  строка.

Что следует помнить

  • Функция НАЙТИ в Excel чувствительна к регистру и не позволяет использовать подстановочные знаки. Чтобы найти совпадения без учета регистра, воспользуйтесь функцией ПОИСК.
  • Функция НАЙТИ будет искать аргумент 'find_text' в 'внутри_текста'  и возвращать позицию первого символа.
  • Вы можете искать подстроку или символ с помощью  'find_text' аргумент. Вы можете использовать ссылки на ячейки или текстовые символы как для  'find_text'  , так и для 'внутри_текста'  Функция НАЙТИ вернет ‘1’, если аргумент  'find_text' представляет собой пустую строку «».
  • Функция НАЙТИ возвращает #ЗНАЧ! ошибка, когда:
    • Функция НАЙТИ не может найти 'find_text' в 'внутри_текста' или
    • Аргумент 'start_num' отрицательный, 0 или превышает длину 'внутри_текста'

Примеры функции НАЙТИ в Excel

Пример 1. Поиск позиции слова в текстовой строке

строка «Даллас, США», функция вернет «1». Здесь 1 представляет позицию начальной точки искомого слова.

Из-за чувствительности функции НАЙТИ к регистру ввод «даллас» в качестве аргумента вернет #ЗНАЧ! ошибка.

Пример 2. Поиск слова в текстовой строке

Аргумент 'start_num' позволяет выбрать начальную позицию для выполнения поиска в текстовой строке. Вы увидите, что в приведенном выше примере функция НАЙТИ возвращает «1», когда мы вводим 1 в качестве 'start_num' . По сути, он ищет текст «Даллас» в «Даллас, США».

Когда мы меняем 'start_num' на ‘2’, возвращается ошибка, потому что затем выполняется поиск «Даллас» в «Аллас, США».

Обратите внимание, что пропуск аргумента  'start_num'  приведет к тому, что функция НАЙТИ примет в качестве начальной позиции «1».

Пример 3. Когда искомый текст встречается в текстовой строке несколько раз

Поскольку функция НАЙТИ обращается к аргументу  'start_num'  , чтобы узнать, хотите ли вы определить начальную позицию, она возвращает ‘ 1″, когда вы вводите 'start_num'  как 1. Это связано с тем, что он находит «Даллас» на позиции «1» в «Даллас, Даллас, США».

Если вы введете 'start_num'  как 2, вы увидите, что возвращается ‘9’. Здесь происходит то, что функция НАЙТИ пытается найти слово «Даллас» в «Аллас, Даллас, США», поскольку вы просите функцию начать поиск со второй позиции. Здесь 9 — стартовая позиция 2-го «Далласа» в «Даллас, Даллас, США».

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

Теперь предположим, что вы хотите узнать положение второго «,» в списке, имеющем формат «Город, Страна, Континент».

Для этого нам нужно вложить две функции НАЙТИ одну в другую. Вторая функция НАЙТИ войдет в первую функцию НАЙТИ в качестве третьего аргумента ( 'start_num' ), например:

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

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

Совет профессионала: Вы можете использовать функции СИМВОЛ и ПОДСТАВИТЬ, чтобы сделать это проще, по следующей формуле:

=НАЙТИ(СИМВОЛ(1), ПОДСТАВИТЬ(A5,",",СИМВОЛ(1),2)

Пример 5. Получение первой части текстовой строки, разделенной знаком «,» (запятая)

Предположим, вам нужен список, состоящий только из названий городов, без названия страны, т. е. символов непосредственно перед «,»).

Для этого мы будем использовать функцию ВЛЕВО и функцию НАЙТИ вместе. Функция НАЙТИ даст нам положение «,», а функция ВЛЕВО позволит нам получить названия городов.

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

Затем мы встраиваем функцию НАЙТИ в функцию ЛЕВЫЙ и используем НАЙТИ(",", A2,1)-1 в качестве второго аргумента, например:

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

Пример 6. Получение второй части текстовой строки, разделенной знаком «,» (запятая)

Возьмем пример 5 и попытаемся получить вторую часть строки.

Для этого мы воспользуемся функцией MID и функцией FIND вместе. Функция НАЙТИ даст нам позицию «,», а функция СРЕДНЯЯ позволит нам выбрать конкретную часть строки, которая нам нужна.

В нашем примере функция НАЙТИ вернет 10 при выполнении для «Амстердам, Нидерланды». Отсюда мы добавим 1, так как мы не хотим включать «,» в наш вывод.

Далее мы используем функцию MID и передаем ей функцию НАЙТИ НАЙТИ(",", A2,1)+1 в качестве второго аргумента, например:

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

Функция НАЙТИ и функция ПОИСК в Excel

Обе функции поиска и поиска имеют аналогичный синтаксис и применение. Однако между этими функциями есть 2 отличия. Давайте углубимся в эти различия:

1. Допуск подстановочных знаков

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

Для совпадения одного символа мы будем использовать вопросительный знак ‘?’, а для совпадения серии символов мы будем использовать звездочку ‘*’.

Давайте рассмотрим это на примере:

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

=ПОИСК(",*ЕВРОПА",A2)

Обратите внимание, как функция ПОИСК Excel возвращает позицию первого символа, если вы введете оба «,» и «название континента» независимо от того, сколько символов существует между текстовой строкой, указанной в  'внутри_текста' аргумент.

Профессиональный совет: Для поиска ‘?’ или ‘*’, просто добавьте тильду (~) перед знаком вопроса или звездочкой.

2. НАЙТИ чувствителен к регистру, тогда как ПОИСК не чувствителен к регистру

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

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

Обработка #ЗНАЧ! ошибки в функции НАЙТИ

Для устранения ошибки #ЗНАЧ! ошибки, мы можем использовать функцию ЕСЛИОШИБКА.

Давайте вернемся к нашему первому примеру, где мы впервые столкнулись с #VALUE! ошибка с функцией НАЙТИ из-за чувствительности функции НАЙТИ к регистру.

Вот синтаксис, который мы будем использовать для исправления:

=ЕСЛИОШИБКА(НАЙТИ("dallas",A3,1), "Не найдено!")

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

Функция ПОИСК Excel | Как правильно использовать ПОИСК

Если вы часто пользуетесь Excel, вы уже знаете о его многочисленных полезных функциях. Многие из этих формул выполняют вычисления или анализируют данные. Однако, если вы хотите облегчить себе жизнь при работе с большими наборами данных, вам следует знать о функции НАЙТИ. Что такое синтаксическая структура? Как вы используете функцию? И в чем разница между FIND и FINDB? Об этом и многом другом вы можете узнать из следующей статьи.

Содержание

  1. Для чего используется формула ПОИСК в Excel?
  2. Функция ПОИСК в Excel – объяснение синтаксиса
  3. Функция ПОИСК в Excel на практике
    1. ПОИСК И ПОИСК: вложенные группы
    2. ПОИСК И НОМЕР: истинные или ложные операторы
    3. ПОИСК И СРЕДН: Извлечение символов ЕСЛИ: Если , то, иначе
    4. НАЙТИ И ЕСЛИ: Если, то, иначе

Зарегистрируйте доменное имя

Создайте свой бренд на отличном домене, включая SSL и личного консультанта!

Частная регистрация

Поддержка 24/7

Электронная почта

Для чего используется формула ПОИСК в Excel?

При работе с большими электронными таблицами с сотнями строк легко запутаться. Как и все другие продукты Office, Excel имеет встроенную функцию поиска .0018 . Но это не всегда то, что вам действительно нужно. Функция поиска автоматически выполняет поиск по всему документу, то есть вы не можете сузить диапазон. Что еще более важно, вы не можете использовать возвращаемые значения в других функциях. Вы не можете переслать результат поиска, потому что функция поиска работает только в интерфейсе.

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

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

Функция ПОИСК в Excel – объяснение синтаксиса

Синтаксис функции ПОИСК не очень сложен. В стандартной версии вам нужно указать только два аргумента: Что вы хотите найти? И где вы хотите его найти?

 =НАЙТИ(Найти_текст)

Вы также можете настроить поиск так, чтобы он начинался с определенного символа:

 =НАЙТИ(начальный_номер)

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

  • Within_text : Этот параметр указывает текст, в котором вы хотите выполнить поиск. Обычно вы указываете ячейку, содержащую текст. Вы также можете ввести текст непосредственно в формулу. В этом случае вы также должны заключить его в кавычки.
  • Start_num : Вы используете числовое значение, чтобы указать символ, с которого должен начаться поиск. Этот параметр является необязательным. Если не ввести это значение, поиск начнется с позиции 1.

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

    Помимо НАЙТИ, Excel также предлагает функцию НАЙТИБД . Обе функции достигают одинаковых результатов и имеют идентичный синтаксис. Они отличаются только наборами символов, которые они поддерживают. ПОИСК работает только с однобайтовыми наборами символов (SBCS). В том числе латинский алфавит. Если вы используете азиатские символы из китайского, японского и корейского языков (CJK), используйте FINDB, так как он поддерживает двухбайтовые наборы символов (DBCS) для этих языков. Каждый символ кодируется двумя байтами, поэтому подсчет корректируется соответствующим образом.

    Функция ПОИСК в Excel на практике

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

    НАЙТИ и НАЙТИ: вложенные группы

    Предположим, мы хотим найти второе, третье или n-е вхождение искомого термина , а не первое.

     =НАЙТИ(Текст для поиска;Текст;НАЙТИ(Текст для поиска;Текст)+1)

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

    НАЙТИ И НОМЕР: Верные или ложные утверждения

    Функция НАЙТИ в Excel позволяет формировать истинное или ложное утверждение из указанной позиции : Содержит ли текст искомый термин или нет?

     =ЧИСЛО(НАЙТИ("Плюшевый мишка";B2))

    Функция ЕСЧИСЛО возвращает значение ИСТИНА, если результатом НАЙТИ является число, в противном случае возвращается ЛОЖЬ. Поскольку функция НАЙТИ в Excel указывает позицию термина как целое число , функция ЕСНИМПЕР может ответить на это. Если текст не содержит поискового термина, НАЙТИ возвращает сообщение об ошибке, которое, конечно, не является числом, и ISNUMBER отвечает, соответственно, ЛОЖЬЮ.

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

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

    НАЙТИ И СРЕДНЯЯ: Извлечение символов

    Коды продуктов могут быть очень длинными и запутанными, поэтому вам может потребоваться извлечь из строки определенное количество символов. Excel предоставляет для этой цели три функции: LEFT, RIGHT и MID . Они очень полезны сами по себе, но формулы еще более эффективны в сочетании с НАЙТИ. Предположим, ваши коды продуктов всегда следуют определенной схеме, состоящей из букв, цифр и дефисов: ABCDE-A-12345-T. Вы хотите извлечь числовую часть в середине.

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

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

    НАЙТИ И ЕСЛИ: Если, то, иначе

    Коды продуктов могут быть очень длинными и запутанными, поэтому вам может потребоваться извлечь из строки определенное количество символов. Excel предоставляет для этой цели три функции: LEFT, RIGHT и MID . Они очень полезны сами по себе, но формулы еще более эффективны в сочетании с НАЙТИ. Предположим, ваши коды продуктов всегда следуют определенной схеме, состоящей из букв, цифр и дефисов: ABCDE-A-12345-T. Вы хотите извлечь числовую часть в середине.

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

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

    =PART(A2;НАЙТИ("-"; A2;НАЙТИ("-"; A2;НАЙТИ("-";A2)+1))+1;5)

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

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

     =ЧАСТЬ(A2;НАЙТИ("-";A2;НАЙТИ("-";A2;НАЙТИ("-";A2)+1))+1; НАЙТИ("-";A2;НАЙТИ("- ";A2;НАЙТИ("-";A2)+1)+1)-НАЙТИ("-";A2;НАЙТИ("-";A2;НАЙТИ("-";A2)-1))-3)

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

    Высоко вложенные, но эффективные: именно так вы извлекаете именно тот диапазон, который вам нужен.

    НАЙТИ И ЕСЛИ: Если, то, иначе

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

     =ЕСЛИ(ЧИСЛО(ИСПОЛНЕНИЕ(B2,A:A,0)),1,0)

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

    Вы можете комбинировать НАЙТИ, ЕСЛИ и ЕОШИБКА, чтобы найти части слова.

    Облачное хранилище HiDrive с IONOS!

    Расположенная в Европе компания HiDrive защищает ваши данные в облаке, чтобы вы могли легко получить к ним доступ с любого устройства!

    Высокий уровень безопасности

    Общий доступ

    Доступно везде

    • Интернет-продажи
    • Учебники
    • Excel

    Статьи по теме

    Excel если-то: принцип работы ЕСЛИ

    Читайте также: