Sheets AcademySheets Academy
Премиум
Условия

Подсветка по условию

В этой таблице остатков две ячейки красные, хотя вручную их никто не закрашивал. Цвет зависит от числа, которое стоит в ячейке:

fx
A
B
C
1
2
3
4
5
6
7
8
Товар
Остаток, шт.
Наушники
24
Колонка
7
Зарядка
31
Кабель
3
Чехол
18
Лист1
Впишите в B4 число 6: ячейка покраснеет. Впишите в B3 число 40: цвет пропадёт.

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

Правило из трёх частей

Любое правило подсветки состоит из трёх частей:

  • ячейки, которые нужно проверять;
  • условие, например «меньше 10» или «больше 100 000»;
  • цвет, которым закрасить подходящие ячейки.

Цвет подбирают по смыслу, как в светофоре:

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

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

Выделите продажи B2:B7 и закрасьте зелёным те, что больше 100 000
fx
A
B
C
1
2
3
4
5
6
7
8
9
Менеджер
Продажи, ₽
Анна
124000
Борис
87500
Вера
100000
Глеб
99900
Дина
143000
Егор
61000
Лист1
На вкладке «Главная» нажмите «Условное форматирование» → «Правила выделения ячеек» → «Больше…». В панели «Условное форматирование» введите 100000, выберите зелёный стиль и нажмите «Готово».

Текст содержит

Кроме сравнений с числом, в том же меню есть «Равно…» для точного совпадения и «Текст содержит…» для поиска слова внутри ячейки. Второе удобно для адресов, названий и комментариев: оно ищет буквы подряд в любом месте ячейки, а большие и маленькие буквы для него одинаковы.

fx
A
B
1
2
3
4
5
6
7
8
Адрес доставки
г. Москва, ул. Ленина, 5
Казань, ул. Баумана, 12
МОСКВА, Тверская, 8
Московская обл., Химки
Самара, ул. Мира, 3
Лист1
На столбце правило «Текст содержит… Москва». Строка с Химками не закрасилась: в слове «Московская» после «Моск» идёт «о», а не «в».

Повторяющиеся значения

На вебинар пришло восемь заявок, но кто-то отправил форму дважды. Глазами повтор в столбце адресов легко пропустить. В том же меню есть правило, которому не нужно ни число, ни текст: «Повторяющиеся значения…» закрашивает всё, что встречается в выделенных ячейках больше одного раза.

fx
A
B
C
1
2
3
4
5
6
7
8
9
10
11
Имя
Email
Анна
anna.petrova@mail.ru
Игорь
igor.s@yandex.ru
Мария
m.belova@gmail.com
Игорь
igor.sh@yandex.ru
Павел
pavel.n@inbox.ru
Анна
Anna.Petrova@mail.ru
Елена
lena.k@gmail.com
Мария
m.belova@gmail.com
Лист1
Выделите B2:B9, на вкладке «Главная» нажмите «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения…» и нажмите ОК.

Закрасились четыре адреса, то есть две пары. Правило красит все копии, а не только вторую, поэтому какую заявку оставить, решает человек. Большие и маленькие буквы для него одинаковы, и два адреса Анны совпали. А адреса двух Игорей отличаются одной буквой, и правило их не тронуло: оно сравнивает значения целиком.

Премии на одном листе

Подсветка хорошо работает вместе с формулами. Ниже таблица премий целиком:

  • порог премии записан в B9;
  • ЕСЛИ в столбце C решает, кому премия;
  • СЧЁТЕСЛИ и СУММЕСЛИ под таблицей подводят итог;
  • правило «Равно… Премия» красит такие ячейки зелёным.
fx
A
B
C
D
1
2
3
4
5
6
7
8
9
10
11
12
13
Менеджер
Продажи, ₽
Премия
Анна
124000
Борис
87500
Вера
100000
Глеб
99900
Дина
143000
Егор
61000
Порог, ₽
100000
Премий
Их продажи, ₽
Лист1
Поменяйте порог в B9 на 90000 или 130000: пересчитаются отметки, итоги и подсветка.

Подсветка меняет только вид ячейки, а не её значение. Формулы цвета не видят: СЧЁТЕСЛИ в B10 считает слова «Премия», а не зелёные ячейки.