Office

Условное форматирование в Excel: правила, формулы, примеры

Условное форматирование в Excel автоматически меняет вид ячейки - заливку, цвет шрифта, рамку - когда значение в ней отвечает заданному условию. Включается оно на вкладке "Главная" кнопкой "Условное форматирование", а правила можно задавать как готовыми шаблонами, так и собственной формулой.

Условное форматирование в Excel: правила, формулы, примеры
Фото: Brews ohare, Public domain, Wikimedia Commons
Содержание
  1. Что такое условное форматирование и когда оно нужно
  2. Как сделать условное форматирование: порядок действий
  3. Готовые правила выделения ячеек
  4. Первые и последние значения, гистограммы, цветовые шкалы и значки
  5. Условное форматирование по формуле
  6. Условное форматирование в сводной таблице
  7. Как управлять, копировать и удалить правила
  8. Частые ошибки и как их исправить

Что такое условное форматирование и когда оно нужно

Обычное форматирование вы назначаете вручную: закрасили ячейку - она так и останется. Условное работает иначе. Excel сам проверяет значения и применяет оформление только к тем ячейкам, которые подходят под правило. Изменили число - цвет пересчитался.

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

Условное форматирование можно применять к диапазону, к целым столбцам и строкам, к умной таблице, а в Excel для Windows и к сводной таблице. Правил на одном диапазоне может быть сколько угодно, они работают одновременно.

Как сделать условное форматирование: порядок действий

  1. Выделите ячейки, к которым нужно применить правило, например столбец с суммами.
  2. Откройте вкладку "Главная" и нажмите кнопку "Условное форматирование" в группе "Стили".
  3. Выберите тип правила: "Правила выделения ячеек", "Правила отбора первых и последних значений", "Гистограммы", "Цветовые шкалы" или "Наборы значков".
  4. Задайте условие и выберите оформление в открывшемся окне, например "Больше..." и значение 50000.
  5. Нажмите "ОК". Ячейки, подходящие под условие, сразу изменят вид.
Макет меню условного форматирования Excel со списком правил
Меню условного форматирования на вкладке Главная

Перед тем как нажать "ОК", посмотрите на предварительный просмотр: Excel показывает результат прямо на листе. Если оформление не нравится, в выпадающем списке справа есть варианты "Пользовательский формат...", где можно выбрать любую заливку, шрифт и границы.

Готовые правила выделения ячеек

Раздел "Правила выделения ячеек" содержит самые востребованные условия. Их хватает для большинства таблиц, и формулы для них писать не нужно.

ПравилоЧто делаетПример
Больше / МеньшеСравнивает с числом или ячейкойВыручка меньше плана
МеждуЗначения в заданном диапазонеОценки от 3 до 4
РавноТочное совпадение числа или текстаСтатус равен "Оплачено"
Текст содержитЧасть слова в ячейкеАдреса с "Москва"
ДатаВчера, на этой неделе, в прошлом месяцеСроки на текущей неделе
Повторяющиеся значенияДубли или уникальныеОдинаковые артикулы

В поле значения можно указать не число, а адрес ячейки, например $F$1. Тогда порог хранится в одном месте, и чтобы поменять условие для всей таблицы, достаточно исправить одну ячейку.

Первые и последние значения, гистограммы, цветовые шкалы и значки

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

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

Если значки или шкалы ведут себя не так, как хотелось, откройте "Условное форматирование" - "Управление правилами", выберите правило, нажмите "Изменить правило" и задайте границы вручную: в процентах, числах или процентилях.

Условное форматирование по формуле

Когда готовых правил мало, выберите "Условное форматирование" - "Создать правило" - "Использовать формулу для определения форматируемых ячеек". Формула должна возвращать ИСТИНА или ЛОЖЬ. Для ячеек, где результат ИСТИНА, применится выбранный формат.

Главное правило: формулу пишут для левой верхней ячейки выделенного диапазона, а ссылки делают смешанными. Чтобы закрасить всю строку, закрепите столбец знаком доллара перед буквой.

ЗадачаФормула (диапазон A2:E100)
Строка целиком, если в столбце E слово "Просрочено"=$E2="Просрочено"
Срок в столбце D уже прошёл=$D2<СЕГОДНЯ()
Чётные строки (зебра)=ОСТАТ(СТРОКА();2)=0
Дубликаты в столбце A=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Пустая ячейка в обязательном столбце B=ЕПУСТО($B2)

Функции СЧЁТЕСЛИ и ЕПУСТО подробно разобраны в отдельных материалах. Если нужна формула с условием для самой таблицы, а не для оформления, соберите её в конструкторе формул Excel.

Макет диалога создания правила условного форматирования с формулой
Правило с формулой закрашивает строку целиком

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

Открыть инструмент отдельной страницей

Условное форматирование в сводной таблице

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

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

Как управлять, копировать и удалить правила

Все правила листа собраны в окне "Условное форматирование" - "Управление правилами". В списке "Показать правила форматирования для" выберите "Этот лист", и вы увидите все условия, порядок их выполнения и диапазоны. Правила можно изменять, удалять, а стрелками менять приоритет: верхнее выполняется первым.

  • Чтобы скопировать правило, выделите ячейку с ним, нажмите "Формат по образцу" на вкладке "Главная" и закрасьте нужный диапазон.
  • Чтобы убрать условное форматирование с диапазона, выделите его и выберите "Условное форматирование" - "Удалить правила" - "Удалить правила из выделенных ячеек".
  • Чтобы очистить весь лист, выберите "Удалить правила со всего листа".
  • Чтобы временно убрать оформление, удалите правило или перенесите его: флажок "Остановить, если истинно" лишь прекращает проверку правил ниже и само правило не отключает.

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

Частые ошибки и как их исправить

Правило не срабатывает на числах, записанных как текст: ячейка с "100" слева, зелёный уголок в углу. Преобразуйте такие данные в числа, иначе сравнение "больше 50" даст неверный результат.

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

Если после удаления данных остались цветные пустые ячейки, правило применено ко всему столбцу. Ограничьте диапазон в поле "Применяется к", например $A$2:$E$500. Это же ускорит работу большого файла: формулы на целых столбцах заставляют Excel пересчитывать миллионы ячеек.

Для печати на чёрно-белом принтере цветовые шкалы теряют смысл. В таких случаях используйте жирный шрифт, рамки или значки вместо заливки.

Частые вопросы

Где находится условное форматирование в Excel?

На вкладке "Главная", в группе "Стили", кнопка "Условное форматирование". Если окно Excel узкое, группа может быть свёрнута в одну кнопку "Стили", тогда нажмите на неё.

Как закрасить всю строку по значению одной ячейки?

Выделите всю таблицу, создайте правило с формулой, например =$E2="Просрочено", и выберите заливку. Знак доллара перед буквой столбца фиксирует столбец, а номер строки остаётся относительным.

Как убрать условное форматирование в Excel?

Выделите нужные ячейки, откройте "Условное форматирование" - "Удалить правила" и выберите удаление из выделенных ячеек или со всего листа. Обычная заливка, назначенная вручную, при этом останется.

Как скопировать условное форматирование в другие ячейки?

Выделите ячейку с правилом, нажмите "Формат по образцу" и протяните по диапазону. Другой путь - изменить поле "Применяется к" в менеджере правил.

Работает ли условное форматирование в сводной таблице?

Да, в Excel для Windows. Правило привязывается к полю, поэтому не пропадает при обновлении данных и перестановке полей сводной таблицы.

Нашли ошибку или устаревший пункт меню? Напишите на admin@programistik.top с адресом страницы. Перед правкой реестра, системных файлов и разделов диска делайте резервную копию.

Читайте по теме