エクセルでシフトの時間重複を調べるには、同じスタッフIDの全勤務について、開始日時と終了日時の両方を比較します。既存の勤務一覧に「入力確認」と「重複判定」の2列を追加すれば、日またぎや別店舗への二重配置も確認できます。
氏名が複数回出てくるだけでは、時間が重なっているとは限りません。ここでは、コピーできる2つの数式、6行の架空例、取消・変更後の確認手順を紹介します。行うのは重複の自動表示であり、勤務の自動割当や自動削除、労働時間の集計ではありません。
時間重複は「同じ人の二つの区間」で判定する
判定する条件は、次の3つです。
- スタッフIDが同じ
- 相手の開始日時が、自分の終了日時より前
- 相手の終了日時が、自分の開始日時より後
日時の条件を式で表すと、相手の開始＜自分の終了、かつ相手の終了＞自分の開始です。この両方を満たす勤務を探します。
たとえば、同じ人の9:00〜18:00と12:00〜13:00は、一方が他方に含まれる「包含」の重複です。相手の開始だけが自分の区間内にあるかを調べる方法では、短い勤務の側から長い勤務を見落とすことがあります。開始・終了を組み合わせて判定しましょう。
12時終了と12時開始は重複にしない
今回の判定では、9:00〜12:00と12:00〜15:00は連続した勤務として扱い、重複にしません。条件に<=や>=ではなく、<と>を使うためです。
ただし、これは時間帯が重ならないという意味にとどまります。12時に東店を出て12時に西店へ入れるかといった、移動時間を含む勤務可否は別に確認してください。
時刻を持たない日別表の氏名重複を探す場合は、Excelシフト表の名前の重複チェックを参照してください。店舗間の移動を含む可否は、他店舗への応援シフトの調整例で確認できます。
既存の勤務一覧に日時と状態をそろえる
まず元のファイルをコピーし、確認用の表を作成します。1勤務を1行にし、1行目には次の見出しを入れてください。店舗ごとに表が分かれている場合は、確認対象の勤務を同じ一覧へ集めます。
| 列 | 見出し | 入力内容・用途 |
|---|---|---|
| A | スタッフID | S001など、同一人物を識別するID |
| B | 氏名 | 管理者が本人を確認するための氏名 |
| C | 店舗 | 勤務先の店舗・施設 |
| D | 開始日時 | 年月日と開始時刻 |
| E | 終了日時 | 年月日と終了時刻 |
| F | 状態 | 有効、または取消 |
| G | 入力確認 | 入力不備を調べる数式 |
| H | 重複判定 | 重なる勤務の有無を表示する数式 |
以下では2〜1000行目を対象にします。実際の列配置が異なる場合は、先にこの配置へそろえるか、数式内の参照先を対応させてください。
スタッフIDと開始・終了日時を確認する
IDはS001のような半角英数字に統一します。同姓同名は別ID、同じ人は店舗をまたいでも同じIDにしてください。前後の空白も取り除きます。
COUNTIFSでは大文字・小文字を区別しない前提で管理し、S001とs001だけで別人を表さない運用にします。また、検索条件で特別な意味を持つ?、*、~はIDに使いません。COUNTIFSの条件ではワイルドカードを使用できるためです。(Microsoft)
D・E列は、2026/9/22 9:00のように年月日と時刻を入力します。Excelは日時を数値で扱い、表示形式によって見せ方を変えます。ここではセルの表示形式をユーザー定義のyyyy/m/d h:mmにそろえてください。(Microsoft)
時刻だけの9:00や、文字列として保存された日時は使いません。夜勤なら、開始を2026/9/22 22:00、終了を2026/9/23 6:00とし、終了側にも翌日の日付を入れます。24時間を超える表記で日付を省略せず、実際の開始・終了日時に直すのが今回の入力ルールです。
取消と入力不備を区別する
F列は「有効」「取消」のどちらかに統一します。空欄や「取り消し」など別の表記は、後述の式では入力確認になります。
取消は、実際に取り消すと決めた勤務を比較から外すための状態です。日時が分からない行や、エラーが出た行を隠す目的で使わないでください。入力不備は、不備として残して修正する必要があります。
二つの数式で重複する行を見つける
G列で比較できる行を確認し、H列ではG列が「対象」の行同士を比較します。先に入力不備を分けることで、取消と未確認の勤務を混同せずに作業できます。
G列で比較できる行を確認する
G2に次の式を入力し、G1000までコピーしてください。
=IF(COUNTA(A2:F2)=0,"",IF(F2="取消","対象外",IF(OR(A2="",F2<>"有効",NOT(ISNUMBER(D2)),NOT(ISNUMBER(E2))),"入力確認",IF(OR(D2<1,E2<=D2),"時刻確認","対象"))))この式は、IFで条件に応じて表示を分けます。判定の順序は次のとおりです。
- 空白：A〜F列がすべて未入力
- 対象外：F列が「取消」
- 入力確認：IDが空欄、状態が不適切、開始・終了日時が数値ではない
- 時刻確認：開始が1未満の値、または終了が開始以下
- 対象：上の条件に該当せず、比較に進める行
ISNUMBERは、セルの値が数値かを確認する関数です。数値に見える文字列も数値へ変換せず判定するため、文字列日時を比較へ進めない確認に使います。(Microsoft)
「入力確認」「時刻確認」が出たら、先に元表を確認して修正します。日またぎの終了日を入れ忘れた場合も、終了時刻だけを調整せず、正しい年月日から直してください。
なお、この式は氏名・店舗の空欄や、IDと本人の対応までは確認しません。数値として成立している日付の入力間違いも、元表との照合が必要です。
H列で同じIDの全勤務を比較する
H2に次の式を入力し、H1000までコピーします。
=IF(G2<>"対象",G2,IF(COUNTIFS($A$2:$A$1000,A2,$D$2:$D$1000,"<"&E2,$E$2:$E$1000,">"&D2,$G$2:$G$1000,"対象")>1,"重複あり","重複なし"))COUNTIFSは、複数の条件をすべて満たす件数を数える関数です。この式では、同じID、開始が自分の終了より前、終了が自分の開始より後、G列が「対象」という4条件を指定しています。(Microsoft)
自分自身も1件として数えるため、判定は>1です。2件以上なら、自分以外にも重なる勤務があると判断します。
店舗を条件に入れていないので、同じIDなら別店舗の勤務も比較対象です。また、指定範囲の全行を見るため、判定前の並べ替えは不要です。隣の行だけを調べる方法と違い、離れた位置の勤務も対象になります。
G列が「対象」でなければ、H列にはG列の表示を引き継ぎます。入力不備を「重複なし」に置き換えてしまわない点が重要です。
行を色分けして相手の勤務を探す
条件付き書式では、数式の判定結果を使ってセルの色を変えられます。次の手順で、重複した行を強調します。(Microsoft)
- A2:H1000を、A2を起点に選択する。
- ［ホーム］→［条件付き書式］→［新しいルール］を開く。
- 数式を使用するルールを選び、
=$H2="重複あり"を入力する。 - 塗りつぶし色を選び、適用先が
=$A$2:$H$1000になっているか確認する。
$H2はH列を固定し、行番号だけを変えて判定する指定です。$H$2にすると各行の判定を見なくなるため、入力時に注意してください。
相手の勤務を探すときは、見出しを含むA1:H1000を選び、［データ］→［フィルター］を設定します。列見出しの矢印から条件を指定して絞り込めます。(Microsoft)
まずH列を「重複あり」に絞り、次にA列で一つのIDを選択します。表示された行のD・E列を照合し、C列で勤務先を確認してください。同じIDが3行以上ある場合、表示された全行が互いに重なるとは限らないので、区間ごとに相手を特定します。
日またぎを含む6行で結果を確かめる
次は架空の勤務例です。A〜F列に入力し、G・H列へ上の式を設定してください。G列は全行「対象」になる想定です。
H列は数式の条件から導いた期待値です。使用するブックで、同じ表示になるか確かめましょう。
| 行 | A：ID | B：氏名 | C：店舗 | D：開始日時 | E：終了日時 | F：状態 | H：期待値 |
|---|---|---|---|---|---|---|---|
| 2 | S001 | 佐藤 | 東店 | 2026/9/22 9:00 | 2026/9/22 12:00 | 有効 | 重複あり |
| 3 | S001 | 佐藤 | 西店 | 2026/9/22 11:00 | 2026/9/22 15:00 | 有効 | 重複あり |
| 4 | S002 | 鈴木 | 東店 | 2026/9/22 9:00 | 2026/9/22 12:00 | 有効 | 重複なし |
| 5 | S002 | 鈴木 | 東店 | 2026/9/22 12:00 | 2026/9/22 15:00 | 有効 | 重複なし |
| 6 | S003 | 高橋 | 東店 | 2026/9/22 22:00 | 2026/9/23 6:00 | 有効 | 重複あり |
| 7 | S003 | 高橋 | 西店 | 2026/9/23 5:00 | 2026/9/23 8:00 | 有効 | 重複あり |
S001は11:00〜12:00が重なり、S002は12:00で接するだけです。S003は開始日が異なりますが、9月23日の5:00〜6:00が重なります。開始日が同じ行だけを調べる方法では、この夜勤の重複を見落とします。
包含・空欄・取消の場合も確認する
本番の表へ適用する前に、コピーした確認用シートで次のケースも試してください。各ケースは独立した架空例で、ほかに同じIDの勤務がない前提です。
| 確認する入力・操作 | G・H列の期待値 |
|---|---|
| 同じID、同日9:00〜18:00と12:00〜13:00 | 両行ともH「重複あり」 |
| 同じID、同日9:00〜12:00と12:00〜15:00 | 両行ともH「重複なし」 |
| 別IDで開始・終了日時が同じ | 両行ともH「重複なし」 |
| 同じID・別店舗で時間が重なる | 両行ともH「重複あり」 |
| A〜F列をすべて未入力にする | G・Hとも空白 |
| ID欠落、日時が文字列、状態の誤字 | G・Hとも「入力確認」 |
| 時刻だけ、終了が開始より前、開始と終了が同じ | G・Hとも「時刻確認」 |
| 重複する2行の一方のFを「取消」にする | 取消行は「対象外」、残る行はH「重複なし」 |
| 元の6行のD3を同日の12:00へ変更する | H2・H3とも「重複なし」 |
取消の確認と開始時刻の変更は、元の6行へ戻してから別々に行います。入力を変えた行だけでなく、重なっていた相手の結果も確認してください。
修正したら入力不備と重複をもう一度確認する
「重複あり」は、どちらの勤務が誤りかを示す表示ではありません。管理者が元のシフト希望や変更連絡を確認し、必要に応じて本人・店舗間で調整します。
取消にする場合は、取り消す勤務と同じ行のF列を「取消」へ変更してください。古い有効行を残したまま取消行だけを追加しても、古い勤務は比較対象に残ります。
修正後は再計算し、フィルターをいったん解除してG列から見直します。Excelには自動・手動の計算設定があるため、結果が変わらない場合は計算設定を確認し、必要に応じて［数式］から再計算を実行してください。(Microsoft)
G列の不備が残る行は比較から外れています。H列に「重複なし」が並んでいても、未確認の行があれば全体のチェックは完了していません。
行を追加したときの範囲に注意する
1001行目以降を追加したら、G・H列の式だけでなく、H列の全参照範囲も広げます。
たとえば2000行目まで使うなら、H式内のA・D・E・G列の末尾をすべて$2000へ変更し、その式をH2:H2000へ入れ直します。G列もG2000までコピーし、条件付き書式とフィルターの範囲を拡張してください。
COUNTIFSに渡す各範囲は、行数・列数をそろえる必要があります。新しい行の式だけを直し、既存行が1000行目までしか見ていない状態を残さないようにします。(Microsoft)
勤務条件と確定までの確認を続ける場合
今回の式は、開始から終了までの勤務全体を配置区間として扱います。休憩時間を自動控除したり、休憩中だけ別店舗勤務を認めたりする判定はしません。総労働時間、法定休憩、給与計算、法的適合の判断も対象外です。
二重配置を直した結果、必要人数や担当者が不足する場合は、勤務案の組み直しが残ります。シフト管理を作成段階から支援する選択肢として、シフトラには自然な日本語のルールを使ってシフト案を生成する機能があります（2026-09-21時点）。(シフトラ)
ただし、これは今回のExcelへ数式を追加したり、誤った行を自動削除したりする機能とは別です。出力された案の確認・調整と最終確定は、管理者が行います。
配布前は入力の正しさまで確認する
配布前の確認では、重複表示の解消に加え、入力不備が残っていないこと、対象行が数式の範囲内に収まっていることを確かめます。日付とスタッフIDは元表・名簿に照らし、取消の指定が実際の変更内容と一致しているかも確認してください。
この手順で得られるのは、「整えた一覧の範囲内で、同じIDの有効な勤務区間が重なっているか」という結果です。入力・重複・修正後の再判定を一続きにして、配布する勤務表を確定しましょう。