Подсчет ячеек по цвету в Excel

Как подсчитать ячейки с цветом в Excel?

Есть несколько способов подсчета ячеек на основе цвета ячейки в Excel.

Способ # 1 — Подсчет ячеек с помощью цвета с использованием метода фильтрации с функцией промежуточного итога
Способ # 2 — Подсчет ячеек с цветом путем создания функции с использованием кода VBA

Теперь давайте подробно обсудим каждый из методов вместе с примером —

# 1 Использование метода фильтрации с функцией промежуточного итога

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

Шаг 1: Во-первых, нам нужно применить функцию промежуточных итогов, поэтому в конце данных откройте функцию SUBTOTAL в excel.

Шаг 2: Как только мы открываем функцию ПРОМЕЖУТОЧНЫЙ ИТОГО, мы получаем список функций под этим; поскольку мы считаем клетки, выберите значок «2 — COUNT”Функция.

Шаг 3: После выбора метода промежуточных итогов нам нужно выбрать диапазон ячеек для подсчета.

Шаг 4: Хорошо, теперь количество этих ячеек будет 7.

Шаг 5: Для приведенных выше данных в структуре применяется опция фильтра.

Шаг 6: Щелкните раскрывающийся список фильтров, чтобы перейти к «Фильтр по цвету ». Он покажет все доступные цвета для фильтрации.

Шаг 7: Выберите любой из цветов, и у нас будут цветные ряды.

Шаг 8: В виде ты мочь видеть выше, наша функция ПРОМЕЖУТОЧНЫЙ ИТОГ показывает только количество отфильтрованных строк. Поскольку оранжевым цветом отмечены только две строки, функция ПРОМЕЖУТОЧНЫЙ ИТОГ показывает счет только как три; Точно так же выберите другой цвет и посмотрите, работает ли он идеально или нет.

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

# 2 Создать функцию с использованием кодирования VBA

Как я уже сказал, к сожалению, у нас нет встроенной функции, которая может помочь нам подсчитывать ячейки на основе цвета в Excel. Мы сказали, что встроенной функции нет, но прелесть в том, что мы можем создать нашу собственную функцию, используя кодирование VBA.

Ниже приведен код, который я уже написал для создания функции подсчета ячеек на основе цвета.

Код:

Function ColorCount(ColorCell As Range, DataRange As Range)

   Dim Data_Range As Range
   Dim Cell_Color As Long

   Cell_Color = ColorCell.Interior.ColorIndex

   For Each Data_Range In DataRange

   If Data_Range.Interior.ColorIndex = Cell_Color Then
   ColorCount = ColorCount + 1
   End If

   Next Data_Range

End Function

Все, что вам нужно сделать, это скопировать приведенный выше код и вставить его в свой модуль VBA, как показано ниже.

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

Поставьте знак равенства и откройте функцию, которую мы создали, используя приведенный выше код.

Первый аргумент этой функции — «нам нужно выбрать цветную ячейку, которую нам нужно подсчитать», поэтому в этом случае я выберу ячейку E2.

Второй аргумент — в каком диапазоне ячеек нам нужно подсчитать выбранный цвет ячеек.

Хорошо, это все, что нам нужно для этой функции. Закройте скобку и нажмите клавишу ввода, чтобы получить счетчик выбранного цвета ячейки.

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

Это делается с помощью VBA «Функция, определяемая пользователем », он будет перебирать предоставленный диапазон ячеек и подсчитывать числовые ячейки, которые имеют тот же цвет, что и предоставленный цвет ячейки.

Затем он суммирует все совпавшие цветовые ячейки и возвращает результат в виде количества этих цветовых ячеек.

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

Что нужно помнить здесь

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