Подписывайтесь на наше сообщество Вконтакте и задавайте любые вопросы!

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

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

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

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

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

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

    Применяется аналогично всем вышеперечисленным.
    Цветовые шкалы
    Автоматически определяют максимальное и минимальное значение в диапазоне и форматирует каждую ячейку по цвету, который соответствует значению, изображая что-то вроде тепловой карты:
    На рисунке видно, что максимальному значению присвоен зеленый цвет, а минимальному - красный. Все значения, которые находятся в диапазоне от минимального к максимальному меняют оттенок заливки в зависимости от приближенности значения к максимальному или минимальному элементу таблицы.
    Наборы значков
    Для каждой ячейки присваивается значок в соответствии с выбранным стилем и весом значения ячейки:
    2
    Рассмотрим использование формул для определения правил условного форматирования
    Вернемся к кейсу, который мы начали рассматривать в самом начале.

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

    Для своевременного пополнения складских запасов нужно контролировать данные в этой таблице, просматривая ее содержимое.
    • Выделяем область для применения условий форматирования (столбец "В")
    • Переходим на вкладку "Главная", выбираем "Сортировка" и далее "Создать правило"
    • В появившемся окне выбираем тип правил "
    • В нижней части окна необходимо произвести необходимые настройки
    • Вводим формулу
    • Уберите знак "$" из формулы, нужно чтобы ячейки имели относительную адресацию для всего диапазона форматирования
    С правилами условного форматирования все становится намного проще: мы сразу видим позиции, которые требуют заказа:
    Файл с решенным кейсом - Кейс 6(решенный)
    Made on
    Tilda