CONCEPT — SHIFT EXCEL
シフト管理をエクセルで作る方法:列・必要人数と不足の数式・移す目安
シフト管理をエクセルで行うとは、名前を縦に、日付を横に並べた表に勤務の区分(早・遅・休など)を入れ、出勤人数と必要人数の差をCOUNTIFなどの関数で数えて、足りない日を見つけることです。この記事では、店舗のアルバイト・パートとイベントのボランティアの両方に使える列の作り方、不足を出す数式、Excelのままでよい規模と、仕組みに移す目安を、説明用の仮想例の表で書きます。
シフト管理をエクセルで行うとは:できることと、できないこと
Excelのシフト表でできるのは、勤務の区分を入れたセルを数えて、日ごとの出勤人数・人ごとの出勤日数・必要人数との差を自動で出すことです。印刷して掲示する、月ごとにシートを複製する、もExcelの得意な作業です。
一方で、Excelの表だけでは手が届かない作業もあります。スタッフのスマートフォンから希望を集める、確定した表を全員に通知する、同じファイルを店長と副店長が同時に直す、といった作業です。表の外のLINEや紙で補うと、希望と確定と出欠が別々の場所に残ります。
この記事の表は、店舗のアルバイト・パートとイベントのボランティアの両方に使えます。役割の列に「レジ・品出し」と書くか「受付・誘導」と書くかが違うだけで、数え方は同じです。
- 今のシフト表の列名を送って、Excelのままでよいかを聞く → 1行目の列名と、毎月の人数・組む人の人数が分かれば答えられます
シフト表の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<>"休"),"確認","")
条件付き書式は、不足の行(D10からF10)を選び、「セルの値が1以上」のときに赤い塗りつぶしにします。冒頭の図では、11月2日の早番が必要3人に対して2人なので、不足のセルが1になり赤く出ます。
役割ごとの人数を数えるCOUNTIFSは、「受付ができる人が早番に何人いるか」を見るためです。人数は足りていても受付が0人、という日を見つけられます。不可の曜日の確認は、曜日の列が増えるほど式が長くなるので、人数が増えたら確認用の列を1つにまとめるか、次の節の目安で仕組みに移すかを考えます。
今の表に数式を入れても不足が合わないときは、勤務のセルの書き方のゆれ(全角と半角、「早番」と「早」)か、数える範囲の$の付け忘れであることが多いです。
- 必要人数の行と今の数式を送って、不足が合わない原因を一緒に見る → 表の1行目と、数式を入れたセルのスクリーンショットがあれば始められます
希望から確定・出欠までを、同じ表の列で閉じる
数式が合っていても、希望はLINE、仮配置は担当者の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つに絞ります。
- スプレッドシートをデータベース代わりにしているとき → 行数より先に来る、表計算の役割の限界
- データベース化とは → Excelの表を、1件1行の台帳に移す手順
- エクセル出勤簿の書き方 → 出欠の列を、月末の勤怠集計へ渡すとき
「この人は土曜不可」を、担当者の頭から会社の人マスタへ移す
不可の曜日や「受付は経験者を1人入れる」といった組み方の決まりが、担当者の記憶とAIとの個人チャットにだけあると、担当が休んだ月は誰も表を組めません。
決まりは、シフト表とは別の「人マスタ」のシートに、1人1行で書きます。列は、名前、できる役割、不可の曜日、連続で出られる日数の上限、経験の有無、最後に直した日です。シフト表のB列とC列は、このシートから引いて埋めます。
組み方の決まり(受付は経験者を1人、未経験者を1人だけの枠にしない)は、同じブックの「ルール」シートに1行1つで書きます。AIにシフトの案を出させるときは、個人の会話履歴に条件を打ち込み直さずに、人マスタとルールのシートを渡します。案が出ても、確定日の欄を人が埋めるまでは仮のままにします。
担当が代わるときに渡すのは、このブックの置き場所だけで足ります。どの店舗・どのイベントで誰が表を組んでいるかを一覧にしたいときは、業務の棚卸しの手順で、組む人・希望の受け口・確定表の置き場所を1行ずつ書き出します。
- 属人化とは → 組み方の決まりが1人の頭にしかない状態の外し方
- 業務の棚卸しのやり方 → どこで誰が表を組んでいるかを一覧にするとき
よくある質問
Q. エクセルでシフトの不足を出すには、どの関数を使いますか?
日付の列ごとにCOUNTIFで勤務の区分(例:早)を数え、必要人数から引きます。=MAX(0,必要人数-出勤人数) にすると、人が余っている日は0になり、不足だけが残ります。役割ごとに数えるときはCOUNTIFSを使います。
Q. 必要人数はどうやって決めますか?
去年の同じ曜日の来客数や売上、イベントなら受付の窓口の数から、日ごと(または時間帯ごと)に決めます。決めた理由を表の右に1行残すと、担当が代わっても同じ数字を入れられます。
Q. 無料のテンプレートで始めてもよいですか?
始められます。使う前に、勤務のセルの区分(早・遅・休)を自社の言い方に揃え、不可の曜日と確定日の列があるかを確かめます。列が無いテンプレートは、足してから使います。
Q. シフト専用のアプリに乗り換えれば、エクセルの手間は消えますか?
希望・不可の曜日・役割・確定・出欠の列がアプリ側に持てないと、またExcelに書き戻します。先に今の表の列名を書き出し、アプリがその列を持てるかを見てから選びます。
Q. ボランティアとアルバイトで、表の作り方は変えますか?
名前・役割・不可の曜日・勤務・出勤日数の列と、不足を出す数式は同じです。ボランティアは保険・謝礼・次回募集へ、アルバイトは勤怠・給与へ渡す列を足します。
Q. シフト担当が代わったとき、何を渡せばよいですか?
シフト表、人マスタ(できる役割・不可の曜日・連続の上限)、組み方のルールのシートが入ったブックの置き場所です。口頭の「いつもの人」は、人マスタの行に直してから渡します。
今のシフト表を1枚送って、Excelのままでよいかを決める
シフト表の1行目の列名と、毎月の人数、組んでいる人の人数を書いて送ってください。不足の出し方の直し方と、Excelのままでよいか・どこから仕組みに移すかをお返しします。どの表から手を付けるかを2時間で決めたいときは、TOC分析 5万円(2時間) から始められます。