Вычисления по цвету ячеек
Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно :) Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету 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
- Что такое "ад условного форматирования" и как его победить
Суть вопроса- можно ли сделать так, что бы суммировались бы только видимые красным шрифтом ячейки ?
в моём примере в ячейке H16 должно стоять значение 17 996.
Спасибо.
Макросы разрешены в настройках безопасности, функция появилась в пользовательских.
Не считает ячейки залитые желтым цветом. Если убрать заливу, то просто считает все значения в строке.
Подумал было что в моей книге не так, но вставил данные в ваш образец, и то же самое. Сумму указывает 0.
Подскажите, что исправить?
Скажите, а есть ли возможность не учитывать ячейки, у которых денежный формат? Или в крайнем случае проводить анализ только каждой, например, 6-ой ячейки?
Спасибо
Подскажите, пожалуйста, можно ли по образцу заливки искать не сумму всех подходящих значений, а минимальное и максимальное среди них?
cell.Offset(, 1).Value
У меня вопрос. Можно ли при помощи данного макроса, доработав его, посчитать сумму ячеек по цветам с разных листов.
Например:
Допустим, создаем в файле (книге) таблицу, со значениями (1, 2, 60 ...), а эта книга, уже состоит из листов, которые содержат разные названия листов, но помимо текста, содержатся цифры 1, 2, 60, 65 и т.п.. Нужно просуммировать значения листов с содержащими цифрами в таблице, учитывая цвета.
пытаюсь сохранить файл после добавления упомянутой функции, пишет что нужно сохранить файл в Macro-enabled worksheet формате. Выбираю сохранить как = сохраняю, закрываю, открываю - формула выдает ошибку #NAME? хотя в ячейке все остается по-прежнему и функция в Visual Basic продолжает существовать. Даже если ее переделать заново по шагам тут же - результат не поддается дрессировке уже. только если брать исходный файл и делать в нем всё начиная с открытия нового модуля в VB
Пыталась сначала создать начальный файл Marco-enabled и в нем уже делать надстройку про цвет, сохраняла, закрывала, открывала - тот же негативный результат.
Как вижу, все пользователи хвалят, и всё, значит, у них получается, что я делаю не так? Excel у меня на английском (2013), может я чего-то недопонимаю?
Рекомендую поставить Office для Windows на виртуальную машину или во вторую загрузочную ОС - иначе 8 из 10 здешних макросов будут для вас бесполезны, к сожалению
Подскажите, пожалуйста, а можно ли каким-то образом создать правило (самое обычное условное форматирование), но только для ячеек, выделенных определенной заливкой?
Например, поменять цвет шрифта значения в ячейках, если оно не равно нулю, но только для ячеек, выделенных конкретной заливкой.
Спасибо!
Нужно вытаскивать код цвета заливки ячейки в отдельный столбец с помощью макрофункции, а потом уже создавать правило условного форматирования по этому столбцу с кодами.
Подскажите пожалуйста, можно ли как-то в макросе убрать автоматический пересчёт ячеек при любых изменениях? Чтобы конкретно эта функция срабатывала только по команде, к примеру. // Office 2003
Задачка. В выбранном диапазоне множество оттенков. Легко ошибиться, если перебирать их по очереди. Требуется макрос, в результате использования которого будет выдаваться несколько ячеек (например столбцом). Каждая из выданных ячеек будет залита соответствующим оттенком из искомого диапазона. А значение в такой ячейке будет суммой значений в ячейках данного оттенка из диапазона. То есть аргумент должен быть один - диапазон. Возможно ли это? Буду признателен за помощь.
Николай подскажите пожалуйста, возможно ли подсчитать среднее значение закрашенных ячеек?? А также, чтоб при этом нулевые ячейки не считались
на
Подскажите, а что нужно исправить в коде, чтоб считалось и по цвету ячейки и по цвету шрифта?
заменить на
Скажите пожалуйста, каким образом можно сделать сумму произведений значения ячеек определенного цвета на значение определенного столбца таблицы этой же строки. Т.е. к примеру цветом выделено некое количество, есть столбец цена. Необходимо подсчитать сумму по цвету ячеек с количеством.
функция Public Function SumByColor
можно ли в эту функцию добавить еще одно условие: суммирование значений с заливкой по определенной фамилии.
пробовала добавить функции суммесли и суммеслимн. не получается.
Суть вопроса- можно ли сделать так, что бы суммировались бы только видимые красным шрифтом ячейки ?
в моём примере в ячейке H16 должно стоять значение 17 996.
Спасибо
Можно ли дополнить этот макрос таким образом, чтобы он считал сумму по всем цветным ячейкам (предполагается, что присутствуют различные цвета) за исключение выбранного цвета?
функция Public Function SumByColor
можно ли в эту функцию добавить еще одно условие: суммирование значений с заливкой по определенной фамилии.
пробовала добавить функции суммесли и суммеслимн. не получается.
Нужно подсчитать количество ячеек определённого цвета , НО закрашеных условным форматированием.
Можно както модернизировать макрос ?
"Проблема" решаемая?
Я тоже сделал заливку условным форматированием ().
Такую заливку макрос не видит
Help me!
У меня почему-то не получается найти :
"категории Определенные пользователем"
что бы воспользоваться формулой.
может в 2016 уже сделали встроенную такую возможность?
Этот макрос работает только с заливкой, выполненной вручную.