エクセルのシフト表と勤怠システムの勤務実績を比較するには、社員番号と勤務日を組み合わせて同じ人・同じ日の記録を照合します。実績の件数を先に数え、1件のときだけ出勤・退勤を取り出すと、時間差だけでなく、記録の不足や重複も確認できます。
毎月のシフト表を作成したあと、月間の予定表と縦長の実績データを目で追っている方に向けて、記入例入りの無料Excelと、その入力手順・数式・判定の読み方を、この記事で紹介します。予定と実績の行順を合わせる手作業は不要ですが、貼り付ける前にデータの形をそろえる必要があります。
社員番号と勤務日で照合するExcelを用意する
例は、営業時間10:00〜20:00、従業員8人の架空の駅前の雑貨店です。対象は2026年12月1日(火)〜7日(月)。店舗・氏名・社員番号・勤務・時刻はすべて架空で、実際の店舗の勤怠記録ではありません。
記入例と数式が入った配布Excelです。登録は不要です。
ブックは「設定」「予定」「実績」「照合」「確認」「使い方」の6シート構成で、マクロは使いません。黄色が入力欄、灰色が数式欄です。操作の概要は「使い方」シートにもまとめています。入力した予定と実績から照合結果を自動表示し、判定別の件数を自動集計します。従業員への勤務の自動割当は行いません。
計算結果はLibreOffice 26.2で再計算して検算しています。Excel各版での実機確認はしていないため、最初に記入例を開き、後述の件数が表示されるか確かめてください。
予定は1人1日1行、実績は1勤務1行にそろえる
日付が横に並ぶ月間表は、そのままでは貼れません。従業員ごとに勤務日を縦へ並べ、社員番号・氏名・勤務日・勤務記号の4列にします。横に並んだ表を1人1日1行へ並べ替える式の考え方は、シフト表をエクセルからCSVに変換する手順で扱っています。佐藤の予定なら、次の形です。
| 「予定」の行 | A:社員番号 | B:氏名 | C:勤務日 | D:勤務記号 |
|---|---|---|---|---|
| 2 | 00101 | 佐藤 | 2026/12/1 | 早 |
| 3 | 00101 | 佐藤 | 2026/12/2 | 休 |
| 4 | 00101 | 佐藤 | 2026/12/3 | 早 |
元表の日付が「1」「2」のような日だけの見出しなら、年・月も補います。休みの日も「休」として1行残してください。記入例は人ごとに7日分を並べた56行で、出勤予定31行、休み25行です。
実績は、出勤・退勤の打刻を組み合わせた1勤務1行のデータを用意します。会社や勤怠システムによって出力の形は異なるので、出勤と退勤が別行の打刻ログは、先に整理が必要です。日をまたぐ夜勤は対象外で、このブックでは出退勤に日付を含めず、時刻だけを入力します。夜勤の打刻は、エクセルで夜勤の打刻を突合する方法で扱っています。
予定のシート:勤務記号から開始・終了を引く
まず「設定」のB3に許容する差を分で入れます。配布時は5です。A7:C16には10種類までの時間定義を登録可能で、初期値は次のとおりです。
| 設定する行 | A:勤務記号 | B:開始 | C:終了 |
|---|---|---|---|
| 7 | 早 | 10:00 | 19:00 |
| 8 | 遅 | 11:00 | 20:00 |
| 9 | 休 | 空欄 | 空欄 |
開始が空欄の記号は休みとして扱います。勤務パターンごとに記号を一つ決め、勤務のある記号は開始・終了を両方入れ、記号を重複させないでください。
続いて「予定」のA2:D301へ、整えた4列を値として貼り付けます。記号をもとにE列へ開始、F列へ終了が出ます。時間定義にない記号は「未登録」になるので、記号の誤りか定義の不足かを確認しましょう。
G列には、社員番号と勤務日をつないだ照合キーが出ます。日付を文字列にそろえる部分にはTEXT関数を使います。(Microsoft サポート「TEXT 関数」)
- 「予定」G2:
=IF(A2="","",A2&"|"&TEXT(C2,"yyyymmdd"))
佐藤の12月1日なら、キーは 00101|20261201 です。氏名や行番号ではなく、この文字列が同じ記録を探します。予定C列と実績B列は、文字列の日付ではなく、Excelの日付の値にそろえてください。
実績のシート:社員番号・勤務日・出勤・退勤を貼る
勤怠システムの画面から取得(出力)したデータは、A列から社員番号・勤務日・出勤時刻・退勤時刻の順に並べ替え、「実績」のA2:D301へ値として貼ります。出退勤は文字列や日時ではなく、時刻だけの値にそろえます。配布時の先頭は次の3行です。
| 行 | A:社員番号 | B:勤務日 | C:出勤 | D:退勤 |
|---|---|---|---|---|
| 2 | 00101 | 2026/12/1 | 10:03 | 19:02 |
| 3 | 00104 | 2026/12/1 | 10:57 | 20:02 |
| 4 | 0205 | 2026/12/1 | 10:59 | 20:04 |
社員番号は、予定側も実績側も文字列のまま保ちます。 この例は社員が5桁、アルバイトが4桁です。佐藤の「00101」と伊藤の「0101」は別人なので、一律の桁数に変換しないでください。
CSVの読み込み時に社員番号列を文字列に指定する方法や、入力前に文字列の書式を設定する方法が、Microsoftから案内されています。先頭の0が落ちた後で書式だけを変えても、元の番号は復元されません。(Microsoft サポート「先頭のゼロおよび大きい数値の保持」)
実績側のキーはE列で作り、F列で対応する予定の件数を数えます。
- 「実績」E2:
=IF(A2="","",A2&"|"&TEXT(B2,"yyyymmdd")) - 「実績」F2:
=IF(A2="","",SUMPRODUCT((予定!$G$2:$G$301=E2)*1))
G列には、予定が0件なら「予定なし」、予定が休みなら「予定は休み」と表示されます。応援の小林「00130」の12月5日は予定表にいないため、実績26行目が「予定なし」です。予定側から探すだけでは、この記録を拾えません。
なお、予定に同じ社員番号・勤務日が2行あっても、専用の警告は出ません。実績F列が2以上の行も確認し、予定を1人1日1行に直してください。
照合:実績の件数を数え、開始・終了の差を分で出す
「照合」は予定の1行につき1行の一覧です。A〜F列に予定、G列に実績の件数、H・I列に実績の出勤・退勤、J・K列に差の分数、L列に判定が並びます。
実績の件数は、同じキーの行をSUMPRODUCTで数えます。1件のときだけ、MATCHで位置を探し、INDEXで時刻を取り出す構成です。MATCHの末尾の0は完全一致を指定します。(Microsoft サポート「SUMPRODUCT 関数」)(Microsoft サポート「MATCH 関数」)
- 「照合」G2:
=IF(A2="","",SUMPRODUCT((実績!$E$2:$E$301=予定!G2)*1)) - 「照合」H2:
=IF(OR(A2="",G2<>1),"",IF(INDEX(実績!$C$2:$C$301,MATCH(予定!G2,実績!$E$2:$E$301,0))="","",INDEX(実績!$C$2:$C$301,MATCH(予定!G2,実績!$E$2:$E$301,0))))
I列は実績のD列から退勤を取り出します。0件や2件以上では、H・I列を空欄にして確認に回します。先に件数を確認するため、山本の12月4日のように実績が2行あっても、片方の時刻だけで比較することはありません。
差は実績−予定です。時刻の差に1440を掛けると分になります。(Microsoft サポート「Excel で 2 つの時刻の差を計算する」)
- 「照合」J2:
=IF(AND(ISNUMBER(H2),ISNUMBER(E2)),ROUND((H2-E2)*1440,0),"") - 「照合」K2:
=IF(AND(ISNUMBER(I2),ISNUMBER(F2)),ROUND((I2-F2)*1440,0),"")
時刻が数値として入っているときだけ計算し、ROUNDで整数の分にします。正なら予定より遅く、負なら早い時刻です。差の列は時刻表示ではなく、分を単位とする数値として読みます。
| 人・勤務日 | 予定 | 実績の出勤〜退勤 | 開始の差 | 終了の差 | 判定 |
|---|---|---|---|---|---|
| 佐藤12/1 | 10:00〜19:00 | 10:03〜19:02 | +3分 | +2分 | 一致 |
| 佐藤12/4 | 10:00〜19:00 | 9:57〜18:20 | −3分 | −40分 | 時間差あり |
| 鈴木12/3 | 11:00〜20:00 | 10:58〜20:45 | −2分 | +45分 | 時間差あり |
| 高橋12/2 | 10:00〜19:00 | 10:12〜19:01 | +12分 | +1分 | 時間差あり |
開始と終了を別々に比べるので、勤務時間の合計だけでは見落とす出退勤のずれが発生していても確認できます。ただし、休憩を差し引いた実働時間の比較は行いません。
判定の分け方と、許容する差
通常の判定は次の7種類です。これに、入力不備を示す「記号が未登録」が加わります。
| 判定 | 条件・読み方 |
|---|---|
| 一致 | 実績1件で、開始・終了の差が両方とも許容分以内 |
| 時間差あり | 実績1件で、開始か終了の差が許容分を超える |
| 実績なし | 出勤予定に対応する実績が0件 |
| 実績が複数 | 出勤予定に対応する実績が2件以上 |
| 打刻不足 | 実績1件だが出勤か退勤が空欄などで、差を計算できない |
| 休みに実績 | 休みの予定に対して実績がある |
| 休み | 休みの予定で実績がない |
「照合」L2の判定式は次のとおりです。
=IF(A2="","",IF(E2="未登録","記号が未登録",IF(E2="",IF(G2=0,"休み","休みに実績"),IF(G2=0,"実績なし",IF(G2>1,"実績が複数",IF(OR(J2="",K2=""),"打刻不足",IF(AND(ABS(J2)<=設定!$B$3,ABS(K2)<=設定!$B$3),"一致","時間差あり")))))))
ABSで早い・遅いの両方向の差を比べています。許容5分なら、佐藤12月1日の+3分・+2分も「一致」です。ここでの一致は時刻が完全に同じという意味ではありません。許容値は確認対象を絞るための設定で、現場の運用に応じて調整します。遅刻の認定や賃金計算の端数処理には使いません。
確認シートで件数と「要確認7件」を確かめる
配布時のデータを再計算すると、「確認」シートには次の件数が出ます。
| セル | 集計項目 | 配布時 |
|---|---|---|
| B4/B5/B6 | 出勤予定/休み/実績の行数 | 31/25/33 |
| B7/B8 | 一致/時間差あり | 26/3 |
| B9/B10/B11 | 実績なし/実績が複数/打刻不足 | 1/1/0 |
| B12/B13/B14 | 休みに実績/記号が未登録/予定なし | 1/0/1 |
| B15 | 要確認の行に確認メモを書いた件数 | 0 |
| B16 | 件数の検算 | OK |
| B18 | 総合判定 | 要確認 7件 |
B16の検算は「26+3+1+1+0=31」で、出勤予定を判定別に分けた件数が合うかを見ています。ここがOKでも、全体の件数が合っていることを示すだけで、個々の記録の確認が終わったわけではありません。
B18では、時間差あり・実績なし・実績が複数・打刻不足・休みに実績・記号が未登録の合計から、確認メモを書いた行を引き、実績側の「予定なし」を加えます。
=IF(B4+B5+B13=0,"予定が未入力",IF(B8+B9+B10+B11+B12+B13-B15+B14=0,"OK:確認が必要な記録は残っていません","要確認 "&(B8+B9+B10+B11+B12+B13-B15+B14)&"件"))
中村12月4日は、照合側の「休みに実績」と実績側の「予定は休み」に出ますが、総合判定では重複して数えません。「予定なし」は別に1件として数えます。
時間差は理由を残し、片方だけの記録は元データを直す
最初に元の確定シフトと実績を別途保存し、比較用のコピーで修正します。「照合」L列の判定で絞り込むと確認しやすくなります。実績側のG列も確認してください。以下は、担当者が本人や責任者(管理者)に相談して確認できたと仮定し、その内容に基づく修正例です。
| 対象 | 確認後の操作 |
|---|---|
| 佐藤12/4・退勤18:20 | 照合M5へ「早退(承認済み)」と記入 |
| 鈴木12/3・退勤20:45 | 照合M11へ「残業(棚卸しの準備)」と記入 |
| 高橋12/2・出勤10:12 | 照合M17へ「遅刻(電車の遅れ・連絡あり)」と記入 |
| 渡辺12/2・実績なし | 勤怠側で打刻もれが訂正された11:00〜20:00の実績を1行追加 |
| 山本12/4・実績が複数 | 中抜けの11:00〜14:00と15:30〜20:02を、比較用に11:00〜20:02の1行へ整理 |
| 中村12/4・休みに実績 | 急な出勤の反映漏れを確認し、設定A10:C10へ「短・11:00・15:00」を追加。予定D54を「短」に修正 |
| 小林12/5・予定なし | 応援予定の漏れを確認し、予定A58:D58へ「00130・小林・2026/12/5・早」を追加 |
時間差の3件は、実績の時刻を直接書き換えずにメモを残します。 メモを書くと総合判定の未確認件数から除かれますが、L列の「時間差あり」は変わりません。文字が入っているだけで確認済みとして数えるため、未確認の行に仮のメモを入れない運用が必要です。
山本の2行をまとめるのは、勤務の始まりと終わりを比較するためです。中抜けを含む時間を労働時間として扱う処理ではなく、元の明細は残します。また、実績がないだけで欠勤と決めたり、休みの予定を理由なく実績へ合わせたりしないでください。休暇や残業の申請を別に管理している場合は、その記録とも照らし合わせます。
修正後は、出勤予定33、休み24、実績33、一致30、時間差あり3、確認メモ3となり、ほかの要確認項目は0です。B18には「OK:確認が必要な記録は残っていません」と出ます。これは、このブック内の確認が完了したという意味です。
確認例:社員番号のゼロ落ち、許容する差、退勤の空欄
操作を試すときは、毎回配布時の状態へ戻してから、1か所だけ変更してください。前の変更を残すと、下表と件数が合わなくなります。
| 変更するセル | 表示の変化 | 総合判定 |
|---|---|---|
| 実績A2を数値の101にする | 佐藤12/1が「実績なし」、実績2行目が「予定なし」。一致は26→25、実績なしは1→2、予定なしは1→2 | 要確認 9件 |
| 設定B3を5から0にする | 1分でも差があれば時間差あり。一致6、時間差あり23 | 要確認 27件 |
| 実績D3を空欄にする | 田中12/1の終了の差が空欄になり「打刻不足」。一致25、打刻不足1 | 要確認 8件 |
ゼロ落ちでは、実績のキーが 101|20261201 となり、予定の 00101|20261201 と一致しません。文字列の「101」でも結果は同じです。元の社員番号を確認し、A列を文字列の書式にしてから「00101」を貼り直します。桁数が混在する番号を従業員マスタと照合して戻す方法は、シフト表のCSVで社員番号がゼロ落ちしたときの直し方にまとめています。
退勤が空欄なら、予定の20:00を推測で補わず、元の打刻を確認します。勤務記号を未登録の「研」に変えた場合も「記号が未登録」になるため、予定の記号を直すか、時間定義へ記号と開始・終了を登録してください。
比較後の確認と、次回の予定データの用意
この比較は、シフト管理の中で予定どおりに勤務できたかという状況を把握し、確認する記録を選び出す作業です。差の分数だけでは残業時間・欠勤・法令への適合を確定できません。割増賃金の計算、休憩を含む実働時間の比較、勤怠システムへの取り込みや連携も対象外です。関連する作業として、予定表どうしの変更箇所はエクセルのシフト表で変更箇所を比較する方法、予定の勤務時間の計算はシフト表をエクセルで自動計算する方法で扱っています。
照合結果を手がかりに、元の記録と勤務の実態を確認しましょう。時間差が多く出る日や人が見つかれば、シフトの組み方の改善を検討する材料としても活用できます。時間帯ごとに必要な人数や、次の月のシフトの計画を見直すときにも役立ちます。
次回以降も毎月の業務として行う場合は、対象期間を予定・実績でそろえ、予定・実績の入力欄と照合M列の確認メモを空にしたコピーへ貼り付けます。設定と数式欄は残してください。予定・実績・照合は2〜301行の300行分なので、超える場合は数式を下へコピーし、各シートの参照範囲も $2:$301 から必要な行まで広げます。行を追加するだけでは集計対象になりません。
予定データの準備には、確定シフトの出力機能も使えます。シフトラでは、確定したシフトを指定した日の分だけCSVまたはExcelで出力できます。CSVの出力項目には従業員ID・氏名・日付・開始と終了・シフト名を含められ、独自のCSVテンプレートで列の順番と見出し名を設定できます(2026-09-05時点の製品情報。利用条件は設定・権限によります)。勤務実績や打刻を取り込んで予定と照合する機能ではありません。
出力後は勤怠側と社員番号を対応させ、シフト名を勤務記号に合わせ、休みの行も含めて入力形へ整えます。勤務実績の用意と照合、時間差の理由の確認は、ここで紹介した手順で進めてください。