時間帯ごとの必要人数と実際に配置された人数を見比べられるシフト表を作ると、忙しい時間の人手不足や、反対に配置が多すぎる時間を早めに把握できます。
エクセルでは、勤務開始時刻と終了時刻を入力し、時間帯別の列を用意して数式を設定するだけで、スタッフごとの勤務状況を自動集計できます。
とくに店舗、コールセンター、介護現場、イベント運営などでは、一日合計の人数だけではなく、各時間帯に何人いるかを確認することが重要です。
時間帯別シフト表を作る基本の流れ
・時間帯を列見出しとして並べる
・氏名、開始時刻、終了時刻を入力する
・勤務中なら人数を数える数式を設定する
・必要人数との差を表示して不足時間を確認する
この記事では、1行目に見出しが入っているサンプルデータを使い、時間帯別に人数を管理するシフト表の作り方を解説します。
時間帯別シフト表の作成手順
それではまず、時間帯別に人数を確認できるシフト表の土台について解説していきます。
| 氏名 | 開始 | 終了 | 09時 | 10時 | 11時 | 12時 | 13時 | 14時 | 15時 | 16時 |
|---|---|---|---|---|---|---|---|---|---|---|
| 田中 | 09:00 | 15:00 | ||||||||
| 佐藤 | 10:00 | 18:00 | ||||||||
| 鈴木 | 09:00 | 17:00 |
この表ではA列に氏名、B列に勤務開始時刻、C列に勤務終了時刻を入力し、D列以降に時間帯を配置します。
時間帯の見出しは文字列ではなく、時刻として入力しておくことが集計の精度を保つポイントです。
時間帯の見出し設定
まずはD1セルに09:00、E1セルに10:00のように入力し、営業時間の終わりまで1時間単位で横方向へ並べます。
D1セルへ09:00を入力した後、E1セルには次の数式を入力すると、1時間後の時刻を自動で表示できます。
=D1+TIME(1,0,0)
TIME関数は時、分、秒を時刻として作る関数であり、TIME(1,0,0)は1時間を表します。
数式を右方向へコピーすれば、10:00、11:00、12:00というように必要な時間帯を連続して作成できます。
表示形式はホームタブの表示形式から時刻を選ぶか、ユーザー定義でh:mmにすると見やすくなります。
営業時間が30分単位なら、TIME(0,30,0)を加算します。
15分単位で管理したい場合はTIME(0,15,0)を使い、列数が増えることを踏まえて横幅を調整しましょう。
スタッフ情報と勤務時間の入力
A2セル以下にはスタッフ名を入力し、B列には開始時刻、C列には終了時刻を入力します。
たとえば田中さんが9時から15時まで勤務する場合、B2に09:00、C2に15:00と入力します。
ここで09:00を文字として入力すると比較用の数式が正しく判定できない場合があるため、セルの表示形式を時刻に設定してください。
開始時刻と終了時刻は、数値として保持された時刻データであることが大切です。
休憩時間も管理したい場合は、開始と終了だけでなく、休憩開始列と休憩終了列を追加します。
ただし最初は勤務中かどうかを判定する基本表を完成させ、その後に休憩や職種別人数の条件を追加すると、数式の間違いを見つけやすくなります。
表の見やすさを整える設定
時間帯別の人数管理では、入力欄と自動計算欄を色で分けると、担当者が迷わず更新できます。
氏名、開始、終了の入力セルは薄い黄色、数式が入る時間帯セルは薄い青色などにすると、役割の違いがひと目で伝わります。
時間帯の列幅は、09時のような表示なら8から10程度にそろえると横スクロール時にも確認しやすくなります。
ウィンドウ枠の固定を使って1行目とA列を固定すれば、下へ人数を追加しても時刻と氏名を見失いにくくなります。
データが増える現場では、テーブルとして書式設定する方法も便利です。
フィルターで特定の部署や担当業務だけを絞り込みながら、時間帯ごとの勤務状況を確認できるようになります。
【操作のポイント】開始時刻、終了時刻、時間帯見出しはすべて時刻形式に統一し、入力欄と数式欄の色を分けておきましょう。
勤務中人数を表示する数式
続いては、各スタッフが時間帯に勤務しているかを判定する数式を確認していきます。
| 氏名 | 開始 | 終了 | 09時 | 10時 | 11時 | 12時 | 13時 |
|---|---|---|---|---|---|---|---|
| 田中 | 09:00 | 15:00 | 1 | 1 | 1 | 1 | 1 |
| 佐藤 | 10:00 | 18:00 | 0 | 1 | 1 | 1 | 1 |
| 鈴木 | 09:00 | 17:00 | 1 | 1 | 1 | 1 | 1 |
時間帯セルに1または0を表示できれば、列ごとの合計からその時間に勤務している人数を求められます。
IF関数とAND関数による勤務判定
D2セルには、開始時刻がD1以下であり、終了時刻がD1より後である場合に1を表示する数式を入力します。
=IF(AND($B2<=D$1,$C2>D$1),1,0)
この数式では、$B2の開始時刻とD$1の時間帯見出しを比較しています。
$B2は列Bだけを固定する複合参照です。
右方向へコピーしても開始時刻の参照列がB列のままになり、下方向へコピーするとスタッフごとの行番号だけが変わります。
D$1は1行目だけを固定する複合参照です。
下方向へコピーしても時間帯見出しは1行目を参照し、右方向へコピーするとE1、F1のように時間帯だけが切り替わります。
絶対参照と相対参照を組み合わせることで、1つの数式を表全体に正しくコピーできます。
AND関数の中では、開始時刻が対象時間以前か、終了時刻が対象時間より後かという2つの条件を判定します。
両方が成り立つときだけIF関数が1を返し、それ以外は0を返す仕組みです。
数式をオートフィルでコピーする操作
D2セルに数式を入力してEnterキーを押し、1が表示されることを確認します。
次にD2セルの右下にある小さな四角形のフィルハンドルへマウスポインターを合わせます。
ポインターが黒い十字に変わったら、必要な時間帯の最終列まで右へドラッグします。
その後、入力済みの時間帯範囲を選択し、最終スタッフ行まで下へドラッグすれば全員分の判定が入ります。
表の途中に空白行がある場合、ダブルクリックによる自動コピーは途中で止まることがあります。
人数が多いシフト表では、コピー範囲を選択してCtrlキーとDキーで下方向へ埋める操作も役立ちます。
コピー後に、開始が10時のスタッフの09時欄が0、10時欄が1になっているかを確認してください。
勤務終了が15時なら15時欄は0になるため、終了時刻を含めるかどうかの社内ルールもあらかじめ決めておきましょう。
休憩時間を除外する応用数式
休憩時間も人数から除外したい場合は、D列より前に休憩開始時刻をD列、休憩終了時刻をE列として追加し、時間帯列をF列から始めます。
勤務中であり、かつ休憩中ではない場合に1を返すには、AND関数とOR関数を組み合わせます。
=IF(AND($B2<=F$1,$C2>F$1,OR($D2>F$1,$E2<=F$1)),1,0)
OR関数の部分は、休憩開始時刻が対象時間より後、または休憩終了時刻が対象時間以前であることを確認しています。
つまり対象時間が休憩の外側にある場合だけ、勤務人数として数える考え方です。
休憩開始と終了が未入力のスタッフがいる運用では、空白セルへの対応を加えた数式が必要です。
休憩を取らない勤務も混在する場合は、空白なら通常勤務として扱う条件を先に設計してから数式を入力しましょう。
【操作のポイント】最初は休憩を除かない基本数式で判定を確認し、運用が固まってから休憩条件を追加すると修正しやすくなります。
時間帯ごとの人数集計
続いては、スタッフ別の勤務判定を合計して時間帯ごとの人数を表示する方法を確認していきます。
| 区分 | 09時 | 10時 | 11時 | 12時 | 13時 | 14時 | 15時 |
|---|---|---|---|---|---|---|---|
| 勤務人数 | 2 | 3 | 3 | 3 | 3 | 2 | 1 |
| 必要人数 | 2 | 3 | 4 | 4 | 3 | 2 | 2 |
| 過不足 | 0 | 0 | -1 | -1 | 0 | 0 | -1 |
個別の勤務判定が1と0で表示されていれば、SUM関数だけで時間帯別の配置人数を求められます。
SUM関数で人数を合計する設定
スタッフ別のデータが2行目から20行目まであり、D列が09時の判定列である場合、D21セルに次の数式を入力します。
=SUM(D2:D20)
SUM関数はD2からD20までの1と0を合計するため、09時に勤務している人数が表示されます。
数式を右へコピーすれば、10時、11時、12時の各列も同じ考え方で集計されます。
スタッフの追加が頻繁にある場合は、あらかじめ余裕を持った範囲を指定するか、テーブル機能を利用して集計対象を自動拡張させると便利です。
時間帯別の人数は、スタッフごとの勤務表を縦に合計するだけで可視化できます。
集計行には太い上罫線を引き、薄い緑などで塗りつぶすと、入力データと結果の区別がつきやすくなります。
必要人数と過不足の比較
勤務人数の下に必要人数の行を作り、各時間帯に必要な人数を手入力します。
たとえば昼食対応で11時から12時に4人必要なら、該当する必要人数セルへ4を入力します。
過不足の行では、勤務人数から必要人数を引く数式を設定します。
=D21-D22
結果が0なら必要人数を満たしており、正の数なら余裕があり、負の数なら人員不足です。
過不足を別行に表示すると、シフトの問題点を数秒で見つけられます。
不足が出た時間については、開始時刻を前倒しできる人、終了時刻を延ばせる人、短時間勤務を追加できる人を検討します。
単純に合計人数を増やすのではなく、どの時間に何人足りないかを起点に調整することが、無駄の少ない人員配置につながります。
条件付き書式による不足時間の強調
過不足の数値が入った範囲を選択し、ホームタブの条件付き書式からセルの強調表示ルールを設定します。
セルの値が0より小さい場合に赤い塗りつぶし、0より大きい場合に淡い青や緑の塗りつぶしを設定すると、状況を直感的に確認できます。
負の値だけを赤くする場合は、新しいルールからセルの値が次の値より小さいを選び、0を指定します。
数字を確認しなくても不足時間が目に入るため、店長や管理者との打ち合わせにも使いやすい表になります。
色だけに依存せず、セル内に不足人数がマイナス表示される状態も保つと、印刷物や白黒表示でも内容を読み取れます。
必要人数は曜日や繁忙期で変わるため、日付ごとのシートへコピーする場合は、必要人数の行を忘れずに更新しましょう。
【操作のポイント】勤務人数、必要人数、過不足を縦に並べ、負の過不足だけを赤くすると、確認すべき時間帯が明確になります。
日付別シフトへの展開方法
続いては、完成した時間帯別の表を日別や週別の勤務管理へ展開する方法を確認していきます。
| 日付 | 氏名 | 開始 | 終了 | 09時 | 10時 | 11時 | 12時 |
|---|---|---|---|---|---|---|---|
| 月曜日 | 田中 | 09:00 | 15:00 | 1 | 1 | 1 | 1 |
| 月曜日 | 佐藤 | 10:00 | 18:00 | 0 | 1 | 1 | 1 |
| 火曜日 | 田中 | 12:00 | 18:00 | 0 | 0 | 0 | 1 |
曜日別または日付別に人数の傾向を把握したいときは、シフト情報へ日付列を加える方法が有効です。
日付列を追加する考え方
A列を日付、B列を氏名、C列を開始時刻、D列を終了時刻とし、E列以降を時間帯の判定列にします。
同じスタッフが複数日に勤務する場合でも、1勤務につき1行として入力すれば問題ありません。
日付を入力する列は、2026/9/1のような日付データとして管理します。
曜日だけを表示したい場合でも、実際の入力値は日付にしておくと、月ごとの集計や祝日の確認へ発展させやすくなります。
日付を文字ではなく日付データとして保存すると、並べ替えや集計の自由度が高まります。
曜日を別列へ表示するには、TEXT関数で日付を参照する方法が使えます。
=TEXT(A2,”aaa”)
この数式では、A2の日付から月、火、水のような曜日を表示できます。
同じ様式を複製する運用
一日分のシフト表をテンプレートとして完成させたら、シートを右クリックして移動またはコピーを選び、コピーを作成します。
新しいシート名を日付や曜日に変更し、スタッフ名と勤務時間だけを入れ替える運用なら、数式や条件付き書式を毎回作る必要がありません。
月間で31枚のシートを作る場合は、各シートのレイアウトを完全にそろえることが大切です。
列の位置や集計行の位置がずれると、後から月次集計を作る際に参照ミスが起こりやすくなります。
テンプレートには、誰が入力しても迷わないよう、開始時刻と終了時刻の入力例をコメントや別シートに残しておくと安心です。
不要な数式セルを手入力で上書きしないよう、必要に応じてシート保護も検討しましょう。
入力規則による時刻ミスの防止
開始時刻と終了時刻の入力列にデータの入力規則を設定すると、誤った時刻や文字の入力を減らせます。
データタブのデータの入力規則で時刻を選び、開始時刻を08:00、終了時刻を22:00のように設定します。
現場の営業時間に合わせて範囲を指定すれば、深夜の誤入力や桁の打ち間違いを見つけやすくなります。
30分単位で勤務時間を統一する場合は、時刻一覧を別シートに作り、リスト形式の入力規則にする方法もあります。
入力規則は完全な防止策ではありませんが、シフト表の品質を入力段階からそろえるための実用的な仕組みです。
勤務時間が翌日にまたがる夜勤は、通常の開始と終了比較では扱いが変わります。
夜勤を含む場合は、日勤用の表と分けるか、終了時刻が開始時刻より小さい場合の条件を追加して運用しましょう。
【操作のポイント】日付を追加しても、時間帯の列構成と集計行の位置は毎日そろえ、テンプレートを複製して使うと管理が安定します。
シフト表の見やすさを高める設定
続いては、時間帯別の人数をより読み取りやすくする表示設定を確認していきます。
| 区分 | 09時 | 10時 | 11時 | 12時 | 13時 | 14時 |
|---|---|---|---|---|---|---|
| 勤務人数 | 2 | 3 | 3 | 3 | 3 | 2 |
| 必要人数 | 2 | 3 | 4 | 4 | 3 | 2 |
| 過不足 | 0 | 0 | -1 | -1 | 0 | 0 |
数式が正しくても、表が読みにくいとシフト調整の判断に時間がかかります。
時間帯ごとの塗り分け
開店直後、昼の繁忙時間、夕方の繁忙時間など、重要な時間帯には見出しセルの色を変えると視線を誘導できます。
たとえば11時から13時を薄いオレンジ、17時から19時を薄い青にすることで、必要人数が変動しやすい時間を目立たせられます。
ただし色を増やしすぎると、かえって過不足の赤色が見えにくくなります。
基本色は少なく保ち、不足の警告色を最も強く見せる設計にすることが重要です。
罫線はすべてを太線にするのではなく、時間帯の境目や集計行だけを太くすると、表の構造が整います。
印刷する場合は、ページレイアウトで横向きを選び、すべての列を1ページに収める設定も確認しましょう。
詳細イメージ図による数式確認
ここでは、勤務判定の数式を入力して右方向へオートフィルする画面イメージを確認していきます。
赤枠のD2セルへ数式を入力した後、フィルハンドルを右へ引っ張ると、各列の見出し時刻を参照する数式へ自動的に変化します。
数式バーに表示される参照を確認し、開始時刻のB列と終了時刻のC列が固定されていることを確かめましょう。
時刻の列だけがD、E、Fと変わる状態なら、複数時間帯へ正しく展開できます。
勤務区分ごとの人数管理
レジ、調理、接客、事務など、業務ごとに必要人数が異なる場合は、氏名の横に勤務区分の列を追加します。
通常のSUM関数では全スタッフを合計しますが、区分別に集計したい場合はSUMIFS関数を使います。
=SUMIFS(D$2:D$20,$B$2:$B$20,”レジ”)
この例では、D列の勤務判定を合計しながら、B列の区分がレジである行だけを対象にしています。
実際の列位置に合わせて、勤務区分の範囲と条件を設定してください。
全体人数が足りていても、必要な業務の担当者が不足していれば現場は回りません。
職種別の時間帯人数を別の集計表へ表示すると、配置の偏りを早く発見できます。
【操作のポイント】見やすさのための色分けと、業務別の集計を組み合わせると、人数だけでは見えない配置の課題を確認できます。
まとめ エクセルで時間帯別に人数を管理するシフト表の作り方
エクセルで時間帯別に人数を管理するシフト表は、開始時刻、終了時刻、時間帯見出しを時刻データとして入力し、勤務中かどうかを1と0で判定することで作成できます。
スタッフ別の判定結果をSUM関数で合計すれば、各時間帯に配置されている人数を自動表示できます。
さらに必要人数との差を計算し、負の数を条件付き書式で赤くすると、人手不足が起きる時間帯を視覚的に把握できます。
日付列、休憩時間、勤務区分を追加すれば、毎日のシフト管理から職種別の配置確認まで対応可能です。
最初は1時間単位のシンプルな表から始め、運用に合わせて30分単位、休憩除外、曜日別集計へ広げていきましょう。
入力ルールと数式の参照方法を統一した時間帯別シフト表を活用し、無理のない人員配置につなげてください。
