【Excel関数】時刻で入力された労働時間を集計する方法

Windows PC

こんにちは、ふみです。みなさんは表計算ソフト「Microsoft Excel(エクセル)」で日中の労働時間や時間外労働、休日労働等の時間を集計したことはありますか?
私は仕事毎に時刻(日付型)で入力された労働時間をExcelで集計していて、24時間を超えると時刻表示が「0:00」に戻ってしまうので、色々工夫した記憶があります。

時刻(日付型)の時間集計は主に時刻形式で集計する方法と、時間を数値に変換して集計する方法の2種類に分かれます。

始業時刻と終業時刻から1日の労働時間を算出する

休憩時間を除外した労働時間

労働時間は「(終業時刻)-(始業時刻)-(休憩時間)」です。
始業時刻をセルA10、終業時刻をセルB10、休憩時間を1時間30分とすると、労働時間を算出するセルは次のように設定します。

=IF($A10="","",$B10-$A10-TIME(1,30,))

これでこの日の労働時間を時刻で表示することができます。

労働時間の数式のみにした場合、未入力の際にセルの値が「-1:30」で表示は「####…」になり、集計時間を正確に集計できなくなります。そのため、セルA10に始業時刻を入力するまでは何も表示されないようにIF関数を使っています。(空白処理)

前に入力されていたデータの影響で時刻で表示されなかった場合、私が使っている Office 2019 のExcelでは入力したセルを右クリック→セルの書式設定(F)…でセルの書式設定を開いた後、分類(C):を「時刻」、種類(T):から「13:30」を選択して「OK」をクリックすると時刻の表示形式に変えることができます。

夜間作業で終業が0時を超える労働時間

夜間作業で0時を超える可能性がある場合は、数式を次のように設定します。
始業時刻をセルA10、終業時刻をセルB10、休憩時間を1時間30分としています。

=IF($A10="","",$B10-$A10+($A10>$B10)-TIME(1,30,))

これで夜間作業で0時を超えた場合、終業時刻に+1日(=24:00)されるので労働時間を集計することができます。

昼休憩のない半日出勤の労働時間

会社の制度に昼休憩のない半日出勤がある場合は、数式を次のように設定します。
始業時刻をセルA10、終業時刻をセルB10、休憩時間を1時間30分としています。

=IF($A10="","",$B10-$A10+($A10>$B10)-TIME(1,30,)*($B10-$A10+($A10>$B10)>=TIME(8,30,)))

これで「(終業時刻)-(始業時刻)」が8:30以上の時に休憩時間を1時間30分を計算するようになるので、8:30未満のときは半日出勤と判定して休憩時間の1時間30分を計算しなくなります。

一定期間の労働時間を時刻で集計する

時刻で入力された労働時間を時刻で集計するには時間を集計するセルをSUM関数で集計します。
労働時間の範囲をセルC10~セルC19とすると、集計するセルの数式は次のように設定します。

=SUM(C$10:C$19)

これでセルC10~セルC19の合計時間を集計しますが、このままでは24時間を超える毎に表示が「0:00」に戻ってしまいます。
そこで、合計時間のセルの書式設定を変更します。

私が使っている Office 2019 のExcelの場合、入力したセルを右クリック→セルの書式設定(F)…でセルの書式設定を開いた後、分類(C):を「ユーザー定義」、種類(T):のウインドウに「[h]:mm」を手入力して「OK」をクリックします。

これで24時間を超えても合計時間が時刻表示されます。
例えば合計が25時間の場合、セルの書式設定をする前は「1:00」で表示されてしまいますが、設定後は「25:00」で表示されるようになります。

一定期間の労働時間を数値で集計する

時刻で入力された労働時間を数値で集計するには1日(=24時間)を1とする日付型から1時間を1とする数値形式に変換する必要があります。
時間を集計するセルをSUM関数で集計した後、24倍します。
労働時間の範囲をセルC10~セルC19とすると、集計するセルの数式は次のように設定します。

=SUM(C$10:C$19)*24

セルの書式設定は分類(C):を「標準」にします。
これで合計時間が数値になります。
例えば合計が25時間30分の場合、「25.5」で表示されるようになります。

Execl関数の豆知識

TIME関数について

TIME関数は時、分、秒を個別に入力して日付型の値を表示させる関数です。
1時間は1/24になります。日付型の1時間を1にする場合は24倍する必要があります。

TIME関数は分、秒を入力する必要がない場合もカッコ内の2つの「,」は必要です。

IF関数で空白処理したセルの足し算

IF関数で空白処理したセルはそのまま足し算すると「#VALUE!」が表示されて計算できませんが、SUM関数を使うと計算することができます
SUM関数は範囲を合計するときに良く使いますが、セルのアドレスを「,」(カンマ)で区切るとピンポイントで足し算ができます。

例えば空白処理をしたセルA1とB2、C5を合計する場合、「=A1+B2+C5」ではエラーが出てしまいますが、

=SUM(A1,B2,C5)

と設定すると空白のセルを無視して足し算することができます。

セルの相対参照と絶対参照

Excelでは参照しているセルの行番号や列番号の前に「$」をつけると絶対参照にすることができます。
「$」をつけない相対参照はコピーするとコピー先へアドレスが移動した行列分、参照先も移動しますが、絶対参照にすると参照先のアドレスが移動しなくなります。
この絶対参照の設定は特に複数の人が編集する可能性のあるフォーマットにおいて、後の編集で誤りを防ぐのに非常に有効です。

セルのアドレスを絶対参照に設定するポイントは次のとおりです。

  • 「$B10-$A10」のように横方向に計算するときは横にアドレスが移動すると数式が機能しなくなる為、列番号を絶対参照にする。
  • 「SUM(C$10:C$19)」のように縦方向に計算するときは縦にアドレスが移動と数式が機能しなくなる為、行番号を絶対参照にする。
  • 表形式になっていない単独のセルを参照するときは縦横共にアドレスが移動すると数式が機能しなくなる為、行番号、列番号の両方を絶対参照にする。

絶対参照の設定は数式に「$」を手入力して設定できます。または数式バーのセルのアドレスをクリックしてからファンクションキーの「F4」を押すたびにクリックしたセルのアドレスが「→絶対参照→行番号のみ絶対参照→列番号のみ絶対参照→相対参照…」と変化させることができます。

IF関数を使わない条件分岐

今回紹介した労働時間の計算では終業が0時を超える労働時間の判定と、昼休憩のない半日出勤の判定の条件分岐は、IF関数を使っていません。

例えば、終業が0時を超える労働時間を判定する式の「+($A10>$B10)」はIF関数で表すと「+IF($A10>$B10,1,0)」になります。
このようにTrueを1、Falseを0で返す条件分岐は、IF関数を使わずに条件式をかっこで閉じるだけで機能します

まとめ

  • 24時間を超える時刻を表示させるにはセルのユーザー定義の種類(T):に「[h]:mm」を手入力する
  • 日付型の1時間を1にする場合は24倍する
  • IF関数で空白処理したセルはSUM関数で計算できる
  • 計算で参照しているセルのアドレスは必要に応じて絶対参照「$」を設定する
  • セルのアドレスの絶対参照は数式バーからファンクションキーの「F4」で設定できる
  • Trueを1、Falseを0で返す条件分岐は、条件式をかっこで閉じるだけで機能する

ありがとうございました。

コメント

タイトルとURLをコピーしました