エクセル当番表のローテーションを関数で自動化｜土日祝を除いて順番に割り当てる方法

2026年6月30日2026年9月15日

独立した関数の歯車を組み合わせて手作業のミスをゼロにするエクセル当番表自動化の全体設計図スライド

こんにちは、SkillStack Lab運営者のスタックです。

Excelで当番表を作るたびに、「土日祝を1日ずつ消すのが面倒」「担当者を順番に割り当てたい」「毎月メンバーの回数が偏っていないか確認するのが大変」と感じていませんか。

結論から言うと、当番表は、WORKDAY・MOD・INDEX・COUNTIFを組み合わせれば、土日祝を除外しながら担当者を順番にローテーションできます。

土日以外が休みの職場なら、WORKDAY.INTLを使えば火曜・木曜休みなどの変則的なカレンダーにも対応可能です。

私自身、情シス時代に当番表を手作業で管理し、会社の創立記念日と振替休日を見落として、休業日に当番を割り当ててしまった経験があります。

この記事では、その失敗も踏まえながら、対象月を入力するだけで日付と担当者が自動で並ぶ当番表を、実際のセル配置と数式まで含めて作ります。

この記事で作る当番表

対象月を変更すると当番日が自動で切り替わる
土曜日・日曜日・祝日・会社休日を自動で除外する
担当者をAさん→Bさん→Cさんの順番で繰り返す
翌月の開始担当者を変更して偏りを減らせる
COUNTIFで各メンバーの当番回数を確認できる
土日以外が休みの職場にも対応できる

エクセル当番表ローテーションの完成形

最初に、今回作る当番表の全体像を確認しましょう。

シート上には、次の項目を用意します。

セル・範囲	入力する内容	用途
H1	対象月の1日	例：2026/9/1
H2	開始番号	1なら名簿1人目から開始
H3	総当番日数	NETWORKDAYSで自動計算
F2:F6	担当者名	Aさん、Bさん、Cさん…
A2:A40	当番日	WORKDAYで自動生成
B2:B40	曜日	日付から自動表示
C2:C40	担当者	INDEX・MODで自動割当
G2:G6	当番回数	COUNTIFで集計

担当者リストのF2:F6には、空白を入れず連続して名前を登録してください。

さらにF2:F6を選択し、Excelの「名前の定義」でStaffListという名前を付けておきます。

同じように、別シートへ祝日や会社休日を縦に並べ、その範囲へHolidayListという名前を付けます。

Excelは日本の祝日を自動的に除外してくれるわけではありません。
WORKDAYで祝日も飛ばすには、国民の祝日や会社独自の休業日をHolidayListへ登録してください。

国民の祝日は、内閣府「国民の祝日について」で確認できます。

土日祝を除いた当番日をWORKDAYで自動生成する

まず、A列へその月の当番日だけを自動表示します。

A2へ入れる完成形の数式

H1へ対象月の1日を入力した状態で、A2へ次の数式を入力してください。

=IF(WORKDAY($H$1-1,ROWS($A$2:A2),HolidayList)>EOMONTH($H$1,0),"",WORKDAY($H$1-1,ROWS($A$2:A2),HolidayList))

そのままA40程度までコピーします。

例えばH1が「2026/9/1」なら、9月の土日とHolidayListに登録した休日を除外しながら、営業日だけが上から順番に並びます。

翌月の日付に到達したら空欄になるため、月の日数に合わせて毎回数式を消す必要もありません。

数式の仕組み

この式では、主に3つの処理を行っています。

WORKDAY：土日とHolidayListの休日を飛ばす
ROWS：下へコピーするたびに1日、2日、3日と営業日を進める
EOMONTH：対象月の月末を超えたら空欄にする

H1から1を引いているのは、月初そのものが営業日だった場合に、その日から表示するためです。

例えば9月1日が営業日なら、WORKDAY(8月31日,1,...)によって9月1日が返されます。

B列へ曜日を自動表示する

B2には、次の数式を入れます。

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

A2が日付なら「月」「火」「水」のように曜日が表示され、A2が空欄ならB列も空欄になります。

スタック

私は情シス時代、当番表を手作業で作っていて、創立記念日と振替休日を見落としたことがあります。休日を目視で判断せず、別の休日マスターとして管理するだけでも確認漏れを減らしやすくなります。

WORKDAY.INTLで土日以外の休業日にも対応する

土日休みではない職場の場合は、WORKDAYではなくWORKDAY.INTLを使います。

WORKDAY.INTLでは、月曜日から日曜日までを7桁の「0」と「1」で指定できます。

0＝稼働日、1＝休業日です。

指定	休業日	利用例
`"0000011"`	土・日	一般的なオフィス
`"0101000"`	火・木	火曜・木曜定休の店舗
`"1000000"`	月	月曜定休の施設

例えば火曜と木曜を除外するなら、A2の式を次のように変更します。

=IF(WORKDAY.INTL($H$1-1,ROWS($A$2:A2),"0101000",HolidayList)>EOMONTH($H$1,0),"",WORKDAY.INTL($H$1-1,ROWS($A$2:A2),"0101000",HolidayList))

7桁の1文字目が月曜日、7文字目が日曜日です。

店舗・クリニック・工場など、曜日固定の休業日が土日ではない場合に便利です。

MODとINDEXで担当者を順番にローテーションする

日付が完成したら、次はC列へ担当者を自動で割り当てます。

例えばStaffListが次の5人だったとします。

Aさん
Bさん
Cさん
Dさん
Eさん

5人目のEさんまで進んだら、次はAさんへ戻したいですよね。

そこで使うのがROW・MOD・INDEXです。

C2へ入れるローテーション数式

C2へ次の数式を入力し、C40程度までコピーしてください。

=IF(A2="","",INDEX(StaffList,MOD(ROW()-ROW($C$2)+$H$2-1,ROWS(StaffList))+1))

H2へ「1」と入力するとStaffListの1人目から始まり、次のように繰り返します。

当番日	担当者
1営業日目	Aさん
2営業日目	Bさん
3営業日目	Cさん
4営業日目	Dさん
5営業日目	Eさん
6営業日目	Aさん

MOD関数が順番を繰り返す仕組み

MODは、割り算をしたときの「余り」を返す関数です。

5人でローテーションする場合、行が増えるたびに計算結果を5で割った余りが次のように変化します。

0 → 1 → 2 → 3 → 4 → 0 → 1 → 2…

最後に1を足して、INDEXへ渡す番号を

1 → 2 → 3 → 4 → 5 → 1 → 2…

と繰り返しています。

INDEXは、その番号をStaffListの何番目の人かへ変換します。

今回のように複数の関数を組み合わせてExcel業務を自動化できるようになりたい方は、UdemyでExcelを体系的に学ぶおすすめ講座も参考にしてください。関数だけでなく、Power QueryやVBAまで目的別に比較しています。

WORKDAYによる日付生成とROW・MOD・INDEXによる担当者ローテーションを組み合わせた当番表

開始番号を変えて毎月同じ人から始まる偏りを防ぐ

単純なローテーションには、もう一つ注意点があります。

毎月必ずAさんから開始すると、営業日数がメンバー数で割り切れない月では、いつも名簿の前半にいる人が1回多く担当する可能性があります。

そこで利用するのがH2の開始番号です。

H2	最初の担当者
1	Aさん
2	Bさん
3	Cさん
4	Dさん
5	Eさん

例えば9月の最後がCさんなら、翌月はDさんから開始するなど、月をまたいで順番をつなげると回数の偏りを抑えやすくなります。

「毎月Aさんからスタート」にしないことがポイントです。
単純な順番ローテーションでも、開始担当者を引き継ぐだけで長期的な偏りを減らしやすくなります。

NETWORKDAYSでその月の総当番日数を確認する

次に、その月に何日分の当番があるのかを確認します。

H3へ次の式を入力します。

=NETWORKDAYS($H$1,EOMONTH($H$1,0),HolidayList)

これで、対象月の土日とHolidayListを除いた総稼働日数が表示されます。

例えば21日なら、その月には当番枠が21回あることになります。

担当者が7人なら、全員3回ずつでちょうど一巡します。

変則休日ならNETWORKDAYS.INTLを使う

WORKDAY.INTLで火曜・木曜を除外した場合は、総当番日数も同じ休日設定で計算しなければ数字が合いません。

その場合はH3を次のようにします。

=NETWORKDAYS.INTL($H$1,EOMONTH($H$1,0),"0101000",HolidayList)

WORKDAY.INTLとNETWORKDAYS.INTLでは、同じ休業日の指定を使うと覚えておきましょう。

COUNTIFで当番回数の偏りを確認する

ローテーションを作ったら、実際に各担当者が何回割り当てられているかを確認します。

F2にAさんの名前がある場合、G2へ次の式を入力します。

=COUNTIF($C$2:$C$40,F2)

G2を下へコピーすると、StaffListに登録した各担当者の当番回数を一覧で確認できます。

1日1人を単純に順番で割り当てるだけなら、当番回数の差は基本的に0回または1回に収まります。

ただし、回数が同じだから負担も同じとは限りません。

月末、繁忙日、特定曜日など、負担が大きい日ばかり特定の人へ集中していないかは別途確認してください。

条件付き書式で回数の多い人を見つける

G列の当番回数へ条件付き書式を設定しておくと、突出した数値を視覚的に確認できます。

ただし条件付き書式は、あくまで確認を補助する機能です。

「赤くならなければ勤務上問題ない」といった労務判断には使わないでください。

祝日マスターへ会社独自の休日も追加する

当番表を長く使うなら、祝日を数式へ直接書き込むのではなく、休日だけを別シートにまとめておく方が管理しやすくなります。

例えば「祝日」シートへ次のように登録します。

日付	休日名
2026/9/21	敬老の日
2026/9/22	休日
2026/9/23	秋分の日
2026/10/1	会社創立記念日
2026/12/30	年末休業

WORKDAYから見れば、国民の祝日も会社の創立記念日も同じ「除外する日付」です。

HolidayListへまとめて登録しておけば、数式そのものを毎年書き換える必要はありません。

国民の祝日は年ごとに確認し、会社独自の年末年始休業・創立記念日・一斉休業日なども追加してください。

IFERRORでエラーを隠す前に原因を直す

元の当番表を他の人も操作する場合、「#VALUE!」や「#REF!」を見せたくないため、IFERRORを使いたくなるかもしれません。

例えば次のように記述できます。

=IFERROR(メインの数式,"設定を確認してください")

ただし、IFERRORはエラーの原因そのものを直す関数ではありません。

作成途中からすべての式をIFERRORで囲むと、参照範囲の間違いや休日マスターの問題に気づきにくくなる場合があります。

まずIFERRORなしで数式が正しく動くことを確認し、完成後に利用者向けの表示を整える目的で使う方がトラブルを切り分けやすくなります。

エクセル当番表を壊れにくくする5つの設定

数式が完成したら、ほかの人が操作しても壊れにくい状態にしておきましょう。

入力セルの色を変える
H1・H2など利用者が変更する場所を分かりやすくします。
数式セルを保護する
A列からC列の計算式を誤って削除しにくくします。
休日マスターを別シートにする
日付計算の設定と通常操作を分離します。
担当者名を直接数式に書かない
StaffListを変更するだけでメンバー変更へ対応しやすくします。
元ファイルのバックアップを残す
数式やマスターを壊した場合に戻せる状態にします。

連番や日付を自動生成する関数をさらに詳しく知りたい方は、Excel関数で連番・日付を自動生成する方法も参考にしてください。

エクセル当番表が向いているケース・向かないケース

関数で当番表を作れるからといって、すべてのシフト管理をExcelへ任せる必要はありません。

Excelが向いている	専用システムも比較したい
1日1人など単純な当番	1日に複数人の配置が必要
順番に回せばよい	希望休や勤務可能時間を反映する
担当者が少ない	人数・店舗・部署が多い
月1回作成して共有する	スタッフ自身がスマホで変更・申請する
交代が少ない	頻繁に交代・欠勤・応援が発生する
勤務時間管理とは別の当番表	勤怠・休暇・シフトまで一体管理する

例えば「朝礼当番」「電話当番」「掃除当番」「鍵当番」など、単純に順番を回す用途ならExcel関数と相性が良いです。

一方、希望休、勤務時間、夜勤、複数拠点、交代申請などの条件まで増えてきたら、数式を継ぎ足すより専用のシフト・勤怠システムを比較した方が管理しやすい場合があります。

勤怠やシフト管理まで含めて検討する場合は、中小企業向け勤怠管理システムの比較で機能や選び方を整理しています。

Excelそのものをどこまで残すか迷っている方は、Excel自動化の方法を用途別に比較した記事も確認してください。

当番表が勤務シフトを兼ねる場合は労務管理を別途確認する

今回の数式は、担当者を機械的に順番で割り当てるための仕組みです。

労働時間、法定休日、時間外労働、休憩などの条件が適切かどうかを判定するものではありません。

当番表が実際の勤務シフトを兼ねる場合は、自社の勤務制度と労務管理ルールを別途確認してください。

労働時間・休日の基本ルールは、厚生労働省「労働時間・休日」で確認できます。

実際の勤務シフトとして運用する場合は、就業規則、勤務制度、36協定など自社の条件に応じて、必要に応じて社会保険労務士などの専門家へ確認してください。

エクセル当番表ローテーション関数のよくある質問

Excelだけで当番表を自動作成できますか？: 単純な順番ローテーションなら可能です。WORKDAYで当番日を生成し、MODとINDEXで担当者を繰り返し割り当てれば、対象月を変更するだけで当番表を更新できます。
祝日は自動的に除外されますか？: 土日はWORKDAYが標準で除外しますが、日本の祝日を自動取得するわけではありません。祝日や会社休日を別の範囲へ登録し、WORKDAYの第3引数へ指定してください。
土日ではなく水曜日を休みにできますか？: WORKDAY.INTLを使えば可能です。月曜から日曜までを7桁の0と1で指定し、休業日にする曜日を1にします。
担当者が増えたら数式を作り直しますか？: StaffListの範囲を新しい担当者まで含むように変更すれば、ROWS(StaffList)で人数を数えているためローテーションへ反映できます。担当者リスト内には空白を入れない方が管理しやすくなります。
毎月同じ人の当番回数が多くなります: 毎月同じ担当者から開始していないか確認してください。H2の開始番号を変更し、前月の最後の担当者の次から開始すると長期的な偏りを減らしやすくなります。
欠勤した人を自動的に別の人へ変更できますか？: 単純な順番割り当てだけでは対応できません。個人ごとの休みや勤務可能日まで考慮すると数式が複雑になるため、条件が多い場合はシフト管理システムや別の仕組みも比較してください。
VBAを使った方がよいですか？: 対象月を変えて日付と担当者を表示するだけなら、今回のような関数で十分です。ボタンを押して当番表を新しいシートへ保存する、PDF化する、メール送信するといった処理まで自動化したい場合はVBAも候補になります。