劇場スケジュールが一目でわかる「ステイジーズカレンダー」をカスタマイズしてみる
ここは、小～中劇場規模の舞台公演（主に演劇）で制作やチケット管理業務をしてきた私が培ってきた事を綴るnoteです。
今回は演劇制作者が日ごろお世話になっているネビュラエンタープライズさんが提供されているWEBコンテンツ「ステイジーズカレンダー」を自分好みにカスタマイズする方法です。
「ステイジーズカレンダー」は、
Googleスプレッドシートにて、首都圏をメインに向こう4か月間の舞台芸術公演の情報をなるべく網羅し、「今何が上演されているんだろう？」「今度のお休みに何が観られそうかな？」といった皆さまのお悩みを解決します。観劇が大好きな私たちスタッフのあったらいいなを形にしてみました。
という素敵なカレンダーです。
上演している作品がわかりやすく一覧になっているだけでなく、
エクセルにダウンロードしたり、フィルタービューを使用してほしい情報だけを表示することが可能です。
せっかくスプレッドシートで作成されていますから、ここでもうひと手間加えて、特定の日にちに特定の場所で上演されている公演を抽出できるように自分だけのカレンダーを作成してみたいと思います。
1.IMPORTRANGE 関数でデータを読み込む
まずは、ステイジーズカレンダーのデータを自分のスプレッドシートに読み込みます。
ステイジーズカレンダーを開いたら、URLをコピーします。
新しいスプレッドシートを作成し、A1に
数式をいれます。式は
=importrange("スプレッドシートのURL","シート名!範囲")
です。
範囲はリンク(D列)～楽日(M列)を取り出したいので、
=importrange("スプレッドシートのURL","カレンダー!d2:m"))
となります。
タイトル行が2行目なので、範囲は「d:m」ではなく「d2:m」とします。
欲しいデータを自分のスプレッドシートに読み込むことができました。
2.FILTER関数でデータを抽出
1) 日付を指定する
次に左下の「＋」をクリックしてシートを追加します。
2行目に先ほど読み込んだデータの1行目(タイトル行)をコピペします。
（分かりやすいように白黒反転してます）
A1に「日にち」と入力し、B1に参照したい日にちを入力するようにします。
まずは、B1で［データ」→［データの入力規則］を選択し［ルールの追加］をクリック。
条件を［有効な日付］にして完了。
B1をダブルクリックするとカレンダーが出てくるので日にちを選択することができます。手入力でもOK。
次にfilter関数をA3に入力していきます。
読み込んだデータには「初日」と「楽日」がありますので、
B1に入力した日にちが初日以降楽日以前の公演を抽出する式を作成します。
=filter('読み込んだデータの範囲',('初日列'<=B1)*('楽日列'>=B1))
つまり
=filter('シート1'!A:J,('シート1'!I:I<=B1)*('シート1'!J:J>=B1))
となります。
条件にあった公演が抽出されました。
フィルター範囲にタイトル行を含めたくない場合は
=filter('シート1'!A2:J,('シート1'!I2:I<=B1)*('シート1'!J2:J>=B1))
のように開始する行を指定することもできます。
ここではどちらでもよいかと思います。
2) 都道府県を指定する
次に都道府県を指定していきます。
都道府県は、例えば「東京と千葉」など複数の条件を指定できるようにしてみます。
都道府県を入力するセル(D1)にデータ入力の規則を追加します。
条件を［プルダウン(範囲内)］として、範囲を都道府県の列(シート1のD列)とします。
タイトル行は含まないようにD2:Dを選択。
そうすると、選択肢にD列に入力されている都道府県名が出てきます。
下までスクロールして、
［複数選択できるようにする］にチェックを入れて完了。
複数の都道府県を選択できるようになりました。
D1には「東京, 千葉」のようにチェックを入れた選択肢がカンマ区切りで入っています。次にこれをそれぞれ別のセルに分けて条件リストを作ります。
使用するのはsplit関数。
=split(テキスト, 区切り文字, [各文字での分割], [空のテキストを削除])
[各文字での分割], [空のテキストを削除]は使用しないのでここでは割愛します。
「東京」と「千葉」の間にはカンマと半角スペースがありますので、これを区切り文字とします。
L2に
=split(D1,", ")
と入力すると、
「東京」と「千葉」が横並びに記載されました。
見やすくするために、横並びを縦並びに変更するtranspose関数を組み合わせます。
=transpose(split(D1,", "))
縦並びになりました。これが選択した条件のリストとなります(L列)。
リストができたところで、選択した都道府県の公演データをfilter関数で取り出します。
条件が複数あるので、countifを組み合わせます。
=filter('読み込んだデータの範囲',countif(選択した条件列,都道府県列))
つまり、
=filter('シート1'!A2:J,countif(L2:L,'シート1'!D2:D))
となります。
選択した東京と千葉の公演だけ取り出すことができました。
3) 条件をかけ合わせる
次に、日付と都道府県を組み合わせて指定できるようにします。
タイトル通り、掛け算記号の＊を使用してかけ合わせます。
日付フィルター =filter('シート1'!A2:J,('シート1'!I2:I<=B1)*('シート1'!J2:J>=B1))
都道府県フィルター =filter('シート1'!A2:J,countif(L2:L,'シート1'!D2:D))
この二つの式をまとめます。
↓
=filter('シート1'!A2:J,('シート1'!I2:I<=B1)*('シート1'!J2:J>=B1)*(countif(L2:L,'シート1'!D2:D)))
これで「12月7日」(B1)に「東京」「千葉」(L列)で上演している公演を抽出することができました！
3.SORT関数で並べ替え
ですがこのままだと、ちょっと見づらいので地域で並べ替えていきます。
sort関数は
=sort(範囲,並べ替える列,昇順／降順)
ですので、
上記で作成したfilter関数を「範囲」とし、列は地域(5列目)、昇順(true)とします。
=sort(filter('シート1'!A2:J,('シート1'!I2:I<=B1)*('シート1'!J2:J>=B1)*(countif(L2:L,'シート1'!D2:D))),5,true)
同じ地域がまとまって表示されるようになりました！
4.書式を整えて見やすくする
あとは列幅や書式などを見やすいように整えれば完成です。
これはもうお好みで。
1) 日にちの書式を変更
変更したい列やセルを選択し、
［表示形式］→［数字］→［カスタム日時］
書式の選択肢が出てくるのでそこから選ぶか、↓赤い囲みを手入力で変更して［適用］
2) 文字列を折り返す
列幅に対する文字列は、エクセルのように「縮小して全体を表示する」ことが出来ないようですので折り返して表示します。
列を選択したら、［表示形式］→［ラッピング］→［折り返す］
3) 行ごとに色を変える
表内のいづれかのセルを選択し、
同じく［表示形式］→［交互の背景色］
範囲では最後の行を指定しないように、A2:J とします。
ヘッダーのチェックを確認し、好きな色を選んだら［完了］
こんな感じでしたらスマホ画面でも見づらくないかなと思います。
ちなみに今のところスマホのスプレッドシートアプリだと、日付をカレンダーで指定したり、プルダウンで複数選択できないようです（おそらく）。
どちらも手入力ならOKです。
5.FILTER機能
さらに、「新宿と下北沢の公演だけ知りたい！」と地域を絞り込みたい時などがあるかもしれません。
関数より気軽に使えるFILTER機能を使用して絞り込みをしてみます。
タイトル行(A2:J2)を選択して、［データ］→［フィルタを作成］
各タイトルセルにストライプの▼マークが表示されるのでクリックし、
クリアしてから、地域を選択。
選択した、下北沢と新宿の情報だけ抽出できました！
但しこの機能、日にちを変更するとフィルター設定しなおす必要がありますので注意が必要です。（再設定が面倒な場合は、↓おまけで解説している関数を試してみてください）
いかがでしたでしょうか。
「明日何か観たいなぁ」と思ったときなど、これでサクッと検索可能になったのでは？
ひと手間と言いつつ何手間もかかってしまいましたが、一度作成しておけば、ステイジーズカレンダーが更新されても新しい情報を自動で取得できますので便利かと思います。
ステイジーズカレンダーは商用利用不可ですのでご注意ください。
スプレッドシートに興味のある方は、ぜひカスタマイズをチャレンジしてみてください！
今回使用した関数で説明を省略しているものは、既にほかのnoteで取り上げています。関数自体に興味のある方は、目次よりご確認ください。
おまけ
地域の絞り込みも関数を使いたい！と思うかもしれません。
ただあまりfilter設定を増やすと数式がどんどん長くなってしまいますので、おまけとしました。
「都道府県」を設定した時と同じように、データ入力規則で「地域」をプルダウン選択できるようにしますが、全都道府県の「地域」から選択するのはちょっと大変。
まずは最初に選んだ都道府県の地域だけリスト表示させるようにしたいです。
・選択した都道府県の地域を一覧にするO列P列
・地域を選択するセルF1
・選択した地域リストを表示させるM列
を作ります。
O2にはD1で指定した都道府県と、その地域を抽出する数式をいれます。
=filter('シート1'!D2:E,countif(L2:L,'シート1'!D2:D))
フィルターで抽出する列は都道府県と地域だけでよいので、D,E列のみ指定しています。
さらに重複データを除いてくれるunique関数と組み合わせ、
=unique(filter('シート1'!D2:E,countif(L2:L,'シート1'!D2:D)))
とします。
これでP列に入力規則の元になるデータができましたので、F1にデータの入力規則を指定します。
都道府県の時と同じように複数選択にチェックを入れて完了。
指定した都道府県の地域だけで、プルダウン選択できるようになりました。
次にsplitとtransposeでリスト化します。M2に数式を入れます。
=transpose(split(F1,", "))
都道府県リスト(L列)と、地域リスト(M列)ができました。
さきほどA3に入力した式にさらにfilter関数を組み合わせて、「都道府県」の「地域」で行われる公演を抽出します。
ただ地域は指定しないこともあるかと思います。F1が空白のままではエラーになってしまいますので、if関数を使用して地域セル(F1)が空白の場合の処理が必要となります。
つまり、
もし「地域」が空白の場合は、「日にち」と「都道府県」で抽出し、そうでない場合は「日にち」「都道府県」「地域」で抽出する。
という式を作ります。（さらに地域ごとに並べ替え）
=sort(if(F1="",filter('シート1'!A2:J,('シート1'!I2:I<=B1)*('シート1'!J2:J>=B1)*(countif(L2:L,'シート1'!D2:D))),filter('シート1'!A2:J,('シート1'!I2:I<=B1)*('シート1'!J2:J>=B1)*(countif(L2:L,'シート1'!D2:D)*(countif(M2:M,'シート1'!E2:E))))),5,true)
これで、地域を指定すると
その地域だけの一覧が表示されます。
地域を空白のままにすると、
選択した都道府県の一覧がすべて表示されます！
手数が多く数式も長いので、もう少しスマートな方法がきっとあると思いますが、こんなやり方もあるよというご紹介でした。