30 функций Excel за 30 дней: ПОИСК (SEARCH)

Вчера в марафоне 30 функций Excel за 30 дней мы распознавали типы ошибок с помощью функции ERROR.TYPE (ТИП.ОШИБКИ) и убедились, что она может быть очень полезна для исправления ошибок в Excel.

18-й день марафона мы посвятим изучению функции SEARCH (ПОИСК). Она ищет символ (или символы) внутри текстовой строки и сообщает, где он был найден. Мы также разберем, как справиться с теми ситуациями, когда эта функция выдаёт ошибку.

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

Функция 18: SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) ищет текстовую строку внутри другой текстовой строки, и, если находит, то сообщает её позицию.

Как можно использовать функцию SEARCH (ПОИСК)?

Функция SEARCH (ПОИСК) ищет текстовую строку внутри другой текстовой строки. Она может:

• Найти строку текста внутри другой текстовой строки (без учёта регистра).
• Использовать в поиске символы подстановки.
• Определить стартовую позицию в просматриваемом тексте.

Синтаксис SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) имеет вот такой синтаксис:

SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])

• find_text (искомый_текст) – текст, который Вы ищете.
• within_text (текст_для_поиска) – текстовая строка, внутри которой происходит поиск.
• start_num (нач_позиция) – если не указан, то поиск начнётся с первого символа.

Ловушки SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) возвратит позицию первой совпадающей строки, не зависимо от регистра. Если Вам нужен поиск с учётом регистра, Вы можете использовать функцию FIND (НАЙТИ), с которой мы познакомимся далее в рамках марафона 30 функций Excel за 30 дней.

Пример 1: Находим текст в строке

Используйте функцию SEARCH (ПОИСК), чтобы найти какой-либо текст внутри текстовой строки. В этом примере мы будем искать одиночный символ (введённый в ячейке B5) внутри текстовой строки, находящейся в ячейке B2.

=SEARCH(B5,B2)
=ПОИСК(B5;B2)

Если текст найден, функция SEARCH (ПОИСК) возвратит номер позиции его первого символа в текстовой строке. Если не найден, результатом будет сообщение об ошибке #VALUE! (#ЗНАЧ).

В случае, когда результатом является ошибка, Вы можете использовать функцию IFERROR (ЕСЛИОШИБКА), чтобы вместо выполнения функции SEARCH (ПОИСК) вывести соответствующее сообщение. Функция IFERROR (ЕСЛИОШИБКА) появилась в Excel, начиная с версии 2007. В более ранних версиях тот же результат можно было получить, используя IF (ЕСЛИ) вместе с ISERROR (ЕОШИБКА).

=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")

Пример 2: Используем символы подстановки с SEARCH (ПОИСК)

Еще один способ проверить результат, возвращаемый SEARCH (ПОИСК), на наличие ошибки – воспользоваться функцией ISNUMBER (ЕЧИСЛО). Если строка найдена, результатом SEARCH (ПОИСК) будет число, а значит функция ISNUMBER (ЕЧИСЛО) вернет TRUE (ИСТИНА). Если же текст не найден, то SEARCH (ПОИСК) сообщит об ошибке, а ISNUMBER (ЕЧИСЛО) возвратит FALSE (ЛОЖЬ).

В значении аргумента find_text (искомый_текст) можно использовать символы подстановки. Символ * (звёздочка) заменяет любое количество символов или их отсутствие, а ? (вопросительный знак) заменяет любой одиночный символ.

В нашем примере использован символ подстановки *, поэтому в названиях улиц будут найдены фразы CENTRAL, CENTER и CENTRE.

=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))

Пример 3: Определяем стартовую позицию для SEARCH (ПОИСК)

Если мы запишем два знака минус (двойное отрицание) перед функцией ISNUMBER (ЕЧИСЛО), то она возвратит значения 1/0 вместо TRUE/FALSE (ИСТИНА/ЛОЖЬ). Далее, функция SUM (СУММ) в ячейке E2 подсчитает суммарное количество записей, где искомый текст был найден.

В следующем примере в столбце B показаны:

Название города | Профессия

Наша задача найти профессии, содержащие текстовую строку, введённую в ячейке E1. Формула в ячейке C2 будет следующая:

=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))

Данная формула нашла строки, которые содержат слово «bank», но в одной из них это слово встречается не в названии профессии, а в названии города. Это нас не устраивает!

После каждого названия города стоит символ | (вертикальная черта), поэтому мы, используя функцию SEARCH (ПОИСК), можем найти позицию этого символа. Его позиция может быть указана, как значение аргумента start_num (нач_позиция) в «главной» функции SEARCH (ПОИСК). В результате названия городов будут проигнорированы поиском.

Теперь проверенная и исправленная формула будет считать только те строки, которые содержат слово «bank» в названии профессии:

=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))