Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL

Для подсчета значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ(). Если критерий единственный, то ее функциональности  вполне достаточно для подсчета и текстовых и числовых значений. А возможность использовать подстановочные знаки *, ? для поиска текстовых значений, делают СЧЁТЕСЛИ() достаточно мощным средством поиска.

Подсчет текстовых значений, удовлетворяющих одному критерию, можно осуществить с помощью функции СЧЁТЕСЛИ(). Данные будем брать из диапазона A2:A12 (см. файл примера).

Подсчитывать будем значения с различными вариантами вхождения слова «яблоки».

Задача

Критерий

Формула

Результат

Примечание

подсчитать значения точно соответствующие критерию

яблоки

=СЧЁТЕСЛИ($A$2:$A$12; "яблоки")

2

Критерий указан в формуле

подсчитать значения точно соответствующие критерию

яблоки
(ячейка C6)

=СЧЁТЕСЛИ($A$2:$A$12; C6)

2

Критерий указан через ссылку

подсчитать значения, заканчивающиеся на слово яблоки, например, Cвежие яблоки

*яблоки
(ячейка С3)

=СЧЁТЕСЛИ($A$2:$A$12; C3)

3

Использован подстановочный знак *
(перед значением)

подсчитать значения, начинающиеся на слово яблоки, например, яблоки местные

яблоки*
(ячейка С4)

=СЧЁТЕСЛИ($A$2:$A$12; C4)

4

Использован подстановочный знак *
(после значения)

подсчитать значения, содержащие слово яблоки в любом месте

*яблоки*
(ячейка С5)

=СЧЁТЕСЛИ($A$2:$A$12; C5)

7

Использовано 2 подстановочных знака *

подсчитать ячейки, начинающиеся с гру и содержащие ровно 6 букв

гру???
(ячейка С7)

=СЧЁТЕСЛИ($A$2:$A$12; C7)

1

Использован подстановочный знак ?

подсчитать значения с учетом РЕгиСТра содержащие Яблоки в любом месте

Яблоки
(ячейка С8)

=СУММ(ЕСЛИ (ЕОШИБКА(НАЙТИ( $C$8;$A$2:$A$12));0;1))

2

Использована формула массива

подсчитать ячейки точно содержащие слово Яблоки с учетом РЕгиСТра

Яблоки
(ячейка С9)

=СУММ(ЕСЛИ( СОВПАД($C$9;A2:A12);1;0))

1

Использована формула массива

Также для подсчета значений с подстановочными знаками может быть использована функция БСЧЕТА(). Для ее работы требуется табличка с критериями (см. файл примера).

Примечание. О подсчете числовых значений с одним критерием читайте статью Функция СЧЁТЕСЛИ() в MS Excel - Подсчет значений с единственным критерием.

О подсчете числовых значений, сохраненных в текстовом формате

Вычисления в MS EXCEL производятся с точностью до 15 знаков (см. статью Округление и точность до 15 значащих цифр в MS EXCEL). Если требуется сохранить число с количеством знаков больше 15 (например, номер кредитной карты), то используют тектовый формат ячейки (см. статью Отображение в MS EXCEL ЧИСЕЛ с точностью более 15 значащих цифр). При подсчете таких значений с помощью функции СЧЕТЕСЛИ() могут возникнуть сложности.

Как видно из рисунка выше формула =СЧЁТЕСЛИ(A7:A14;C9) должна была вернуть значение 2, т.к. в столбце А два значения 12345678910110005 (выделено красным, 17 значащих цифр). Однако, формула возвращает 8, т.е. все значения удовлетворяют критерию. Дело в том, что СЧЕТЕСЛИ() пытается перед подсчетом преобразовать значения из столбца А в числа. Ей здесь это удается. После преобразования мы получим числа с точностью 15 знаков, а не 17, т.е. все числа из столбца А будут преобразованы в 12345678910110000 и станут равны между собой.

Чтобы этого не происходило используйте формулу =СЧЁТЕСЛИ(A7:A14;"*"&C9) - преобразования удастся избежать. 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)