Googleフォームの回答データをQUERY関数で上手に活用しよう（３）
前回の記事はこちらです。
１．支援先小学校でのQUERY関数活用方法
QUERY関数は知っているだけでは必要な時になかなか使いづらいものです。
少しでも構文エラーが有れば訳のわからないエラー攻めとなり、一人ではめげてしまいがちです。
私は最初、コピペや写経をしながら自分なりの成功事例を一か所にまとめていつでも参照できるようにしてきました。
（１）フォーム回答データを新しいものから順に並べてみる
今回は支援先の小学校でのQUERY関数活用事例を紹介したいと思います。
一番活用例が多いのは、前回説明した「フォーム回答を新しい回答から古いものへと逆順に表示する」というものです。最近はフォーム作成時一緒に参照用シートを組み込むようにしています。
解説は前回の記事を参照してください。
（２）key4（学年+組+出席番号）で個人データを検索する
次によく使うのが key4 と呼ぶ４桁の（年＋組＋出席番号）で検索し該当児童の明細データを表示する照会型のシートビューです。
セル Ｂ１ に key4 1409 と入力すると シート "フォームの回答1" に蓄積されたデータの中から1年4組9番の該当児童のデータを検索して表示されます。
このシートでは年組番号の情報を key4 と呼ぶ項目にひとまとめに表示するので、 年・組・出席番号 の個別表示は省略しました。
（３）key4の設定にArrayFormula関数を使う
key4 で検索できるようにするために、 シート "フォームの回答1" の学年、組、番号 の項目を連結して Ｉ列に key4 を設定します。セル I1 に見出し"key4" と入力し、セル I2 に次のようなArrayFormula関数を使ってC,D,E列を連結しました。
=arrayformula((C2:C)&(D2:D)&(E2:E))ArrayFormula関数を作るコツをご紹介します。
先ず、I2セルに次の様に文字列連結の関数を入力すると、セル I2 だけに４桁の年組番号が表示されます。
= C2 & D2 & E2
ArrayFormula は スプレッドシートだけで使える優れた関数で、データの先頭行に下記のルールで入力するだけで、最下部まで自動的に反映されます。
ArrayFormula関数の書き方はセル指定 C2 の後ろに :列名 C を続けて、C2:C の様に表現します。以下、D2, E2 も次のように変更します。
=ARRAYFORMULA(
C2:C
& D2:D
& E2:E )
= ARRAYFORMULA(C2:C & D2:D & E2:E)今度はセル I2 に関数を入れてEnter を押したとたんに最下部まで key4 の値が表示されたと思います。
key4 の検索準備が出来ましたので、シート key4で検索 に戻ります。
1行目には検索キーの key4 をに入力する場所を確保しますので、QUERY関数は セル A2 に入力します。
QUERY関数は次のようになります。
=QUERY('フォームの回答 1'!A1:I, "SELECT A,I,F,G,H WHERE I = '"&B1&"' ORDER BY I ASC, A ASC")フォームの回答1 の入力データ範囲は新たに key4 の列が増えたので A1:I となります。
表示する列と表示順を SELECT句 で次のように指定しました。
SELECT A,I,F,G,H1列目 Ａ：タイムスタンプ
2列目 Ｉ：Key4
3列目 Ｆ：名前
4列目 Ｇ：質問１の答え
5列目 Ｈ：質問２の答え
検索対象を WHERE句 で指定します。
WHERE I = '"&B1&"'I = '"&B1&"'
&B1& をダブルクォーテーションで囲み、更にその外側をシングルクォーテーションで囲みます。
検索対象データが複数存在すれば該当する一覧データが表示されます。
私の支援先小学校では 全校key4マスター の隣のシートに key4 検索画面を設けており、名簿一覧表全体を目視参照することは少なくなりました。
今回はここまで。
お勧め情報
ノンプロ研 カワムラさんが書かれたQuery関数の解説本は丁寧に解説されておりお勧めです。
「会社員がVLOOKUPの次に覚えるQUERY関数超入門」 カワムラシンヤ著
電子版はこちらからどうぞ。
紙の本は私のECサイトで販売しています。