Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта

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

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

«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

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

1. Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.

   

   Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
2. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:

   • Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
   • Условные цвета – чтобы учитывать только ячейки с условным форматированием
   • Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
3. Из следующего выпадающего списка выберите «Вычислять по цвету» Фона.
4. Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

   

   Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
5. Выберите поместить результаты на новый или существующий лист.
6. Нажмите OK Готово!

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

1. Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.

   

   Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
2. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:

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

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

1. Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.

   

   Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
2. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:

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

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

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

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

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