いまさら聞けないExcelの使い方講座

【Excel】勤務時間や給与を計算するにはいつものやり方ではダメ！エクセルで正しく時間を計算するテクニック

トップスタジオ

2019年9月2日 06:55

勤務時間の合計や給与が正しく計算できない！

　Excelを使って売上表などの金額の計算を行うことはよくありますが、Excelでは勤務時間のような時間の計算もできるので、勤怠管理をExcelで行っている職場も多いかもしれません。ある企業で、新たに勤怠管理表（①）をExcelで作成したとします。この表のE列には、毎日の勤務時間が入力されています（勤務時間は、退勤時刻－出勤時刻－休憩時間で求められます）。

　この勤怠管理表のセルF34に、1か月の勤務時間の合計を求める欄を作り、「=SUM(E4:E33)」（②）という数式を入力しました。

　ところが、1か月の勤務時間の合計を求めたはずなのに、セルF34には「5:30」（③）と表示されています。出勤している日は常に7時間以上は勤務しているのに、1か月の勤務時間の合計が「5:30」というのはおかしいですよね。

　このように、時間の合計を計算した時に合計が24時間を超えると正しい結果が表示されないことがあります。この勤怠管理表では、この下のセルF36で給与の計算も行えるようにしたいのですが、このままでは正しい給与を計算することもできませんよね。

　今回は、時間の合計が24時間を超える時にもセルに正しく合計を表示させる方法と、Excelで時給と勤務時間を掛けて給与を正しく求める方法を解説します。

24時間を超える時間を表示させたい場合は表示形式を変更する

　それでは、先ほどの勤怠管理表の例で、1か月の勤務時間の合計が正しく表示されるように修正してみましょう。勤務時間の合計を求める数式を入力したセルF34（①）をクリックして選択し、［ホーム］タブの［数値］グループの右下にある矢印（②）をクリックします。

　［セルの書式設定］ダイアログボックスが表示されます。［表示形式］タブ（③）をクリックし、［分類］欄で［ユーザー定義］（④）を選択します。［種類］欄に「[h]:mm」（⑤）と入力し、［OK］（⑥）をクリックします。ここで、hを[]で囲んで入力することにより、24時間を超える時間が正しく表示されるようになります。

　表示形式が変更され、1か月の勤務時間の合計がセルF34に正しく表示されました（⑦）。

　今回の例の場合、セルF34に「5:30」と表示された時に、合計として正しい数値ではないことに気づかずスルーしてしまうと、勤務時間の管理やこのあと行う給与の計算が正しく行えなくなってしまいます。数式を作成したあとは、セルに表示されている値が想定通りのものかどうかをきちんと確認する癖をつけておくと、このような表示形式の違いや自分のミスにも気づきやすくなりますよ。

時給と勤務時間を掛けて給与を求める計算でもひと工夫が必要

　1か月の勤務時間の合計が正しく計算できたので、時給と勤務時間を掛けて1か月分の給与を求めてみましょう。「時給×勤務時間」で給与が計算できるので、セルF36に「=F35*F34」（①）と入力して［Enter］キーを押してみます。

　ところが、表示された値は正しい給与の額よりも明らかに少ない値（②）です。

　なぜこのようなことが発生するかを説明する前に、Excelがどのようにして時間のデータを扱っているかを覚えておきましょう。今回のように、時刻データをもとに給与を計算する場合、そのまま掛け算をしても正しい値は求められません。時刻として表示されている値を数値表示に変換する必要があります。セルF34に表示された101:30（101時間30分）は、101.5（時間）として計算する必要があるのです。

　Excelでは、時間を「シリアル値」というもので管理しています。時間のシリアル値は、0から始まって24時間で「1」となる小数です。例えば、12:00はシリアル値だと「0.5」、6:00は「0.25」になります。

　24時間で「1」になるということは、シリアル値に24を掛ければ時刻データを数値に変換できるということになります。少し複雑ですが「数値に変換するには24を掛ければよい」と覚えておいてください。

　この説明だけだとよくわかりにくいかもしれないので、実際に計算してみましょう。先ほど数式を入力したセルF36に「=F35*F34*24」（③）と入力し、［Enter］キーを押します。

　時刻がシリアル値に24を掛けた値になったことで、給与が正しく計算されました（④）。

　シリアル値だった時刻が、24を掛けることで数値に換算され、時間×時給で正しく給与が求められるようになったというわけです。「時給から給与を求める時は24を掛ける」ことを覚えておきましょう。