Как в Excel подсчитать определенные ячейки

Количество выделенных ячеек Excel показывает сам: выделите диапазон и посмотрите в строку состояния внизу окна — там будет «Количество» (непустые ячейки). Если показателя нет, щёлкните строку состояния правой кнопкой и включите «Количество» или «Количество чисел». Формулами определённые ячейки считают =СЧЁТЗ(A2:A100) (непустые), =СЧЁТ(A2:A100) (числа), =СЧИТАТЬПУСТОТЫ(A2:A100) (пустые) и =СЧЁТЕСЛИ(A2:A100;">0") (по условию). Ниже — какие ещё бывают варианты и где о них прочитать.

«Подсчёт определенных ячеек в Excel» звучит как совсем несложная задача, но на практике у неё настолько много вариаций, что мне с трудом удастся перечислить их все.

Но я всё же постараюсь подойти к этому вопросу максимально структурно.

Если ответы на их все дать на одной странице, будет неудобно. Поэтому ответ на каждый из вопросов содержится в отдельных ссылках внутри этой статьи.

Основные формулы подсчёта ячеек

Для отфильтрованных данных обычные функции считают и скрытые строки. Посчитать только видимые ячейки поможет =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A2:A100) (103 — аналог СЧЁТЗ без скрытых строк), для нескольких условий — СЧЁТЕСЛИМН: =СЧЁТЕСЛИМН(A2:A100;">0";B2:B100;"Москва").

Чем определяется ячейка (параметры)

Удивительно, но с виду простая прямоугольная ячейка Excel на поверку - довольно сложный объект. Ведь она представляет собою одновременно:

• Форматирование ячейки,
• Формулу в ячейке,
• Значение ячейки.

Причем каждая из этих составляющих ячейки также может определяться множеством других параметров и характеристик. Разберем их по порядку и попробуем определить соответствующие задачи по извлечению и подсчету таких ячеек.

Подсчет ячеек по параметрам их форматирования

Ячейка богата способами её отформатировать — у неё есть

• Текст и его атрибуты:
  • Начертание (полужирный, курсив, подчеркнутый...),
  • Размер (8, 9, 10...),
  • Шрифт (Arial, Verdana, Times New Roman и т.д.),
  • Цвет шрифта,
  • Выравнивание по ширине и высоте.
• Фон (он же заливка):
  • Сплошной,
  • Узор,
  • Градиент.
• Геометрические размеры:
  • Ширина,
  • Высота.

Вряд ли кому-то придется искать и считать ячейки с узором вида «50% серый», но, например, довольно часто люди ищут:

• Формулу «жирный шрифт» в Excel,
• Как посчитать выделенные цветом ячейки,
• Как посчитать ячейки определённого цвета.

Подсчёт ячеек по параметрам их формул

Формулы в ячейках либо есть, либо их нет, но и формулы в свою очередь могут содержать самые разные паттерны, доступные для поиска. В Excel 2013 и более поздних версиях наиболее полезной для этих целей будет функция Ф.ТЕКСТ. Она позволяет показывать формулы в ячейках как текст, после чего их можно обрабатывать как обычные текстовые данные.

Подсчёт ячеек по наличию значения

Самый верхнеуровневый параметр значения ячейки - это его наличие. А отсюда и соответствующие задачи:

• Посчитать пустые ячейки — =СЧИТАТЬПУСТОТЫ(A2:A100);
• Посчитать непустые ячейки — =СЧЁТЗ(A2:A100);
• Посчитать количество заполненных ячеек;
• Посчитать количество ячеек с определенным значением — =СЧЁТЕСЛИ(A2:A100;"Москва").

Подсчёт ячеек по типу данных

Но и наличие значения можно сузить до наличия определённого значения по его типу, не глядя вглубь самого значения. О каких типах речь:

• Посчитать ячейки с логическим типом данных;
• Посчитать числовые ячейки — =СЧЁТ(A2:A100);
• Посчитать текстовые ячейки — =СЧЁТЕСЛИ(A2:A100;"*").

Подсчёт по характеристикам самих значений ячеек

У каждого значения ячейки может быть ряд параметров, который в свою очередь зависит от типа данных, которые в них содержатся.

От простого к сложному:

Логические значения

Здесь все просто — в таких ячейках могут быть лишь два значения - ИСТИНА и ЛОЖЬ, отсюда и задачи

• посчитать количество ИСТИН в Excel;
• посчитать ЛОЖЬ/ложные значения в Excel.

Числовые значения

С числами все чуть сложнее, помимо самого их наличия, числа могут быть больше или меньше определенных значений, их часто суммируют и т.д. Поэтому вариаций задач с ними больше:

• Посчитать цифры в ячейках и ячейки с цифрами;
• Посчитать ячейки со значением больше нуля;
• Посчитать сумму отфильтрованных ячеек;
• Посчитать сумму определённых ячеек.

Текстовые значения

Наиболее вариативны задачи по работе с текстовыми ячейками:

• Посчитать ячейки, содержащие определенный текст
  • в начале ячейки,
  • в конце,
  • где угодно,
  • целиком совпадающие с текстом;
• посчитать слова в тексте;
• посчитать определенные символы в тексте.

Почему ячейки считаются неверно

• СЧЁТЗ считает «пустые» ячейки. В них пробел или формула, возвращающая "". Для таких ячеек используйте =СУММПРОИЗВ(--(СЖПРОБЕЛЫ(A2:A100)<>"")).
• СЧЁТ не видит числа. Числа сохранены как текст — преобразуйте их в числа.
• После фильтра количество не изменилось. СЧЁТЗ и СЧЁТЕСЛИ учитывают скрытые строки. Используйте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;…).
• В строке состояния нет «Количества». Щёлкните строку состояния правой кнопкой и включите нужные показатели.

Подсчёт ячеек в Excel — частые вопросы

Как посчитать количество выделенных ячеек?

Выделите диапазон — «Количество» появится в строке состояния внизу окна. Если его нет, включите показатель правым кликом по строке состояния.

Как посчитать только определённые ячейки?

Функцией =СЧЁТЕСЛИ(A2:A100;"условие"): например ">0" — числа больше нуля, "Москва" — ячейки с этим значением, "*текст*" — ячейки, содержащие фрагмент.

Как посчитать непустые ячейки?

Функцией =СЧЁТЗ(A2:A100). Пустые — =СЧИТАТЬПУСТОТЫ(A2:A100).

Как посчитать ячейки после фильтра?

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A2:A100) — считает только видимые непустые ячейки.

Итоги: как посчитать определённые ячейки в Excel

Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: посчитать слова и посчитать символы в ячейке.