Сумма ячеек по цвету
Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно :) Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету 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) можно найти нашу функцию и вставить ее на лист:
У нее два аргумента:
- DataRange - диапазон раскрашенных ячеек с числами
- ColorSample - ячейка, цвет которой принимается как образец для суммирования
Цвет шрифта
Легко изменить нашу функцию, чтобы она учитывала не цвет заливки фона, а цвет шрифта ячейки. Для этого в строке 6 просто замените свойство Interior на Font в обеих частях выражения.
Количество вместо суммы
Если вам нужно подсчитывать не сумму покрашенных определенным цветом ячеек, а всего лишь их количество, то наша функция будет еще проще. Замените в ней 7-ю строку на:
Sum = Sum + 1
Нюансы пересчета
К сожалению изменение цвета заливки или цвета шрифта ячейки Excel не считает изменением ее содержимого, поэтому не запускает пересчет формул. То есть при перекрашивании исходных ячеек с числами в другие цвета итоговая сумма по нашей функции пересчитываться не будет.
Полностью решить эту проблему невозможно, но можно ее существенно облегчить. Для этого в третьей строке нашей функции используется команда Application.Volatile True. Она заставляет 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
можно ли в эту функцию добавить еще одно условие: суммирование значений с заливкой по определенной фамилии.
пробовала добавить функции суммесли и суммеслимн. не получается.
Нужно подсчитать количество ячеек определённого цвета , НО закрашеных условным форматированием.
Можно както модернизировать макрос ?
У меня почему-то не получается найти :
"категории Определенные пользователем"
что бы воспользоваться формулой.
может в 2016 уже сделали встроенную такую возможность?
Этот макрос работает только с заливкой, выполненной вручную.
Подскажите, пожалуйста, можно ли объединить пользовательскую формулу сложения по цвету с логикой стандартной формулы "СУММЕСЛИ"? т.е. нужно, чтобы формула считывала имя ячеек столбца А и цвет соответствующей ячейки другого столбца В, и суммировала ячейки столбца В.