表計算芸人が語るGoogleスプレッドシートによるコミュニティ運営術

多分noteをご利用の方にはゲームコミュニティを運営されてる方もそこそこいらっしゃると思うんですけど、その中でコミュニティ主体の企画をやる事ってあるじゃないですか？ありますよね？そんな時に例えば、何らかの集計を行う事ってあると思うんです。そんな時にもしかしたら役に立つかもしれないGoogleスプレッドシートのおはなしです。

対象としては100人規模くらいまではこのやり方で対応できると思うんですけど、内容にもよります。スケジュール管理とかならhoraroとかOengusとかあるし、トーナメント表が必要ならchallongeとかになるわけで。

Googleフォーム + Googleスプレッドシート

弊コミュニティだとなにか集計が必要な時は全てこの組み合わせです。勿論集計に際しては表計算ガリガリ書ける……lookup系関数は駆使できる位のスキルは要ると思うんですけど、そのくらいが分かるならちょっと頑張れば集計はそんな労力なく出来ると思うんです。但し、僕が要求したいラインは「ほっといてもリアルタイムに結果が反映されつつ、ある程度きれいに集計結果が出ている」ところまで行きたいんです。具体的に#N/Aとか見えてほしくないし、行溢れたのでセルの計算式足しに行ったりしたくないんです。じゃあこういうのマスクするのにどうするの？って話なんですけど、iferror()関数とかで隠すのが一番手軽です。ただ、これだとどのくらいのデータが来るのか見越して行追加しておかないとならなかったりするのであんまり好きじゃなかったりします。この話自体は「表計算芸人」の話なのでもうちょっとトリッキーというか、Googleスプレッドシートにちょっと面白い関数あるよ、というプレゼンテーションなのでもうちょっと頑張ります。というわけで……。

非破壊編集

ちょっと例も出しましょう。集計としてはそこまで大きな規模ではないんですけど、直近でフォームで集計した機体の投票数をカウントするだけのアンケートを作っているのでそちらを例とします。

次元戦闘機パイロット一般調査 #2 集計結果

このシートのform、fixシートの解説です。
まずGoogleフォームの集計はすべてスプレッドシートにリンクして、集計結果をimportrange()関数で取り込んでいます。これは集計に使ったシートを場合によってはファイルのコピーだけで使いまわそうという魂胆です。また、リンクしたスプレッドシートは別なシートから参照する際、「'フォームの回答 1'!$a:$i」のような変換が必要な文字列で参照するのも嫌だという理由があります。

で、回答者が回答を間違えるということがまぁあります。当然あるので対応方法を用意しています。今回は回答の内容がそこまで難しくなく、すべてまとめて投票数として集計できるので、全部並べて、間違えた奴だけ直して、差し替えたリストを作るという作業です。
まずその作業用に別なシートを用意して、集計結果を吸い上げます。出来る限りシートは作業工程で分割しておきます。面倒くさがるととんでもない計算式を書かされてメンテナンス出来なくなるので面倒くさがらないように。今回はflatten()関数で二次元配列をただの配列にしてしまうのが簡単でした。で、並べたリストに対して間違っているものは隣に修正内容を書くようにして、修正内容があれば修正内容を、なければそのままという条件分岐を用意しています。じゃあこのif計算式をだばーーーーーーーっと4~500並べる……のはクソめんどくさいのでしません。

変態配列関数

現在のGoogleスプレッドシートには配列の各要素を計算して、結果を配列として返す類の関数が充実しています。充実しているっていうか、Excelだと構造化参照で簡単に書けるんですけど、それと対応するものと言う事だと思います。丁度話のタイミングが合うのでfix!d2に実際に入っているmap()関数を挙げます。
基本的にGoogleスプレッドシートの配列回りの関数はlambda()関数とセットになりますが、とりあえずこの意味はすっ飛ばして……map()関数の引数として指定する参照は複数の参照を引数として渡すんですけど、行列数が同じ参照でなければいけません。map()関数がやること自体は「1つ目の参照と2つ目の参照の（相対的に）同じ位置のものをlambda()関数で記述した式で計算して、（相対的に）同じ位置に返す」という挙動です。なので「（相対的に）同じ位置」を確認しようとしたらこっちにはあるけどあっちにはない、ということがあると計算してくれません。というわけで、A列の要素数をcounta()関数で確認、offset()関数で参照の大きさを固定しています。滅茶滅茶面倒な書き方なんですけど、これによって「何もしなくても計算すべきところは勝手に計算する」というようになります。
ちなみにGoogleスプレッドシートのセル内改行はAlt+Enterです。最悪インデントを駆使してメンテナンス性を上げます。なにやってんだこいつ。

このあともこのトンチキな配列関数を駆使して徹底的に必要な分だけ自動で計算させていくんですけど、計算する要素数を減らすためのテクニックとしてoffset()関数で切る以外にも、filter()関数で空白のセルをフィルタするという手もあります。この場合要素数の狂いに気をつけないとおかしくなります。ぶっちゃけるとここまでするくらいなら素の計算式突っ込んで縦に引き延ばしていく方が簡単まであるので、僕はちょっとした病に罹っていると思ってください。

シートを分けまくる成果

というわけでこのあとtallyシートで投票数を集計して、rankシートで並べ替えつつ見栄えのする出力まで持っていき、更に出力結果は結果シートから参照しています。何故ここまでするのかと言うと、集計の過程を見られたくないケースが実際に発生したことがあり、その過程を隠すには出力結果しか見られない別なシートを用意する必要があったためです（具体的にはGoogleフォームから転送してもらった画像ファイル名に回答者の本名が出てしまうケースがあります）。具体的には集計したシートは見せたくないシートを非表示にして編集不可で公開、結果表示のシートからはimportrange()関数で参照する、ということになります。その他web公開だとセル内の文字を変な方向にできないのでそれを乗り超えるなど、ちょっとした利点は結構あります。

ちなみに実際に集計で使用している小技ですが、tallyシートの機体リストは集計結果をunique()関数で絞ると機体の順番がきれいに出てこないので、orderシートにあらかじめ手並んでほしい順番を用意しておき（この順番はR-TYPE FINAL2準拠です）、そちらのリストをfilter()関数でフィルタしています。最終的に得票数で並べるのにどうして順番が？と思われると思いますが、これで同票数の場合でも我々がよく知っている順番で並びます。
そのほか、rankシートで同票数の順位をうまいこと表示していますが、match()関数が順番に数えてくれる事を信用した小技です。このテの盲点になりがちな技が表計算に経結構あります。

最後に

基本noteに書くことは「自分が書きたいこと」なので、今回も人の役に立つかどうかは度外視の表計算芸人の漫談です。ただ、Googleスプレッドシートのアクセスコントロールのためにシートを分けて、importrange()関数を使う方法は割とオススメなので活用してもらえると幸いです。その上で、Googleスプレッドシートの配列関数は書いてて呆れるくらいのクソ長関数で無茶苦茶出来るので表計算芸人としてはこんなに無駄に楽しい遊びねぇな、という感じです。極まったクソ関数情報、他にもあればお待ちしています。……居るのそんな奴？