【Googleスプレッドシート】関数だけで勤怠管理表を作る方法｜無料で月末集計を自動化

導入

勤怠管理、Excelでやってるんですけど、毎月の集計に2〜3時間かかってて…。関数を入れても、誰かがセルを壊したり、コピペミスしたりして、結局チェックに時間がかかってるんです😭

フォックン

その悩み、Googleスプレッドシートなら「関数だけで仕組みを作る」「共有しても壊れにくくする」が両立できます。無料だし、クラウドでどこからでも打刻確認できるので、在宅勤務にも強いですよ 🦉

オウル先生

この記事で得られること

• ✅ スプレッドシートで勤怠管理表を関数だけで作る手順
• ✅ 勤務時間・残業時間を自動計算する数式
• ✅ 月次集計を自動で出す方法
• ✅ 運用しやすいシート構成のコツ

スプレッドシートで勤怠管理するメリット

目次

1. 勤怠管理表のシート構成
2. 日付と曜日を自動で入れる
3. 勤務時間を関数で計算する
4. 休憩時間を差し引く
5. 残業時間を自動で出す
6. 月次集計を1行でまとめる
7. 入力しやすくする工夫
8. よくあるトラブルと対処
9. まとめ

勤怠管理表のシート構成

1枚目：日別の打刻シート（例：「4月」）

• 日付：手入力でもよいが、後述の関数で自動化すると楽
• 出勤・退勤：時刻（9:00 など）で入力。スプレッドシートは「9:00」と入れると時刻として認識する
• 休憩(分)：60 のように「分」だけ数字で
• 勤務時間・残業：すべて関数で計算

まずは「出勤・退勤・休憩」だけ手で入れて、あとは全部関数に任せる形にすると、ミスが減ります 🦉

オウル先生

2枚目：月次集計シート（例：「集計」）

月ごとのシート（4月・5月…）から、合計だけを参照して表示するシートにすると、経営層への報告が楽になります。

日付と曜日を自動で入れる

日付を連続で入れる

月初日（例：4月1日）を A2 に入力し、その下に次の式を入れます。

= A2 + 1

A2 を「4/1」、A3 に =A2+1 を入れて下にコピーすると、4/2、4/3…と続きます。

曜日を自動表示する

B2 に次を入れ、下にコピーします。

= TEXT(A2, "aaa")

「月」「火」…と表示されます。英語にしたい場合は "ddd"（Mon, Tue…）に変更できます。

土日を色分けしたい場合は、B列の曜日で「条件付き書式」を使うと、土曜を青、日曜を赤にしたりできますね！

フォックン

その通りです。条件付き書式で「セルの書式設定の条件」→「次の文字列を含む」で「土」「日」を指定すれば、見やすくなります 🦉

オウル先生

勤務時間を関数で計算する

基本の考え方

• 勤務時間 = 退勤 − 出勤
• スプレッドシートでは時刻は「小数」で扱われるため、引き算すると「時間」が求められます。
• 例：退勤 18:00、出勤 9:00 → 18:00−9:00 = 9時間

勤務時間（休憩なし）を出す

F2 に次の式を入れ、下にコピーします。

= IF(AND(C2<>"", D2<>""), D2 - C2, "")

• 出勤（C2）と退勤（D2）の両方が入力されているときだけ計算
• どちらか空欄なら空白にし、エラーを防ぎます

表示を「○時間○分」にする

計算結果はデフォルトだと「9:00:00」のように表示されます。「9時間0分」のように見せたい場合は、セルの書式設定で次のようにします。

1. F列を選択 → 表示形式 → 数値 → ユーザー定義
2. 次のいずれかを入力：
   • [h]"時間"m"分" （例：9時間0分）
   • または [h]:mm （例：9:00 のまま、24時間超も表示可能）

24時間を超える月次合計を出す場合は、書式は [h]:mm や [h]"時間"m"分" にしておくと正確です 🦉

オウル先生

休憩時間を差し引く

休憩（分）を「時間」に換算して差し引く

E列に「60」（分）のように入っている場合、勤務時間は次のようにします。

= IF(AND(C2<>"", D2<>""), D2 - C2 - (E2/60/24), "")

• E2/60/24 で「分」を「日」単位の小数に変換（スプレッドシートの時刻は「1日=1」）
• 退勤−出勤から休憩を引いた値が、実質の勤務時間になります

休憩が空欄のときは 0 として扱いたい場合は、次のようにします。

= IF(AND(C2<>"", D2<>""), D2 - C2 - (IF(E2="", 0, E2)/60/24), "")

残業時間を自動で出す

所定労働時間（例：8時間）を超えた分だけ残業とする

G2 に次の式を入れ、下にコピーします。

= IF(F2="", "", MAX(0, F2 - (8/24)))

• F2 が空白なら G2 も空白
• 勤務時間（F2）から 8時間（8/24）を引き、マイナスなら 0 にする
• 所定が 7時間なら (7/24)、7.5時間なら (7.5/24) に変更即可

残業の表示形式

G列も 表示形式 で [h]"時間"m"分" または [h]:mm にすると、読みやすくなります。

深夜勤務で日付をまたぐ場合は、この式のままで大丈夫ですか？

フォックン

はい。スプレッドシートの時刻は「日付+時刻」で扱われるので、退勤が翌日 2:00 なら「26:00」のように計算されます。表示形式に [h]:mm を使えば、25時間30分なども正しく表示されます 🦉

オウル先生

月次集計を1行でまとめる

当月の「勤務時間合計」を出す

日別シート（例：「4月」）の F 列が勤務時間だとします。同じスプレッドシート内の「集計」シートで、4月の総勤務時間を出すには次のようにします。

= SUMIF('4月'!A:A, ">=2024/4/1", '4月'!F:F) - SUMIF('4月'!A:A, ">2024/4/30", '4月'!F:F)

日付範囲で SUM したい場合は、次の方が分かりやすいです。

= SUM(FILTER('4月'!F:F, ('4月'!A:A>=DATE(2024,4,1)) * ('4月'!A:A<=DATE(2024,4,30))))

• DATE(年, 月, 日) で日付を指定
• 月だけ変えれば、5月・6月…も同じパターンで集計できます

残業時間の月次合計

残業が G 列なら、上記の F を G に変えた式を「集計」シートに用意します。

月ごとにシートを分けておき、「集計」シートで各月を参照する形にすると、1年分を一つのファイルで管理できます 🦉

オウル先生

入力しやすくする工夫

出勤・退勤を「ドロップダウン」にしない場合

時刻はそのまま「9:00」「18:30」と入力すれば認識されます。桁を揃えたい場合は、データの入力規則は使わず、表示形式で「時刻」を選んでおくと、入力が楽になります。

休憩時間をリストで選ぶ

休憩（分）をよく使う値だけ選べるようにするには、E列で データ → データの入力規則 を設定します。

• 条件：リストを直接指定
• 値：45, 60, 90 のようにカンマ区切りで入力

これでプルダウンから 45分・60分・90分 を選べます。

列の保護（編集できる人を制限する）

• 日付・曜日・勤務時間・残業は計算列なので、編集不可にすると安全です。
• データ → シートと範囲を保護 で、該当範囲を「編集者を除く」にし、自分だけ編集可能にすることができます。
• 出勤・退勤・休憩だけ各担当者が編集し、それ以外は触れないようにする運用がおすすめです。

よくあるトラブルと対処

勤務時間が「#####」や変な値になる

• セル幅が狭いと ##### と表示されます。列幅を広げるか、表示形式を短いものに変更してください。
• 退勤より出勤が大きい（例：出勤 18:00・退勤 9:00）と負の値になります。翌日退勤の場合は日付を含めた日時（例：翌日 9:00）で入力するか、数式で 24 時間を足す処理を検討してください。

合計が 0:00 や 24:00 で止まる

• 合計が 24 時間を超えると、書式が「h:mm」のままだと 0:00 に戻って見えます。表示形式を [h]:mm または [h]"時間"m"分" にすると、25時間、30時間も正しく表示されます。

誰かが数式を消してしまう

• シートと範囲の保護で、数式が入っている列（日付・曜日・勤務時間・残業）を「編集不可」にし、出勤・退勤・休憩だけ編集可能にすると防げます。
• 併せて、ChatGPTで勤怠チェックを自動化する方法 のように、AIでチェック用のプロンプトを用意しておくと、集計前の確認が楽になります。

まとめ

スプレッドシートだけで、ここまでできるんですね。無料だし、共有も簡単で助かります！

フォックン

はい。関数だけで勤怠管理表を作っておけば、毎月の集計時間を大きく削減できます。まずは「日付・出勤・退勤・休憩」の4項目を入力し、勤務時間と残業を関数で出す形から始めてみてください。慣れてきたら月次集計シートを追加し、必要に応じて条件付き書式や保護で運用しやすくしていくとよいですよ 🦉

オウル先生

この記事のポイント

オウル先生&フォックン

ブログライター

オウル先生 フォックンが運営する未経験からのプログラミング上達ガイド！ プログラミング学習に興味があるけど、 「どのスクールを選べばいいか分からない…」 「自分に合った学習方法が知りたい…」 「本当にエンジニアになれるか不安…」 そんな悩みをお持ちのあなたへ。 オウル先生とフォックンが、プログラミングスクール選びから学習方法、キャリア形成まで、丁寧にサポートします！ 豊富な情報と分かりやすい解説で、あなたのプログラミング学習を成功へと導きます。