こんにちは、id:yone-yamaです。 この記事はSpreadsheets/Excel Advent Calendar 2018の7日目の記事です。
昨日はid:minemuracoffeeさんの転生シリーズ第三話でした。控えめに言って神回です。
Spreadsheet、最高ですよね。某ネット系企業への新卒入社から数年間のExcel傾倒時代を経て、今はすっかりSpreadsheet信者です。過去にもこんな記事を書いたりして、その尊さを讃えてしきりの日々を過ごしています。
お気に入り関数はQUERY、ARRAYFORMULA、IMPORTRANGEです。いかつい文字数の関数を使うとテンションが上がりますよね。
ところで
みなさんaikoって知ってます？
山形ライブ終わりました！皆さん、ご心配ご迷惑をおかけして本当にごめんなさい、そしてありがとうございました！どうにかライブをする事が出来ました。会場に来てくれた皆さんが最後まで連れて行ってくれました。ありがとう。ステージ降りたら怒られたで笑でもたまらなくて近くに行きたかった！！ pic.twitter.com/slmRGWE4bi
— aiko official (@aiko_dochibi) 2018年10月28日
ご覧いただいてお分かりの通り、いわゆる女神なんですけど。
今日のテーマ
この女神 a.k.a. aiko大先生が世に生み出し給うた大名曲たちしめて232曲、シュッとアレをコレしてイントロドンならぬ歌い出しドンクイズにするということをやります
1. 曲目リストを取得しましょう
まずは曲リストを用意したいです。手打ちで用意するのも愛があっていいですが、Spreadsheetである必要がなくなるのでスクレイピングをします。
ただ残念ながら、aikoオフィシャルサイトにはスクレイピングできそうなlist型ないしtable型の曲一覧がありません。 （list＝一覧。table=表。HTMLのタグによって判別します）
ついてはちょっとずっこい感じがしますが、歌詞サイト「歌net」のaiko楽曲一覧を拝借しましょう。
使うのはみんな大好きIMPORTHTML関数です。こういう使い方をするやつですね。
IMPORTHTML ("URL" , "対象となるlist or table" , "番号" )
なにはともあれsheet.newを叩いて新規スプレッドシートを作成しまして、
ついでA1にURLを入れます。
Sheet.newは最近リリースされた機能で、[docs | sheet | slide ].newをURLで叩くと新規のファイルが立ち上がるすぐれものです。便利だ。
本来であれば
IMPORTHTML("URL","table",番号)
みたいな感じでシュッとリストが取れます。が、
あれ？ページ内にtableが複数あるみたいですね。これでは一つの関数では取れなさそう。 そしてなんと2ページもある。aiko大師匠は名曲たくさんですからね。Webページなんぞには収まりきらない。致し方ない。
こういうときはQUERY関数を使います。標準的な使い方は以下。
QUERY("なんかの範囲" "Select 特定列数だけ取る Where 条件合致したやつだけ取る")
QUERY関数ちゃんの地味にすごいところは、異なる対象の配列を並べて全部取ってきてくれるところ。こんなふうに書きます。
QUERY({("なんかの範囲");("なんか別の範囲");("なんかこれまた別の範囲")})
全体を{}で囲って、範囲ごとに;で区切ってあげればあら不思議、全部の範囲をまるっと取ってきてくれます。
さらにSelect文（ここだけ取ってきてね）、Where文（この条件のときだけ取ってきてね）もしっかり効きます。
今回は１ページに6つtableが存在し、かつ各tableの行頭に見出し行が入っちゃってます。
これらをすべて範囲にして、かつ行頭が「曲名」になっている行を省きましょう。 つまり'{範囲A;範囲B}'に指定した上で、'Where<>曲名'になっていればよいわけです。
最終的にブチ込む関数はこんな感じになります。
長え。みづれえ。モバイルフレンドリーせえよ。
とまれ、これを用いて、歌netのaiko楽曲一覧ページから曲リストを取得していきます。
こうなりました。
あれ、曲名以外に「歌い出し」の欄がありますね。これは天が我に歌い出しドンをせよ、とのお告げでしょうか。でしょうかというか、そうです。
2. ドンを準備する
歌い出しドンのドン部分をつくっていきます。
まずは別シートに曲名と歌い出しの組み合わせだけを抜き出します。これもQUERY関数でシュッと。
QUERY(元シートの範囲,Select 抜き出したい列)
という感じでアレします。今回はA:Eを範囲として、E列（歌い出し） - A列（曲名）という順で抜き出したいので、QUERY(raw!A:E, "Select E,A")という記述になりました。
すると結果が以下です。
AとB列に歌い出し、曲名が出てきます。
3. ドンの出題順と選択肢をつくる
次に、元データの曲はあいうえお順に並んでいて味気ないのでランダムにします。 加えて正答と誤答が必要になるのでそれを準備します。
ここではRANDBETWEENという関数を使います。なんだかRYDEENみたいでイキフンがグッドですね。
- 発売日: 2014/11/05
- メディア: MP3 ダウンロード
RANDBETWEEN(下限値,上限値)
という、上と下を決めてその間で適当に数値を振ってくれる関数です。
RANDBETWEEN(1,COUNTA($B:$B)-1)
今回はこういう記述をしました。 下限値が1、上限値がMAXの曲数-1なのはOFFSET関数を使うための布石。後述します。 結果はこんな感じです。
4. 乱数をもとにいい感じに並べ替える
RANDBETWEENで生成した乱数は、OFFSETで特定セルの値を呼び出すのに使います。
OFFSET(開始セル,動かす行数,動かす列数,行の長さ,列の長さ)
これによって、参照セルの数値に基づいて違う位置のセルの値を返してもらえます。使い方はこんな感じ。
OFFSET(今の行 , 乱数ぶん行を動かす,,,)
※参照セルと行or列だけ指定すれば、あとの行数/列数は省略しちゃえます
先ごろのRANDBETWEENの下限値を1、上限を総行数 - 1にしたのはこれが理由で、
下限が0だと今いる行を取ってきちゃうので正答と誤答が一緒になっちゃうのでした。
かつ、上限が行数分だと一覧をはみだして空欄を返しちゃうという。
OFFSETはVLOOKUPに代表される他関数と違って「指定したスタート地点から”ｎ個分”動かす」関数なので、ここがハマりどころです。
...という感じのアレをして、乱数分勝手にずれた歌い出し文を取りました。（D列）
そこにVLOOKUPをして、取り出した歌い出し文に正しい曲目を取ってくることも忘れずに。
大量の行をさばくときは1セルに入れるだけで配列一気に持ってきてくれるARRAYFORMULAをつけてあげましょうね。（E列）
さらに、D列と全然関係ねえ曲目も取ってきましょう。
同じく、RANDBETWEENでつくった乱数（G列）を参照してOFFSETでずらした曲目を取ってきます。（F列）
結果がこちら。
こうして、D：歌い出し / E：歌い出しに対応した曲名（正答）/ F：関係ねえ曲名（誤答）の3列＝歌い出しドンのドンが取得できました。
実際のシートはこちらです。味気ない。ここからの作業で、味気をあらせていきます。
5. ようやく来たぜGAS
元となるシートがやっと出来たので、ようやく歌い出しドンのドンたるゆえん、クイズ形式に取り掛かります。
ここからはさすがにSpreadsheetだけでは出来ない範囲なので、GAS(Google App Script)を使っていきます。 僕も全然詳しくないので詳細の説明は省きますが、「JavaScriptでいい感じにSpreadSheetと他サービスをアレできるなにか」と覚えておけばよさそう。
GASみたいな言語は基本的になにやるにしても先人がいるので、Qiitaでソリューションを模索します。
探したらドンピシャな先人がおられた。
こちらを基に、というかこのスクリプトをコピペさせてもらって、クイズを作っていく形です。
「ツール > スクリプトエディタ」にこんな感じでいれます。
Google FormのIDを入れる部分があるので、そこだけ新しいFormを作成してIDをいれます。 google系のサービスはURLの中のなんかよくわかんねえ長え文字列がたいがいIDなので、獣の直感でもってIDを記載していきます。
Form側はこんな感じです。中身は空にしときましょう。
ちなみに処理が走る前にform側の設定で「テスト」をONにします。なんかよくわかんないけどこれがONになってないとエラーを吐きます。
GetRange（範囲選択）がちょっとハマりどころです。行と列の指定順序を間違えないように。今回は233行もあるので、全部クロールすると重すぎました。
GetRange[開始行 , 開始列 , 行数 , 列数]
開始を4行目/1列目に指定して、設問数となる行数は20行にしてみます。穏当な設問数ですね。
入れました。これでRUNしましょう。保存して再生ボタンを押します。
処理、進んでいそう。処理をしている間は基本暇なので、当然aikoのニューイヤームービーを貪るように見ていきましょうね。
~~
おわりました。意外と早い。なぜならaikoのニューイヤームービーを貪るように見ていたから。
終わったあとにGoogle Formの対象formを見ましょう。おおっ、ちゃんと仕上がっている！！！！
名前などもよしなにつけましょう。google formは冒頭に動画を入れたりトップ画を変えられたりするので、気持ちに応じてやったりやらなかったりします。 今回のヘッダーは僕が今年の夏を捧げたLove Like Aloha Vol.6の終演後に茅ヶ崎サザンビーチにブチ上がった花火の様子です。生きててよかった。
そんなこんなで
できました。
アルバム曲だけではなく、シングルカップリング曲ですらも無慈悲無作為に選定しているので難易度は鬼中の鬼です。 ぜひ回答してみて下さい。はたして満点を取れる限界オタクはいるかな！？！？（僕は満点でした）
というわけで
Excel/Spreadsheetsアドベントカレンダー7日目でした。ありがとうございました。ブログは受け取る人の気持ちを考える必要がなくて好きです。
次は@daaaaaaiさんの「メンテナブルExcelVBAについて書きたい気持ち」です。楽しみ。
ちなみにあと2日ぶん枠があるので、今からでも登録できます。1週間経過現在、異世界転生・番組表・ライフログ・GAS・異世界転生・aikoと実に多彩なラインナップをみせております。ぜひ。
