Вычисления по цвету ячеек

230681 20.01.2013 Скачать пример


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

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

На вкладке Разработчик (Developer) нажмите кнопку Visual Basic или сочетание клавиш Alt+F11, чтобы открыть редактор макросов. Если такой вкладки у вас не видно, то включите ее в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon).

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

Function CountByColor(DataRange As Range, ColorSample As Range) As Long
    Dim cell As Range, n As Long
    
    For Each cell In DataRange
        If cell.Interior.Color = ColorSample.Interior.Color Then n = n + 1
    Next cell
    CountByColor = n
End Function

Function SumByColor(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value
    Next cell
    SumByColor = total
End Function

Function AverageByColor(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double, n As Long
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then
            total = total + cell.Value
            n = n + 1
        End If
    Next cell
    AverageByColor = total / n
End Function
 

Как легко сообразить, первая функция здесь вычисляет количество ячеек с заданным цветом заливки, вторая - сумму, а третья - среднее арифметическое. У всех функций два аргумента:

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

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

Сумма, количество и среднее ячеек с заданным цветом заливки

Добавление условий

Аналогичный подход можно легко масштабировать, добавляя, при необходимости, дополнительные условия в проверку (команда if ... then...). Так, например, если нам нужно при вычислении среднего арифметического учитывать не только цвет заливки, но и цвет шрифта (т.е. считать не просто жёлтые, а именно жёлто-красные ячейки), то код нашей макро-функции будет выглядеть так:
 Function AverageByColor2(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double, n As Long
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color And cell.Font.Color = ColorSample.Font.Color Then
            total = total + cell.Value
            n = n + 1
        End If
    Next cell
    AverageByColor2 = total / n
End Function
Разница только в добавленном через логическую связку "И" (and) условии на проверку соответствия цвета шрифта очередной проверяемой ячейки cell.Font.Color цвету шрифта ячейки-образца ColorSample.Font.Color.

Ограничения и нюансы пересчёта

У созданных нами макрофункций есть 2 важных нюанса.

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

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

Полностью решить эту проблему невозможно, но есть несколько способов её обойти:

  • Сделать двойной щелчок левой кнопкой мыши по ячейки с нашей формулой и нажать на Enter, т.е. имитировать повторный ввод функции в ячейку - Excel её заново пересчитает и выдаст обновленный результат.
  • Можно использовать сочетание клавиш Ctrl+Alt+F9, которое принудительно заставит Excel пересчитать всё формулы и функции независимо от того, изменились ли для них исходные данные. Но это сочетание нужно будет не забывать нажимать каждый раз при изменении исходных данных.
  • Добавить в код наших макрофункций (в любое место) команду Application.Volatile True. Эта команда языка Visual Basic заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию  F9). Однако, в этом случае, скорость работы наших функций заметно снизится, т.к. их пересчёт будет происходить постоянно - даже когда мы не меняли цвета или значения ячеек в исходных данных. Пользуйтесь этим методом осторожно.

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

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

 


Страницы: 1  2  
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
Здравствуйте , столкнулся со следущей проблемой :
Нужно подсчитать количество ячеек определённого цвета , НО закрашеных условным форматированием.
Можно както модернизировать макрос ?
20.12.2019 11:58:48
Посмотрел дату вопроса - прошло почти три года.
"Проблема" решаемая?
Я тоже сделал заливку условным форматированием (https://www.planetaexcel.ru/techniques/9/194/).
Такую заливку макрос не видит :(
Help me!
24.12.2019 10:45:17
Не решаемая :(
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
Страницы: 1  2  
Наверх