Сумма ячеек по цвету
Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно :) Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету 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
Задачка. В выбранном диапазоне множество оттенков. Легко ошибиться, если перебирать их по очереди. Требуется макрос, в результате использования которого будет выдаваться несколько ячеек (например столбцом). Каждая из выданных ячеек будет залита соответствующим оттенком из искомого диапазона. А значение в такой ячейке будет суммой значений в ячейках данного оттенка из диапазона. То есть аргумент должен быть один - диапазон. Возможно ли это? Буду признателен за помощь.
Николай подскажите пожалуйста, возможно ли подсчитать среднее значение закрашенных ячеек?? А также, чтоб при этом нулевые ячейки не считались
на
Подскажите, а что нужно исправить в коде, чтоб считалось и по цвету ячейки и по цвету шрифта?
заменить на
Скажите пожалуйста, каким образом можно сделать сумму произведений значения ячеек определенного цвета на значение определенного столбца таблицы этой же строки. Т.е. к примеру цветом выделено некое количество, есть столбец цена. Необходимо подсчитать сумму по цвету ячеек с количеством.