Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Как изменить шаблон книги Excel по умолчанию
Дальше: Проверка данных (Data validation)
Условное форматирование (Conditional formatting)
Файл с примерами: Условное форматирование.xlsxУсловное форматирование (Conditional formatting) — это автоматическое форматирование ячеек при соблюдении заданных вами условий, например:
• все ячейки с числами более 10 000 выделяются полужирным шрифтом, и к ним применяется определенный числовой формат;
• все дубликаты заливаются зеленым цветом;
• к любой текстовой ячейке, содержащей слово «Москва», применяется красный цвет шрифта.
Условное форматирование (Conditional formatting) находится на ленте инструментов на вкладке «Главная» (Home) в Excel.
В Google Таблицах — в меню «Формат» (Format) (Alt + O + F).
В меню условного форматирования Excel — следующие опции.
Правила выделения ячеек (Highlight Cells Rules): здесь можно настроить выделение ячеек, содержащих значения «больше/меньше определенного уровня» (для чисел), определенный текст, определенные даты или дубликаты.
Правила отбора первых и последних значений (Top/Bottom Rules): здесь правила с относительными условиями — например, для выделения значений выше или ниже среднего, лучших или худших значений (допустим, 10% наименьших или 20% наибольших значений).
Далее следуют три варианта условного форматирования с графическими элементами (гистограммы, цветовые шкалы и наборы значков) — подробнее о них ниже.
Создать правило (New Rule): создание собственного правила с указанием всех параметров, в том числе есть возможность создать условное форматирование с условием, заданным формулами.
Удалить правила (Clear Rules): удаление правил из выделенных ячеек или всего листа.
Управление правилами (Manage Rules): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.
«ОБЫЧНЫЕ» ПРАВИЛА
Числа
Рассмотрим работу условного форматирования на примере. Наша задача — выделить зеленым все ячейки с продажами более 400.
Выделяем все ячейки от B2 и до конца столбца (это проще всего сделать сочетанием клавиш Ctrl + Shift + ↓) и вызываем правило условного форматирования «Больше» (Greater Than).
В появившемся окне вводим число и выбираем стиль форматирования тех ячеек, значения в которых будут больше этого числа.
Условное форматирование (и это касается не только правил «Больше», а любых правил вообще) применяется автоматически при изменении данных, то есть как только в какой-то ячейке мы поменяем значение на число менее 400, она перестанет форматироваться, и наоборот, если ячейка начнет удовлетворять условиям, к ней будет применена зеленая заливка.
Условное форматирование выше по приоритету, чем обычное. То есть даже если вы применили к ячейке, например, желтую заливку, но к ней также применяется условное форматирование и она соответствует заданному условию, то она будет зеленой (или другого цвета/форматирования, заданного именно в условном форматировании).
ОТБОР ПЕРВЫХ И ПОСЛЕДНИХ ЗНАЧЕНИЙ
Здесь есть три варианта выделения ячеек: первые и последние значения (по величине), значения, входящие в заданный процент самых больших и самых маленьких, и значения выше/ниже среднего.
Допустим, мы хотим выделить 5 самых больших чисел в диапазоне. Тогда мы выделяем диапазон с числами и выбираем вариант «Первые 10 элементов…» (Top 10 Items). Обратите внимание, что в названиях тут фигурируют 10 элементов или 10%, хотя на самом деле эти значения мы можем задавать сами.
В поле с количеством по умолчанию будет 10, но мы вправе менять это число. В примере мы выделяем зеленым только 5 наибольших элементов.
Если нам нужно выделять какую-то часть (в процентах) самых больших или самых маленьких значений, то нужны правила «Первые 10%» (Top 10%) или «Последние 10%» (Bottom 10%). Допустим, мы хотим выделить красным худшую четверть — 25% самых маленьких значений. Это правило «Последние 10%».
Наконец, если мы хотим выделить все дни выше или ниже среднего значения, используем соответствующие правила «Выше среднего» (Above Average) и «Ниже среднего» (Below Average). У них нет числового параметра — только возможность выбора форматирования.
Текст
Правила для текстовых ячеек есть двух типов: точное соответствие («Равно») и «Текст содержит», то есть вхождение определенного слова / символа / сочетания символов в состав текстовой строки в ячейке.
Так, если в следующей таблице мы хотим выделить все ячейки с конкретной моделью ноутбука в столбце «Товар», то подойдет и правило «Равно» (Equal To), и правило «Текст содержит» (Text that Contains).
А вот если мы хотим выделить цветом все товары бренда Lenovo (то есть ячейки, в которых это слово входит в состав текста), то подойдет только правило «Текст содержит» (Text that Contains).
ССЫЛКИ НА ЯЧЕЙКИ В ПРАВИЛАХ УСЛОВНОГО ФОРМАТИРОВАНИЯ
В обычных правилах условного форматирования можно ссылаться на условие, указанное в ячейке, а не в самом правиле.
Обычно условие (например, число, больше которого должны быть значения ячеек, чтобы они форматировались) указывается в самом правиле, например:
В таком случае все ячейки с числами более 400 будут автоматически форматироваться. Но чтобы изменить само условие (число 400), нужно будет изменять правило условного форматирования:
Главная → Условное форматирование → Управление правилами (Home → Conditional Formatting → Manage Rules).
Если же нужна возможность изменить условие форматирования в любой момент в ячейке, не изменяя правило, нужно вместо числа ввести в окне правила ссылку на ячейку, в которой будет храниться это число. А еще проще — щелкнуть на нее мышкой, тогда ссылка сформируется автоматически.
Обратите внимание на доллары в ссылке на ячейку E1 в правиле — это «закрепленная» (абсолютная, со знаками доллара) ссылка на ячейку E1. Мы форматируем целый столбец B с продажами, а сравниваем каждое число в нем с одной и той же ячейкой E1. Если бы мы не закрепили ссылку на нее долларами, то с ней сравнивалась бы только первая ячейка диапазона — B2. А уже B3 бы сравнивалась с пустой ячейкой E2, B4 — с E3 и так далее (по аналогии с протягиванием формул, при котором все относительные ссылки смещаются).
Теперь при изменении числа в E1 форматирование будет изменяться автоматически, так как именно эта ячейка выступает условием для нашего правила.
Могут быть ситуации, когда нужна именно относительная ссылка. Например, если вы сравниваете два столбца друг с другом и хотите, чтобы выделялись числа в столбце B, которые больше чисел в той же строке в столбце C. Например, если в одном столбце остатки, а в другом заказы и нам нужно выделить красным те остатки, которые меньше резервов.
В таком случае нам нужна относительная ссылка, ведь B2 будет сравниваться с C2, B3 c С3 и так далее — каждый раз это разные ячейки в рамках одной и той же строки.
Поэтому после выделения всего столбца нужно задать условие для первой форматируемой ячейки и сделать его в виде относительной ссылки (мы задаем условие для первой ячейки форматируемого диапазона и представляем, что оно протягивается на все остальные, как формула).
ГИСТОГРАММЫ, ЦВЕТОВЫЕ ШКАЛЫ И ЗНАЧКИ
В условном форматировании есть три опции для визуализации данных с помощью графических элементов: гистограммы (Data Bars), цветовые шкалы (Color Scales) и значки (Icon Sets). Причем их можно применять как к обычным ячейкам с данными, так и к полям сводных.
В Google Таблицах графических элементов (гистограмм и значков) в условном форматировании нет — только цветовые шкалы. Значки можно вставлять с помощью текстовых функций или через функцию IMAGE (если у вас есть ссылка на значок или эмодзи). А гистограмму можно построить с помощью функции SPARKLINE (горизонтальная гистограмма в этой функции имеет тип bar).
Гистограммы (Data bars)
Гистограммы — это маленькие графики (горизонтальные линии, или, как они называются в Excel, столбики) в ячейках. Их ширина зависит от значения в ячейке, а максимальная ширина (во всю ячейку) определяется максимальным значением в диапазоне.
Есть двенадцать готовых вариантов гистограмм — со сплошным цветом и полупрозрачных.
Если в диапазоне есть отрицательные числа, автоматически появится ось и отрицательные значения будут красного цвета, хотя этот параметр можно тоже поменять в настройках.
Если зайти в настройки правила, будет возможность выбрать цвет столбца, цвет границы, включить отображение только гистограмм без данных. В настройки можно попасть:
— если сразу выбрать в списке наборов значков не готовый вариант, а «Другие правила» (More Rules), тогда вы попадете в диалоговое окно с настройками;
— через «Управление правилами» (Manage Rules) в «Условном форматировании».
Например, так будет выглядеть гистограмма, если активировать опцию «Показывать только столбец» (Show Bar Only) в настройках.
Цветовые шкалы (Color Scales)
Цветовые шкалы позволяют залить ячейки разными цветами (изменить цвет фона) в зависимости от их значений. В цветовых шкалах задаются «пограничные» цвета: для минимальных и максимальных значений (а также средних значений, хотя можно задать и только два цвета) форматируемого диапазона. Ячейки будут окрашиваться в соответствующие оттенки в зависимости от того, к чему ближе числа в этих ячейках.
Есть встроенные двух- и трехцветные шкалы, которые подойдут для многих случаев.
Есть возможность настраивать свои шкалы, для этого нужно выбрать вариант «Другие правила» (More Rules). В диалоговом окне выбираем нужные цвета и в образце видим, как будет выглядеть фон ячеек.
Наборы значков (Icon Sets)
Наборы значков состоят из трех, четырех или пяти значков, и, как и в случае со шкалами, можно выбирать из готовых наборов или формировать свои.
По умолчанию значки отображаются следующим образом: числа в форматируемом диапазоне разбиваются на равные части (три, четыре или пять), и для каждой части применяется свой значок.
Если их три, то для каждой трети (меньшая треть, средняя и наибольшая).
Если четыре, то для каждой из четырех равных частей.
Если пять, то, соответственно, для каждой из пяти.
Но настройки можно изменять — см. .
Превращаем цифры (оценки) в значки
Значки хорошо подходят для оценок, например от 1 до 5.
В таком случае нужно выбрать набор значков с количеством, соответствующим числу оценок, — и каждой будет соответствовать свой.
Если включить опцию «Показывать только значок» в настройках правила, то значения в ячейках отображаться не будут — только сами значки.
Настройка графических объектов условного форматирования
Гистограммы, значки и цветовые шкалы можно настраивать гибко, не ограничиваясь стандартными правилами. По умолчанию происходит следующее.
• В случае с цветовой шкалой все числа в диапазоне сортируются: для минимального применяется один «крайний» цвет шкалы, для максимального — другой, для среднего (если в шкале три цвета) — «средний» цвет, а для остальных — в зависимости от положения числа на спектре всех значений в диапазоне.
• В случае с иконками все числа в диапазоне будут разделены на три, четыре или пять (смотря сколько иконок в выбранном наборе) равных частей и для каждой из них будет применяться значок из набора.
• В случае с гистограммой к максимальному числу в диапазоне применяется гистограмма шириной во весь столбец, а к остальным — шириной относительно максимального значения.
Если вам нужно что-то другое, заходите в настройки правила или сразу выбирайте вариант «Другие правила» (More Rules).
Для шкал можно выбирать варианты с двумя или тремя цветами, задавать любые цвета и менять то, что выступает минимальным, средним и максимальным значением.
Например, можно задать точные числа (а не минимум/максимум, как по умолчанию) в качестве «пограничных».
Тогда для всех чисел меньше минимума и больше максимума будут применяться одни и те же цвета уже без градиента.
Обратите внимание, что есть тип «Процент» (Percent) и «Процентиль» (Percentile), — так, по умолчанию у трехцветных шкал «средний» цвет для процентиля равен 50. Это медиана — значение, которое находится в середине набора чисел (если его упорядочить).
То есть процент — про значения, а процентиль — про положение в упорядоченном наборе чисел.
Разница хорошо видна в случае с диапазонами, где одно число сильно выбивается из ряда: в варианте с процентом просто нет числа, которое бы заливалось желтым.
В случае со значками можно убрать какие-то значки из набора или взять для каких-то случаев отдельный значок из другого набора.
В следующем примере мы ориентируемся не на проценты, а на конкретные числа (для этого нужно поменять «Тип» на «Число»): числа больше 40 000 — галочка, числа от 25 000 до 40 000 — без значка (значок выбирается из списка, можно комбинировать значки из разных наборов или, как в этом случае, вовсе убрать значок для определенных условий), числа до 25 000 — крестик.
Если мы хотим, чтобы гистограмма была шириной на весь столбец для какого-то фиксированного значения, а не уже имеющегося в диапазоне максимального значения, то можно поменять это значение.
