Подсветка дат и сроков
Простой способ
Выделите диапазон с датами на листе и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Дата (Home – Conditional Formatting – Highlight Cell Rules – Date Occuring). В открывшемся окне выберите из выпадающего списка нужный вариант подсветки:
Сложный, но красивый способ
Теперь давайте разберем задачку посложнее и поинтереснее. Предположим, что у нас есть большая таблица поставок каких-либо товаров:
Обратите внимание на дату отгрузки. Если она в прошлом, то товар уже поставлен – можно не волноваться. Если она в будущем – значит мы должны держать вопрос на контроле и не забыть организовать поставку к указанному сроку. И, наконец, если дата отгрузки совпадает с сегодняшней, то надо бросать все дела и заниматься именно этой партией в данный момент (наивысший приоритет).
Для наглядности, можно настроить три правила условного форматирования, чтобы автоматически заливать всю строку с данными по партии в разные цвета в зависимости от даты отгрузки. Для этого выделим всю таблицу (без шапки) и выберем на вкладке Главная – Условное форматирование – Создать правило (Home – Conditional Formatting – Create Rule). В открывшемся окне зададим последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и введем в поле следующую формулу:
Эта формула берет последовательно содержимое ячеек E5, E6, E7… из столбца с датой отгрузки и сравнивает эту дату с сегодняшней датой из ячейки C2. Если дата отгрузки раньше, чем сегодняшняя, то отгрузка уже была. Обратите внимание на знаки доллара, используемые для закрепления ссылок. Ссылка на $C$2 должна быть абсолютной – с двумя знаками доллара. Ссылка на первую ячейку столбца с датой отгрузки должна быть с закреплением только столбца, но не строки, т.е. $E5.
После ввода формулы можно задать цвет заливки и шрифта, нажав на кнопку Формат (Format) и применить потом наше правило, нажав на кнопку ОК. Затем повторить всю процедуру для проверки будущих поставок и поставок на текущий день. Для отгруженных партий можно, например, выбрать серый цвет, для будущих заказов – зеленый и для сегодняшних – срочный красный:
Вместо текущей даты можно вставить в ячейку С2 функцию СЕГОДНЯ (TODAY), которая будет обновлять дату каждый раз при открытии файла, что будет приводить к автоматическому обновлению цветов в таблице.
Если подобная подсветка нужна не всегда, а только на определенное время работы с таблицей, то можно добавить к уже сделанному еще и, своего рода, выключатель. Для этого откройте вкладку Разработчик (Developer). Если ее не видно, то сначала включите ее через Файл – Параметры – Настроить ленту и нажмите кнопку Вставить (Insert):
В открывшемся списке инструментов выберите Флажок (Checkbox) из верхнего набора Элементы управления формы и щелкните по тому месту листа, где хотите его разместить. Затем можно задать размеры надписи и поменять ее текст (правой кнопкой мыши – Изменить текст):
Теперь, чтобы использовать флажок для включения-выключения подсветки, нужно связать его с любой ячейкой на листе. Щелкните правой кнопкой мыши по нарисованному флажку и выберите в контекстном меню команду Формат объекта (Format Object) и затем в открывшемся окне задайте любую подходящую ячейку в поле Связь с ячейкой (Cell Link):
Проверьте как все работает. В связанную ячейку Е2 должно выводиться значение ИСТИНА, когда флажок включен или ЛОЖЬ, когда он выключен.
Теперь осталось добавить одно правило в условное форматирование, чтобы наш флажок включал-выключал подсветку дат. Выделите всю нашу таблицу (кроме шапки) и откройте на вкладке Главная - Условное форматирование - Управление правилами (Home - Conditional Formatting - Manage Rules). В открывшемся окне должны быть хорошо видны созданные нами ранее правила для подсветки прошлых, будущих и настоящих дат разными цветами:
Жмем кнопку Создать правило (New Rule), выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и вводим в поле следующую формулу:
Формат не задаем и нажимаем ОК. Созданное правило должно добавится к общему списку. Теперь необходимо поднять его на первую строчку стрелками (если оно не еще не там) и включить напротив него справа флажок Остановить если истина (Stop If True):
Параметр с малопонятным названием Остановить, если истина делает простую вещь: если правило, напротив которого он стоит, выполняется (т.е. наш флаг Подсветка сроков на листе выключен), то Microsoft Excel останавливает дальнейшую обработку правил, т.е. не переходит к следующим правилам в списке условного форматирования и не заливает таблицу. Что и требуется.
Ссылки по теме
- Условное форматирование в Excel 2007-2013 (видео)
- Полосатая заливка строк таблицы "зеброй"
- Как Excel на самом деле работает с датами и временем
Николай, подскажите пожалуйста. На примере заливки "будущих отгрузок" - те, что зеленым цветом,
Как можно задействовать "денежные суммы", именно в выделеных строках, к примеру суммировать выделенные "затраты" или суммировать "общую массу" в отдельной ячейке?
Николай скажите, можно ли справа от таблицы сделать 4 кнопки, отдельно каждая, чтобы при нажатии на нее можно было показывать только красные строки, нажав на синюю кнопку только синюю и т.д.? Чтобы не открывать фильтр и там выбирать фильтр по цвету. Свести все управление к вставленным кнопкам. И еще одна кнопка чтоб фильтровать даты по очереди, т.к. в таблице они записаны все в разнобой.
А макрос большой может получиться?
Подскажите пожалуйста, можно ли установить форматирование строки по условию в одной ячейке? Например, если в ячейке "сп", то строка "бледно-оранжевая", если "зп", то строка серая, если "п", то строка розовая и т.д. И так по всей таблице.
вопрос по красивому способу. есть бланк заявок в котором даты окончание каждого этапа производства рекламы (заявка/макет/согласование/утверждение/изготовление/установка). так вот хотелось чтоб например если уже неделю по заявке ничего не происходит, то отметить желтым. две недели - оранжевым. месяц - красным. как это реализовать?
- я бы искал с помощью функции МАКС самую последнюю дату активности по проекту
- проверял бы вычитанием СЕГОДНЯ-МАКС сколько дней прошло с момента последней активности
- если это количество больше 14 - ставил оранжевый, если больше 30 - красный и т.д. с помощью условного форматирования.
Как-то такНиколай, подскажите, пожалуйста, будет ли работать подсветка дат и сроков, если таблица будет "умной" (динамической)?
Подскажите пожалуйста, возможно ли задать условие для форматирования вида =$F$4<($C$1+1),
где $C$1- это соответственно сегодняшняя дата,
$F$4 - текущая ячейка с предполагаемой датой отгрузки.
Необходимо сделать так, чтобы интервал от 3-5 дней до отгрузки от текущей даты, подсвечивался определенным цветом для отслеживания этапа отгрузки (второй, сложный тип). Соответственно, только ли создавая каждый раз условие можно добиться нужного эффекта или же можно задать интервал в условии (или же двойное условие).
Честно говоря не понял как сделать написанное тут:
т.е. условие -- "просто меньше". "На сколько меньше" (на день, два, три, семь...) это условие сведений не содержит. Как тогда "повторить всю процедуру"? Несколько раз с одной и той же формулой, но разным форматированием? Не понимаю...
Ishu ne mogu nayti otvet vot na kakoy vopros.
Vverxu kajdogo stolbika oboznachena data. I tak na 1 god. Sprava 2 stolbsa nedvigayushiyesya.
Kak sdelat chtob kogda otkrivat tablisu kajdiy den avtomaticheski otkrivalya stolbik s tekushey datoy. Spasibo.
Pojaluysta otvette.
Подскажите пожалуйста, как создать форматирования по времени. По часам то есть.
У меня вопрос: Как сделать подстветку нескольких ячеек по вертикали, синхронизировать их, чтобы они закрашивались в соответствующей ячейке? Я ввожу в условное форматирование такую формулу "=C4=$AJ$4:$AJ$32", между AJ4:AJ32 у меня даты, но проблема в том, что подсвечивается только AJ4, остальные не срабатывают. В чём может быть проблема?
Помогите сделать заливку цветом среднего значения оценок в зависимости от статуса ученика