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

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

В своей работе мы практически ежедневно сталкиваемся с таблицами Excel. Для их анализа, зачастую, используем разноцветные заливки ячеек. Визуализация данных, безусловно, помогает пользователю легче усваивать содержимое таблицы. Но, что делать, когда наступает момент подсчета результата? Для этого мы предлагаем не использовать стандартный подход применения фильтров по цвету, а воспользоваться редактором Microsoft Visual Basic (далее VBA), который есть в любом стандартном пакете от Microsoft Office.

Запускать его будем в Microsoft Excel по следующему алгоритму:

  • запускаем MS Excel;
  • входим во вкладку «Вид»;
  • нажимаем «Макросы»;
  • в отрывшемся окне вводим название нашей будущей программы (разрешены английские буквы и символы);
  • нажимаем «Создать».

Теперь мы в редакторе Visual Basic, и чтобы осуществить расчет по количеству залитых ячеек используем следующий код:

Public Function SumColour(DataRange As Range, ColorSample As Range) As Double Dim cell As Range Dim SumAll As Long Application.Volatile True SumAll = 0 For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then SumAll = SumAll+ 1 End If Next cell SumColour = SumAll End Function
Как посчитать сумму цветных ячеек в Excel, не используя фильтры?

Так, с помощью функции SumColour, можно пересчитать количество ячеек.

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

Для пересчета суммы окрашенных ячеек берем вышеописанный код, меняем название функции (например, SumByColour) и строку кода «SumAll = SumAll+ 1» на «SumAll = SumAll+ cell.Value».

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

На практике использование редактора VBA помогает пересчитывать большие объемы данных не опасаясь ошибок, связанных с сортировкой и фильтрацией в Excel.

А какой способ используете вы? Делитесь в комментариях!

66
5 комментариев

Теперь мы в редакторе Visual Basic... 
Скажите, а вы не пробовали проделать то же самое в Google Docs (Spreadsheets)?
Работает?

Попробовал бы сам, но к сожалению, не в курсе как там запускать подобные скрипты.

1

Добрый день. Для Google Sheets есть такие же скрипты . Как-то писал скрипт для 8 цветов. Например, в настройках вы выбираете комфортные для себя цвета и скрипт потом суммирует то что выделено цветом

1
Автор

Добрый день! Спасибо за ваш вопрос.
Запускали только на локальном ресурсе, используя Excel.
В Google Docs пока не реализовывали.

Как-то это нехорошо, считать цветных отдельно от белых...

На небольшом объеме алгоритм ОК, но будет очень медленным на больших. Как вариант ускорения - использовать UDF для вывода кода цвета заливки в доп столбце и делать расчет по этому столбцу штатной функцией с условим