Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта
Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:
Перед началом работы добавьте «Счёт по цвету» в Excel
«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Как посчитать значения ячеек на основе цвета заливки
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
Как посчитать значения ячеек на основе цвета шрифта
Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Как посчитать значения ячеек на основе цвета условного форматирования
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона или Шрифта , в зависимости от типа условного форматирования.
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:
Какие ячейки и значения учитываются при вычислениях
Надстройка автоматически распознает и произведет расчет по всем цветам в диапазоне. Включая чёрный цвет по умолчанию — так, вы сможете сравнить результаты значений с цветным и чёрным цветом шрифта.
В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение.Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).
[expert_bq id=»1570″]в поле Форматировать значения, для которых следующая формула является истинной введите ВЫБОР B 9; A 6 A9;ПОИСК A 6;A9 ;ПОИСК A 6;A9 1; A 6 ПРАВСИМВ A9;ДЛСТР A 6. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Эта статья создана для пользователей, уже уверенно применяющих Условное форматирование. Для тех, кто только начинает использовать этот инструмент, рекомендуется ознакомиться с базовой статьей по этой теме Условное форматирование в MS EXCEL .
Изменение цвета ячейки MS Excel, в зависимости от значения — Вектор развития. Офисные системы для бизнеса
В появившемся окне выделяем оба правила по очереди, и нажимаем кнопку “Изменить“, после чего, в уже знакомом окне “Формат” переходим на вкладку “Шрифт” и меняем цвет текста на белый. Вот что у меня получилось в итоге:
Изменение цвета ячейки MS Excel, в зависимости от значения
А ведь он итак прекрасно умеет это делать – нам с вами остается только ему слегка помочь!
Сначала выделяем диапазон данных, то есть содержимое второго столбца таблицы MS Excel, а затем идем на вкладку “Главная“, где в группе “Стили” активируем инструмент “Условное форматирование“, и в раскрывшемся списке выбираем “Создать правило“.
В появившемся окне “Создание правила форматирования” выбираем Тип правила: “Форматировать только ячейки которые содержат”, а в конструкторе ниже, устанавливаем параметры: “Значение ячейки”, “Меньше” и вручную вписываем наш “край”: число 20.
Нажимаем кнопку “Формат” ниже, переходим на вкладку “Заливка” и выбираем красный цвет. Нажимаем “Ок”.
Примечание: вы, конечно, можете помечать ячейки не цветом их фона, а, например, размером или цветом шрифта – для этого в том же окне нужно воспользоваться соответствующими вкладками.
Посмотрите на таблицу – яблок и мандаринов у нас явно осталось совсем мало, пора делать новый закуп!
Теперь, по аналогии, создадим ещё одно правило – только на этот раз с параметрами “Значение ячейки”, “Больше”, 20. В качестве заливки укажем зеленый цвет. Готово.
Верхний и нижний диапазон, excel теперь определят без нашего участия
Мне этого показалось мало – черный текст на красном и зеленом фоне читается плохо, поэтому я решил немного украсить наши правила, и заменить цвет текста на белый. Чтобы проделать это, откройте инструмент “Условное форматирование”, но выберите не пункт “Создать правило”, а “Управление правилами“, ниже.
Изменяем правила условного форматирования в MS Excel
Настраиваем уже созданные правила форматирования в Excel
В появившемся окне выделяем оба правила по очереди, и нажимаем кнопку “Изменить“, после чего, в уже знакомом окне “Формат” переходим на вкладку “Шрифт” и меняем цвет текста на белый. Вот что у меня получилось в итоге:
Теперь я изменил не только фон ячеек таблицы, но и цвет шрифта
Попробуем изменить “плохие” значения на “хорошие”? Раз и готово – цвет автоматически изменился, как только в соответствующих ячейках появились значения, попадающие под действие одного из правил.
Выделение строк таблицы в EXCEL в зависимости от условия в ячейке. Примеры и описание
Выбираем нужные пункты в открывшемся окошке. Я залью темно зеленым все значения, что больше 90. Поскольку в последнем поле я указала адрес ( =$F$15 ), то при изменении в ячейке числа 90, например, на 110, правило также поменяется. Сохраните изменения, кликнув по кнопке «ОК» .
