シフト表をエクセルで自動作成する方法

制度・業界の動き公開 2026年11月1日更新 2026年11月8日11分で読めます

エクセルでシフト表を自動作成するといっても、関数で自動にできるのは、日付と曜日を並べること、勤務の記号を一覧から選ばせること、日ごとの人数と不足を数えること、決まりから外れた人に色をつけることの4つです。誰をどの日に入れるかという割り当てそのものは、関数だけでは決まりません。それでも、人数の数え漏れや休憩の付け忘れを式に見張らせておくと、月末にシフト表を何度も見直す手間はかなり減ります。倉庫の場合はさらに、時間帯ごとの必要人数と、フォークリフトに乗れる人の数を同じ表で数えられるかどうかが、使えるひな形かどうかの分かれ目になります。

この記事では、COUNTIF・SUMPRODUCT・条件付き書式・データの入力規則だけで組む手順を、式をそのまま書きながら説明します。後半では、倉庫でエクセルのシフト表が詰まりやすいところと、専用のソフトに移る目安も取り上げました。記事の式で組んだひな形は、おまけのExcelとしてそのまま使えます。

エクセルのシフト表は、どこまで自動にできるのか

自動にできるのは「並べる・選ばせる・数える・知らせる」までで、割り当ての判断は人に残ります。シフト作成ソフトの「自動作成」は、スタッフの希望や技能をもとに割り当てまで提案してくれますが、エクセルの関数は入っている値を計算するだけで、空いたマスに人を入れてはくれません。ただ、担当者が毎月いちばん時間を取られているのは、割り当てを考えることより、入れた結果を数え直して足りない日を探すほうです。そこを式に任せるだけでも、表を作る作業は「埋めて、赤いところを直す」の繰り返しに変わります。表1は、シフト表を作るときの作業ごとに、エクセルでどこまで自動にできるかを並べたものです。

表1　シフト表の作業と、エクセルで自動にできる範囲

作業	エクセルでの自動化	使う機能
日付と曜日を並べる	年と月を入れれば自動	DATE・MONTH・WEEKDAY
土日と祝日に色をつける	自動	条件付き書式・COUNTIF
勤務の記号を入れる	一覧から選ぶだけにできる	データの入力規則
日ごと・時間帯ごとの人数	自動	COUNTIF・SUMIF・SUMPRODUCT
不足や上限超えを知らせる	自動で色や文字が出る	条件付き書式・IF
誰をどの日に入れるか	人が決める	（ソルバーで試せるが設定が重い）

表1の最後の行は、エクセルのソルバーというアドインを使えば、条件を満たす割り当てを計算させることもできます。ただ、条件を一つずつ数式に書き直す必要があり、スタッフの入れ替わりや希望の変更があるたびに設定を直すことになるので、毎月の運用に乗せている現場は多くありません。この記事では、割り当ては人が決め、残りの5つを式に任せる形を前提に進めます。

ひな形は、どんなシートに分けて組めばいいのか

設定・スタッフ・必要人数・シフト表の4枚に分け、毎月さわるのはシフト表だけにします。1枚のシートに勤務時間もスタッフの情報も書き込むと、早番の開始時刻を変えたときに、どの式を直せばいいのかが作った本人にしか分からなくなります。勤務の記号と時間を「設定」に、人の情報を「スタッフ」に、要る人数を「必要人数」に切り出しておけば、シフト表の式はそれを参照するだけで済みます。属人化しやすいのは表そのものより、どこに何の前提が書いてあるかが見えないことなので、シートを分けるのはそのまま引き継ぎの備えにもなります。図1は、4枚のシートの関係を示したものです。

図1　シフト表のひな形を4枚のシートに分ける

設定：勤務の記号、開始と終了の時刻、休憩、時間帯の区切り、祝日

▼

スタッフ：名前、雇用の形、フォークリフトに乗れるか、週の上限時間

必要人数：曜日ごと・時間帯ごとに要る人数

▼

シフト表：記号を選ぶと、人数・不足・週の実働が自動で出る

設定のシートでは、記号ごとに開始・終了・休憩を入れ、実働の時間を式で出しておきます。実働は「=(終了−開始)*24−休憩/60」で求まり、時刻はエクセルの中では1日を1とした小数なので、24を掛けると時間になります。そのうえで、2時間ごとの時間帯それぞれについて、その記号の人がその時間帯をまるごと働くなら1、そうでなければ0を式で出します。表2は、おまけのExcelに入れてある記号の例です。

表2　勤務の記号と、数える時間帯の例

記号	勤務時間	休憩	実働	人数に数える時間帯
早	8:00〜17:00	60分	8時間	8時〜16時
中	10:00〜19:00	60分	8時間	10時〜18時
遅	13:00〜22:00	60分	8時間	14時〜22時
短	9:00〜14:00	なし	5時間	10時〜14時
夕	17:00〜22:00	なし	5時間	18時〜22時

時間帯は2時間ごと。途中から入る記号（13時から入る遅番の12時〜14時など）は、その時間帯には数えない設定にしています。

表2のように途中から入る人を数えないのは、人数を少なめに見積もるほうに倒すためです。12時〜14時の時間帯に13時から1人入っても、前半の1時間は人がいないので、そこを1人と数えると昼の山で足りなくなります。時間帯の幅を1時間に細かくすれば実態に近づきますが、そのぶん集計の行が倍になるので、まずは2時間で始めて、足りなさが見えてきた時間だけ細かくするのが扱いやすいと思います。

日付と曜日は、どうすれば毎月自動で並ぶのか

1日目にDATE関数を置き、2日目からは「前の日＋1」を、月が変わったら空白にする式で並べます。こうしておくと、シフト表の上にある年と月のセルを書き換えるだけで、31日ある月も28日の月も正しい日数だけ日付が並びます。曜日は日付から計算できるので、手で打つ必要はありません。表3は、日付と曜日、土日と祝日の色分けに使う式をまとめたものです。式の中のE8が1日目の日付、C6とE6が年と月を入れるセルです。

表3　日付と曜日を自動で並べる式

置く場所	式	はたらき
1日目の日付	=DATE($C$6,$E$6,1)	年と月から1日を作る
2日目以降	=IF(E8=””,””,IF(MONTH(E8+1)=$E$6,E8+1,””))	翌日が同じ月なら表示、次の月なら空白
曜日	=IF(E8=””,””,MID(“月火水木金土日”,WEEKDAY(E8,2),1))	月曜を1とする番号から曜日の字を取る
土日の色（条件付き書式）	=AND(E$8<>””,WEEKDAY(E$8,2)>=6)	土曜と日曜の列に色をつける
祝日の色（条件付き書式）	=COUNTIF(祝日の一覧,E$8)>0	祝日の一覧にある日に色をつける

WEEKDAY関数は2番目の引数で番号の振り方が変わり、2を指定すると月曜が1、日曜が7になります。このため「6以上」と書けば土曜と日曜をまとめて拾えます。条件付き書式は、［ホーム］の［条件付き書式］から［新しいルール］を開き、「数式を使用して、書式設定するセルを決定」を選んで上の式を入れます。E$8のように行だけに$を付けるのがこつで、こうすると表の下の行まで同じ日付の行を見に行くので、列ごとまとめて色がつきます。祝日は自動では判定できないので、設定のシートに一覧を作っておき、COUNTIFでその日が一覧にあるかを調べます。

勤務の記号を一覧から選ばせるのは、なぜか

記号の書き方がそろわないと、COUNTIFが数え漏らして人数が合わなくなるからです。COUNTIFは指定した文字と完全に一致するセルだけを数えるので、「早」と「早番」、全角と半角、後ろに空白が入った「早」は別のものとして扱われます。何人もが同じ表に入力する倉庫では、こうした揺れが月に何か所も混ざり、集計の人数が実際より少なく出ます。データの入力規則で記号をリストから選ぶ形にすれば、そもそも違う書き方が入りません。

設定の仕方は、記号を入れるマスをまとめて選び、［データ］タブの［データの入力規則］で、入力値の種類を「リスト」にして、元の値に「=設定!$B$8:$B$15」のように設定のシートの記号の範囲を指定します。記号を足したくなったら設定のシートの空いた行に書くだけで、ドロップダウンにも集計にも反映されます。ひとつ注意したいのは、別のファイルからコピーして貼り付けると、入力規則ごと上書きされてしまうことです。先月の表から写すときは、［形式を選択して貼り付け］で値だけを貼ると、選択の仕組みが残ります。

日ごとの人数と時間帯ごとの不足は、どう数えるのか

記号ごとの人数はCOUNTIFで、時間帯ごとの人数はSUMPRODUCTとSUMIFの組み合わせで数えます。記号ごとの人数は簡単で、ある日の列に「早」がいくつあるかを「=COUNTIF(E$10:E$39,”早”)」で数えれば出ます。難しいのは時間帯ごとの人数で、14時〜16時には早番も中番も遅番もいるので、記号ごとに数えた人数を足し合わせる必要があります。そこで、その日の列の記号を一つずつ設定のシートで引き、その時間帯の1か0を合計する式にします。表4は、集計に使う式をまとめたものです。

表4　人数と不足を数える式

数えるもの	式（1日目の列の例）
記号ごとの人数	=COUNTIF(E$10:E$39,”早”)
時間帯ごとの配置人数	=SUMPRODUCT(SUMIF(設定!$B$8:$B$15,E$10:E$39,設定!J$8:J$15))
その日の必要人数	=INDEX(必要人数!C$6:C$13,MATCH(曜日か「祝」,必要人数!$B$6:$B$13,0))
不足（マイナスが足りない）	=配置人数−必要人数　（0より小さければ条件付き書式で赤）
フォークリフトに乗れる人の出勤	=SUMPRODUCT(($D$10:$D$39=”○”)*SUMIF(設定!$B$8:$B$15,E$10:E$39,設定!$H$8:$H$15))

設定!J列はその時間帯に数えるかの1と0、設定!H列は出勤なら1の列。必要人数は、祝日の一覧にある日は「祝」の行を引く。

2行目の式は、SUMIFの条件にその日の列の30マスをまとめて渡しているのがポイントです。SUMIFは30マス分の答えを並べて返し、SUMPRODUCTがそれを足し上げるので、記号が何種類あっても式は1本で済みます。フォークリフトの行も同じ考え方で、「○」の人だけに1を掛けてから足しています。複数の条件で数えるならCOUNTIFSも使えますが、条件の範囲はすべて同じ行数と列数にそろえる決まりがあるので、表の形を変えたときに壊れやすくなります。図2は、おまけのExcelの例で、ある平日の時間帯ごとの配置と必要人数を比べたものです。

図2　ある平日の時間帯ごとの配置人数と必要人数（おまけExcelの例）

8〜10時配置

1人

8〜10時必要

2人

10〜12時配置

5人

10〜12時必要

4人

14〜16時配置

5人

14〜16時必要

5人

20〜22時配置

4人

20〜22時必要

2人

12月3日（木）の例。濃い棒が配置、薄い棒が必要人数。差がマイナスの時間帯はシフト表で赤く表示される。

図2の日は、朝の8時〜10時が1人足りない一方で、夜の20時〜22時は2人多く入っています。1日の合計人数だけを見ていると、この偏りは見えません。この日なら遅番の1人を早番に替えるだけで、14時〜16時の人数を変えずに朝の不足が埋まり、夜の余りも減ります。時間帯ごとに並べるのは、人を足すより先に、入れ替えで直せる日を見つけるためでもあります。

休憩と休日の決まりは、式でどう見張るのか

労働基準法の休憩・休日・労働時間の線を式にしておくと、表を作った時点で抜けに気づけます。法律が決めている線のうち、シフト表で確かめやすいのは3つです。1日8時間・週40時間を超えて働かせるには36協定が要ること（32条）、労働時間が6時間を超えたら45分以上、8時間を超えたら1時間以上の休憩を途中に与えること（34条）、毎週少なくとも1回か、4週間を通じて4日以上の休日を与えること（35条）です。表5は、これらと社内の決まりを、ひな形の中でどう見張っているかを並べたものです。

表5　決まりと、ひな形での見張り方

決まり	線	ひな形での見張り方
休憩	6時間超で45分、8時間超で1時間（34条）	設定のシートで実働と休憩を比べ、足りない記号に文字を出す
労働時間	1日8時間・週40時間（32条）	1週ごとの実働が40時間を超えた人に文字を出す
休日	毎週1回か、4週4日（35条）	月の公休が4日未満の人に文字を出す
連続の勤務	社内の目安	休みなしで6日目になったマスをオレンジにする
週の上限時間	雇用契約や社会保険の線	スタッフごとの上限を超えた週に文字を出す

休みなしで6日続いたら色をつける式は、6日目のマスに「=AND(COUNTBLANK(E10:J10)=0,COUNTIF(E10:J10,”休”)=0)」のような条件付き書式を入れ、表の右へずらして使います。6連勤そのものが法律違反になるわけではありませんが、7日目も入れると週1回の休日が取れなくなるので、その手前で気づけるようにしておく意味があります。パートの週の時間は、社会保険や雇用保険の加入の線と関わるので、人ごとの上限を「スタッフ」のシートに書いて比べます。シフト制で働く人の労働条件の決め方と、週20時間の線の扱いは、倉庫のシフト作成が属人化する理由の記事で詳しく書いています。

式の判定は目安で、最後は就業規則で確かめます。4週4日の休日は、就業規則で決めた起算日から4週ずつ数えるので、ひな形のように月の1日から区切った数え方とは一致しないことがあります。前の月から続いている連勤も、月をまたぐと数えられません。式は気づくための仕組みと考え、変形労働時間制を使っている場合などは、労務の担当者と線の引き方を決めてから式に落とすのが安全です。

倉庫でエクセルのシフト表が詰まるのは、どこか

いちばん詰まるのは式ではなく、必要人数の行を何から埋めるかです。ひな形の「必要人数」は曜日ごとに固定した数字を入れる形なので、月末の締めや荷主の販促、連休の前後のように物量が跳ねる日には、表の上では足りていても現場では足りません。本来は、その日に処理する見込みの物量を、1人1時間あたりにこなせる量で割って人数を出し、それを必要人数の行に入れるのが筋です。この「1人1時間あたりの量」は人時生産性そのもので、実績から工程ごとに出しておくと、人数の見積もりがぶれにくくなります。物量の見込みをエクセルで立てる方法は、需要予測をエクセルでやる方法の記事にまとめました。

表6　倉庫で手が止まりやすいところと、手当て

詰まるところ	起きること	手当て
必要人数が曜日で固定	月末や販促の日に足りなくなる	物量の見込みから日ごとの人数を出して入れる
工程が増える	ピッキング・検品・梱包ごとに集計が膨らむ	記号に工程を含め（「早P」「早K」など）、工程ごとに必要人数の表を分ける
前日の変更	確定版と手直し版が混ざる	確定した時点でシートをコピーし、日付を付けて残す
複数人での編集	式のセルを上書きされる	シートの保護で、記号を入れるマスだけ編集できるようにする
日をまたぐ夜勤	22時から翌朝の勤務が数えられない	終了を24時以降で入れる式に変えるか、夜勤だけ別の表にする

表6の下の4つは、エクセルの工夫でかなりの部分を手当てできます。式のセルを守るシートの保護と、一覧から選ばせる入力規則は、在庫管理表をエクセルで作る方法の記事でも同じ考え方で組んでいます。手に負えなくなるのは、拠点が複数になって人の貸し借りが日常になったときや、スタッフが自分のスマートフォンで希望を出し、確定したシフトをすぐ見られる仕組みが要るようになったときです。そうなったらシフト管理のソフトに移す時期ですが、移しても必要人数を何から決めるかという1つ目の問題は残ります。私たちが倉庫の方とお話しするときも、表の作り方より、出荷物量から必要人員を決めるところで相談を受けることがほとんどです。

エクセルのシフト表のままでよいかの確認

シフトを組む拠点が1か所で、人の貸し借りが少ない
スタッフの数が、1枚の表で見渡せる人数に収まっている
希望の受け付けと確定版の連絡が、今のやり方で回っている
式のセルを触れるのが、決まった1〜2人に限られている
必要人数の行を、物量の見込みから毎月入れ直せている

よくある質問

マクロ（VBA）がないと、シフト表の自動作成はできませんか

日付の表示、記号の選択、人数と不足の集計、決まりの見張りまでは、関数と条件付き書式だけでできます。マクロを使うと割り当ての下書きまで作れますが、社内の設定でマクロが止められていることも多く、作った人がいなくなると直せなくなりがちです。まず関数だけで組み、それでも手が回らない作業が残ったときに考えるので十分です。

Googleスプレッドシートでも同じ式で動きますか

COUNTIF・SUMIF・SUMPRODUCT・INDEX・MATCH・WEEKDAY、入力規則のリストはスプレッドシートにもあるので、集計の式はおおむねそのまま動きます。ただ、条件付き書式の中で別のシートを直接参照できないため、祝日の色分けはINDIRECT関数を使う形に書き換える必要があります。

祝日の一覧はどこから取ればいいですか

内閣府の「国民の祝日について」のページに、1955年から2027年までの祝日を収めたCSVのファイルがあります。翌年分は毎年2月ごろに公表されるので、年に一度、設定のシートの一覧に足すと決めておくと漏れません。

人が足りない日に、自動で人を足してくれる式は作れますか

関数だけでは作れません。足りない時間帯が赤く出たら、その日に休みで、週の上限時間に余裕がある人を探して入れる、という手順を人が踏むことになります。スタッフのシートに週の実働の合計を出しておくと、声をかける相手を選びやすくなります。

執筆

株式会社MatrixFlow需要予測AI「MatrixFlow」の開発元。内閣府SIP 国家実証プロジェクトに参画し、物流センターの出荷物量予測と人員・配車計画の支援を行っています。記事の内容は公開時点の情報です。