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

Как суммировать данные ячеек по цвету, то есть вычислить сумму значений ячеек при условном форматировании?

Условное форматирование в Excel – удобное средство визуального выделения ячеек в зависимости от условия, то есть оформление ячеек отличается в зависимости от их значения. Реализовано начиная с Excel 2007. Такое оформление визуально помогает находить нужную информацию. Вот простой пример:

Удобно, красиво, наглядно. А что если расширить функционал Excel и сделать суммирование данных ячеек по цвету?

Задача решается использованием экранной формы:

и при помощи такого вот макроса, который запускается по нажатию кнопки Вычислить экранной формы:

Private Sub btn1_Click()
    Dim Mycolor As Range
    Dim RangeForSUM As Range
    Dim MyResult As Range
    Dim SummRange As Double

SummRange = 0
Set Mycolor = Range(RefEdit1.Value)
    For Each cell In Range(RefEdit2.Value)
        If cell.DisplayFormat.Interior.Color = Mycolor.DisplayFormat.Interior.Color Then
            SummRange = SummRange + cell.Value
        End If
    Next cell

Range(Me.RefEdit3.Value).Value = SummRange
Unload UserForm1
End Sub

Чтобы вывести форму на экран, в редакторе Microsoft VBA добавляется программный модуль с единственной процедурой вывода экранной формы:

Вот так выглядит результат работы макроса:

Надо еще добавить, что макрос работает, если ячейки закрашены именно условным форматированием, так как использует свойство DisplayFormat.Interior.Color.

Если ячейки залиты цветом вручную, нужно изменить одну строчку макроса:

If cell.DisplayFormat.Interior.Color = Mycolor.DisplayFormat.Interior.Color Then на

If cell.Interior.Color = Mycolor.Interior.Color Then

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

Public Function СУММПОЦВЕТУ(Mycolor As Range, RangeForSUM As Range) As Double
  Dim MySum As Double
  Application.Volatile True
   For Each cell In RangeForSUM
    If cell.Interior.Color = Mycolor.Interior.Color Then
     MySum = MySum + cell.Value
    End If
   Next cell
  СУММПОЦВЕТУ = MySum
End Function

Использование функции:

=СУММПОЦВЕТУ(A2; A1:D5)

A2 - указываем ячейку, с которой нужно взять нужный цвет заливки;
А1:D5 - указываем диапазон по которому провести сложение.

Пробуем функцию в Excel:

Важно. Если после подсчета зальете в диапазоне дополнительно какие то ячейки нужным цветом, то функция автоматом НЕ ПЕРЕСЧИТАЕТСЯ. Чтобы пересчиталась надо нажать F9

Файл с макросом, формой и пользовательской функцией СУММПОЦВЕТУ можно скачать по ссылке.

Обратите внимание, что и макросы и пользовательские функции работают только в той книге, куда они были добавлены. Если пользоваться ими приходится достаточно часто, имеет смысл задуматься о хранении макросов и пользовательских процедур в каком-то определенном месте, откуда их можно копировать по мере необходимости. Кроме этого, можно пользоваться таким мощным средством Excel, как надстройки.

Более подробно о создании и использовании надстроек Excel можно прочитать здесь.

Ранее:
Как разделить столбец Excel на несколько диапазонов?

Далее:
Как найти и выделить повторяющиеся значения ячеек, например цветом?