エクセルのシフト表で、指定した2人のペア回数を集計するには、同じ日付列の勤務記号を比較します。本稿で数えるのは、異なる2人が「同じ日・同じ対象勤務記号」で入る日数です。
たとえば後述の7日間では、2人の勤務記号がそろうのは9/1と9/3の2回。一致する日を1、それ以外の確認済みの日を0にし、1の合計と該当日を照合します。
職員が行、日付が列の既存表を残し、別シートに回数・該当日・未確認日数を表示します。元表の変更に合わせて関数で更新する方法であり、勤務の自動割当ではありません。数式を追加する場所を、3人・7日間の記入例に沿って示します。
ペア回数は「同じ日・同じ勤務記号」で数える
今回の集計対象は「早・日・遅」、対象外は「休・有休・有・明」とします。2人とも「早」なら1回、「早」と「日」なら0回。2人とも「休」でも数えません。単に両方が出勤する日数とは異なる集計です。個人の曜日別勤務日数を調べる場合は、土日の出勤回数をカウントする方法を参照してください。
予定表を参照した結果は予定回数です。実績を調べるなら、取消・交代を反映した実績表を参照してください。同じ記号でも、勤務時間の重なり、店舗、共同担当、教育担当の実績までは判断しません。
まず職場で「何を1回とするか」を決め、その定義に合わせて対象記号と期間を設定します。
既存の勤務表と集計シートを対応づける
作業前にブックを複製し、別名保存してください。勤務入力セルは残したまま、「ペア集計」シートを追加します。
以下は架空の3人・7日間の例です。元表名を「勤務表」とし、行列位置を次のように置きます。
| 行＼列 | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 3 | ID | 氏名 | 2026/9/1 | 2026/9/2 | 2026/9/3 | 2026/9/4 | 2026/9/5 | 2026/9/6 | 2026/9/7 |
| 4 | S001 | 佐藤 | 早 | 遅 | 日 | 休 | 有休 | 明 | 早 |
| 5 | S002 | 鈴木 | 早 | 日 | 日 | 休 | 有休 | 明 | 遅 |
| 6 | S003 | 佐藤 | 日 | 早 | 遅 | 早 | 日 | 早 | 休 |
S001とS003は同姓の別人です。IDは大文字の半角英数字に統一し、* ? ~を含めず、大文字・小文字だけで別IDを作らない運用にします。各IDは1行、各日付は時刻なしのExcel日付値で1列ずつ、一意に配置してください。
「ペア集計」の配置は次のとおりです。B4以降の結果欄には、後述の式を入れます。
| セル・範囲 | 入力内容・用途 |
|---|---|
| B2／B3 | 1人目のID「S001」／2人目のID「S002」 |
| B4 | ID確認 |
| B5／B6 | 1人目／2人目のID範囲内位置 |
| B7／B8／B9 | 確認できた回数／未確認日数／状況 |
| J2:J4 | 上から「早」「日」「遅」 |
| L2:L5 | 上から「休」「有休」「有」「明」 |
| C12:I12 | 日付 |
| C13:I13／C14:I14 | 1人目／2人目の勤務 |
| C15:I15／C16:I16 | 日付確認／記号確認 |
| C17:I17／C18:I18 | 日別判定／該当日 |
J列・L列のリストは、リスト内・リスト間とも重複や空欄をなくします。12行目と18行目の日付セルは表示形式をm/dに設定してください。
以下のC列に入れる式は、すべてI列まで右コピーします。 実際の月表では、実在する最後の日付列まで広げます。存在しない月末の空日付列は含めません。
2人は氏名ではなくIDで指定する
最初に、選んだIDが元表に1件ずつ存在するかを確かめます。
B4：
=IF(OR(B2="",B3=""),"2人を指定",IF(B2=B3,"別の2人を指定",IF(OR(SUMPRODUCT(--(勤務表!$A$4:$A$6=B2))<>1,SUMPRODUCT(--(勤務表!$A$4:$A$6=B3))<>1),"IDを確認","OK")))B5：
=IF($B$4="OK",MATCH($B$2,勤務表!$A$4:$A$6,0),"")B6：
=IF($B$4="OK",MATCH($B$3,勤務表!$A$4:$A$6,0),"")同一IDの二重指定は「別の2人を指定」、未登録や選択IDの重複は「IDを確認」とします。0回として処理しません。
MATCHの照合型0は完全一致した値の範囲内位置を返します。この例ではB5が1、B6が2となり、次の勤務参照に使われます。(Microsoft サポート)
日付と2人分の勤務を参照する
C12：
=IF(勤務表!C3="","",勤務表!C3)C13：
=IF($B$4<>"OK","",IF(INDEX(勤務表!C$4:C$6,$B$5)="","",INDEX(勤務表!C$4:C$6,$B$5)))C14：
=IF($B$4<>"OK","",IF(INDEX(勤務表!C$4:C$6,$B$6)="","",INDEX(勤務表!C$4:C$6,$B$6)))INDEXは指定位置の値を取り出す関数です。ここでは1日分の1列から1セルだけを参照し、行全体を返す配列出力にはしていません。(Microsoft サポート)
元表の空欄は明示的に空文字へ戻します。入力漏れを数値の0や休みとして扱わないための処理です。
日付と勤務記号の未確認を分ける
C15：日付確認
=IF(C12="","日付確認",IF(NOT(ISNUMBER(C12)),"日付確認",IF(OR(C12<1,C12<>INT(C12),COUNTIFS($C$12:$I$12,C12)<>1),"日付確認","OK")))空欄・文字列・0以下・小数部分のある日時・重複日付を「日付確認」にします。ただし、一意の日付でも別月への入力間違いや対象日の抜けは別途照合が必要です。
COUNTIFSは条件に合うセルを数える関数です。本稿では日付の重複や「未確認」の件数に使い、勤務記号の所属判定には使いません。検索条件に*や?を使うとワイルドカードとして扱われるためです。(Microsoft サポート)
C16：記号確認
=IF($B$4<>"OK","",IF(C15<>"OK",C15,IF(OR(C13="",C14=""),"未確認",IF(OR(SUMPRODUCT(--($J$2:$J$4=C13))+SUMPRODUCT(--($L$2:$L$5=C13))<>1,SUMPRODUCT(--($J$2:$J$4=C14))+SUMPRODUCT(--($L$2:$L$5=C14))<>1),"未確認","OK"))))2人の記号がそれぞれ、対象・対象外のどちらかにちょうど1回登録されているかを確認します。未知の記号だけでなく、両リストへ重複登録した記号も「未確認」です。ただし、使われていない記号の重複までは検出しないので、設定時にリスト全体を確認してください。
--は比較結果を1・0に変えるための記述です。ここでのSUMPRODUCTは記号を=で比較し、小さな登録範囲だけを数えます。複数配列を使う場合は大きさをそろえ、性能面から全列参照を避けます。(Microsoft サポート) (learn.microsoft.com)
一致日の1を合計し、該当日を照合する
C17：日別判定
=IF(C16<>"OK",C16,IF(AND(C13=C14,SUMPRODUCT(--($J$2:$J$4=C13))=1),1,0))C18：該当日
=IF(C17=1,C12,"")17行目は、同じ対象勤務なら1、確認できた不一致・対象外なら0です。18行目には一致日のみを表示します。日付を1セルに連結したり、別の縦表へ詰め直したりする構成ではありません。
続いて、未確認日数、状況、回数の順に式を入れます。
B8：
=IF($B$4="OK",COUNTIFS($C$16:$I$16,"未確認"),"")B9：
=IF($B$4<>"OK",$B$4,IF(COUNTIFS($C$15:$I$15,"日付確認")>0,"日付を確認",IF($B$8>0,"未確認の日あり","確認済み")))B7：
=IF(OR($B$9="確認済み",$B$9="未確認の日あり"),SUM($C$17:$I$17),"")SUMは参照範囲の文字列を除いて数値を合計します。そのためB7だけでは未確認の有無が分かりません。必ずB8・B9と併記してください。(Microsoft サポート)
架空例の初期状態で照合する結果は、2回、該当日9/1・9/3、未確認0日、状況「確認済み」です。
| 行＼列 | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|
| 17：日別判定 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
| 18：該当日 | 9/1 | 9/3 |
0回と「未確認の日あり」を区別する
両方が「休・有休・有・明」のいずれかなら、その日は確認できた0です。一方、片方でも空欄なら「未確認」。2人とも空欄でも、未確認は2件ではなく1日と数えます。
未登録の「研修」が2人で一致していても数えません。「早*」「早の後ろに空白」「数値0」も、この登録リストでは未確認です。「早番」を「早」へ自動変換する処理もありません。
記号の未確認があればB7には確認できた一致分を残し、B9は「未確認の日あり」とします。ID・日付に不備があればB7は空欄です。B8は記号の未確認日数なので、日付の不備は15行目とB9で確認します。
勤務を1日変えて回数と日付を確かめる
複製したブックで、次の順に操作してください。表は架空例の照合用の期待値です。
| 元表の操作 | B7の回数 | 18行目の該当日 | B8／B9 |
|---|---|---|---|
| D5を「日」から「遅」へ | 3 | 9/1・9/2・9/3 | 0／確認済み |
| D5を「日」へ戻す | 2 | 9/1・9/3 | 0／確認済み |
| I4を空欄にする | 2 | 9/1・9/3 | 1／未確認の日あり |
| I4を「早」へ戻す | 2 | 9/1・9/3 | 0／確認済み |
さらに、初期状態からE4の「日」を「休」に変えれば、期待値は1回・9/1のみです。確認後は元に戻します。
自動更新には、Excelの計算方法を「自動」に設定します。変更が反映されないときは、計算方法が「手動」になっていないか確認してください。(Microsoft サポート)
別のペアや翌月へ広げるときの確認
別のペアはB2・B3のIDを変更して調べます。初期表でS002×S001へ逆に指定しても2回、S001×S003なら0回・未確認0日が期待値です。
元表を並べ替えるときは、ID・氏名・勤務データを行単位で一緒に動かします。ID列だけの並べ替えは避けてください。
職員の最終行と実在する最終日まで参照をそろえる
実際の月表へ移す際は、次の箇所をまとめて変更します。
- 職員範囲： B4・B5・B6のID範囲と、13・14行目のINDEX参照範囲の開始行・最終行をそろえる。
- 日付範囲： C15のCOUNTIFSの列末尾を直してから最終日まで右コピーし、B7・B8・B9のSUM／COUNTIFSも同じ列末尾へそろえる。
- 記号リスト： 対象記号や対象外記号を追加するときは、C16・C17のJ列／L列の参照末尾も実際の登録範囲へそろえてから右コピーする。
- 翌月の参照先： 元表のシート名、期間の開始・終了、列数、日付の抜けを照合する。
9月全体なら9/30の列までが対象です。31日固定の空日付列を含めると、「日付確認」になります。実在する対象日の勤務欄が空なのと、対象期間外の列は分けて扱ってください。
複製ブックで、追加した最終職員を選び、最終日に対象記号の一致を1つ作って、回数と根拠日に反映されるかも確かめます。元表や数式にエラーがあれば、0へ置き換えず参照先と入力を修正します。
ペア回数を次のシフト調整に使うとき
集計結果は、次の配置を検討する材料です。回数が少ないだけで教育不足、多いだけで不公平とは判断できません。同じ店舗・担当・時間を表す記号なのかを、職場の運用と照合してください。教育担当を決めるときは、新人とベテランのシフトを組む配置例で、指導時間と営業の両立も確認してください。全職員の組合せ一覧や月をまたぐ統合は、本稿では扱いません。
毎月のシフト管理で組合せ条件を割当に反映する作業まで見直すなら、シフトラでは、自然な日本語のルールやシフト希望をもとにしたシフト案生成と、確定シフトのExcel出力に対応しています（2026-09-22時点）。(シフトラ)
これは本稿のペア回数集計とは別の機能です。出力後の回数照合や、シフト案の最終確認は管理者が行います。まずは既存表で「回数・根拠日・未確認」が一致する状態を作り、その結果を次の配置調整に使ってください。