【勤務表Excel】勤務時間とか残業時間、自動計算してほしくない？【バッチリExcel相談室 #006】

こんにちは。バッチリです。

うだるような暑さが続く5月の下旬、いかがお過ごしですか？

4月に入社された方、新入社員の方々は入社研修などを終えられ、OJT（お前が・自分で・トレーニング）しているところでしょうか？

五月病に浮かされてませんか？
今年、令和元年は7月15日の海の日まで祝日がありませんが大丈夫ですか？

そんな陰気な時期に畳みかけるように無意味な作業ってありますよね。

勤務表、昔はExcelで管理してるようなところもあって、すごく面倒な作業でしたね。月締めが近くなると勤務表を仕上げるだけで30分～1時間経っちゃうことも・・・今はポータルでポチポチしてるんで楽になりましたけどね。

まさか、まだ、勤務表をExcelで管理してる会社なんて、ありませんよね？
え！？あなたの会社、そのまさか！？ｗｗｗ

（失礼しました・・・僕も2018年まではそうでした・・・）

勤務表作るの、面倒臭いんだよ！って言いたいですよね。
でも新入社員の皆さん、怖い上司にそんなこと言えませんよね。

そんなときは、これ！勤務表自動計算ツール～！

これを使えば、出勤時刻と退勤時刻を入力するだけで、勤務時間と残業時間を計算してくれるんだ！

Excel関数で作ってみたので、画像とともに解説していきます。
みなさんも是非作ってみてください！

では、早速解説していきます。

１．準備

いきなり関数を書いていくわけにはいきません。準備します。

「設定」シートを作っておいてください。

１－１．祝祭日

祝祭日をまとめて書いておきます。
2019年～2022年あたりを書いておけばしばらく大丈夫でしょう。

なお、画像のように日付を選択して名前を「祝祭日」に設定などしておくとのちのち便利なのでしておいてください。

１－２．勤務時間帯

勤務時間帯を書いておきます。

午前、午後、夜～ぐらいでしょうか。

最後の夜２の終了時刻のように「99:00」と入力しておけば取りこぼしは無いでしょう。

※注意※　休憩時間ではありません！勤務時間です！

１－３．カレンダー表をひと月分

画像のように、日付、出勤時刻、退勤時刻、勤務時間、残業時間、備考
の欄を作ってください。

日付はその月の初めから終わりまで。

画像ではセルA3以降に「=A2+1」と数式を入れてます。毎月使うのでこの方が便利かなと。

２．関数

画像のように、作業列を作ってください。

休日か、勤務時間（午前）、（午後）、（夜１）、（夜２）、（夜３）
こちらは、先ほど作った勤務時間帯表の行と対応しています。

なぜ作業列を作るのかというと、数式が長ったらしいからです。

２－１．祝日かどうか

祝日かどうかは、勤務時間計算には関係ないので、お好みで。

数式は以下の通り

=NETWORKDAYS(勤務表!$A2, 勤務表!$A2, 祝祭日)=0

NETWORKDAYS(開始日, 終了日, [祝祭日])
開始日から終了日までの[祝祭日を除いた]平日の日数を出力する

という関数を使っています。
画像では、4/1から4/1までの祝祭日を除いた日数が0ならば、TRUE、そうでなければFALSEを出力しています。
4/1は祝祭日ではないので、4/1～4/1の平日は1日、よって0ではないため「FALSE」と表示されています。
また、4/6は平日ではないので、4/6～4/6の平日は0日、よって「TRUE」と表示されています。

条件付き書式で、A:F列を選び、数式に「=$H2」と入力すれば、画像のように土日祝祭日がオレンジ色になります。

２－２．各時間帯における勤務時間

数式は以下の通り。

=IF(AND(勤務表!$B2<>"", 勤務表!$C2<>""
　　　　　,勤務表!$B2<INDIRECT("設定!$F$"&COLUMN()-COLUMN($H$1)+1)
　　　　　,INDIRECT("設定!$E$"&COLUMN()-COLUMN($H$1)+1)<勤務表!$C2)
　,MIN(勤務表!$C2,INDIRECT("設定!$F$"&COLUMN()-COLUMN($H$1)+1))
　　-MAX(勤務表!$B2,INDIRECT("設定!$E$"&COLUMN()-COLUMN($H$1)+1))
　,0)

これを、セルI2からM32までコピペするだけ。
ね？簡単でしょ？

IF(条件, 真の場合の処理, 偽の場合の処理)
⇒条件がTRUEの場合、真の場合の処理を行う。FALSEの場合は偽の場合の処理を行う。この真/偽、TRUE/FALSEを真偽値なんて言ったりします。

AND(～, ～, ・・・)
⇒～と～と・・・がすべてTRUEの場合、AND(～,～,・・・)もTRUEとなります。論理積と言います。日本語では「AかつB」とか言いますね。数学AだかBで。

勤務表!$B2<>"", 勤務表!$C2<>""
⇒どっちも空欄でないかどうか

勤務表!$B2<INDIRECT("設定!$F$"&COLUMN()-COLUMN($H$1)+1)
⇒出勤時刻が設定シートのセルF2（午前の終了時刻）よりも早いかどうか
　⇒INDIRECTはセル参照を関数を使って指定したいときに使います。
　⇒COLUMN()は自分のセルの列が何列目かを返します。I列は9列目。
　⇒COLUMN($H$1)はセルH1が何列目かを返します。H列は8列目。
　　⇒INDIRECT("設定!$F$"&COLUMN()-COLUMN($H$1)+1)＝設定!$F$2
　　・・・①

INDIRECT("設定!$E$"&COLUMN()-COLUMN($H$1)+1)<勤務表!$C2)
⇒退勤時刻が設定シートのセルE2（午前の終了時刻）よりも遅いかどうか
　　・・・②

①と②により、出勤～退勤が午前の時間帯に重なっているかを表しています。なぜそうなるのかは下の画像をご覧ください（説明省略）。

勤務時間がどんな場合でも、重なるのはこの条件だからです！（ごり押し）

２－３．勤務時間と残業時間

さて、あとは、各時間帯の勤務時間を合計するだけですね。

勤務時間は全部をSUM、残業時間は夜１～３をSUMするだけです。

解説不要ですね？

３．おわり

いかがでしたか？
ご自身でも勤務表を作ることができましたか？

上手くできた人も、できなかった人も、周りの人より関数に触れた回数が多いことを誇ってください。

「これができないよ～」などのご意見、いただけるとすごくうれしいです。

４．ファイルダウンロード

noteをご購入いただいた方は、今回説明した勤務表をダウンロードできます。以下をご理解の上、ご購入ください。

■ご利用に際して
・配布しましたExcelマクロファイルは学習用ですので、商用利用や悪用利用、無断配布、複製はお断りしております
・利用により発生した損害については、当方は一切責任を負いかねます
・デジタルコンテンツの性質上、返金はいたしかねます。コンテンツの内容につき不備がございましたら、ご連絡くだされば更新いたします
・ソフトウエア著作権は、当方に帰属します

※マガジンをご購入いただきますと特典としてExcel相談ができます。
　内容によってはオーダーメイドファイルをnoteで公開いたします。
　相談はTwitter DMまで！ぜひ！

勤務表.xlsx

25.2 KB

ファイルダウンロードについて

ダウンロード

ご購入ありがとうございます！
ファイルに不備等ございましたら教えてくださると助かります。

バッチリ