ВПР и СЧЁТЕСЛИ по части текста

А вы знали, что критерием поиска в ВПР или СУММЕСЛИ / СЧЁТЕСЛИ и т.д. может быть часть ячейки?

Покажу на примере.

Пример с функцией ВПР

Предположим, в желтую ячейку нужно вывести день рождения сотрудницы по имени Ирина. Критерий поиска — только имя.

Но ведь в таблице-источнике, в которой мы будем искать совпадение — нет столбца Имя. Только ФИО.

Фишка ВПР и СЧЁТЕСЛИ, о которой я узнала через 15 лет работы в Excel

Если сразу хочется разделить столбец ФИО на Фамилию, имя и отчество и уже оттуда тянуть — не торопитесь.

Напишем в желтую ячейку формулу:

=ВПР(«*ирина*»;C2:D11;2;0)

где «*ирина*» — и есть тот самый критерий поиска по части ячейки.

Здесь используются два значка-оператора — кавычки и звездочка.

  • звездочка — заменяет любое количество символов. Т.е.перед и после слова ирина может находиться любое количество любых символов.
  • кавычки «» — обязательно ставятся для текстового аргумента поиска в функциях excel.

Остальные аргументы стандартные для функции ВПР:

C2:D11 — таблица для поиска, здесь ее не закрепляем абсолютными ссылками, т.к. копировать формулу не будем.

— номер столбца в таблице, из которого будут возвращаться данные.

0 — аргумент интервальный просмотр.

Как видите, день рождения сотрудницы по имени Ирина (Лесиченко Ирина Ивановна) подтянулся правильно.

Фишка ВПР и СЧЁТЕСЛИ, о которой я узнала через 15 лет работы в Excel

Пример с функцией СЧЁТЕСЛИ

На примере той же таблицы, найдем количество сотрудниц по имени Елена.

Напишем формулу:

=СЧЁТЕСЛИ(C2:C11;»*елена*»)

Где

C2:C11 — диапазон для поиска

«*елена*» — критерий поиска, образованный по тому же принципу, что и для предыдущего примера. Формула будет искать все ячейки, в которых содержится «елена» в любой части ячейки, и вернет их количество.

Фишка ВПР и СЧЁТЕСЛИ, о которой я узнала через 15 лет работы в Excel

Формула посчитала значение 3 — и действительно, в списке три сотрудницы с таким именем.

Аналогично этот прием работает в функциях СУММЕСЛИ и СУММЕСЛИМН.

Ловушка этого способа

В эту ловушку я тоже попадала.

Будьте внимательны, если задаете в качестве критерия поиска слово, которое предположительно может быть частью другого слова.

Например, я считала количество сотрудников с именем Петр (буква ё не использовалась).

Критерием для функции СЧЁТЕСЛИ был «*петр*». И конечно, результатом расчета функции были не только все Петры, но и Петровы, Петровичи и Петровны, поскольку «Петр» является частью этих слов-отчеств.

В таком случае после слова-критерия (или перед ним) нужно поставить пробел. В данном случае, критерием поиска будет «*Петр *» (с пробелом перед второй звездочкой).