見出し画像

Google スプレッドシートでQUERY関数を利用したデータ抽出

ともかつのノート

12/9 と 12/16 に GEG Hiroshima のオンラインイベントで、「Google スプレッドシート」について取り上げられていて、そこで学んだ QUERY 関数について少しまとめておきます。
この QUERY 関数が使えるようになれば、「Google フォーム」の回答を抽出・整形できます。

  • この記事は、「はてなブログ」で 2021/12/20 に投稿したものを移転させたものです。note に移転する際に、細かな部分で変更してある部分もあります。

はじめに

Microsoft Excel には、抽出するだけの FILTER 関数であれば実装されていますが、QUERY 関数は実装されていません。　※FILTER 関数は、「Google スプレッドシート」でも利用できます。

QUERY 関数と同様の機能を、Microsoft Excel は Power Query という機能で実現しています。Power Query については、最後に QUERY 関数と少し比較します。

使用したサンプルデータ

用意したサンプルデータ

使用例

　QUERY 関数の使用例を、以下の (1) ～ (3) で例示します。これらの例が理解できれば、いろいろな応用ができると思います。

(1) AB型の男性を若い順に表示

　シート「AB型の男性を若い順」では、シート「元データ」の内容から「AB型」「男性」が抽出され、「生年月日」の降順（すなわち、若い順）に表示されています。

AB型の男性を若い順に表示

上図のように抽出・表示するために、セル A1 には次のように QUERY 関数が入力されています。　※QUERY 関数を入力するのは、セル A1 でなくても構いません。

=QUERY('元データ'!A:K,"select * where F='男' and G='AB' order by E desc")

  • QUERY 関数の第一引数は、元データとなるデータを指定します。

  • QUERY 関数の第二引数は、抽出・表示の条件を指定。文字列として指定するので、二重引用符（ " ）で囲みます。

  • 文字列中の select * は、表示の対象となるデータがすべて（ * ）であることを意味します。

  • 文字列中の where 以降は、抽出の条件になります。F='男' となっているので、元データの列 F が男性（ ’男’ ）であるものを抽出することを意味します。　※二重引用符内の文字列なので引用符（ ' ）で囲みます。

  • 続けて and G='AB' と指定されているので、 抽出する条件に列 G が AB型（ 'AB' ）であることを追加しています。

  • order by E desc は、表示する順序を列 E（生年月日）の降順（desc）に指定することを意味します。

上記のような 1行の関数を入力するだけで、元データから抽出・並び替えを行ったデータが表示できました。

(2) 北陸三県の女性

シート「北陸三県の女性」では、シート「元データ」の内容から「住所」が北陸三県（富山県、石川県、福井県）の「女性」が抽出され、「生年月日」の昇順に表示されています。ただし、表示されているのは「氏名（列 B）」「ひらがな（列 C）」「年齢（列 D）」「住所（列 K）」「生年月日（列 E）」だけです。

北陸三県の女性
=QUERY('元データ'!A:K,"select B,C,D,K,E where F='女' and (K like '富山県%' or K like '石川県%' or K like '福井県%') order by E asc")

  • select B,C,D,K,E と指定されていることで、表示の対象となるデータと、表示順を指定しています。B,C,D,K,E と指定することで、「氏名（列 B）」「ひらがな（列 C）」「年齢（列 D）」「住所（列 K）」「生年月日（列 E）」だけが、指定された順に表示されています。

  • where F='女' と指定されていることで、元データの列 F が女性（ ’女’ ）であるものを抽出しています。

  • and ( ) と指定されていることで、where の条件に加えて指定する条件を指定しています。and の両側の条件を満たすデータを抽出します。

  • K like '富山県%' と指定されていることで、抽出する条件として、住所（列 K）が「富山県」で始まっているものを指定します。　※like '富山県%' と指定することで、'富山県' ではじまるものが抽出される。% がワイルドカードを意味します。

  • 続けて or K like '石川県%' or K like '福井県%' と指定されていることで、抽出する条件として、住所（列 K）が「石川県」「福井県」で始まっているものを追加で指定しています。これらの条件が or で連結されているので、いずれかの条件を満たすものが抽出されます。

  • order by E asc と指定されていることで、表示する順序を列 E（生年月日）の昇順（asc）で表示します。　※生年月日の昇順は、年齢が高い順

　このように検索条件に and や or を組み合わせ、元データとは異なる順番で必要なデータだけを表示できます。

(3) 指定した日付のデータ

シート「指定した日付のデータ」では、シート「元データ」の内容から「タイムスタンプ」が指定した日付（2021/12/08）を抽出し、「タイムスタンプ」の昇順に表示されています。ただし、表示されているのは「タイムスタンプ（列 A）」「氏名（列 B）」「ひらがな（列 C）」「年齢（列 D）」「住所（列 K）」「生年月日（列 E）」だけです。

指定した日付のデータ
=QUERY('元データ'!A:K,"select A,B,C,D,K,E where A contains date '2021-12-08' order by A asc")

  • select A,B,C,D,K,E と指定されていることで、表示の対象となるデータと表示順を指定しています。

  • where A contains date '2021-12-08' と指定されていることで、元データの列 A に指定日付（2021/12/08）であるものを抽出しています。　※日付データとして取り扱うために、date '2021-12-08' と指定しています。この場合には、/ ではなく - で年月日を区切っているところに注意！

  • order by A asc と指定されていることで、表示する順序を列 A（タイムスタンプ）の昇順（asc）に指定しています。

　扱いづらそうな日時のデータについても、上記のように抽出可能です。また、以下のように記述することで、TODAY 関数と TEXT 関数を組み合わせ、当日のデータだけを抽出可能です。

=QUERY('元データ'!A:K,"select A,B,C,D,K,E where A contains date '"&text(today(),"yyyy-mm-dd")&"' order by A asc")

QUERY 関数を利用するアイディア

QUERY 関数の利用方法として、

  • 「Google フォーム」の回答から、特定の回答や当日の回答だけを抽出する。

  • 抽出する条件ごとにシートを作成しておき、利用するケースに応じてシートを使い分ける。

というものが考えられますが、

  • QUERY 関数の第二引数となる文字列を、セルに入力された値から生成することで、抽出する条件を変化させる。

といった利用も面白いかもしれません。

QUERY 関数とPower Query

Power Query は、似たような名称で同様の機能を実現するものではあるものの、QUERY 関数とはまるで異なるものです。

上述の使用例を Power Query でも行ってみて、以下のように感じました。

  • Power Query の方が、より「ノーコード」という感じがする。
    しかし、すべてを画面操作で設定する分だけ、操作量が多くなってしまう。また、作成されたクエリーを見ても何をしているのか一見してわかり難い。

  • Power Query の方が、より高度な処理が行えそうな気がした。

  • QUERY 関数の方が、作成した内容の使いまわしが簡単に行えそう。

まとめ

「Google スプレッドシート」にしても、Microsoft Excel にしても、QUERY 関数や Power Query を利用することで、シート内のデータを抽出して表示できるのは便利！

この記事が気に入ったらサポートをしてみませんか？