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

Короткое руководство: Поиск и подсчёт по цвету

Этот метод не требует формул. Он прост и подойдёт, если вам нужно разово узнать количество ячеек с одинаковым цветом.

1. Выделите диапазон или лист, где нужно посчитать цветные ячейки.
2. Перейдите на вкладку Главная.
3. В группе Редактирование кликните по значку лупы с подписью Найти и выделить.
4. Выберите Найти….

5. Если вкладка «Найти» не раскрыта — нажмите Параметры >>.

6. Нажмите на маленькую стрелку раскрывающегося списка рядом с Формат и выберите Выбрать формат из ячейки….

7. Кликните по ячейке с нужной заливкой или цветом шрифта. Формат отобразится в блоке «Просмотр».

8. Нажмите Найти всё. Внизу окна отобразится общее число найденных совпадений.

Важно: тот же процесс работает для подсчёта по цвету текста — выберите ячейку с нужным цветом шрифта вместо заливки.

Недостатки метода

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

Альтернативные подходы (когда нужен динамический подсчёт)

Ниже — рабочие варианты в порядке от простого к более гибкому.

• Вспомогательная колонка + формула. Используйте пользовательскую формулу на основе GET.CELL в именованном диапазоне, затем посчитайте COUNTIF по значениям этой колонки.
• Пользовательская функция (VBA/UDF). Напишите UDF, например GetFillColor(cell), который возвращает код цвета; затем COUNTIF или SUMPRODUCT по этому значению. Работает динамично при пересчёте книги.
• Power Query. Импортируйте таблицу в Power Query, добавьте столбец с цветом через расширения (ограничено версиями) или экспортируйте свойства ячеек сторонним инструментом. Подходит для ETL и больших наборов.
• Фильтр по цвету + SUBTOTAL. Отфильтруйте по цвету и используйте SUBTOTAL для подсчёта видимых строк. Полезно для быстрых отчётов.

Краткая инструкция: UDF на VBA (микро-метод)

1. Нажмите Alt+F11, чтобы открыть редактор VBA.
2. Вставьте новый модуль и добавьте код:

Function GetFillColor(rng As Range) As Long
  GetFillColor = rng.Interior.Color
End Function

3. В таблице используйте формулу =GetFillColor(A2) в вспомогательной колонке.
4. Затем посчитайте количество одинаковых кодов, например: =COUNTIF(B:B; 255)

Примечание: VBA-функция вернёт код цвета RGB ячейки. Книга должна быть сохранена в формате, поддерживающем макросы (.xlsm).

Мини-методология выбора подхода

• Нужен быстрый одноразовый ответ → используйте «Найти» с выбором формата.
• Нужна автоматизация и пересчёт при изменениях → используйте UDF (VBA) или вспомогательный столбец с GET.CELL.
• Работа с большими данными или автоматизированными процессами → Power Query.
• Нужно только показать отфильтрованные строки и посчитать видимые → фильтр по цвету + SUBTOTAL.

Решение для ролей: чек-лист

• Для рядового пользователя:
  • Использовать «Найти» для одноразового подсчёта.
  • Записать шаги, если придётся повторять.
• Для аналитика/администратора:
  • Оценить объём данных и частоту обновлений.
  • Если чаще одного раза — реализовать UDF или вспомогательную колонку.
• Для разработчика/автоматизатора:
  • Реализовать UDF и добавить обработчики событий (Worksheet_Change) при необходимости.
  • Обеспечить тесты и резервную копию макросов.

Пример: подсчёт с помощью вспомогательной колонки и GET.CELL

1. Создайте имя через Диспетчер имён: в поле «Ссылка» используйте формулу =GET.CELL(63;INDIRECT(“RC”;FALSE)) — 63 возвращает цвет заливки.
2. Вставьте в строку формулу с именем для каждой строки.
3. Посчитайте повторяющиеся значения через COUNTIF.

Примечание: GET.CELL — макрофункция Excel 4.0, работает как именованная формула и требует ручного пересчёта в некоторых ситуациях.

Модель принятия решения (Mermaid)

flowchart TD
  A[Нужно посчитать цветные ячейки?] --> B{Одноразово или часто?}
  B -- Одноразово --> C[Использовать Найти + Выбрать формат]
  B -- Часто --> D{Требуется автоматическое обновление?}
  D -- Нет --> E[Power Query или фильтр + SUBTOTAL]
  D -- Да --> F{Разрешены макросы?}
  F -- Да --> G[Реализовать UDF 'VBA']
  F -- Нет --> H[Вспомогательная колонка с GET.CELL]

Критерии приёмки

• Подсчёт показывает ожидаемое количество для тестового набора (ручная валидация).
• Решение обновляется при изменении цветов (если это требование).
• Макросы документированы и подписаны, если применяются.

Краткая справка

GET.CELL — специальная формула Excel 4.0 для получения свойств ячеек. UDF — пользовательская функция на VBA (User-Defined Function).

Краткое резюме

Инструмент «Найти» с выбором формата — самый быстрый путь для разового подсчёта цветных ячеек. Для динамики используйте VBA/UDF, GET.CELL или Power Query в зависимости от ограничений безопасности и объёма данных. Выберите метод по критерию частоты обновлений и требованиям к автоматизации.

Важно: всегда сохраняйте резервную копию файла перед добавлением макросов или массовыми изменениями формата.