Общая формула
=COUNTIF(range,"*txt*")
Резюме
Для подсчета ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ с подстановочным знаком. В показанном примере формула E5 выглядит так:
=COUNTIF(B5:B15,"*a*")
Объяснение
Функция СЧЁТЕСЛИ подсчитывает ячейки в диапазоне, который соответствует критериям. Например, чтобы подсчитать количество ячеек в диапазоне, содержащем "a", вы можете использовать:
=COUNTIF(range,"a") // exact match
Однако обратите внимание, что это точное совпадение. Для подсчета ячейка должна содержать ровно «а». Если в ячейке есть другие символы, она не будет засчитана.
Для показанной проблемы мы хотим подсчитать ячейки, содержащие определенный текст, то есть текст может быть где угодно в ячейке. Для этого нам нужно использовать символ звездочки (*) в качестве подстановочного знака. Например, для подсчета ячеек, содержащих букву «а», мы используем:
=COUNTIF(range,"*a*")
Звездочка в формуле означает «соответствие любому количеству символов, включая ноль», поэтому этот шаблон будет считать любую ячейку, содержащую «a» в любой позиции, с другими символами слева или справа или без них. Все формулы, используемые в примере, следуют одному шаблону:
=COUNTIF(B5:B15,"*a*") // contains "a" =COUNTIF(B5:B15,"*2*") // contains "2" =COUNTIF(B5:B15,"*-S*") // contains "-s" =COUNTIF(B5:B15,"*x*") // contains "x"
Примечание. СЧЁТЕСЛИ не чувствительно к регистру.
Со ссылкой на ячейку
Вы можете легко настроить эту формулу, чтобы использовать в качестве критериев содержимое другой ячейки. Например, если A1 содержит текст, который вы хотите сопоставить, вы можете использовать:
=COUNTIF(range,"*"&A1&"*")
Считайте конкретные числа
Вышеуказанная формула СЧЁТЕСЛИ не будет работать, если вы ищете конкретное число, а ячейки содержат числовые данные. Это связано с тем, что подстановочный знак автоматически заставляет СЧЁТЕСЛИ искать только текст (т.е. искать «2» вместо только 2). Поскольку текстовое значение никогда не будет найдено в истинном числе, СЧЁТЕСЛИ вернет ноль.
Чтобы подсчитать определенное число в числовом содержании, вы можете использовать другую формулу, основанную на функции ПОИСК и функции ЕЧИСЛО, например:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
В этой формуле текст - это текст, который вы ищете, а диапазон - это диапазон ячеек, которые вы хотите подсчитать. Когда ПОИСК находит совпадение, он возвращает число, а функция ЕЧИСЛО преобразует его в значение ИСТИНА или ЛОЖЬ. Поскольку мы проверяем более одной ячейки, результатом будет массив или значения ИСТИНА и ЛОЖЬ. Двойное отрицание используется для преобразования значений ИСТИНА и ЛОЖЬ в единицы и нули, а функция СУММПРОИЗВ вернет сумму массива в качестве окончательного результата.
ПОИСК автоматически обрабатывает все числа как текст, поэтому он найдет, например, 1 в числе 201. Кроме того, нет необходимости использовать подстановочные знаки для обозначения позиции, поскольку ПОИСК автоматически просматривает весь текст в ячейке.
Версия с учетом регистра
Для подсчета с учетом регистра вы можете использовать формулу, основанную на функции НАЙТИ:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Здесь функция НАЙТИ используется вместо ПОИСКА, потому что НАЙТИ чувствителен к регистру. В остальном формула работает так же, как формула ISNUMBER + FIND, описанная выше.
