Выделение даты цветом в excel

Простой способ

Выделите диапазон с датами на листе и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Дата ( 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 останавливает дальнейшую обработку правил, т.е. не переходит к следующим правилам в списке условного форматирования и не заливает таблицу. Что и требуется.

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо.

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хорошо приспособлена для больших информационных баз. Практика показала, что дальше заполнения таблиц доходит редко. Таблица растет, информации становится больше и возникает необходимость быстро выбрать только нужную. В подобной ситуации встает вопрос как в Excel выделить ячейку цветом при определенном условии, применить к строкам цветовые градиенты в зависимости от типа или наименования поставщика, сделать работу с информацией быстрой и удобной? Подробнее читаем ниже.

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

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

С помощью этого набора инструментов делают следующие выборки:

• находят в таблице числовые значения, которые больше установленного;
• находят значения, которые меньше установленного;
• находят числа, находящиеся в пределах заданного интервала;
• определяют значения равные условному числу;
• помечают в выбранных текстовых полях только те, которые необходимы;
• отмечают столбцы и числа за необходимую дату;
• находят повторяющиеся значения текста или числа;
• придумывают правила, необходимые пользователю.

Посмотрите, как ищется выбранный текст: в первом поле задается условие, а во втором указывают, каким образом выделить полученный результат. Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Если хочется применить иные оттенки – сделать это можно перейдя в «Пользовательский формат». Аналогичным образом реализуются все «Правила выделения ячеек».

Очень творчески реализуются «Другие правила»: в шести вариантах сценария придумывайте те, которые наиболее удобны для работы, например, градиент:

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

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

Рассмотрим вторую группу функций «Правила отбора первых и последних значений». В ней вы сможете:

• выделить цветом первое или последнее N-ое количество ячеек;
• применить форматирование к заданному проценту ячеек;
• выделить ячейки, содержащие значение выше или ниже среднего в массиве;
• во вкладке «Другие правила» задать необходимый функционал.

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

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

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

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

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

С помощью вкладки «Удалить правило» созданные сценарии удаляются со всего листа, из выбранного диапазона значений, из таблицы.

Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.

Отбор ячеек по датам

Чтобы разобраться, как в excel сделать цвет ячейки от значения установленной даты, рассмотрим пример с датами закупок у поставщиков в январе 2019 года. Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число». Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

Для отбора нужных дат применяем такую последовательность действий:

• выделяем столбцы с датами (в нашем случае за январь);
• находим инструмент «Условное форматирование»;
• в «Правилах выделения ячеек» выбираем пункт «Дата»;
• в правой части форматирования открываем выпадающее окно с правилами;
• выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
• в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
• выборка окрасилась, жмем «ОК».

С помощью форматирования ячеек, содержащих дату, можно выбрать значения по десяти вариантам: вчера/сегодня/завтра, на прошлой/текущей/следующей неделе, в прошлом/текущем/следующем месяце, за последние 7 дней.

Выделение цветом столбца по условию

Для анализа деятельности фирмы с помощью таблицы разберем на примере как поменять цвет ячейки в excel в зависимости от условия, заданного работником. В качестве примера возьмем таблицу заказов за январь 2019 года по десяти контрагентам.

Нам необходимо пометить синим цветом тех поставщиков, у которых мы купили товара на сумму большую, чем 100 000 рублей. Чтобы сделать такую выборку воспользуемся следующим алгоритмом действий:

• выделяем столбец с январскими закупками;
• кликаем инструмент «Условное форматирование»;
• переходим в «Правила выделения ячеек»;
• пункт «Больше…»;
• в правой части форматирования устанавливаем сумму 100 000 рублей;
• в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
• необходимая выборка окрасилась в синий цвет, жмем «ОК».

Инструмент «Условное форматирование» применяется для решения ежедневных задач бизнеса. С его помощью анализируют информацию, подбирают необходимые компоненты, проверяют сроки и условия взаимодействия поставщика и клиента. Пользователь сам придумывает нужные для него комбинации.

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

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

Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel

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

Чтобы составить аналогичный отчет с таким же автоматическим форматированием ячеек по условию выполните следующее:

1. Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
2. В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
3. В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.

А в результате выделились все даты актуальность которых превышает 90 дней.