スプレッドシートで豊かな生活を
この記事はB4UT Advent Calendar 2022に参加しています。
どうも。Smaです。
突然ですが、みなさんはGoogleスプレッドシートをご存知でしょうか？
Googleスプレッドシートとは
Googleスプレッドシートは、Excelのような表計算ソフトのシートを共有しリアルタイムで同時に編集できるサービスです。個人的なデータ整理にはもちろんサークル等の運営や情報共有、もっと大規模なイベントで使われる場合もあります。
SEGAの音ゲーマーならお世話になっているであろう譜面定数表もスプレッドシートで作られているものが有名です。
よく「スプシ」と略されます。
筆者のスプシ実績
ここで、僕が今までにB4UT内で制作したシートをいくつか紹介したいと思います。
個人企画：ゲキチュウマイ6アンサークイズ
個人的に制作したクイズ企画「ゲキチュウマイ6アンサークイズ」で画面配信に使用するシートを作成しました。
SEG4UT Championship スプシ担当(2代目)
B4UT内で有志により行われるSEGA音ゲーの大会「SEG4UT Championship」のスプシ担当を引き継ぎ、機能の追加や改修を行いました。
その他、B4UT外でも制作等を補助するためのシートをいくつか制作しています。
スプシ講座
今回の記事では、僕が今までに得たスプシ制作における技術や考え方などを僕なりの形で書いていきます。
基礎編：オンゲキのレーティングを計算しよう
（スプシやエクセルを一切触ったことがなくてもわかってもらえるようにがんばります）
まずはスプシをよく知らない方にもその便利さを実感していただくため、比較的シンプルなシートの作り方を紹介します。テーマはズバリスコア管理。最終目標はレーティングを計算することです。今回はオンゲキを想定して作りますが、CHUNITHMやArcaeaなどにも同じ方法が使えるはずなのでぜひ一緒に作ってみてください。
スプシを使う
まずは白紙のスプレッドシートを開きます。「Google スプレッドシート」と検索してみましょう。こんなページにたどり着きましたか？
いきなりあみあみの画面が出てきた！という方はそれで大丈夫。ビジネスツール感あふれる紹介に飛ばされた！という方もいると思いますが、「スプレッドシートに移動」をクリックすれば同じ画面になるはずです。
それでは「新しいスプレッドシートを作成」をクリックしましょう。
それではA列に曲名、B列にその曲のスコアを入力してみましょう。
これで最低限のスコア管理ができるようになりました。ここからはレーティングの計算に挑戦していきます。
レーティング計算の準備
ここで、オンゲキのレーティング計算について軽く触れておきます。
オンゲキでは各曲とそのスコア*1に対してレーティング値が計算され、以下の条件を満たす計55曲の平均値が全体のレーティングとなります。
- 新曲枠(15曲)
現在のバージョンで追加された曲のうち、レーティング値が高い曲 - ベスト枠(30曲)
以前のバージョンで追加された曲のうち、レーティング値が高い曲 - リーセント枠(10曲)
最近遊んだ曲のうち、レーティング値が高い曲(一部譜面を除く)
また、レーティング値は譜面定数(小数点以下までつけられたレベル)とスコアを使って計算します。（詳細な計算方法は後述）
それでは、まずは譜面定数を入力する欄を作りましょう。スコアが入力されているB列を右クリックし、「左に1列挿入」をクリックします。
これで新たにできたB列に譜面定数を、スコアの右にあるD列にレーティング値を入力することにしましょう。
譜面定数については、有志が制作している譜面定数表があるのでここから調べます。
曲ごとのレーティング値を計算する
それではレーティング値の計算に入っていきましょう。曲ごとのレーティング値は、ランクSの97万点を基準に次のように増えていきます。
- 970000点(ランクS) : 譜面定数+0.0
↓ 200点ごとに+0.01 - 1000000点(ランクSSS) : 譜面定数+1.5
↓ 150点ごとに+0.01 - 1007500点(ランクSSS+) : 譜面定数+2.0 (上限)
ベスト枠に入るような曲ならたぶんSランクは出ているので、それ未満のスコアは考えないことにしましょう。これを式に落とし込みます。（このあたりは本来スプシ技術の話ではありませんが、スプシ作成にあたってこういう作業は重要なので解説します）
100万点でレーティング値の増え方が変わることに注目すると、スコアが100万点未満の場合と100万点以上の場合でそれぞれのレーティング値は次のように書けるとされています。
- スコアが100万点未満
→ 譜面定数 + { (スコア- 970000) / 200 } * 0.01 - スコアが100万点以上
→ 譜面定数 + { (スコア- 1000000) / 150 } * 0.01 + 1.5
また、譜面定数とスコアはシートに入力されている値を使うので、その部分はセル番地*2に置き換えます。とりあえず一番上のTiamaT:F minorのことだけを考えると、
- スコアが100万点未満
→ B2 + { ( C2 - 970000) / 200 } * 0.01 - スコアが100万点以上
→ B2 + { ( C2 - 1000000) / 150 } * 0.01 + 1.5
と書くことができます。
次はこの場合分けをIF関数を使って表します。IF関数は次のように使います。
IF(条件式, 処理1, 処理2)
↑このように書くことで、条件式の部分が成り立つときは処理1、成り立たないときは処理2の結果がセルに表示されます。
どう使うかというと、つまりこういうことです。
IF( C2 < 1000000, B2 + { ( C2 - 970000) / 200 } * 0.01 , B2 + { ( C2 - 1000000) / 150 } * 0.01 + 1.5)
だいぶ長い式になってきましたが、表している内容は先程書いたこれと同じです。
- スコアが100万点未満
→ B2 + { ( C2 - 970000) / 200 } * 0.01- スコアが100万点以上
→ B2 + { ( C2 - 1000000) / 150 } * 0.01 + 1.5
これでレーティングの計算式が完成しました！シートに入力して確認してみましょう。
スプレッドシートに計算式を入力する場合は最初にイコールをつけるというルールがあります。これをしっかり守って入力してあげれば......
式のとおりにレーティング値が表示されました！
さらに、セルの右下の小さい四角をドラッグしてあげると......
なんとその下の曲に対しても同様にレーティング値を計算してくれます！
こうすると下の行に対しては参照するセルを自動でずらした式を入力してくれます。スプシくんは賢いですね。
SSS+を上限にする
これで終わり......いや、ちょっと待ってください。
よく見るとGrievous Ladyのレーティング値が微妙に16.4を超えています。そう、今の計算式ではSSS+を超えても101万点までレーティング値が伸び続けてしまいます。これはいけない！
ということで、MIN関数を使って上限を設定してあげましょう。MIN関数は次のように使います。
MIN(値1, 値2, ...)
このように入力すると、与えた値の中で最も小さい値を返してくれます。
つまり、「先程の式で計算したレーティング値」と「譜面定数+2.0」をMIN関数に入力することで、スコアがどんなに大きくなってもレーティング値は譜面定数+2.0より大きい値にならなくなります。
これでレーティング値の計算は終わりです！あとは
- 曲のデータをたくさん入力する
- レーティング値が高い順に並び替える
（ヒント：右クリック） - 上から30曲の平均を計算する（AVERAGE関数を使います）
の3つを行えばベスト枠が計算できます！ここまでがんばれた皆さんならできるはずです！
基礎編はこれで終わりですが、このシートはあなたが望めばもっと進化するはずです。カスタマイズで君だけの最強のスプシを作ろう！
応用編：SEG4UT Championshipを裏で支えたスプシ技術
さて、ここからは講座とは名ばかりの書きたいこと書きまくるタイムです。
僕はちょうど1年前くらいに行われたB4UT非公式大会「SEG4UT Championship（以下セガチャン）」の2代目スプシ担当として、大会で使用する数々のスプレッドシートの制作・改造を行いました。このとき僕なりに考えたことがいくつかあったので、誰かの参考になることを願って書いてみることにします。
なお、ここからはスプシ用語が解説されない場合があります。ご容赦ください。
視覚的な楽しさと利便性の両立
まずはセガチャンで使用したシートの一部をご覧いただこうと思います。
これらのシートは運営が諸々の記録に使うと同時に、試合当日や事前配信などで参加者が目にするものでもあります。そのため、参加者が見たときの楽しさと運営側の使いやすさを両立する必要があるものたちになっています。
前者については僕が引き継いだ時点でかなり良いものになっていたので、引き継ぎ以降は試合当日の運営の負担をできるだけ減らせるように制作を行いました。
やったこと（紹介していないシートの内容も含みます）
- 自選曲名の入力をコピペから番号(1〜3)に
- 実際に選択された自選曲の色付け
- 決勝の順位点計算や色分けを自動化
- トーナメントの結果から次の試合の組み合わせを自動入力
条件付き書式は無限の可能性を秘めている
面倒な文字色変更や背景色変更を自動でやってくれる条件付き書式ですが、普通はセルに入力したデータに応じてそのセルの書式を変えてくれるのみです（それでも十分便利なのですが）。
しかし、条件付き書式の設定に「カスタム数式」を利用することでそれを覆すことができます。
「カスタム数式」では条件としてTRUEかFALSEを返す計算式を使えますが、条件付き書式を複数のセルに適用すると、普通に計算式をコピペしたときと同様に参照先がずれて不都合が生じます。
例えば前章のレーティング管理シートで、SSS+の曲は行全体を赤文字にしたい！と思って次のように書いてもうまくいきません。
しかし、もしもカスタム書式の中で絶対参照が使えたらどうでしょうか？条件のC3を$C3に置き換えれば、どの列からもC列を参照する条件にすることができるはずです。
できました！スプレッドシートの機能をフル活用することで条件付き書式が絶大なパワーを発揮することがわかります。見やすさにこだわる場合には条件付き書式を極めて損はないと言っていいでしょう。*3
注意点なんですが、条件付き書式の重ねがけはできません。条件付き書式には優先度があり、セルごとに一番優先度が高い一つしか適用されません。
実際にどういうことが起きるかというと、背景色を変える条件と文字色を変える条件を用意しても絶対に一方しか反映されないので困ります。この制約は頭の片隅に入っているといいんじゃないかと思っています。無理やりなんとかすることもできなくはないですが......*4
ちなみに、式の可読性が終わる代わりに参照するセルの位置を数字で指定できるOFFSET関数というヤバい奴も存在します。自選曲を黄色く塗る機能はコイツで実装されており、とんでもない式になっています。
こういうことをやるのはおすすめしません。マジで作り直さないとな......
GASに頼るという手段
この世にはGoogle App Scriptというものが存在します。JavaScriptをベースにしてGoogleの機能を使えるようにした感じのプログラミング言語です。プログラミング言語です。いよいよスプシ技術かどうかも怪しくなってきましたね。
すでにお見せした課題曲抽選シートには「シャッフル開始」「次の曲」というボタンがあり、クリックすることで登録した機能が発動します。この2つにはGASを使って開発したものを登録しています。
GASの使い方まで説明し始めるともう本当に収拾がつかないので目を逸らすことにしますが、手順が決まっているが手動でやるのは面倒なことをできるようにすれば便利です。ただプログラミング全般の利点を説明しています。疲れてきていますね。
ただし、GASで作った機能は使う方法がやや限られているうえに使う端末によってはボタンが押せない場合があること、作るには多少プログラミングと向き合う必要がある点には注意が必要です。
...もっとも、ここまでの内容について来られたのであれば、もしプログラミングをやったことがなくても素質は十分すぎるほどにあると思いますが。
さいごに
みなさんも、スプレッドシートに自分の欲しい機能を実装して豊かな生活を送りましょう！
