Суммирование ячеек по цвету заливки
Добавление заливки ячеек делает ваш документ Excel более понятным и удобочитаемым. Иногда приходится производить суммирование ячеек с выбранным цветом заливки.
К сожалению, Excel не имеет встроенной функции, которая использует цвета заливки в качестве условий в формулах. К счастью, просуммировать ячейки вы можете с помощью функции из надстройки VBA-Excel.
Описание функции
Функция =СУММЗАЛИВКА(ДИАПАЗОН; ЯЧЕЙКА) имеет два обязательных аргумента:
- ДИАПАЗОН — ссылка на диапазон ячеек, где необходимо произвести суммирование ячеек с определенной заливкой.
- ЯЧЕЙКА — ссылка на ячейку с цветом заливки, по которому необходимо провести суммирование.
Если вы забыли синтаксис данной функции, вы всегда сможете вызвать формулу из выпадающего списка функций в надстройке.
Замечание об автоматическом вычислении. Так как пересчет формул в Excel автоматически не происходит при изменении заливки ячеек, то данная функция не пересчитывается автоматически. Для того, чтобы принудительно пересчитать формулы используйте сочетание клавиш Ctrl + Alt +F9.
Код на VBA
Public Function СУММЗАЛИВКА(ДИАПАЗОН As Range, ЯЧЕЙКА) As Double Dim S As Double Dim rCell As Range Dim ColCell As Long ColCell = ЯЧЕЙКА.Interior.Color S = 0 For Each rCell In ДИАПАЗОН If rCell.Interior.Color = ColCell Then S = S + rCell.Value End If Next СУММЗАЛИВКА = S End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Вычислить сумму цветных ячеек в Excel (с заливкой)
TLDR В видео демонстрируется обходной путь для вычисления суммы ячеек на основе их цвета в Excel с использованием опции фильтра и макроса.
Summarize any YouTube video Summarize any video by yourself
Install on Chrome
Ключевые выводы
Расшифровка предоставляет метод суммирования значений в ячейках, которые ранее были разделены по цвету в Excel.
Настроив фильтр и используя опцию «фильтровать по цвету», вы можете легко рассчитать сумму значений в ячейках с определенным цветом в Excel.
Использование маркеров автозаполнения в Excel позволяет быстро и эффективно вводить данные и манипулировать ими, повышая производительность при выполнении задач анализа данных.
Использование ячеек разного цвета в Excel может помочь более точно упорядочить и рассчитать значения.
В видео демонстрируется метод суммирования ячеек на основе их цвета в Excel, позволяющий проводить более продвинутый анализ данных и вычисления.
Как посчитать сумму цветных ячеек в Excel, не используя фильтры?
В своей работе мы практически ежедневно сталкиваемся с таблицами Excel. Для их анализа, зачастую, используем разноцветные заливки ячеек. Визуализация данных, безусловно, помогает пользователю легче усваивать содержимое таблицы. Но, что делать, когда наступает момент подсчета результата? Для этого мы предлагаем не использовать стандартный подход применения фильтров по цвету, а воспользоваться редактором Microsoft Visual Basic (далее VBA), который есть в любом стандартном пакете от Microsoft Office.
Запускать его будем в Microsoft Excel по следующему алгоритму:
- запускаем MS Excel;
- входим во вкладку «Вид»;
- нажимаем «Макросы»;
- в отрывшемся окне вводим название нашей будущей программы (разрешены английские буквы и символы);
- нажимаем «Создать».
Теперь мы в редакторе Visual Basic, и чтобы осуществить расчет по количеству залитых ячеек используем следующий код:
Public Function SumColour(DataRange As Range, ColorSample As Range) As Double Dim cell As Range Dim SumAll As Long Application.Volatile True SumAll = 0 For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then SumAll = SumAll+ 1 End If Next cell SumColour = SumAll End Function
Так, с помощью функции SumColour, можно пересчитать количество ячеек.
Для пересчета суммы окрашенных ячеек берем вышеописанный код, меняем название функции (например, SumByColour) и строку кода «SumAll = SumAll+ 1» на «SumAll = SumAll+ cell.Value».
На практике использование редактора VBA помогает пересчитывать большие объемы данных не опасаясь ошибок, связанных с сортировкой и фильтрацией в Excel.
А какой способ используете вы? Делитесь в комментариях!
18K открытий
5 комментариев
Теперь мы в редакторе Visual Basic.
Скажите, а вы не пробовали проделать то же самое в Google Docs (Spreadsheets)?
Работает?
Попробовал бы сам, но к сожалению, не в курсе как там запускать подобные скрипты.
Развернуть ветку
Добрый день. Для Google Sheets есть такие же скрипты . Как-то писал скрипт для 8 цветов. Например, в настройках вы выбираете комфортные для себя цвета и скрипт потом суммирует то что выделено цветом
Развернуть ветку
Добрый день! Спасибо за ваш вопрос.
Запускали только на локальном ресурсе, используя Excel.
В Google Docs пока не реализовывали.
Развернуть ветку
Как-то это нехорошо, считать цветных отдельно от белых.
Развернуть ветку
На небольшом объеме алгоритм ОК, но будет очень медленным на больших. Как вариант ускорения — использовать UDF для вывода кода цвета заливки в доп столбце и делать расчет по этому столбцу штатной функцией с условим
Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта
Работая с таблицами Excel, часто прибегают к заливке фона или цветному шрифту. Выделение ячеек обращает на себя внимание и служит своего рода цветовым кодом. Напр., можно применить зелёный фон как знак успешности показателей, или назначить значениям красный шрифт как сигнал тревоги. Тем не менее, такая очевидная задача, как посчитать и суммировать значения с одинаковым цветом, нередко превращается в часы программирования макросов или формул.
Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:
Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
Агрегация по цвету заливки и/или цвету условного форматирования
Cчёт по цвету фона ячеек или цвету шрифта
Предпросмотр и вставка таблицы результата на рабочий лист
Перед началом работы добавьте «Счёт по цвету» в Excel
«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как посчитать значения ячеек на основе цвета заливки
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.
Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона .
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!
Как посчитать значения ячеек на основе цвета шрифта
Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.
Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Шрифта .
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!
Как посчитать значения ячеек на основе цвета условного форматирования
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.
Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона или Шрифта , в зависимости от типа условного форматирования.
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!
Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)
Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:
СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
СУММ (SUM) – сумма всех значений в диапазоне по цвету
СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
МИН (MIN) – наименьшее значение в диапазоне по цвету
МАКС (MAX) – наибольшее значение в диапазоне по цвету
Какие ячейки и значения учитываются при вычислениях
Надстройка автоматически распознает и произведет расчет по всем цветам в диапазоне. Включая чёрный цвет по умолчанию — так, вы сможете сравнить результаты значений с цветным и чёрным цветом шрифта.
В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение.Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).