勤怠管理は、従業員の労働時間、残業、有給休暇などを適切に管理することで、健全な労働環境を維持し、法令遵守を確保するために欠かせないプロセスです。
本ブログでは、EXCEL女子が作成した勤怠管理表の使い方ついて、入力欄から自動計算の仕組み、さらには実際の活用例に至るまで、ステップバイステップで解説します。
ぜひ、従業員一人ひとりの勤務状況を正確に把握し、労働時間の適正管理を行っていきましょう。
▼まずは、下記よりダウンロードください
勤怠管理表は、従業員の勤務時間や休暇、欠勤、遅刻などの勤務記録を記録するものです。
通常、企業や組織内の人事部門や管理者が使用します。
勤怠管理表には、
などが記録されます。
勤怠管理表の目的は、従業員の勤務状況を正確に記録し、
などに役立てることです。また、従業員が自身の勤務記録を確認し、必要に応じて修正を行うこともあります。
近年では、勤怠管理表を電子化してオンラインの勤怠管理システムやアプリケーションを利用する企業も増えています。これにより、データの管理や集計が効率化され、管理者や従業員がより便利に勤務記録を管理できるようになりました。
こちらでは、日々の勤務記録をどのように入力するか、その手順とポイントを解説します。
下図の水色の部分が入力欄です。
書式設定が設定されているので、数値で入力します。
入力例
「2023」「8」
24時間形式で開始と終了時刻の定刻を入力します。
入力例
「9:00」「18:00」
勤務の状況を入力します。プルダウンから選択可能です。
プルダウン項目
・有給休暇
・午前有給
・午後有給
・遅刻
・早退
・フレックス
24時間形式で実際の勤務時間を入力します。休憩が1時間の場合「1:00」と入力します。
入力例
「9:00」「18:00」「0:30」
当社の勤怠管理テンプレートは、あらかじめ入力したデータを基に自動で計算を行う便利な欄が設けられています。
これらの自動計算欄は、
など、従業員の勤務状況に関する重要な指標を計算します。以下に、主な自動計算欄とその機能を解説します。
これにより、日々の勤務記録から月単位での稼働統計まで、管理者と従業員双方の時間を節約し、効率的な勤怠管理を実現します。
計算式：=SEQUENCE(DAY(EOMONTH(DATE(F3,G3,1),0)),1,DATE(F3,G3,1))
B列の日付を自動表示するために使用している関数は以下となります。
「開始値」から「目盛り」分ずつ増える「行」×「列」の数列を配列として返す
=SEQUENCE(行,[列],[開始],[目盛り])
行：返される行数を指定
列：返される列数を指定し、省略すると「1」となる
開始：数列の最初の数値を指定
目盛り：数列の後続の各あて位の増分値を指定
SEQUENCE関数について、詳しくはコチラもご覧ください。
開始日から起算して、指定された月数だけ前または後の月の最終日に対応するシリアル値を返す
=EOMONTH(開始日, 月)
開始日：起算日を表す日付を指定
月：開始日から起算した月数を指定。
"月" に正の数を指定すると起算日より後の日付を返し、負の数を指定すると起算日より前の日付を返す
3 つの独立した値を受け取り、それらを組み合わせて日付を作成する
=DATE(年, 月,日)
年：年を指定
月：月を指定
日：日を指定
シリアル番号で表された、日付の日情報を返す
=DAY(シリアル値)
シリアル値：日の情報を返したいシリアル値を指定
この数式は、特定の年月（【F3】セルの年、【G3】セルに月）を基に該当月の日付を自動的に生成するために使用されます。関数の動作を詳しく見てみましょう。
DATE(F3, G3, 1)
【F3】セルの年と【G3】セルの月から、その月の初日の日付を生成します。
EOMONTH(DATE(F3,G3,1),0)
【F3】セルと【G3】セルから生成された日付を基にして、その月の最終日の日付を生成しているため、月によって最終日が違う場合も対応可能です。
DAY(...)
EOMONTH関数から得られた月の最終日の日付から、月の日数を抽出します。
SEQUENCE(...)
最終的に第1引数に月の日数（DAY(EOMONTH(DATE(F3,G3,1),0)))、第2引数に列（この場合は1列）、第3引数に開始値（DATE(F3, G3, 1))を指定して、その月の全日付を一列に自動生成します。
生成されるのはシリアル値ですが、ユーザー定義の表示形式の機能を使用して「日付」の表示としています。
ユーザー定義の表示形式について、詳しくはコチラもご覧ください。
この関数によって、指定された月の1日から月末までの日付が一列にスピルされて返るため、勤怠管理表などで日付の入力作業を省力化できます。
スピルについて、詳しくはコチラもご覧ください。
計算式：=IF(B9="","",TEXT(B9,"aaa"))
表示形式コードを使用して数値に書式設定を適用することで、数値の表示方法を変更する。
=TEXT(値,表示形式)
値：テキストに変換する数値
表示形式：指定された値に適用する書式を定義するテキスト文字列
【B9】セルが空白（""）かどうかを確認します。【B9】が空白の場合、結果も空白（""）となります。空白でない場合、次の式が返ります。
【B9】セルの内容ですが、TEXT関数を使ってフォーマット変換します。
ここで、"aaa"のフォーマットは、セルの日付を曜日の省略形（例: "月", "火", "水" など）に変換します。
この式を使うことで、【B9】セルに日付が入力されている場合に限り、その日付を対応する曜日の省略形に変換し、【B9】が空の場合は何も表示せずに空白を保持します。これにより、勤怠管理表などで日付に基づいた曜日を簡単に表示させることができます。
計算式：=IF(B9="","",IF(OR(C9="土",C9="日"),"休業日","営業日"))
【B9】セルが空白かどうかを確認します。もし【B9】が空白（""）なら、結果も空白（""）を返します。これは日付が入力されていない場合に、何も表示しないようにするためです。【B9】に日付が入力されているなら、次の条件式に進みます。
【C9】セルに記載されている曜日が土曜日または日曜日であるかを確認します。
もし【C9】が「土」または「日」であれば「休業日」と表示します。それ以外の曜日であれば「営業日」と表示します。
この式を用いることで、勤怠管理表などで日付と曜日に応じて自動的にその日が営業日か休業日かを表示させることができます。
計算式：=IF(AND(F9="",G9=""),"",G9-F9-H9)
【F9】セル（出勤時間）と【G9】セル（退勤時間）が両方とも空白かどうかを確認します。
両方とも空白であれば、TRUEを返します。
第一引数（ここではAND関数の結果）がTRUEの場合、つまり出勤時間と退勤時間の両方が未入力の場合は、空白（""）を返します。それ以外の場合、つまり出勤時間と退勤時間のいずれかが入力されている場合は、退勤時間(G9)から出勤時間(F9)を引き、更に休憩時間(H9)を差し引いた結果（実質的な稼働時間）を計算します。
この式により、出勤時間と退勤時間のどちらか、または両方が未入力の場合は稼働時間を計算せず、両方入力されている場合のみ稼働時間を自動計算することができます。
これにより、勤怠データの入力が不完全な場合の誤計算を防ぎます。
計算式：=IFERROR(IF(I9-TIME(8,0,0)>0,I9-TIME(8,0,0),""),"")
数式がエラーと評価される場合に指定した値を返します。
=IFERROR(値, エラーの場合の値)
値：エラーかどうかをチェックする引数
エラーの場合の値：数式がエラー値と評価された場合に返す値を指定
指定した時刻に対応する小数（シリアル値）を返します。
=TIME(時, 分, 秒)
時：時を指定
分：分を指定
秒：秒を指定
【I9】セル（稼働時間）から8時間（標準労働時間）を引きます。ここで、TIME(8,0,0)は8時間0分0秒を表すので、シリアル値同士の計算となります。
実働時間から8時間を引いた値が0より大きい場合、つまり残業が発生している場合は、その残業時間（I9からTIME(8,0,0)を引いた値）を返します。残業が発生していない場合は、空白（""）を返します。
最外側のIFERROR関数は、式の計算中にエラー（例えば、I9が空白の場合にI9-TIME(8,0,0)が計算できないなど）が発生した場合に、エラーの代わりに空白（""）を返します。
この式を用いることで、残業時間を条件に応じて適切に計算し、計算不可能な状況をエラー表示させずにスマートに処理することが可能です。
計算式：=IFERROR(IF(G9<=TIME(22,0,0),"",(G9-TIME(22,0,0))),"")
この部分は、G9セル（通常、退勤時間を示す）が22時00分以前であるかどうかを確認します。
22時以前であれば、空白（""）を返します。
22時以降である場合は、G9（終了時刻）から22時00分（TIME(22,0,0)）を引いた結果を計算します。この結果は、22時以降の勤務時間、つまり深夜残業時間を示します。
最外側のIFERROR関数は、内側の式がエラー（例えば、【G9】セルが空白などで時間の計算ができない場合）を返した際に、代わりに空白（""）を返します。これにより、計算が不可能な状況でもシートがきれいに保たれ、ユーザーにエラーメッセージが表示されることが避けられます。
この式を使用することで、勤怠管理表で深夜残業時間を条件付きで計算し、可能な場合のみ残業時間を表示させることができます。また、エラーを適切に処理し、ユーザーが使いやすい表計算シートになります。
計算式：=IF(OR(E9="午前有給",E9="午後有給"),0.5,IF(E9="有給休暇",1,0))
【E9】セルが「午前有給」または「午後有給」のいずれかであるかを判断します。どちらかが当てはまる場合、TRUEを返します。
上記の条件がTRUEの場合、つまり午前または午後だけ有給休暇を取得している場合は、0.5日の休暇として計算します。これは半日有給を意味します。
もしE9セルが「有給休暇」である場合は1日分の休暇として1を返します。それ以外の場合（つまり休暇を取っていない、または他の条件の休暇である場合）は0を返します。
この式により、勤怠管理表での休暇取得状況を簡単に数値化し、休暇日数を自動で計算することができます。
半日有給休暇の場合は0.5、1日の有給休暇の場合は1として、休暇日数の管理が行えるようになります。
【I39】セル
各日の稼働時間
の２つについてSUM関数を使用して算出しています。
【J39】セル
指定期間内で発生した総残業時間が計算され表示されます。
※残業時間は、1日の標準労働時間を超えた時間の合計をSUM関数を使用して算出しています。
【K39】セル
22時以降の深夜残業時間の合計を示します。
深夜時間帯に働いた時間の合計についてSUM関数を使用して算出しています。
【J6】セル
有給休暇の取得日数が合計され、月内での総休暇日数を示します。
休暇の合計についてSUM関数を使用して算出しています。
【K6:L6】
遅刻と早退の日数についてCOUNTIFS関数を使用して計算しています。
E列の
の回数をそれぞれ数えています。
条件付き書式を使用して、営業日が休業日の場合は、該当行がグレーになるよう設定しています。
シートに「名前付き範囲」を設定しておくことで、新しい月のデータを作成する際は、名前ボックスから該当範囲を簡単に選択ができるため、入力欄を簡単にクリアすることができます。
Excelでは、日付や時刻は「シリアル値」という形式で内部的に管理されています。
シリアル値とは、特定の基準日からの経過日数を表す数値のことで、日付を数値で表現することにより、日付間の計算を可能にします。
Excelの基準日は、1900年1月0日がシリアル値1となっており、例えば1900年1月1日はシリアル値2となります。このシステムにより、日付を加算や減算する計算や、2つの日付間の差を求める計算が簡単に行えます。
時刻に関しても同様に、1日を1として、その日の一部を小数点以下で表現します。
例えば、正午（12:00 PM）は0.5（半日）として表されます。
|
日時 |
シリアル値 |
|
12:00 |
0.5 |
|
24:00 |
1 |
|
1900年1月1日 |
1 |
|
1901年1月1日 |
367 |
|
2022年1月1日 |
44562 |
このシリアル値を理解することで、
がより明確になり、効率的なデータ管理や分析が可能になります。
シリアル値はユーザーには見えない形で活動していますが、Excelで日付や時刻を扱う上で非常に重要な役割を果たしています。
通常の数値は24で割ればシリアル値と同じになります。シリアル値に24をかけると通常の数値になります。
Excelはビジネスシーンにおいて欠かせないツールの一つですが、その全機能を習得することは容易ではありません。今回ご紹介した関数や機能など、Excelにはさまざまな便利な関数や機能があります。
これらの機能を効率的に使用することで、日々の業務をよりスムーズに、そして正確に進めることが可能になります。
しかし、自己学習のみでは発見しづらいテクニックや応用方法も多く存在します。そこで、更なるスキルアップを目指す方には、専門的なExcel研修の受講をお勧めします。
当社では、『Microsoft認定トレーナー』資格を持つEXCEL女子による「Excel研修」を提供しております。書籍化するなど注目いただいている研修です。
社員教育に時間を割けない、Excelスキルの底上げをしたいなどお悩みでしたら、ぜひお気軽にお問い合わせください。
また、勤怠テンプレートをうまく活用するためのサポートが必要な企業様にはEXCEL女子によるサポートサービスもございます。お気軽にお問い合わせください。