Обща формула
=COUNTIF(range,"*txt*")
Обобщение
За да преброите клетки, които съдържат определен текст, можете да използвате функцията COUNTIF с заместващ знак. В показания пример формулата в E5 е:
=COUNTIF(B5:B15,"*a*")
Обяснение
Функцията COUNTIF брои клетки в диапазон, които отговарят на критериите. Например, за да преброите броя на клетките в диапазон, който съдържа "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"
Забележка: COUNTIF не чувствителен към регистъра.
С препратка към клетка
Можете лесно да настроите тази формула, за да използвате съдържанието на друга клетка за критериите. Например, ако A1 съдържа текста, който искате да съвпадне, можете да използвате:
=COUNTIF(range,"*"&A1&"*")
Пребройте конкретни числа
Формулата COUNTIF по-горе няма да работи, ако търсите определен номер и клетките съдържат цифрови данни. Това е така, защото заместващият знак автоматично кара COUNTIF да търси само текст (т.е. да търси "2" вместо само 2). Тъй като текстова стойност никога няма да бъде намерена в истинско число, COUNTIF ще върне нула.
За да преброите конкретно число в числово съдържание, можете да използвате различна формула, базирана на функцията SEARCH и функцията ISNUMBER по следния начин:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
В тази формула текстът е текстът, който търсите, а диапазонът е диапазонът от клетки, които искате да преброите. Когато SEARCH открие съвпадение, той ще върне число и функцията ISNUMBER ще преобразува числото в TRUE или FALSE стойност. Тъй като проверяваме повече от една клетка, резултатът ще бъде масив или TRUE и FALSE стойности. Двойното отрицателно се използва за преобразуване на стойностите TRUE и FALSE в 1s и 0s, а функцията SUMPRODUCT ще върне сумата от масива като краен резултат.
SEARCH автоматично третира всички числа като текст, така че ще намери например 1 в числото 201. Също така, няма нужда да се използват заместващи символи, за да се посочи позиция, тъй като SEARCH автоматично ще преглежда целия текст в клетка.
Версия, чувствителна към малки и големи букви
За отчитане на малки и големи букви можете да използвате формула, базирана на функцията FIND:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Тук се използва функцията FIND вместо SEARCH, тъй като FIND е чувствителна към малки и големи букви. В други отношения формулата работи като формулата ISNUMBER + FIND, описана по-горе.
