Скотт Хартшорн. Условное форматирование

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

Scott Hartshorn. Excel Conditional Formatting: Tips You Can Use Immediately To Make Your Data Stand Out

Скачать заметку в формате Word или pdf, примеры в формате Excel

Условное форматирование расположено на ленте Excel в меню ГЛАВНАЯ –> Стили. При нажатии на условное форматирование появится выпадающее меню с опциями (рис. 1). Это меню можно также вызвать нажатием клавиш Alt+Я+Л.

Рис. 1. Меню Условное форматирование

Цветовые шкалы

Цветовые шкалы создают заливку ячеек в зависимости от значения в ячейках. Можно использовать 2- или 3-цветные шкалы. Цвета непрерывно переходят один в другой. На рис. 2 показаны вероятности выигрыша в Техасском Холдеме для стартовой руки, образованной картами на пересечении заголовков строки и столбца, против случайной руки. Максимальным значениям назначен зеленый цвет, средним – желтый, минимальным – красный. Чтобы узнать, насколько хорошо стартовая рука, сопоставьте значение одной карты в строке со значением другой карты в столбце. Одномастные руки расположены выше диагонали, разномастные – ниже. Например, в ячейке F6 представлена вероятность (62,6%) выигрыша одномастных КВ против случайной руки.

Рис. 2. Техасский Холдем: стартовые руки против случайной руки

Окраска дает понять, какие руки лучше, а какие не так хороши. Без условного форматирования (рис. 3) таблица читается существенно сложнее.

Рис. 3. Исходная таблица без условного форматирования

Чтобы задать условное форматирование, выберите все ячейки с числовыми значениями (диапазон С5:О17), перейдите в меню Условное форматирование, выберите опцию Цветовые шкалы и в появившемся окне выберите верхнюю левую цветовую шкалу (рис. 4). Как только вы наведете мышь на эту опцию, Excel отразит на листе, как будет выглядеть выбранное условное форматирование. В нашем примере мы выбрали шкалу Зеленый-желтый-красный. По умолчанию Excel присваивает зеленый цвет ячейке с наибольшим значением в диапазоне (С5). Ячейка с наименьшим значением окрашивается в красный цвет (N17). В желтый цвет окрашивается ячейка, соответствующая 50-ому процентилю.

Рис. 4. Выбор трехцветной шкалы условного форматирования

В основной палитре Excel предлагает шесть трехцветных шкал (выделены красными линиями на рис. 5) и шесть двухцветных шкал (оставшиеся не выделенными). На рис. 5 выбрана палитра Красный-белый-синий (последняя среди трехцветных). Еще больше возможностей предоставляется, если кликнуть на Другие правила. В этом случае откроется окно Создание правила форматирования (рис. 6). Это же окно можно вызвать, если пропустить боковое меню Цветовые шкалы, а в выпадающем меню Условное форматирование сразу выбрать Создать правило… –> Форматировать все ячейки на основании их значений (рис. 6).

Рис. 5. Иные варианты цветовых шкал

Рис. 6. Меню Создание правила форматирования

Вы можете выбрать 2- или 3-цветную шкалу (см. рис. 6 и рис. 7). Гистограмма и Набор значков будут рассмотрены позже.

Рис. 7. Выбор 2- или 3-цветной шкалы

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

Рис. 8. Индивидуальные настройки

Опция Формула позволяет ссылаться на значение в ячейке, которое, в свою очередь, может быть введено на листе Excel, как константа, или вычисляться на основе формулы (рис. 9). Ссылка в окне Формула должна быть абсолютной. В противном случае Excel выдаст ошибку и предложит повторить попытку (рис. 10).

Рис. 9. Минимальное значение установлено с помощью формулы – ссылки на ячейку

Рис. 10. Ошибка из-за того, что ссылка в окне Формула относительная

Если руки с вероятностью выигрыша менее 0,6 становятся не приемлемыми для вступления в игру, большинство ячеек приобретает красные оттенки (рис. 11). Сравни с рис. 2.

Рис. 11. Техасский Холдем: стартовые руки с вероятностью выигрыша более 60%

Чтобы избавиться от форматирования…

…можно воспользоваться одним из трех вариантов. Пройдите по меню Условное форматирование –> Удалить правила (рис. 12). Можно удалить правила только из выбранных ячеек или из всего листа.

Рис. 12. Удаление правил

Кликните на опцию Управление правилами… (нижняя строчка меню Условное форматирование, см. рис. 12).  Откроется окно Диспетчер правил условного форматирования (рис. 13). Сначала выделите правило, а затем кликните Удалить правило.

Рис. 13. Управление правилами

Используйте меню ГЛАВНАЯ –> Буфер обмена –> Формат по образцу (рис. 14). Выделите ячейки, в которых нет форматирования и примените их формат к ячейкам, содержащим условное форматирование.

Рис. 14. Формат по образцу

Управление правилами

Это окно позволяет просмотреть все правила для выделенного диапазона или всего листа. Здесь можно: добавить новое правило, изменить существующие правила, удалить правило, изменить диапазон, к которому применяется правило, изменить порядок применения правил. Если к одной и той же ячейке применяется несколько условных форматов, Excel сначала применит первое (верхнее) правило, а затем все последующие правила, если они не конфликтуют с более приоритетными форматами. Всякий раз, когда вы создаете новое правило, оно размещается первым (вверху списка). Вы можете изменить порядок применения (расположения в окне) правил, используя стрелки (рис. 15).

Рис. 15. Изменения порядка использования правил

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

Рис. 16. Цветовая шкала и гистограмма, примененные одновременно

Гистограммы внутри ячеек

Применить условное форматирование в виде гистограммы можно двумя способами. Первый – пройти по меню Условное форматирование –> Гистограмма. Второй – пройти по меню Условное форматирование –> Создать правило… В открывшемся окне Создание правила форматирования (см. рис. 6) выберите Форматировать все ячейки на основании их значений. А затем в строке Стиль формата выберите Гистограмма (см. рис. 7).

В следующем примере (рис. 17) приведены имена студентов и их рейтинг… слева без условного форматирования, справа – с гистограммами. Величина столбика – это отношение значения в ячейке к наибольшему значению во всех ячейках того же столбца. Самое большое значение рейтинга у Саманты – 0,952, поэтому ее гистограмма заполняет всю ячейку.

Рис. 17. Рейтинг студентов; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Если отсортировать список не по алфавиту (как было первоначально), а по значениям рейтинга (от большего к меньшему), картина станет еще более наглядной (рис. 18).

Рис. 18. Отсортированный рейтинг студентов

Если пройти по меню Условное форматирование –> Управление правилами… и в открывшемся окне Диспетчер правил условного форматирования выбрать наше правило и кликнуть Изменить правило… откроется окно Изменение правил форматирования, в котором вы получите доступ к разнообразным настройкам (рис. 19):

1. Показывать только столбец – будут показаны столбики гистограммы, а числа будут скрыты
2. Настройка минимального / максимального значения
3. Изменение цвета фона / границы гистограммы
4. Настройка способа отображения отрицательных чисел (откроется еще одно окно)
5. Изменение направления столбцов: по контексту, справа налево, слева направо

Рис. 19. Тонкие настройки гистограммы

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

Набор значков

По сути, значки работают так же, как цветные шкалы, за исключением того, что вместо затенения ячеек они помещают значок внутрь ячейки. Сравните цветные полосы со значками тех же цветов (рис. 20). К тонким настройкам вы можете добраться с помощью окна Изменение правил форматирования как описано в предыдущем разделе.

Рис. 20. Форматирование с помощью значков и цветных шкал

Выделение определенных ячеек

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

Рис. 21. Правила выделения ячеек

В следующем примере (рис. 22) с помощью формулы =СЛУЧМЕЖДУ(1;6) я смоделировал 20 бросков 6-гранной кости. С помощью опции Правила выделения ячеек –> Равно я задал значение 6, которые подлежат выделению светло-красной заливкой и темно-красным текстом. Задайте указанное форматирование, а затем понажимайте F9. Функция СЛУЧМЕЖДУ() является волатильной, т.е. будет пересчитываться при каждом нажатии. Условное форматирование будет меняться синхронно.

Рис. 22. Выделены только 6-ки

Если выбрать опцию Дата (см. рис. 21), вы получите доступ к условному форматированию на основе дат (рис. 23), которое редко используется, но весьма наглядно. Также можно выделить повторяющиеся или уникальные значения (опция Повторяющиеся значения… на рис. 21) или ячейки, содержащие определенный текст.

Рис. 23. Условное форматирование на основе даты

Если вы не хотите выделять конкретное значение, но хотите выделить значение, на основе его ранга (расположения в отсортированном массиве данных), вы можете использовать опцию Правила отбора первых и последних значений (рис. 24). Естественно, все опции, указанные в этом разделе, также могут быть выбраны с помощью меню Создать правило…

Рис. 24. Правила отбора ячеек в зависимости от их ранга

Настройка формата

В окнах Создание/Изменение правила форматирования вы можете кликнуть на кнопке Формат, и получить доступ к набору опций, такому же, как и при обычном форматировании ячеек (рис. 25).

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

В следующем примере (рис. 26) с помощью условного форматирования будет задан цвет и шрифт для роста президентов США в футах: если рост 6 футов или более, то сделать шрифт полужирным, а фон ячейки светло-коричневым.

Рис. 26. Рост президентов 6 и более футов выделен п/ж шрифтом и заливкой ячеек

Использование формул

Какие ячейки форматировать, можно задать с помощью формул. Введите формулу для первой ячейки из диапазона (рис. 27). Далее формула будет автоматически применяться к другим ячейкам. Для этого примера я сделал еще одну колонку, где указал рост президентов в сантиметрах. Я хочу выделить зеленым цветом рост президента, который был выше, чем его предшественник. Для Джона Адамса нужно ввести формулу =Е3>Е2.

Рис. 27. Использование формул для отбора ячеек, подлежащих условному форматированию

Вот что получилось:

Рис. 28. Зеленым цветом выделен рост президента в сантиметрах, если он больше, чем у его предшественника

Как же работает формула =Е3>Е2? Для Джона Адамса и Джорджа Вашингтона формула верна. Но что происходит для иных ячеек? Поскольку формула содержит только относительные ссылки в ячейке Е4 условное форматирование будет проверять истинность формулы =Е4>Е3. И так далее. Фактически формула для каждой ячейки в столбце Е проверяет, больше ли значение предыдущего (над ним) или нет.

А вот формула =$Е$3>$Е$2 будет сравнивать Е3 и Е2 во всех ячейках столбца Е, поэтому ответ будет неверным. Формула =Е3>Е$2 будет сравнивать рост текущего президента с ростом Джорджа Вашингтона, и выделит цветом всех президентов, чей рост больше, чем у Вашингтона (рис. 29).

Рис. 29. Зеленым цветом выделен рост президента в сантиметрах, если он больше, чем Джорджа Вашингтона

Если бы мы применяли подобное форматирование к нескольким столбцам (например, к диапазону С2:Е45), чтобы не смешивать мух с котлетами, нам следовало бы использовать формулу =$E3>$E2. Она не позволит сравнивать значения из разных столбцов.

Неочевидные нюансы использования формул

Правило 1. Формула всегда должна начинаться со знака равно (иначе, это не формула).

Правило 2. Форматируются ячейки, для которых формула возвращает истинное значение. Т.е., формула неявно использует функцию ЕСЛИ. Если формула возвращает ИСТИНА – форматируем. Если, ЛОЖЬ – не форматируем. Поэтому, после знака равно должна идти формула, истинная для некоторых ячеек и ложная для других. Например, формула =5*8 возвращает значение 40, которое не зависит от содержания ячейки. Excel трактует любые числовые значения, кроме нуля, а также любой текст, как ИСТИНА, а ноль и значения ошибок, как ЛОЖЬ. Поэтому условное форматирование на основе формулы =5*8 отформатирует все ячейки диапазона.

Правило 3. Поскольку формула проверяет истинность или ложность, она может содержать знак равно внутри себя. Например, вполне допустима формула =Е3=Е2. Первый знак равенства говорит о том, что это формула. Второй знак равенства – условие проверки Е3=Е2. Сравните с обычной формулой в ячейке: =ЕСЛИ(Е3=Е2;…;…). Формула =Е3=Е2 отформатирует ячейки, значение которых равно вышележащим, и не отформатирует, если не равно.

Правило 4. Аккуратно используйте абсолютные, относительные и смешанные ссылки. Например, для выделения максимального значения в столбце, используйте формулу: =B2=МАКС(B$2:B$13), см. рис. 30. А для выделения максимального значения в строке: =B2=МАКС($B2:$D2), рис. 31.

Рис. 30. Выделить максимальное значение в столбце

Рис. 31. Выделить максимальное значение в строке

Правило 5. В формуле допускаются ссылки на ячейки с других листов.