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

Условное форматирование ячеек - набор инструментов, которые автоматически форматируют ячейки по определенным условиям (правилам).

Инструменты раздела "Условное форматирование" находятся на самом видном месте - на вкладке "Главная".

При нажатии предоставляется выбор нескольких групп инструментов:

• Правила выделения ячеек - сравнительные инструменты.
• Правила отбора первых и последних значений - из названия думаю всё понятно.
• Гистограммы - внутри ячейки создается гистограмма, которая пропорциональна величине значения.
• Цветовые шкалы - форматируют все значения цветовой гаммой в зависимости от их близости к минимуму/максимуму.
• Наборы значков - подставляются ярлыки.
• Управление правилами - фактически диспетчер примененных правил к ячейкам.

Давай пройдемся по каждой группе.

1 группа "Правила выделения ячеек"

• Больше *** - система форматирует значения больше указанного.
• Меньше ***- система форматирует значения меньше указанного.
• Между *** - система форматирует значения, находящиеся в промежутке между указанными.
• Равно *** - система форматирует значения равные указанному.
• Текст содержит *** - система форматирует ячейки, которые содержат указанный текст.
• Дата *** - система форматирует ячейки, которые содержат указанную дату.
• Повторяющиеся значения *** - система форматирует дубликаты.
• Другие правила - существуют почти в каждом пункте, всё то, что не вошло в список.

Видеопример

Простой пример по форматированию значений больше 10.

2 группа "Правила отбора первых и последних значений"

Уверен, что этих инструментов будет всё понятно и без пояснений)

• Первые 10 элементов
• Первые 10%
• Последние 10 элементов
• Последние 10%
• Выше среднего
• Ниже среднего

Видеопример

Форматирование значений выше среднего. Система просматривает все значения в диапазоне и выделяет те, что выше среднего от всех значений.

3 группа "Гистограммы"

Гистограммы хороши для случаев, когда необходимо визуально отразить разброс значений. Внутри ячейки помещается небольшая гистограмма (график), которая отражает величину значения по сравнению с другими.

На выбор предлагаются лишь разные цветовые вариации гистограмм.

Видеопример

4 группа "Цветовые шкалы"

Цветовые шкалы - это разметка всех значений оттенками цветов в зависимости от их приближения к минимуму и максимуму.

Это как отражение высот и глубин на географическом атласе. Чем глубже природный бассейн, тем темнее синий цвет.

Также и здесь. Чем ближе к максимуму, тем интенсивнее выбранный цвет.

На выбор доступны разные цветовые вариации. Не более.

Видеопример

5 группа "Наборы значков"

Инструмент позволяет проставлять определенные ярлыки для значений, подпадающих под условия.

Видеопример

Управление правилами

Самая удобная штука здесь - пункт Управления правилами. Он включает в себя пункты "Создать правило" и "Удалить правила".

Выглядит как своего рода диспетчер всех условий.

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

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

Чем выше правило в списке, тем оно приоритетнее. Порядок правил изменяется с помощью стрелок.

Если я ставлю флажок "Остановить, если истина" на первом правиле "больше чем 200", то при получении значений >200, система останавливается и не применяет больше никаких других правил.

Если значений больше 200 нет, то система проверяет следующее правило.

Таким образом работает чекбокс "Остановить, если истина".

Видеопример

Демонстрация работы в "Управлении правилами".

Как работать с формулами в условиях?

Помимо предустановленных правил форматирования, можно записать свое в виде формулы.

Необходимо создать правило и выбрать пункт "Использовать формулу для определения форматируемых ячеек".

В нижнем окне прописываем формулы через знак равно "=". Форматирование задается по кнопке "Формат.

Проверим как это работает на практике↓

Задача 1: закрашивать ячейки в столбце А, если значение в столбце В равно "Да".

Формула для решения задачи:

Да, тут двойной знак равно. В первом случае он означает начало формулы. Второй - это проверка ячеек в столбце В на значение "Да". Если это истина, то произойдет форматирование.

В нашем случае столбец Да/Нет начинается с ячейки В1, поэтому она и указана в формуле.

Задача 2: закрашивать ячейки с фамилиями менеджеров, если сумма значений по месяцам больше 30.

Формула для решения задачи:

Видеопример

Те же задачи в видео.

________________________________

По всем вопросам и интересным предложениям пиши сюда: @excelstudybot

Подписывайся на канал Обучайся Excel