はじめに
毎日、退勤時間に応じて休憩時間をつい暗算して入力していませんか？
この記事では、ExcelのVLOOKUP関数を使って休憩時間を自動計算する方法をご紹介します。
背景
私が参画していた現場では、勤怠打刻システムとは別に、タイムシートへ勤務実績を転記する必要がありました。しかし、休憩時間は「退勤時刻」や「残業の有無」によって変わるため、毎回暗算で入力しており、ミスが生じる可能性もあり、もう少し効率的に算出できないかと考えExcelのVLOOKUP関数を用いた休憩時間の自動計算を実装しました。
この方法は汎用的に応用できると感じたため、今回記事としてまとめました。
※基本的なVLOOKUP関数の使い方の説明は省いております。
今回のケース
以下のように、あらかじめ休憩時間がシステム上で組み込まれているとします。
- 12:00 ~ 13:00 → 60分の昼休み
- 17:30 ~ 17:45 → 15分の残業前休憩（残業前の休憩が必須で設定されています）
そのため、例えば下記の勤務時間で打刻した場合、休憩時間はこちらのようになります。
- 9:00 ~ 17:30 勤務 → 休憩時間は合計 60分
- 9:00 ~ 17:45 勤務 → 休憩時間は合計 75分
このように勤務時間によって休憩時間が変動するケースでは、まずは「どの退勤時刻に対して、どの休憩時間を設定するか」を整理する必要があります。
そこで最初のステップとして、マスターテーブルを作成し、ルールを一覧化していきます。
なお、今回は半休等のイレギュラーな勤務時間を除き、終日、通常勤務で昼休憩を取得した場合を想定しています。
ステップ１：マスターテーブルの作成
Excelシート１のA3からB18のセルに、退勤時間ごとに対応する休憩時間を一覧化したマスターテーブルを作成します。
これにより、VLOOKUP関数を使って「退勤時刻 → 休憩時間」を自動的に参照できるようになります。
ポイント：
- 退勤時刻は昇順に並べておくと関数が扱いやすい
- 分単位で細かく登録するか、ある程度の区切りで登録するかは運用に応じて調整可能
ステップ２：VLOOKUP関数の記述
次に、シート2のD列にVLOOKUP関数を記述します。
具体的には、D3セルに以下の数式を入力し、そのまま下方向へコピー（フィルハンドルをドラッグ）します。
=IFERROR(VLOOKUP(C3,Sheet1!$A$3:$B$18,2,TRUE),"")
この式では、
- シート2のC3セルに記載した退勤時刻をキーとして検索
- シート1のマスターテーブル（A列〜B列） から休憩時間を参照
- 該当データがなければ空白を返す
という処理をしています。
注意点
今回のケースで特に重要なのは、「時刻データ型の一致」と「エラー処理」 です。
-
エラー処理（IFERROR関数）
VLOOKUPが該当データを見つけられない場合、そのままだと#N/Aが表示されます。
IFERRORを組み合わせることで、エラー時に空白を返すようにでき、シートの見やすさや可視化に役立ちます。 -
時刻データの型
検索値（退勤時刻）と、マスターテーブルの検索列の型をそろえる必要があります。
どちらも「時刻データ（シリアル値）」なのか、「文字列」なのかが一致していないと、#N/Aエラーが発生する原因になります。 -
近似一致（TRUE）の指定
今回は「退勤時刻に最も近い休憩時間」を取得したいので、VLOOKUPの第4引数にTRUE（近似一致）を指定しています。
例えば 17:46以降の退勤時刻は、マスターテーブルにそのものズバリの値がないため、近い値（17:45の行）を参照する必要があります。
もし完全一致で検索する場合はFALSEを指定します。
まとめ
この記事では、VLOOKUP関数とIFERROR関数を組み合わせることで、退勤時間に応じた休憩時間の自動算出を実現しました。他にも、シフト管理や工数管理などにも応用可能だと考えていますので、ぜひご自身の業務に導入して、効率化を図るきっかけになれば幸いです。