残業手当や深夜手当を含むバイト代をExcelで自動計算する

第9回の連載で「バイトの勤務時間と給与」を計算する方法を紹介したが、その内容は「残業手当」や「深夜手当」を無視した、かなり大雑把なものでしかなかった。このため、実際には不十分なケースが多いと考えられる。そこで今回は、これらの問題を解決するために、関数IFの条件に「時間」を指定する方法を紹介していこう。

計算表の作成と表示形式の指定

Excelには、条件に応じて処理を分岐できる「関数IF」が用意されている。関数IFの基本的な使い方はすでに知っている方が多いと思われるので、今回は関数IFの条件に「時間」を指定する方法を紹介していこう。これにより「残業手当」や「深夜手当」を含めたバイト代などを算出することも可能となる。応用範囲は広いので、ぜひ覚えておこう。

関数IFの条件などに「時間」を指定する方法

まずは、第9回の連載で作成した計算表を簡単に紹介しておこう。この表では、（退時刻）－（出時刻）の数式で「勤務時間」を算出し、その合計に「時給」を掛け算することで「給与」を求める、という処理を行った。

第9回の連載で作成した勤務時間と給与の計算表

ただし、実際の給与計算においては、これだけでは不十分な場合が多いと考えられる。というのも、1日8時間を超えた分の「残業手当」や、22時以降に勤務した場合の「深夜手当」が加算されていないからだ。また、勤務の途中に休憩時間があるケースもあるだろう。

そこで今回は、これらの問題にも対応できるように、計算表を改良した例を紹介していこう。まずは、以下のような形に表を作成しなおす。

表の準備

続いて、「時刻」や「時間」を示すセルの表示形式を変更する。以下の図に示したセル範囲を選択し、「セルの書式設定」を呼び出す。

セル範囲の選択

「セルの書式設定」が表示されたら「ユーザー定義」の表示形式を選択し、種類に[h]:mmと入力して［OK］ボタンをクリックする。

表示形式に[h]:mmを指定

これで24時間以上の時間（時刻）も適切に表示できるようになる。この書式指定を忘れると、「労働時間の合計」などが正しく表示されないケースがあるので注意すること（詳しくは第9回の連載を参照）。

なお、「退時刻」が深夜24時を超える場合は、「25:00」のように「当日の時刻」として時刻を入力するのが基本だ。表示形式に[h]:mmを指定すると、こういった24時以降の時刻もそのまま表示されるようになる。

24時以降（翌日の深夜）の表示例

休憩時間を除いた労働時間の計算

それでは、具体的な計算方法を紹介していこう。まずは、勤務の途中に「休憩時間」がある場合の対処法だ。今回の例では、各日の「休憩時間」を入力するセルを用意することで、この問題に対応している。

休憩時間の入力

これで「休憩時間」を差し引いた「労働時間」を算出できる。具体的には、（退時刻）－（出時刻）で拘束時間を算出し、さらにそこから「休憩」の時間を引き算すればよい。よって、（退時刻）－（出時刻）－（休憩）と数式を入力する。

労働時間の計算

自動計算された労働時間

あとは、この数式をオートフィルでコピーするだけ。これで各日の「労働時間」を自動計算できる。

オートフィルで数式をコピー

関数IFを使った残業時間の計算

続いては、各日の「残業時間」を自動計算する方法を紹介していこう。1日の労働時間が8時間を超える場合は、8時間を超える部分を「残業」（時間外労働）として扱わなければならない。

この計算は「関数IF」を使って以下のように処理すると実現できる。

・条件：「労働時間」が8時間を超えるか？

・超える場合（真の場合）
　（労働時間）－（8時間）で「残業時間」を算出

・超えない場合（偽の場合）
　「残業時間」は0時間（なし）

前回の連載で解説したように、Excelは「時間」をシリアル値で管理している。シリアル値では24時間が数値の「1」に相当するので、8時間は「8/24」という数値になる。これを関数IFの引数に指定していけばよい。順番に解説していこう。

第1引数の「条件」は、「労働時間が8時間を超えるか？」となる。つまり、「F4セルが8/24より大きいか？」を指定すればよいことになる。これを比較演算子を使って書くと、「F4>8/24」という記述になる。

労働時間が8時間を超える場合（真の場合）は、その残業時間を算出する計算を行う。これは（労働時間）－（8時間）で計算できるので、第2引数には「F4-8/24」という数式を記述すればよい。

労働時間が8時間を超えない場合（偽の場合）は、残業時間は「なし」と考えられる。よって、第3引数にはゼロ（0）または空文字（""）を指定すればよい。

以上をまとめると、関数IFの記述は以下の図のようになる。

関数IFの入力

「Enter」キーを押して結果を見ると、残業時間が正しく計算されているのを確認できるはずだ（10時間－8時間＝2時間）。

関数IFにより自動計算された残業時間

あとは、この関数IFをオートフィルでコピーするだけ。これで各日の「残業時間」を自動計算できる。

オートフィルで関数IFをコピー

関数IFを使った深夜勤務時間の計算

続いては、「深夜勤務」の時間を自動計算する方法を紹介していこう。こちらは22時以降に働いた時間を調べればよいことになる（厳密には22時から翌朝5時）。この計算を「関数IF」で処理する場合の処理手順は以下のようになる。

・条件：「退時刻」が22時より遅いか？

・遅い場合（真の場合）
　（退時間）－（22時）で「深夜勤務の時間」を算出

・遅くない場合（偽の場合）
　「深夜勤務の時間」は0時間（なし）

ここでのポイントは、22時をシリアル値で示すと「22/24」という数値になること。これを理解していれば、先ほどの「残業時間」と同じような考え方で「関数IF」を記述できるはずだ。具体的に示すと、関数IFの記述は以下の図のようになる。

関数IFの入力

上図の例では23時に退出しているので、22時以降の勤務時間は1時間になる。関数IFにより、これが正しく計算されていることを確認できるだろう。

関数IFにより自動計算された深夜勤務時間

あとは、この関数IFをオートフィルでコピーするだけ。これで各日の「深夜勤務の時間」を自動計算できる。

オートフィルで関数IFをコピー

それぞれの合計時間と給与の計算

ここまで計算できたら、それぞれの合計時間を算出する。この計算は「関数SUM」で実行できる。各セルの表示形式に[h]:mmを指定していれば、24時間以上の時間も適切に表示されるはずだ。

労働時間の合計を関数SUMで算出

オートフィルで関数SUMをコピー

続いて、バイト代（給与）の計算を進めていこう。まずは、（時給）×（労働時間）を計算する。第9回の連載でも紹介したように、この数式は（時給）×（労働時間）×24と記述しなければならない。シリアル値の1時間は「1/24」という数値になることを考慮して、最後に「24」を掛け算する必要があることを忘れないようにしよう。