エクセル（Excel）のシフト表は、DATE関数とTEXT関数で日付・曜日を自動で並べ、COUNTIF関数で出勤日数と日ごとの人数を集計し、条件付き書式で土日や人手不足の日に色を付ける、という3段階で作るのが基本です。この記事では、シフト表で使う関数を早見表で整理したうえで、数式をそのまま使える月間シフト表の作り方、夜勤をまたぐ勤務時間の計算、8時間超えのチェック、年収の上限から残り時間を出す方法まで順に解説します。
シフト表作成を自動化したい方へ
シフト表でよく使う関数を、目的別にまとめました。まずはDATE・TEXT・COUNTA・COUNTIF・SUMの5つを覚えれば、日付と曜日の自動入力、出勤日数と人数の集計まで作れます。
| 目的 | 関数 | 数式の例 |
|---|---|---|
| 日付を自動で並べる | DATE | =DATE($A$1,$B$1,1) |
| 曜日を表示する | TEXT | =TEXT(D3,"aaa") |
| 出勤日数を数える | COUNTA／COUNTIF | =COUNTA(D5:AH5)-COUNTIF(D5:AH5,"休") |
| 勤務区分ごとの人数を数える | COUNTIF | =COUNTIF(D$5:D$14,$A16) |
| 役割・時間帯など複数条件で数える | COUNTIFS | =COUNTIFS(D$5:D$14,"早",$B$5:$B$14,"リーダー") |
| 出勤人数を合計する | SUM | =SUM(D16:D18) |
| 月末以降を空欄にする・判定する | IF／MONTH | =IF(MONTH(D3+1)=$B$1,D3+1,"") |
| 土日を判定して色を付ける | WEEKDAY | =WEEKDAY(D$3)=1 |
| 日付をまたぐ勤務時間を出す | MOD | =MOD(C2-B2,1)*24 |
| 勤務記号から勤務時間を引く | XLOOKUP／VLOOKUP | =XLOOKUP(D5,記号表!$A$2:$A$5,記号表!$B$2:$B$5,0) |
| 人件費を計算する | SUMPRODUCT | =SUMPRODUCT(勤務時間の範囲,時給の範囲) |
| 残り日数を切り捨てで出す | ROUNDDOWN | =ROUNDDOWN(F2/C2,0) |
XLOOKUP関数はExcel 2021以降とMicrosoft 365で使えます。それより前のバージョンでは、同じ処理をVLOOKUP関数（例：=VLOOKUP(D5,記号表!$A$2:$B$5,2,FALSE)）で行えます。
シフト管理表（シフト表）とは、従業員の出勤日・勤務時間帯をまとめて管理するための表です。エクセルでは主に次の4種類を作れます。この記事の手順では、最もよく使われる月間シフト表を例にします。
| シフトの種類 | 特徴 | 向いている業態 |
|---|---|---|
| タイムシフト | 横軸が時間、縦軸が従業員名。1日単位で人員配置を可視化 | 飲食・小売など時間帯ごとに人数が変わる業態 |
| 週間シフト | 1週間分の出勤曜日と時間をまとめて管理 | 曜日ごとに特定の配置が必要な業態 |
| 月間シフト | カレンダー形式で出勤日と勤務区分（早番・遅番など）を月単位で把握 | 介護・医療・工場など勤務区分で回す業態 |
| 休暇管理表 | 希望休・有給休暇の取得予定と残日数を管理 | シフト表と別に休みを管理したい職場 |
タイムシフトは勤務時間帯を横棒で表すため、時間帯ごとの人員の過不足が一目でわかります。月間シフトは「早」「遅」「夜」「休」などの記号で出勤日を管理し、COUNTIF関数で人数を集計する作り方と相性が良い形式です。
ここでは、年と月を書き換えるだけで毎月使い回せる月間シフト表を作ります。表のレイアウトは次のとおりです。
| 場所 | 入れるもの |
|---|---|
| A1／B1 | 年（例：2026）／月（例：11） |
| 3行目・4行目（D列〜AH列） | 日付／曜日 |
| A列・B列・C列（5〜14行目） | 従業員名／役割／出勤日数 |
| D5:AH14 | 勤務記号（早・遅・夜・休） |
| 16〜21行目 | 勤務区分ごとの人数／出勤人数の合計／必要人数／過不足 |
A1に年（2026）、B1に月（11）を入力し、日付を並べる最初のセルD3に次の式を入れます。
=DATE($A$1,$B$1,1)
隣のE3には、前の日付に1を足し、翌月になったら空欄にする式を入れてAH3までコピーします。
=IF(D3="","",IF(MONTH(D3+1)=$B$1,D3+1,""))
これで30日までの月は31日の列が空欄になり、月を書き換えるだけで日付がすべて更新されます。日付のセルは「セルの書式設定」→「ユーザー定義」で「d」にすると、日にちだけを表示できます。
D4に次の式を入れてAH4までコピーします。
=IF(D3="","",TEXT(D3,"aaa"))
「aaa」は「月」「火」、「aaaa」は「月曜日」の形式で表示されます。WEEKDAY関数は曜日を1〜7の数値で返す関数で、表示よりも次の章の色付けの判定に向いています。
A5以降に従業員名、B列に役割（リーダー・一般など）を入力します。次にD5:AH14を選択し、「データ」→「データの入力規則」で入力値の種類を「リスト」にして、元の値に「早,遅,夜,休」と入力します。
勤務記号をプルダウンから選ぶ形にすると、「早番」「早」のような表記ゆれがなくなり、COUNTIF関数での集計ミスを防げます。
C5に次の式を入れて下にコピーすると、休み以外の記号が入った日数＝出勤日数が出ます。
=COUNTA(D5:AH5)-COUNTIF(D5:AH5,"休")
COUNTA関数は空白以外のセルをすべて数えるため、「休」を入力する運用では、休みの数を引いておかないと出勤日数が多く出てしまいます。
日ごとの人数は、A16〜A18に「早」「遅」「夜」と入力し、D16に次の式を入れて縦横にコピーします。
=COUNTIF(D$5:D$14,$A16)
「$」で行と列を固定しているので、コピーするだけで全日・全区分の人数が集計できます。リーダーが入っているかを確認したい日は、COUNTIFS関数で複数の条件を組み合わせます。
=COUNTIFS(D$5:D$14,"早",$B$5:$B$14,"リーダー")
D19に出勤人数の合計、20行目に日ごとの必要人数を入力し、D21で過不足を計算します。
=SUM(D16:D18) ／ =D19-D20
D21:AH21を選択し、「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」で =D21<0 と入力し、塗りつぶしを赤に設定します。マイナス（人が足りない日）だけが赤くなり、シフトの組み直しが必要な日がすぐにわかります。
▼ あわせて読みたい記事
シフト管理アプリおすすめ比較19選｜無料あり・管理者向け/個人向けの選び方
AIで勤務表・シフト表を自動作成｜ChatGPTとの違いとおすすめツール17選
土日や祝日の列に色が付いていると、休日の人員配置を確認しやすくなります。条件付き書式で次のルールを設定します。
=WEEKDAY(D$3)=1、土曜日は =WEEKDAY(D$3)=7 と入力し、それぞれ塗りつぶしの色を設定します。=COUNTIF(祝日!$A:$A,D$3)>0 というルールを追加します。「D$3」のように行だけを固定するのがポイントです。範囲内のどのセルでも3行目の日付を見て判定するため、列全体に色が付きます。祝日の一覧は内閣府が公開している「国民の祝日」の日付をコピーして使えます。
時間で管理するシフト表では、開始・終了時刻から勤務時間を計算し、法律や契約の上限を超えていないかを確認します。
B列に開始時刻、C列に終了時刻、D列に休憩時間（時間数）を入れた場合、E列の実働時間は次の式で求めます。
=MOD(C2-B2,1)*24-D2
エクセルの時刻は1日を「1」とした数値なので、24を掛けると時間数になります。22:00〜翌6:00のように日付をまたぐ勤務は「終了−開始」がマイナスになりますが、MOD関数で1で割った余りを取ると正しく8時間と計算できます。E列の表示形式は「標準」にしてください。
月間シフト表で「早」「遅」などの記号を使っている場合は、別シート「記号表」にA列＝記号、B列＝実働時間（早＝7.5、遅＝7.5、夜＝8、休＝0など）を用意し、XLOOKUP関数で時間を引きます。
=XLOOKUP(D5,記号表!$A$2:$A$5,記号表!$B$2:$B$5,0)
1人の月の合計勤務時間を一つのセルで出したい場合は、SUMPRODUCT関数とCOUNTIF関数を組み合わせます。
=SUMPRODUCT(COUNTIF(D5:AH5,記号表!$A$2:$A$5)*記号表!$B$2:$B$5)
記号ごとの出勤回数×時間を合計するので、記号表の時間を変えるだけで全員の勤務時間が更新されます。月の勤務時間に時給を掛ければ人件費も計算でき、全員分の合計は =SUMPRODUCT(勤務時間の範囲,時給の範囲) で出せます。
条件付き書式を使うと、決められた時間を超えた従業員のセルを自動で赤くできます。
=MOD(B2-A2,1)*24 です（日付をまたがない勤務なら =(B2-A2)*24 でも同じ結果になります）。書式は「数値」または「標準」に設定してください。=C2-D2 と入力して超過時間を表示させます。=C2>D2 と入力し、「書式」でセルの塗りつぶし色を赤に設定して保存します。労働基準法では、法定労働時間を1日8時間・週40時間と定めています。実働時間の横に次の式を入れておくと、超えた日に「要確認」と表示されます。
=IF(E2>8,"要確認","")
週の合計は7日分の実働時間をSUM関数で合計し、40を超えたら色を付けるルールを設定します。休憩時間（6時間超で45分以上、8時間超で1時間以上）が入っているかも一緒に確認しておくと安心です。
▼ シフト作成で守る法律のルールは、以下の記事で解説しています
シフト制と労働基準法｜違法にならないシフト作成のルールと注意点
扶養の範囲で働きたいパート・アルバイトのシフトを調整するときに、年収の上限から「年内にあと何時間入れるか」を計算する方法です。
| セル | 内容 | 入力例 |
|---|---|---|
| A2 | 時給 | 1,200円 |
| B2 | 1日の労働時間 | 6時間 |
| C2 | 1日の収入額 | =A2*B2 |
| D2 | 上限年収額 | 1,300,000円 |
| E2 | 取得済み収入額 | 1,120,000円 |
上限年収額は、本人がどの「年収の壁」を基準にするかで変わります。例では社会保険の扶養（被扶養者）の基準である130万円にしています。所得税がかからない年収は2026年分・2027年分は178万円で、勤務先の社会保険に加入する基準は2026年10月から月額8.8万円の賃金要件が撤廃されるなど制度改正が続いているため、最新の基準を本人と確認してから入力してください。
▼ 年収の壁については、以下の記事で解説しています
106万円の壁は2026年10月に撤廃｜損しない働き方と週20時間の壁を社労士が解説
F2に =D2-E2 と入力します。上限年収から取得済み収入を差し引いた残額が表示されます（例では180,000円）。
G2に =ROUNDDOWN(F2/C2,0) と入力します。四捨五入ではなく切り捨て（ROUNDDOWN）を使うことで、端数による年収上限オーバーを防げます（例では180,000÷7,200＝25日）。
H2に =G2*B2 と入力します。残り可能日数に1日の労働時間を掛けた値が、年内に入れる残り時間数です（例では150時間）。交通費の扱いなど、年収に含めるかどうかの基準は壁ごとに異なる点にも注意しましょう。
列・行ごと非表示にする場合：対象の列または行を右クリックして「非表示」を選ぶと、印刷時に除外されます。時給など列全体が対象であればこの方法が最もシンプルです。
特定のセルだけ非表示にする場合（条件付き書式を使う方法）：
文字が背景に溶け込んで見えなくなるため、印刷しても表示されません。画面上でも見えなくなるので、確認したいときはセルを選択して数式バーで内容を見ます。印刷前には印刷プレビューで確認することをおすすめします。
エクセルでシフト管理表を作る前に、メリットとデメリットの両方を理解しておくことが重要です。
| デメリット | 具体的なリスク |
|---|---|
| 属人化のリスク | 関数を組んだ担当者が不在になると、修正や引き継ぎができなくなる |
| ミスが発生しやすい | 行の追加やコピーで数式の参照がずれても気づきにくく、集計ミスにつながる |
| 複雑化すると管理困難 | 従業員数や勤務パターンが増えると、希望休や勤務ルールを満たす組み合わせを手作業で探す負担が大きくなる |
エクセルは「入力されたデータの集計・チェック」を得意としますが、「希望休や勤務ルールを満たすシフトを自動で組むこと」は得意ではありません。毎月のシフト作成に何時間もかかっている、担当者しか作れないといった状態であれば、シフト作成ツールへの切り替えを検討するタイミングです。
▼ エクセルのシフト表テンプレートは、以下の記事でダウンロードできます
【2026年最新】無料シフト表テンプレート（Excel/自動計算付）作り方とアプリも紹介
【エクセルテンプレート無料提供】エクセルで作るシフト表、タイムスケジュール表のコツを紹介！
エクセル勤怠管理表の作り方！無料テンプレートと関数の設定、脱エクセルのタイミング
基本は5ステップです。①年と月を入力してDATE関数で日付を並べる、②TEXT関数で曜日を表示する、③従業員名と勤務記号のプルダウンを用意する、④COUNTIF関数で出勤日数と日別の人数を集計する、⑤必要人数との過不足を計算して足りない日に色を付ける、の順に作ると、毎月の作り直しが年と月の書き換えだけで済みます。
日付と曜日にはDATE関数とTEXT関数、出勤日数や勤務区分ごとの人数にはCOUNTA関数とCOUNTIF関数、役割や時間帯などの複数条件にはCOUNTIFS関数、合計にはSUM関数を使います。勤務時間の計算にはMOD関数、勤務記号から時間を引くにはXLOOKUP関数やVLOOKUP関数、人件費の計算にはSUMPRODUCT関数が便利です。
年と月を入力したセルを使い、最初の日付を =DATE(年のセル,月のセル,1) で作り、隣のセルに前日+1を返す式を入れてコピーします。曜日は =TEXT(日付のセル,"aaa") で「月」「火」と表示できます。月が変わったら年と月のセルを書き換えるだけで、日付と曜日がすべて更新されます。
22時から翌6時のように日付をまたぐ勤務を「終了−開始」で計算すると、終了時刻のほうが小さくなるためマイナスになり、エクセルでは「####」と表示されます。=MOD(終了-開始,1)*24 のようにMOD関数を使うと、日付をまたいでも正しい時間数（この例では8時間）を計算できます。休憩時間を引く場合は、さらに休憩の時間数を差し引きます。
条件付き書式の「数式を使用して、書式設定するセルを決定」を使います。日付が3行目にある場合、=WEEKDAY(D$3)=1 で日曜日、=WEEKDAY(D$3)=7 で土曜日に色を付けられます。祝日は別シートに祝日の日付一覧を作り、=COUNTIF(祝日!$A:$A,D$3)>0 というルールを追加します。
DATE、COUNTA、COUNTIF、COUNTIFS、SUM、IF、MOD、SUMPRODUCT、XLOOKUPなど、この記事で紹介している主な関数はGoogleスプレッドシートでも使えます。ただし曜日の表示形式など一部の書式や、マクロ（VBA）はそのまま動かないため、ファイルを移す場合は表示を確認してください。
主なデメリットは、関数を組んだ担当者しか直せなくなる属人化、手入力や数式の崩れによるミス、従業員数や勤務パターンが増えたときの作業負担の3つです。エクセルは集計やチェックは得意ですが、希望休や勤務ルールを満たすシフトを自動で組むことは得意ではないため、作成に毎月長い時間がかかる場合はシフト作成ツールの検討が有効です。
エクセルでシフト管理表を作るうえで、重要なポイントをまとめます。
JRシステムが提供しているシフト作成・管理ツール「勤務シフト作成お助けマン」は、自動でシフト表を作成するクラウドサービスです。エクセルをベースにしているため、見た目や使い勝手はそのまま。エクセルのデータをコピー＆ペーストできるため、これまでと同様のインターフェースで作業が可能です。
「勤務シフト作成お助けマン」には、早番・遅番・夜勤等の「1日1記号を割り当てるシフト表」を作成できる「勤務シフト作成お助けマンDay」と、10:00〜17:30等の「時間を割り当てるシフト表」を作成する「勤務シフト作成お助けマンTime」があります。作成したいシフト表に合わせてサービスを選べます。
本番利用時と同じ機能を2か月無料でトライアルできます。ぜひ、自社に合った方法でシフト表を作成・管理しましょう。