EXCELで日数計算

はじめに

EXCEL関数で稼働日数を計算するものが有りますが、シート1枚でパッと見れたら良いよなぁと思いまして作ってみました。

ダウンロード

利用したいだけの方は、こちらからダウンロードしてご利用ください。

※ZIP圧縮されておりますので、適当なフォルダに解凍してください。

 

ちょこっと解説

祝日については、設定シートからダウンロードできるようになっております。

説明については、CSVファイルをダウンロードしてシートにセットするを参照ください。

 

Sheet1

A1セルに年を入力するとカレンダーが更新されます。土日祝日は条件付き書式で色づけされるようにしております。

「祝日セット」ボタンを押すと祝日と休日がセットされます。

右側に曜日別の集計など表示されます。

一番右側の平日と休日セルは、土日祝日が休日とした場合の日数になります。

VBA

Option Explicit

''' 祝日セット
Private Sub cmdSetHoliday_Click()
    Dim nen As Integer
    Dim tuki As Integer
    Dim holiday() As String
    Dim userHoliday() As String
    Dim i As Integer
    Dim d As Integer

    Application.ScreenUpdating = False
    
    nen = Me.Range("A1").Value
    
    For tuki = 1 To 12
        ' 祝日取得
        holiday = GetHoliday(nen, tuki)
        ' 休日取得
        userHoliday = GetUserHoliday(tuki)
        
        For i = 3 To 33
            d = Me.Cells(2, i)
            Me.Cells(tuki * 2 + 2, i).Value = ""
            If holiday(d) <> "" Then
                Me.Cells(tuki * 2 + 2, i).Value = "祝"
            End If
            If userHoliday(d) <> "" Then
                Me.Cells(tuki * 2 + 2, i).Value = "祝"
            End If
        Next
    Next tuki

    Application.ScreenUpdating = True
    
    MsgBox "祝日セット完了しました。", vbInformation
End Sub


''' 祝日取得
Private Function GetHoliday(y As Integer, m As Integer)
On Error GoTo Err_Handler
    
    Dim i As Integer
    Dim d(31) As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xl_file As String
    Dim sql As String
    Dim curRow As Integer
    
    Dim sYMD As Date
    Dim eYMD As Date
    
    'ツールメニューの参照設定'
    ' Microsoft ActiveX Data Objects 2.8 Library'
    'チェック'
    xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'
 
    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    #If Win64 Then
        ' 64bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #Else
        ' 32bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #End If
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    sYMD = CDate(y & "/" & m & "/1")
    eYMD = DateAdd("d", -1, DateAdd("m", 1, sYMD))
    
    sql = "SELECT 国民の祝日・休日月日, 国民の祝日・休日名称 FROM [祝日$]" _
        & "  WHERE" _
        & "  国民の祝日・休日月日 >= #" & sYMD & "# AND 国民の祝日・休日月日 <= #" & eYMD & "#"
    rs.Open sql, cn, adOpenStatic
    
    ' 祝日配列初期化
    For i = 1 To 31
        d(i) = ""
    Next i
    Do While Not rs.EOF
        d(Format(rs.Fields("国民の祝日・休日月日"), "d")) = rs.Fields("国民の祝日・休日名称")
        rs.MoveNext
    Loop
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    GetHoliday = d
    Exit Function
    
Err_Handler:
    MsgBox "祝日取得 : " & Err.Description, vbExclamation
    On Error Resume Next
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Function

''' ユーザ休日取得
Private Function GetUserHoliday(m As Integer)
On Error GoTo Err_Handler
    
    Dim i As Integer
    Dim d(31) As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xl_file As String
    Dim sql As String
    Dim curRow As Integer
    
    Dim sYMD As Date
    Dim eYMD As Date
    
    'ツールメニューの参照設定'
    ' Microsoft ActiveX Data Objects 2.8 Library'
    'チェック'
    xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'
 
    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    #If Win64 Then
        ' 64bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #Else
        ' 32bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #End If
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    sql = "SELECT 日, 休日名 FROM [休日$]" _
        & "  WHERE" _
        & "  月 = " & m
    rs.Open sql, cn, adOpenStatic
    
    ' 祝日配列初期化
    For i = 1 To 31
        d(i) = ""
    Next i
    Do While Not rs.EOF
        d(rs!日) = rs!休日名
        rs.MoveNext
    Loop
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    GetUserHoliday = d
    Exit Function
    
Err_Handler:
    MsgBox "休日取得 : " & Err.Description, vbExclamation
    On Error Resume Next
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Function

 

「祝日セット」ボタンを押すと、cmdSetHoliday_Click 部分が実行されます。

A1セルの年を取得して月の繰り返し（12回）で月を取得し日の繰り返し（31回）で”祝”文字を曜日の下に表示するようにします。

祝日、休日の取得は、SQLを利用しております。

詳しくは、EXCEL VBAでSQL文を使って集計するも参考にしてください。

祝日の取得は、該当する月の1日から末日までの範囲取得するSELECT文となっております。

休日の取得は、該当する月のデータを取得するSELECT文となっております。

 

最後に

特別難しいことをしておりませんが、いろいろなことを組み合わせておりますので参考にしていただけたら良いかと思います。

• EXCELでSQL
• 条件付き書式で曜日に色設定
• COUNTIFで曜日カウント
• 祝日をダウンロードしてシートにセットなど