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


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



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

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



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



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



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



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

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

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

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

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



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

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

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

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



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

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

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

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