Excelで勤怠ログから直前の退室時間と休憩開始時間の差を抽出する方法｜関数で自動計算する手順

Excelで入退室や休憩などの記録を管理している場合、膨大なログの中から「休憩開始直前の退室時間」「休憩開始までに何分かかったか」といった特定の条件だけを抽出したいケースがあります。特に人数が多く、同じ人の記録が連続して入力されている場合、手作業で確認するのは非常に大変です。

この記事では、Excel 2016でも利用できる関数を使い、勤怠・入退室データから条件に合った時間差を自動計算する考え方と具体的な方法を解説します。

勤怠ログから特定の時間差を求める基本的な考え方

今回のようなデータでは、単純に「休憩入」の行だけを見るのではなく、その直前にある同じ人物の「退室」データを探す必要があります。

例えば以下のような流れの場合です。

ID	項目	時間
202	退室	8:00
202	休憩入	8:15

この場合は「休憩入の時間」から「直前の退室時間」を引くことで、15分という結果を求められます。

ポイントは、条件として「同じID」「同じ人物」「項目が退室」「休憩入の直前」という4つの条件を考えることです。

Excel 2016では、最新のXLOOKUP関数は使用できないため、INDEX関数とMATCH関数を組み合わせる方法が便利です。

例えば、以下のようにデータが入力されているとします。

「休憩入」の行で、その直前にある同じIDの「退室」時間を取得する場合、補助列を作成して条件を整理すると管理しやすくなります。

Excelでは複雑な条件を一つの数式だけで処理するより、補助列を使うことで分かりやすく安定した管理ができます。

例えばE列に「前行の項目」を表示します。E2セルに以下のような式を入力します。

=C1

これを下方向へコピーすると、現在の行から見て1つ前の項目を確認できます。

さらに、F列で「現在が休憩入で、前の行が退室なら時間差を計算する」という条件を作ります。

=IF(AND(C2=”休憩入”,C1=”退室”,A2=A1),D2-D1,””)

この式では、同じIDの人について、退室の直後に休憩入が記録されている場合だけ時間差を表示します。

Excelの時間は内部的には1日を1として管理しています。そのため、時刻同士を引くと結果は時間形式になります。

例えば、8時00分から8時15分までの場合、結果は0:15になります。これを「15分」と表示したい場合は、計算結果に1440を掛けます。

例。

=(D2-D1)*1440

この結果は分単位の数値になります。

例えば0:15の場合、0.010416という値になりますが、1440を掛けることで15分として表示できます。

社員や利用者が何人もいる場合、計算結果を作成した後にフィルター機能を利用すると確認が簡単になります。

ID番号ごとに並べ替えることで、同じ人物の行がまとまり、休憩までの時間や入退室状況を確認しやすくなります。

また、集計目的であればピボットテーブルを利用して、人物ごとの平均休憩移行時間や回数を分析することもできます。

このような勤怠ログ分析では、入力ルールを統一することが重要です。「休憩」「休憩入」「休憩開始」など複数の表記が混在すると、Excelの条件検索では正しく判定できません。

例えば項目名は必ず「出勤」「退勤」「入室」「退室」「休憩入」「休憩出」のように固定すると、後から関数や集計機能を利用しやすくなります。

また、同じ人物の記録はID番号で管理し、名前の表記揺れを避けることで、大人数のデータでも正確に処理できます。

Excel 2016で入退室ログから「休憩入の直前の退室時間」や「何分かかったか」を抽出する場合、現在の行と前の行を比較する考え方が基本になります。

補助列を使って前行の項目を確認し、IF関数やAND関数で条件を指定すると、人数が多いデータでも自動計算できます。

勤怠データは入力ルールを統一することで、Excelだけでも複雑な入退室管理や時間分析が可能になります。