Мы используем cookie, чтобы сайтом было удобно пользоваться
Условное форматирование (Conditional formatting)
Условное форматирование (Conditional formatting)
— это автоматическое форматирование ячеек при соблюдении заданных вами условий, например:
В Google Таблицах — в меню «Формат» (Format) (Alt+O + F)
В меню условного форматирования Excel следующие опции:
Правила выделения ячеек (Highlight Cells Rules): здесь можно настроить выделение ячеек, содержащих значения больше/меньше определенного уровня (для чисел), содержащих определенный текст, определенные даты или дубликаты.
Правила отбора первых и последних значений (Top/Bottom Rules): здесь правила с относительными условиями, например, для выделения значений выше или ниже среднего, самых больших 10% или 20% наименьших.
Далее следуют три варианта условного форматирования с графическими элементами (гистограммы, цветовые шкалы и наборы значков) — подробнее о них ниже.
Создать правило (New Rule): создание собственного правила с указанием всех параметров, в том числе есть возможность создать условное форматирование с условием, заданным формулами.
Удалить правила (Clear Rules): удаление правил из выделенных ячеек или всего листа.
Управление правилами (Manage Rules): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.
— это автоматическое форматирование ячеек при соблюдении заданных вами условий, например:
- Все ячейки с числами более 10 000 выделяются полужирным шрифтом, и к ним применяется определенный числовой формат.
- Все дубликаты заливаются зеленым цветом.
- К любой текстовой ячейке, содержащей слово «Москва», применяется красный цвет шрифта.
В Google Таблицах — в меню «Формат» (Format) (Alt+O + F)
В меню условного форматирования Excel следующие опции:
Правила выделения ячеек (Highlight Cells Rules): здесь можно настроить выделение ячеек, содержащих значения больше/меньше определенного уровня (для чисел), содержащих определенный текст, определенные даты или дубликаты.
Правила отбора первых и последних значений (Top/Bottom Rules): здесь правила с относительными условиями, например, для выделения значений выше или ниже среднего, самых больших 10% или 20% наименьших.
Далее следуют три варианта условного форматирования с графическими элементами (гистограммы, цветовые шкалы и наборы значков) — подробнее о них ниже.
Создать правило (New Rule): создание собственного правила с указанием всех параметров, в том числе есть возможность создать условное форматирование с условием, заданным формулами.
Удалить правила (Clear Rules): удаление правил из выделенных ячеек или всего листа.
Управление правилами (Manage Rules): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.
«Обычные» правила
Числа
Рассмотрим работу условного форматирования на примере. Наша задача — выделить зеленым все ячейки с продажами более 400.
Рассмотрим работу условного форматирования на примере. Наша задача — выделить зеленым все ячейки с продажами более 400.
Выделяем все ячейки от B2 и до конца столбца (это проще всего сделать сочетанием клавиш Ctrl + Shift + ↓) и вызываем правило условного форматирования «Больше» (Greater Than):
В появившемся окне вводим число и выбираем стиль форматирования тех ячеек, значения в которых будут больше этого числа:
Условное форматирование (и это касается не только правил «Больше», а любых правил вообще) применяется автоматически при изменении данных — то есть, как только в какой-то ячейке мы поменяем значение на число менее 400, она перестанет форматироваться, и наоборот — если ячейка начнет удовлетворять условиям, к ней будет применена зеленая заливка.
Условное форматирование выше по приоритету, чем обычное. То есть даже если вы применили к ячейке, например, желтую заливку, но к ней также применяется условное форматирование и она соответствует заданному условию — то она будет зеленой (или другого цвета/форматирования, заданного именно в условном форматировании).
Условное форматирование выше по приоритету, чем обычное. То есть даже если вы применили к ячейке, например, желтую заливку, но к ней также применяется условное форматирование и она соответствует заданному условию — то она будет зеленой (или другого цвета/форматирования, заданного именно в условном форматировании).
Ссылки на ячейки в правилах условного форматирования
В обычных правилах условного форматирования можно ссылаться на условие, указанное в ячейке, а не в самом правиле.
Обычно условие (например, число, больше которого должны быть значения ячеек, чтобы они форматировались) указывается в самом правиле, например:
Обычно условие (например, число, больше которого должны быть значения ячеек, чтобы они форматировались) указывается в самом правиле, например:
В таком случае все ячейки с числами более 400 будут автоматически форматироваться. Но чтобы изменить само условие (число 400), нужно будет изменять правило условного форматирования:
Главная → Условное форматирование → Управление правилами
Home → Conditional Formatting → Manage Rules
Если же нужна возможность изменить условие форматирования в любой момент в ячейке, не изменяя правило, нужно вместо числа ввести в окне правила ссылку на ячейку, в которой будет храниться это число. А еще проще — щелкнуть на нее мышкой, тогда ссылка сформируется автоматически:
Главная → Условное форматирование → Управление правилами
Home → Conditional Formatting → Manage Rules
Если же нужна возможность изменить условие форматирования в любой момент в ячейке, не изменяя правило, нужно вместо числа ввести в окне правила ссылку на ячейку, в которой будет храниться это число. А еще проще — щелкнуть на нее мышкой, тогда ссылка сформируется автоматически:
Обратите внимание на доллары в ссылке на ячейку E1 в правиле — это «закрепленная» (абсолютная, со знаками доллара) ссылка на ячейку E1. Мы форматируем целый столбец B с продажами, а сравниваем каждое число в нем с одной и той же ячейкой E1. Если бы мы не закрепили ссылку на нее долларами, то с ней сравнивалась бы только первая ячейка диапазона — B2. А уже B3 бы сравнивалась с пустой ячейкой E2, B4 — с E3 и так далее (по аналогии с «протягиванием» формул, при котором все относительные ссылки смещаются).
Теперь при изменении числа в E1 форматирование будет изменяться автоматически, так как именно эта ячейка выступает условием для нашего правила.
Теперь при изменении числа в E1 форматирование будет изменяться автоматически, так как именно эта ячейка выступает условием для нашего правила.
Могут быть ситуации, когда нужна именно относительная ссылка. Например, если вы сравниваете два столбца между собой и хотите, чтобы выделялись числа в столбце B, которые больше чисел в той же строке в столбце C. Например, если в одном столбце остатки, а в другом заказы — и нам нужно выделить красным те остатки, которые меньше резервов.
В таком случае нам нужна относительная ссылка: ведь B2 будет сравниваться с C2, B3 c С3 и так далее — каждый раз это разные ячейки в рамках одной и той же строки.
Поэтому после выделения всего столбца нужно задать условие для первой форматируемой ячейки и сделать его в виде относительной ссылки (мы задаем условие для первой ячейки форматируемого диапазона и представляем, что оно «протягивается» на все остальные, как формула):
В таком случае нам нужна относительная ссылка: ведь B2 будет сравниваться с C2, B3 c С3 и так далее — каждый раз это разные ячейки в рамках одной и той же строки.
Поэтому после выделения всего столбца нужно задать условие для первой форматируемой ячейки и сделать его в виде относительной ссылки (мы задаем условие для первой ячейки форматируемого диапазона и представляем, что оно «протягивается» на все остальные, как формула):
