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

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

• Выделение выходных и праздничных дней с помощью условного форматирования
• Формула Excel - Использование вспомогательного столбца для отметки выходных и праздничных дней
• Код VBA - Автоматическое затемнение выходных и праздничных дней

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

Функция условного форматирования Excel позволяет вам автоматически выделять выходные дни (обычно субботы и воскресенья) и праздники в ваших записях дат — без написания каких-либо расчетов в ваши данные. Этот метод подходит для пользователей, которые часто просматривают или обновляют свои листы, и хотят цветовые сигналы для нерабочих дней, которые обновляются мгновенно при изменении данных или праздников.

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

1. Выберите диапазон с датами, которые вы хотите выделить. 

2. Перейдите на вкладку Главная, нажмите Условное форматирование и выберите Новое правило, чтобы открыть редактор правил. См. скриншот:

3. В диалоговом окне Новое правило форматирования:

• Выберите Использовать формулу для определения ячеек для форматирования из списка типов правил.
• В поле ввода формулы введите следующую формулу: =NETWORKDAYS($A2,$A2,$F$2:$F$6)=0
• Нажмите кнопку Формат, чтобы установить стиль выделения.

4. В окне Формат ячеек перейдите на вкладку Заливка и выберите цвет фона для выходных и праздничных дней. Этот цвет отличает нерабочие дни от обычных для быстрого визуального ознакомления.

5. Нажмите OK дважды, чтобы закрыть диалоговые окна. Ваше выбранное выделение автоматически появится для любой даты, совпадающей с выходными или указанными праздниками в выбранной области. Праздники можно обновлять в любое время, редактируя диапазон праздников, и форматирование будет обновлено соответственно.

Раскройте магию Excel с Kutools AI

• Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
• Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
• Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
• Интерпретация формул: Легко разбирайтесь в сложных формулах.
• Перевод текста: Преодолейте языковые барьеры в ваших таблицах.

Улучшите возможности Excel с помощью инструментов на базе ИИ. Скачать сейчас и испытайте беспрецедентную эффективность!

Формула Excel – Использование вспомогательного столбца для отметки выходных и праздничных дней

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

Предположим, ваши даты находятся в столбце A, начиная со строки 2, а праздники перечислены в F2:F6. Вставьте новый столбец рядом со столбцом дат и введите заголовок, например «Тип дня».

1. В ячейке B2 (вспомогательный столбец) введите следующую формулу:

=IF(OR(WEEKDAY(A2,2)>=6,COUNTIF($F$2:$F$6,A2)>0),"Weekend/Holiday","Workday")

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

Код VBA – Автоматическое затемнение выходных и праздничных дней

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

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

1. Нажмите Разработчик > Visual Basic. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль.

2. Скопируйте и вставьте следующий код в только что созданный Модуль:

Sub ShadeWeekendsAndHolidays()
    Dim rngDates As Range
    Dim rngHolidays As Range
    Dim cell As Range
    Dim xTitleId As String
    
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set rngDates = Application.InputBox("Select the range with dates:", xTitleId, Selection.Address, Type:=8)
    Set rngHolidays = Application.InputBox("Select the range with holiday dates:", xTitleId, , Type:=8)
    On Error GoTo 0
    
    If rngDates Is Nothing Then Exit Sub
    If rngHolidays Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    For Each cell In rngDates
        If IsDate(cell.Value) Then
            If Weekday(cell.Value, vbMonday) >= 6 Or Not IsError(Application.Match(CDbl(cell.Value), rngHolidays, 0)) Then
                cell.Interior.Color = RGB(255, 199, 206) ' Light red fill; adjust as needed
            Else
                cell.Interior.ColorIndex = xlNone ' Remove fill from regular days
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

3. Нажмите клавишу F5, чтобы запустить этот код, появится запрос выбрать ячейки с датами, затем ваш список ячеек с праздниками. Следуйте инструкциям на экране.

Макрос выделяет выходные (суббота/воскресенье) и праздники (согласно вашему списку) цветом заливки. Настройте RGB(255,199,206) в коде для использования предпочитаемого цвета, если это необходимо.

Больше связанных статей: