Функции ПОИСК и НАЙТИ в Excel
Обе возвращают позицию текста внутри строки. Различаются регистром и подстановочными знаками — и на этом ломаются чаще всего.
Коротко
Обе отвечают на один вопрос: с какого знака внутри строки начинается нужный кусок. =ПОИСК("/";B5) в артикуле TK-1043/КР вернёт 8.
Различий ровно два. ПОИСК не различает регистр и понимает подстановочные знаки: ? — любой один знак, * — любое их число. НАЙТИ регистр различает и подстановочные знаки считает обычными символами.
Само число позиции нужно редко. Обычно его отдают дальше: ЛЕВСИМВ и ПСТР режут строку по этой позиции, а ЕЧИСЛО превращает её в ответ «нашлось или нет» — потому что если не нашлось, обе функции возвращают не ноль, а ошибку #ЗНАЧ!.
То же самое формулой в Excel
=ПОИСК("/";B5) — позиция косой черты в артикуле=ЛЕВСИМВ(B5;ПОИСК("/";B5)-1) — всё до косой черты — сам артикул=ПСТР(B5;ПОИСК("/";B5)+1;20) — всё после — код цвета=ЕСЛИ(ЕЧИСЛО(ПОИСК("лампа";C5));"да";"нет") — содержит ли строка слово; регистр не важен=ЕСЛИ(ЕЧИСЛО(НАЙТИ("Лампа";C5));"да";"нет") — то же с учётом регистра: «лампа» уже не совпадёт=ПОИСК("?о";C5) — подстановочные знаки понимает только ПОИСК=ЕСЛИОШИБКА(ПОИСК("шкаф";C5);0) — нет вхождения — #ЗНАЧ!, поэтому обёртка обязательна=ДЛСТР(C5)-ДЛСТР(ПОДСТАВИТЬ(C5;" ";""))+1 — сколько слов в строкеФайл с разбором строки
Шесть артикулов вида TK-1043/КР и наименования с городом. Восемь примеров: позиция знака, разрез строки на части, проверка вхождения, разница по регистру и подсчёт слов.
Файл
poisk-nayti.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Решите, что ищете: знак-разделитель (косая черта, дефис, пробел) или слово.
- Наберите
=ПОИСК("что";ячейка)— искомое в кавычках, ячейка кликом. - Посмотрите на ответ: это номер знака, с которого начинается совпадение.
#ЗНАЧ!означает «не нашлось». - Нужно вырезать часть строки — отдайте позицию в ЛЕВСИМВ, ПРАВСИМВ или ПСТР:
=ЛЕВСИМВ(B5;ПОИСК("/";B5)-1). Минус один — чтобы сам разделитель не попал в результат. - Нужен ответ «да или нет» — оберните в
ЕСЛИ(ЕЧИСЛО(…);"да";"нет"). - Регистр важен — замените ПОИСК на НАЙТИ, всё остальное останется прежним.
Где обычно ломается
#ЗНАЧ!вместо нуля. Обе функции при отсутствии совпадения возвращают ошибку, а не ноль. Поэтому в реальных формулах они почти всегда стоят внутри ЕЧИСЛО или ЕСЛИОШИБКА.- ПОИСК нашёл там, где не должен. Он не различает регистр и понимает
*и?как подстановочные. Если ищете буквальную звёздочку, ставьте перед ней тильду:ПОИСК("~*";B5)— или берите НАЙТИ. - Ищете второе вхождение. Третий аргумент — с какой позиции начинать:
=ПОИСК("/";B5;ПОИСК("/";B5)+1)найдёт вторую косую черту. - Путают с ВПР. ПОИСК ищет кусок внутри одной строки, а не значение в таблице. Найти строку в справочнике — это ВПР или ИНДЕКС с ПОИСКПОЗ.
- Ctrl + F — это не ПОИСК. Окно поиска по листу ничего не считает и в формулы не попадает; функция нужна там, где результат должен лечь в ячейку и пересчитываться.