Как выделить праздники в Excel

Обновлено: 21.11.2024

С помощью функций даты в Excel можно выполнять такие вычисления, как сложение или вычитание, и, таким образом, создавать автоматизированные или полуавтоматические рабочие листы (в частности, используя функцию СЕЙЧАС). Функции даты в сочетании с условным форматированием позволяют создавать электронные таблицы, автоматически отображающие оповещения о дате, когда приближается крайний срок.

Основы условного форматирования дат

В меню Главная/Условное форматирование/Правила выделения ячеек > Дата возникновения. вы можете создать следующие правила:

  • Вчера
  • Сегодня
  • Завтра
  • За последние 7 дней
  • На прошлой неделе
  • На этой неделе
  • На следующей неделе
  • Последний месяц
  • В этом месяце
  • В следующем месяце

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

Выделение выходных

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

Чтобы изменить цвет выходных, откройте меню «Условное форматирование» > «Новое правило».

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

В текстовом поле "Форматировать значения, в которых эта формула верна" введите следующую формулу ДЕНЬ НЕДЕЛИ, чтобы определить, является ли ячейка субботой (6) или воскресеньем (7).

Параметр 2 означает, что суббота = 6, а воскресенье = 7. Этот параметр очень удобен для тестирования на выходных

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

Затем вы настраиваете формат условия, нажимая кнопку "Формат" и выбирая цвет заливки (в данном примере оранжевый)

Нажав OK, откройте «Условное форматирование» > «Управление правилами»

Выберите этот параметр, чтобы визуализировать правила рабочего листа вместо выбора по умолчанию. В разделе Применимо к измените диапазон, который соответствует вашему первоначальному выбору при создании правил, чтобы распространить его на весь столбец.

Измените значения месяца и года в следующей таблице. В выходные дни вы увидите другой цвет.

Выделение государственных праздников

Чтобы обогатить предыдущую книгу, вы также хотите раскрасить праздничные дни. Для этого у вас должен быть столбец государственных праздников в вашей стране в столбце вашей рабочей тетради (но не обязательно на том же листе). В нашем примере у нас есть государственные праздники США в столбце AH (по отношению к год в ячейке B2.)

Еще раз воспользуемся формулой с условным форматированием. В этом случае мы используем формулу СЧЁТЕСЛИ, чтобы подсчитать, превышает ли количество государственных праздников в текущем месяце 1.

Затем в диалоговом окне "Управление правилами" выберите диапазон B4:AF11. Если вы хотите выделить праздничные дни среди выходных, переместите правило праздничных дней в начало списка.

Выделение задержек

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

В следующем примере мы показываем

  • желтые даты между 1 и 2 месяцами
  • оранжевый, возраст от 2 до 3 месяцев.
  • фиолетовый, даты старше 3 месяцев

Затем мы создаем три правила условного форматирования, используя формулу DATEDIF . Соответственно, для трех случаев у вас будут следующие формулы:

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

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

Сначала мы переходим в новый столбец (столбец E), снова вычисляем разницу в количестве дней в году с помощью формулы DATEDIF и параметра "yd". =DATEDIF($D2,СЕГОДНЯ(),"гд")

Затем мы выбираем пункт меню «Условное форматирование»> «Новое правило». Отформатируйте все ячейки на основе их значения и выберите следующие параметры:

  • Масштабировать 3 цвета
  • Минимум, 0 красных
  • Медиана, 10 желтых
  • Максимум – 30 белых.

В результате получается градиентная цветовая шкала с оттенками от белого до красного и желтого. Чем ближе к 0, тем больше красного, чем ближе к 10, тем больше желтого, а чем ближе к 30, тем больше белого.

Нередко приходится работать с датами в Excel. Будь то диаграмма Ганта для управления проектами, реестр или просто календарь, мы бы хотели, чтобы Excel выделял нерабочие даты. Одной из самых распространенных задач должно быть выделение выходных и праздничных дней. Это может быть утомительной ручной задачей, когда вам нужно поддерживать ее ежемесячно. Хорошая новость заключается в том, что эту утомительную и скучную задачу можно легко выполнить с помощью условного форматирования.

Оставьте скучные задачи в Excel и получайте больше удовольствия от жизни. 🙂

Формула для определения дня недели

На мой взгляд, для этого есть две функции: ТЕКСТ и ДЕНЬ НЕД.

Действительно, я предпочитаю использовать функцию ТЕКСТ, поскольку возвращаемый ею результат более интуитивно понятен.

См. ниже, чтобы узнать, что я имел в виду под «более интуитивным».

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

Нам понадобится формула для настройки условия. И формула также интуитивно понятна:

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

  1. Выберите диапазон (в нашем примере A2:G11)
  2. Перейти на вкладку "Главная".
  3. Условное форматирование
  4. Новое правило…

Когда откроется диалоговое окно «Новое правило форматирования»:

  1. Выберите «Использовать формулу для определения форматируемых ячеек».
  2. Введите формулу =ТЕКСТ($A2,"DDD")="Сб"
  3. Установите нужный формат (при соблюдении этого условия)

Выделена строка с субботой(-ами). Ага!

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

Тогда вы должны получить следующий результат:

Теперь давайте посмотрим, какие условия были применены:

  1. Правило (применяется в указанном порядке): существует два правила, основанные на формуле, которую мы установили (примечание: если у вас есть другие правила, вы также увидите их здесь).
  2. Формат: формат, установленный при соблюдении правила.
  3. Применяется к: диапазон, к которому применяются правило и формат.

Совет. Поскольку мы хотим выделить и субботу, и воскресенье в одном формате, мы можем объединить два правила в одно, используя следующую формулу:

Функция ИЛИ указывает Excel возвращать ИСТИНА при выполнении одного из условий.

Чтобы выделить праздничные дни

Для этого мы должны указать в таблице государственные праздники. (В разных регионах разные праздничные дни… и настоящее препятствие заключается в том, что в разных отраслях могут быть разные праздничные дни, поэтому в Excel нет встроенной функции для определения государственных праздников).

В нашем примере праздничные дни указаны в таблице $I$2:$I$18

Формула условного форматирования для выделения государственных праздников

Эта формула подсчитывает вхождение A2 (дата) в диапазоне $I$2:$I$18 (праздничные дни). Другими словами, если это государственный праздник (как указано в PublicHolidays), возвращается 1 (ИСТИНА). Если это не государственный праздник (не указанный в PublicHolidays), возвращается 0 (FALSE). Это именно то, что нам нужно для настройки условного форматирования. 🙂

Давайте добавим это правило, повторив шаги с формулой:

(Примечание: для праздничных дней используется другой формат, чтобы продемонстрировать другую концепцию, которую я собираюсь объяснить)

Давайте проверим это, изменив даты в столбце A

Дааааааааааааааааааааааааааааааа! 🙂

Вопрос. Что делать, если государственный праздник выпадает на субботу? Какие форматы он будет отображать?

Для наглядности я отметил 2 марта (субботу) как государственный праздник. И вы можете ожидать, что он будет выделен как государственный праздник, и это правда.

Давайте рассмотрим порядок правил, заданных в условном форматировании:

Обратите внимание на то, что выделено желтым цветом на скриншоте выше.

Что, если мы поменяем порядок двух правил? Смотрите это:

Видишь?! Порядок правил имеет значение, когда они перекрываются. Первым прибыл - первым обслужен Эквивалент в русском языке: поздний гость гложет и кость. 🙂

Пожалуйста, загрузите образец файла, чтобы следовать ему и практиковаться. Во время практики вы обнаружите…

Условное форматирование с формулой может быть сложным (или трудным), особенно если макет данных неудачный

потому что вам нужно очень четко и осторожно относиться к диапазону «применимо к» и абсолютным/относительным ссылкам, установленным в формуле. В противном случае это не сработает и может сильно запутать, если не разочаровать. 😛

В следующем посте я расскажу о случае, когда небольшое изменение в макете (см. ниже) повлияет на настройку условного форматирования.

где пустая строка вставляется между каждыми данными по каким-то причинам…

Сьюзан Харкинс показывает, как объединить функцию и условное форматирование, чтобы выделить выходные и праздничные дни с помощью Excel.

Выходные и праздничные дни важны для большинства из нас. Помимо удовольствия от свободного времени, мы должны учитывать его при планировании проектов. Функции ЧИСТРАБДНИ() и ЧИСТРАБДНИ.МЕЖД() в Excel возвращают количество дней, исключая праздники и выходные, между двумя датами. Интересно, что вы также можете использовать эти функции для возврата 1 и 0, что соответствует TRUE и FALSE соответственно. После этого можно легко применить условный формат, который выделяет выходные и праздничные дни в списке дат.

Я использую Excel 2007 в системе Windows 7, но все ленточные версии поддерживают функцию ЧИСТРАБДНИ(), используемую в этом методе. Вы можете работать со своими данными или скачать демонстрационный файл .xlsx или .xls.

О ЧИСТРАБДНИ()

Функция ЧИСТРАБДНИ() в Excel возвращает количество дней между двумя датами, исключая выходные и праздничные дни. Эта функция использует следующий синтаксис:

ЧИСТРАБДНИ(начало, конец, праздники)

где начало и конец представляют собой первый и последний дни периода, который вы считаете, а праздники – это диапазон, который относится к списку дат праздников.

В Excel 2003 функция ЧИСТРАБДНИ() доступна через надстройку Analysis ToolPak, которую можно включить следующим образом:

  1. Выберите «Надстройки» в меню «Инструменты».
  2. Выберите «Пакет анализа» и нажмите «ОК».

Данные

Простой набор данных, показанный на рис. A, содержит список дат и таблицу известных праздников. Самая большая проблема этого метода заключается в том, что вы должны указывать праздники и ссылаться на них как на диапазон. Этот маршрут дает вам контроль над праздниками и требует лишь немного дополнительных усилий. Примерный список праздников основан на США; вы захотите настроить его в соответствии с потребностями вашей организации.

Рисунок А

В этом наборе данных мы будем выделять выходные и праздничные дни.

Список праздников представляет собой таблицу, поэтому вы можете автоматически обновлять ее, не обновляя функцию ЧИСТРАБДНИ(), которую мы добавим чуть позже. Список дат в столбце B не является таблицей, но вполне может ею быть. В этом случае это не будет иметь никакого значения для функции, которую мы собираемся исследовать.Если вы используете Excel 2003, вы не можете преобразовать свой список праздников в таблицу, поэтому вам придется использовать динамический диапазон или обновить функцию ЧИСТРАБДНИ(), если вы добавите новые даты праздников.

Если вы не знаете, как создать таблицу, вот как это сделать:

  1. Нажмите внутри набора данных.
  2. Перейдите на вкладку "Вставка".
  3. Нажмите «Таблица» в группе «Таблицы».
  4. В появившемся диалоговом окне установите (или нет) флажок «Моя таблица имеет заголовки» (рис. B) и нажмите «ОК».
    Рисунок Б

    5. Теперь давайте продолжим, введя функцию ЧИСТРАБДНИ() в C4. Если вы используете объект Table для праздников, используйте следующую функцию:

    Если вы используете Excel 2003, введите диапазон праздничных дней:

    После входа в функцию скопируйте ее в оставшиеся даты, как показано на рисунке C. Как видите, эта функция возвращает 0 для дат, выпадающих на субботу и воскресенье, и 1 для дат, выпадающих с понедельника по пятницу. Вы также можете заметить, что две даты рабочих дней, 1 января 2016 г. и 18 января 2016 г., являются рабочими днями, но функция возвращает 0. Эти даты перечислены в списке выходных дней в столбце F. Поскольку цель этой функции — подсчет рабочих дней, это также исключает праздничные дни.

    Рисунок C

    Копировать ЧИСТРАБДНИ().

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

    Условный формат

    Теперь пришло время применить правило условного форматирования, которое будет выделять все выходные и праздничные дни в вашем списке дат в столбце B:

    1. Выберите B4:B49 (вы хотите выбрать список дат, к которым вы хотите применить условный формат).
    2. В группе "Стили" (на вкладке "Главная") нажмите "Условное форматирование" и в раскрывающемся списке выберите "Новое правило".
    3. В открывшемся диалоговом окне выберите параметр Использовать формулу для определения форматируемых ячеек на верхней панели.
    4. Введите выражение =$C4=0 и нажмите "Формат".
    5. Перейдите на вкладку "Заливка", выберите цвет и нажмите "ОК". На этом этапе вы можете увидеть правило и формат (рисунок D).
      Рисунок D
    6. Нажмите "ОК", чтобы вернуться к рабочему листу.

      7. На рис. E показано условное форматирование в действии. Все субботы, воскресенья и праздничные дни (как указано в столбце F) выделены красным цветом (или цветом, выбранным на шаге 5).

      Рисунок Д

      Выделите выходные и праздничные дни.

      Вы можете добавить функцию в правило условного форматирования (шаг 4) вместо добавления функции на лист (добавление на лист позволяет увидеть, как работает функция). Если вы хотите опустить столбец C, используйте следующее выражение на шаге 4:

      Пользовательские выходные

      Функция ЧИСТРАБДНИ() в Excel 2007 определяет дату выходного дня как субботу или воскресенье. Это не всегда будет работать для каждой организации. Excel 2010 и более поздние версии предлагают вторую функцию, позволяющую указать, какие дни являются выходными. ЧИСТРАБДНИ.МЕЖД() использует следующий синтаксис:

      NETWORKSDAYS.INTL(начало, конец, [выходные], [праздники])

      где выходные — одно из целых чисел в таблице А. Например, если ваши выходные состоят из воскресенья и понедельника, вы должны использовать значение аргумента 2.

      Таблица А

      Отправьте мне свой вопрос об Office

      Опубликовано: 28 сентября 2015 г., 6:13 по тихоокеанскому времени. Изменено: 25 сентября 2015 г., 18:29 по тихоокеанскому времени. Подробнее о программном обеспечении

      С помощью инструмента условного форматирования вы можете автоматически изменять цвет ячеек.

      Вы можете использовать настройку по умолчанию, но вы также можете создать собственное правило с формулами.

      Список государственных праздников

      В следующем документе приведен список государственных праздников в США

      Формула для поиска государственных праздников

      В следующем календаре указаны дни июля 2019 года. Цвета выходных задаются правилом условного форматирования.

      Идея состоит в том, чтобы учитывать, входит ли каждый день в календаре в список государственных праздников. А функция, которая подсчитывает элементы в списке, — это функция СЧЁТЕСЛИ или СЧЁТЕСЛИМН.

      Например, для 3 июля формула следующая

      И на 4 июля

      Преобразовать формулу в тест

      Как видите, ожидаемый результат равен 1. Поэтому наш тест легко написать следующим образом. Да, ссылка здесь B1, первая ячейка календаря

      Создайте правило условного форматирования

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

      Мы пишем тест в текстовом поле пользовательского правила.

      Очень важно заблокировать только ссылку на строку (B$1). Таким образом, ваш тест всегда будет проверять значения первой строки; дата 😉

      И, наконец, мы настраиваем диапазон ячеек, к которым применяются правила

      Влияние на календарь

      Затем автоматически при обнаружении даты в таблице государственных праздников правило изменит цвет в календаре.

      Читайте также: