第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」を掛け算する必要があることを忘れないようにしよう。

  • (時給)×(労働時間)の計算

残業時間ついては、時給の25%以上を「時間外手当」として割増する必要がある。仮に、この割増分を25%とする場合は、(時給の25%)×(残業時間)×24で「残業手当」を計算できる。こちらも最後に「24」を掛け算する必要があることを忘れないこと。

  • 残業時間の割増分の計算

さらに深夜勤務の時間についても、時給の25%以上を「深夜手当」として割増しなければならない。割増分を25%とする場合、その数式は(時給の25%)×(深夜勤務時間)×24という記述になる。

  • 深夜勤務の割増分の計算

最後に、これらの金額を「関数SUM」で合計すると、バイト代(給与)の総額を算出できる。

  • それぞれの合計を関数SUMで計算

  • 算出されたバイト代(給与)

このように時間と数値(シリアル値)の関係を把握していれば、関数や数式の中でも自由に「時間」を扱えるようになる。給与計算だけでなく、さまざまな分野に応用できるので、この機会に「時間」の扱い方をよく研究しておくとよいだろう。

なお、筆者は法律の専門家ではないため、上記で説明した給与計算は「厳密に正しいもの」とは限らないことに留意していただきたい。「週40時間を超える部分も残業扱いになる」など、上記の例は法令に完全対応できていない部分もある。

今回の連載で紹介した内容は、関数・数式で「時間」や「時刻」を処理するときの「わかりやすい例のひとつ」として捉えていただければ幸いだ。

各工程に要する時間をもとにスケジュール表を作成する場合、「入館時刻」と「退館時刻」のデータをもとに各時間帯の入場者数を求める場合など、計算に「時間」や「時刻」を含められると便利なケースは数多く考えられる。このような場合に役立つ知識として、シリアル値の扱い方を覚えておくと、きっと役に立つはずだ。