Подсчет окрашенных ячеек в Excel: от фильтра до формул

Стандартными функциями вроде СЧЁТЕСЛИ посчитать ячейки по цвету заливки или шрифта в Excel нельзя, так как они анализируют только значения. Однако задачу можно решить четырьмя способами: от быстрого ручного фильтра до создания собственной функции на макросах (VBA), которая будет работать как обычная формула. Выбор метода зависит от вашей версии Excel и необходимости автоматизации.

Способ 1: Фильтр по цвету (без формул и кода)

Этот метод идеален для разовой проверки данных. Он не требует написания формул и работает во всех версиях Excel.

1. Выделите шапку таблицы или весь диапазон данных.
2. На вкладке Данные нажмите кнопку Фильтр (или сочетание клавиш Ctrl + Shift + L).
3. Нажмите на стрелку фильтра в нужном столбце.
4. Выберите пункт Фильтр по цвету и укажите нужный оттенок заливки или шрифта.
5. Посмотрите в левый нижний угол окна Excel (строка состояния). Там будет надпись вида: «Найдено: X из Y записей». Число X — это и есть количество окрашенных ячеек.

Способ 2: Функция пользователя VBA (Автоматический подсчет)

Если вам нужно, чтобы количество ячеек пересчитывалось автоматически при изменении цвета или данных, стандартные средства не помогут. Потребуется создать пользовательскую функцию (UDF) через редактор макросов. Это универсальное решение для Excel 2010–2026.

Инструкция по созданию функции:

1. Нажмите Alt + F11, чтобы открыть редактор VBA.
2. В меню выберите Insert > Module.
3. Скопируйте и вставьте следующий код в белое окно модуля:

Function CountByColor(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim targetColor As Long
    Dim count As Long
    
    ' Получаем код цвета из ячейки-образца
    targetColor = colorCell.Interior.Color
    
    ' Проходим по диапазону
    For Each cell In rng
        If cell.Interior.Color = targetColor Then
            count = count + 1
        End If
    Next cell
    
    CountByColor = count
End Function

4. Закройте редактор VBA (Alt + Q) и вернитесь в таблицу.
5. Используйте новую формулу как обычную функцию: =CountByColor(A1:A100; D1) Где A1:A100 — диапазон для подсчета, а D1 — любая ячейка, закрашенная в нужный вам цвет (образец).

Способ 3: Сортировка и функция СЧЁТЗ

Если использование макросов запрещено политикой безопасности компании, можно воспользоваться комбинацией сортировки и подсчета.

1. Отсортируйте столбец по цвету (Данные > Сортировка > выбрать цвет в параметрах). Все окрашенные ячейки соберутся в одном месте.
2. Визуально определите границы блока с цветом.
3. Используйте функцию СЧЁТЗ или СЧЁТ для этого конкретного диапазона: =СЧЁТЗ(A2:A15)

Минус метода: при добавлении новых данных сортировку и формулу придется обновлять вручную.

Сравнение методов решения задачи

Частые ошибки и нюансы

• Разные оттенки одного цвета. Excel различает цвета по числовому коду (RGB). Светло-красный и темно-красный для программы — это два разных цвета. При использовании VBA убедитесь, что ячейка-образец имеет точно такой же оттенок, как и целевые ячейки.
• Условное форматирование. Если цвет ячейки задан через «Условное форматирование», свойство .Interior.Color в VBA может возвращать код «Нет цвета» (так как физически ячейка не закрашена, цвет применяется визуально). Для таких случаев нужны более сложные скрипты, проверяющие свойства DisplayFormat.
• Сохранение файла. Файл с макросами (Способ 2) необходимо сохранять в формате «Книга Excel с поддержкой макросов» (.xlsm). В обычном формате (.xlsx) код будет удален при сохранении.

FAQ

Можно ли посчитать цвет шрифта, а не заливки? Да. В коде VBA замените строку targetColor = colorCell.Interior.Color на targetColor = colorCell.Font.Color. Логика работы останется той же.

Работает ли это в Google Таблицах? В Google Таблицах нет встроенной функции подсчета по цвету, но там проще писать скрипты на Apps Script, которые работают аналогично описанному выше VBA.

Почему функция CountByColor возвращает #ИМЯ? Ошибка #ИМЯ? (или #NAME?) означает, что Excel не видит функцию. Проверьте, сохранен ли код в модуле, и убедитесь, что имя файла имеет расширение .xlsm. Также проверьте, не переименовали ли вы функцию в коде.