Эксель поиск функция: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Поиск в экселе: способы, функция, примеры

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

Поиск

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

  1. Первый блок используется для записи искомой информации.
  2. Вторая часть функции позволяет задать диапазон поиска по части текста.
  3. Третий аргумент является необязательным. Его использование оправдано, если известна точка начала поиска внутри ячейки.

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

  1. Составляете список на рабочем листе
  1. В соседнем столбце записываете =ПОИСК(«а»;$B$4:$B$11). Не забывайте ставить двойные кавычки при использовании текста в качестве аргумента.
  2. Используя маркер автозаполнения, применяете формулу ко всем остальным ячейкам. Диапазон поиска был зафиксирован значками доллара для более корректной работы.

Полученные результаты можно дальше использовать для приведения к более удобному виду.

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

  1. Нулевая ячейка
  2. Блок не содержит искомой информации.

В нашем случае фрукт Персик не содержит ни одной а, поэтому программа выдала ошибку.

Найти

Комбинация клавиш Ctrl+F позволяет быстро вызвать окно функции Найти. Также можно активировать инструмент через отдельную кнопку на Панели инструментов.

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

Данная функция реализована и в виде формулы с одноименным названием, которая также позволяет осуществлять поиск по словам. Рассмотрим работу на примере списка фруктов. Вписываете формулу =НАЙТИ(«а»;$B$4:$B$11), применяете автозаполнение и получаете результат:

Различия

Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:

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

На заметку! Чтобы найти именно эти символы в тексте необходимо использовать тильду (

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

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

Функция ПОИСК в Excel (6 практических примеров)

Функция Excel ПОИСК ( ПОИСКB ) используется для поиска одной текстовой строки во второй текстовой строке и возврата номера начальной позиции первой текстовой строки из первый символ второй текстовой строки. Например, =ПОИСК(«p», «Apple») возвращает 2, так как первое вхождение p находится на 2-й позиции.

В этом разделе:

  1. Синтаксис функции ПОИСК
  2. Вернуть позицию текста
  3. Извлечь текст перед первым пробелом с помощью ПОИСК и ВЛЕВО
  4. Проверить, содержит ли ячейка текст
  5. Заменить текст другим путем поиска
  6. Использование массива с функцией ПОИСК для извлечения имени
  7. Разница между НАЙТИ и ПОИСК

1. Синтаксис функции ПОИСК:

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

где:

  • find_text : текст, который пользователи хотят найти. Это аргумент Required .
  • внутри_текста : текст, в котором пользователи хотят найти значение аргумента find_text . Это также аргумент Required .
  • start_num : номер символа в аргументе inside_text , с которого пользователи хотят начать поиск. [ Дополнительно ]

2. Возврат позиции текста :

Чтобы найти позицию текста в другом тексте, используется формула =ПОИСК(C6, D6) , которая возвращает позицию определенного текста в другом тексте.

3. Извлечь текст перед первым пробелом с помощью ПОИСК и ВЛЕВО:

Чтобы ввести текст перед первым пробелом с помощью ВЛЕВО и ПОИСК, формула = ВЛЕВО(C6, ПОИСК(» «, C6)) , которая возвращает текст перед первым пространством.

4. Проверить, содержит ли ячейка текст :

Чтобы проверить, содержит ли ячейка текст, используется формула =ЧИСЛО(ПОИСК(D6, C6)) , которая возвращает ИСТИНА , если в ячейке находится конкретный текст; в противном случае возвращается FALSE .

5. Заменить текст другим с помощью поиска:

Для замены текста с помощью поиска используется формула =ЗАМЕНИТЬ(C6,ПОИСК(D6,C6),6,»Сумма») .

6. Использование массива с функцией ПОИСК:

Чтобы найти первое имя из списка имен, пользователи могут применить формулу массива как {=LEFT(C8:C14, SEARCH(» «, C8:C14,1)-1)} , который возвращает первое имя.

7. Разница между ПОИСК и ПОИСК:

Различия между функциями ПОИСК и ПОИСК следующие:

Дополнительные показания:

  1. Сообщение MS Office о функции ПОИСК 9 0017
  2. Извлечь имя в Excel ( 3 практических примера)
  3. Извлечь отчества (3 практических примера)
  4. Функция Excel ARRAYTOTEXT
  5. Excel Функция CLEAN с практическими примерами
  6. Excel Функция CONCAT для соединения текста
  7. Excel REPLACE Функция для замены текста
  8. Excel Функция TRIM с практическими примерами
  9. Получить имя из адреса электронной почты

Excel ПОИСК и вы НАЙДЕТЕ • My Online Training Hub

В Excel есть две почти одинаковые функции; ИЩИТЕ и НАЙДИТЕ.

Функции ПОИСК и НАЙТИ позволяют найти расположение текста в текстовой строке.

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

Итак, давайте посмотрим, как мы можем их использовать.

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

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

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

ячейка, содержащая текст или текстовую строку, начиная с позиции x)

Примечание: если [start_num] не указан, он будет начинаться с 1.

Например; если ячейка A1 содержит слова «ушел», а ваша формула гласит:

 =ПОИСК('E',A1)

Результатом будет 2, так как буква «e» является второй буквой в ячейке A1.

Если вы хотите найти текстовую строку, скажем, «отсутствует»

 =ПОИСК('отсутствует',A1)

Формула вернет результат 6, так как слово «отсутствует» начинается в позиции 6 в ячейке А1.

Примеры функции ПОИСК

Объяснение примеров функции ПОИСК:

  1. Вы заметите, что в первом примере функция ПОИСК игнорировала тот факт, что мы искали букву «E» в верхнем регистре, и по-прежнему возвращала 2. Если вам нужна чувствительность к регистру, используйте функцию НАЙТИ.
  2. Во втором примере мы поставили start_num как «4», и результат был 6.
  3. В третьем примере мы хотели, чтобы он возвращал начальную позицию текстовой строки «edle», поэтому Excel проигнорировал первую букву «e» и вернул результат «3».
  4. В четвертом примере мы использовали подстановочный знак вопросительного знака. Подстановочный знак вопросительного знака вернет позицию текста, начинающуюся с «e», затем состоящую из одного символа и заканчивающуюся на «d».
  5. В пятом примере мы использовали подстановочный знак звездочки. Подстановочный знак звездочки позволяет нам искать любую строку, начинающуюся с «h» и заканчивающуюся на «k».
  6. Если текстовая строка не найдена, функция ПОИСК вернет результат #Н/Д.

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

Воп-де-ду Я слышал, вы говорите. Для чего я когда-либо буду использовать это? Хорошо, я слышу тебя.

Некоторые интересные способы использования ПОИСКА и НАЙТИ

Сами по себе ПОИСК и НАЙТИ не очень полезны, поэтому давайте рассмотрим некоторые способы их использования, вложенные в другие формулы, чтобы раскрыть их силу!

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

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

  1. Преобразуйте результат в ответ ИСТИНА/ЛОЖЬ, используя ISNUMBER, а затем используйте его в операторе IF, чтобы вернуть значение для «коричневых лис» и другое значение для всего остального.

    2.  

    Краткое пояснение: Формула в ячейке B20 ищет слово «коричневый», если результатом является число (ISNUMBER), выберите значение в ячейке F20, а если нет (если слово не найденный ПОИСК вернет ошибку #Н/Д), затем выберите значение в ячейке F21.

  2. Используйте с MID для анализа столбцов в качестве альтернативы Text to Columns

    3.  

    В ячейке C27:

     =ПОИСК(" ",A27)

    возвращает позицию первого пробела, которая также является длиной первого слова (включая пробел).

    В ячейке
    D27:

     =ЕСЛИОШИБКА(ПОИСК(" ",$A$27,C27+1),LEN($A$27)+1)

    Находит место второго пробела.

    Если результатом является ошибка, возвращается длина всей текстовой строки + 1. Это просто обработка ошибок, так как последнее слово в текстовой строке возвращает #ЗНАЧ! ошибка без этого.

    В ячейке C28:

     =СРЕДНИЙ($A$27,1;C27)

    возвращает первые семь символов в ячейке A27.

    В ячейке D28:

     =СРЕДНЕЕ($A$27,C27,ЕСЛИОШИБКА(D27-C27,100))

    Опять же, ЕСЛИОШИБКА — это просто обработка ошибок для последнего слова в текстовой строке.

    Теперь вы можете копировать формулы из D27 и D28 в столбцы столько раз, сколько потребуется, и Боб станет вашим дядей!

    Краткое пояснение: В этом примере функция ПОИСК используется для поиска начальной позиции каждого слова в ячейке A27.

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

    Затем он использует эти числа в формулах MID в строке 28, чтобы извлечь отдельные слова из ячейки A27 и поместить их в отдельный столбец. Так же, как вы можете сделать с текстом в столбцы.

    Для этого требуются 4 разные формулы, но как только вы их настроите, вы сможете копировать их по столбцам и быстро анализировать большие объемы текста.

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

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