Условное форматирование в Excel
Ячейка красится сама, когда значение попадает под правило: просрочка, минус, повтор, топ-3. Готовые правила в файле и формулы для своих.
Коротко
Условное форматирование — это правило вида «если значение такое, закрась ячейку так». Заливка не хранится в ячейке: она пересчитывается каждый раз, когда значение меняется. Поэтому подсветка не устаревает и её нельзя случайно затереть, вписав новое число.
Правила бывают двух видов. По значению самой ячейки — больше ста, меньше нуля, содержит слово, входит в тройку крупнейших, повторяется. Их собирают мышкой в меню, формула не нужна. По формуле — когда цвет ячейки зависит не от неё самой: подсветить всю строку по статусу в одном столбце, сравнить с соседней ячейкой, посмотреть на дату. Такое правило — это выражение, которое должно дать ИСТИНА или ЛОЖЬ.
В файле-песочнице три столбца с данными: «Значение» (C7:C16), «Число» (D7:D16) и «Дата» (E7:E16), сегодняшняя дата пересчитывается в C4 формулой =СЕГОДНЯ(). На них уже висят семь правил — меняете числа и даты в жёлтых ячейках и сразу видите, как переезжает подсветка.
То же самое формулой в Excel
=$C6>100 — подсветить всю строку по значению одной ячейки: доллар только перед буквой=И($E7<>"";$E7<СЕГОДНЯ()) — дата уже прошла; первое условие нужно, иначе покрасятся пустые ячейки=И($E7<>"";$E7>=СЕГОДНЯ();$E7-СЕГОДНЯ()<=7) — срок подходит: до даты осталось не больше недели=И(D7>=10;D7<=50) — число между 10 и 50 — то, чего в файле нет и что дописывается за минуту=D7<СРЗНАЧ($D$7:$D$16) — ниже среднего по столбцу; диапазон среднего закреплён, ячейка — нет=СЧЁТЕСЛИ($B$7:$B$100;B7)>1 — значение встречается в столбце больше одного раза — повтор=ЕПУСТО(B7) — пустая ячейка: годится для контроля незаполненных полейПесочница: семь готовых правил на живых данных
Лист «Правила» с тремя столбцами примеров. Уже настроены: больше ста — зелёным, меньше нуля — красным, тройка крупнейших — жёлтым и гистограмма прямо в ячейках (столбец «Число»); текст со словом «срочно» и повторяющиеся значения (столбец «Значение»); просроченная дата — красным (столбец «Дата»). Меняете значения — подсветка переезжает. Открыть и разобрать сами правила: Главная → Условное форматирование → Управление правилами.
Файл
uslovnoe-formatirovanie.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Выделите диапазон целиком, а не одну ячейку: правило применяется ровно к тому, что выделено. Для подсветки всей строки выделяйте строки со всеми столбцами, например
B6:F40. - Главная → Условное форматирование → Создать правило.
- Для простого случая берите «Форматировать только ячейки, которые содержат»: выбираете «значение ячейки», «больше», вводите 100 — и никакой формулы не требуется.
- Для всего остального — «Использовать формулу для определения форматируемых ячеек». Формулу пишите для левой верхней ячейки выделения: Excel сам размножит её на весь диапазон, сдвигая ссылки. Выделили
B6:F40— формула пишется так, как если бы проверялась строка 6. - Расставьте доллары. Нужно подсветить всю строку по одному столбцу — закрепите столбец, но не строку:
=$C6>100. Доллар перед буквой держит столбец, отсутствие доллара перед цифрой позволяет правилу ехать вниз по строкам. - Формат → «Заливка», выберите цвет. Ярких цветов лучше не больше трёх на лист: подсвечено всё — значит не подсвечено ничего.
- ОК. Проверить и поправить готовое: Условное форматирование → Управление правилами. Там же в поле «Применяется к» правится диапазон — это быстрее, чем создавать правило заново.
Где обычно ломается
Пять причин, по которым условное форматирование «не работает».
- Формула написана для не той ячейки. Правило пишется для левой верхней ячейки выделенного диапазона, а Excel показывает вам активную ячейку — они совпадают не всегда. Выделили
B6:F40протяжкой снизу вверх — активной осталасьB40, и правило уедет на 34 строки. Перед созданием правила щёлкните по левой верхней ячейке диапазона. - Лишние или недостающие доллары.
=$C6>100красит строку по столбцу C.=$C$6>100проверяет одну-единственную ячейку и красит либо всё, либо ничего.=C6>100проверяет каждую ячейку саму по себе. Три похожих записи — три разных результата. - Пустые ячейки считаются нулём. Правило «меньше нуля» пустые не тронет, а вот «меньше сегодняшней даты» покрасит их все: пустая ячейка для Excel — это 0, то есть 0 января 1900 года. Спасает первое условие:
=И($E7<>"";$E7<СЕГОДНЯ()). - Порядок правил и «Остановить, если истина». Правила проверяются сверху вниз, и первое подошедшее закрашивает ячейку. Если «меньше нуля» стоит ниже «топ-3», минус может остаться неокрашенным. Порядок меняется стрелками в «Управлении правилами», там же стоит флажок «Остановить, если истина».
- Диапазон расползся от копирования. Копирование ячеек тащит правила за собой, и в «Управлении правилами» вместо одной строки появляются десятки обрывков вроде
$B$7:$B$9;$B$12. Файл при этом заметно тяжелеет. Лечится так: удалить лишние правила и заново задать один диапазон в поле «Применяется к», а копировать потом через «Специальная вставка → значения».
Снять форматирование: выделить диапазон и Условное форматирование → Удалить правила → «из выделенных ячеек». Целиком по листу — «со всего листа». Обычная заливка при этом остаётся: она в ячейке, а не в правиле.
В Google Таблицах всё то же самое, только меню называется «Формат → Условное форматирование», а вместо «Использовать формулу» — «Ваша формула». Формулы совпадают до знака, включая доллары.