Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Рассмотрим работу условного форматирования на примере. Наша задача — выделить зеленым все ячейки с продажами более 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 и так далее — каждый раз это разные ячейки в рамках одной и той же строки.