Подсчет ТЕКСТовых значений с единственным критерием в 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) - преобразования удастся избежать.


Комментарии
Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved