Добрый день уважаемый пользователь Microsoft Excel!
Темой статьи будет возможность подсветить сроки и даты в ячейках Excel, которые будут указаны в условиях как критические. Это необходимо когда выполнение любых условий привязанных к датам являются очень важными.
Статья будет полезна тем, кому важны сроки окончания действий договоров, для их перезаключения, проведения тендеров, тут вообще необходимо жёсткое соответствие срокам, отслеживания дат отгрузок товаров согласно соглашениям, ведение строгой и простой отчётности и т.п… Как видите, сфера применения очень обширна и данная возможность пригодится многим юристам, работникам кадровых служб, экономистам и бухгалтерам.
Давайте рассмотрим на практике два способа применения подсветки с помощью условного форматирования:
- С помощью правил выделения ячеек;
- С помощью правил условного форматирования и элементов управления форм.
Подсветка дат и сроков с помощью правил выделения ячеек
Это самый простой способ, который возможно осуществить с помощью нескольких кликов. Для начала необходимо выделить нужный диапазон с вашими датами, на панели управления во вкладке «Главная», выбрать выпадающее меню «Условное форматирование», выбрать в списке «Правила выделения ячеек», далее выбрать тип «Дата…».
Следующим шагом вы в диалоговом окне «Дата» в выпадающем списке указываем условия выделения, например, выбрать все даты «На текущей неделе» и выберем для них красный цвет.
Подсветка дат в ячейках с помощью условного форматирования и элементов управления форм
Этот способ будет более сложным, конечно, но и лучше, и красочнее, и интересней. Рассмотрим наш пример на основе отдела продаж и плану отгрузки товаров для клиентов, выполнение, которого очень строго и обязательно для исполнения, если необходима кристальная репутация и деньги.
Создадим таблицу отгрузки товаров:
Главным аргументом в работе будут даты отгрузки, когда в списке дата уже прошла, значит, товар поставлен и всё у вас хорошо. Когда же дата еще не наступила, вам необходимо контролировать этот процесс и сделать отгрузку в указанный период. А вот если дата отгрузки и сегодняшняя дата совпадают, тогда бросайте всё и бегите немедленно заниматься срочной отгрузкой, так как ее приоритет в работе будет максимальной.
Для наибольшей наглядности настроим несколько правил для работы условного форматирования, чтобы происходила заливка строки таблицы в определённые цвета в зависимости от указанных дат поставки.
Для начала выделим созданную таблицу и на вкладке «Главная» выбираем в выпадающем списке «Условное форматирование» пункт «Создать правило». В открывшемся диалоговом окне выбираете правило «Использовать формулу для определения форматируемых ячеек» и вводим формулу: =$D5<$C$2.
Введенная формула будет производить последовательное сравнение ячеек с датами отгрузок с текущей датой в ячейке С2. В случае, когда указанная дата была раньше текущей, отгрузка уже произведена. В обязательном порядке нужно использовать знак «$» для создания абсолютной ссылки, так как иначе результат будет неправильным.
Следующий шаг это визуализация данных с помощью цвета. Нажимаете кнопку «Формат» и задаем цвет шрифта и заливки, кликаем на «ОК». Аналогично создаем еще два правила, но с разными цветовыми критериями, для поставок сегодня и в будущем. Для отгруженных партий можно использовать серый цвет, неактивный, для будущих отгрузок — зелёный цвет и для срочных сегодняшних отгрузок — красный цвет приоритета.
Для улучшения и автоматизации таблицы в ячейку С2 вводим функцию СЕГОДНЯ, а не проставляем дату вручную, это позволит при каждом открытии файла сверяться с системным временем и обновлять информацию в таблице и ее цвет. Выглядеть ваша форма будет так:
Кода такая иллюминация файлов вас не привлекает или не надо вам постоянно, то есть возможность прикрепить к таблице переключатель видов. На вкладке
«Разработчик» (более подробно о работе с ней описано в статье работы с макросами), в блоке «Элементы управления», в выпадающем меню кнопки «Вставить», выбираем элемент «Флажок» и размещаем его в удобном для вас месте.
Кликаем на рамке элемента и в контекстном меню выбираем пункт «Изменить текст» и вводите тот, который вам будет понятным.
Следующим шагом будет привязка к любой ячейке на рабочем листе флажок, который будет включать или выключать подсветку дат. Для этого кликаете мышкой на элемент и вызываете контекстное меню формы, выбираете пункт «Формат объекта» и затем в диалоговом окне «Формат элемента управление» во вкладке «Элемент управления» в поле «Связь с ячейкой» указываем любую ячейку. Рекомендую указывать привязку в пустой строке, а потом с помощью меню попросту скрыть ненужную строку.
Теперь, как видите, в ячейку, которая связана с формой, при включенном флажке выводится результат ИСТИНА, если флажка нет, показывает значение ЛОЖЬ.
Последним шагом будет добавление еще одного правила в перечень правил условного форматирования для работы флажка переключения подсветки сроков и дат в ячейках Excel. Снова выделяете весь диапазон вашей таблицы на вкладке «Главная» кликаете кнопку «Условное форматирование» и в выпадающем меню выбираете пункт «Управление правилами» и видите перечень ранее созданных правил:
Создаем наше последнее правило, кликаете кнопочку
«Создать правило» выбираете тип правила «Использовать формулу для определения форматируемых ячеек» и прописываем в поле формулу: =$F$2<>ИСТИНА.
Формат указывать не надо, так как таблица должна выглядеть обыкновенной, нажимаем кнопочку «ОК» добавляем новое правило в общий список. В случае если новое правило не возглавило список, то необходимо стрелочками его туда пропихнуть и установить флажок в столбике «Остановить если истина».
Указание последнего параметра необходимо для того, чтобы, когда ваше правило выполнялось (выключен флажок «Включить индикатор строк»), Excel автоматически отключал все нижестоящие правила условного форматирования и таблица остается, девственно чиста от заливок, чего, собственно вы и добивались!
