Условное форматирование и проверка данных

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

  • Средний
  • 14 мин
  • 10 вопросов
  • Урок 12 из 27

В этом уроке ты узнаешь

  • Применять готовые правила условного форматирования
  • Писать правило-формулу, которое выделяет всю строку
  • Задавать числовые ограничения и раскрывающиеся списки с помощью проверки данных

Учитель хочет, чтобы баллы ниже 50 сами становились красными, а ввести по ошибке 150 баллов было невозможно. Для этого в Excel есть два инструмента: условное форматирование меняет вид ячейки в зависимости от её значения, а проверка данных не даёт ввести неправильные данные.

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

Все правила находятся в меню Home › Styles › Conditional Formatting. Правило задаётся один раз: когда меняется значение, сам меняется и цвет. Основные виды правил:

Пункт менюЧто делает
Highlight Cells RulesGreater Than, Less Than, Between, Equal To, Text that Contains, Duplicate Values — выделяет ячейки, отвечающие условию
Top/Bottom RulesTop 10 Items, Bottom 10%, Above Average — лучшие, худшие или выше среднего
Data Barsполоса внутри ячейки, пропорциональная значению
Color Scalesцветовая шкала: например, малые значения красные, большие — зелёные
Icon Setsзначки: стрелки, светофоры или звёзды
  1. Выдели диапазон

    Выдели ячейки с баллами, например B2:B30.

  2. Выбери правило

    Нажми Home › Styles › Conditional Formatting › Highlight Cells Rules › Less Than….

  3. Задай порог и формат

    Введи в поле 50, выбери в списке Light Red Fill with Dark Red Text и нажми OK. Как только балл опустится ниже 50, ячейка станет красной.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Правило с формулой: выделяем всю строку

Чтобы выделить не только балл, а всю строку ученика, выдели A2:D30 и открой Conditional Formatting › New Rule… › Use a formula to determine which cells to format. Формулу пиши для левой верхней ячейки выделения: =$D2<50. Знак $ закрепляет столбец D, поэтому каждая ячейка строки проверяет одну и ту же ячейку D, а номер строки остаётся относительным. Когда формула возвращает TRUE, Excel применяет формат. Изменить правила можно в Manage Rules…, удалить — через Clear Rules.

Интерактив

Загрузка симуляции…
Проверь формулы правил: каждая возвращает TRUE или FALSE.

Проверка данных

  1. Открой окно

    Выдели B2:B30 и нажми Data › Data Tools › Data Validation.

  2. Задай правило

    На вкладке Settings выбери Allow: Whole number, Data: between, Minimum: 0, Maximum: 100.

  3. Добавь подсказку

    На вкладке Input Message введи текст, который появится при выборе ячейки, например «Введите целое число от 0 до 100».

  4. Выбери сообщение об ошибке

    На вкладке Error Alert выбери вид: Stop не принимает неверное значение, Warning спрашивает, Information только сообщает. Нажми OK.

Для раскрывающегося списка выбери Allow: List и введи значения в поле Source: Баку,Гянджа,Сумгайыт (при некоторых региональных настройках вместо запятой нужна точка с запятой) — или укажи диапазон на листе: =$H$2:$H$4. Рядом с ячейкой появится стрелка, и пользователь выберет значение из списка — больше никаких «баку» или «Баку » с лишним пробелом в конце.

Запомни

  • Условное форматирование (Home › Styles) автоматически меняет вид ячейки в зависимости от её значения.
  • Правило-формула пишется для левой верхней ячейки и применяет формат там, где возвращает TRUE; =$D2<50 выделяет всю строку.
  • Проверка данных (Data › Data Tools) задаёт ограничения, списки, подсказки и сообщения об ошибках.
  • Проверка не касается уже введённых значений — найди их через Circle Invalid Data.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

Вопрос 1 / 10

Вопрос 1 / 10. Где находится меню условного форматирования?