Суммирование в экселе по цвету. Заливка ячеек в зависимости от значения в Microsoft Excel

Подсчет ячеек по цвету заливки

Функция подсчитывает количество ячеек, заливка которых имеет определенный цвет. Может пригодиться, если ведется учет каких-либо соревнований и каждое место в туре имеет свой цвет ячейки. После заполнения такая таблица может и выглядит очень наглядно, но подсчитать количество первых мест, вторых, третьих становится большой проблемой, ведь в Excel до сих пор нет функций, способных суммировать/подсчитывать ячейки по цвету.

Вызов команды через стандартный диалог:

Вызов с панели MulTEx:

Сумма/Поиск/Функции - Математические - СчетЯчеек_Заливка

Синтаксис:
=СчетЯчеек_Заливка($E$2:$E$20 ; $E$7 ; I13 ; $A$2:$A$20)

ДиапазонСчета ($E$2:$E$20) - диапазон значений для подсчета. Можно указать несколько столбцов. Столбец с критерием(если планируется считать еще и по критерию) не обязательно должен входит в диапазон.

ЯчейкаОбразец ($E$7) - ячейка-образец с цветом заливки. Ячейки с этим цветом будут подсчитаны.

Критерий (I13) - необязательный аргумент. Если указан, то подсчитываются ячейки с указанным критерием и цветом заливки. Допускается применение в критерии символов подстановки - "*" и "?" . Например, для подсчета только ячеек, в которых содержится слово "мир" необходимо указать в качестве критерия - "*мир*" . Если необходимо посчитать количество непустых ячеек с указанным цветом заливки, то можно указать критерий: "*?*" . Если не указан, то подсчитываются все ячейки с указанным цветом заливки.
Так же данный аргумент может принимать в качестве критерия символы сравнения (, =,):

">0" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше нуля;
">=2" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше или равно двум;
"0" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не равно нулю;
"" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не пустые;

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: ""&D$1

ДиапазонКритерия ($A$2:$A$20) - Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан) . ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСчета . Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСчета .

ИспУФ () - Необязательный аргумент. Допускается указание логических значений ИСТИНА(TRUE) или ЛОЖЬ(FALSE). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет подсчитывать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет подсчитывать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца - то она не будет подсчитана.

Функция подсчитывает любые ячейки, заливка которых равна заливке ячейки-образца. Даже если ячейка будет пустая, но заливка будет равна указанной - ячейка будет подсчитана. Чтобы подсчитать только заполненные ячейки в качестве критерия следует указать - "*?*" , а ДиапазонКритерия не указывать.

Важно: Функция не вычисляется при изменении цвета заливки. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2 -Enter . Либо нажать сочетания клавиш Shift +F9 (пересчет функций активного листа) или клавишу F9 (пересчет функций всей книги)

Примечание: данная функция будет корректно работать даже при примененном к ячейке Условном форматировании. Однако если в ячейке/диапазоне присутствуют условия, формат для которых задан при помощи шкал, градиентов, гистограмм и значков - функция может вернуть некорректный результат. Связано это с тем, что Excel не предоставляет доступ к данным типам УФ извне.

Работая с таблицами Excel, часто прибегают к заливке фона или цветному шрифту. Выделение ячеек обращает на себя внимание и служит своего рода цветовым кодом. Напр., можно применить зелёный фон как знак успешности показателей, или назначить значениям красный шрифт как сигнал тревоги. Тем не менее, такая очевидная задача, как посчитать и суммировать значения с одинаковым цветом, нередко превращается в часы программирования макросов или формул.

Надстройка «Счёт по цвету» мгновенно и без VBA агрегирует значения любого типа цветовой расцветки:

Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
Агрегация по цвету заливки и/или цвету условного форматирования
Cчёт по цвету фона ячеек или цвету шрифта
Предпросмотр и вставка таблицы результата на рабочий лист

Добавить «Счёт по цвету» в Excel 2019, 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2019 - 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

Как посчитать значения ячеек на основе цвета заливки

Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.

Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
- Заданные цвета - чтобы учитывать только ячейки со сплошной заливкой фона
Из следующего выпадающего списка выберите «Вычислять по цвету» «Фона».
Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
Нажмите OK > Готово!

Как посчитать значения ячеек на основе цвета шрифта

Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.

Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Заданные цвета - чтобы учитывать только ячейки заданным цветом шрифта
- Все цвета - чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» «Шрифта».
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс - с разбивкой по цвету.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK > Готово!

Как посчитать значения ячеек на основе цвета условного форматирования

Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.

Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Условные цвета - чтобы учитывать только ячейки с условным форматированием
- Все цвета - чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» «Фона» или «Шрифта», в зависимости от типа условного форматирования.
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс - с разбивкой по цвету.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK > Готово!

Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)

Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:

СЧЁТ (COUNT) - подсчёт количества всех значений в диапазоне по цвету
СУММ (SUM) - сумма всех значений в диапазоне по цвету
СРЗНАЧ (AVERAGE) - среднее (арифметическое среднее) всех значений в диапазоне по цвету
МИН (MIN) - наименьшее значение в диапазоне по цвету
МАКС (MAX) - наибольшее значение в диапазоне по цвету

Как ячейки и значения учитываются при вычислениях

Надстройка автоматически распознает и произведет расчет по всем цветам в диапазоне. Включая чёрный цвет по умолчанию — так, вы сможете сравнить результаты значений с цветным и чёрным цветом шрифта.

В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение. Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).
Объединённые ячейки обрабатываются как одна ячейка.

Появились вопросы или предложения? Оставьте комментарий ниже.

Для выполнения этой задачи будем использовать возможности условного форматирования.
Возьмем таблицу, содержащую список заказов, сроки их исполнения, текущий статус и стоимость. Попробуем сделать так, чтобы ее ячейки раскрашивались сами, в зависимости от их содержимого.

Инструкция для Excel 2010

ВКЛЮЧИТЕ СУБТИТРЫ!

Как это сделать в Excel 2007

ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».

Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.

Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.

Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.

Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.

«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.

Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции .

Как это сделать в Excel 2003

ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!

Просуммируем значения ячеек в зависимости от цвета их заливки. Здесь же покажем, как подсчитать такие ячейки.

Функции для суммирования значений по цвету ячеек в EXCEL не существует (по крайней мере, в EXCEL 2016 и в более ранних версиях). Вероятно, подавляющему большинству пользователей это не требуется.

Пусть дан диапазон ячеек в столбце А. Пользователь выделил цветом ячейки, чтобы разбить значения по группам.

Необходимо сложить значения ячеек в зависимости от цвета фона. Основная задача: Как нам "объяснить" функции сложения, что нужно складывать значения, например, только зеленых ячеек?

Это можно сделать разными способами, приведем 3 из них: с помощью , Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA.

С помощью Автофильтра (ручной метод)

Добавьте справа еще один столбец с заголовком Код цвета .
Выделите заголовки и нажмите CTRL+SHIFT+L, т.е. вызовите Автофильтр ()
Вызовите меню Автофильтра , выберите зеленый цвет

Будут отображены только строки с зелеными ячейками
Введите напротив каждого "зеленого" значения число 1

Сделайте тоже для всех цветов

Введите формулу =СУММЕСЛИ(B7:B17;E7;A7:A17) как показано в файле примера (лист Фильтр) .

Для используйте функцию СЧЕТЕСЛИ() .

С помощью Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

Сразу предупрежу, что начинающему пользователю EXCEL будет сложно разобраться с этим и следующим разделом.

Идея заключается в том, чтобы автоматически вывести в соседнем столбце числовой код фона ячейки (в MS EXCEL все цвета имеют соответствующий числовой код). Для этого нам потребуется функция, которая может вернуть этот код. Ни одна обычная функция этого не умеет. Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(), которая возвращает код цвета заливки ячейки (она может много, но нам потребуется только это ее свойство).

Примечание: Макрофункции - это набор функций к EXCEL 4-й версии, которые нельзя напрямую использовать на листе EXCEL современных версий, а можно использовать только в качестве . Макрофункции - промежуточный вариант между обычными функциями и функциями VBA. Для работы с этими функциями требуется сохранить файл в формате с макросами *.xlsm

Сделайте активной ячейку В7 (это важно, т.к. мы будем использовать в формуле)
В введите формулу =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Макрофункция!A7)
Назовите ее Цвет

Закройте Диспетчер имен
Введите в ячейку В7 формулу =Цвет и скопируйте ее вниз.

Сложение значений организовано так же как и в предыдущем разделе.

Макрофункция работает кривовато:

если вы измените цвет ячейки, то макрофункция не обновит значения кода (для этого нужно опять скопировать формулу из В7 вниз или выделить ячейку, нажать клавишу F2 и затем ENTER )
функция возвращает только 56 цветов (так называемая палитра EXCEL), т.е. если цвета близки, например, зеленый и светло зеленый, то коды этих цветов могут совпасть. Подробнее об этом см. лист файла примера Colors . Как следствие, будут сложены значения из ячеек с разными цветами.

С помощью VBA

В файле примера на листе VBA приведено решение с помощью VBA. Решений может быть множество:

можно создать кнопку, после нажатия она будет вводить код цвета в соседний столбец (реализован этот вариант).
можно написать пользовательскую функцию, которая будет автоматически обновлять код цвета при изменении цвета ячейки (реализовать несколько сложнее);
можно написать программу, которая будет анализировать диапазон цветных ячеек, определять количество различных цветов, вычислять в отдельном диапазоне суммы для каждого цвета (реализовать не сложно, но у каждого пользователя свои требования: ячейки с суммами должны быть в определенном месте, необходимо учесть возможность дополнения диапазона новыми значениями и пр.).