スプレッドシートでお手軽輪番シートを作ってみた

touyou 作業効率化 ツール

みなさんこんにちは！エンジニアの藤井（ @touyou_dev ）です！ 今回は現在実際に使っている、スプレッドシートで作った輪番シートの作り方についてご紹介します。

実際得られる結果はこちら

この画像だけだと何のことやらという感じかもしれませんが、これは毎日自動で更新されるシートになっており、以下のような場面で使えます。

  • 朝会の司会を全員で順番に回したい時
  • 掃除当番を家族全員で回したい時
  • 某ラジオ番組のパーソナリティを一週間で割り切れない人数で一日ずつ回したい時

そして今回作ったものは以下のような性質があります。

  • 毎日自動で更新される
  • 人数が変わっても人リストを更新するだけ
  • Google App Scriptは不要
  • 平日のみに対応（祝日は未対応）

それでは早速作り方を紹介します！

用意するもの

今回必要なものは以下の三つです

  • スプレッドシート（エクセルでもいいかも？）
  • 輪番にしたい人のリスト
  • 輪番をなんとしてでも楽にしたいという気持ち

では早速作っていきましょう。

作り方

Step. 0 シートの用意

まずは3枚のシートを用意します。

名前はなんでもいいんですが、役割を明確にするため今回はmember, list, resultという名前にしました。

Step. 1 人リストの用意

シートができたらまずはmemberのシートのA列に上から輪番にしたい人を入力してB1のセルに=COUNTA(A:A)という式を入れます。

COUNTAという関数は指定した範囲に空白でない要素がいくつあるかを数えるものになります。今回は範囲としてA:A、つまりA列全体を表しているため、B1には常にリストに今何人の人が入力されているかが表示されるようになります。 これでmemberシートは完成です。

Step. 2 全体データの用意

続いては全体データを用意します。輪番を追うだけであればこのシートのみで役割としては完結するものになります。 まず、A1に初日の日付を入れます。

続いてA2に=WORKDAY(A1, 1)と入力します。

この時点で最新のスプレッドシートであればA列全てをいい感じに埋めてくれるサジェストを出してくると思うので、それはそのまま受け入れてしまっていいです。 WORKDAYという関数が何かというと、ある日から平日のみでカウントした時のn日後の日付を返すというものになります。 そのため、今回A2にはA1の次の平日が指定され、以降も同じように一個上の日付に対して次の平日が表示されていくようになります。

なおここで土日以外を休みにしたい人や、祝日もちゃんと含めたい人は指定することもできます。こちら他の関連関数を使った方が良い場合もあるので詳しくは公式ドキュメントを参考にしてください。

support.google.com

これができたら次はB1に=INDIRECT("'member'!A" & TO_TEXT(MOD(ROW($A1)-1, member!$B$1) + 1), TRUE)と入力します

これは何をやっているか順番に説明していきます。

  1. ROW($A1)-1で今のセルが上から何個目かを取得します。（0から始めたいため、-1をしています）
  2. member!$B$1はmemberシートの人数が計算されているセルを指しています。そのためMOD(ROW($A1)-1, member!$B1$1)で現在の行数を人数で割った余りを取ることができます。
  3. 2で作った値に1を足すことで、参照すべき行数の数字ができるので、数字を文字列にするTO_TEXT関数を使って、"'member'!A"という文字列と&で結合します。
  4. 3で参照すべきセルのアドレスが作れるので、INDIRECT関数で取り出します。

なお、ここで一度1を引いてから改めて足しているのは、アドレスにA0のようなものがないからです。そのためアドレスとして作る値は今回の例だと1~9のいずれかである必要があり、それには上のような手順が必要になります。

あとはこれを繰り返し下までコピーするだけです。$での固定を行っているので各行に合わせてROWの引数だけ変化し、行ごとの人が表示されるようになります。

これで全体データは完成です。連番を見たいだけであればこの時点でも十分に使えるかと思います。

Step 3. 閲覧用シートの作成

最後に、全体データのままだと全期間が表示されてしまいわかりにくいので直近数日だけを抜き出すシートを作成します。

今度はresultシート内に作っていきます。まずはA1に=TODAY()+ROW($A1)-1と入力します。

これはTODAY()で今日の日付を取得できるので、それに補正値を足している形になります。 今回は一番上を当日にしたかったので、行番号-1にしました。1行目だとこの補正は必要ないのですが、こうすることでコピーして扱いやすくできます。 実際これを下に引っ張ってコピーするとちゃんと今日から行番号-1日後の日付が入力されると思います。

そしたら最後の仕上げはB1に=VLOOKUP($A1, list!A:B, 2)というスプシ芸お馴染みの関数を入力し、下までコピーするだけです。こうすることでresultシートは今日から数日間のみの輪番を見れるシートになりました。

まとめ

今回はスプレッドシートでお手軽に輪番シートを作る方法を紹介させていただきました。 あくまで簡易的なものであり、祝日対応や途中でスキップされた時の対応などはこのままだとできないですが、そこは運用でもカバーできる範疇ではあると思うのでよければぜひ活用してみてください。

今でこそさまざまなWebサービスがあったり、昨今話題のChatGPTなどで昔ながらのツールの存在意義が問われる機会も多くなったのかなと思います。ですがこのように一工夫するだけでどんなツールでも活用法は見つけられます。今後もそのツールで何ができるのかをしっかり見極め、楽して便利に活用していきたいと思います。