【GAS入門】QUERYとは？使い方とコード一覧！スプレッドシート
グーグルスプレッドシートで、
QUERYが使えるようになると、一気にできることが広がります。
ですが、グーグルの公式の説明文は、英語しかありませんので、
ざっと、query関数について説明していきます。
QUERYとは
QUERYとは、
データベース用の関数です。
・データベースから、検索条件に合ったデータを取り出す
・データベースの内容を集計する。
ことなどができます。
といっても、分かりにくいと思いますので、
早速実例を用いて説明していきたいと思います。
説明より、まず実践。サンプルデータダウンロード
説明より、まずは実践。QUERYを実際に使ってみてください！
※下記ボタンよりサンプルを複製してご利用下さい。
そのままですと閲覧のみ可能になっていますので、
ファイル→コピーを作成で、自分のスプレッドシートにコピーを作成してください。
編集可能になったら、
上部の条件ををいろいろと変更してみてください。
すると、下の表がどんどん更新されていくと思います。
なんか複雑なことをしていそうですが、実はこれが、プログラム無しで出来ます。
今回使用するデータベース
今回使用するデータベースは、
・C列～I列に記載された
・人事のデータ
となっております。
ここから、QUERY関数を用いて、色々とデータ処理を行っていきたいと思います。
| C列 | D列 | E列 | F列 | G列 | H列 | I列 |
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | TRUE |
| Dave | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | FALSE |
| Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | FALSE |
| Ben | Sales | 12:00:00 | 400 | 2002-10-10 | 32 | TRUE |
| Dana | Sales | 12:00:00 | 350 | 2004-09-08 | 25 | FALSE |
| Mike | Marketing | 13:00:00 | 800 | 2005-01-10 | 24 | TRUE |
必要な列だけ取り出す。
まずは、必要な列だけ取り出すやり方を見ていきます。
下記データは7列ですので、まだ見やすいデータですが、実際に扱うデータは、かなり大きな表です。そんな時は
「QUERY+select」
を使います。
| C列 | D列 | E列 | F列 | G列 | H列 | I列 |
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | TRUE |
| Dave | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | FALSE |
| Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | FALSE |
| Ben | Sales | 12:00:00 | 400 | 2002-10-10 | 32 | TRUE |
| Dana | Sales | 12:00:00 | 350 | 2004-09-08 | 25 | FALSE |
| Mike | Marketing | 13:00:00 | 800 | 2005-01-10 | 24 | TRUE |
例えば、下記の通り、所属(D列)と、給料(F列)だけ取り出したい場合、
=QUERY($C$4:$J$10,"select D,F",-1)
という数式を打ち込みます。
すると、その列だけが抜き出されたデータが生成されます。
| dept | salary |
| Eng | 1000 |
| Eng | 500 |
| Eng | 600 |
| Sales | 400 |
| Sales | 350 |
| Marketing | 800 |
実際の動きは下記動画をご覧ください。
表示させたいセルに数式を打ち込むと、
データが取り出されていることが分かると思います。
打ち込んでいないセルまで、データが書き出されるので、
慣れるまでは少々扱いにくいですが、数式一つで、高速でデータを拾ってくることができるので、非常に使い勝手がいいです。
特定の条件のデータを抜き出す。
次は特定の条件で抜き出す場合です。
今回は、年収600万円以上の人を抜き出してみます。
使用する関数は、
「where」
です。
whereを使うと、このデータが、
| C列 | D列 | E列 | F列 | G列 | H列 | I列 |
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | TRUE |
| Dave | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | FALSE |
| Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | FALSE |
| Ben | Sales | 12:00:00 | 400 | 2002-10-10 | 32 | TRUE |
| Dana | Sales | 12:00:00 | 350 | 2004-09-08 | 25 | FALSE |
| Mike | Marketing | 13:00:00 | 800 | 2005-01-10 | 24 | TRUE |
=QUERY($C$4:$J$10,"where F>=600",-1)
↓
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | TRUE |
| Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | FALSE |
| Mike | Marketing | 13:00:00 | 800 | 2005-01-10 | 24 | TRUE |
このようになります。
600万円以上の給料の人が検索できたことが分かります。
次は、複数検索してみましょう
| C列 | D列 | E列 | F列 | G列 | H列 | I列 |
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | TRUE |
| Dave | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | FALSE |
| Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | FALSE |
| Ben | Sales | 12:00:00 | 400 | 2002-10-10 | 32 | TRUE |
| Dana | Sales | 12:00:00 | 350 | 2004-09-08 | 25 | FALSE |
| Mike | Marketing | 13:00:00 | 800 | 2005-01-10 | 24 | TRUE |
=QUERY($C$4:$J$10,"where D != 'Eng' and date '2005-01-09' > G",-1)
↓
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| Ben | Sales | 12:00:00 | 400 | 2002-10-10 | 32 | TRUE |
| Dana | Sales | 12:00:00 | 350 | 2004-09-08 | 25 | FALSE |
上記の通り、andを使うことによって、二つの条件で検索することができます。
データを集計する。
データを集計したい場合は、下記関数を用います。
| 名前 | 説明 |
| avg() | グループの列のすべての値の平均値を返します。 |
| count() | グループの指定された列の要素数を返します。ヌルセルはカウントされません。 |
| max() | グループの列の最大値を返します。日付は古いものと比較され、文字列は大文字と小文字を区別してアルファベット順に比較されます。 |
| min() | グループの列の最小値を返します。日付は古いものと比較され、文字列は大文字と小文字を区別してアルファベット順に比較されます |
| sum() | グループの列のすべての値の合計を返します。 |
例えば、一番低い給料を求めたい場合は、
| C列 | D列 | E列 | F列 | G列 | H列 | I列 |
| name | dept | lunchTime | salary | hireDate | age | isSenior |
| John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | TRUE |
| Dave | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | FALSE |
| Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | FALSE |
| Ben | Sales | 12:00:00 | 400 | 2002-10-10 | 32 | TRUE |
| Dana | Sales | 12:00:00 | 350 | 2004-09-08 | 25 | FALSE |
| Mike | Marketing | 13:00:00 | 800 | 2005-01-10 | 24 | TRUE |
=QUERY($C$4:$J$10,"select min(F)",1)
↓F列の最低値を抽出
| min salary |
| 350 |
このようにデータを抜き出すことができます。
まとめ
このように、QUERY関数を使うことで、
簡単に必要なデータをまとめて、集計することができます。
このQUERYを用いることで、データベースも簡単に構築可能ですので、
ぜひお試しください！！
下記記事では、サンプルもあります！！
コメント欄