はじめに
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で曜日カウント
- 祝日をダウンロードしてシートにセットなど