はじめに
EXCEL関数で稼働日数を計算するものが有りますが、シート1枚でパッと見れたら良いよなぁと思いまして作ってみました。
ダウンロード
利用したいだけの方は、こちらからダウンロードしてご利用ください。
※ZIP圧縮されておりますので、適当なフォルダに解凍してください。
ちょこっと解説
祝日については、設定シートからダウンロードできるようになっております。
説明については、CSVファイルをダウンロードしてシートにセットするを参照ください。
Sheet1
A1セルに年を入力するとカレンダーが更新されます。土日祝日は条件付き書式で色づけされるようにしております。
「祝日セット」ボタンを押すと祝日と休日がセットされます。
右側に曜日別の集計など表示されます。
一番右側の平日と休日セルは、土日祝日が休日とした場合の日数になります。
VBA
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 |
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で曜日カウント
- 祝日をダウンロードしてシートにセットなど