メインコンテンツへスキップ
約14分で読めます

エクセルのシフト表で資格期限をチェックする|勤務日と有効期限を照合し、期限切れ・期限未入力の勤務を出す

エクセルのシフト表で勤務日と資格の有効期限を照合し、期限切れ・期限未入力の勤務を見つけるガイド

エクセルのシフト表で資格期限をチェックするには、「担当の記号に必要な資格」と「従業員ごとの有効期限」を登録し、各勤務日と照合します。月初には有効でも、月の途中で期限が切れる人は、期限後の勤務だけを確認対象にできます。

配布Excelでは、作成・入力済みのシフトに「○・切・未」を自動表示し、期限切れ・期限未入力の勤務を一覧にします。担当の入力と入れ替えは人が行い、数式が照合と件数集計を受け持つ仕組みです。1件ずつ見比べる時間を減らせます。

エクセルのシフト表で資格期限をチェックするには

入力するのは「シフト表」「必要資格」「有効期限」の3シート。結果は「チェック」「一覧」「確認」に出ます。「作業用」「使い方」を含む8シートの記入例入りExcelを用意しました。

資格期限をチェックするExcelをダウンロード(.xlsx)

このページの配布ファイルで、登録は不要です。

シフト表の担当の記号、記号ごとの必要資格、従業員ごとの有効期限の3つの表を照合し、チェックシートに○(有効)・切(期限切れ)・未(期限未入力)を出す流れ。
シフト表の担当の記号、必要資格の表、有効期限の表の3つを照合する。資格が必要な勤務ごとに、勤務日が有効期限以前なら○、有効期限より後なら「切」、有効期限の登録がなければ「未」と表示する。

図を拡大して見る

記入例は、製造業向けに用意した、架空の部品工場の組立ライン8人分、2026年12月の31日分です。職場・氏名・社員番号・担当・資格名・期限はすべて架空で、資格が必要な勤務58件のうち、期限切れ5件、期限未入力4件がある状態から試すことが可能です。

ファイルはマクロなし。検算はLibreOffice 26.2での再計算によるものです(2026-11-12時点)。Excel各版での実機確認はしていないため、利用環境でも後述の件数・表示を確かめてください。

期限の管理表との違い:勤務日の時点で有効かを見る

この表で比べるのは「今日」と期限ではなく、担当に入る勤務日と期限です。鈴木の社内検査員認定が2026/12/15までなら、12/9・13の「検」は○、12/16以降の「検」は切になります。期限までの残り日数や更新通知を管理する表ではありません。

「社内検査員認定」「フォークリフト社内再教育」は、社内で実施して期限を管理する認定・研修等の架空の名称です。このExcelは入力された日付だけを照合し、資格・研修の制度上の有効期間、更新の要否、配置の適否、法令への適合を判定しません。

法令上必要な資格や教育は、この表とは別に確認します。社内の期限欄を埋めるだけで、そうした条件の確認まで済むわけではありません。

対象年月と担当の記号を入力する

「シフト表」のB2に年、D2に月を入れます。記入例はB2が2026、D2が12。4行目のC列から日付、5行目に曜日が並び、A7:A36に社員番号、B7:B36に氏名、C7:AG36にその日の担当の記号を入力します。

社員番号は文字列で扱います。例では社員が5桁、アルバイトが4桁で、どちらも先頭が0です。

区分氏名と社員番号
社員佐藤:00101、鈴木:00102、高橋:00103、田中:00104、山本:00120
アルバイト伊藤:0101、渡辺:0205、中村:0310

特に「00101」と「0101」を同じ番号にしないでください。入力欄は文字列の書式ですが、別の表から貼り付けるときも先頭の0と桁数を照合します。資格名と同様に、番号の書き方が違えば別の組み合わせとして扱われます。

自分の職場で使うときは、コピーしたファイルの黄色い入力欄を消して入れ直します。消す範囲は「シフト表」A7:AG36、「必要資格」A4:C15、「有効期限」A4:A203とC4:D203です。灰色の数式セル、特に「有効期限」のB列・E〜H列は残してください。

必要資格の表:担当の記号と資格名を対応させる

「必要資格」のA4:A15に記号、B4:B15に必要な資格名を指定して登録します。C列はメモ欄です。資格が不要な記号も表に登録し、その資格名だけを空欄にします。

行記号必要な資格名メモ
4組空欄組立
5検社内検査員認定外観検査
6運フォークリフト社内再教育フォークリフト運搬
7休空欄休み

この記号と資格の関係(対応)は、自分の職場の決まりや基準に応じて書き換えます。表にない記号をシフトへ入力すると「チェック」に「?」が出るため、資格不要の担当も必ず表に登録し、未登録のまま使用しないでください。

1記号に対応する資格は1つです。2つ以上の資格を同時に確認する業務は、表を2つ使って両方を照合するなどの設計が必要です。また、半角の *・?・~ は記号に使いません。照合に使うMATCH関数では、これらが検索やエスケープに使われるためです。(Microsoft サポート「MATCH 関数」)

有効期限の表:1人1資格1行で登録する

「有効期限」の4〜203行に、A列=社員番号、C列=資格名、D列=有効期限を入れます。B列の氏名は自動表示です。資格名は「必要資格」と同じ表記を使い、期限は自社の台帳や、資格を取得したときの証明書など、期限を証明できる書類・資料に記載された日付に照らして入力します。

記入例の登録内容は以下のとおりです。佐藤の2行は、二重登録を確認するために残しています。

行社員番号・氏名資格名有効期限
400101・佐藤社内検査員認定2026/11/30
500101・佐藤社内検査員認定2027/11/30
600102・鈴木社内検査員認定2026/12/15
700102・鈴木フォークリフト社内再教育2027/9/30
800103・高橋社内検査員認定2027/3/31
900103・高橋フォークリフト社内再教育2027/5/31
1000120・山本社内検査員認定2027/6/30
1100120・山本フォークリフト社内再教育2028/1/31

中村(0310)のフォークリフト社内再教育は未登録です。行が存在しない場合も、必要な勤務に入れば「未」として見つかります。新規に入った人や、新しく担当に加わった人の登録漏れも、ここで分かります。

F列には、期限が空欄なら「期限なし」、文字列なら「日付でない」、同じ人・同じ資格の重複には「二重登録」と表示します。社員番号や資格名が欠けた行は「入力不足」です。G列で社員番号と資格名を結び、H列では同じ組み合わせの最も遅い期限を採用します。

二重登録では、後から入力した行を無条件に採用するわけではありません。両方の日付を比べて遅いほうを使うため、誤って先の日付を入れた場合もその値が使われます。「二重登録」が出たら台帳を確かめ、正しい行だけを残してください。

数式は次のとおりです。E4は比較に使う値を取り出し、F4は入力状態を表示します。

有効期限!E4
=IF(ISNUMBER(D4),D4,0)

有効期限!F4
=IF(AND(A4="",C4="",D4=""),"",IF(G4="","入力不足",IF(D4="","期限なし",IF(NOT(ISNUMBER(D4)),"日付でない",IF(SUMPRODUCT(($G$4:$G$203=G4)*1)>1,"二重登録","")))))

ISNUMBERは数値かどうかを調べる関数で、文字列を数値へ変換しません。(Microsoft サポート「IS 関数」) 見た目が日付でも、文字として入った値(文字列)ならこの表では期限未入力として扱います。一方、数値で入力した年月日の誤りまでは検出しないため、台帳との照合は必要です。

チェック表:勤務日と有効期限を照合する

「作業用」は30人×31日を1勤務1行に並べ直す補助シートです。E列が勤務日、F列が必要資格、G列が社員番号と資格名の照合キー、H列が採用する期限、I列が判定になります。

シフト表とチェックシートの抜粋。鈴木の「検」は期限2026/12/15の翌日から切になり、中村の「運」は有効期限の登録がないので未になる。判定の式つき。
チェック表の抜粋(12月9日〜26日)。鈴木の「検」は12/9・12/13が○、有効期限2026/12/15の翌日以降の12/16・17・20・21・25が「切」。中村の「運」12/10・17・24・26は有効期限の登録がないため「未」になる。

図を拡大して見る

H2で該当する期限の最大値を取り、I2で勤務日と比べます。

作業用!H2
=IF(G2="","",SUMPRODUCT(MAX((有効期限!$G$4:$G$203=G2)*有効期限!$E$4:$E$203)))

作業用!I2
=IF(E2="","",IF(F2="?","?",IF(G2="","",IF(H2=0,"未",IF(E2>H2,"切","○")))))
表示意味
○勤務日が有効期限以前
切勤務日が有効期限より後
未対応する期限の登録がない、空欄、または文字列
?必要資格の表にない記号
空欄資格不要の記号・休み・未入力・月の日数を超える日

期限の当日は○で、翌日から切です。これは配布Excelの比較ルールです。「○」は入力された期限内という意味に限られ、業務に就ける条件をすべて確認した印ではありません。

「チェック」C7には、補助シートの判定を元のシフト表と同じ位置へ戻す式が入っています。

チェック!C7
=INDEX(作業用!$I$2:$I$931,(ROW()-7)*31+COLUMN()-2)

C7:AG36で該当する勤務を読み、AH列で人ごとの「切」、AI列で「未」の件数を確認します。日ごとの件数は38行が「切」、39行が「未」です。例では鈴木のAH8が5、中村のAI14が4になります。

確認対象の一覧と件数

「一覧」には、「チェック」の中から「切・未」だけが、人の順・日付の順で詰めて並びます。作り方は、まず「作業用」J列で対象に連番を振り、「一覧」H列で連番に対応する位置を探し、各列のINDEX関数で必要な情報を取り出す流れです。

一覧シートに並ぶ確認対象9件(鈴木の期限切れ5件、中村の期限未入力4件)と、確認シートの配布時の値(資格が必要な勤務58、有効49、期限切れ5、期限未入力4)。
一覧シートには確認対象の9件(鈴木の期限切れ5件、中村の期限未入力4件)が並ぶ。確認シートは、資格が必要な勤務58、有効49、期限切れ5、期限未入力4で検算はOK、総合判定は「要確認:期限切れ・期限未入力の勤務があります」。

図を拡大して見る

作業用!J2
=IF(OR(I2="切",I2="未"),COUNT($J$1:J1)+1,"")

一覧!H2
=IF(ROW()-1>MAX(作業用!$J$2:$J$931),"",MATCH(ROW()-1,作業用!$J$2:$J$931,0))

一覧!A2
=IF($H2="","",INDEX(作業用!$C$2:$C$931,$H2))

一覧の列は、社員番号・氏名・勤務日・記号・必要な資格・有効期限・判定です。印刷して責任者との確認に使えます。配布時の9行を、ここでは人ごとにまとめて示します。

社員番号・氏名勤務日(2026年12月)記号・必要な資格有効期限判定・件数
00102・鈴木16・17・20・21・25日検・社内検査員認定2026/12/15期限切れ5件
0310・中村10・17・24・26日運・フォークリフト社内再教育空欄期限未入力4件

「確認」のB4〜B12では、結果と入力状況を項目ごとにまとめて確認できます。詳細は「一覧」と「チェック」で見ます。

セル確認項目配布時の値
B4資格が必要な勤務58
B5有効(○)49
B6期限切れ(切)5
B7期限未入力(未)4
B8表にない記号(?)0
B9採用する期限が対象月内にある登録行数1
B10二重登録の行数2
B11日付でない有効期限0
B12有効期限が空欄の登録0

「期限未入力の勤務」と「有効期限が空欄の登録」は数える対象が違います。配布時の中村は登録行そのものがないので、勤務側のB7は4件でも、空欄行を数えるB12は0件です。B12だけを見て登録漏れがないと判断しないでください。

C4の検算は49+5+4=58で「OK」ですが、これは件数の合計が合うという意味です。B14の総合判定は「要確認:期限切れ・期限未入力の勤務があります」。また、「?」は一覧の対象外なので、一覧が空でもB8を確認してください。B4が0の場合は「資格が必要な勤務がありません」と表示されます。想定と違う場合は、担当の記号と必要資格の対応から点検します。

確認例:月の途中の期限、未登録、二重登録、文字列の日付

次の例は、配布時の状態に戻してから、一つずつ確認します。入力内容によって、勤務の表示と台帳側の注意がどう変わるかを見てください。

確認例5つ。期限を更新すると切が0件、期限を登録すると未が0件、文字列の日付は日付でないと表示され未が計10件、資格名の表記違いは未が計13件、同じ人・同じ資格の2行は遅いほうの期限を採用する。
確認例5つ。鈴木の期限を2027/12/15に更新すると期限切れは0件、中村の期限を登録すると期限未入力は0件になる。文字列の日付は「日付でない」で未が計10件、資格名の表記違いは未が計13件。同じ人・同じ資格が2行あるときは遅いほうの期限を採用する。

図を拡大して見る

確認する状況入力・操作結果
月の途中の期限鈴木の期限が2026/12/15の状態で確認「検」の12/9・13は○、16・17・20・21・25は切
未登録「有効期限」A12に0310、C12にフォークリフト社内再教育を入れ、D12は空欄F12は「期限なし」、中村の「運」4件は未のまま
二重登録佐藤の4・5行目を残して確認F4・F5は「二重登録」。遅い2027/11/30を使い、「検」9件は○
文字列の日付「有効期限」D8に、先頭にアポストロフィを付けて2027/3/31を入力F8は「日付でない」。高橋の「検」6件が未となり、未は合計10件
資格名の表記違い「有効期限」C10を「検査員認定」に変更山本の「検」9件が未となり、未は合計13件。有効期限のF10には注意が出ない

文字列の日付は、先頭のアポストロフィを外し、日付として入力し直します。資格名の表記違いは、期限そのものが入っていても照合できません。「必要資格」の名称にそろえてから、勤務側の「未」が解消したかを見ます。

月の切り替えも試せます。「シフト表」D2を11にすると、記号の並びが同じでも31日目は対象外になり、資格が必要な勤務は56件、有効52件、切0件、未4件です。鈴木の期限より前の勤務になり、佐藤の古い期限も採用されないため、対象月内に期限が来る登録は0になります。

見つかった後の作業と、担当に必要な条件の管理

期限切れが出たら、先に本人と責任者に相談し、更新後の期限を台帳・証明書で確かめて登録を直すか、その日の担当を替えます。更新予定がある、または更新を申請しているというだけで期限を延ばさず、新しい証明書の提出を受けてから登録を直します。担当の入れ替えは、組織の決まりに沿って責任者が判断してください。期限未入力なら、まず登録漏れ・空欄・文字列・表記違いのどれが原因かを探すところから始めます。

配布時の2026年12月の状態に戻し、次の順番で修正を試します。

  1. 「有効期限」D6を2027/12/15に更新すると、鈴木の切5件が○になり、切は0件になります。
  2. A12に0310、C12にフォークリフト社内再教育、D12に2027/8/31を登録すると、中村の未4件も○になり、未は0件になります。
  3. 両方を直すと58件すべてが○で、一覧は空になります。ただしB14は「勤務はOK:有効期限の表に直す行があります」です。
  4. 佐藤の古い登録であるA4・C4:D4の入力を消すと、二重登録も0になり、B14が「OK」に変わります。行全体や数式セルは削除しません。

配布範囲は従業員30人×31日、記号12個、有効期限200行、一覧300行です。上限を超える場合は数式の参照範囲も広げます。特に確認対象が300件を超えると一覧に全件が出ないため、B6とB7の合計件数も確かめてください。

シフト管理のシステムで担当できる人と必要人数も整理するなら、シフトラではスキル・担当を定義して従業員を紐づけ、担当・スキル別に必要人数を設定できます(2026-09-05時点の製品情報。利用条件は設定・権限によります)。資格や研修の有効期限を勤務日と照合する機能ではないため、勤務日時点の資格期限は、このExcelや台帳で別途照合する運用が必要です。

担当ごとに任せられる人を整理する台帳はスキルマップの無料エクセルテンプレート、責任者がいない日の確認はエクセルのシフト表で責任者の不在をチェックする方法、資格者を時間帯ごとに配置する条件はAIシフトで資格者を配置する方法で扱っています。

実際の運用では、期限を更新したときだけでなく、担当の変更を行う場合にも「チェック」「一覧」「確認」を開き、残った勤務と入力欄を確かめてからシフトを確定し、その月の勤務を開始してください。

担当に必要な条件を見ながらシフトを組んでいる担当者へ

担当ごとに、入れる従業員と必要人数を決めてシフトを作る。

シフトラは、スキル・担当を定義して担当できる従業員を紐づけ、必要人数を担当・スキルごとに分けて設定できます。スキルごとに使用できるシフトパターンを限定することもできます。

  • スキル・担当ごとに担当できる従業員を紐づけ
  • 必要人数を担当・スキルごとに設定
  • スキルごとに使用できるシフトパターンを限定

資格や研修の有効期限を勤務日と照合する機能ではありません。本記事のチェックは配布Excelの数式で行います。

機能・料金・導入の流れを見る 資料を請求する
目次