Возможности !SEMTools

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Посчитать пустые ячейки
  • Посчитать непустые ячейки
  • Посчитать количество заполненных ячеек
  • Посчитать количество ячеек с определенным значением

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

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

  • Посчитать ячейки с логическим типом данных
  • Посчитать числовые ячейки
  • Посчитать текстовые ячейки

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

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

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

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

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

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

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

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

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

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

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

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