出勤簿を作る　(4)

はじめに

　今回は集計出力のプログラムです。集計シートとCSVファイルに出力します。
　集計は具体的に言うと社員一人に対して１本のデータにします。項目は以下の通りです。

　　・実働時間合計　
　　・欠勤時間合計
　　・有給時間合計
　　・深夜業時間合計
　　・残業時間合計
　　・出勤日数
　　・交通費支給日数合計

　実働時間合計～残業時間合計までは、各出勤簿シートの合計値から得る事ができます。
出勤日数、交通費支給日数合計に関しては、その他の値から割り出す形になります。

それぞれ項目を予め設けてもいいのですが、計算で求める方法のサンプルとして捉えてください。

　今回のサンプルには架空の社員が登録されています。

出勤日数と交通費支給日数合計の求め方

　出勤日数は、その月の日数から公休日を引けば良いでしょう。ただ今回は全日欠勤はカウントしないようにします。全日の欠勤は実働時間が０になるはずなので、計算式は以下の様になります。
　
　出勤日数 ＝ 出勤すべき日数 – 公休日日数 – 実働時間が0の日数

　尚、出勤日数を「出勤すべき日数」と捉えるならば、全日欠勤した日を含めるケースもあるでしょう。これはそれぞれの会社のやり方で変わると思います。

　もし皆勤手当などを支給している会社であれば、実働時間が0の日数 ＝ 0 の時に支給すれば良いでしょう。

　次は交通費支給の日数計算です。
　前述の出勤に数に対して、全日有給の場合には本人が通勤していないので除外されます。通常有給休暇を取った場合は出勤した事になりますが、それが全て有給又は欠勤の場合は出勤のための交通費はカウントされません。
　そこで式は以下の様になります。
　
　交通費支給日数合計＝出勤日数 – 実働時間と(有給時間+欠勤時間）が同じ日の日数

集計データの出力

　集計データは「集計」というシートを作って出力します。
　集計データはメニューシートから集計出力ボタンを押した時に計算されます。
　その後、出勤簿の値を変更した場合は、再度、集計出力ボタンを押さなければ反映されませんので注意してください。
　
　集計シートで値を確認したら、CSVファイルに出力できるようにします。

シートを非表示に設定する

　少々話が逸れてしまいますが、これまで原本、社員リスト、勤務パターンなどのシートは常に表示されている状態でした。これらのシートは頻繁に変更するものではありませんので、普段は非表示にしておき、メニューシートにボタンを配置して表示・非表示を変更できるようにしておきます。
　今回の集計シートも同じように表示を切り替えられるようにします。

　プログラムは難しくありません。シートオブジェクトのVisibleプロパティの True か False に設定するだけです。
　原本シートであれば以下の様にします。シート名を変更すればそのまま別のシートにも使う事ができます。

Sub 勤務表原本の表示_非表示()
    If Worksheets("原本").Visible = True Then
         Worksheets("原本").Visible = False
    Else
         Worksheets("原本").Visible = True
    End If
End Sub

　ボタンは以下の様に右側に設置しました。集計ボタン、集計CSVファイル出力ボタンは後述します。

集計のプログラム

　集計は集計シートに値を転記していきます。

Sub 集計()
    Dim totalSh As Worksheet
    Set totalSh = Worksheets("集計")
    
    '集計シートの値を全てクリア
    totalSh.Cells.Clear
    
    '項目名を記述
    totalSh.Range("A1") = "社員番号"
    totalSh.Range("B1") = "氏名"
    totalSh.Range("C1") = "実働時間合計"
    totalSh.Range("D1") = "欠勤時間合計"
    totalSh.Range("E1") = "有給時間合計"
    totalSh.Range("F1") = "深夜業時間合計"
    totalSh.Range("G1") = "残業時間合計"
    totalSh.Range("I1") = "出勤日数"
    totalSh.Range("H1") = "交通費支給日数合計"
    
    Dim sh, i As Long, j As Long
    Dim cnt As Long, cnt2 As Long, cnt3 As Long
    Dim totalDayCnt As Long, dayCnt As Long
    
    i = 2
    For Each sh In Worksheets   '社員の出勤簿のみ値を取得
        If sh.Name <> "原本" _
            And sh.Name <> "社員リスト" _
            And sh.Name <> "メニュー" _
            And sh.Name <> "勤務パターン" _
            And sh.Name <> "集計" Then
            
            totalSh.Cells(i, "A") = Replace(sh.Range("B2"), "№", "")
            totalSh.Cells(i, "B") = sh.Range("D2")
            totalSh.Cells(i, "C") = sh.Range("H36")
            totalSh.Cells(i, "D") = sh.Range("I36")
            totalSh.Cells(i, "E") = sh.Range("J36")
            totalSh.Cells(i, "F") = sh.Range("K36")
            totalSh.Cells(i, "G") = sh.Range("L36")
            
            totalDayCnt = 0
            cnt = 0
            cnt2 = 0
            cnt3 = 0
            For j = 5 To 35
                If sh.Cells(j, "B") <> "" Then
                    '日付のカウント
                    totalDayCnt = totalDayCnt + 1
                    '公休日のカウント
                    If sh.Cells(j, "E") = "" Then
                        cnt = cnt + 1
                    '全日欠勤のカウント
                    ElseIf sh.Cells(j, "H") = 0 Then
                        cnt2 = cnt2 + 1
                    '実働時間合計＝有給
                    ElseIf sh.Cells(j, "H") = sh.Cells(j, "J") Then
                        cnt3 = cnt3 + 1
                    End If
                End If
            Next j
            '出勤日
            totalSh.Cells(i, "H") = totalDayCnt - cnt - cnt2
            '交通費回数
            totalSh.Cells(i, "I") = totalDayCnt - cnt - cnt3
            i = i + 1
            
        End If
    Next sh
    
End Sub

　最初に集計シートの値を全てクリアします。続いて一行目に項目名を記述します。
　あとは各シートを巡回し、原本や勤務パターンなどを除いて、社員の出勤簿シートの場合のみ集計値を記録していきます。

　実働時間～残業時間合計までは、36行目の合計値を転記するだけで済みます。
　出勤日数と交通費支給日数合計に関しては、日付のカウントや公休日のカウントなどを使って導き出しています。

集計シートは以下の様になります。

CSV出力のプログラム

　集計処理は集計シートに値を記録して確認するためのものでした。CSVファイル出力は他のプログラムのデータとして活用するためのプログラムになります。

Sub OutputCSV()
    Dim result As Long
    Dim myMessage As String
    myMessage = "このブックと同じフォルダにCSVファイルを出力します。よろしいですか？"
    result = MsgBox(myMessage, vbYesNo + vbQuestion + vbDefaultButton2, "確認")
    If result = vbNo Then Exit Sub


    Dim myPath As String
    Dim myBookName As String
    Dim csvFileName As String
    
    myPath = ThisWorkbook.Path
    myBookName = ThisWorkbook.Name
    With Worksheets("メニュー")
    csvFileName = "syukkinbo" & .Range("B4") & Format(.Range("D4"), "00")
    End With
    
    Call 集計
    
    Application.DisplayAlerts = False
    
    'CSV出力するシートをアクティブにしておく
    Worksheets("集計").Visible = True
    Worksheets("集計").Select
    'CSVファイルに出力
    ThisWorkbook.SaveAs myPath & "\" & csvFileName & ".csv", FileFormat:=xlCSV
    '元のファイル名に戻して保存
    ThisWorkbook.SaveAs myPath & "\" & myBookName, _
                     FileFormat:=xlOpenXMLWorkbookMacroEnabled
    '集計シートの名前を元に戻す
    ActiveSheet.Name = "集計"

    Application.DisplayAlerts = True
End Sub

　最初にCSVファイルを作るか確認するメッセージを出します。
　OKの場合、CSVファイルの名前を設定します。今回は「syukkinbo202403.csv」などとなるように設定しています。
　次に、CSV出力するシートをアクティブにしておく必要があります。CSVファイルに出力できるのは１つのシートだけです。集計シートが表示されていないとSelectできませんので注意してください。
　
　続いて今開いているブックをCSVファイルとして保存します。そしてすぐに元ファイル名のExcelファイルで保存し直します。
　この時、集計シートのシート名がCSVファイル名に変更されているので、「集計」に戻してやります。

最後に

　ここまでで出勤簿として値を編集し、集計を出力できるようになりました。
　このままでも使用できると思いますが、実際にはいくつかの問題を抱えています。
　
　・数式マクロが入ったセルに値を直接入れてしまうと不具合の原因となる
　・欠勤、有給、残業などの合計時間数が実働時間と異ってもエラーとならない
　
　次回は、このような不具合を改善してより強固なシステムにしてみましょう。

　　今回のサンプルには架空の社員が登録されています。各社員のシートを削除し、社員リストを変更すればご自身の環境の出勤簿として試す事ができます。

　プログラムに質問等がある場合は、こちらからどうぞ。