А вы знали, что критерием поиска в ВПР или СУММЕСЛИ / СЧЁТЕСЛИ и т.д. может быть часть ячейки?
Покажу на примере.
Пример с функцией ВПР
Предположим, в желтую ячейку нужно вывести день рождения сотрудницы по имени Ирина. Критерий поиска — только имя.
Но ведь в таблице-источнике, в которой мы будем искать совпадение — нет столбца Имя. Только ФИО.
Если сразу хочется разделить столбец ФИО на Фамилию, имя и отчество и уже оттуда тянуть — не торопитесь.
Напишем в желтую ячейку формулу:
=ВПР(«*ирина*»;C2:D11;2;0)
где «*ирина*» — и есть тот самый критерий поиска по части ячейки.
Здесь используются два значка-оператора — кавычки и звездочка.
- звездочка * — заменяет любое количество символов. Т.е.перед и после слова ирина может находиться любое количество любых символов.
- кавычки «» — обязательно ставятся для текстового аргумента поиска в функциях excel.
Остальные аргументы стандартные для функции ВПР:
C2:D11 — таблица для поиска, здесь ее не закрепляем абсолютными ссылками, т.к. копировать формулу не будем.
2 — номер столбца в таблице, из которого будут возвращаться данные.
0 — аргумент интервальный просмотр.
Как видите, день рождения сотрудницы по имени Ирина (Лесиченко Ирина Ивановна) подтянулся правильно.
Пример с функцией СЧЁТЕСЛИ
На примере той же таблицы, найдем количество сотрудниц по имени Елена.
Напишем формулу:
=СЧЁТЕСЛИ(C2:C11;»*елена*»)
Где
C2:C11 — диапазон для поиска
«*елена*» — критерий поиска, образованный по тому же принципу, что и для предыдущего примера. Формула будет искать все ячейки, в которых содержится «елена» в любой части ячейки, и вернет их количество.
Формула посчитала значение 3 — и действительно, в списке три сотрудницы с таким именем.
Аналогично этот прием работает в функциях СУММЕСЛИ и СУММЕСЛИМН.
Ловушка этого способа
В эту ловушку я тоже попадала.
Будьте внимательны, если задаете в качестве критерия поиска слово, которое предположительно может быть частью другого слова.
Например, я считала количество сотрудников с именем Петр (буква ё не использовалась).
Критерием для функции СЧЁТЕСЛИ был «*петр*». И конечно, результатом расчета функции были не только все Петры, но и Петровы, Петровичи и Петровны, поскольку «Петр» является частью этих слов-отчеств.
В таком случае после слова-критерия (или перед ним) нужно поставить пробел. В данном случае, критерием поиска будет «*Петр *» (с пробелом перед второй звездочкой).
