Подсчёт количества ячеек, содержащих конкретный текст в Excel

Допустим, у вас есть список текстовых строк, и вы хотите подсчитать количество ячеек, содержащих определённый текст как часть своего содержимого. В таком случае можно использовать подстановочные знаки (*) в условии функции COUNTIF — они обозначают любые символы или текст. В этой статье я покажу, как с помощью формул решить эту задачу в Excel.

• Подсчёт количества ячеек, содержащих конкретный текст без учёта регистра С учетом регистра
• Подсчёт количества ячеек, содержащих конкретный текст с учётом регистра С учетом регистра

Подсчёт количества ячеек, содержащих конкретный текст без учёта регистра С учетом регистра

Чтобы подсчитать количество ячеек, частично совпадающих с заданной текстовой строкой, воспользуйтесь функцией СЧЁТЕСЛИ в Excel. Выполните следующие действия:

Универсальная формула с жёстко заданным текстом:

• range: диапазон ячеек, содержащих текстовые строки, которые необходимо подсчитать;
• *: подстановочный знак, соответствующий любому количеству символов;
• text: конкретный текст или символ, который нужно найти.

Как показано на скриншоте ниже, я хочу подсчитать количество ячеек, содержащих текст «Tokyo», в диапазоне A2:A11.

Введите или скопируйте приведённую ниже формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить результат (см. скриншот):

Универсальная формула со ссылкой на ячейку:

• range: диапазон ячеек, содержащих текстовые строки, которые необходимо подсчитать;
• *: подстановочный знак, соответствующий любому количеству символов;
• cellЯчейка, содержащая конкретный текст или символ, который нужно найти.

Чтобы сделать формулу динамической, замените жёстко заданную строку ссылкой на ячейку. Например, если критерии находятся в диапазоне C2:C4 и вы хотите подсчитать ячейки, содержащие фрагменты этих текстов, выполните следующие действия.

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку, чтобы получить результат:

2. Затем перетащите маркер заполнения вниз до ячеек, к которым нужно применить формулу, — и вы получите результаты, как показано на скриншоте ниже:

Советы: если вы хотите подсчитать количество ячеек, содержащих более одного текстового критерия, например, ячейки, содержащие одновременно «Tokyo» и «Class1» в диапазоне A2:A11, как показано на скриншоте ниже:

Для подсчёта ячеек с несколькими подстроками используйте одну из следующих формул по своему усмотрению:

Подсчёт количества ячеек, содержащих конкретный текст с учётом регистра С учетом регистра

Чтобы подсчитать количество ячеек, содержащих конкретный текст с учётом регистра — то есть точно соответствующий указанному тексту, — используйте комбинацию функций SUMPRODUCT, ISNUMBER и FIND. Универсальный синтаксис формулы:

• text: текст или ячейка, содержащая текст, который вы ищете;
• range: диапазон ячеек, содержащих текстовые строки, которые нужно подсчитать.

Введите или скопируйте приведённую ниже формулу в пустую ячейку, а затем нажмите клавишу Enter, чтобы получить результат:

Пояснение формулы:

• FIND(C2, A2:A11): функция FIND чувствительна к регистру и ищет указанный текст в каждой ячейке диапазона. Если текст найден, она возвращает позицию первого символа; в противном случае — ошибку #ЗНАЧ!. В результате вы получите массив следующего вида: {7;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;1;6;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!}.
• ISNUMBER(FIND(C2, A2:A11)): функция ISNUMBER преобразует числовые значения в ИСТИНА, а ошибки — в ЛОЖЬ, формируя следующий массив: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
• --(ISNUMBER(FIND(C2, A2:A11))): двойной унарный минус (--) преобразует значения ИСТИНА и ЛОЖЬ в числа 1 и 0 соответственно, например: {1;0;0;0;1;1;0;0;0;0}.
• SUMPRODUCT(--(ISNUMBER(FIND(C2, A2:A11))))=SUMPRODUCT({1,0,0,0,1,1,0,0,0,0}): наконец, функция SUMPRODUCT суммирует все числа в массиве и возвращает итоговый результат — 3.

Используемые связанные функции:

Другие статьи: