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