【Excel】エクセルにてタイムカード（打刻ボタンを押すだけ）の作り方と集計・計算方法【テンプレート作成・残業時間：関数やマクロ】

この記事では、エクセルにてタイムカード（打刻ボタンを押すだけ）の作り方と集計・計算方法【テンプレート作成・残業時間：関数やマクロ】を解説します。

それでは詳しく見ていきましょう！

タイムカードの仕様

作成の前に今回作成するタイムカードの仕様をお伝えいたします。

表のうち、青色セルは関数が入っています。

グレーのセルは任意で手動修正・入力が可能なセルです。

出勤ボタンを押下すると、日付欄に当日の日付、出勤欄に出勤時間が自動入力されます。

退勤ボタンを押下すると、退勤欄に退勤時間が自動入力されます。

出勤時間・退勤時間が入力されると、勤務時間欄にその日の勤務時間・残業時間が表示されます。

もし勤務時間が0時間を切る場合はエラーメッセージ欄にメッセージが表示されます。

タイムカードフォーマットの作成

まず、関数をつかってタイムカードのフォーマットを作ります。

赤字に関数を入れ、下図イメージまで作っていきましょう。

フォーマット作成

任意のファイル名でエクセルを新規作成します。

保存形式をExcelマクロ有効ブック（*.xlsm）に設定し、任意の場所に”名前をつけて保存”してください。

シート1に表を作成していきます。内容は以下です。シート名は任意で結構です。

・A1セル：タイトル

・B6～E7セル：社員番号・名前・合計_勤務時間・合計_残業時間

・B9～I30セル：日付・出勤・退勤・休憩・勤務時間・残業時間・備考・エラーメッセージ

後ほど作成するマクロに影響するので、日付欄はB列・出勤欄はC
列・退勤欄はD列で必ず作成してください。

サンプルでは最終行を30行目までとしていますが、任意の長さに設定してかまいません。

休憩欄作成

休憩欄のE10～E30セルには初期値として1:00を入力します。

この時セルの書式設定がユーザー定義のh:mmになっていることを確認してください。

勤務時間の関数作成

次に、勤務時間欄のF10セルに以下関数を設定します。

=IF(OR(C10=””,D10=””),””,D10-C10-E10)

勤務時間は、”退勤時間–出勤時間–休憩時間”です。

そのため数式はD10-C10-E10です。

もし出勤時間or退勤時間が空白の場合は空白で返したいので、IF(OR(C10=””,D10=””),””,D10-C10-E10)となります。

関数を最終行まで伸ばして完了です。

この時セルの書式設定がユーザー定義のh:mmになっていることを確認してください。

残業時間の関数作成

次に、残業時間欄のG10セルに以下関数を設定します。

=IF(OR(C10=””,D10=””),””,IF(F10<=TIME(8,0,0),0,F10-TIME(8,0,0)))

労働基準法の定義では、1日8時間、1週間40時間を超えた労働時間が残業時間になるので、残業時間を求めるには、”勤務時間-8時間”となります。

時間を計算するときは、TIME関数を利用します。

8時間を計算できるように、TIME(8,0,0)で表します。

F10-TIME(8,0,0)

次に、勤務時間が8時間以内だった場合、0を表示させるIF文を追加します。

IF(F10<=TIME(8,0,0),0,F10-TIME(8,0,0))

最後に、出勤時間or退勤時間が空白だった場合、空白を表示させるIF文を追加します。

IF(OR(C10=””,D10=””),””,IF(F10<=TIME(8,0,0),0,F10-TIME(8,0,0)))

関数を最終行まで伸ばして完了です。

この時セルの書式設定がユーザー定義のh:mmになっていることを確認してください。

エラーメッセージ欄作成

次にエラーメッセージ欄I10セルに以下数式を入力します。

=IF(IF(OR(C10=””,D10=””),””,D10-C10-E10)<0,”勤怠エラー：出勤・退勤・休憩時間を正しく入力してください。“,””)

出勤時間・退勤時間が空白の場合が空欄・勤務時間が0時間以下の場合に、“勤怠エラー：出勤・退勤・休憩時間を正しく入力してください。“というエラーメッセージを表示させます。関数を最終行まで伸ばします。

次に、エラー文が表示された時に文字が太字・赤字になるよう設定しましょう。

I列を指定し、ホームタブの条件付き書式→新しいルールを選択します。

ダイアログボックスが開くので、指定の値を含むセルだけを書式設定を選択

ルールの内容を以下に設定します。

・次の値に等しい

・勤怠エラー：出勤・退勤・休憩時間を正しく入力してください。

書式ボタンを押下し、フォントを赤字・太字に設定します。

OKを押下します。

これでエラーメッセージ欄の作成が完了しました。

合計_勤務時間欄の作成

次にF列の勤務時間を表すため、D7セルに以下数式を入力します。

=SUM（F:F）

この時セルの書式設定を、ユーザー定義の[h]:mmに変更します。

[h]:mmは、24時間以上の累計時間数を表します。h:mmのままにした場合24時間表記になってしまうので、合計値がおかしくなります。ご注意ください。

合計_残業時間欄の作成

次にG列残業時間の合計値を表すために、G7セルに以下数式を入力します。

=SUM（G:G）

この時セルの書式設定を、ユーザー定義の[h]:mmに変更します。

これで、タイムカードのフォーマットおよび関数の設定が完了しました。

出勤ボタン作成

ボタンフォーム作成

次にマクロをつかって出勤ボタンを押下すると、日付欄に当日の日付、出勤欄に出勤時間が自動入力されるボタンを作成していきましょう。

エクセルの開発タブの挿入→左上のボタンを押下します。

＜開発タブが表示されない場合＞

ファイル→オプション→リボンのユーザー設定→コマンドの選択から開発を選択

追加を押下→OKを押下します。

マウスが＋ボタンに代わるので、ワークシート上でドラッグしてボタンを描画します。

ダイアログボックスが出てきますので、マクロ名を”出勤_Click”に変更します。

マクロの保存先を”作業中のブック”に変更してOKを押下します。

ワークシート上にボタンが表示されます。

カーソルを合わせてボタン名を出勤に変更します。

文字の上で右クリックし、コントロールの書式変更から太字・文字の大きさを14に設定します。

ボタンが完成しました。

出勤マクロの登録

次にマクロを登録していきます。

出勤ボタンの上で右クリック→マクロの登録→新規作成

エディター画面が開きます。この画面のことをVisual Basic Editorと呼びます。

Sub　タイトル() ～　End　Subが一つのマクロの区切りになっています。

この間にプログラムを書いていきます。

タイトルには先ほどボタン作成時に設定した“出勤”が入っていますので、今回は設定不要です。

では早速、SubとEndSubの間に以下プログラムを入れましょう。

コピー＆ペーストでOKです。

  Dim WB As Workbook
  Dim WS As Worksheet
  Dim lastrow As Integer
  Dim D1 As Date
  Dim T1 As Date
  
  Set WB = ThisWorkbook
  Set WS = WB.ActiveSheet
  
    With WS
  
      lastrow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
  
      D1 = Date
      T1 = Format(Now(), "HH:MM")
  
      .Cells(lastrow, 2).Value = D1
      .Cells(lastrow, 3).Value = T1
  
  　End With

簡単にマクロの解説をしていきます。

まず全体をざっくり説明すると、変数の宣言・オブジェクト変数の設定・具体的な動きが書かれています。

変数の宣言

マクロ内で利用する変数と型を宣言します。

  Dim 変数名 As データ型

変数名は任意の文字列を指定できます。

データ型の指定は省略可能ですが、省略すると思わぬエラーが発生してしまうこともあるので、必ず指定するようにしましょう。省略した場合はデータ型はすべての型を表すVariantになります。

例えば、今回設定したD1という変数には日付を入れたいと思っているのでDate型で宣言しました。

Dim D1 As Date

・オブジェクト変数の設定

ワークブック・ワークシートなどのオブジェクトを変数に入れるためには、Setステートメントを使います。

Dim オブジェクト変数名 As オブジェクト型名

Set オブジェクト変数名 = オブジェクト

例えば、今回のプログラムではこのように書かれています。

Dim WB As Workbook

変数WBにワークブックを入れると宣言します。

Set WB = ThisWorkbook

変数WBにこのワークブックを格納します。

このように宣言することで、このワークブックを動かすことができます。

またSet WB = Workbook（“ブック名.xlsm”）のようにブック名を指定しているとこのワークブックだけでなく複数のブックを動かすことが可能です。

Dim WS As Worksheet

変数WSにワークシートを入れると宣言します。

Set WS = WB.ActiveSheet

変数WSに先ほど設定したWBのなかでアクティブなワークシートを格納します。

このように、ワークシートも指定することができます。

もしシート名の変更予定がない場合は、Set WB = WB.worksheets（“シート名”）でも指定可能です。またSet WB = WB.worksheets（“Sheet1”）と表記すれば、一番左のシートと指定することもできます。シートが複数ある場合、どのシートを動かせばよいかを必ず指定するようにしましょう。

Withステートメント

Withステートメントを利用すると、指定オブジェクト（主語）に対しての処理（動詞）を省略して記述できます。

今回の記述でご説明すると、WSが主語になります。

  With WS

      .Cells(lastrow, 2).Value = D1

      .Cells(lastrow, 3).Value = T1

  　End With

こちらを省略せずに記述するとこのようになります。

WS.Cells(lastrow, 2).Value = D1

WS.Cells(lastrow, 3).Value = T1

Withステートメントを利用せずにコードを記述することも可能ですが、長いコードを記述すると記述ミスや修正時に書き替え漏れなどが発生します。Withステートメントを利用することでコードの可読性も高まります。何度も同じ変数を利用するときは極力Withステートメントを利用しましょう。

利用方法はまずWithオブジェクト名で主語を指定します。

End　Withまでの間で主語を省略できるようになります。

.と記述すると、WB.と入力した場合と同じ動きをします。

  With オブジェクト名

      .Cells(lastrow, 2).Value = D1

  End With

具体的な動きの説明

With内の具体的な動きを簡単に説明します。

   With WS

      lastrow = .Cells(Rows.Count, 2).End(xlUp).Row + 1

  　　変数lastrowにB列最終行に＋1した数を代入します。

      D1 = Date

変数D1に本日の日付を代入します。

      T1 = Format(Now(), “HH:MM”)

変数T1に現在時刻をHH:MMの形式で代入します。

      .Cells(lastrow, 2).Value = D1

B列最終行の一行下にD1の値を代入します。

      .Cells(lastrow, 3).Value = T1

C列最終行の一行下にD1の値を代入します。

  　End With

最終行取得について

今回のように日付欄の最終行が毎日変動する場合、どのように最終行を指定すればよいでしょうか。コードの記述は以下です。

lastrow = .Cells(Rows.Count, 2).End(xlUp).Row + 1

手作業でA1セルから一番下のデータに移動するときCtrl + ↓を使うと思います。

これをマクロでは、

Cells(1, 1).End(xlDown)

このように書きます。

しかし途中に空欄のセルがある場合、空白のセルで止まってしまい、最終行に行き着くことができません。

空白行があっても一番下のセルを取得するためにはA列の一番下のセルからCtrl + ↑を使って最終行を取得します。

記述はこうなります。

Cells（Rows.Count,1）.End(xlUp)：Ctrl+↑

Rowsとはシート全行のことで、Countなのでその数＝シートの行数ということです。

Ａ列の一番下（空白含む）からCtrl + ↑して止まったところ＝値がある行ということになります。マクロではこのコードは必須なので、覚えておくとよいでしょう。

今回は出勤時刻を最終行の1つ下の行に入れたいので、その行数に＋1した数をlastrowに設定します。

日付を取得する

      D1 = Date

      T1 = Format(Now(), “HH:MM”)

日付を取得するには、Date関数を利用します。時刻を取得するにはNow関数を利用します。今回は、時間を15:30のような形で取得したいのでFormat関数で書式を指定して取得しています。

コードをペーストしたら上書き保存し、Visual Basic Editorを×で閉じます。

出勤ボタンが完成しました。

ボタンを押すと、本日の日付が日付欄に、現在時刻が出勤欄に表示されました。

フォームボタン（退勤）の作成

 最後に退勤ボタンを作成し、マクロを登録していきます。

出勤ボタンと同じようにエクセルの開発タブの挿入→左上のボタンを押下

マウスが＋ボタンに代わるので、ワークシート上でドラッグしてボタンを描画

ダイアログボックスが出てきますので、マクロ名を”退勤_Click”に変更します。

マクロの保存先を”作業中のブック”に変更してOKを押下します。

ワークシート上にボタンが表示されます。

カーソルを合わせてボタン名を退勤に変更します。

文字の上で右クリックし、コントロールの書式変更から太字・文字の大きさを14に設定します。

ボタンが完成しました。

退勤マクロの登録

次にマクロを登録していきます。

退勤ボタンの上で右クリック→マクロの登録→新規作成

エディター画面が開きますので、Sub　退勤() ～　End　Subの間に以下コードをペーストします。

  Dim WB As Workbook
  Dim WS As Worksheet
  Dim lastrow As Integer
  Dim T1 As Date
  Dim R1 As VbMsgBoxResult
  
  
  Set WB = ThisWorkbook
  Set WS = WB.ActiveSheet
  
  With WS
  
    lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
  
      If .Cells(lastrow, 4) = "" Then
      
        T1 = Format(Now(), "HH:MM")
        .Cells(lastrow, 4).Value = T1
        
        Else
      
      R1 = MsgBox("既に退勤時間が入力されています。このまま上書きしますか？", Buttons:=vbYesNo)
    
       If R1 = vbYes Then
         
        T1 = Format(Now(), "HH:MM")
        .Cells(lastrow, 4).Value = T1
       
       Else
       
        MsgBox "退勤時間を入力せず終了します。"
       
       End If

      End If
  
  End With

簡単にマクロの解説をしていきます。

まず中身をざっくり説明すると、変数の宣言・オブジェクト変数の設定・具体的な動きが書かれています。

出勤ボタンで変数の宣言・オブジェクト変数の設定・最終行取得・日付取得については、簡単にご説明しましたので、初めて登場したIFステートメントについてご紹介します。

IFステートメント

条件式に合致するか否かで処理方法を変更するときに利用します。

If 条件式 Then

    処理1

Else

    処理2

End If

今回のIF文は二つの条件分岐を組み合わせています。

条件1:D列最終行に値が入っていない場合　→Yes/No

No→　条件2:退勤時間を上書きする場合　→Yes/No

一つ一つのコードを説明すると以下になります。

     If .Cells(lastrow, 4) = “” Then

     D列最終行に値が入っていない場合

        T1 = Format(Now(), “HH:MM”)

変数T1に現在時刻を代入

        .Cells(lastrow, 4).Value = T1

        D列最終行にT1の値を代入

        Else

D列最終行に値が入っている場合

      R1 = MsgBox(“既に退勤時間が入力されています。このまま上書きしますか？“, Buttons:=vbYesNo)

以下メッセージボックスを表示させます。

       If R1 = vbYes Then

“はい”をクリックした場合

T1 = Format(Now(), “HH:MM”)

変数T1に現在時刻を代入

        .Cells(lastrow, 4).Value = T1

        D列最終行にT1の値を代入

       Else

“いいえ”やそれ以外をクリックした場合

        MsgBox “退勤時間を入力せず終了します。“

以下メッセージを表示し、マクロを終了します。

       End If

      End If

コードをペーストしたら上書き保存し、Visual Basic Editorを×で閉じます。

退勤ボタンが完成しました。

ボタンを押すと、現在時刻が退勤欄に表示されました。

エラーメッセージについて

出勤時間が16:46/退勤時間が17:36の場合、エラーが表示されます。

原因は勤務時間がマイナスになっているためです。

退勤時間 17:36―出勤時間 16:46―休憩時間 1:00 ＝　-00:10

休憩時間は1:00で初期設定されていますが、勤務時間が50分の場合、休憩時間が50分を超えることはないので休憩時間を修正しましょう。

0:00と手入力で修正します。

正しい値を入力するとでエラーを修正することができました。

まとめ　エクセルにてタイムカードの作り方と集計・計算方法【テンプレート作成・残業時間：関数やマクロ】

この記事では、エクセルにてタイムカード（打刻ボタンを押すだけ）の作り方と集計・計算方法【テンプレート作成・残業時間：関数やマクロ】をご紹介しました。

ぜひ、アレンジしてご自身の業務でも活用してみてください。