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

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

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

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

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

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

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

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

Пример 1.1

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

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

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

Пример 1.3

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

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

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

Пример 1.5

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

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

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

Пример 1.7

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

Как подсчитать ячейки с цветом в Excel, пример 1.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

Пример 1.9

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

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

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

Пример 1.11

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

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

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

Пример 1.13

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

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

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

Пример 1.15

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

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

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

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

Пример 1.17

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

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

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

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.