✅ Как посчитать ЦВЕТНЫЕ ячейки в Excel (Пошаговое руководство + ВИДЕО)

Содержание

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

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

Разве не было бы замечательно, если бы в Excel была функция подсчета цветных ячеек?

К сожалению, для этого нет встроенной функции.

НО…

Это легко сделать.

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

В этом руководстве я покажу вам три способа подсчета цветных ячеек в Excel (с VBA и без него):

Использование фильтра и функции ПРОМЕЖУТОЧНЫЙ ИТОГ
Использование функции GET.CELL
Использование пользовательской функции, созданной с помощью VBA

# 1 Подсчет цветных ячеек с помощью фильтра и ПРОМЕЖУТОЧНОГО ИТОГА

Чтобы подсчитать цветные ячейки в Excel, вам необходимо выполнить следующие два шага:

Фильтровать цветные ячейки
Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для подсчета видимых цветных ячеек (после фильтрации).

Предположим, у вас есть набор данных, как показано ниже:

В этом наборе данных используются два цвета фона (зеленый и оранжевый).

Вот шаги подсчета цветных ячеек в Excel:

В любой ячейке под набором данных используйте следующую формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (102; E1: E20)
Выберите заголовки.
Перейдите в Данные -> Сортировка и фильтр -> Фильтр. Это применит фильтр ко всем заголовкам.
Щелкните любой из раскрывающихся списков фильтров.
Перейдите в «Фильтр по цвету» и выберите цвет. В приведенном выше наборе данных, поскольку для выделения ячеек используются два цвета, фильтр показывает два цвета для фильтрации этих ячеек.

Как только вы отфильтруете ячейки, вы заметите, что значение в функции ПРОМЕЖУТОЧНЫЙ ИТОГ изменяется и возвращает только количество ячеек, видимых после фильтрации.

Как это работает?

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ использует 102 в качестве первого аргумента, который используется для подсчета видимых ячеек (скрытые строки не учитываются) в указанном диапазоне.

Если данные не отфильтрованы, они возвращают 19, но если они отфильтрованы, то возвращается только количество видимых ячеек.

Попробуйте сами… Загрузите файл примера

# 2 Подсчет цветных ячеек с помощью функции GET.CELL

GET.CELL - это функция Macro4, которая была сохранена из соображений совместимости.

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

Однако он работает в именованных диапазонах Excel.

Смотрите также: Узнайте больше о функции GET.CELL.

Вот три шага по использованию GET.CELL для подсчета цветных ячеек в Excel:

Создайте именованный диапазон с помощью функции GET.CELL
Используйте именованный диапазон, чтобы получить цветовой код в столбце
Использование номера цвета для подсчета количества окрашенных ячеек (по цвету)

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

Создание именованного диапазона

Перейдите в Формулы -> Определить имя.
В диалоговом окне «Новое имя» введите:
- Имя: GetColor
- Объем: Рабочая тетрадь
- Относится к: = GET.CELL (38, Sheet1! $ A2)
  В приведенной выше формуле я использовал Лист1! $ A2 как второй аргумент. Вам нужно использовать ссылку на столбец, в котором у вас есть ячейки с цветом фона.

Получение цветового кода для каждой ячейки

В ячейке рядом с данными используйте формулу = GetColor

Эта формула вернет 0, если в ячейке НЕТ цвета фона, и определенное число, если есть цвет фона.

Это число зависит от цвета, поэтому все ячейки с одинаковым цветом фона получают одно и то же число.

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

Если вы выполните описанный выше процесс, у вас будет столбец с числами, соответствующими цвету фона в нем.

Чтобы получить количество определенного цвета:

Где-нибудь под набором данных укажите тот же цвет фона для ячейки, которую вы хотите подсчитать. Убедитесь, что вы делаете это в том же столбце, который вы использовали при создании именованного диапазона. Например, я использовал столбец A и, следовательно, буду использовать только ячейки в столбце «A».
В соседней ячейке используйте следующую формулу:

= СЧЁТЕСЛИ ($ F $ 2: $ F $ 20, GetColor)

Эта формула даст вам количество всех ячеек с указанным цветом фона.

Как это работает?

Функция СЧЁТЕСЛИ использует именованный диапазон (GetColor) в качестве критерия. Именованный диапазон в формуле относится к соседней ячейке слева (в столбце A) и возвращает цветовой код для этой ячейки. Следовательно, этот номер цветового кода является критерием.

Функция СЧЁТЕСЛИ использует диапазон ($ F $ 2: $ F $ 18), который содержит номера цветовых кодов всех ячеек и возвращает количество на основе числа критериев.

Попробуйте сами… Загрузите файл примера

# 3 Подсчет количества цветов с помощью VBA (путем создания пользовательской функции)

В двух приведенных выше методах вы узнали, как подсчитывать цветные ячейки без использования VBA.

Но, если вас устраивает использование VBA, это самый простой из трех методов.

Используя VBA, мы могли бы создать настраиваемую функцию, которая будет работать как функция СЧЁТЕСЛИ и возвращать количество ячеек с определенным цветом фона.

Вот код:

'Код, созданный Sumit Bansal из https://trumpexcel.com Функция GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Установить rCell = CountRange для каждого rCell In rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Функция

Чтобы создать эту настраиваемую функцию:

Когда ваша книга активна, нажмите Alt + F11 (или щелкните правой кнопкой мыши вкладку рабочего листа и выберите Просмотреть код). Это откроет редактор VB.
На левой панели под книгой, с которой вы работаете, щелкните правой кнопкой мыши любой из листов и выберите Вставить -> Модуль. Это вставит новый модуль. Скопируйте и вставьте код в окно кода модуля.
Дважды щелкните имя модуля (по умолчанию имя модуля в Module1) и вставьте код в окно кода.
Закройте редактор VB.
Вот и все! Теперь у вас есть настраиваемая функция на листе под названием GetColorCount.

Чтобы использовать эту функцию, просто используйте ее как любую обычную функцию Excel.

Синтаксис: = GetColorCount (CountRange, CountColor)

CountRange: диапазон, в котором вы хотите подсчитать ячейки с указанным цветом фона.
CountColor: цвет, для которого вы хотите посчитать ячейки.

Чтобы использовать эту формулу, используйте тот же цвет фона (который вы хотите подсчитать) в ячейке и используйте формулу. Аргумент CountColor будет той же ячейкой, в которой вы вводите формулу (как показано ниже):