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

Быстрые ссылки

• Подсчёт через Find
• Подсчёт через фильтр и SUBTOTAL
• Альтернативные подходы и сценарии использования

Подсчёт окрашенных ячеек через Find

Когда нужен быстрый одноразовый подсчёт — используйте инструмент “Find” (Найти). Этот метод не создаёт формул: он просто найдёт и покажет количество совпадений.

Шаги:

1. Выделите диапазон, в котором нужно искать.
2. На вкладке Home в группе Editing нажмите “Find & Select” → “Find”.

3. В окне “Find and Replace” нажмите “Options”.

4. Если вы точно знаете формат (например, зелёная заливка), нажмите “Format” и выберите нужную заливку на вкладке Fill. Если формат неизвестен или вы хотите взять формат из конкретной ячейки — нажмите стрелку рядом с “Format” → “Choose Format From Cell” и кликните по ячейке с нужным цветом.

5. Нажмите “Find All”. В расширенном окне внизу будет показан список найденных ячеек и строка с количеством: “X Cell(s) Found” — это и есть искомое число.

Важно:

• Метод считает по факту форматирования видимой ячейки (заливка/цвет шрифта и т. д.).
• Если формат применён через условное форматирование, “Find” находит ячейки по их отображаемому формату, но не даст динамического счётчика — при изменении данных придётся повторять поиск.

Когда этот способ не сработает:

• Формат зависит от формулы в условном форматировании, и вы хотите динамически реагировать на изменения — тогда лучше фильтр + SUBTOTAL или VBA.
• Нужен отчёт, который обновляется автоматически — используйте следующий метод.

Подсчёт окрашенных ячеек через фильтр и SUBTOTAL

Этот метод даёт динамический счётчик, который меняется при фильтрации. Идеально, когда вы часто меняете фильтры или хотите видеть число в ячейке рабочего листа.

1. В ячейке, где должен отображаться счёт, введите формулу SUBTOTAL. Пример (замените A2:A19 на свой диапазон):

=SUBTOTAL(102,A2:A19)

В этом примере 102 — числовой код для функции COUNT, применяемой в SUBTOTAL; SUBTOTAL игнорирует скрытые вручную строки в зависимости от кода.

2. Включите фильтрацию: выделите заголовок столбца и на вкладке Home выберите “Sort & Filter” → “Filter”.

3. Нажмите стрелку фильтра в заголовке столбца, наведите на “Filter by Color” и выберите нужную заливку или цвет шрифта.

4. SUBTOTAL автоматически покажет число только для видимых после фильтрации по цвету строк.

Чтобы убрать фильтр, выберите пункт “Clear Filter From” в меню фильтра.

Плюсы этого метода:

• Счёт обновляется при изменении фильтра.
• Не требует макросов для базового использования.

Ограничения:

• SUBTOTAL считает только видимые строки (по фильтру), но сам по себе не различает цвет без фильтра по цвету.
• Если у вас смешаны форматы (заливка + цвет шрифта) и нужен сложный набор правил, проще написать UDF.

Альтернативные подходы

1. Простая UDF на VBA — гибко и надёжно. Вставьте модуль и используйте функцию CountColored:

Function CountColored(rColor As Range, rRange As Range) As Long
  Dim c As Range
  Application.Volatile
  For Each c In rRange
    If c.Interior.Color = rColor.Interior.Color Then
      CountColored = CountColored + 1
    End If
  Next c
End Function

Пример применения в листе:

=CountColored($B$1,$A$2:$A$100)

Где B1 — ячейка с образцом цвета. UDF быстро сравнивает свойство .Interior.Color и работает с динамикой (при изменении цвета может потребоваться пересчёт листа).

2. Power Query — подходит для больших таблиц и повторяемых процессов. Импортируйте диапазон в Power Query, добавьте столбец с цветом через пользовательские колонки (в Power Query Desktop через M-код или расширения) и затем группируйте/считаете по значению цвета.

3. GET.CELL через именованную формулу — устаревший подход (использует макросы в бэкэнде листа), требует создания именованного диапазона и volatile-пересчёта. Подойдёт, если вы не хотите включать VBA, но он менее прозрачен.

Когда выбирать альтернативу:

• Нужна автоматическая и мгновенная реакция на изменение цвета вручную → UDF на VBA.
• Объём данных большой и требуется ETL → Power Query.
• Простая одноразовая проверка → Find.

Практическая методология для команды

Мини-метод: 1) Определите цель подсчёта (отчёт, визуальная проверка, автоматизация). 2) Выберите инструмент (Find / SUBTOTAL+Filter / UDF / Power Query). 3) Тестируйте на небольшом диапазоне. 4) Документируйте источник цвета: ручная заливка или условное форматирование. 5) Внедрите и проверьте после изменений.

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

• Число в целевой ячейке совпадает с результатом ручной выборки для трёх тестовых цветов.
• При смене фильтра по цвету значение обновляется автоматически (для SUBTOTAL метода).
• При изменении цвета образцовой ячейки UDF отражает новое значение после пересчёта.

Роль-based чеклист

• Аналитик: проверить, что диапазон корректно выбран; подтвердить, что подсчёт отражает видимые строки.
• Менеджер: подтвердить требования — одноразовый отчёт или автоматический счёт.
• Админ: при использовании VBA проверить безопасность макросов и подпись файла.

Шпаргалка по формулам и инструментам

• Быстро: Find → Options → Format → Find All.
• Динамически: =SUBTOTAL(102,мой_диапазон) + Filter by Color.
• Если нужен гибкий автоматический счёт: используйте UDF CountColored выше.
• Для ETL и повторных отчётов: Power Query.

Тестовые случаи для проверки

• Один цвет в диапазоне из 100 ячеек — сравнить Find vs UDF vs SUBTOTAL+Filter.
• Ячейки с одинаковой заливкой, но разными цветами шрифта — проверить, что метод считает по заливке, если это требование.
• Условное форматирование: изменить входные данные, убедиться, что подсчёт обновляется (UDF по умолчанию не видит формулу условного форматирования как значение; в этом случае лучше фильтр по цвету или пересчитать вручную).

Совместимость и заметки по локали

• Функции и UI-метки сохраняются в английских названиях в формульной записи при использовании некоторых версий Excel на английском; в русской версии имена функций локализованы (например, SUBTOTAL → ПОДИТОГ). Проверьте локаль формул, если делитесь файлом с коллегами.
• VBA-код одинаков на всех локалях.

Глоссарий в одну строку

• SUBTOTAL: агрегирующая функция, которая может учитывать только видимые строки; используется с кодом функции.
• UDF: пользовательская функция на VBA.
• Filter by Color: фильтр столбца по цвету заливки или шрифта.

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

Если вам нужен одноразовый быстрый подсчёт — используйте Find; если нужен динамический счёт для отображения в ячейке — используйте SUBTOTAL вместе с фильтром по цвету; если требуется гибкость и автоматизация — реализуйте простую UDF на VBA или подготовьте процесс в Power Query.

Важно

• При работе с файлами, в которых макросы запрещены, избегайте UDF и используйте фильтры или Power Query.

Социальное превью

OG Title: Как посчитать окрашенные ячейки в Excel OG Description: Быстрые и надёжные методы: Find, SUBTOTAL+Filter, VBA и Power Query — выберите подходящий и автоматизируйте подсчёт цветов.

Конец статьи.