Excel VBA 実践レポート（勤怠・給与管理ツール編②）

こんにちは、ECF Tech
担当 Michiharu.Tです。

Excel VBA 実践レポートと題しまして、実用的なツールを作成するための試行錯誤の状況やプログラムの解説をお伝えしています。今回は、勤怠・給与管理ツール作成の第２回です。前回から作成している勤怠入力シート完成に向けて必要な機能を追加するとともに、誤操作を未然に防ぐツールづくりを心がけて進めます。

途中、筆者の試行錯誤が見える部分もあるかもしれませんが、VBAツールの作成練習用の題材としても使用できると思いますので、ご活用頂けたら幸いです。

前回の記事は下のリンクから、ご確認いただけます。

Excel VBA 実践レポート（勤怠・給与管理ツール編①）

こんにちは、ECF Tech担当 Michiharu.Tです。Excel VBA 実践レポートと題しまして、実用的なツールを作成するための試行錯誤の状況やプログラムの解説をお伝えしたいと思います。いくつかの記事に分けてお伝えするボリュームに...

また、その他のExcel VBA関連の教材および練習問題は下記よりご覧いただけます。

【初心者向け】動かして学ぶExcel VBA入門

Excel VBAを動かしながら学べる連載をお送りしています。講座目次第１回 Excel VBAを始めようExcel VBAとはExcelマクロを動かしてみようExcelマクロの保存第２回基本操作(1)セルに値を設定セルの削除オブジェクト...

本記事の対象読者
本記事で実装するしくみ
実践レポート
まとめと次回の課題

本記事の対象読者

Excelの基本的な操作知識をお持ちの方
Excel VBAの基本的な知識をお持ちの方
Excel VBAで何らかのツール（便利なしくみ）を構築したいと考えている方

本記事で実装するしくみ

本記事では、次のしくみを実装します。

開始列、終了列、休憩列は、「0:00～23:59」の値しか入力できないようにする。また、入力ミスの場合には適切なメッセージを表示する。
入力できない部分を保護する。
出勤日数、欠勤日数、総勤務時間、時間外労働の項目を自動計算する。

実践レポート

ここから、実際の作成手順を紹介しながら、感じたことを合わせて書いていきたいと思います。こちらから、前回終了時点でのExcelファイルをダウンロードできます。

開始、終了、休憩列の入力制限

前回の記事で、開始列(F9～F39セル)、終了列(I9～I39セル)、休憩(L9～L39セル)を時刻形式に表示設定しましたが、入力規則については特に設定をしていません。これらの列には、「0:00～23:59」までの値しか入れられないように設定します。

メニューからデータ > データの入力規則とたどります。

設定タブで次の項目を入力します。

入力値の種類：時刻
開始時刻：0:00
終了時刻：23:59

次にエラーメッセージタブで次の項目を入力します。

タイトル：時刻入力エラー
エラーメッセージ：時刻は0:00～23:59の範囲を指定してください。

設定できたら、テストとして該当するいずれかのセルに25:00などの不正な値を入力してみましょう。下記のように表示されればOKです。

時間外、勤務時間列の式の見直し

時間外列、勤務時間列の式を見直したいと思います。両列には現在、それぞれ計算式が入っていますが、計算結果が負数となった場合には次のように「######」が表示されてしまいます。

上の例では、開始時刻と終了時刻が前後しているため、勤務時間列および時間外列の計算結果が負数になってっしまっています。

これに対応するため、計算結果が負数になってしまった場合には、一律に「0:00」となるように式を修正します。

勤務時間の先頭列(R9セル)には、=IF(I9-F9-L9<0,0,I9-F9-L9)を入力します。これにより、式I9-F9-L9が0より小さい場合には0が設定され、そうでない場合には式の結果が出力されます。

式を入力したら、R10～R39セルにコピーします。

時間外列の先頭列(O9セル)には、=IF(R9-管理情報!B$2<0,0,R9-管理情報!B$2)を入力します。こちらも行っている処理は同様です。式R9-管理情報!B$2が0より小さい場合には0、そうでない場合には式の結果が出力されます。

式を入力したら、O10～O39セルにコピーします。

セルの修正ができたら、先ほどのように誤った時刻が入力されていても0:00を表示することができます。

シートの保護を設定する

利用者が入力すべき部分以外を操作できないように、シートを保護します。シート保護は、「入力可能な場所だけを設定して、後は保護(操作不可)する」という考え方で設定します。勤怠入力シートで、操作可能なセルは下図の緑色の部分です。

※勤怠、開始、終了、休憩、備考列は39行目までが操作可能セルです。

シートの保護をする前に、これらのセルを保護対象から外します。任意のセルやセル範囲を選択し、右クリックします。次にセルの書式設定をクリックします。

保護タブを選択し、ロックのチェックボックスを外し、OKをクリックします。

必要なセルのロックをすべて外したら、メニューから、校閲 > シートの保護を選択します。

次のようなダイアログが表示されますが、何も操作せずにOKをクリックします。

シートの保護が開始されます。「保護したセルが操作できないこと」と「保護していないセルが操作できること」を確認します。保護しているセルを操作しようとした場合には、次のようなエラーを出力します。

出勤日数、欠勤日数の算出

出勤日数(L3セル)と欠勤日数(O3セル)を計算する式を設定します。いずれも、勤怠列(C9～C39セル)内の「出勤」「欠勤」などのセルの数を数えることで求めたいと思います。

出勤日数(L3セル)には、式=COUNTIF(C9:E39,"出勤")+COUNTIF(C9:E39,"有休")を入力します。COUNTIF関数は、条件を満たすセルの数を返す関数です。式内の最初のCOUNTIF関数では、勤怠列から 「出勤」の数 を数えています。また、有休についても出勤数としてカウントしたいので、もう1つのCOUNTIF関数で 「有休」の数 を数えています。出勤と有休の２つの日数を合計したものが出勤日数となります。

欠勤日数(O3セル)には、式=COUNTIF(C9:E39,"欠勤")を入力します。考え方は出勤日数セルの式と同様です。「欠勤」の数を数えています。

総勤務時間と時間外労働時間の算出

総勤務時間(R3セル)と時間外労働時間(U3セル)を計算する式を追加します。総勤務時間は勤務時間列の合計、時間外労働は時間外列の合計で求めることができます。それぞれの式は次のようになります。

総勤務時間(R3セル)の式：=SUM(R9:T39)
時間外労働(U3セル)の式：=SUM(O9:Q39)

(考察)集計の算出について

ここまで、出勤日数、欠勤日数、総勤務時間、時間外労働時間の算出の式を追加しましたが、ここはマクロにするべきか悩ましいポイントです。必要な機能を実装する方法としては、「Excel機能と関数を利用する方法」と「マクロを利用する方法」がありますが、それぞれ次の特徴があると考えています。

	Excel機能と関数	マクロ
メリット	入力と同時に即時反映されるので、利用者がわかりやすい。 VBAの知識がない人でも修正できる。	処理がマクロにまとめられるので、VBAを知っていれば、何が行われているかわかりやすい。後からの仕様変更に対応しやすい。
デメリット	後からの仕様変更に伴い、結局マクロに変更せざるを得ない場合がある。式や設定が様々なセルに散在し、処理が見えにくい。	VBAの知識が必要。

一概にどちらが良いとは言えず、ケースバイケースで考えていくと良いと思います。

ちなみに、出勤日数、欠勤日数、総勤務時間、時間外労働時間の算出をマクロで実装した場合のプログラム例は次のようになります。今回の実装には採用しなかったので詳細な解説は割愛いたしますが、プログラム内の多くコメントを入れていますので、参考になれば幸いです。

'月の各種集計結果を算出する
Sub CalcMonthlyTotal()
    '各変数の宣言
    Dim workDayTotal    '出勤日数
    Dim AbsentTotal     '欠勤日数
    Dim workTimeTotal   '勤務時間
    Dim overTimeTotal   '時間外労働時間
    Dim i               'ループカウンタ
    Dim kintaiWs        'ワークシート保持変数

    '各変数の初期化
    workTimeTotal = 0
    AbsentTotal = 0
    workTimeTotal = 0
    overTimeTotal = 0
    '「勤怠入力」ワークシートを変数で保持
    Set kintaiWs = Worksheets("勤怠入力")

    '31日分のデータを確認
    For i = 1 To 31 Step 1
        Select Case Cells(8 + i, 3).Value
            '出勤と有休は勤務日扱い
            Case "出勤", "有休"
                '出勤日数を集計
                workDayTotal = workDayTotal + 1
            Case "欠勤"
                '欠勤日数を集計
                AbsentTotal = AbsentTotal + 1
        End Select
        '勤務時間を集計
        workTimeTotal = workTimeTotal + Cells(8 + i, 18).Value
        '残業時間を集計
        overTimeTotal = overTimeTotal + Cells(8 + i, 15).Value
    Next

    '出勤日数をL3へ設定
    kintaiWs.Range("L3").Value = workDayTotal
    '欠勤日数をO3へ設定
    kintaiWs.Range("O3").Value = AbsentTotal
    '勤務時間をR3へ設定
    kintaiWs.Range("R3").Value = workTimeTotal
    '時間外労働時間をU3へ設定
    kintaiWs.Range("U3").Value = overTimeTotal

End Sub