Как выделить дату цветом в excel
Можно ли в Excel автоматически выделить текущий день, неделю или месяц в диапазоне данных? По умолчанию Excel предоставляет функцию условного форматирования, чтобы помочь вам решить эту задачу, как вы могли бы применить это условное форматирование?
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Выделите дату текущего дня / недели / месяца в ячейке с условным форматированием
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вам просто нужно выделить ячейку текущего дня, недели или месяца, вы можете сделать это, выполнив следующие простые шаги:
1. Выберите диапазон данных, в котором вы хотите выделить конкретную дату.
2. Нажмите Главная > Выделите правила ячеек > Дата наступления, см. снимок экрана:
3. И в Дата наступления В диалоговом окне в первом раскрывающемся списке выберите дату, которую вы хотите выделить, а во втором раскрывающемся списке выберите один цвет, который вы хотите отформатировать для определенной даты, см. снимок экрана:
4. Затем нажмите OK, ячейка с сегодняшней датой была выделена, как показано на следующем снимке экрана:
Выделите дату текущего дня / недели / месяца в строке с условным форматированием
1. Выберите диапазон данных или целые строки данных, за исключением заголовков, если они есть.
2. Нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:
3. А потом в Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант, затем введите эту формулу = сегодня () - день недели (сегодня (), 3) = $ A2-будний день ($ A2, 3) в Значения формата, в которых эта формула верна поле, чтобы выделить дату текущей недели. (A2 указывает на первую ячейку даты, которую вы использовали, вы можете заменить ее по своему усмотрению) См. снимок экрана:
Чаевые: Чтобы выделить сегодняшнюю дату, примените эту формулу: = $ A2 = СЕГОДНЯ () ;
Чтобы выделить дату текущего месяца, введите следующую формулу: = ТЕКСТ ($ A2, "ммгг") = ТЕКСТ (СЕГОДНЯ (), "ммгг") .
4. После ввода формулы нажмите Формат кнопку в Формат ячеек диалоговом окне выберите один цвет, который вам нравится Заполнять меню.
5. Затем нажмите OK > OK чтобы закрыть диалоги. И строки, содержащие дату текущей недели, отформатированы. Смотрите скриншот:
Внимание: В этом диапазоне данных форматирование будет изменяться автоматически при изменении или добавлении записей дат.
Во многих фирмах отдельное внимание уделяется датам, выпадающим после определенного пройденного периода. С помощью условного форматирования можно легко составить отчет «После периода» на котором выделены пройденные даты.
Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel
Пример представлен ниже на рисунке в виде отчета, в котором даты за более чем 90 дней от текущей даты выделенные другим цветом заливки.
Чтобы составить аналогичный отчет с таким же автоматическим форматированием ячеек по условию выполните следующее:
- Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
- В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
- В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
А в результате выделились все даты актуальность которых превышает 90 дней.
Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный - зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких - серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя - зеленым. И так далее - насколько фантазии хватит.
Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат - Условное форматирование (Format - Conditional formatting) .
В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format) , параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники - желтым, а неуспевающие - красным цветом:
Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях - бесконечно.
Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.
Другой, гораздо более мощный и красивый вариант применения условного форматирования - это возможность проверять не значение выделенных ячеек, а заданную формулу:
Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать - другие.
Выделение цветом всей строки
Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе - он фиксирует столбец, оставляя незафиксированной ссылку на строку - проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:
Выделение максимальных и минимальных значений
Ну, здесь все достаточно очевидно - проверяем, равно ли значение ячейки максимальному или минимальному по диапазону - и заливаем соответствующим цветом:
В англоязычной версии это функции MIN и MAX, соответственно.
Выделение всех значений больше(меньше) среднего
Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:
Скрытие ячеек с ошибками
Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR) , которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:
Скрытие данных при печати
Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати - делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение ("да", "нет"):
Заливка недопустимых значений
Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF) , которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:
Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю - желтым:
Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования - заливку ячеек цветовыми градиентами, миниграфики и значки:
Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью. :)
Достаточно малоизвестный инструмент Excel, но это как раз тот инструмент при помощи которого можно изменить форматирование ячейки(цвет заливки, шрифт, границы) в зависимости от условия, не прибегая к помощи VBA. Например, у нас имеется таблица данных, в одном столбце которой находятся даты и хотелось бы, чтобы при открытии файла ячейка с текущей датой сама выделялась цветом. Допустим это столбец B . Для этого:
- выделяем столбец с датами
- Excel 2003 : Формат (Format) -Условное форматирование (Conditional formatting) ;
- Excel 2007-2010 : вкладка Главная (General) -Условное форматирование (Conditional formatting) -Создать правило (New rule)
- выбираем формула; для Excel 2007 : Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)
- вписываем в поле условие - = $B2 =СЕГОДНЯ()
- выбираем способ форматирования ячеек(в примере цвет заливки - красный)
- жмем Ок.
Если Вам необходимо выделять не только ячейку с датой, а всю строку таблицы, то в пункте 1 выделяем не столбец, а всю таблицу.
Tips_FC_Date.xls (30,0 KiB, 8 783 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
ВидеоурокиПоиск по меткам
Александр, написано же красным специально: Если есть вопрос по проблеме в Excel- добро пожаловаться на Форум.
Ваш вопрос к статье отношения не имеет и будет удален. Уже во второй раз. На третий просто занесу в список спама.
Существует ли в Excel возможность в ячейку "вбить" любую необходимую дату не традиционным способом, а через возможность высвечивания в данной ячейке календарика?
Юрий, только через написание кода.
Я пользуюсь своим: Быстрый календарь
Еще поищите в интернете надстройку SamradDatePicker
Спасибо, очень полезная статья!
А можно ли применить выделение даты, но при этом в нужной строке указано лишь сегодняшнее число? Например, 4
@Дмитрий
Прошу прощения, разобрался сам. Формула =ДЕНЬ(СЕГОДНЯ())
Можно ли в условном форматировании сравнивать колонку D и только одну ячейку F1? Если в D у меня даты любые , а в F1 сегодняшняя дата. Спасибо
=$F$1=D1
подробнее можно прочитать, набрав в поиске относительные и абсолютные ссылки.
Читайте также: