エクセルのシフト表に「早」「早番」「A」が混在し、COUNTIFで数えた件数が合わない。そんなときは、元の勤務記号と統一記号を並べた「記号対応表」を作り、別シートへ置き換えた結果を表示します。検索と置換を1つずつ繰り返す手間のかかる作業を、対応表の登録・修正にまとめる方法を、記入例つきで解説します。
勤務記号の統一では、書き方をそろえた後の確認も欠かせません。対応表にない記号を「?」付きで残し、統一前後の件数を照合します。さらに、同じ記号でも店舗によって勤務時間が違わないか、開始・終了・休憩の定義を比べましょう。
エクセルのシフト表の勤務記号を統一するには
元の表を残し、対応表で変換し、件数と時間定義を確認するのが基本です。店長や管理者などの担当者が決めるのは、どの書き方を同じ勤務として扱うか。数式が行うのは、決めた対応に沿った自動表示と自動集計で、スタッフの勤務の自動割当ではありません。
複数店舗のシフト表を扱う担当者向けに、記入例と数式を入れた無料の配布Excel(テンプレート)です。登録は不要です。
ブックは「元の表」「記号対応表」「統一後」「確認」「時間定義」「使い方」の6シート構成です。2026-11-10時点の配布版はマクロなしで、LibreOfficeによる再計算で本文の件数と確認例を照合しています。まず記入済みの状態を開き、結果を見てから自店のデータへ置き換えてください。以下の変更例は、それぞれ配布時のブックを複製して試します。
例:2店舗で書き方が違う12月のシフト表
例は、架空の雑貨店チェーンの駅前店・公園通り店、8人分の2026年12月のシフト表です。営業時間は10:00〜20:00。統一記号の基準は「早=10:00〜19:00」「遅=11:00〜20:00」で、どちらも休憩60分、「休」は休みとします。日勤・夜勤・明けのような記号を使う職場でも、対応表に登録する記号の種類が変わるだけで、手順は同じです。
「元の表」の4行目が見出し、5〜12行目が8人のデータです。A列に店舗、B列に社員番号、C列に氏名、D〜AH列に日付ごと(1〜31日)の勤務記号が入り、対象範囲はD5:AH12。248セルすべてに入力があります。
| 店舗 | 元の表で使われている書き方 |
|---|---|
| 駅前店 | 早・遅・休。一部に前後の空白がある |
| 公園通り店 | 早番・遅番・公休。山本の一部は全角の「A」と半角の「A」 |
| 両店舗の確認対象 | 対応表にない「研」が合計3件 |
B列の社員番号は文字列のまま扱います。この例では社員が5桁、アルバイトが4桁で、佐藤は「00101」、伊藤は「0101」です。自店の表を貼るときもB列を文字列にして、先頭の0を残してください。今回の置き換え対象は勤務記号のセルです。
記号対応表を作る(元の記号と統一記号)
「記号対応表」のA列に元の記号、B列に統一記号を入力します。登録範囲はA2:B21で、配布版には次の8件が入っています。
| 行 | A列:元の記号 | B列:統一記号 |
|---|---|---|
| 2 | 早 | 早 |
| 3 | 遅 | 遅 |
| 4 | 休 | 休 |
| 5 | 早番 | 早 |
| 6 | 遅番 | 遅 |
| 7 | 公休 | 休 |
| 8 | A | 早 |
| 9 | A | 早 |
すでに統一した書き方の「早」「遅」「休」も登録します。全角の「A」と半角の「A」は両方を別の行へ入れ、対応表の記号には空白を付けません。この例では「公休」と「休」を同じ休みとして扱いますが、自店で意味が違う区分までまとめないでください。
照合に使うMATCHは英字の大文字と小文字を区別せず、半角の「?」「*」をワイルドカード、「~」をその指定に関わる文字として扱います。この方式では大文字・小文字だけで勤務を区別せず、これらの半角記号も勤務記号に使わないルールにします。(Microsoft サポート「MATCH 関数」)
C列は登録ミスの確認欄です。記号対応表!C2の式をC21までコピーすると、同じ元の記号が2件以上あれば「二重登録」、B列が空なら「統一記号なし」と表示します。
=IF(A2="","",IF(SUMPRODUCT(--EXACT($A$2:$A$21,A2))>1,"二重登録",IF(B2="","統一記号なし","")))
警告が出た行は、統一後の表を利用する前に必ず修正します。「表示結果が変わらないから問題ない」とは判断しません。
統一後の表:空白を除いてINDEX・MATCHで置き換える
「統一後」のA〜C列は元の表の店舗・社員番号・氏名を表示します。勤務記号は、統一後!D5に次の式を入れ、右へAH列まで、下へ12行目までコピーします。配布版には入力済みです。
=IF(SUBSTITUTE(SUBSTITUTE(元の表!D5," ","")," ","")="","",IFERROR(INDEX(記号対応表!$B$2:$B$21,MATCH(SUBSTITUTE(SUBSTITUTE(元の表!D5," ","")," ",""),記号対応表!$A$2:$A$21,0)),"?"&SUBSTITUTE(SUBSTITUTE(元の表!D5," ","")," ","")))
式は内側から読むと分かります。まずSUBSTITUTEで全角スペースと半角スペースを空文字に置き換えます。SUBSTITUTEは置換対象の回数を省略すると、指定した文字をすべて置き換える関数です。(Microsoft サポート「SUBSTITUTE 関数」)
この式では前後だけでなく、記号の途中にある空白も除きます。空白自体に意味を持たせた記号には、そのまま適用しないでください。また、タブや改行まで除去する式ではありません。空白を除いた結果が空なら、勤務を補わず空欄を返します。
次にMATCHの末尾の「0」で一致する元の記号の位置を探し、INDEXで同じ位置の統一記号を取り出します。MATCHは一致する最初の位置を返すため、対応表の二重登録を先に解消する必要があります。(Microsoft サポート「MATCH 関数」)(Microsoft サポート「INDEX 関数」)
例えば、佐藤の12月8日・K5の「 早」は「早」、鈴木の12月3日・F6と12月17日・T6の「遅 」は「遅」になります。空白付きの記号を対応表へ追加する作業は不要です。
対応表にない記号は「?」付きで表示する
IFERRORは数式がエラーになった場合に指定した値を返す関数です。(Microsoft サポート「IFERROR 関数」) この式では、対応表に見つからない記号を消さず、全角の「?」を先頭に付けて残します。
配布時は、高橋の12月9日・L7、田中の12月9日・L9、中村の12月19日・V12が「?研」になります。「研」を独立した記号として残すと決めたら、次の入力を行ってください。
- 「記号対応表」のA10に「研」、B10に「研」を入力する。
- 「確認」のE8にも「研」を入力する。
- 「統一後」の該当セルが「研」に変わり、未登録が0件、研が3件になることを確かめる。
対応表だけ直してE8を追加しないと、置き換えはできても集計から「研」が漏れ、「確認」の判定が「不一致」になります。新しい統一記号は、対応表と集計用の一覧をセットで更新しましょう。
統一の前後で件数を比べる
「確認」のA5:C24が統一前、E5:F10が統一後の集計欄です。統一後の表を出勤人数の集計に使う前に、ここで件数をチェックします。統一前も空白を除いて数えるため、「早」52件には空白付きの入力も含まれます。配布時の内訳は次のとおりです。
| 統一先 | 統一前の記号別件数 | 統一後の件数 |
|---|---|---|
| 早 | 早52・早番33・A3・A2 | 90 |
| 遅 | 遅22・遅番34 | 56 |
| 休 | 休49・公休50 | 99 |
| 未登録 | 研3 | 3 |
| 合計 | 登録済み245・未登録3 | 248 |
元の記号別件数を求める確認!C5の式は、C24までコピーします。A・B列は対応表の2〜21行目と同じ並びです。
=IF(A5="","",SUMPRODUCT(--EXACT(SUBSTITUTE(SUBSTITUTE(元の表!$D$5:$AH$12," ","")," ",""),A5)))
EXACTで文字列が同じかを調べ、SUMPRODUCTで一致したセルを数えます。EXACTは大文字と小文字を区別する文字列比較なので、件数確認はMATCHによる置換と同じ照合方法ではない点にも注意してください。(Microsoft サポート「EXACT 関数」) この式では比較結果を「—」で数値に変え、合計して件数にしています。
統一後のE5:E10は入力欄です。配布時はE5に「早」、E6に「遅」、E7に「休」が入り、F列がそれぞれの件数を表示します。確認!F5の式はF10までコピーします。
=IF(E5="","",SUMPRODUCT(--EXACT(統一後!$D$5:$AH$12,E5)))
未登録件数を求める確認!F14は、先頭が全角の「?」のセルを数える式です。このため、正式な統一記号の先頭には「?」を使いません。
=SUMPRODUCT(--(LEFT(統一後!$D$5:$AH$12,1)="?"))
| 「確認」のセル | 確認する内容 | 配布時の値 |
|---|---|---|
| F12 | 入力済みのセル数 | 248 |
| F13 | 対応表で置き換えた件数 | 245 |
| F14 | 未登録の件数 | 3 |
| F15 | 統一後の件数と未登録件数の合計 | 248 |
| F16 | 件数の判定 | 一致 |
| F17 | 空白が付いていた入力の件数 | 3 |
判定では、入力済みのセル数と統一後の合計、対応表で置き換えた件数と登録済み記号の集計値を、それぞれ比べます。「一致」は件数の対応が取れているという意味で、未登録がなくなったという意味ではありません。 F14の未登録と、対応表の警告も別に確認してください。
同じ記号で勤務時間が違う店舗を見つける
文字をそろえても、時間の意味がそろったとは限りません。「時間定義」のA5:D8に統一記号・開始・終了・休憩(分)の基準、A12:F19に店舗別の定義と判定を置きます。開始・終了は時刻、休憩は分数で入力し、「休」のように時間のない記号は載せません。
基準の5行目は「早・10:00・19:00・60」、6行目は「遅・11:00・20:00・60」です。店舗側には、その店舗で実際に使っている時間を入れて比べます。
| 行 | 店舗 | 記号 | 開始〜終了 | 休憩 | 判定 |
|---|---|---|---|---|---|
| 12 | 駅前店 | 早 | 10:00〜19:00 | 60分 | 一致 |
| 13 | 駅前店 | 遅 | 11:00〜20:00 | 60分 | 一致 |
| 14 | 公園通り店 | 早 | 10:00〜19:00 | 60分 | 一致 |
| 15 | 公園通り店 | 遅 | 13:00〜22:00 | 60分 | 食い違い |
時間定義!F12の式をF19までコピーします。
=IF(B12="","",IF(SUMPRODUCT(--EXACT($A$5:$A$8,B12))=0,"基準なし",IF(SUMPRODUCT(EXACT($A$5:$A$8,B12)*($B$5:$B$8=C12)*($C$5:$C$8=D12)*($D$5:$D$8=E12))>=1,"一致","食い違い")))
開始・終了・休憩がすべて基準と同じときだけ「一致」です。基準に記号がなければ「基準なし」、記号はあっても時間が異なれば「食い違い」と表示します。配布時はF15が「食い違い」で、件数を示すF21は1です。
具体的な直し方は、実際の勤務を確認して決めます。
- 時間が誤記だった場合:C15を11:00、D15を20:00へ修正します。F15が「一致」、F21が0になります。
- 実際に時間が違う場合:「記号対応表」のB6を「遅2」へ変更。「時間定義」のA7:D7に「遅2・13:00・22:00・60」を追加し、B15も「遅2」にします。「確認」のE8へ「遅2」を追加してください。
後者では早90・遅22・休99・遅2が34件になり、未登録は3件のまま、件数判定は「一致」です。時間の食い違いは0件になります。
この仕組みは元の記号だけで置換し、店舗を検索条件にしていません。他の店舗でも「遅番」を使っていれば、そちらも「遅2」になります。同じ元の記号が店舗によって違う勤務を表す場合は、店舗ごとに対応表を分けて処理する必要があります。
確認例:未登録の記号、空白付き、時間の食い違い、二重登録
操作を試すときは、次の4点を見ます。各例は配布時の状態から個別に確認してください。
| 確認例 | 操作・確認箇所 | 見る結果 |
|---|---|---|
| 未登録 | 記号対応表のA10・B10に「研」、確認E8にも「研」 | 未登録0、研3、判定「一致」 |
| 空白付き | 統一後のF6・T6・K5 | 「遅」「遅」「早」。確認F17は3 |
| 時間の食い違い | 公園通り店の時間を直すか「遅2」に分ける | 時間定義F15が「一致」、F21が0 |
| 二重登録 | 記号対応表のA10に「早番」、B10に「遅」を追加 | C5とC10が「二重登録」 |
二重登録の例では、上にある「早番→早」が優先して使われるため、統一後の表は変わりません。それでも対応表には矛盾が残っています。誤って追加した10行目のA・B列を消し、C列の警告がなくなったことを確認してから使ってください。
「研」の登録と「遅2」への分割を同じブックで行う場合は、E5:E10の空いている行へ両方を登録します。例のE8をそのまま上書きすると、先に追加した記号が集計から外れます。
統一した後の運用
元の表や対応表を直すと、再計算で統一後の表と件数に反映されます。自店の人数や日数に合わせて調整するときは、「統一後」の式だけでなく「確認」の集計範囲も変更します。配布版は8人×31日、対応表は21行目までです。統一記号を増やす場合はE5:E10の一覧、時間定義を増やす場合は基準と店舗別の照合範囲も合わせてください。
統一した結果を元の表へ戻すことも可能です。その場合は元データを別に保管し、「統一後」のD5:AH12をコピーして、戻し先へ「値」で貼り付けます。数式を残した照合用ブックも保管しておけば、どの記号をどう直したかを対応表から確認できます。
入力時のプルダウンはエクセルでシフト表を作る方法、複数ファイルの集約はシフト表を複数ファイルから集約する方法、記号からの勤務時間計算はシフト表の勤務時間を自動計算する方法、勤怠システム用コードへの変換はシフト表をエクセルからCSVに変換する手順で扱っています。それぞれ別の作業です。この記事で完成するのは、既存の勤務記号をそろえ、未登録・件数・時間定義の食い違いを確認する表です。
複数店舗のシフト管理をシステムやサービスへ移すことを検討する場合も、記号と時間の整理は設定の土台になります。シフトラでは、名称と開始・終了時刻を持つシフトパターンを登録し、パターンごとに休憩時間を分単位で設定できます(2026-09-05時点の製品情報)。これは既存Excelの記号を本稿の手順で自動統一する機能ではなく、どの勤務をまとめ、どれを分けるかは担当者が決めます。
確定した統一記号と時間定義を店舗間で共有し、毎月のシフト作成で同じ対応表を使用して照合できる状態にしておきましょう。