Сумма ячеек по цвету

Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно :) Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету Excel в последних версиях научился, то суммировать по цвету до сих пор не умеет.

Чтобы исправить этот существенный недостаток можно использовать несложную пользовательскую функцию на Visual Basic, которая позволит нам суммировать ячейки с определенным цветом.

Откройте редактор Visual Basic:

  • В Excel 2003 и старше для этого нужно выбрать в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
  • В новых версиях Excel 2007-2013 перейти на вкладку Разработчик (Developer) и нажать кнопку Visual Basic. Если такой вкладки у вас не видно, то включите ее в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon)

В окне редактора вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такой функции:

Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double 
     Dim Sum As Double 
     Application.Volatile True 

     For Each cell In DataRange 
         If cell.Interior.Color = ColorSample.Interior.Color Then 
             Sum = Sum + cell.Value 
         End If 
     Next cell 
     SumByColor = Sum 
 End Function 
 

Если теперь вернуться в Excel, то в Мастере функций (Вставка - Функция) в появившейся там категории Определенные пользователем (User Defined) можно найти нашу функцию и вставить ее на лист:

color-calc1.png 

У нее два аргумента:

  • DataRange - диапазон раскрашенных ячеек с числами
  • ColorSample - ячейка, цвет которой принимается как образец для суммирования

Цвет шрифта

Легко изменить нашу функцию, чтобы она учитывала не цвет заливки фона, а цвет шрифта ячейки. Для этого в строке 6 просто замените свойство Interior на Font в обеих частях выражения.

Количество вместо суммы

Если вам нужно подсчитывать не сумму покрашенных определенным цветом ячеек, а всего лишь их количество, то наша функция будет еще проще. Замените в ней 7-ю строку на:

Sum = Sum + 1

Нюансы пересчета

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

Полностью решить эту проблему невозможно, но можно ее существенно облегчить. Для этого в третьей строке нашей функции используется команда Application.Volatile True. Она заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию  F9).

И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец - "думать" будет долго :)

Ссылки по теме

 



26.07.2013 14:42:39
как быть если цвет в ячейках определн по результату условного форматирования? Как переделать функцию?
12.10.2013 13:23:56
Тут все на порядок сложнее. В VBA нет инструментов, позволяющих определить цвет заливки ячейки, если было применено условное форматирование. Единственный способ - это проверять в коде условия, прописанные в условном форматировании, но это можно делать только для простых условий, а если были применены, например, цветовые шкалы - не поможет ничего :(
05.02.2017 18:57:07
Добрый день, прошу помочь - в столбце ячеек применено условие изменения цвета чернил к цвету фона и видно только расчёт в строке, в которой есть хотя бы одно из вычитаемых, но в сумме (ячейка H 16  в примере) указывается  сумма всех ячеек с учётом  невидимых -
Суть вопроса- можно ли сделать так, что бы суммировались бы только видимые красным шрифтом ячейки ?
в моём примере в ячейке H16  должно стоять значение 17 996.

https://yadi.sk/d/qojfWrhx3Cqpf3

Спасибо.
18.11.2013 23:01:18
Макрос не работает в экселе 2013.
Макросы разрешены в настройках безопасности, функция появилась в пользовательских.
Не считает ячейки залитые желтым цветом. Если убрать заливу, то просто считает все значения в строке.
Подумал было что в моей книге не так, но вставил данные в ваш образец, и то же самое. Сумму указывает 0.

Подскажите, что исправить?
08.01.2014 09:57:03
Макрос не работает с заливкой с условным форматированием - у вас не она?
08.01.2014 07:02:19
Можно ли усовершенствовать данную функцию: при суммировании функция плюсует только цифра, но если в диапазоне суммирования попадаются буквы, то выдается ошибка в значении. Для примера стандартная функция СУММ считает и с буквами (пропуская их). Функция из Плекса тоже  не могут суммировать пропустив буквы.
08.01.2014 09:56:11
Сергей, пропускать нечисловые значения можно так:
Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double
     Dim Sum As Double
     Application.Volatile True
 
     For Each cell In DataRange 
         If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then
             Sum = Sum + cell.Value 
         End If
     Next cell 
     SumByColor = Sum 
 End Function
31.07.2014 14:06:34
Николай, здравствуйте. Спасибо за помощь.

Скажите, а есть ли возможность не учитывать ячейки, у которых денежный формат? Или в крайнем случае проводить анализ только каждой, например, 6-ой ячейки?
23.08.2014 21:34:57
Добрый день. А как можно, наоборот, учитывать только текстовые значения?
Спасибо
Макрос в 2013 работает, просто функция автоматом почему то не обновляется :) Надо постоянно клацать на ячейку с функцией чтобы она подсчитала кол-во ячеек ;) Правда функция не работает с белым цветом :(
07.05.2014 12:56:33
А статью до конца прочитать?
19.05.2014 12:34:41
Большое спасибо, у меня вышло! Респект вам!
25.08.2014 14:39:46
День добрый ! Все супер! Но можно ли задать еще условие чтоб считал количество закрашенных ячеек дат в столбце конкретного месяца?
03.09.2014 18:42:05
Николай, подскажите, пожалуйста, есть ли возможность при размещении кода в книгу Personal поменять имя функции, убрав PERSONAL.XLSB! и вызывать функцию вручную, а не через мастер функций?
12.11.2014 12:27:18
День добрый!
Подскажите, пожалуйста, можно ли по образцу заливки искать не сумму всех подходящих значений, а минимальное и максимальное среди них?
14.02.2015 11:52:43
Ксения, да, можно, но придется слегка изменить код:
Public Function MaxByColor(DataRange As Range, ColorSample As Range) As Double
     Dim Sum As Double
     Application.Volatile True
 
     For Each cell In DataRange
         If cell.Interior.Color = ColorSample.Interior.Color Then
             if cell.Value>CurMax then CurMax=cell.Value
         End If
     Next cell
     MaxByColor = CurMax
 End Function
Для поиска минимального по цвету просто замените Max на Min и знак > на <.
29.01.2015 11:10:14
Здравствуйте, формула заработала! Скажите, а как соединить эту функцию с функцией "промежуточные итоги"?
14.02.2015 11:49:39
Я бы добавил справа от вашей таблицы еще один столбец, где с помощью нашей функции вывел бы коды цветов каждой строки. А потом уже подвел итоги по этому столбцу.
10.02.2015 00:07:24
Здравствуйте, функция не считает количество (Sum = Sum + 1) ячеек, если цвет задан условным форматированием... подскажите, пожалуйста, как можно это исправить?
14.02.2015 11:48:50
Михаил, к сожалению, цвет условного форматирования так учесть невозможно (ограничение самого Excel).
28.02.2015 20:10:16
Здравствуйте, а как сделать, чтобы функция суммировала значения из соседних ячеек следующего столбца (цветом выделены названия, а их значения в соседнем столбце цветом не выделяются)?
04.03.2015 23:02:54
Нашел ответ:)
cell.Offset(, 1).Value
07.05.2015 09:52:36
Здравствуйте!
У меня вопрос. Можно ли при помощи данного макроса, доработав его, посчитать сумму ячеек по цветам с разных листов.
Например:
Допустим, создаем в файле (книге) таблицу, со значениями (1, 2, 60 ...), а эта книга, уже состоит из листов, которые содержат разные названия листов, но помимо текста, содержатся цифры 1, 2, 60, 65 и т.п.. Нужно просуммировать значения листов с содержащими цифрами в таблице, учитывая цвета.
29.09.2015 11:07:10
очень полезная вещь, но либо лыжи не едут у меня...
пытаюсь сохранить файл после добавления упомянутой функции, пишет что нужно сохранить файл в Macro-enabled worksheet формате. Выбираю сохранить как = сохраняю, закрываю, открываю - формула выдает ошибку #NAME? хотя в ячейке все остается по-прежнему и функция в Visual Basic продолжает существовать. Даже если ее переделать заново по шагам тут же - результат не поддается дрессировке уже. только если брать исходный файл и делать в нем всё начиная с открытия нового модуля в VB
Пыталась сначала создать начальный файл Marco-enabled и в нем уже делать надстройку про цвет, сохраняла, закрывала, открывала - тот же негативный результат.
Как вижу, все пользователи хвалят, и всё, значит, у них получается, что я делаю не так? Excel у меня на английском (2013), может я чего-то недопонимаю?
13.11.2015 14:35:09
Коллеги, с офисом для Mac это работает? У меня офис 2016, и либо я что не так делаю, либо такой приём в нём не работает. Подскажите, как быть?
06.12.2015 14:37:26
С офисом для Mac много чего не работает - у него другая объектная модель.
Рекомендую поставить Office для Windows на виртуальную машину или во вторую загрузочную ОС - иначе 8 из 10 здешних макросов будут для вас бесполезны, к сожалению :cry:
22.11.2015 11:34:01
Николай, подскажите, где ошибка... Выдает значение 0. На Max все работает исправно

Public Function MinByColor(DataRange As Range, ColorSample As Range) As Double
     Dim Sum As Double
     Application.Volatile True
 
     For Each cell In DataRange
         If cell.Interior.Color = ColorSample.Interior.Color Then
             If cell.Value < CurMin Then CurMin = cell.Value
         End If
     Next cell
     MinByColor = CurMin
 End Function
02.12.2015 16:21:42
Добрый день!

Подскажите, пожалуйста, а можно ли каким-то образом создать правило (самое обычное условное форматирование), но только для ячеек, выделенных определенной заливкой?
Например, поменять цвет шрифта значения в ячейках, если оно не равно нулю, но только для ячеек, выделенных конкретной заливкой.

Спасибо!
06.12.2015 14:38:18
Стандартными средствами - нет.
Нужно вытаскивать код цвета заливки ячейки в отдельный столбец с помощью макрофункции, а потом уже создавать правило условного форматирования по этому столбцу с кодами.
07.12.2015 15:41:28
День добрый!
Подскажите пожалуйста, можно ли как-то в макросе убрать автоматический пересчёт ячеек при любых изменениях? Чтобы конкретно эта функция срабатывала только по команде, к примеру. // Office 2003
02.01.2016 10:54:32
Николай, день добрый! Если вернуться, к вопросу о заливке ячеек с применением УФ, вы сможете помоч? При заливке я использую зелёный и серый цвета
25.02.2016 09:03:58
Добрый день. а как сделать, чтобы подсчет велся если в указанных ячейках нужного цвета встречаются буквы?
12.03.2016 13:05:53
А как в ColorSample ограничить выбор одной ячейкой, чтобы при выборе пользователем диапазона, программа ругалась, как это происходит с другими функциями при указании недопустимых аргументов?
30.05.2016 12:12:44
Восхищаюсь этим сайтом!!! Использую этот макрос и радуюсь!!!! благодарность создателю
07.07.2016 12:34:11
Здравствуйте!
Задачка. В выбранном диапазоне множество оттенков. Легко ошибиться, если перебирать их по очереди. Требуется макрос, в результате использования которого будет выдаваться несколько ячеек (например столбцом). Каждая из выданных ячеек будет залита соответствующим оттенком из искомого диапазона. А значение в такой ячейке будет  суммой значений в ячейках данного оттенка из диапазона. То есть аргумент должен быть один - диапазон.  Возможно ли это? Буду признателен за помощь.  
25.10.2016 07:18:38
Здравствуйте!
Николай подскажите пожалуйста, возможно ли подсчитать среднее значение закрашенных ячеек?? А также, чтоб при этом нулевые ячейки не считались
27.11.2016 19:04:10
Должно быть как-то так:
Public Function AverageColoredCells(DataRange As Range) As Double
     Dim Sum As Double
     Dim n as long
     Application.Volatile True
 
     For Each cell In DataRange 
      If cell.Interior.ColorIndex <> -4142 And cell.Value<>0 Then
       Sum = Sum + cell.Value 
       n = n+1
      End If
     Next cell 
     AverageColoredCells = Sum / n
 End Function
18.11.2016 13:00:28
Подскажите пожалуйста, как переделать данный скрипт на сумму ячеек в строке. Или проблема в Офис 365? Не работает вообщем
26.11.2016 05:16:03
Николай, подскажите, как можно посчитать цифры в зачеркнутых ячейках
27.11.2016 19:00:31
Замените строку
If cell.Interior.Color = ColorSample.Interior.Color Then

на
If cell.Font.Strikethrough = True Then
27.11.2016 01:28:55
Добрый день.

Подскажите, а что нужно исправить в коде, чтоб считалось и по цвету ячейки и по цвету шрифта?
27.11.2016 18:58:37
Нужно строку
If cell.Interior.Color = ColorSample.Interior.Color Then

заменить на
If cell.Interior.Color = ColorSample.Interior.Color And cell.Font.Color = ColorSample.Font.Color Then
12.12.2016 00:28:22
Подскажите , пожалуйста, можно ли изменить код чтоб счет был и по цвету ячейки  и по содержимому ячейки (буквам)?
13.01.2017 15:56:54
Добрый день, Николай.
Скажите пожалуйста, каким образом можно сделать сумму произведений значения ячеек определенного цвета на значение определенного столбца таблицы этой же строки. Т.е. к примеру цветом выделено некое количество, есть столбец цена. Необходимо подсчитать сумму по цвету ячеек с количеством.
17.01.2017 13:32:09
Спасибо Николай, очень помогли. Благодарю вас!!!:)
31.01.2017 12:52:08
Николай, добрый день. Помогите, пожалуйста. Проблема в следующем. Мне необходимо, чтобы цвет шрифта в ячейки выбирался, исходя из значения другой ячейки. Для примера Если А1="Желтый", то цвет шрифта А2=желтый.  
04.02.2017 17:58:05
здравствуйте, Николай! просьба помочь.
функция Public Function SumByColor
можно ли в эту функцию добавить еще одно условие: суммирование значений с заливкой по определенной фамилии.
пробовала добавить функции суммесли и суммеслимн. не получается.
05.02.2017 18:58:30
Добрый день, прошу помочь - в столбце ячеек применено условие изменения цвета чернил к цвету фона и видно только расчёт в строке, в которой есть хотя бы одно из вычитаемых, но в сумме (ячейка H 16 в примере) указывается сумма всех ячеек с учётом невидимых -
Суть вопроса- можно ли сделать так, что бы суммировались бы только видимые красным шрифтом ячейки ?
в моём примере в ячейке H16 должно стоять значение 17 996.

https://yadi.sk/d/qojfWrhx3Cqpf3

Спасибо
07.02.2017 15:52:40
Николай, подскажите, пожалуйста, есть ли возможность при размещении кода в книгу Personal поменять имя функции, убрав PERSONAL.XLSB! и вызывать функцию вручную, а не через мастер функций?
09.02.2017 12:06:07
Добрый день!
Можно ли дополнить этот макрос таким образом, чтобы он считал сумму по всем цветным ячейкам (предполагается, что присутствуют различные цвета) за исключение выбранного цвета?
12.02.2017 11:22:48
здравствуйте! просьба помочь.
функция Public Function SumByColor
можно ли в эту функцию добавить еще одно условие: суммирование значений с заливкой по определенной фамилии.
пробовала добавить функции суммесли и суммеслимн. не получается.
21.02.2017 11:48:37
Здравствуйте , столкнулся со следущей проблемой :
Нужно подсчитать количество ячеек определённого цвета , НО закрашеных условным форматированием.
Можно както модернизировать макрос ?
22.04.2017 12:44:59
А как сделать подсчёт содержимого маркированных цветом ячеек в Excel 2016?
У меня почему-то не получается найти :
"категории Определенные пользователем"
что бы воспользоваться формулой.

может в 2016 уже сделали встроенную такую возможность?  
01.05.2017 15:28:39
Добрый день, друзья! Выдает значение ноль! Может кто подскажет в чем проблема? Пробовал и в других книгах. Ноль и всё…
10.11.2017 14:40:59
У вас там не условное форматирование дает заливку, часом?
Этот макрос работает только с заливкой, выполненной вручную.
Доброго времени суток, что нужно изменить в строке Sum = Sum + 1 , чтобы посчитать все закрашенные ячейки с буквой В???  
10.11.2017 14:40:01
Нужно изменить в 6-й строке:
If cell.Interior.Color = ColorSample.Interior.Color Then
на
If cell.Interior.Color = ColorSample.Interior.Color and cell.value Like "*В*" Then
05.03.2018 10:06:12
Добрый день!  можно-ли посчитать сумму средних значений закрашенных ячеек (ячейки разных цветов) и при этом пропускать не закрашенные?
21.01.2019 23:57:20
Вот читаю коменты и думаю на сколько же несовершенен и сложен в познании Эксель, что мы простые смертные будем делать без таких людей как Николай. Спасибо Вам
25.02.2019 14:40:27
Николай, добрый день.

Подскажите, пожалуйста, можно ли объединить пользовательскую формулу сложения по цвету с логикой стандартной формулы "СУММЕСЛИ"? т.е. нужно, чтобы формула считывала имя ячеек столбца А и цвет соответствующей ячейки другого столбца В, и суммировала ячейки столбца В.  
Наверх