Как посчитать количество ячеек в Excel (Эксель): формула определенное значение символов

Как в Экселе посчитать сумму определенных ячеек? Иногда при работе в Microsoft Office Excel возникает необходимость посчитать количество ячеек, как это сделать мы рассмотрим далее.

Вариант первый самый простой и элементарный, сделать его достаточно легко. Выделяем диапазон ячеек и внизу справа видим количество выделенных ячеек.

Вариант второй когда нужно количество отобразить цифрой в самом Excel. В таком случае используем встроенную функцию Microsoft Office Excel (СЧЁТЗ). Для этого встаем в ячейку в которой должен отобразится результат и пишем функцию «= СЧЁТЗ». Откроются параметры функции которые можно отредактировать или воспользоваться более простым вариантом, просто выделить нужный диапазон ячеек и нажать Enter.

Возможен вариант когда нужно посчитать ячейки содержащие определенные критерии. Для этого нужно воспользоваться функцией «СЧЁТЕСЛЫ». Указываем перечень критериев по которым будет подсчитывать количество ячеек сверху к низу в колонке.

Напротив первого критерия в соседней ячейке прописываем функцию СЧЁТЕСЛЫ «= СЧЁТЕСЛЫ (диапазон; критерий)» и нажимаем Enter. Далее достаточно просто протянуть формулу сверху вниз и получаем результат.

Также начиная с версии Microsoft Office Excel (эксель) 2007 появилась новая функция для подсчета ячеек которая называется «СЧЁТЕСЛИМН» (счёт если множество), которая позволяет задавать разное количество диапазонов и значений для отбора по нужным критериям разделяя их запятыми, что позволяет делать более сложные подсчеты ячеек.

Функция СЧЕТ как и СРЗНАЧ относится к категории статистических. Ее основная задача заключается в том, чтобы посчитать количество ячеек в списке аргументов, содержащих исключительно числовые значения. Не беда, если в таблице также присутствует и текст: функция просто «не заметит» его.

Далее, если в ячейках таблицы будут указаны цифры, которые означает какое-то время, эти значения, само собой, функция учтет.

Одной из разновидностей данной функции является СЧЕТЗ. Она также относится к статическим, а ее задачей является подсчет непустых ячеек в диапазоне. К слову, к непустым относятся ячейки, в которых зафиксированы числовые или текстовые значения, время, дата, а также такие логические значения, как «ИСТИНА» и «ЛОЖЬ». Противоположная по смыслу функция – СЧИТАТЬПУСТОТЫ – она, как вы уже догадались, считает только пустые ячейки.

Функция СЧЕТЕСЛИ

Благодаря функционалу СЧЕТЕСЛИ можно считать ячейки в Excel с применением различного рода условий. К примеру, это может быть необходимость подсчета ячеек, значение которых – отрицательное. Еще один вариант – подсчет ячеек, значение которых – больше, чем, скажем, А4. Следующий пример применения функции СЧЕТЕСЛИ заключается в том, чтобы считать ячейки, где есть только текст. Для этого в основной формуле нужно дописать слово «текст», и выглядит этот процесс таким образом, как вы видите на скриншоте.

Если функция СЧЕТЕСЛИ работает только с одним условием, то ее ближайший родственник – СЧЕТЕСЛИМН – с двумя и более условиями. Данная функция позволяет считать ячейки, используя условия «и/или».

Как подсчитать число ячеек, содержащих определенный символ / букву. У меня получилось решить задачу без использования кода VBA на основе формулы массива. Если вы не использовали ранее такие формулы, рекомендую начать с заметки Excel. Введение в формулы массива.

Шаг 1. Определим, содержится ли искомый символ в ячейке (рис. 1).

Рис. 1. Содержится ли искомый символ в выбранной ячейке
НАЙТИ($C$1;A1) – ищет символ, хранящийся в $C$1, в строке А1; если находит, возвращает позицию этого символа в строке; если не находит, то возвращает ошибку #ЗНАЧ!
Скачать заметку в формате Word, примеры в формате Excel
Шаг 2. Избавляемся от ошибочных значений (рис. 2).

Рис. 2. Замена ошибочных значений нулями
ЕСЛИОШИБКА(НАЙТИ($C$1;A1);0) – возвращает 0, если значение функции НАЙТИ($C$1;A1) выдает ошибку, в противном случае возвращает само значение функции НАЙТИ($C$1;A1)
Шаг. 3. Заменяем номер позиции на единицу

Рис. 3. Приведение всех положительных значений к единице
=ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0) – преобразование, позволяющее для любого значения позиции (не ошибки) получить 1; номер позиции, возвращаемый функцией НАЙТИ($C$1;A1), делится на 1000 и округляется до ближайшего целого (если быть точным, то ячейка Excel максимально может содержать 32 767 символов, так что «для надежности» можно заменить 1000 на 32 767 :); в этом случае, вы избежите ошибки при любом содержимом ячеек).

В качестве альтернативы можно использовать функцию ЕСЛИ:

=ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1)>0;1);0) – если значение функции НАЙТИ($C$1;A1) больше нуля, то функция ЕСЛИ заменяем это значение на единицу. Заметьте, что третий аргумент в функции ЕСЛИ отсутствует, так как функция НАЙТИ($C$1;A1) не может вернуть значение меньше или равно 0 (функция НАЙТИ($C$1;A1) возвращает только положительные значения или ошибку #ЗНАЧ!).
Шаг. 4. Суммируем все единички. Напомню, что единица соответствует ячейке, содержащей искомый символ, а ноль – ячейке, не содержащей искомый символ (рис. 4).

Рис. 4. Суммирование ячеек, содержащих искомый символ

В ячейке С2 я использовал формулу массива:
{=СУММ(ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1:A14)/1000;1);0))}
Функция последовательно вычисляет значения =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0), =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A2)/1000;1);0) … и так вплоть до =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A14)/1000;1);0)
Результат вычисления (0 или 1) заносится в память, формируя виртуальный массив {1,1,1,0,1,1,0,1,1,0,0,0,1}. Функция СУММ просто суммирует все единицы.

Альтернативная функция: =СУММ(ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1:A14)>0;1);0))

Использование критерия И

Критерий И подсчитывает ячейки, если все указанные условия выполнились. Типичным примером является формула, которая считает количество значений, входящих в числовой диапазон. Например, вы захотели подсчитать, сколько ячеек содержат значение, большее 0 и меньшее либо равное 12. Любая ячейка, которая имеет положительное значение, меньшее или равное 12, будет включена в подсчет.
Следующая формула, которая работает только в Excel 2007 и более поздних версиях, считает ячейки, которые имеют значение от 0 до 12, в диапазоне, названном Данные: =СЧЁТЕСЛИМН(Данные;">=0";Данные;"0")-СЧЁТЕСЛИ(Данные;">12").
Данная формула использует разговорный английский и подсчитывает количество значений, больших 0, а затем отнимает количество значений, больших 12. В результате получается количество ячеек со значениями, большими 0 и меньшими либо равными

12. Создание такого рода формул может привести к путанице, поскольку формула ссылается на условие ">12", даже если цель заключается в подсчете значений, меньших или равных 12. Альтернативный метод заключается в использовании формулы массива, например такой, как следующая (создание формул такого типа вам может показаться проще): =СУММ((Данные>0)*(Данные1000"). Для совместимости с предыдущими версиями Excel указывайте следующую формулу для подсчета количества элементов, которые отвечают всем трем критериям: =СУММПРОИЗВ((Месяц="Январь")*(Агент="Селезнев")*(Объем>1000)).

Рис. 116.2. Для подсчета строк, отвечающих нескольким критериям, применяется фильтрация

Рис. 116.2. Для подсчета строк, отвечающих нескольким критериям, применяется фильтрация

Если ваши данные находятся в таблице, созданной после вызова команды , вы можете испольВставка ► Таблицы ► Таблицазовать фильтрацию для отображения только тех строк, которые отвечают нескольким критериям. На рис. 116.2 показаны данные, отфильтрованные таким образом, чтобы отображались только январские продажи Селезнева, объем которых превышает 1000. Я также добавил строку Итого к этой таблице, которая показывает количество. Однако стоит отметить, что фильтрация не может быть использована для критерия ИЛИ, который мы обсудим далее.

Использование критерия ИЛИ
Для подсчета ячеек с использованием критерия ИЛИ иногда можно применять несколько функций СЧЁТЕСЛИ. Следующая формула, например, подсчитывает количество экземпляров 1,3 и 5 в диапазоне, названном Данные: =СЧЁТЕСЛИ(Данные;1)+СЧЁТЕСЛИ(Данные;3)+СЧЁТЕСЛИ(Данные;5).
Вы можете также использовать функцию СЧЁТЕСЛИ в формуле массива. Так, следующая формула массива возвращает тот же результат, что и предыдущая формула: =СУММ(СЧЁТЕСЛИ(Данные;{1;3:5})).
Введите формулу массива и нажмите Ctrl+Shift+Enter.

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

Следующая формула массива возвращает корректное количество: =СУММ(ЕСЛИ((Месяц="Январь")+(Агент="Селезнев")+(Объем>1000);1)).
Объединение критериев И и ИЛИ
Вы можете комбинировать критерии И и ИЛИ при подсчете. Возможно, вы захотите подсчитать продажи, которые соответствуют такому критерию: месяц — январь, агент — Селезнев или Петров. Эта формула массива возвращает количество продаж, которые отвечают критерию:
=СУММ((Месяц="Январь")*ЕСЛИ((Агент="Селезнев")+(Агент="Петров");1)).

Рейтинг:(голосов:1)