Условное форматирование в Excel: правила, формулы и примеры
By Mirko Peters · · Updated
Условное форматирование в Excel окрашивает ячейки по правилу. Разберём правила «Выделить ячейки», «Первые и последние значения», гистограммы, цветовые шкалы, наборы значков, формулы, порядок правил и их очистку.
Automatically translated with AI from the original post. Read the English original
Условное форматирование в Excel меняет внешний вид ячейки (цвет заливки, шрифт или значок) в зависимости от её содержимого. Вы выделяете ячейки, переходите на вкладку Главная, выбираете Условное форматирование и задаёте правило, например Больше или Повторяющиеся значения. Когда данные меняются, форматирование меняется вместе с ними. Эта статья предназначена для пользователей Excel, которые создают отчёты, трекеры или списки и хотят, чтобы просроченные даты, дубликаты или лучшие результаты выделялись без ручной проверки каждой строки. Вы узнаете о встроенных типах правил, как записать правило с формулой, как работает порядок правил, а также как очистить правила и устранить неполадки.
Ключевые выводы
- Выделите ячейки и выберите «Условное форматирование» на вкладке «Главная». Можно также использовать кнопку «Экспресс-анализ» или сочетание клавиш Ctrl+Q.
- Правила выделения ячеек и правила отбора первых и последних значений покрывают большинство типичных задач: «Больше», «Между», «Текст содержит», «Повторяющиеся значения», «Выше среднего».
- Для собственной логики выберите «Создать правило», а затем «Использовать формулу для определения форматируемых ячеек». Формула должна начинаться со знака = и возвращать ИСТИНА или ЛОЖЬ.
- Правила применяются сверху вниз в окне «Управление правилами». Если два правила конфликтуют, побеждает правило, стоящее выше, а параметр «Остановить, если истина» прекращает дальнейшую проверку.
- Условное форматирование не может ссылаться на другие книги, а текстовые критерии ограничены 255 символами.
Основной источник большинства сведений в этой статье — страница Microsoft о том, как использовать условное форматирование для выделения информации в Excel. Если я добавляю что-то, чего нет на этой странице, я об этом говорю.
Как применить условное форматирование в Excel
Microsoft сообщает, что условное форматирование можно применить к выделенному диапазону, именованному диапазону или таблице Excel. В Excel для Windows оно также работает с отчётами сводных таблиц. Есть два способа начать.
Через ленту.
- Выделите ячейки.
- На вкладке Главная выберите Условное форматирование.
- Наведите указатель на тип правила, выберите правило и следуйте открывшемуся диалогу. Для большинства правил нужно указать значение или текст и формат.
Через «Экспресс-анализ».
- Выделите данные. В правом нижнем углу выделения появится небольшая кнопка «Экспресс-анализ».
- Нажмите кнопку или нажмите Ctrl+Q.
- На вкладке Форматирование наведите указатель на вариант, чтобы увидеть предпросмотр, и затем щёлкните его. Если открылось окно Текст содержит, введите текст и нажмите ОК.
Варианты на этой вкладке зависят от данных. Для выделения, содержащего только текст, предлагаются такие варианты, как «Текст», «Дубликат», «Уникальный», «Равно» и «Очистить». Для числового выделения предлагаются «Гистограммы», «Цвета», «Наборы значков», «Больше», «Первые 10%» и «Очистить». Это самый быстрый способ оценить внешний вид, прежде чем применять его.
Меню на вкладке «Главная» — это и есть главный инструмент, и остальная часть статьи следует его структуре: Правила выделения ячеек, Правила отбора первых и последних значений, Гистограммы, Цветовые шкалы, Наборы значков, Создать правило, Управление правилами и Удалить правила. Если вам нужна справка о самой функции, см. страницу условное форматирование.
Выделение ячеек простыми правилами
Правила выделения ячеек — это быстрые правила. Microsoft перечисляет такие правила, как «Между», «Равно», «Текст содержит», «Дата наступает» и «Повторяющиеся значения» («Больше» и «Меньше» находятся в том же меню). Каждое из них задаёт простой вопрос о ячейке и выделяет ячейки, для которых ответ «да».
Типичные случаи применения:
- Больше для столбца продаж, чтобы выделить всё, что превышает целевое значение.
- Между для отображения значений в допустимом диапазоне; тот же правило с обратным условием, если нужны выбросы.
- Равно для точного значения, например кода статуса или оценки 100.
- Текст содержит для выделения каждой ячейки со словом вроде «поздно» или названием продукта. Частичные совпадения тоже учитываются, поскольку правило ищет текст в любом месте ячейки.
- Дата наступает для выделения дат, попадающих в выбранный период. В диалоговом окне Excel предлагает готовые варианты, поэтому выберите один из них, а не вводите дату вручную.
- Повторяющиеся значения для выделения повторов в списке. Это самая быстрая проверка перед созданием сводной таблицы или поиска.
Правила отбора первых и последних значений работают с ранжированием: Первые 10 элементов, Последние 10 %, Выше среднего и Ниже среднего. Ограничения Microsoft для них: число для первых или последних значений — от 1 до 1000, или от 1 до 100, если используются проценты; процентили — от 0 до 100 (число вводится без знака процента).
Небольшой пример поясняет это. Допустим, в столбце C находятся значения заказов. Выделите C2 до последней строки, выберите Правила выделения ячеек > Больше, введите целевое значение и выберите зелёную заливку. Добавьте правило Меньше с минимальным значением и оранжевой заливкой для тех же ячеек. Теперь самые сильные и слабые заказы выделяются, а средние остаются без формата. Измените число, и цвета изменятся.
Гистограммы, цветовые шкалы и наборы значков
Эти три инструмента показывают, как значения соотносятся между собой в диапазоне, а не то, проходят ли они проверку. Так их описывают страницы Microsoft:
- Гистограммы помещают полосу в каждую ячейку. Чем длиннее полоса, тем больше значение. Можно выбрать градиентную или сплошную заливку, показать границы, отображать только полосы и настраивать формат отрицательных значений.
- Цветовые шкалы используют два или три цвета. В двухцветной шкале один цвет обозначает высокие значения, а другой — низкие. Трёхцветная шкала добавляет средний цвет.
- Наборы значков используют от трёх до пяти категорий, и каждый значок соответствует диапазону значений. Можно изменить порядок значков, показать только значки или скрыть значок для отдельных ячеек с помощью Нет значка ячейки.
Порядок действий для всех трёх одинаков: выделите ячейки, перейдите на вкладку Главная > Условное форматирование, наведите указатель на тип и щёлкните стиль. На странице Microsoft о гистограммах, цветовых шкалах и наборах значков есть полезный совет: если расширить столбец с гистограммами, различия между значениями станет легче увидеть. Наборы значков можно сочетать с другими условными форматами.
Используйте их аккуратно. Столбец, полный трёхцветных шкал поверх заливок и значков, трудно читать. Хорошая привычка — один сигнал на столбец. Кроме того, цвет не должен быть единственным носителем смысла, ведь некоторые читатели не различают красный и зелёный. Здесь помогают значки или текстовый столбец, и я бы добавил один из них в любой отчёт, который уходит широкой аудитории.
Что касается ограничений цветовых шкал, Microsoft указывает, что минимальное значение должно быть меньше максимального, а середина по умолчанию составляет 50 процентов.
Создание собственного правила с помощью формулы
Когда встроенных правил недостаточно, используйте Создать правило. Откроется диалоговое окно Создание правила форматирования, в котором выберите Использовать формулу для определения форматируемых ячеек. Правило Microsoft: формула должна начинаться с = и возвращать ИСТИНА или ЛОЖЬ. Ячейки, для которых формула возвращает ИСТИНА, получают формат. Сначала проверьте формулу на ошибки, а если ошибки возможны, используйте функции ЕОШ или ЕСЛИОШИБКА.
Напишите формулу для первой ячейки выделенного диапазона. Если диапазон начинается с B2, пишите формулу так, будто вы смотрите на B2, а Excel применит ту же логику к остальным ячейкам. Здесь легко ошибиться, и знак доллара имеет значение: $B2 фиксирует столбец и позволяет строке меняться, поэтому так форматируют всю строку по значению одного столбца.
Примеры Microsoft показывают, что возможно:
- Истекающие сроки.
=B2<TODAY()для красной заливки просроченных пропусков и=B2<TODAY()+60для жёлтой заливки пропусков, срок которых истекает в течение 60 дней. Красное правило должно стоять в списке выше жёлтого, иначе жёлтое правило тоже будет срабатывать на просроченные. - Чередование строк.
=MOD(ROW(),2)=1закрашивает каждую нечётную строку. - Дубликаты.
=COUNTIF($A$2:$A$400,A2)>1отмечает дубликаты в диапазоне A2:A400. - Два условия. В примере Microsoft с бюджетом используются
ANDиOR, чтобы окрасить результат в зелёный, когда первоначальный взнос и ежемесячный платёж укладываются в бюджет, и в красный, когда не укладывается хотя бы один из них.
Обратите внимание на структуру примера с COUNTIF: диапазон абсолютный ($A$2:$A$400), а проверяемая ячейка относительная (A2). Так выглядит большинство полезных формул в правилах.
Если правило не работает, проверьте, что формула действительно возвращает ИСТИНА для первой ячейки и что вы написали её для верхней левой ячейки выделения. Правило, написанное для другой ячейки, сдвигается на строку и выглядит случайным.
Рабочий пример: трекер задач
Вот настройка, которую можно повторить за пять минут. В столбце A находятся названия задач, в столбце B сроки выполнения, в столбце C статус, например «Открыто» или «Готово», а в столбце D количество часов.
- Выделите ячейки от B2 до последней строки. Добавьте правило с формулой
=B2<TODAY()и красной заливкой. Так отмечаются все просроченные задачи. - Для тех же ячеек добавьте правило
=B2<TODAY()+7с жёлтой заливкой и переместите красное правило выше в разделе «Управление правилами». Теперь просроченные задачи красные, задачи со сроком в течение недели жёлтые, а остальные остаются без изменений. - Выделите всю таблицу, от A2 до D100, и добавьте правило с формулой вроде
=$C2="Готово"с серым шрифтом. Знак доллара фиксирует столбец, поэтому вся строка становится серой, когда в статусе указано «Готово». Эта формула — мой собственный шаблон, а не один из примеров Microsoft, но он следует тем же правилам: начинается со знака равенства и возвращает ИСТИНА или ЛОЖЬ. - Выделите D2 и далее и примените Гистограммы, чтобы самые трудоёмкие задачи было легко заметить.
Здесь может пойти не так две вещи. Если красное правило стоит ниже жёлтого, просроченные задачи будут выглядеть жёлтыми, потому что оба условия истинны, и побеждает правило, которое выше. Если правило «Готово» стоит первым и вы включили «Остановить, если истина», цветовая индикация дат у завершённых задач исчезнет, что, вероятно, и нужно. Сначала проверьте это на копии и поменяйте порядок правил, чтобы увидеть разницу.
Управление порядком правил
Откройте Условное форматирование > Управление правилами, чтобы увидеть Диспетчер правил условного форматирования. Microsoft объясняет, как это работает:
- Правила проверяются сверху вниз, а новые правила по умолчанию добавляются в начало. Переместить вверх и Переместить вниз меняют их порядок.
- Если правила не конфликтуют, применяются все они. Например, одно правило может задать полужирный шрифт, а другое — красную заливку.
- Если правила конфликтуют, побеждает тот, что выше.
- Условный формат, который истинен, переопределяет ручной формат на тех же ячейках. Если удалить правило, ручной формат вернётся.
- Прекратить, если истина останавливает проверку, как только правило становится истинным. Microsoft отмечает, что это имитирует поведение старых версий Excel. Этот параметр нельзя задать для правил с гистограммами, цветовыми шкалами и наборами значков.
Если вы видите неожиданный цвет, ищите причину в Диспетчере правил.
Копирование, поиск и очистка условного форматирования
- Копирование в другие ячейки. Используйте Формат по образцу на вкладке «Главная». Двойной щелчок позволяет применить формат к нескольким областям, а Esc завершает работу. Если в правиле используется формула, проверьте ссылки после копирования.
- Поиск ячеек с форматированием. Выберите Найти и выделить > Условное форматирование или Выделить группу ячеек > Условные форматы.
- Очистка правил. Выделите ячейки, затем выберите Главная > Условное форматирование > Очистить правила > Очистить правила из выделенных ячеек или Очистить правила со всего листа.
Одно ограничение, которое указывает Microsoft: при вставке форматированных ячеек в другой экземпляр Excel правило не переносится, поэтому, если правила должны сохраниться, копируйте внутри одного экземпляра.
Ограничения и причины, по которым правило может не работать
- Другая книга. Условное форматирование не может ссылаться на внешние книги. Сначала перенесите данные в файл.
- Сводные таблицы. В области «Значения» сводной таблицы нельзя задать форматирование по тексту, по дате или по уникальным и повторяющимся значениям. Работают только правила, основанные на числах.
- Длинный текст. Текстовые условия ограничены 255 символами.
- Порядок. Правило, стоящее выше, переопределяет правило, стоящее ниже. Поднимите более конкретное правило вверх.
- Ручная заливка. Если у ячейки есть ручная заливка и правило выполняется, побеждает правило. Если результат выглядит неверно, проверьте, действительно ли правило выполняется.
- Excel для веба. На странице, которую я использовал, нет шагов и ограничений, специфичных для веба. Я бы не стал утверждать, что все типы правил работают в браузере одинаково. Проверяйте на копии.
Распространённые ошибки
- Ввод формулы не в ту ячейку, из-за чего подсветка сдвигается на одну строку.
- Использование абсолютных ссылок (
$A$1) там, где нужна относительная, и окраска либо всего, либо ничего. - Накопление правил поверх правил, пока никто не понимает, почему ячейка красная. Откройте «Управление правилами» и наведите порядок.
- Вставка форматированных ячеек в другое окно Excel с ожиданием, что правила перенесутся. Microsoft пишет, что не переносятся.
- Опора только на красный и зеленый цвета.
- Применение правила ко всему столбцу, когда оно нужно только для 200 строк.
Смежные темы для изучения
- Выделяйте дубликаты до их удаления. Правило «Повторяющиеся значения» хорошо подходит для проверки перед очисткой списка.
- Раскрывающиеся списки и правила вместе. Отмечайте строку по статусу, который кто-то выбирает из списка.
- Условное форматирование в сводных таблицах. В области «Значения» работают только правила, основанные на числах, поэтому продумывайте макет заранее.
- Панели мониторинга. Сочетание гистограмм, значков и срезов делает отчет понятным с первого взгляда.
- Производительность на больших листах. Держите диапазоны компактными, а количество правил небольшим. На странице Microsoft нет конкретных цифр, поэтому проверяйте на своих данных.
Следующий шаг
Откройте реальный лист и примените одно правило из группы «Правила выделения ячеек». Затем попробуйте правило на основе формулы, например =B2<TODAY() для столбца с датами, и откройте «Управление правилами», чтобы увидеть, как работает порядок. Держите условное форматирование в Excel простым: один понятный сигнал в столбце лучше, чем радуга.