概要
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関数を調べてみてください。
最後に
今回は、当番表を手動で更新するのが大変なので自動化してみました。
ご参考になれば幸いです。
ありがとうございました！