Нужна доработка таблицы?
Кейс 6. Условное форматирование
Этот инструмент позволяет увеличить эффективность работы в несколько раз - просто задаем условия, и данные формируются автоматически.
Например, когда до платежа остается два дня, ячейка подсвечивается желтым цветом, если значение расходов в одном из месяцев стало больше средней величины, то ячейка будет красной. Теперь разберем это более подробно
Увеличение эффективности работы и задание условий, ограничений и автоматическое форматирование данных.
Имеется отчет об остатках склада. Для каждой номенклатуры есть норма минимального остатка на складе:
Для своевременного пополнения складских запасов нужно контролировать данные в этой таблице, просматривая ее содержимое.
С правилами условного форматирования все становится намного
проще - мы сразу видим позиции, которые требуют заказа.
1
- Открывается меню:
Настройка условного форматирования
- Переходим на вкладку "Главная" и выбираем "Условное форматирование":
Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться, достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.
В нижней части еще три команды, с помощью которых происходит ручное создание, удаление и управление правилами условного форматирования.
О них также поговорим.
Все сценарии разбиты на категории:
Правила выделения ячеек
Правила выделения ячеек → Больше...
- Выделяем диапазон ячеек для проверки
Применяют для ячеек, которые сравниваются с определенным значением. Возможны различные варианты, которые показаны на рисунке ниже:
- Выбираем соответствующую команду
-
В диалоговом окне вводим значение и делаем настройки параметров выделения
- Нажимаем "ОК" и получаем результат
Правила выделения ячеек → Меньше...
Форматируются ячейки из выделенного диапазона, значение которых меньше заданного.
Правила выделения ячеек → Между...
Форматируются ячейки из диапазона, которые попадают в заданный интервал значений.
Правила выделения ячеек → Равно...
Форматируются ячейки, равные заданному значению. Сравнивать можно и числа и текст.
Правила выделения ячеек → Текст содержит...
Форматируются ячейки, содержащие заданный текст.
Правила выделения ячеек → Дата...
Правила форматирования применяются к датам. Удобно для контроля графика платежей, отгрузок..
Правила выделения ячеек → Повторяющиеся значения...
Хороший способ найти задвоенные позиции. В настройках можно выбрать и обратный вариант - уникальные значения
Правила отбора первых и последних значений
Правила отбора → Первые 10 элементов...
Выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».
Выделяются 10 наибольших значений. Количество регулируется в диалоговом окне.
Правила отбора → Первые 10%...
Выделяются ячейки с самым большим значением, которые составляют 10% от общей суммы. Долю можно регулировать в диалоговом окне.
Правила отбора → Выше среднего и ниже среднего...
Аналогичным образом работают "Последние 10" и "Последние 10%". Только условиями для форматирования являются минимальные элементы.
Выделяются ячейки с самым большим значением, которые составляют 10% от общей суммы. Долю можно регулировать в диалоговом окне.
Гистограммы
Позволяют в каждую ячейку с числом добавить столбец линейной гистограммы, размер которой определяется относительно максимального значения в выделенном диапазоне.
Применяется аналогично всем вышеперечисленным.
Цветовые шкалы
Автоматически определяют максимальное и минимальное значение в диапазоне и форматирует каждую ячейку по цвету, который соответствует значению, изображая что-то вроде тепловой карты:
На рисунке видно, что максимальному значению присвоен зеленый цвет, а минимальному - красный. Все значения, которые находятся в диапазоне от минимального к максимальному меняют оттенок заливки в зависимости от приближенности значения к максимальному или минимальному элементу таблицы.
Наборы значков
Для каждой ячейки присваивается значок в соответствии с выбранным стилем и весом значения ячейки:
2
Рассмотрим использование формул для определения правил условного форматирования
Вернемся к кейсу, который мы начали рассматривать в самом начале.
Имеется отчет об остатках склада. Для каждой номенклатуры есть норма минимального остатка на складе.
Для своевременного пополнения складских запасов нужно контролировать данные в этой таблице, просматривая ее содержимое.
- Выделяем область для применения условий форматирования (столбец "В")
- Переходим на вкладку "Главная", выбираем "Сортировка" и далее "Создать правило"
- В появившемся окне выбираем тип правил "
- В нижней части окна необходимо произвести необходимые настройки
-
Вводим формулу
- Уберите знак "$" из формулы, нужно чтобы ячейки имели относительную адресацию для всего диапазона форматирования
С правилами условного форматирования все становится намного проще: мы сразу видим позиции, которые требуют заказа:
КЕЙС 5
Сортировка данных в таблицах
Обзор правильного упорядочивания информации в таблицах с помощью сортировки.