Условное форматирование в MS EXCEL. Цветовые шкалы

Разберемся с правилами Условного форматирования, которые называются Цветовые шкалы. Эти шкалы удобны для визуального анализа небольшого массива числовых данных и легко настраиваются. А также глубже взглянем на цвета, которыми окрашиваются данные: постараемся определить код их цвета.

Пусть у нас есть столбец с числовыми значениями от 0 до 17. Необходимо применить Условное форматирование , чтобы значения близкие к максимальному числу 17 имели красный оттенок, а числа близкие к минимальному 0 - белый.

Для этого воспользуемся Условным форматированием (УФ). Выделим диапазон ячеек, вкладка Главная, группа Стили, пункт меню Условное форматирование, выберем Цветовые шкалы, наконец выберем цветовую шкалу "Белый-красный".

Диапазон ячеек примет следующий вид.

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

Если в диапазоне изменить значения, например добавить значения 17 вместо 14, 15, 16, то эта часть ячеек будет перекрашена.

Если присмотреться, то все ячейки, содержащие 17, будут перекрашены в одинаковый самый яркий цвет, а остальные ячейки, содержащие числа от 0 до 13, останутся без изменений.

Если посмотреть на правило УФ, то мы увидим, что EXCEL использовал цветовую шкалу от белого (минимум) до красного (максимум).

Понятно, что ячейка, содержащая минимальное значение 0, окрашена белым (фактически мы задали этот цвет вручную, выбрав в меню нужную цветовую шкалу), а максимальное 17 - красным (на рисунке выше, расположен справа). Но, что за цвета выбраны для промежуточных значений?

Как было сказано выше, чем значение ближе к максимальному, тем, оно "краснее" и наоборот. Но, как EXCEL вычисляет степень "красноты"?

Чтобы в этом разобраться, нужно вспомнить что любому цвету в компьютере соответствует код. Например, белый цвет в модели RGB имеет код (255, 255, 255), а черный (0, 0, 0). Для тех, кто не знаком со схемами кодировок цветов, далее читать преждевременно. Необходимо предварительно ознакомиться с основами RGB (аббревиатура английских слов red, green, blue — красный, зелёный, синий). RGB - это цветовая модель, описывающая способ кодирования цвета для цветовоспроизведения с помощью трёх цветов, которые принято называть основными.

Двухцветная шкала Белый - Красный

Чтобы двинуться дальше, необходимо отметить, что "красный" цвет в правиле УФ не настоящий красный. Хотя это видно визуально, но можно и проверить.

В правиле УФ выберите "красный" цвет и затем Другие цвета...

Появится диалоговое окно.

Пользователи, знакомые с RGB кодировкой, ожидали для красного цвета увидеть (255, 0, 0), но в окне отображаются другие коды (248, 105, 107). Это сделано для эстетики, т.к. чисто красный цвет достаточной яркий и может травмировать неискушенных пользователей своей однозначностью)).

Если мы выставим в правиле истинный красный, то наш диапазон данных будут перекрашен (правая колонка).

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

Чтобы узнать коды цветов ячеек воспользуемся стандартным инструментом "Цветоподборщик" (color Picker) из PowerToys для Windows (бесплатный, для win11 его можно установить через Microsoft store).

Цветоподборщик возвращает код цвета в шестнадцатеричном формате (на картинке выше выведен код для ячейки содержащей 12), который состоит из 3х составляющих RGB: первые два символа ff соответствуют значению 255 для красного (в десятичном формате). Для всех значений столбца от 0 до 17 значения красной составляющей не меняются. Затем идут значения для зеленого и голубого. Эти значения синхронно меняются от 0 до 255. Почему так?

Т.к. любой цвет в компьютере задается с помощью 3х независимых шкал RGB, то наглядной интерпретацией этого является куб, цвета которого меняются в зависимости от координаты точки в пространстве.

В нашем случае, когда мы задали в правиле УФ 2 точки (белую и красную), то тем самым мы определили прямую, вдоль которой будут лежать все промежуточные. На рисунке выше эта прямая выделена толстой черной стрелкой.

Действительно, это соответствует тем кодам цветов, которые мы определили выше с помощью Цветоподборщика:

• прямая лежит на грани куба, для которой R=255 и не меняется
• зеленая и голубая "координаты" растут синхронно, т.к. прямая - это биссектриса
• конечная точка (White) имеет координаты (255, 255, 255)

Теперь, понимая как EXCEL назначает цвета для промежуточных ячеек, можно вычислить их числовой код:

• цветовая шкала каждой составляющей содержит 256 значений, что соответствует 255 интервалам между ними.
• У нас 18 ячеек (от 0 до 17 включительно), соответственно 17 интервалов.
• 255/17=15 (делится нацело, поэтому мы и взяли диапазон ячеек от 0 до 17)
• Т.е. код цвета (зеленого и голубого) должен меняться на 15 единиц с каждой ячейкой (точнее для каждого целого значения в диапазоне от 0 до 17).

Именно это мы и наблюдаем в столбцах Е и F в файле примера (см. таблицу с кодами на рисунке выше). Можно самостоятельно убедиться, что вычисленные коды идеально совпадают с результатом Цветоподборщика для соответствующих строк.

Для вычисления шестнадцатеричного кода мы использовали встроенную функцию =ДЕС.В.ШЕСТН

Окрашивание фона ячейки по коду цвета (без VBA)

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

К сожалению, в общем случае такую задачу в EXCEL можно решить только с помощью программы VBA (написав макрос, см. ниже). Но, для палитры цветов, лежащих на одной прямой, этот трюк можно провернуть и без программирования.

Например, закрасим ячейку оттенком серого. Известно, что все оттенки серого, включая черный и белый цвет, лежат на главной диагонали куба RGB, начинающейся в (0, 0, 0) и заканчивающейся в точке (255, 255, 255). Причем все значения для 3х цветов будут одинаковыми для каждой точки диагонали.

В предыдущем разделе мы научились вычислять код цвета заливки ячеек диапазона зная числовые значения этого диапазона. Обратная задача - залить ячейку цветом, зная его код, решается (для нашего частного случая) еще проще.

Нужно создать правило УФ с использованием Двухцветной шкалы. В качестве цветов задать черный и белый. Правило нужно применить к 3м ячейкам. В крайние ячейки ввести 0 и 255, соответственно, а в среднюю - код цвета в десятичном формате.

С помощью файла примера и Цветоподборщика можно убедиться, что значение в средней ячейке корректно задает его цвет.

Аналогичные манипуляции можно делать с любыми палитрами цветов, лежащими на прямых. Наиболее наглядными являются палитры лежащие на ребрах куба и диагоналях его граней. Еще 3 варианта подбора заливки по коду цвета приведены в файле примера.

Обратите внимание, что пользователь задает 1 координату (код цвета только для 1й составляющей), что соответствует положению точки на прямой. Остальные коды составляющих цвет вычисляются автоматически.

Окрашивание фона ячейки по коду цвета (с VBA, пользовательская функция)

В файле примера также приведена пользовательская функция (UDF - user defined function), которая позволяет закрасить ячейку определенным цветом. Требуется задать значения 3х составляющих цвета RGB, что аналогично координате в 3х мерном пространстве (см. куб).

Цветовой код получившейся заливки можно проверить через диалоговое окно Формат ячейки.

Трехцветные шкалы

В правила УФ есть еще одна опция с многообещающим названием Трехцветные шкалы.

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

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

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

Пусть это будет точка со значением 5 (среднее значение, к которому мы стремимся), все значения около 5 будут в желтоватой зоне. Значения больше среднего - в красной, а меньше в зеленой. Если диапазон будет содержать много значений около 5, то весь диапазон будет иметь желтый оттенок. Это будет свидетельствовать о небольшой дисперсии (разбросе) значений около среднего.