Excelのシフト表で時間別人数を集計する方法｜休憩を除くCOUNTIFS

Excelのシフト表で時間別人数を出すには、確認時刻に「勤務開始済みで、まだ勤務終了していない人」を数え、そこから休憩中の人数を差し引きます。「勤務枠の人数－休憩中の人数＝勤務中の人数」と分けると、出勤していても持ち場にいない人を除外できます。

以下では、架空の4人分のシフト表を使い、入力からCOUNTIFS関数による集計、必要人数との比較までを説明します。表と数式を手元のエクセルに入力して再現してください。ここで作るのは入力済みシフトの集計表であり、勤務を自動で割り当てる仕組みではありません。

時間別人数は「その時刻の勤務中人数」で数える

この表でいう時間別人数は、12:00、12:30など、各時刻ちょうどに勤務中の人数です。「12時台の1時間を通じて、最低何人いるか」を直接求めるものではありません。

まず、この数え方と入力の前提をそろえます。

開始を含め、終了を含めない

勤務中と判定する条件は、「勤務開始が確認時刻以前」「勤務終了が確認時刻より後」の2つです。

たとえば12:00開始の人は12:00に数え、12:00終了の人は数えません。休憩にも同じルールを適用し、12:00から13:00まで休憩する人は、12:00には休憩中、13:00には勤務中として扱います。

開始・終了の両方を含めると、交代時刻に退勤する人まで残っているように集計してしまいます。

1人1行・休憩1回を前提にする

対象は、同じ1日内で完結する勤務です。1人につき1行とし、休憩は最大1回。休憩なしの場合は、休憩開始と休憩終了を両方空欄にします。

複数休憩や分割勤務は、この表の適用外です。単純に同じ人の行を増やすのではなく、勤務・休憩の明細構造から見直してください。

また、数式が数えるのは条件に合う行です。名前だけで同一人物を名寄せする仕組みではないため、同じ人を重複入力しないことも前提になります。

図を拡大して見る

手順1：勤務と休憩の時刻を入力する

集計式より先に、元データを整えます。時刻の入力ミスがあると、数式が正しくても人数は合いません。

A～E列に4人のデータを入れる

新しいシートのA1:E1に見出し、A2:E5に次のデータを入力してください。勤務時刻・休憩時刻はすべて架空の検算例です。

この例ではA6:E101を空欄にし、集計対象を2～101行に統一します。F列は空け、右側のG～L列に集計表を作ります。

時刻は「9時」などの文字ではなく、半角のコロンを使って「9:00」と入力してください。日付付きの値を混在させず、時刻だけでそろえます。

B2:E101と、後で使うG2:G101の表示形式は「h:mm」に設定します。Windows版では、［セルの書式設定］→［表示形式］→［ユーザー定義］の［種類］に入力する手順です。Excelの版によって操作名が多少異なる場合があります。(Microsoft サポート)

数値と時刻の前後関係を確認する

入力後は、次の条件を満たすか確認します。

B2が数値か調べるには、空いているM2などに次の式を入力します。

=ISNUMBER(B2)

ISNUMBERは、対象が数値ならTRUE、それ以外ならFALSEを返す関数です。文字として保存された時刻を見つける確認に使えます。(Microsoft サポート)

C～E列も、時刻を入力したセルについて確認してください。FALSEになった時刻は、表示形式を整えるだけで済ませず、入力内容を確認して入れ直します。

ただし、TRUEでも時刻の前後関係までは保証されません。休憩開始だけを入力して終了を空欄にした行や、勤務終了後まで休憩が続く行は、集計前に修正しましょう。

手順2：COUNTIFSで勤務中人数を集計する

COUNTIFS関数は、複数の条件をすべて満たすセルの組をカウントします。今回は勤務開始・終了などの条件を同じ行に適用し、その時刻に該当する人数を求めます。(Microsoft サポート)

G～L列に確認時刻と集計欄を作る

G1:L1には次の見出しを入力し、G2に12:00、K2に数字の3を入れてください。H2・I2・J2・L2には、この後で数式を設定します。

G3に12:30、G4に13:00、G5に15:00、G6に16:00を入力します。G列も元データと同じく、文字ではなく数値の時刻にしてください。

H～L列は人数を表示するため、表示形式を「標準」または小数点以下0桁の「数値」にそろえます。この例の必要人数3人は、検算のために置いた架空の値で、全確認時刻で共通です。

H列で勤務枠、I列で休憩中の人数を数える

まず、H2に次の式を入力します。

=COUNTIFS($A$2:$A$101,"<>",$B$2:$B$101,"<>",$B$2:$B$101,"<="&G2,$C$2:$C$101,">"&G2)

氏名と勤務開始が空欄でなく、勤務開始がG2以前、勤務終了がG2より後の行を数えます。休憩中の人も、この段階では勤務枠の人数に含まれます。

次に、I2へ休憩中の人数を数える式を入力してください。

=COUNTIFS($A$2:$A$101,"<>",$B$2:$B$101,"<>",$B$2:$B$101,"<="&G2,$C$2:$C$101,">"&G2,$D$2:$D$101,"<>",$D$2:$D$101,"<="&G2,$E$2:$E$101,">"&G2)

H2の勤務条件に加え、「休憩開始が空欄でない」「休憩開始がG2以前」「休憩終了がG2より後」を確認します。休憩のD・E列が両方空欄の人は、休憩中の人数には入りません。

$A$2:$A$101などの範囲は、コピーしても参照先が変わらない絶対参照です。一方、G2には$を付けていないため、下へコピーするとG3、G4へ変わります。(Microsoft サポート)

J～L列で勤務中人数と過不足を計算する

J2には、勤務枠から休憩中の人数を引く式を入れます。

=H2-I2

K2の必要人数が3になっていることを確認し、L2には次の式を入力してください。

=J2-K2

これで、勤務中人数から必要人数を引いた過不足が出ます。マイナスなら不足、0なら人数一致、正数なら必要人数を上回っている状態です。

最後にH2:L2をコピーし、H3:L6へ貼り付けます。数式だけでなくK列の3もコピーされ、5つの確認時刻が同じ条件で計算されます。G列に時刻を入れていない行まで、先回りして数式を広げる必要はありません。

手順3：5つの時刻で検算する

ここまで入力すると、G1:L6は次の結果になります。実際の勤務実績ではなく、先ほどの架空データから求めた検算表です。

12:00は、勤務枠にいる4人から休憩中のAさんを引き、Bさん・Cさん・Dさんの3人です。

12:30にはCさんも休憩に入り、勤務中はBさん・Dさんの2人に減ります。13:00にはAさんとCさんが戻り、代わりにBさんが休憩に入るため、Aさん・Cさん・Dさんの3人になります。

15:00は勤務終了したCさんを含めず、16:00はDさんも含めません。特にこの2行は、「終了時刻ちょうどを数えない」という境界の検算に使えます。

図を拡大して見る

手順4：不足を赤く表示し、シフトを調整する

集計できたら、勤務中人数そのものではなく、必要人数との差を見ます。今回なら12:30と16:00が、それぞれ1人不足している確認時刻です。

条件付き書式は「0より小さい」で設定する

L2:L101を選択し、次の順に設定します。

1. ［ホーム］→［条件付き書式］→［新しいルール］を開く。
2. ［指定の値を含むセルだけを書式設定］を選ぶ。
3. ［セルの値］が0より小さい条件にする。
4. ［書式］で赤い塗りつぶしなどを指定する。

条件付き書式では、セルの値と比較条件に応じて書式を変えられます。ここでは「0と等しくない」ではなく「0より小さい」を選び、余剰と不足を区別します。(Microsoft サポート)

ただし、人数が一致していても、必要な資格や担当業務を担える人がいるかは、この表だけでは判定できません。L列の0は、あくまで入力した必要人数との一致です。

時間帯を通じた不足は変化点で確認する

今回の例は、12:00に3人いても、12:30には2人になります。したがって、12:00の結果だけを見て「12時台はずっと3人いる」と判断してはいけません。

30分ごと、15分ごとに確認しても、その間に勤務や休憩が始まれば変化を見落とします。厳密に確認するなら、全員の勤務開始・終了、休憩開始・終了の時刻をG列へ追加してください。

同じ時刻は1つにまとめ、早い順に並べます。この検算例なら、9:00、10:00、11:00、14:00、14:30、17:00、18:00も追加対象です。追加した各行にはH～L列の数式・必要人数を設定します。

必要人数が変わる時刻も確認対象に加え、K列にはその時点の値を入れましょう。人数と必要人数の変化点をそろえることで、確認時刻の間に隠れた不足を点検できます。

図を拡大して見る

実運用では入力ミスと調整後の人数を確認する

人数が合わないときは、式を変更する前に、氏名の空欄・重複、時刻の文字入力、休憩の片側だけの空欄を確認します。H列から違うなら勤務データ、H列は合うのにJ列が違うなら休憩データとI列を調べる順番です。

従業員を102行目以降へ追加した場合は、すべてのCOUNTIFSの終端101を同じ行まで伸ばしてください。COUNTIFSの各条件範囲は、行数・列数をそろえる必要があります。(Microsoft サポート)

不足を見つけたら、休憩の開始時刻や勤務案を担当者が調整し、変更後の人数を再確認します。12:30の不足が消えても、別の時刻に不足が移っていないかまで確認することが、シフト管理では重要です。

こうした調整が増える場合は、シフト希望・必要人数・連勤上限などを条件に勤務案を作るシフトラも選択肢になります。確認・確定したシフトを既存のExcel帳票へ出力する機能があり、案の確認と最終確定は担当者が行います。(シフトラ)

個人の実働時間や月合計を求めたい場合は、Excelで勤務時間を自動計算する手順を参照してください。日をまたぐ夜勤の入力と帯表示は、夜間シフト表のガントチャートで扱っています。

まとめ：休憩を引いた人数で必要人数と比べる

Excelの時間別人数は、勤務開始・終了の条件で数えた人数から、同じ時刻に休憩中の人数を引いて求めます。まず4人の検算例で結果を合わせ、その後に自分の職場のデータへ置き換えてください。

運用時は、開始を含め終了を含めない境界、時刻の数値入力、休憩の整合性、集計範囲をそろえることが基本です。最後に勤務・休憩・必要人数の変化点まで確認し、時間帯の途中にある不足を見落とさない集計表に整えましょう。