Excelで祝日を自動判定・計算する方法｜関数・条件付き書式・VBAを完全解説

=IF(COUNTIF(祝日リスト,A1)>0,"祝日","")

=IF(COUNTIF(祝日リスト,A2)>0,VLOOKUP(A2,祝日テーブル,2,FALSE),"")

Function IsHoliday(targetDate As Date, holidayRange As Range) As Boolean
    Dim cell As Range
    For Each cell In holidayRange
        If cell.Value = targetDate Then
            IsHoliday = True
            Exit Function
        End If
    Next cell
    IsHoliday = False
End Function

Sub CreateCalendar()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim startDate As Date
    startDate = DateSerial(Year(Date), Month(Date), 1) '当月1日

    Dim endDate As Date
    endDate = DateSerial(Year(Date), Month(Date) + 1, 0) '当月末日

    Dim currentDate As Date
    Dim row As Long
    row = 2

    '見出し
    ws.Range("A1").Value = "日付"
    ws.Range("B1").Value = "曜日"
    ws.Range("C1").Value = "祝日名"
    ws.Range("D1").Value = "区分"

    For currentDate = startDate To endDate
        ws.Cells(row, 1).Value = currentDate
        ws.Cells(row, 2).Value = Format(currentDate, "aaa")

        '祝日判定
        Dim holidayName As String
        holidayName = GetHolidayName(currentDate)
        ws.Cells(row, 3).Value = holidayName

        '区分（営業日/休日）
        If Weekday(currentDate, vbMonday) >= 6 Then
            ws.Cells(row, 4).Value = "休日"
            ws.Cells(row, 4).Interior.Color = RGB(200, 200, 255)
        ElseIf holidayName <> "" Then
            ws.Cells(row, 4).Value = "祝日"
            ws.Cells(row, 4).Interior.Color = RGB(255, 200, 200)
        Else
            ws.Cells(row, 4).Value = "営業日"
        End If

        row = row + 1
    Next currentDate

    '書式設定
    ws.Columns("A:D").AutoFit
End Sub

Function GetHolidayName(targetDate As Date) As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("祝日リスト")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row

    Dim i As Long
    For i = 1 To lastRow
        If ws.Cells(i, 1).Value = targetDate Then
            GetHolidayName = ws.Cells(i, 2).Value
            Exit Function
        End If
    Next i

    GetHolidayName = ""
End Function

Sub UpdateHolidayList()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("祝日リスト")

    '翌年の固定祝日を追加
    Dim nextYear As Integer
    nextYear = Year(Date) + 1

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row + 1

    '固定祝日（日付が毎年同じ）
    Dim fixedHolidays As Variant
    fixedHolidays = Array( _
        Array(1, 1, "元日"), _
        Array(2, 11, "建国記念の日"), _
        Array(2, 23, "天皇誕生日"), _
        Array(4, 29, "昭和の日"), _
        Array(5, 3, "憲法記念日"), _
        Array(5, 4, "みどりの日"), _
        Array(5, 5, "こどもの日"), _
        Array(8, 11, "山の日"), _
        Array(11, 3, "文化の日"), _
        Array(11, 23, "勤労感謝の日") _
    )

    Dim h As Variant
    For Each h In fixedHolidays
        ws.Cells(lastRow, 1).Value = DateSerial(nextYear, h(0), h(1))
        ws.Cells(lastRow, 2).Value = h(2)
        lastRow = lastRow + 1
    Next h

    '※ハッピーマンデー（成人の日・海の日・敬老の日・スポーツの日）と
    '  春分の日・秋分の日は毎年異なるため、手動確認が必要
    MsgBox "固定祝日を追加しました。" & vbCrLf & _
           "以下は手動で確認・追加してください：" & vbCrLf & _
           "・成人の日（1月第2月曜）" & vbCrLf & _
           "・春分の日（3月20日頃）" & vbCrLf & _
           "・海の日（7月第3月曜）" & vbCrLf & _
           "・敬老の日（9月第3月曜）" & vbCrLf & _
           "・秋分の日（9月23日頃）" & vbCrLf & _
           "・スポーツの日（10月第2月曜）" & vbCrLf & _
           "・振替休日"
End Sub