Кейс 6. Условное форматирование

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

Увеличение эффективности работы и задание условий, ограничений и автоматическое форматирование данных.

Имеется отчет об остатках склада. Для каждой номенклатуры есть норма минимального остатка на складе:
Для своевременного пополнения складских запасов нужно контролировать данные в этой таблице, просматривая ее содержимое.
С правилами условного форматирования все становится намного
проще - мы сразу видим позиции, которые требуют заказа.
1
  • Открывается меню:
Настройка условного форматирования
  • Переходим на вкладку "Главная" и выбираем "Условное форматирование":
Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться, достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.

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

Все сценарии разбиты на категории:
Правило выделения ячеек
Правило отбора первых и последних значений
Гистограммы
Цветовые шкалы
Наборы значков
Правила выделения ячеек
Правила выделения ячеекБольше...
  • Выделяем диапазон ячеек для проверки
Применяют для ячеек, которые сравниваются с определенным значением. Возможны различные варианты, которые показаны на рисунке ниже:
  • Выбираем соответствующую команду
  • В диалоговом окне вводим значение и делаем настройки параметров выделения
  • Нажимаем "ОК" и получаем результат
Правила выделения ячеекМеньше...
Форматируются ячейки из выделенного диапазона, значение которых меньше заданного.
Правила выделения ячеекМежду...
Форматируются ячейки из диапазона, которые попадают в заданный интервал значений.
Правила выделения ячеекРавно...
Форматируются ячейки, равные заданному значению. Сравнивать можно и числа и текст.
Правила выделения ячеекТекст содержит...
Форматируются ячейки, содержащие заданный текст.
Правила выделения ячеекДата...
Правила форматирования применяются к датам. Удобно для контроля графика платежей, отгрузок..
Правила выделения ячеекПовторяющиеся значения...
Хороший способ найти задвоенные позиции. В настройках можно выбрать и обратный вариант - уникальные значения
Правила отбора первых и последних значений
Правила отбораПервые 10 элементов...
Выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».
Выделяются 10 наибольших значений. Количество регулируется в диалоговом окне.
Правила отбораПервые 10%...
Выделяются ячейки с самым большим значением, которые составляют 10% от общей суммы. Долю можно регулировать в диалоговом окне.
Правила отбораВыше среднего и ниже среднего...
Аналогичным образом работают "Последние 10" и "Последние 10%". Только условиями для форматирования являются минимальные элементы.

Выделяются ячейки с самым большим значением, которые составляют 10% от общей суммы. Долю можно регулировать в диалоговом окне.

Гистограммы

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

Применяется аналогично всем вышеперечисленным.

Цветовые шкалы

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

На рисунке видно, что максимальному значению присвоен зеленый цвет, а минимальному - красный. Все значения, которые находятся в диапазоне от минимального к максимальному меняют оттенок заливки в зависимости от приближенности значения к максимальному или минимальному элементу таблицы.

Наборы значков

Для каждой ячейки присваивается значок в соответствии с выбранным стилем и весом значения ячейки:

2

Рассмотрим использование формул для определения правил условного форматирования

Вернемся к кейсу, который мы начали рассматривать в самом начале.

Имеется отчет об остатках склада. Для каждой номенклатуры есть норма минимального остатка на складе.

Для своевременного пополнения складских запасов нужно контролировать данные в этой таблице, просматривая ее содержимое.

  • Выделяем область для применения условий форматирования (столбец "В")
  • Переходим на вкладку "Главная", выбираем "Сортировка" и далее "Создать правило"
  • В появившемся окне выбираем тип правил "

  • В нижней части окна необходимо произвести необходимые настройки
  • Вводим формулу

  • Уберите знак "$" из формулы, нужно чтобы ячейки имели относительную адресацию для всего диапазона форматирования

С правилами условного форматирования все становится намного проще: мы сразу видим позиции, которые требуют заказа:

Файл с решенным кейсом - Кейс 6(решенный)
comments powered by HyperComments