Содержание:
Вчера на марафоне 30 функций Excel за 30 дней мы распознали типы ошибок с помощью функции ERROR.TYPE (ТИП ОШИБКИ) и убедился, что это может быть очень полезно для исправления ошибок в Excel.
18-й день марафона мы посвятим изучению функции ПОИСК (ПОИСК). Он ищет символ (или символы) в текстовой строке и сообщает, где он был найден. Мы также рассмотрим, как действовать в ситуациях, когда эта функция выдает ошибку.
Итак, рассмотрим подробнее теорию и практические примеры функции ПОИСК (ПОИСК). Если у вас есть какие-то хитрости или примеры работы с этой функцией, поделитесь ими в комментариях.
Функция 18: ПОИСК
Функция ПОИСК (ПОИСК) ищет текстовую строку внутри другой текстовой строки и, если она найдена, сообщает ее положение.
Как я могу использовать функцию ПОИСК?
Функция ПОИСК (ПОИСК) ищет текстовую строку внутри другой текстовой строки. Она может:
- Найдите строку текста внутри другой текстовой строки (без учета регистра).
- Используйте подстановочные знаки при поиске.
- Определить начальную позицию в просматриваемом тексте.
Синтаксис ПОИСКА
Функция ПОИСК (ПОИСК) имеет следующий синтаксис:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- найти_текст (search_text) — это текст, который вы ищете.
- внутри_текст (text_for_search) – текстовая строка, внутри которой осуществляется поиск.
- начальный_номер (start_position) – если не указано, поиск начнется с первого символа.
Ловушки ПОИСК (ПОИСК)
Функция ПОИСК (ПОИСК) вернет позицию первой совпадающей строки без учета регистра. Если вам нужен поиск с учетом регистра, вы можете использовать функцию НАЙТИ (НАЙТИ), с которыми мы встретимся позже в марафоне 30 функций Excel за 30 дней.
Пример 1. Поиск текста в строке
Используйте функцию ПОИСК (ПОИСК), чтобы найти текст в текстовой строке. В этом примере мы будем искать один символ (введенный в ячейку B5) в текстовой строке, найденной в ячейке B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
Если текст найден, функция ПОИСК (ПОИСК) вернет номер позиции первого символа в текстовой строке. Если не найден, результатом будет сообщение об ошибке. #СТОИМОСТЬ! (#ТАК).
Если результатом является ошибка, вы можете использовать функцию IFERROR (ЕСЛИОШИБКА), чтобы вместо выполнения функции ПОИСК (ПОИСК) отобразит соответствующее сообщение. Функция IFERROR (ЕСЛИОШИБКА) появилась в Excel начиная с версии 2007. В более ранних версиях тот же результат можно было получить, используя IF (ЕСЛИ) вместе с ISERROR (ЕОШИБКА).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Пример 2. Использование подстановочных знаков в ПОИСК
Другой способ проверить возвращаемый результат ПОИСК (ПОИСК), при ошибке – используйте функцию ISNUMBER (НОМЕР). Если строка найдена, результат ПОИСК (ПОИСК) будет числом, что означает функцию ISNUMBER (ISNUMBER) вернет TRUE. Если текст не найден, то ПОИСК (ПОИСК) сообщит об ошибке, и ISNUMBER (ISNUMBER) вернет ЛОЖЬ.
В значении аргумента найти_текст (search_text) вы можете использовать подстановочные знаки. Символ * (звездочка) заменяет любое количество символов или не заменяет ни одного, и ? (знак вопроса) заменяет любой одиночный символ.
В нашем примере используется подстановочный знак. *, поэтому фразы CENTRAL, CENTER и CENTER будут встречаться в названиях улиц.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Пример 3: Определение начальной позиции для ПОИСКА (ПОИСК)
Если перед функцией написать два знака минус (двойное отрицание) ISNUMBER (ISNUMBER), он вернет значения 1/0 вместо ИСТИНА/ЛОЖЬ (ИСТИНА/ЛОЖЬ). Далее, функция SUM (СУММ) в ячейке E2 подсчитает общее количество записей, в которых был найден искомый текст.
В следующем примере в столбце B показано:
Название города | Профессия
Наша задача — найти профессии, содержащие текстовую строку, введенную в ячейку E1. Формула в ячейке C2 будет такой:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
Эта формула нашла строки, содержащие слово «банк», но в одной из них это слово встречается не в названии профессии, а в названии города. Нам это не подходит!
За каждым названием города следует символ | (вертикальная черта), поэтому мы, используя функцию ПОИСК (ПОИСК), мы можем найти позицию этого персонажа. Его позиция может быть указана как значение аргумента начальный_номер (start_position) в «основной» функции ПОИСК (ПОИСК). В результате названия городов будут игнорироваться поиском.
Теперь проверенная и исправленная формула будет учитывать только те строки, которые содержат в названии профессии слово «банк»:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))