エクセルでシフト希望を時間帯別に集計するには、回収した希望を「氏名・日付・可否・開始・終了」の1件1行にそろえます。そのうえで、30分の時間帯全体に勤務できる行をCOUNTIFSで数え、TEXTJOINで候補者の名前を並べます。
紙やLINEの回答をめくりながら人数を数えている店長・現場の管理者に向けて、入力例付きの無料のExcelと数式を用意しました。この記事では、入力表の作り方、集計の式、確認の流れの順に解説します。希望を集める段階の表は、シフト希望表のエクセルテンプレートで配布しています。集計するのは、希望段階の勤務できる人数です。「未定」と答えた人、その日の希望が入力されていない人も分けて確認できます。
エクセルでシフト希望を時間帯別に集計するには
配布Excelには「希望入力」「スタッフ」「時間帯別集計」「使い方」の4シートがあり、マクロは使用していません。登録は不要です。
使う順番は次のとおりです。
- 「スタッフ」のA2:A9に対象者8人の氏名を入れる。
- 「希望入力」のA〜E列に、回収した希望を1件1行で入力する。
- F〜H列の確認表示を見て、重なりや入力ミスを直す。
- 「時間帯別集計」のB3に集計日を入れ、E6:E25に必要人数を入力する。
- 可の人数・候補者名・未定・未回答・過不足を確認する。
黄色のセルが入力欄、灰色のセルが数式です。配布時には確認用の誤入力も入っています。シフト管理システムを導入していなくても、Excelだけで運用できます。最初は例のまま表示を確かめ、自分の店で使う際に入力例を置き換えてください。
確定シフトの時間別人数とは別の作業
この表が答えるのは「その時間帯に誰を勤務候補にできるか」です。候補者が4人いても、4人全員の出勤が決まったわけではありません。数式による自動集計・名前の自動表示までが対象で、勤務者の自動割当は行いません。
確定後は、配置した人から休憩中の人を除いて勤務中の人数を数えます。その作業は、Excelのシフト表で時間別人数を集計する方法で扱っています。
希望入力表は1件1行にそろえる
「希望入力」はA列が氏名、B列が日付、C列が可否、D列が開始、E列が終了です。可否は「可」「未定」から選びます。氏名はスタッフ一覧と同じ表記にし、日付は「2026/12/12」、時刻は「9:30」のように入力してください。集計の前に入力内容を確認する流れにすると、転記ミスの発生を防ぎやすくなります。
同じ人が1日に二つの時間帯を希望した場合は、行を分けます。伊藤の「10:00〜13:00」と「17:00〜20:30」を1行の「10:00〜20:30」にまとめると、勤務できない13:00〜17:00まで候補に入ってしまうためです。
入力・集計の対象は2〜200行です。1か月分をまとめて入れても、日付ごとに集計できます。200行を超えて使う場合は、人数・名前・確認列の参照範囲をそろえて広げます。スタッフを増やすときも、未回答・回答済み人数の式で使う「スタッフ!$A$2:$A$9」を広げる必要があります。
架空例:12月12日(土)の8人の希望
例は、営業10:00〜20:00、従業員8人の架空の駅前の雑貨店です。「スタッフ」のA2:A9には、佐藤、鈴木、高橋、田中、伊藤、渡辺、山本、中村の順に入力します。
次の12件を「希望入力」の2〜13行目に入れます。12月12日分は、回答した7人の9行です。伊藤の分割希望と中村の二重入力があるため、人数と行数が一致しません。残る3行は翌日分の確認例です。
| 行 | 氏名 | 日付 | 可否 | 開始 | 終了 |
|---|---|---|---|---|---|
| 2 | 佐藤 | 2026/12/12 | 可 | 9:30 | 18:30 |
| 3 | 鈴木 | 2026/12/12 | 可 | 11:00 | 20:30 |
| 4 | 高橋 | 2026/12/12 | 可 | 10:00 | 16:00 |
| 5 | 田中 | 2026/12/12 | 可 | 12:00 | 20:00 |
| 6 | 伊藤 | 2026/12/12 | 可 | 10:00 | 13:00 |
| 7 | 伊藤 | 2026/12/12 | 可 | 17:00 | 20:30 |
| 8 | 渡辺 | 2026/12/12 | 未定 | 15:00 | 20:30 |
| 9 | 中村 | 2026/12/12 | 可 | 10:00 | 14:00 |
| 10 | 中村 | 2026/12/12 | 可 | 10:00 | 12:00 |
| 11 | 鈴木 | 2026/12/13 | 可 | 20:00 | 11:00 |
| 12 | 高橋 | ’2026/12/13 | 可 | 10:00 | 16:00 |
| 13 | 田中 | 2026/12/13 | 可 | 12:00 | 20:00 |
10行目は重複、11行目は開始と終了の逆転、12行目は日付が文字列になった例です。高橋の日付の先頭の「‘」は、文字列入力を再現するための記号で、通常の入力では付けません。山本は未回答のため、この表には行がありません。
COUNTIFSで人数、TEXTJOINで候補者名を出す
「時間帯別集計」のB3に「2026/12/12」を入れます。A列は枠の開始、B列は枠の終了で、6〜25行目が10:00〜20:00の20枠です。A6は =$D$3、B6は =A6+TIME(0,30,0)。A7以降も30分ずつ進む式が入っています。
C6:可の人数 COUNTIFSは、指定した複数の条件をすべて満たす行を数えるために使います。(Microsoft サポート「COUNTIFS 関数」)
=COUNTIFS(希望入力!$B$2:$B$200,$B$3,希望入力!$C$2:$C$200,"可",希望入力!$D$2:$D$200,"<="&A6,希望入力!$E$2:$E$200,">="&B6)
条件は「日付がB3と同じ」「可否が可」「希望の開始が枠の開始以前」「希望の終了が枠の終了以降」の四つです。開始時点だけでなく、枠全体をカバーするかを判定します。
伊藤は12:30〜13:00には含まれますが、13:00〜13:30には含まれません。17:00〜17:30には再び含まれます。終了時刻と枠の終了が同じ場合を含めるため、最後の条件は >= です。
G6:勤務できる候補者の名前 TEXTJOINは、文字列を指定した区切り記号でつなぐ関数です。TRUEを指定すると空のセルを無視します。(Microsoft サポート「TEXTJOIN 関数」)
=TEXTJOIN("、",TRUE,IF((希望入力!$B$2:$B$200=$B$3)*(希望入力!$C$2:$C$200="可")*(希望入力!$D$2:$D$200<=A6)*(希望入力!$E$2:$E$200>=B6),希望入力!$A$2:$A$200,""))
IFで同じ四条件に合う氏名だけを残し、「、」でつなぎます。配布Excelでは配列数式として設定済みです。C6とG6を下へコピーすると、集計日と入力範囲は固定されたまま、枠の開始・終了だけが次の行に変わります。
次は、中村の二重入力を修正した後の20枠です。配布時のままでは10:00〜12:00の最初の4枠が「5・5・6・6」になるため、後述の手順で直してから照合してください。「未定」欄の「—」は該当者なしを示します。
| 時間帯 | 可の人数 | 候補者名 | 未定(人数・名前) | 必要人数 | 過不足 |
|---|---|---|---|---|---|
| 10:00〜10:30 | 4 | 佐藤、高橋、伊藤、中村 | 0・— | 2 | +2 |
| 10:30〜11:00 | 4 | 佐藤、高橋、伊藤、中村 | 0・— | 2 | +2 |
| 11:00〜11:30 | 5 | 佐藤、鈴木、高橋、伊藤、中村 | 0・— | 2 | +3 |
| 11:30〜12:00 | 5 | 佐藤、鈴木、高橋、伊藤、中村 | 0・— | 2 | +3 |
| 12:00〜12:30 | 6 | 佐藤、鈴木、高橋、田中、伊藤、中村 | 0・— | 3 | +3 |
| 12:30〜13:00 | 6 | 佐藤、鈴木、高橋、田中、伊藤、中村 | 0・— | 3 | +3 |
| 13:00〜13:30 | 5 | 佐藤、鈴木、高橋、田中、中村 | 0・— | 3 | +2 |
| 13:30〜14:00 | 5 | 佐藤、鈴木、高橋、田中、中村 | 0・— | 3 | +2 |
| 14:00〜14:30 | 4 | 佐藤、鈴木、高橋、田中 | 0・— | 3 | +1 |
| 14:30〜15:00 | 4 | 佐藤、鈴木、高橋、田中 | 0・— | 3 | +1 |
| 15:00〜15:30 | 4 | 佐藤、鈴木、高橋、田中 | 1・渡辺 | 3 | +1 |
| 15:30〜16:00 | 4 | 佐藤、鈴木、高橋、田中 | 1・渡辺 | 3 | +1 |
| 16:00〜16:30 | 3 | 佐藤、鈴木、田中 | 1・渡辺 | 3 | 0 |
| 16:30〜17:00 | 3 | 佐藤、鈴木、田中 | 1・渡辺 | 3 | 0 |
| 17:00〜17:30 | 4 | 佐藤、鈴木、田中、伊藤 | 1・渡辺 | 3 | +1 |
| 17:30〜18:00 | 4 | 佐藤、鈴木、田中、伊藤 | 1・渡辺 | 3 | +1 |
| 18:00〜18:30 | 4 | 佐藤、鈴木、田中、伊藤 | 1・渡辺 | 2 | +2 |
| 18:30〜19:00 | 3 | 鈴木、田中、伊藤 | 1・渡辺 | 2 | +1 |
| 19:00〜19:30 | 3 | 鈴木、田中、伊藤 | 1・渡辺 | 2 | +1 |
| 19:30〜20:00 | 3 | 鈴木、田中、伊藤 | 1・渡辺 | 2 | +1 |
未定と未回答を分けて確認する
D列は未定の人数、H列は未定の人の名前です。D6はC6の式、H6はG6の式の「可」を「未定」に変えています。この例では15:00以降の10枠に渡辺が1人と表示されます。可の人数には加えず、本人に確認して勤務できると決まったら、可否欄を「可」に変更します。
未回答の名前はC27に、次の配列数式で出します。
=TEXTJOIN("、",TRUE,IF(COUNTIFS(希望入力!$A$2:$A$200,スタッフ!$A$2:$A$9,希望入力!$B$2:$B$200,$B$3)=0,スタッフ!$A$2:$A$9,""))
スタッフ一覧の各氏名について、その日の入力が0件なら名前を残す式です。例ではC27が「山本」、C28の回答済み人数が「7」になります。渡辺は「未定」という回答を入力しているので、未回答には入りません。
ただし、この「未回答」は厳密にはその日の入力がない人です。配布Excelでは不可・休みの日を入力しないため、休みと回答済みの人もここに出ます。実務では原票や回収記録(回収管理表)と照合し、未提出なのか、休みなのか、転記漏れなのかを確かめてから連絡してください。作成済みのシフト表と希望休の食い違いを見つける作業は、シフト表で希望休をチェックする方法で扱っています。
重なり・開始と終了の逆転・文字列の日付を直す
確認列は異常を表示する仕組みで、該当行を自動で集計から除外するものではありません。直した後は上書き保存し、集計表の数値が更新されたことを見てから使います。配布時の例では、中村の2行、鈴木の1行、高橋の1行に確認表示が出ます。
F2:同じ人・同じ日の時間帯の重なり
=IF(AND(A2<>"",D2<>"",E2<>""),IF(SUMPRODUCT(($A$2:$A$200=A2)*($B$2:$B$200=B2)*($D$2:$D$200<E2)*($E$2:$E$200>D2))>1,"重なり",""),"")
SUMPRODUCTで「氏名と日付が同じ、かつ時間が重なる」行を数え、自分自身を含めて2行以上なら「重なり」と表示します。伊藤の二つの希望は離れた時間帯なので、この確認には該当しません。
中村は9行目の10:00〜14:00を残し、10行目のA〜E列を消去します。行全体を削除する必要はなく、F〜H列の確認式は残してください。10:00〜10:30の人数は5人から4人になり、候補者名も「佐藤、高橋、伊藤、中村」に変わります。重なりの表示は両方の行から消えます。
G2:開始が終了より後になっていないか
=IF(AND(D2<>"",E2<>"",D2>E2),"開始>終了","")
鈴木の12月13日は20:00開始・11:00終了なので、G11に「開始>終了」と出ます。この例は逆転入力のため、D11を11:00、E11を20:00に直します。実データでは原票を確認してから訂正してください。本表は同日内の希望が対象で、日をまたぐ希望を自動補正する設計ではありません。
H2:日付が文字列になっていないか
=IF(AND(B2<>"",NOT(ISNUMBER(B2))),"日付が文字列","")
ISNUMBERは値が数値かを判定し、文字列を数値へ変換して判定することはありません。(Microsoft サポート「IS 関数」)
高橋のB12は見た目が日付でも文字列です。セルを「標準」にして入力を消し、先頭の「‘」を付けずに「2026/12/13」と再入力します。H12の表示が消えたことまで確かめてください。日付の見た目だけを変えて済ませないのがポイントです。
条件付き書式により、F〜H列に確認表示がある入力行は赤系の色になります。この三つの確認だけでは、氏名の誤り、開始と終了が同じ入力、時刻の文字列まで検出できません。空欄や原票との食い違いも入力時に確認します。
必要人数と比べて過不足を読む
「時間帯別集計」のE列には、10:00〜12:00の各枠に2人、12:00〜18:00に3人、18:00〜20:00に2人を入れます。これは架空の店で設定した必要人数です。F6の式は次のとおりで、未定の人数は足していません。
=IF(E6="","",C6-E6)
正の値は必要人数を上回る候補者数、0は候補者数と必要人数が同じ、負の値は不足人数を示します。必要人数は曜日や時間帯の状況に合わせて店舗ごとに決めます。F6:F25には、負の値を赤系で表示する条件付き書式が設定されています。E列を空欄にすると過不足も空欄になるため、空欄を「不足なし」と読まないでください。
例では16:00〜17:00の2枠が過不足0です。候補者は佐藤・鈴木・田中の3人で、渡辺は未定のまま。候補者数では足りていても、休憩や担当業務を含めた配置が成立したことまでは示しません。候補が多い時間帯より、選択の余地がない時間帯から勤務者を検討します。
Excelのバージョンとスプレッドシートの対応を確認する
2026-11-09時点で、TEXTJOINの対応対象にはExcel 2019・2021・2024とMicrosoft 365が含まれます。名前の一覧まで使う場合は、TEXTJOINに対応する環境が前提です。(Microsoft サポート「TEXTJOIN 関数」)
本文のTEXTJOIN式を手入力・編集する場合、Windows版Excel 2019ではCtrl+Shift+Enterで配列数式として確定します。動的配列に対応したMicrosoft 365ではEnterで確定できます。数式を囲む波かっこを自分で打つ必要はありません。(Microsoft サポート「配列数式のガイドラインと例」)
TEXTJOIN自体に未対応の版では、確定キーを変えても名前の式は動きません。人数だけ出る場合も、氏名欄を未確認のまま使わず、関数の対応と配列数式の入力方法を確認してください。
GoogleスプレッドシートにもTEXTJOINがあります(2026-11-09時点)。(Google ドキュメント エディタ ヘルプ「TEXTJOIN」)
配布Excelの検算は、openpyxlで作ったファイルをLibreOffice 26.2で再計算し、本文の人数・名前と一致することを確かめたものです。Excel各版やGoogleスプレッドシートで、このファイルの全機能を実機確認したものではありません。別の環境で開いた際は、まず中村の重複を消して4人になること、渡辺と山本が別々に表示されることを確かめてください。
候補者から勤務者を決め、確定シフトへ進む
候補者が出たら、必要人数、担当できる仕事、勤務時間の上限などの勤務条件、休憩を照らし合わせて、誰を入れるかを決めます。希望を反映した勤務表を作る基本の手順は休み希望を反映する無料テンプレートで扱っています。希望表からの転記と勤務者の決定は担当者に残る作業です。この表では人件費の集計も行いません。
希望収集から確定までのシフト管理をまとめたい場合、シフトラには、専用Webリンクで集めた希望をもとに、日別・担当別に希望から見た勤務可能人数を確認する機能と、提出済み・未提出の一覧があります(2026-09-05時点の製品情報。利用条件は設定・権限によります)。30分ごとの候補者の名前を一覧にする機能ではないため、その確認には本記事の配布Excelを使います。日別の人数確認で足りるか、今回のように30分ごとの候補者名まで必要かを整理して検討してください。
まとめとして、まずは集計日を入れ、確認表示を直し、人数と名前がそろうところまで進めましょう。その結果をもとに勤務者を選び、確定したシフトで休憩を含む時間別人数を確認してください。