Подсчет ТЕКСТовых значений с единственным критерием в EXCEL
18 апреля 2013 г.
Для подсчета значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ() . Если критерий единственный, то ее функциональности вполне достаточно для подсчета и текстовых и числовых значений. А возможность использовать подстановочные знаки *, ? для поиска текстовых значений, делают СЧЁТЕСЛИ() достаточно мощным средством поиска.
Подсчет текстовых значений, удовлетворяющих одному критерию, можно осуществить с помощью функции СЧЁТЕСЛИ() . Данные будем брать из диапазона 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) - преобразования удастся избежать.
Комментарии