Условное форматирование: уровень “Бог”. Формулы для раскраски строк и шахматки

Привет, дорогой читатель! 👋

Вы уже знакомы с условным форматированием? Умеете красить ячейки красным, если просрочка, или зелёным — если выполнено? Отлично. А теперь забудьте об этом «детском саде». Сегодня мы поднимемся на уровень «Бог» 🧙‍♂️. Вы научитесь раскрашивать целые строки по условию в одной ячейке и создавать шахматку, которая динамически подстраивается под ваши данные.

Поехали! 🚀

🎨 Как раскрасить строку целиком (а не одну ячейку)

Стандартное условное форматирование по умолчанию применяется к тем ячейкам, которые вы выделили. Чтобы подсветить всю строку, нужно:

1. Выделить весь диапазон, который нужно форматировать (например, A2:F20).
2. Создать новое правило → «Использовать формулу для определения форматируемых ячеек».
3. Ввести формулу, которая фиксирует столбец с условием, но не фиксирует строку.

🔑 Секрет: используйте знак $ только перед буквой столбца (смешанная ссылка). Например, если условие находится в столбце C, формула для выделенной строки будет =$C2="Иванов А.А.". В данном случае будут выделены все строки, в которых есть "Иванов А.А." как завхоз.

Пример выделения строк в Эксель

📌 Пример 1: выделяем выполненные заказы

Пусть у вас таблица: заказ (A), сумма (B), статус (C). Хотим зелёным залить всю строку, если в столбце C написано «Выполнен».

• Выделяем диапазон A2:C20. Важно не выделять первую строку с заголовком!
• Правило: формула =$C2="Выполнен".
• Задаём заливку зелёным.

✅ Готово! Теперь каждая строка со статусом «Выполнен» будет подсвечена целиком.

📌 Пример 2: красный цвет для просроченных задач

В столбце D — дата выполнения. Если дата меньше сегодняшней (<СЕГОДНЯ()), а статус ещё не «Выполнен», строка должна гореть красным.

Формула:

=И($D2<СЕГОДНЯ(); $C2<>"Выполнен")

Снова фиксируем столбцы D и C, а строки остаются относительными. Применяем к диапазону A2:D100 — и вот уже вся строка кричит о проблеме. 🔴

Выделены только те строки, по которым заказ еще не выполнен и дата просрочена.

♟️ Шахматная раскраска: от простого к сложному

Классическая «зебра» (чередование цветов через строку) делается элементарно:

• Формула: =ОСТАТ(СТРОКА();2)=0
• Формула: =ОСТАТ(СТРОКА();2)=1 — для противоположного цвета.

Но это подходит только для статичных таблиц. Что делать, если вы хотите, чтобы цвет менялся при изменении значения в определённом столбце? Например, группировка строк по проектам или датам. Тогда строки, относящиеся к одному проекту, должны быть одного цвета, а следующий проект — другого, и так чередоваться. Это называется шахматка по группам.

🧩 Формула для чередования групп

Предположим, данные отсортированы по столбцу A (название проекта). Нам нужно, чтобы каждый раз, когда значение в столбце A меняется, цвет переключался.

Пример исходных данных. Нужно строки по каждому проекту выделить своим цветом.

Выделяем диапазон A2:D100 (или шире) и используем формулу:

=ОСТАТ(СУММ(--($A$2:$A2<>$A$1:$A1));2)=0

Разберём магию:

• $A$2:$A2<>$A$1:A1 — сравниваем текущую ячейку с предыдущей, получаем массив ИСТИНА/ЛОЖЬ на момент изменения.
• -- превращает ИСТИНА в 1, ЛОЖЬ в 0.
• СУММ считает количество изменений от начала до текущей строки.
• ОСТАТ(...;2)=0 — чередует чётные и нечётные группы.

👉 Важно: эта формула работает как формула массива, но в условном форматировании она вводится как обычная. Главное — правильно зафиксировать ссылки: начало диапазона должно быть абсолютным ($A$2), а конец — относительным ($A2).

Результат форматирования по проектам.

🎨 Пример: раскраска групп по датам

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

Формула (выделяем весь диапазон данных, начиная с второй строки, так как первая не с чем сравнивать):

=ОСТАТ(СУММ(--($D$2:$D2<>$D$1:$D1));2)=0

Теперь ваша таблица выглядит как профессиональный отчёт — группы наглядно отделены друг от друга. 📊

🔧 Комбинируем правила

Никто не запрещает вам накладывать несколько условий. Например:

• Сначала «зебра» для удобства чтения (чередование цветов).
• Сверху — правило выделения красным просроченных строк.

Порядок правил важен: используйте «Управление правилами» и расставляйте приоритеты. Не забывайте про галочку «Остановить, если истинно» — чтобы «зебра» не перекрашивала красные строки в свой цвет.

🧠 Полезные формулы для продвинутых

• Подсветка выходных в календаре: =ДЕНЬНЕД($A2;2)>5 (применяется к строке с датами).
• Строка, содержащая искомый текст: =НАЙТИ("важно"; $B2) — без ЕСЛИОШИБКА даст ошибку в ячейках без текста, поэтому лучше обернуть в ЕЧИСЛО(НАЙТИ(...)).
• Строка с максимальным значением в столбце: =$C2=МАКС($C:$C) — выделяем победителя. 🏆

🎯 Заключение

Условное форматирование на формулах — это настоящий крючок, на который можно повесить почти любую логику. Вы больше не ограничены меню «больше/меньше». Теперь вы можете:

• подсвечивать целые строки по статусам,
• создавать динамические «шахматки» для группировок,
• управлять вниманием пользователя на сложных отчётах.

Потренируйтесь на своих таблицах — и очень скоро вы начнёте видеть данные в цвете так, как раньше и не мечтали.

🔥 Ставьте лайк, если хотите ещё статей про «божественный» уровень Excel! Подписывайтесь, чтобы не пропустить разборы сложных формул, Power Query и макросов. Если есть свои фишки с условным форматированием — делитесь в комментариях, обсудим! 👇

Успехов в работе с данными! 💪

⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇

Материалы по Эксель. Содержание данного канала:

https://dzen.ru/a/ZhpQXTxmQDShWlXf

⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆

На сегодня все!

Спасибо за внимание!