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

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


Условное форматирование в 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 можно прочитать здесь.