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

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

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

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

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

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, Minimum, Maximum)

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

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

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

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

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