Excel で残業代を計算

こんにちは、デジタル事業本部の藤田です。

一般的にサラリーマンの給与には、時間外の労働に対し残業代として割り当てられる手当が含まれます。とはいえ、残業代の計算は一体どのような仕組みになっているのでしょうか。サンプル問題をもとに Excel で計算してみようと思います。

問題
下記の条件で求められる割増賃金として、次のうち正しいものはどれか。

【条件】
〇1日所定労働日数：8時間
〇賃金締め日：毎月末日
〇賃金支給日：翌月25日
〇給与：時間給1,000円
〇勤怠状況
9月11日（水）：残業42分
9月13日（金）：残業90分
9月17日（火）：残業164分
9月18日（水）：残業95分
9月20日（金）：残業175分
9月22日（日）：休日出勤490分
9月25日（水）：残業240分、残業（深夜）60分

①27,500円　　　②27,750円　　　③28,550円　　　④28,750円
給与計算実務能力検定　サンプル問題より

● 残業代とは

そもそも残業代とはなんでしょうか？ 労働基準法では１日８時間を超えた労働時間に対し、通常支払う賃金に一定の割増率で加算した賃金を支払わなければなりません。普段「残業代」と呼んでいるものはこの割増賃金のことを指します。

１日８時間を超えた労働時間

１日８時間を超えた労働時間については、通常支払われる賃金に25％以上の割増賃金が加算されます（労働基準法第三十七条第一項）。また、午後10時から午前５時までの労働時間については、さらに25％以上の割増賃金が加算されます（労働基準法第三十七条第四項）。

法定休日の労働時間

法定休日とは毎週少くとも一回は与えられる休日のことです（労働基準法第三十五条第一項）。日曜日が法定休日の場合が多いですがそうではない場合もあります。法定休日の労働時間は通常支払われる賃金に35％以上の割増賃金が加算されます。

● Excel で勤怠表を作成

まずは、サンプル問題の勤怠状況をもとに Excel で勤怠表を作成してみます。

勤怠状況から、出社時刻が９時、退社時刻は18時であることがわかります。

● １日８時間を超えた労働時間を計算

では、１日８時間を超えた労働時間を計算してみます。労働時間が８時間を超えていたら退社時刻から出社時刻と休憩時間を引いた時間を算出する数式です。
例として、9月11日（E列12行目のセル）に数式を入力しています。

 =IF(D12-B12-C12>1/3,D12-B12-C12-1/3,"")) 

「1/3」とは１日の時間計算のうち８時間をあらわしています（24時間×1/3＝８時間） 。

休みの日は残業時間の計算が不要です。出社時刻が空白であれば計算しません。

 =IF(ISBLANK(B12),"",IF(D12-B12-C12>1/3,D12-B12-C12-1/3,"")) 

ISBLANK 関数で出社時刻が空白かどうかを判断しています。

土日も計算しないように数式を追加します。

 =IF(OR(WEEKDAY(A12,2)>=6,ISBLANK(B12)),"",IF(D12-B12-C12>1/3,D12-B12-C12-1/3,"")) 

WEEKDAY 関数で土日かどうか、もしくは ISBLANK 関数で出社時刻が空白かどうかを判断しています。

この数式をE列に反映させることで１日８時間を超えた労働時間が計算できます。

● 深夜残業を計算

午後10時から午前５時までの労働時間については、さらに25％以上の割増賃金が加算されます。退社時刻が午後10時を超えた分の労働時間を計算してみます。
例として、9月25日（F列26行目のセル）に数式を入力しています。

 =IF(D26>TIME(22,0,0),D26-TIME(22,0,0),"") 

TIME 関数で退社時刻が午後10時を超えているかどうかを判断しています。

この数式をF列に反映させることで深夜残業が計算できます。

● 法定休日の労働時間を計算

法定休日の労働時間は通常支払われる賃金に35％以上の割増賃金が加算されます。サンプル問題での法定休日である日曜日の労働時間を計算してみます。
例として、9月22日（G列23行目のセル）に数式を入力しています。

 =IF(AND(WEEKDAY(A23)=1, B23&C23&D23<>""),D23-B23-C23,"") 

WEEKDAY 関数で日曜日であるかどうかを判断し、かつ出社時刻／休憩時間／退社時刻が空白ではないことを確認しています。

この数式をG列に反映させることで法定休日である日曜日の労働時間が計算できます。

● 合計時間を計算

残業時間、深夜残業時間、法定休日労働時間がそれぞれ算出されました。各列を月ごとにまとめて計算してみます。
例として、残業時間の合計（E列32行目のセル）に数式を入力してみます。

 =SUM(E2:E31) 

ここで気をつけないといけないのは、時間を合計した際に24時間以上になる場合です。セルの書式設定→ユーザー設定で [h]:mm と設定します。h は時間をあらわしますがそのままでは24時が０時となってしまいます。累計時間をあらわすために [h] と表記します。

数式とセルの書式設定を残業時間、深夜残業時間、法定休日労働時間それぞれの合計セルに反映させることで合計時間が計算できます。

● 割増賃金の計算

残業時間、深夜残業時間、法定休日労働時間はそれぞれ、月の合計のうち1時間未満の時間（分）が法律により端数処理を認められています。

30分以上→切り上げ
30分未満→切り下げ

例として、残業時間の合計（E列32行目のセル）に対し端数処理をしてみます。まずは分を60で割って四捨五入します。

 =ROUND(MINUTE(E32)/60,0) 

MINUTE 関数で分を取得し60で割った数値を ROUND 関数で四捨五入しています。

時間は合計時間をそのまま取得します。

 =HOUR(E32) 

HOUR 関数で時間を取得できますが、このままでは24時間が０にリセットされてしまうので累計時間を計算する工夫が必要です。

 =DAY(E32)*24+HOUR(E32) 

DAY 関数で日数を算出し24を掛けて時間を足す処理をしています。

この端数処理を行った時間外労働時間の計算と、時間給と割増率を掛ける数式を一度で行えるように書き換えるとこうなります。

 =(DAY(E32)*24+HOUR(E32)+ROUND(MINUTE(E32)/60,0))*$D35*E34 

残業時間、深夜残業時間、法定休日労働時間のそれぞれにこの数式を反映させることで割増賃金が計算できます。

あとはこの3つを足し算すると…

残業代（割増賃金）が算出されます！

よって、サンプル問題の正解は③ということになります。

● 月給制の場合の割増賃金の基礎になる賃金

サンプル問題は時給制でしたが、月給制の場合では1時間あたりの賃金はいくらとなるのでしょうか？ 月給制での割増賃金の基礎になる賃金計算方法は、

月給÷1年間における1か月平均所定労働時間

となります。ここでいう月給には家族手当／扶養手当／通勤手当／住宅手当などは含まれません（一律支給の場合を除く）。

例えば、年間所定休日122日、１日の所定労働時間が８時間、月給の内訳が基本給215,000円、役職手当8,000円、住宅手当20,000（一律全員に支給）、通勤手当15,000円の場合で Excel で計算してみます。
例として、列Bにそれぞれ値と数式を入れてます。

1年間における1か月平均所定労働時間は、365（日）から年間所定休日を引いた日数に1日の労働時間を掛け、12（か月）で割ります。

 =(365-B1)*B2/12 

月給には基本給、役職手当が含まれます。また、住宅手当も一律支給なので対象に含みます。通勤手当は含まれません。

月給を1年間における1か月平均所定労働時間で割ると、月給制の場合での割増賃金の基礎になる賃金が算出されます。

● 時間外労働の計算方法は人によって変わります

今回の記事で取り上げた Excel の例はあくまでサンプル問題用であり、実際は１日の所定労働時間が８時間ではない場合や、変形労働時間制を採用している会社の場合、また会社規模によって時間外労働の計算方法はさまざまです。さらに管理職につきましては時間外手当や休日手当の支払いの義務はありません。月合計１時間未満の端数処理も切り下げ／切り上げはせず、Excel や給与計算ソフトを使用すれば１分単位の計算もたやすいものです。というわけで計算方法は会社や人によって変わってくるということをご了承ください。