見出し画像

Googleフォームの回答データをQUERY関数で上手に活用しよう（１）

kirikirik

２０２２年６月、７月と二度に渡り、とあるコミュニティでQUERY関数をテーマにLT（Lightning Talk；10～20分程度の短いプレゼン）登壇の機会を頂きました。

内容は「Googleフォームの回答データを上手に活用するコツ」でしたが、少し発展させたQUERY関数を使いこなす為の具体例を紹介してみようと思います。

私が使っているQUERY関数は機能全体のごく一部ですが、非常に強力なツールで、スプレッドシートのデータ活用の際にいろいろな場面で利用しています。

どのように活用しているかパターン別に紹介していきます。

  1.  Google Forms で入力された「フォームの回答」シートに溜まっていく多くのデータをもとに、複数の別シートを作って目的別のビューを設定します。

  2.  シートを選べば即、全回答をもとにリアルタイムのデータが参照出来ます。

  3.  抽出設定も可能。表題上部の特定セルを抽出キーに設定して照会型のシートを作成し活用しています。

  4. 抽出条件は「フィルタ作成」とは異なりデータ更新アクションが不要。（filter関数ならQuery関数同様リアルタイムで結果反映されました。いっしょの様ですね。）

  5.   列の表示可否と表示順序を自由に設定出来ます。


１．フォーム回答集計で遭遇する問題

サンプルのフォームとスプレッドシート　は次の様なものです。

フォームを扱っていくと次のような問題によく遭遇します。

・データが多くなると最新回答の確認が面倒！
・ずっと右の方の列を確認したいけれど、いちいちカーソル移動が面倒！
・タイムスタンプやメルアド列は必要だけど普段は邪魔だな
・列項目の順序を入れ替えて活用しやすく整えたい
・「フィルタを作成」後に回答データが追加されても結果に反映されないのでリロードや再設定してデータ取り込み作業がめんどう

QUERY関数の使い方を少し覚えれば、これらの問題は一挙に解消します。

２．QUERY関数とは

QUERY関数とは「データ」に「条件」を指定して目的のデータを抽出する関数

Google公式ドキュメント
　QUERY
　Query Language Reference (Version 0.7)

私がQUERY関数を学んだsiteはこちらです。一歩一歩易しく説明されていてお勧めです。

https://tonari-it.com/spreadsheet-what-is-query/

技術書展13 （2022/9開催）で ノンプロ研メンバーの カワムラさんが「会社員がVLOOKUPの次に覚えるQUERY関数超入門」という書籍を出版されました。
こちらも手順が丁寧に解説されていますので大変参考になると思います。

現在、電子書籍は 技術書展13 で販売中です。
紙の書籍をご希望の方は 私の 通販サイト KiriPlayPark で購入いただけます。

さて、本題に戻りましょう。

QUERY関数の特徴
・一般的に使われるSpreadsheetの「フィルタ作成」や「シートの並び替え」は、元データが変わったら、手動で条件を設定しなおす必要がある。

・QUERY関数は、関数を入力すれば元データに変更が発生した場合も
リアルタイムで結果が反映される。

・レスポンスはどんなものか。
私の支援先小学校で昨年から使っていますので検索の基本パターンを紹介します。
児童個人を表すキーを学年・組・番号の4桁コード（以下、key4と呼ぶ）にしています。
［6312］を入力すると該当児童の明細データが表示される検索例です。
セキュリティ上のリスク軽減のため、各種実績データには児童の氏名は入れません。用途によって本人を特定する key4 またはスタミナ持久走で説明した 6桁コード（QRID）で紐づけるようにしています。

検索セルに key4 を入力すると、0.3～1秒程度（初回だけは2～3秒）で個人データが表示されます。

元データ（フォームの回答1）
　表の大きさ：2,677件 x32列
　データ容量：1.6MB
　実際の検索動画はこちら （音声解説はありません）

本日（9/29現在）3,600件 1.9MB（11/4現在）4,550件 2.2MB に増えましたが、検索レスポンスは変わらず十分実用になっています。

３．QUERY関数の書き方を さらっと解説

QUERY関数の概要をLT資料を使ってさらっと解説していきます。

QUERY関数は　データ・クエリ・見出し　3つの引数で構成されています。

① データ部　入力データを定義する
①-1 参照元シート名 は 'フォームの回答 1'!　の様に指定します。
①-2「取り扱うセル範囲」は表題を含むデータ部の 左上のセル（A1）と右端の列（H）を次のように指定します。
出力先に表題を作ってある場合など、データだけでよければデータ部の範囲を選択します。
A1:H　最後に次の SELECT句 と区切る , （カンマ）が必要です。

② SELECT句　出力列を定義する
SELECT句はクエリの最初の部分です。
データ部で定義した列名を表示順にカンマ区切りで羅列します。
データ部と同じレイアウトで表示する場合は 列名の代わりに　* （アスタリスク）１文字で代用できます。

③ WHERE句　選択・除外条件を定義する
WHERE句はデータの選択・除外条件を指定します。
① で定義した列の中から引用していきます。
フォーム回答から希望のビューを作るのによく使うのが、次の表現です。　
A IS NOT NULL ; A列の値が空白でないものを選択する
ちょっとわかりにくいですが、実際のデータだけを対象とする。という意味になります。

④ ORDER BY句　並び替え順序を定義する
ORDER BY句　は並び替え（ソート）条件を指定します。
下のサンプルでは、まず　E列を昇順に並び替え、次にA列を降順に並び替えになります。

⑤ 見出しの行数　アウトプットの表題行数を指定する
データ部で見出しを含んだ場合は出力先のデータに表題の有無や表題の行数を指定できます。

さて、今回はここまで。

次回は実用的な事例を紹介していきたいと思います。


いいなと思ったら応援しよう！