エクセルでシフト表を作る関数と手順｜人数集計・勤務時間・色分けを自動化【2026年版】

エクセル（Excel）のシフト表は、DATE関数とTEXT関数で日付・曜日を自動で並べ、COUNTIF関数で出勤日数と日ごとの人数を集計し、条件付き書式で土日や人手不足の日に色を付ける、という3段階で作るのが基本です。この記事では、シフト表で使う関数を早見表で整理したうえで、数式をそのまま使える月間シフト表の作り方、夜勤をまたぐ勤務時間の計算、8時間超えのチェック、年収の上限から残り時間を出す方法まで順に解説します。

シフト表作成を自動化したい方へ

1. エクセルのシフト表で使う関数一覧【早見表】
2. エクセルで作れるシフト表の種類は？
3. エクセルの関数で月間シフト表を作る手順
4. 土日・祝日に自動で色を付ける方法は？
5. 勤務時間を自動計算する関数は？夜勤・休憩・8時間超えのチェック
6. 年内に残り何時間シフトに入れるかを計算する方法は？
7. シフト表の印刷時に特定の情報を非表示にする方法は？
8. エクセルでシフトを作るメリット・デメリットは？
9. よくある質問
10. まとめ

エクセルのシフト表で使う関数一覧【早見表】

シフト表でよく使う関数を、目的別にまとめました。まずはDATE・TEXT・COUNTA・COUNTIF・SUMの5つを覚えれば、日付と曜日の自動入力、出勤日数と人数の集計まで作れます。

XLOOKUP関数はExcel 2021以降とMicrosoft 365で使えます。それより前のバージョンでは、同じ処理をVLOOKUP関数（例：=VLOOKUP(D5,記号表!$A$2:$B$5,2,FALSE)）で行えます。

エクセルで作れるシフト表の種類は？

シフト管理表（シフト表）とは、従業員の出勤日・勤務時間帯をまとめて管理するための表です。エクセルでは主に次の4種類を作れます。この記事の手順では、最もよく使われる月間シフト表を例にします。

タイムシフトは勤務時間帯を横棒で表すため、時間帯ごとの人員の過不足が一目でわかります。月間シフトは「早」「遅」「夜」「休」などの記号で出勤日を管理し、COUNTIF関数で人数を集計する作り方と相性が良い形式です。

エクセルの関数で月間シフト表を作る手順

ここでは、年と月を書き換えるだけで毎月使い回せる月間シフト表を作ります。表のレイアウトは次のとおりです。

Step 1：年・月を入力し、DATE関数で日付を自動で並べる

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」にすると、日にちだけを表示できます。

Step 2：TEXT関数で曜日を表示する

D4に次の式を入れてAH4までコピーします。

=IF(D3="","",TEXT(D3,"aaa"))

「aaa」は「月」「火」、「aaaa」は「月曜日」の形式で表示されます。WEEKDAY関数は曜日を1〜7の数値で返す関数で、表示よりも次の章の色付けの判定に向いています。

Step 3：従業員名と勤務記号のプルダウンを用意する

A5以降に従業員名、B列に役割（リーダー・一般など）を入力します。次にD5:AH14を選択し、「データ」→「データの入力規則」で入力値の種類を「リスト」にして、元の値に「早,遅,夜,休」と入力します。

勤務記号をプルダウンから選ぶ形にすると、「早番」「早」のような表記ゆれがなくなり、COUNTIF関数での集計ミスを防げます。

Step 4：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,"リーダー")

Step 5：必要人数との過不足を計算して色を付ける

D19に出勤人数の合計、20行目に日ごとの必要人数を入力し、D21で過不足を計算します。

=SUM(D16:D18)　／　=D19-D20

D21:AH21を選択し、「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」で =D21<0 と入力し、塗りつぶしを赤に設定します。マイナス（人が足りない日）だけが赤くなり、シフトの組み直しが必要な日がすぐにわかります。

▼ あわせて読みたい記事
シフト管理アプリおすすめ比較19選｜無料あり・管理者向け/個人向けの選び方
AIで勤務表・シフト表を自動作成｜ChatGPTとの違いとおすすめツール17選

土日・祝日に自動で色を付ける方法は？

土日や祝日の列に色が付いていると、休日の人員配置を確認しやすくなります。条件付き書式で次のルールを設定します。

1. 色を付けたい範囲（例：D3:AH21）を選択します。
2. 「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。
3. 日曜日は =WEEKDAY(D$3)=1、土曜日は =WEEKDAY(D$3)=7 と入力し、それぞれ塗りつぶしの色を設定します。
4. 祝日は、別シート「祝日」のA列に祝日の日付を並べ、=COUNTIF(祝日!$A:$A,D$3)>0 というルールを追加します。

「D$3」のように行だけを固定するのがポイントです。範囲内のどのセルでも3行目の日付を見て判定するため、列全体に色が付きます。祝日の一覧は内閣府が公開している「国民の祝日」の日付をコピーして使えます。

勤務時間を自動計算する関数は？夜勤・休憩・8時間超えのチェック

時間で管理するシフト表では、開始・終了時刻から勤務時間を計算し、法律や契約の上限を超えていないかを確認します。

開始・終了時刻から勤務時間を出す（夜勤にも対応）

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(勤務時間の範囲,時給の範囲) で出せます。

勤務時間オーバーのセルに自動で色を付ける

条件付き書式を使うと、決められた時間を超えた従業員のセルを自動で赤くできます。

1. A列に勤務開始時間、B列に勤務終了時間を入力します。
2. C列に実際の勤務時間を計算します。C2の数式は =MOD(B2-A2,1)*24 です（日付をまたがない勤務なら =(B2-A2)*24 でも同じ結果になります）。書式は「数値」または「標準」に設定してください。
3. D列に各従業員の許容勤務時間を入力します。
4. E列に =C2-D2 と入力して超過時間を表示させます。
5. C列を選択し、「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。
6. 条件に =C2>D2 と入力し、「書式」でセルの塗りつぶし色を赤に設定して保存します。

1日8時間・週40時間を超えていないかチェックする

労働基準法では、法定労働時間を1日8時間・週40時間と定めています。実働時間の横に次の式を入れておくと、超えた日に「要確認」と表示されます。

=IF(E2>8,"要確認","")

週の合計は7日分の実働時間をSUM関数で合計し、40を超えたら色を付けるルールを設定します。休憩時間（6時間超で45分以上、8時間超で1時間以上）が入っているかも一緒に確認しておくと安心です。

▼ シフト作成で守る法律のルールは、以下の記事で解説しています
シフト制と労働基準法｜違法にならないシフト作成のルールと注意点

年内に残り何時間シフトに入れるかを計算する方法は？

扶養の範囲で働きたいパート・アルバイトのシフトを調整するときに、年収の上限から「年内にあと何時間入れるか」を計算する方法です。

Step 1：基本情報をセルに入力する

上限年収額は、本人がどの「年収の壁」を基準にするかで変わります。例では社会保険の扶養（被扶養者）の基準である130万円にしています。所得税がかからない年収は2026年分・2027年分は178万円で、勤務先の社会保険に加入する基準は2026年10月から月額8.8万円の賃金要件が撤廃されるなど制度改正が続いているため、最新の基準を本人と確認してから入力してください。

▼ 年収の壁については、以下の記事で解説しています
106万円の壁は2026年10月に撤廃｜損しない働き方と週20時間の壁を社労士が解説

Step 2：残り取得可能収入額を算出する

F2に =D2-E2 と入力します。上限年収から取得済み収入を差し引いた残額が表示されます（例では180,000円）。

Step 3：残りシフト可能日数を算出する

G2に =ROUNDDOWN(F2/C2,0) と入力します。四捨五入ではなく切り捨て（ROUNDDOWN）を使うことで、端数による年収上限オーバーを防げます（例では180,000÷7,200＝25日）。

Step 4：残りシフト時間数を算出する

H2に =G2*B2 と入力します。残り可能日数に1日の労働時間を掛けた値が、年内に入れる残り時間数です（例では150時間）。交通費の扱いなど、年収に含めるかどうかの基準は壁ごとに異なる点にも注意しましょう。

シフト表の印刷時に特定の情報を非表示にする方法は？

列・行ごと非表示にする場合：対象の列または行を右クリックして「非表示」を選ぶと、印刷時に除外されます。時給など列全体が対象であればこの方法が最もシンプルです。

特定のセルだけ非表示にする場合（条件付き書式を使う方法）：

1. 非表示にしたいセルに「※」を入力しておきます。
2. 「条件付き書式」→「セルの強調表示ルール」→「文字列」で「※」を指定します。
3. 「ユーザー設定の書式」でフォントの色をシートの背景色（白）と同じ色に設定します。

文字が背景に溶け込んで見えなくなるため、印刷しても表示されません。画面上でも見えなくなるので、確認したいときはセルを選択して数式バーで内容を見ます。印刷前には印刷プレビューで確認することをおすすめします。

エクセルでシフトを作るメリット・デメリットは？

エクセルでシフト管理表を作る前に、メリットとデメリットの両方を理解しておくことが重要です。

エクセルシフトのメリット

• 低コスト：多くの職場ですでに導入済みのため、追加費用なしで使い始められます。
• 操作が比較的シンプル：この記事の5つの基本関数を覚えるだけで、すぐに作成を始められます。
• 自由度の高いカスタマイズ：関数やマクロを使えば、自社のシフトルールに合わせた表を構築できます。

エクセルシフトのデメリット

エクセルは「入力されたデータの集計・チェック」を得意としますが、「希望休や勤務ルールを満たすシフトを自動で組むこと」は得意ではありません。毎月のシフト作成に何時間もかかっている、担当者しか作れないといった状態であれば、シフト作成ツールへの切り替えを検討するタイミングです。

▼ エクセルのシフト表テンプレートは、以下の記事でダウンロードできます
【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 というルールを追加します。

Googleスプレッドシートでも同じ関数は使えますか？

DATE、COUNTA、COUNTIF、COUNTIFS、SUM、IF、MOD、SUMPRODUCT、XLOOKUPなど、この記事で紹介している主な関数はGoogleスプレッドシートでも使えます。ただし曜日の表示形式など一部の書式や、マクロ（VBA）はそのまま動かないため、ファイルを移す場合は表示を確認してください。

エクセルでシフトを管理するデメリットは何ですか？

主なデメリットは、関数を組んだ担当者しか直せなくなる属人化、手入力や数式の崩れによるミス、従業員数や勤務パターンが増えたときの作業負担の3つです。エクセルは集計やチェックは得意ですが、希望休や勤務ルールを満たすシフトを自動で組むことは得意ではないため、作成に毎月長い時間がかかる場合はシフト作成ツールの検討が有効です。

まとめ：基本の5関数で作り、手間がかかるなら自動作成へ

エクセルでシフト管理表を作るうえで、重要なポイントをまとめます。

1. DATE・TEXT・COUNTA・COUNTIF・SUMの5関数が基本：日付と曜日の自動入力、出勤日数と日ごとの人数の集計まで作れます。年と月を入力するセルを起点にすると、毎月の作り直しが不要になります。
2. 条件付き書式で「見える化」する：土日・祝日、人手が足りない日、勤務時間オーバーに自動で色を付けると、確認漏れを減らせます。
3. 勤務時間はMOD関数で計算する：夜勤のように日付をまたぐ勤務も正しく計算でき、8時間・40時間を超えていないかのチェックにもつなげられます。
4. 複雑なシフトには専用ツールの検討を：エクセルは集計・チェックは得意ですが、シフトを自動で組むことは不得意です。従業員数が増えたり、勤務パターンが複雑になったりした場合は、シフト自動作成ツールへの移行が効率化への近道です。

JRシステムが提供しているシフト作成・管理ツール「勤務シフト作成お助けマン」は、自動でシフト表を作成するクラウドサービスです。エクセルをベースにしているため、見た目や使い勝手はそのまま。エクセルのデータをコピー＆ペーストできるため、これまでと同様のインターフェースで作業が可能です。

「勤務シフト作成お助けマン」には、早番・遅番・夜勤等の「1日1記号を割り当てるシフト表」を作成できる「勤務シフト作成お助けマンDay」と、10:00〜17:30等の「時間を割り当てるシフト表」を作成する「勤務シフト作成お助けマンTime」があります。作成したいシフト表に合わせてサービスを選べます。

本番利用時と同じ機能を2か月無料でトライアルできます。ぜひ、自社に合った方法でシフト表を作成・管理しましょう。