Excelでシフト表を自動作成する方法｜関数だけで集計・過不足チェックまで自動化

2026年6月6日 23:48

「毎月のシフト作成に何時間もかかっている」「人数の過不足を手で数えていてミスが出る」——飲食店や小売、クリニックなど、少人数のシフトを組む現場で必ず出てくる悩みです。

この記事では、Excelの標準機能（関数とデータの入力規則）だけで、シフト表の集計・人件費計算・人手不足の警告まで自動化する方法を、コピペで使える数式付きで解説します。専用ソフトもマクロも不要です。

この記事でできるようになること

まず別シート（例：「設定」）に、シフトの種類と1コマあたりの労働時間を入力します。

あわせて「1日あたりの必要人数（例：3）」も入力しておきます。後で過不足の判定に使います。記号を1文字（早・遅・夜・休）に統一しておくのが、後の集計をシンプルにするコツです。

シフト表シートの上部に「年」と「月」のセルを用意します（仮にB3＝年、E3＝月とします）。1日〜31日の数字を横に並べ、その下の行に次の数式を入れると、曜日が自動表示されます。

=IF(日付セル<=DAY(EOMONTH(DATE($B$3,$E$3,1),0)),
   CHOOSE(WEEKDAY(DATE($B$3,$E$3,日付セル)),"日","月","火","水","木","金","土"),
   "")

ポイントは2つ。EOMONTHでその月の末日を求め、月末を超える日付（2月の30日など）は自動で空欄にしています。CHOOSE＋WEEKDAYを使うことで、PCの言語設定に関係なく必ず日本語の曜日が出ます。

シフトを入力するセル範囲を選択し、「データ」→「データの入力規則」→「リスト」を選び、元の値に次を入力します。

早,遅,夜,休

これで各セルがドロップダウンになり、表記ゆれ（「早番」「はやばん」など）が起きません。集計が確実に効くようになります。

各日付の列について、出勤人数は「入力されたセル数から『休』を引く」ことで求められます。

出勤人数 =COUNTA(その列のスタッフ範囲)-COUNTIF(その列のスタッフ範囲,"休")

そのうえで、過不足は単純な引き算です。

過不足 =出勤人数セル - 必要人数セル

必要人数は「設定」シートの値を参照しつつ、土日だけ数字を上書きすれば、曜日ごとに必要人数を変えられます。

スタッフごとの行で、各区分の出現回数に労働時間を掛けて合計します。

総労働時間 =COUNTIF(その人の行,"早")*早の時間
          +COUNTIF(その人の行,"遅")*遅の時間
          +COUNTIF(その人の行,"夜")*夜の時間

人件費は、総労働時間に時給を掛けるだけです。

人件費 =総労働時間 * 時給

時給を「スタッフ名簿」シートから参照しておけば、時給改定のときも1か所直すだけで全体に反映されます。

過不足の行を選択し、「条件付き書式」→「セルの強調表示ルール」→「指定の値より小さい」→「0」を選び、赤い塗りつぶしを設定します。これで、必要人数に足りない日が一目で赤く浮かび上がり、組み直しの判断が一瞬でできます。

ここまでの仕組みは「一度作れば毎月使い回せる」ものですが、数式・入力規則・条件付き書式を最初から正しく組むのは、慣れていないと半日仕事です。1か所でも参照がずれると集計が狂い、原因探しでさらに時間を取られます。

そこで、この記事の内容をすべて組み込み済みの完成テンプレートを用意しました。

毎月のシフト作成を「数時間」から「数分」に変えるためのテンプレートです。ダウンロード後すぐに自分の店舗用に書き換えて使えます。

Excelのシフト表は、DATE／EOMONTH／COUNTA／COUNTIFと条件付き書式を組み合わせれば、集計から人件費、人手不足の警告まで自動化できます。まずはこの記事の数式を試してみてください。「作る時間すら惜しい」という方は、完成テンプレートを使えば今日から運用を始められます。

シフト（予定）と、実際に働いた時間（実績）は別物です。労働安全衛生法66条の8の3により、労働時間は客観的な方法で把握し、記録を3年間保存することが求められます。管理監督者も対象です。

シフト管理をExcelで続けていい会社と、そろそろ限界の会社の見分け方を整理しました。

出勤・退勤から残業・深夜まで自動集計する勤怠表はこちら（無料）。

ここまで読んでいただきありがとうございます。記事やテンプレがお役に立ったら、応援（チップ）していただけると嬉しいです。いただいた応援は、より使いやすいテンプレづくりの活動費に使わせていただきます📊 凪