こんにちは、たこかいなです。
ボケクエスト３でのチラ裏ブログ以来２度目の登場です。
今回は僕がいつもお世話になってる大喜利プラスに関連して、自分の得票を集計する「大喜利プラススコア管理Googleスプレッドシート」を作ったよ、という記事です。
作ったといってもまだ仮配布のβ版です。実際に使ってもらって不具合とかを直したいので、「我こそは人柱に！」という方に是非使っていただき、フィードバックをお願いできればと思っています。
以下、長いので覚悟してくださいね。
大喜利プラススコア管理スプレッドシートって何？
まず大喜利プラスについて。次々と出される大喜利のお題に３分以内で答え、３分以内で互いのボケに投票。短いサイクルで次々とボケて、各々の得票を競う大喜利サイトです。
大喜利プラスには「段位」や「週間ランキング」など、実力の目安となるいくつかの指標があります。これらは過去の得票を元に算出されるのですが、あまり細かいことまではサイト上で表示されません。
そこで、自分でスコアを記録・集計することによって、これらの指標をわかりやすく確認しよう！というのが、この「大喜利プラススコア管理スプレッドシート」の役割です。
もともとは、大喜利プラスのプレイヤー仲間である進撃さん*1がスコア記録、段位計算の仕組みをExcelシートで組み上げ、公開してくださいました。
それを僕がもらってきて、自分の見たい指標を閲覧する機能をゴニョゴニョと追加したのがこのスプレッドシートとなります。
進撃さんなくしてこのシートが生まれることはありませんでした。改めて感謝します。
できること
- 基本操作は、項目一覧から票数を選ぶだけ。シンプルに票数の記録が行えます。
- 過去500回分の票数を元に段位を判定。次に何票獲得すれば昇段するか、あるいは次に何票獲得しないと降段となるかを表示します。
- 期ごとの集計が行えます。平均得票数およびH得票数が計算できます。
日ごと、さらにAM/PMごとの得票率集計も行っています。 - 「きまぐれメソッド(?)」に基づいた「10ボケごとに集計した票数」が計算できます。100回ごとの集計も行っており、大まかな好不調の波が確認できます。
利用に必要な条件およびおことわり
- 「Googleスプレッドシート」で動作するアプリケーションですので、利用するにはGoogleのアカウントが必要です。
- スマホで操作する場合、Googleスプレッドシートのアプリをインストールする必要があります。以下のリンクからどうぞ。
最終的には、必要であればGoogleアカウントの解説方法なんかも紹介して、できる限り使ってもらえるハードルを下げたいとは思ってますが、現時点ではどこまで詳しく書く必要があるのかもサッパリわからない状況です。
そんなわけで、ひとまずβ版の時点では「Googleアカウント」および「Googleスプレッドシート」が何なのかわかる、あるいは「GoogleスプレッドシートをいじったことはないけどExcelの操作くらいならわかる」といった方に試していただければと思っています。めっちゃ基礎的な操作のサポートはできかねます。
- 毎回の票数を記録するのは各自頑張ってください。大喜利プラスのサイトから自動的にとってくるとかのナイスな機能は用意していません。
特に昇段・降段判定は、漏れや間違いのみならず入力順序が違っても正確に計算できません。習慣づくまではけっこう忘れがちなんですよね。注意。 - 500回分の記録がたまるまでは段位判定は正常機能しません。あくまでスプレッドシート上での集計なので当然なのですが、悪しからず。
使いかた
スプレッドシートを自分の環境にコピーする
私たこかいなのGoogleアカウント上で、「大喜利プラス マイデータ（テンプレート）Ver0.0.1」という名前でシートを共有しています。
大喜利プラス マイデータ（テンプレート）Ver0.0.1 - Google スプレッドシート
編集はできないようにしてあるので、各自のGoogleアカウント（Googleドライブ）にコピーして使ってもらうことになります。
なお、実はこの「自分の環境にコピーする」手順がいちばんテストが行えてない手順です。できた！と思っても自分の手元の特殊な環境で辛うじて動いてただけだった…みたいな話はあるあるなので、今回も何かいきなり皆さんの手元で躓くポイントがあったとしてもご了承ください。そこも含めての人柱ということで…
そんなわけで、使い始める際には是非私まで一声お声かけください。そして、何か問題があったら勿論のこと、「上手くいったよ！」というご報告もめっちゃ役立つので、是非ご報告いただければと思います。
以下、Android端末での画面です。iPhone版などで違うところがあったら教えてください。
- 上記「大喜利プラス マイデータ（テンプレート）Ver0.0.1」のリンクを開きます。Googleスプレッドシートがインストールされている端末ならば、アプリが自動的に立ち上がるはずです。
- ただし、この状態では編集できません。たこかいなの所有しているマスターデータで、他の人には編集権限がないためです。
ためしにスプレッドシート上のセルを適当に選択してみてください。画面下の入力欄のところに「表示専用」と書かれていて、入力を受け付けてくれないはずです。
自分のアカウントにスプレッドシートのコピーを作成するには、画面右上の「３つの点」（メニューの表示）をタップしてください。 -
開いたメニューの中から「共有とエクスポート」を選択します。
-
さらにサブメニューの中から「コピーの作成」を選んでください。
- しばらくするとコピーが完了し、編集できるあなた専用のスプレッドシートが出来上がります。
ためしに適当なセルを選択してみてください。下図のように、入力を受け付けてくれそうな表示になっていれば、コピー成功です。
票数の入力
さて、自分だけの記録をする準備ができました。さっそく票数を記録してみましょう*2。
以下、Android端末での画面です。iPhone版などで違うところがあったら教えてください。
- メインの「Records」シート上の左上の方に、オレンジ色で右端に▼がついてるセルがあります。すぐ上に「↓項目を選んで記録」と書いてあります。このオレンジのセルが、票数入力用の特別なセルです。
このオレンジのセルをタップし、続いて画面下端の入力欄（白いとこ）をタップしてください。
- 入力項目の選択肢一覧が表示されるので、記録する票数を選択してください。
【注※】スプレッドシート起動直後は、なぜかこの選択肢が１発で表示されず、普通の入力欄みたいになることがあるみたいです。選択肢が出てこない場合、いったん別のセルをタップして、もう一度 1. の手順を繰り返してみてください。
- 選択すると、オレンジ色のセルに選択した項目が反映されます。
今回の例では「１票」を選んでみました。
この状態のまましばらく待ってください。
- だいたい数秒で、左側の記録部分の一番上に票数と時間帯（変わる場合だけ）が反映され、過去の記録が下にスライドします。
そしてオレンジ色のセルが再び空欄になります。こうなったら反映完了です。
画面左上のチェックマークでデータを確定してください。
なお、票数入力の選択肢は「0票」～「15票」までしか用意してません。
もっと大きな得票をした場合は、とりあえず適当な票数を選択して上記の 1.～4. を行い、記録領域の一番上のセルを手入力で修正してください。
トップ・大ボケ・神ボケを記録する
入力用の項目一覧に、以下のような項目が用意されています。
- ノーマル
- トップ
- 大ボケ
- 神ボケ
これらを選択すると、最新の票数記録の文字の色が変わります。必ず先に票数を記録し、その後に色を変えるようにしてください。これも入力用のセルが空欄になれば完了です。
ノーマル＝黒、トップ＝オレンジ、大ボケ＝赤、神ボケ＝紫です。あくまで見た目の問題で、特に色を付けたら何か集計されるとかはありません。
記録を元に戻す
入力ミスをしたり、間違えて２度登録してしまった場合などは、「１個巻き戻す」を選ぶと、最新のデータが消え、古いデータが１個ずつ上に戻されます。
連続して「１個巻き戻す」を選べばどんどん古いのがせり上がってきますが、一番古いデータ（1000回前）は消えてしまっているのでご了承ください。
なお、この方法でやり直せるのは最新データだけです。途中をいじりたい場合は後で説明します。
各シートについて
- 記録・段位判定シート
- 期ごと集計シート
- 10ボケごと集計シート
各シートは全体的にPCで見ることを意識して作っているため、スマホ画面には収まりません。
スクロールして情報確認することになりますが、ご了承ください。
記録・段位判定シート
「Records」というシートが記録・段位判定シートです。
このシートは大部分が進撃さん謹製のExcelシートから受け継いだものですが、ちょっと機能追加しています。
①入力用セル…「票数の入力」で説明した通り、このセルで項目を選択し、票数を入力することで記録が反映されていきます。
②入力用ボタン(PC専用)…入力用セルと機能は同じです。各ボタンを押すと記録が反映されます。ただスマホではうまく動作しません。（入力用セルを作ったのでもう不要かもしれない…）
③票数記録…左から２番目の列が票数の記録です。一番上が最新で、古い記録は下にスライドされていきます。過去500回分＋余分にもう500回分の記録を残しています。
数字に「トップ(オレンジ)」「大ボケ(赤)」「神ボケ(紫)」の色をつけることもできます。
④日付・時間帯記録…その票数を獲得した日、およびAM/PMを自動記録します。期ごとの集計で使用しています。
あくまでスプレッドシートで記録した日時なので、後から記録した場合などは実際の大喜利が開催された日時と一致しません。訂正したい場合は手入力でお願いします。
⑤得票回数…過去500回分で特定の票数を何回獲得したか、回数を集計しています。
⑥得票回数分布グラフ…⑤の集計を円グラフで表示したものです。
⑦過去100回分の票数推移…直近100回分の票数を折れ線グラフで表示したものです。右端が最新、左端が100回前です。
⑧得票合計・平均、現在の段位…過去500回分のボケの票数合計および平均、そしてそこから導き出される現在の段位を表示しています。
⑨昇格・降格の条件…次のボケで何票獲得したら昇段できるか、および何票以上獲得しないと降段してしまうかを表示しています。
昇段は常に可能性があるのですが（あり得ない票数なのはともかくとして）、降段は絶対しないケースもあります。降段する可能性のない場合は「-」と表示されます。
⑩今後の流出票数…今後の10ボケで「過去500回から流出する」票数を表示しています。要は491回～500回前の得票数です。昔の自分との勝負にご利用ください。
期ごと集計シート
「WeeklyResult」というシートが期ごと集計シートです。
大喜利プラスの第○○期、すなわち月曜0:00～日曜23:59までの間の記録を抽出し、期間の得票率やH得票率を表示しています。集計が残っている限り過去にさかのぼって情報を見ることもできます。
基本的に見る専門のシートになりますが、「期の番号」のとこだけは編集OKです。
①集計する期の番号…集計対象とする期の番号を入力します。入力用セルで「今期データへ」を選択することで自動計算されますが、手入力で過去の集計を見ることもできます。（※集計シートに記録が残ってる場合に限ります）
このシートで唯一の入力対象となるセルです。他のセルは全部自動計算なので、いじらないでください。
②期の開始・終了日…①のセルに入力した期の開始日、終了日が表示されます。
③スプレッドシート更新時刻…得票の記録を最後に設定した日・時間帯(AM/PM)を表示しています。あんまり深い意味はありません。
④日・時間帯別得票…期中の各曜日・時間帯(AM/PM)別にボケ数・得票数を集計し、平均を算出しています。
平均値によってセルの色が変わります。
- 2.0未満…青
- 2.0～2.5…緑
- 2.5～3.0…黄色
- 3.0以上…赤
⑤期の集計…１週間の集計を表示します。セルの色は日・時間帯別と同じです。
現在のランキングで使用されるH得票率に加え、過去のランキングで使用されていた期全体の平均得票率、さらにAM/PMごとの得票率も計算しています*4。
⑥H得票率計算用…H得票率を自動計算するためのセルです。期間内の得票率の上位40個を表示しています。
なお、詳しい方はお気づきかと思いますが、このシートは一部非表示にしてある行・列があります。集計上必要なセルなので削除はしないでください。
10ボケごと集計シート
「10Blocks」というシートが、10ボケごとに合計を集計する、いわゆる「きまぐれメソッド」のシートです。このシートも見る専門のシートです。
過去500回分まで10ボケ単位での小計を計算、グラフで表示します。また100ボケ単位での得票合計推移もグラフ化しています。
①100ボケ単位の表示…100ボケ単位で合計を出しているので、データは100個ずつの塊になっています。その表示です。
②10ボケ単位で並べ替えた過去得票数…集計シートから、10ボケを１列として得票数を並べ替えたものです。
一番上が最新で、下に行くにしたがって古いデータになってる点にご注意ください。メモ帳で記録する際には上から下へ進むについれて最新になるのが普通だと思いますが、逆になっています。
③10ボケごと集計…左１列分の合計です。当然これも上が最新、下に行くにしたがって古いデータとなります。過去のデータはスライドしていくため、この集計も変動していくことにご注意ください。
④10ボケごとグラフ…③を100ボケ単位で棒グラフ化したものです。グラフの縮尺は自動計算されるため、違う100ボケ分と長さをくらべても意味ないです。あくまでお100ボケ単位の中で浮き沈みをご確認ください。
⑤100ボケごと集計…この100ボケ単位の合計です。
⑥100ボケごと記録…100ボケごとの集計を並べて記載したものです。
⑦100ボケごと推移グラフ…100ボケ単位で得票総計の推移を折れ線グラフ化したものです。左が新しく、右が古いデータです。大まかな浮き沈みの傾向を把握できます。
⑧管理用…500ボケの総計を「チェックサム」として計算してます。これが記録・段位判定シートの合計と一致してなければ何かバグってます。
こんなときはどうするか
記録し忘れた！途中に票数を挿入したい
うっかり記録を忘れた回があったりして、最新ではない下の方のデータを修正したいことも出てくるかと思います。
これに対応する機能は用意してないので、基本的にはスプレッドシートの機能そのものを使ってコピペで対応することになります。
ただ、１つだけ気をつける必要があるのが、普通のコピペは使わないでください。
実はこのスプレッドシートには「名前付き範囲」が多数設定されており、この名前付き範囲を使って計算している処理がたくさんあります。普通のコピペをすると、この名前付き範囲が示すセル範囲が変わってしまい、計算が合わなくなってしまうことがあるのです。
こんな手順で対応できるので、参考にしてください。
- いったん記録や日付の欄を、スプレッドシートのどこか余白にコピペする。
記録・段位判定シートの右のほうの何もない辺りが便利だと思います。 - 手編集やら入力用セルの操作やらを使って、下の方のセルの値を整える。
くどいですがこの過程でコピペは使わないでください。 - 余白にとっておいたセルをコピーし、記録や日付の欄に反映させる。
この際に単純な「ペースト」ではなく、かならず「特殊貼り付け」を使ってください。
「値のみ張り付け」を選べば、数字だけが反映されます。さらにトップや大ボケなどの文字色を反映するには、もう１回「特殊貼りつけ」から「書式のみ貼り付け」を選択すれば反映されます。 - 修正が終わったら、余白に保存してあったコピペ部分を削除…といいたいところですが、しばらく放置するのをお勧めします。
慣れないうちは直し方を誤ることもあるでしょう。後から気づいたときに取り返しがつきますので。
今後について
正直、まだ動作不具合とか残ってると思ってます。β版ですので。
いちおう自分が使ってきているシートではありますが、今回公開するにあたって作り直した箇所もあり、えてしてそういった場所にバグが入ったりするものです。
とりあえず「多少不具合あっても泣かないから使ってみたい！」という人柱属性の方に使っていただき、フィードバックにご協力いただければと思っています。不具合や、機能に関する要望など。
要望はそんなに応えられないと思ってますが、意見が多ければ考慮します（期待はしないでね）、くらいに捉えてください。
あとは悩んでるのが、正式公開も含めたバージョンアップの方法なんですが…
Googleスプレッドシートってバージョンアップ版を配布するような仕組みあるんでしょうか。正直そこんとこあまり詳しくないので…
まあ最悪でも、毎回新しいスプレッドシートを各自コピーしてもらい、記録データはコピペ（特殊貼り付けで！）で移し替えてもらう、ということは可能なのですが。
Googleスプレッドシートのアプリケーションのバージョン管理に詳しい方がもしおられましたら、いい方法を教えていただけますと幸いです。
【参考】気になる人のためにちょっとだけ仕組みの話
この管理シートは、Googleスプレッドシートの持ついろんな機能を駆使して実装されています。ザックリ言えばこんな感じです。
いわゆるExcel関数
スコアの集計や平均を出すような、あるセルの値が変わると結果も変わる…程度の計算はだいたいこの方式でやってます。
唯一、GoogleスプレッドシートにはあるけどExcelにはない関数が「SORTN」関数を使ってるのがH得票率の集計欄です。
名前付き範囲
票数や日付の記録範囲をはじめ、入力セルなどはほぼ全部「名前付き範囲」で管理されています。Googleスクリプトで名前を使ってセル範囲を取り出し、各種処理を行っています。
なんで？って言うと、そうしといた方がセルのレイアウト変更したいときとかに直す箇所が少なくて済むんですよね。ちょっとした生活の知恵です。
シートに設定されている名前付き範囲は、メニューから「データ＞名前付き範囲」を選べば閲覧できます。ただし見るだけにしてくださいね。範囲設定や、名前付き範囲の名前が変わると、シートの機能が壊れてしまいます。
条件付き書式
条件付き書式は、期ごと集計シートでセルの色を得票率で変えるのに使ってます。
具体的にはそのセルを選んだ状態でメニューから「表示形式＞条件付き書式」で閲覧できます（※PC版だけ）。3.0より大きい数字にしたい！とかカスタマイズしたい方はどうぞ。実際H得票率は3.0で頭打ちにしたらほとんど意味ない気はしている。
データの入力規則
データの入力規則を設定しているのは、入力用のセルだけです。
セルを選んでメニューから「データ＞データの入力規則」を見ていただければわかるのですが、選択肢を名前付き範囲で規定することで、一覧から選択するようなインタフェースにしています。
ちなみに項目一覧は非表示にしてある「Menu」というシートに書いてあります。
Googleスクリプト
票数の記録や文字の色変えといった入力処理、期や日付の計算といった処理は、ほぼ全部Googleスクリプトに頼っています。
JavaScriptみたいな形式のプログラミング言語で、Googleスプレッドシートをはじめとした各種クラウドサービスの処理を行うことができます。
このGoogleスクリプト、正直ちょっと気になる人もいるのではないかと思ってます。Excelに埋め込める「VBAマクロ」なんかと同じで、その気になればウイルス的なこともできちゃう代物なんで…。
ていうか、本当はGoogleのクラウドデータを編集するような機能を呼び出すプログラムなら、最初に「本当にいいですか？」と許可を求められるはずなんですよね。
実際、入力用ボタンを押して記録しようとすると、本当にこのプログラムに実行許可を与えるか確認されます。こんな感じで。
ところが、入力用セルで値を選択したときはこの確認が出ず、いきなり機能が実行されるのです。正直なんでなのかよくわかってません。これもいずれ解決したい（ちゃんと許可を求めるようにしたい）ので詳しい方おられたら教えてください。
ともあれ現時点では、ぶっちゃけそんな怪しいことしてない！ということは、ひとまず私を信用してもらうしかないかなあと思ってます。ただし僕の手元からのコピーではなく二次配布されているものがあったりしたら、スクリプトが改変されてる可能性もあるわけで、正直僕の管理範囲を超えてしまいます。必ず一次ソースである上記リンクからコピーするようにしてください。
なお、Googleスクリプトはスプレッドシートに埋め込まれており、メニューから「ツール＞スクリプトエディタ」を選べば閲覧できます（※PC版だけ）ので、プログラムがわかるorわかる知り合いがいる人は、それを見てもらうのもアリかもしれません。
いろいろ書きましたが
…なんか歯切れの悪い物言いが多くなってしまいました。
まあこの手のモノを作って配る人は心配しすぎるくらいでちょうどいいと思ってるので、そんなもんだと思ってください(笑)
ともあれ、コツコツ記録をつけることが苦にならない人なら、楽しく使ってもらえること請け合いです。実際長いこと使ってる僕自身が保証します！
皆さんも是非、快適に一喜一憂できる大喜利プラスライフを。
*1:差し支えなければTwitterアカウントを記事中にご紹介したい…。ご本人からのご連絡お待ちしております
*2:いちおう入力方法は３種類あるんですが、スマホからできる方法が１個しかないのでその説明だけします。いずれ他の２つは消しちゃうかも
*3:本当は４枚です。非表示にしてる「メニュー項目一覧」シートがあります
*4:AM/PMなどという謎の欄は、時間帯によって取れる票が変わる様子を集計したかったのがきっかけで作りました。もともとは昼/夜の分類で、かつ手入力で適当に運用していたのですが、配布に際して自動計算に向いてるAM/PMに変えてしまいました。てなわけであまり意味のある集計ではないかも…何かいいアイデアください。
