＃23　スプレッドシートで1週間ごとに自動更新の当番表を作ってみた

概要

1週間ごとに担当者がローテーションしていく当番表を作成しました。
自動更新になっていて、翌週月曜日になると自動的に当番がスライドします。
一番下まで行くとまた先頭の人に戻ります。

初日

翌週月曜日

仕組み

当番の更新

B列には、こちらの式を設定しています。

=IF(MOD(QUOTIENT(DATEDIF($E$1,TODAY(),"d"),7),COUNTA(A:A))=ROW()-1,"当番","")

各関数の意味合いはざっくりと以下の通りです。

関数	説明
IF(論理式,TRUE値,FALSE値)	論理式の条件を満たすときTRUE値、満たさないときFALSE値を返す
MOD(被除数,除数)	被除数 ÷ 除数のあまりを返す
QUOTIENT(被除数,除数)	被除数 ÷ 除数の商を整数で返す
DATEDIF(開始日,終了日,単位)	開始日から終了日までの差を返す。今回は単位dなので日数
TODAY()	今日の日付を返す
COUNTA(範囲)	範囲内の空白でないセルの個数を返す。今回は範囲としてA列を指定
ROW()	セルの行番号を返す

これらを整理すると、上の式は
(E1の日から今日までの経過週数 ÷ 人数)のあまり + 1 行目に「当番」と表示する
という意味になります。
初週は、経過週数が0なので、1行目の人が当番。次の週は、経過週数が1なので、2行目の人が当番。といった具合です。

セルの背景色の変更

条件付き書式を使用し、セルの値が「当番」のときだけ背景色がつくように設定しています。

自動更新を有効にする

スプレッドシートは、デフォルトではファイルに変更があったときしかTODAY()の再計算がされないようなので、
時間経過でも更新されるように設定を変更します。
ファイル → 設定 でスプレッドシートの設定を開き、計算 → 再計算 を「変更時と毎時」に設定

応用

当番更新日を月曜以外にする

E1を基準日にしていますので、そこに月曜日の日付を入れれば月曜日更新。火曜日の日付を入れれば火曜日更新となります。

複数の当番を設定する

当番Aでなかったら当番Bか判定する、当番Bでなかったら当番Cか判定する...といったように条件をつけ足していくことで複数の当番をローテーションさせることも可能です。
例として、当番Aと当番Bをローテーションさせる場合の式は以下のようになります。

=IF(MOD(QUOTIENT(DATEDIF($E$1,TODAY(),"d"),7),COUNTA(A:A))=ROW()-1,"当番A",IF(MOD(QUOTIENT(DATEDIF($E$1,TODAY(),"d"),7)+1,COUNTA(A:A))=ROW()-1,"当番B",""))

条件付き書式は複数設定可能なので、セルの値が当番Aのときは緑、当番Bのときは黄といったように設定すれば色分けも可能です。

あるいは、B列は当番A用の関数、C列は当番B用の関数、といったように列を切り分けることでも実現可能です。
この場合、当番B用の式は当番Aより一つ下にずらすので

=IF(MOD(QUOTIENT(DATEDIF($E$1,TODAY(),"d"),7)+1,COUNTA(B:B))=ROW()-1,"当番B","")

になります。

週替わりではなく、日替わり、月替わりの当番表にする

式の QUOTIENT(DATEDIF($E$1,TODAY(),"d"),7) の部分を書き換えることで、日替わり、月替わりにすることができます。
日替わりの場合は、÷7をする必要がないので DATEDIF($E$1,TODAY(),"d")です。
月替わりの場合は、単位を月(m)にすればよいので DATEDIF($E$1,TODAY(),"m")です。毎月1日に更新する場合は、開始日を1日にします。
平日のみの日替わりとしたい場合は、祝・祭日のリストを用意し、NETWORKDAYS関数を使う必要があります。こちらの詳細は割愛しますので、必要であればNETWORKDAYS関数を調べてみてください。

最後に

今回は、当番表を手動で更新するのが大変なので自動化してみました。
ご参考になれば幸いです。
ありがとうございました！