CONCEPT — SHIFT EXCEL

シフト管理をエクセルで作る方法:列・必要人数と不足の数式・移す目安

業務設計・仕組み化 sento.group 編集部 · · 約7分

Excelのシフト表の仮想例。名前・役割・不可の曜日・11月1日から3日の勤務・出勤日数の列があり、下の3行で早番の出勤人数と必要人数を並べ、11月2日の不足1を赤で示し、使うCOUNTIFとMAXの数式を添えた図
図:日付ごとに出勤人数を数え、必要人数との差で不足を出します(説明用の仮想例)。

シフト管理をエクセルで行うとは、名前を縦に、日付を横に並べた表に勤務の区分(早・遅・休など)を入れ、出勤人数と必要人数の差をCOUNTIFなどの関数で数えて、足りない日を見つけることです。この記事では、店舗のアルバイト・パートとイベントのボランティアの両方に使える列の作り方、不足を出す数式、Excelのままでよい規模と、仕組みに移す目安を、説明用の仮想例の表で書きます。

シフト管理をエクセルで行うとは:できることと、できないこと

Excelのシフト表でできるのは、勤務の区分を入れたセルを数えて、日ごとの出勤人数・人ごとの出勤日数・必要人数との差を自動で出すことです。印刷して掲示する、月ごとにシートを複製する、もExcelの得意な作業です。

一方で、Excelの表だけでは手が届かない作業もあります。スタッフのスマートフォンから希望を集める、確定した表を全員に通知する、同じファイルを店長と副店長が同時に直す、といった作業です。表の外のLINEや紙で補うと、希望と確定と出欠が別々の場所に残ります。

この記事の表は、店舗のアルバイト・パートとイベントのボランティアの両方に使えます。役割の列に「レジ・品出し」と書くか「受付・誘導」と書くかが違うだけで、数え方は同じです。

シフト表の3つの形:月ごと・週ごと・時間帯ごと

作り始める前に、表の横軸を何にするかを決めます。横軸が変わると、数える範囲と必要人数の入れ方が変わります。

  • 月ごとの表:横に1日から31日を並べ、セルに早・遅・休を入れます。店舗のアルバイトやパートで、月の初めに1か月分を出す職場に向きます。この記事の数式はこの形で書いています。
  • 週ごとの表:横に月曜から日曜を並べ、毎週作り直します。希望の出し直しが多い職場や、学生のスタッフが多く試験期間で予定が変わる職場に向きます。
  • 時間帯ごとの表:1日分を、横に9時・10時・11時と1時間刻みで並べ、出る時間に印を付けます。イベントのボランティアの受付・誘導や、昼の混む時間だけ人を増やす飲食店に向きます。必要人数は時間帯ごとに入れます。

月ごとの表で人数を数え、混む日だけ時間帯ごとの表を別シートに作る、という組み合わせもできます。その場合は、名前の列を同じ順に揃えておくと、2枚を見比べやすくなります。

エクセルでのシフト表の作り方:列を決める5つの手順

冒頭の図と同じ表を、空のシートから作る手順です。説明用の仮想例として、5人のスタッフと3日分で書きます。

  • 1行目に見出しを入れる:A1「名前」、B1「役割」、C1「不可の曜日」、D1から右に日付、最後の列に「出勤日数」。日付の下の段に曜日を出したいときは、=TEXT(D1,"aaa") で日付から曜日を出します。
  • 2行目から下に、1人1行で名前を入れる:役割(受付・誘導・倉庫など)と、出られない曜日も同じ行に書きます。「この人は日曜不可」を担当者の記憶に残さず、C列の値として残すための列です。
  • 勤務のセルに入力規則を付ける:D2から右下までを選び、データの入力規則で「リスト」を選び、元の値に「早,遅,休」と入れます。「早番」「はや」のような書き方のゆれがあると、あとの数式が数えません。
  • 表の下に3行を足す:「早番の出勤人数」「早番の必要人数」「不足」。遅番も数えるなら、同じ3行をもう1組足します。必要人数の行だけは、手で数字を入れます。
  • 右端の出勤日数と、下の3行に数式を入れる:数式は次の節に書きます。D列に入れて右へコピーし、出勤日数は2行目に入れて下へコピーします。

必要人数は、去年の同じ曜日の来客数や売上、イベントなら受付の窓口の数から、日ごと(または時間帯ごと)に決めて入れます。決めた理由を、表の右に「必要人数の根拠」の列として1行書いておくと、担当が代わっても同じ数字を入れられます。

必要人数と不足を関数で出す:COUNTIF・MAX・条件付き書式

不足を出す考え方は、日付の列ごとに「早」と書いたセルを数え、必要人数から引くことです。マイナスになる日は人が余っている日なので、0にそろえて不足だけを見ます。

出勤人数(D8)   =COUNTIF(D$2:D$6,"早")
必要人数(D9)   手で入れる(例:3)
不足(D10)      =MAX(0,D9-D8)
余り(D11)      =MAX(0,D8-D9)
出勤日数(G2)   =COUNTIF(D2:F2,"早")+COUNTIF(D2:F2,"遅")
役割ごとの人数   =COUNTIFS($B$2:$B$6,"受付",D$2:D$6,"早")
不可の曜日に入っていないか(H2)
                 =IF(AND(C2="日",E2<>"休"),"確認","")
説明用の仮想例。D列に入れてE・F列へコピーします。行番号の前の$は、右へコピーしても数える行がずれないようにする印です。

条件付き書式は、不足の行(D10からF10)を選び、「セルの値が1以上」のときに赤い塗りつぶしにします。冒頭の図では、11月2日の早番が必要3人に対して2人なので、不足のセルが1になり赤く出ます。

役割ごとの人数を数えるCOUNTIFSは、「受付ができる人が早番に何人いるか」を見るためです。人数は足りていても受付が0人、という日を見つけられます。不可の曜日の確認は、曜日の列が増えるほど式が長くなるので、人数が増えたら確認用の列を1つにまとめるか、次の節の目安で仕組みに移すかを考えます。

今の表に数式を入れても不足が合わないときは、勤務のセルの書き方のゆれ(全角と半角、「早番」と「早」)か、数える範囲の$の付け忘れであることが多いです。

希望から確定・出欠までを、同じ表の列で閉じる

数式が合っていても、希望はLINE、仮配置は担当者のExcel、確定はチャット、出欠は紙、と分かれていると、翌月にまた同じ聞き直しが起きます。

希望の収集、担当者Excelでの仮配置、チャットでの確定、紙の出欠までが左から右へ進み、制約が1人の頭に残る流れ
図:希望は集まるのに、仮配置の制約が1人のExcelに閉じます(説明用の仮想例)。
  • 希望:別シートに「名前・日付・希望(出・休)・提出日」の4列で受けます。フォームで集めるなら、フォームの回答シートをそのまま使います。
  • 確定:シフト表に「確定日」の欄を1つ置き、確定したら日付を入れます。セルの色を確定の印にしません。
  • 出欠:当日の担当が、確定表の隣に「出欠」と「欠けた理由(体調・交通・連絡なし)」の列を埋めます。振替は、元の枠を休にして、新しい枠を足します。

ボランティアなら、保険の対象者と謝礼の対象者をこの出欠の列から拾えます。アルバイトなら、同じ列が勤怠の下書きになります。次回の募集は「前回受付を希望した人」を希望シートから抜き出して始めます。

Excelのままでよい規模と、シフト管理の仕組みに移す5つの目安

Excelのままで回せるのは、組む人が1人で、希望の受け口が1つにまとまり、表を同時に直す人がいないうちです。次の5つのうち、3つ以上が当てはまったら、シフト管理のアプリや台帳の仕組みに移す時期です。

  • 希望が3か所以上から届く:紙・LINE・口頭・フォームが混ざり、Excelに打ち直している。
  • 確定後の変更を、毎回1人ずつ個別に連絡している:変更のたびに、誰に送ったかを覚えておく必要がある。
  • 同じ表を2人以上が直す:店長と副店長、本部と現場が、同じファイルを別々に開いて上書きする。
  • 店舗やイベントが2つ以上あり、同じスタッフを取り合う:Aの表とBの表を見比べないと、重なりに気づけない。
  • シフト表から勤怠・給与・謝礼の表へ、毎月手で写している:写す列と写す人が決まっていて、その人が休むと月末が止まる。

移すと決めたら、先に今の表の列名を書き出し、名前・役割・不可の曜日・希望・確定・出欠の6つが新しい仕組みにも持てるかを確かめます。列の意味を決めずにアプリを入れると、足りない列をまたExcelで補います。表計算をデータの置き場として使い続けるときの限界は、スプレッドシートをデータベース代わりにしているときの記事に、移し方はデータベース化の記事に分けて書いています。

どの表から移すかを2時間で決めたいときは、TOC分析 5万円(2時間) で、シフト表・希望の受け口・勤怠の表を並べ、月末に止まっている箇所を1つに絞ります。

「この人は土曜不可」を、担当者の頭から会社の人マスタへ移す

不可の曜日や「受付は経験者を1人入れる」といった組み方の決まりが、担当者の記憶とAIとの個人チャットにだけあると、担当が休んだ月は誰も表を組めません。

決まりは、シフト表とは別の「人マスタ」のシートに、1人1行で書きます。列は、名前、できる役割、不可の曜日、連続で出られる日数の上限、経験の有無、最後に直した日です。シフト表のB列とC列は、このシートから引いて埋めます。

組み方の決まり(受付は経験者を1人、未経験者を1人だけの枠にしない)は、同じブックの「ルール」シートに1行1つで書きます。AIにシフトの案を出させるときは、個人の会話履歴に条件を打ち込み直さずに、人マスタとルールのシートを渡します。案が出ても、確定日の欄を人が埋めるまでは仮のままにします。

担当が代わるときに渡すのは、このブックの置き場所だけで足ります。どの店舗・どのイベントで誰が表を組んでいるかを一覧にしたいときは、業務の棚卸しの手順で、組む人・希望の受け口・確定表の置き場所を1行ずつ書き出します。

よくある質問

Q. エクセルでシフトの不足を出すには、どの関数を使いますか?

日付の列ごとにCOUNTIFで勤務の区分(例:早)を数え、必要人数から引きます。=MAX(0,必要人数-出勤人数) にすると、人が余っている日は0になり、不足だけが残ります。役割ごとに数えるときはCOUNTIFSを使います。

Q. 必要人数はどうやって決めますか?

去年の同じ曜日の来客数や売上、イベントなら受付の窓口の数から、日ごと(または時間帯ごと)に決めます。決めた理由を表の右に1行残すと、担当が代わっても同じ数字を入れられます。

Q. 無料のテンプレートで始めてもよいですか?

始められます。使う前に、勤務のセルの区分(早・遅・休)を自社の言い方に揃え、不可の曜日と確定日の列があるかを確かめます。列が無いテンプレートは、足してから使います。

Q. シフト専用のアプリに乗り換えれば、エクセルの手間は消えますか?

希望・不可の曜日・役割・確定・出欠の列がアプリ側に持てないと、またExcelに書き戻します。先に今の表の列名を書き出し、アプリがその列を持てるかを見てから選びます。

Q. ボランティアとアルバイトで、表の作り方は変えますか?

名前・役割・不可の曜日・勤務・出勤日数の列と、不足を出す数式は同じです。ボランティアは保険・謝礼・次回募集へ、アルバイトは勤怠・給与へ渡す列を足します。

Q. シフト担当が代わったとき、何を渡せばよいですか?

シフト表、人マスタ(できる役割・不可の曜日・連続の上限)、組み方のルールのシートが入ったブックの置き場所です。口頭の「いつもの人」は、人マスタの行に直してから渡します。

今のシフト表を1枚送って、Excelのままでよいかを決める

シフト表の1行目の列名と、毎月の人数、組んでいる人の人数を書いて送ってください。不足の出し方の直し方と、Excelのままでよいか・どこから仕組みに移すかをお返しします。どの表から手を付けるかを2時間で決めたいときは、TOC分析 5万円(2時間) から始められます。

無料相談を予約する