【EXCEL：VBA】指定した年月の月間勤務表を作成したい

指定した年月の月間勤務表を作成したい

SHARE

この記事では、ExcelVBAを利用して「指定した年月の月間勤務表」をボタン1つ押すだけで作成する方法について説明しています。

 

日毎の開始時刻、終了時刻、休憩時間を入力すると、日毎の勤務時間が計算されます。

 

日々の勤務表を入力することにより、勤務日数や勤務時間合計が計算されます。

 

毎月手作業で作成した勤務表を年月を指定するだけで、一瞬で作成できるようになります。

 

 

それでは、指定した年月の月間勤務表を作成する機能を作っていきましょう。

 

困っている女性

毎月手作業で月間勤務表を作成しているの。先月の月間勤務表をコピーして、日にちと曜日を変更して作成するの。

困っている女性

地味に面倒なので、もっと簡単に作成する方法はないの！？
それなら、VBAを使えば指定した年月の月間勤務表を一瞬で作成できるよ。

解決する男性

あとは、毎日の勤務開始時刻、終了時刻、休憩時間を入力するだけで、月間の勤務日数と勤務時間合計を計算してくれるよ。月末にあせって、計算しなくてよくなるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

 

指定した年月の月間勤務表を作成する機能の概要図

以下は、指定した年月の月間勤務表を作成する機能の概要図です。

指定した年月の月間勤務表を作成する機能の概要図

指定した年月の月間勤務表を作成する機能の概要図

 

年と月を指定すると、指定された年月の月間勤務表が作成されます。

機能的にはシンプルですが、手作業で作成するとなると、少し面倒くさい作業となります。

それを、VBAを利用することにより、ボタン1つだけで作成することができるようになります。

 

◆似たような機能については、以下の記事を参照してください。


指定した年月の予定表を作成したい

【EXCEL：VBA】指定した年月の予定表を作成したい

 


何日後・何日前の日付を知りたい

【EXCEL：VBA】何日後・何日前の日付を求めたい

 

指定した年月の月間勤務表を作成する機能を、1つ1つ順番に説明していきます。

 

VBAで月間勤務表を作成する機能を作成する

今回は、メインシートすら必要ありません。

VBAプログラムを入力するだけで、必要な機能を作成できます。

 

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

Microsoft Visual Basic for Applicationsを起動する

 

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

VBAプログラムを入力する（その１）

VBAプログラムを入力する（その１）

 

VBAプログラムを入力する（その２）

VBAプログラムを入力する（その２）

 

VBAプログラムを入力する（その３）

VBAプログラムを入力する（その３）

 

標準モジュールを追加して、以下のソースを書きます。

Public Sub Main_Proc()

    Dim sht As Worksheet
    Dim nen As Integer
    Dim tuki As Integer
    Dim startday As Date
    Dim endday As Date
    Dim countday As Integer
    Dim i As Integer
    
    '１．年の指定ダイアログを表示する
    nen = InputBox("年を指定してください。", "年指定", year(Now))
    
    '２．月の指定ダイアログを表示する
    tuki = InputBox("月を指定してください。", "月指定", month(Now))
    
    '３．新しいシートを追加する
    ThisWorkbook.Sheets.Add After:=Sheets(ThisWorkbook.Sheets.Count)
    
    '４．対象シートを変数に格納する
    Set sht = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    '５．対象シートの名前を指定された年月に変更する
    sht.Name = nen & Format(tuki, "00")
    
    '６．シート全体を上下中央揃えにする
    sht.Cells.VerticalAlignment = xlCenter

    '７．シート全体を左右中央揃えにする
    sht.Cells.HorizontalAlignment = xlCenter

    '８．太字を設定する
    sht.Range("A1:F1").Font.Bold = True
    sht.Range("A3:F3").Font.Bold = True

    '９．各ラベルを設定する
    sht.Range("A1") = "勤務表"
    sht.Range("B1") = "年"
    sht.Range("C1") = "月"
    sht.Range("D1") = "勤務日数"
    sht.Range("E1") = "勤務時間計"
    sht.Range("F1") = "氏名"

    sht.Range("B3") = "開始時刻"
    sht.Range("C3") = "終了時刻"
    sht.Range("D3") = "休憩時間"
    sht.Range("E3") = "勤務時間"
    sht.Range("F3") = "備考"

    '１０．セルを結合する
    sht.Range("A1:A3").Merge
    
    '１１．年と月を設定する
    sht.Range("B2") = nen
    sht.Range("C2") = tuki

    '１２．月末日を取得する
    startday = DateSerial(nen, tuki, 1)
    endday = DateAdd("d", -1, DateAdd("m", 1, startday))
    
    '１３．日数を計算する
    countday = DateDiff("d", startday, endday)

    For i = 0 To countday
        '１４．1日から月末日を設定する
        sht.Cells(4 + i, 1) = DateAdd("d", i, startday)

        '１５．日にちの表示形式を設定する
        sht.Cells(4 + i, 1).NumberFormatLocal = "d""日""(aaa)"
    Next

    '１６．時刻の表示形式を設定する
    sht.Cells(2, 5).NumberFormat = "[h]:mm"
    sht.Range(sht.Cells(4, 2), sht.Cells(4 + countday, 5)).NumberFormat = "hh:mm"
    
    '１７．勤務日数の計算式を設定する
    sht.Range("D2") = "=COUNTA(C4:C" & 4 + countday & ")"
    
    '１８．勤務時間計の計算式を設定する
    sht.Range("E2") = "=SUM(E4:E" & 4 + countday & ")"
    
    '１９．勤務時間の計算式を設定する
    For i = 0 To countday
        sht.Cells(4 + i, 5) = "=C" & 4 + i & "-B" & 4 + i & "-D" & 4 + i
    Next
    
    '２０．罫線を設定する
    With sht.Range(sht.Cells(1, 1), sht.Cells(4 + countday, 6))
        .Borders.LineStyle = True
        .Borders.Color = -1003520
    End With
    
    '２１．塗りつぶしの色を設定する
    sht.Range("A1:C1").Interior.Color = 16772300
    sht.Range("A3:D3").Interior.Color = 16772300
    
    sht.Range("D1:F1").Interior.Color = 13434828
    sht.Range("E3:F3").Interior.Color = 13434828
    
    sht.Range("D2:E2").Interior.Color = 13434879
    sht.Range(sht.Cells(4, 5), sht.Cells(4 + countday, 5)).Interior.Color = 13434879
    
    '２２．列幅を設定する
    sht.Columns("A:E").ColumnWidth = 11
    sht.Columns("F:F").ColumnWidth = 25
    
    sht.Range("B4").Select
    
    MsgBox "完了"

End Sub

 

次に、EXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

 

年月を指定して月間勤務表を作成する機能の使い方

それでは、作成したい年月の月間勤務表を作成する方法を説明します。

 

「開発」タブの「マクロ」をクリックします。

マクロを起動する

マクロを起動する

 

先程作成したVBAプログラム「Main_Proc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックします。

実行ボタンをクリックする

実行ボタンをクリックする

 

次に、年を指定するダイアログが表示されます。

デフォルトで現在の年が表示されますので、変更したい場合は好きな月を入力します。

そして、OKボタンをクリックします。

年を指定する

年を指定する

 

次に、月を指定するダイアログが表示されます。

デフォルトで現在の月が表示されますので、変更したい場合は好きな月を入力します。

そして、OKボタンをクリックします。

月を指定する

月を指定する

 

しばらく待つと、指定年月のシートが追加され、月間勤務表が作成されます。

今回は、2021年2月を指定しています。

指定した月間勤務表が作成される

指定した月間勤務表が作成される

月間勤務表には、2021年2月の1日から月末日の28日までが縦に並び、各日にちの曜日も表示されます。

あとは毎日、開始時刻と終了時刻、休憩時間を入力していくだけです。

なにかメモして置きたいことは、備考欄に入力しておきます。

 

指定した年月の月間勤務表を作成するVBAプログラム（Main_Proc）の説明

それでは、指定した年月の月間勤務表を作成するVBAプログラム（Main_Proc）を説明します。

１．年の指定ダイアログを表示する

'１．年の指定ダイアログを表示する
nen = InputBox("年を指定してください。", "年指定", year(Now))

ここでは、年を指定するダイアログを表示させています。

ダイアログにはデフォルトで、現在の年を表示させています。

入力された年を変数「nen」に格納するようにしています。

 

２．月の指定ダイアログを表示する

'２．月の指定ダイアログを表示する
tuki = InputBox("月を指定してください。", "月指定", month(Now))

ここでは、月を指定するダイアログを表示させています。

ダイアログにはデフォルトで、現在の月を表示されています。

入力された月を変数「tuki」に格納するようにしています。

 

３．新しいシートを追加する

'３．新しいシートを追加する
ThisWorkbook.Sheets.Add After:=Sheets(ThisWorkbook.Sheets.Count)

ここでは、現在のEXCELに存在するシート数を取得し、全てのシートの右に新しいシートを追加しています。

例えば、シートが3つ存在している場合、左から3つ目のシートの右に新しいシートを追加します。

 

４．対象シートを変数に格納する

'４．対象シートを変数に格納する
Set sht = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

ここでは、新しく追加したシートを変数「sht」に格納しています。

この後の処理で何度もこのシートを利用するので、変数に格納しています。

 

５．対象シートの名前を指定された年月に変更する

'５．対象シートの名前を指定された年月に変更する
sht.Name = nen & Format(tuki, "00")

ここでは、新しく追加シートのシート名を変更しています。

変更するシート名は、ダイアログで指定された年月を設定します。

月が1月～9月の場合、1桁になるのでFormat関数を利用して2桁表示に加工しています。

例えば、1月の”01”に加工されます。

 

６．シート全体を上下中央揃えにする

'６．シート全体を上下中央揃えにする
sht.Cells.VerticalAlignment = xlCenter

ここでは、シートのすべてのセルを上下中央揃えにしています。

 

７．シート全体を左右中央揃えにする

'７．シート全体を左右中央揃えにする
sht.Cells.HorizontalAlignment = xlCenter

ここでは、シートのすべてのセルを左右中央揃えにしています。

 

８．太字を設定する

'８．太字を設定する
sht.Range("A1:F1").Font.Bold = True
sht.Range("A3:F3").Font.Bold = True

ここでは、月間勤務表のラベルにあたるセルを太字に設定しています。

この後の処理で表示するラベルの文字を設定します。

ラベルは例えば、勤務表や年、月などです。

 

９．各ラベルを設定する

'９．各ラベルを設定する
sht.Range("A1") = "勤務表"
sht.Range("B1") = "年"
sht.Range("C1") = "月"
sht.Range("D1") = "勤務日数"
sht.Range("E1") = "勤務時間計"
sht.Range("F1") = "氏名"

sht.Range("B3") = "開始時刻"
sht.Range("C3") = "終了時刻"
sht.Range("D3") = "休憩時間"
sht.Range("E3") = "勤務時間"
sht.Range("F3") = "備考"

ここでは、月間勤務表のラベル文字を各セルに設定しています。

例えば、A1セルには”勤務表”、B3セルには”開始時刻”を設定します。

 

１０．セルを結合する

'１０．セルを結合する
sht.Range("A1:A3").Merge

ここでは、ラベル「勤務表」を表示させるセルを結合しています。

 

１１．年と月を設定する

'１１．年と月を設定する
sht.Range("B2") = nen
sht.Range("C2") = tuki

ここでは、ダイアログで指定された年と月をB2セル、C2セルに設定しています。

 

１２．月末日を取得する

'１２．月末日を取得する
startday = DateSerial(nen, tuki, 1)
endday = DateAdd("d", -1, DateAdd("m", 1, startday))

ここでは、指定された年月の月末日を取得しています。

具体的には、指定された年月の1日の1ヶ月後の1日前を算出することにより、指定年月の月末日を算出します。

例えば、2021年2月を指定した場合、2021年2月1日の1ヶ月後にあたる2021年3月1日を算出します。

次に、2021年3月1日の1日前にあたる2021年2月28日を算出します。

 

１３．日数を計算する

'１３．日数を計算する
countday = DateDiff("d", startday, endday)

ここでは、指定された年月の日数を計算しています。

正確に言うと、指定年月の1日から月末日までの日数差を計算しています。

例えば、2021年2月なら、2月1日から2月28日の日数差27を算出しています。

2月1日を0行目とした時に、2月28日は何行下になるかを知りたいからです。

 

１４．1日から月末日を設定する

'１４．1日から月末日を設定する
sht.Cells(4 + i, 1) = DateAdd("d", i, startday)

ここでは、A列に指定された年月の1日から月末日の日にちを設定しています。

 

１５．日にちの表示形式を設定する

'１５．日にちの表示形式を設定する
sht.Cells(4 + i, 1).NumberFormatLocal = "d""日""(aaa)"

ここでは、先程設定した1日から月末日までの日にちに対して、表示形式を変更しています。

例えば、2021年2月1日なら、”1日(月)”のように日にちと曜日が表示されるようにしています。

 

１６．時刻の表示形式を設定する

'１６．時刻の表示形式を設定する
sht.Cells(2, 5).NumberFormat = "[h]:mm"
sht.Range(sht.Cells(4, 2), sht.Cells(4 + countday, 5)).NumberFormat = "hh:mm"

ここでは、時刻の表示形式を設定しています。

2行5列目にあたる「勤務時間計」には、24時間を超えて表示させたいので、”[h]:mm”を設定しています。

次に、1日から月末日の勤務時間を表示させる5列目には、”hh:mm”を設定しています。

 

１７．勤務日数の計算式を設定する

'１７．勤務日数の計算式を設定する
sht.Range("D2") = "=COUNTA(C4:C" & 4 + countday & ")"

ここでは、勤務日数を表示するD2セルにEXCEL関数を設定しています。

1日から月末日までの各行のC列（終了時刻）が入力されているセル数を計算しています。

終了時刻が未入力の日は、出勤していない日としています。

 

１８．勤務時間計の計算式を設定する

'１８．勤務時間計の計算式を設定する
sht.Range("E2") = "=SUM(E4:E" & 4 + countday & ")"

ここでは、勤務時間計を表示するE2セルにEXCEL関数を設定しています。

1日から月末日までの各行のE列（勤務時間）を合計しています。

 

１９．勤務時間の計算式を設定する

'１９．勤務時間の計算式を設定する
For i = 0 To countday
    sht.Cells(4 + i, 5) = "=C" & 4 + i & "-B" & 4 + i & "-D" & 4 + i
Next

ここでは、各行のE列に1日の勤務時間を計算するEXCEL関数を設定しています。

具体的には、終了時刻から開始時刻および休憩時間をマイナスしています。

 

２０．罫線を設定する

'２０．罫線を設定する
With sht.Range(sht.Cells(1, 1), sht.Cells(4 + countday, 6))
    .Borders.LineStyle = True
    .Borders.Color = -1003520
End With

ここでは、月間勤務表の罫線を設定しています。

A1セルからF列の最終行までを対象に罫線を設定します。

 

２１．塗りつぶしの色を設定する

'２１．塗りつぶしの色を設定する
sht.Range("A1:C1").Interior.Color = 16772300
sht.Range("A3:D3").Interior.Color = 16772300

sht.Range("D1:F1").Interior.Color = 13434828
sht.Range("E3:F3").Interior.Color = 13434828

sht.Range("D2:E2").Interior.Color = 13434879
sht.Range(sht.Cells(4, 5), sht.Cells(4 + countday, 5)).Interior.Color = 13434879

ここでは、各セルの塗りつぶしの色を設定しています。

1行目から3行目およびE列に対して、塗りつぶしの色を設定します。

 

２２．列幅を設定する

'２２．列幅を設定する
sht.Columns("A:E").ColumnWidth = 11
sht.Columns("F:F").ColumnWidth = 25

ここでは、各列の列幅を設定しています。

A列からF列に対して、列幅を設定します。

 

ということで、指定した年月の月間勤務表を作成する機能を利用できるようになりました。

 

それでは、毎月作成する必要がある勤務表をサクッと作成して、自分の勤務状況を記録していきましょう。

もっと重要な作業に集中して、こういった定型的な作業は、VBAを利用して効率化していきましょう。

 

以上です。

 

あわせて読みたい

 

最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。

 

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

前の記事

ACCESSからテーブル・クエリデータを取得したい
【EXCEL：VBA】ACCESSからテーブル・クエリデータを…

次の記事

【EXCEL：VBA】サブフォルダを含む全てのフォルダやファイ…
サブフォルダを含む全てのフォルダやファイルの情報を取得したい