Googleフォームで集めたデータを授業で活用する方法【QUERY】

以前こんなことをつぶやきました。

Googleフォームの単元テストによる各生徒のデータが溜まってきた。
この段階での課題が、生徒ごとに上手くデータを纏められないか。
その解決策がGoogleスプレッドシートの
QUERY関数とIMPORTRANGE関数であることに気づき、遊び中。
クラス番号入力とorder byを組み合わせれば検索しやすい。
感動。

— こうち＠中学理科 (@kouchi_scientek) December 18, 2021

　昨年は、フォームで単元テストをつくり、自主的にテスト勉強する活用までは行ってきました。
　課題としては、学びのログを残すことができ、こちらで確認できるのはいいのですが、この生データのままだと、誰がどれだけできたのか探すのが大変なこと。
　今年はここから１ステップ進み、このデータを上手く活用し、更に生徒の学びや、我々の負担の削減に活用できないかというところにチャレンジしています。

様々なクラスで使うとごっちゃごちゃ、毎回手作業でソートをかけるのがめんどくさい。
結果について個人をすぐに特定できず、アドバイスができなかったり、
提出状況を確認しづらいのが課題でした。

結論

　まずは結論だけ述べると
①フォームのスプレッドシートを開く
②シートを追加する
③シートにクエリ関数を入れ、組と番号を指定する
④昇順、降順ソートをかける
だけで、かなりデータを活用しやすくなりました。※1つの単元テストの場合です。
　関数にすると下のような感じです。
=query(data!A:F,"select * where A is not null and C=1 or D=0 order by B desc　,D asc")
　自分の場合、単元テストではまず組と番号を答えさせるので、C列（組）とD列（番号）を指定するとかなり絞ることができるのです。

4,5番の子は満点合格できていないぞと確認ができ、すぐにサポートすることができます。

自分好みにカスタマイズ

　そして現状はこれを楽に使えるようにカスタマイズし、関数を直接いじるのではなく、数値を入力するセルを入れる形の式にしました。不格好ですが、自分が使いやすくわかれば良いので。
　関数にすると下のような感じです。
=query(data9!A:F,"select * where A is not null and C="&B2&""&B5&" D="&B3&" order by "&B4&" "&B6&" ,D asc")

　さらに、これだと真面目な生徒は何度も満点をとっており重複して見づらかったので、=UNIQUEを加えてあげることにより、更に見やすくなりました。

発展させると

　これを学期、1年分とデータを重ねていくと、個人の膨大な学習ログを残すことができ、評価に繋げられるのではないかと構想中です。
それぞれの単元テストのスプレッドシートをひとまとめにするのがIMPORTRANGE関数です。
　もはや関数を見ると頭が痛くなってきますが、単元テスト８と単元テスト９をつなげ、１組の１番の子と指定をしてあげれば、下の図のような感じで個々の１年間のログを表示することができます。
　関数にすると下のような感じです。
=QUERY({QUERY(IMPORTRANGE("単元テスト８のアドレス", "data8!A1:G"), "select *"); QUERY(IMPORTRANGE("単元テスト９のアドレス","data9!A:G"), "select *")}, "where Col3=1 and Col4 =1 Order by Col2 desc ,Col4 asc")

　中学では今年度から新学習指導要領がスタートしました。１０ヶ月目に入り、見えてきた課題は、持続可能な指導と評価の一体化。これがテーマかなと。
　その一助となるのが、ICTであり、Googleスプレッドシートの関数なのかなと思います。またなにか面白い機能を発見して便利だなと思うことはnoteにまとめていきます。
　教育関係者の負担を減らし、生徒たちの指導支援に力を注げられればという願いを込めて。