Управленческий учёт
Внедрение CRM
Cоздание сайтов
Создание приложений
Получить консультацию
Разработка Чат-ботов
Получить консультацию
Telegram-чат

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

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

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

Скачать Кейс 6

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

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

1

  • Открывается меню:

    Настройка условного форматирования

    • Переходим на вкладку "Главная" и выбираем "Условное форматирование":

    Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться, достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.

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

    Все сценарии разбиты на категории:

    Правило выделения ячеек
    Правило отбора первых и последних значений
    Гистограммы
    Цветовые шкалы
    Наборы значков

    Правила выделения ячеек

    Правила выделения ячеек → Больше...

    • Выделяем диапазон ячеек для проверки

    Применяют для ячеек, которые сравниваются с определенным значением. Возможны различные варианты, которые показаны на рисунке ниже:

    • Выбираем соответствующую команду
    • В диалоговом окне вводим значение и делаем настройки параметров выделения

    • Нажимаем "ОК" и получаем результат

    Правила выделения ячеек → Меньше...

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

    Правила выделения ячеек → Между...

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

    Правила выделения ячеек → Равно...

    Форматируются ячейки, равные заданному значению. Сравнивать можно и числа и текст.

    Правила выделения ячеек → Текст содержит...

    Форматируются ячейки, содержащие заданный текст.

    Правила выделения ячеек → Дата...

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

    Правила выделения ячеек → Повторяющиеся значения...

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

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

    Правила отбора → Первые 10 элементов...

    Выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».

    Выделяются 10 наибольших значений. Количество регулируется в диалоговом окне.

    Правила отбора → Первые 10%...

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

    Правила отбора → Выше среднего и ниже среднего...

    Аналогичным образом работают "Последние 10" и "Последние 10%". Только условиями для форматирования являются минимальные элементы.

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

    Гистограммы

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

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

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

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

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

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

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

    2

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

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

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

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

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

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

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

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

    Кейс 6 (Решенный)
    *Для самопроверки можете загрузить файл с решенный кейсом
    КЕЙС 5
    Сортировка данных в таблицах
    Обзор правильного упорядочивания информации в таблицах с помощью сортировки.
    КЕЙС 7
    Графики и диаграммы
    Анализ данных при построении графиков и диаграмм