Вычисления по цвету ячеек
Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно :) Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету 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 и не задавайте в качестве первого аргумента целый столбец - "думать" будет долго :)
Ссылки по теме
- Сортировка строк по цвету
- Функции подсчета количества и суммы ячеек по цвету из надстройки PLEX
- Что такое "ад условного форматирования" и как его победить
Подскажите, пожалуйста, можно ли объединить пользовательскую формулу сложения по цвету с логикой стандартной формулы "СУММЕСЛИ"? т.е. нужно, чтобы формула считывала имя ячеек столбца А и цвет соответствующей ячейки другого столбца В, и суммировала ячейки столбца В.
Меня тоже интересует данный вопрос. Если возможно конечно, хотелось бы объединить SumByColor с СУММЕСЛИМН.
Заранее благодарю!
10.11.2017 14:40:01
Нужно изменить в 6-й строке:
Ваши макросы работали отлично, пока я не добавил в таблицу новые столбцы и строки, которые, впрочем, не должны были повлиять на их функционирование.
Но теперь функция SumByColor выдаёт ошибку #ЗНАЧ!, а функция SumByCallColor продолжает работать нормально.
Подскажите, как избавиться от ошибки.
К стати в ячейках стал использовать формулы, а не значения. Может быть из-за этого не работает.
Тема давняя, мне тоже помогла очень, большое спасибо!
Есть вопрос - можно ли сделать так чтобы рассчитывались ячейки с учетом заливки, т.е. двух цветов?
Заранее спасибо!
Подскажите, пожалуйста, можно ли сложить не диапазон, а несколько отдельных ячеек ?
Спасибо
Нужен макрос, повторяющий функционал формулы СуммЕслиМн, но чтобы один из диапазонов суммирования был по цвету ячейки, а остальные (еще 1 или 2 диапазона) по критерию как в формуле.
Заранее спасибо.
"Если теперь вернуться в Excel, то в Мастере функций (Вставка - Функция) в появившейся там категории Определенные пользователем (User Defined) можно найти нашу функцию и вставить ее на лист:"
У меня в меню "Вставка" нет меню "Функция" (Excel 10)
И вообще меню "Функция" нигде найти не могу???
Sub DisplayColorCount() Dim Rng As Range Dim CountRange As Range Dim ColorRange As Range Dim xBackColor As Long On Error Resume Next xTitleId = "dptutorials" Set CountRange = Application.Selection Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type:=8) Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type:=8) Set ColorRange = ColorRange.Range("A1";) xReturn = 0 For Each Rng In CountRange qqq = Rng.Value xxx = Rng.DisplayFormat.Interior.Color If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then xBackColor = xBackColor + 1 End If Next MsgBox "Count of Colors is " & xBackColor End SubПоставила макрос для счета по цвету, у меня начал тормозить EXEL 2019. Примерно 11 секунд ничего не могу дальше делать, вносить данные. В чем может быть причина? И что можно сделать? Заранее, благодарю!)
Помогите пожалуйста, очень нужна помощь
UPDATE: вопрос отпал. Просто скопировал функцию, дал ей новое имя, а в коде вместо "=", написал "<>":))
Отличный материал, спасибо. Но есть необходимость добавления доп. условия по принципу работы функции "счётеслимн" (как я почитал в комментариях - весьма актуальный вопрос)
Может быть вопрос и не совсем по теме, но все же...
Ничего подобного не нашел.
Задача: Подставить в ячейки, закрашенные условным форматированием, значения в зависимости от цвета условного форматирования.
Например: красный цвет форматирования - "К" (буква в ячейке залитой этим цветом).
Заранее спасибо!!!
Пожалуйста, подскажите, как нужно поменять функцию, чтобы в диапазоне вычислялась формула для всех ячеек, кроме тех, которые выделены цветом. Причем у меня не просто сумма вычисляется, а =СУММЕСЛИ(E9:T16;"<=8")+((СЧЁТЕСЛИ(E9:T16;">8")*8))
Пробовала в функции = менять на <> и выходит ошибка.
Не могу догадаться, как это сделать.