Google Forms から名簿への出力 -未回答者を一網打尽に-

希望調査をとるときに、 Google Forms はとても便利である。対象者全員から希望をとるのに、大量の紙を印刷したり、それを人力で集計したりという手間が省ける。

本校で使っているシステムの中では、スタディサプリにもアンケート機能があるのだが、これはいまいち使い勝手がよくない（個人の感想）。自動で集計してくれるものの、全校生徒に一斉に配信するな、って言われてるし、配信したらしたで、未回答の生徒を探すのがちょっとめんどくさい。だったらクラス単位で配信すればいいのだろうが、それも設定するのがめんどくさかったりする。

そんなわけで、Google 教の信者（？）である私は、 Google Forms と Google Spreadsheet の関数でなんとかしてしまおう、という発想にたどり着くわけです。

で、実際にやってみましょう。

まずは、適当にフォームを作成しました。実際に回答できるようになっていますので、適当に回答してみてもらっても大丈夫です。

適当に回答した結果が、Spreadsheet に出力されるのが集計しやすくていいです。スタディサプリはその時点での回答状況を MS Excel ファイルに出力するだけなので、リアルタイムでの集計には不向きです。

で、回答されると、こちらのファイルに出力されます。先程の Forms と同様に、こちらも編集できるようになっていますので、適当に回答を削除して、前述の Forms から回答をしてみて、どのように変化するかを確かめてもらって大丈夫です。

この出力されたファイルから、（本来はログインしているメールアドレスで個人を確認して）個人毎の集計をとります。その結果がこちらのファイルになります。これで回答を集め、更に未回答者も一目で分かる状態になりました。

ところが、回答をしているのが生徒だからか、深く考えずに回答しているのか、後から回答を変えたい、と言ってくる場合があります。その場合、再度回答したものが反映されればいいのですが、 「vlookup」という関数は一致するものを「上から」順に探すものであり、 Google Forms の回答はどんどん「下に」追加されていきます。なので、単純に考えれば、昔の回答を削除すればいいのですが、再回答したことを知らなければ、そのまま古い回答が表示されたままになってしまい・・・

それを”関数のみで”解決する方法があります。

それは、「sort」関数を使って、回答を逆順に並べ替えたデータから探すというもの。実際に作ってみたデータはこちら。

このファイルの解説をしていくと・・・

まずは「シート3」に、「importrange」で回答を引っ張ってきている。同じファイルの中で作業をするのであれば、これは必要ない。

次に「シート2」で、逆順に並べ替えている。

=sort('シート3'!A:C,row('シート3'!A:A),false)

最初の「'シート3'!A:C」は、並べ替える範囲を定義している。

「row」は、行番号を返す関数であるので、「'シート3'!A:A」のそれぞれのせるの行番号を表している。

最後の「false」は「降順」を表している。

つまり、「'シート3'!A:C」を「'シート3'!A:A」の行番号が大きい順に並べろ、という指示をしている。行番号はもちろん上から小さい順に並んでいるので、大きい順に並べたら最初と逆順になるのである。

こうすることで、Forms で回答される度に上に回答が増えていくので、あとは通常通りに「vlookup」で拾うことで最新の回答に上書きされる、という仕組みである。