【GAS入門】QUERYとは?コード一覧!スプレッドシート

この記事は約6分で読めます。
スポンサーリンク
スポンサーリンク

【GAS入門】QUERYとは?できることとコード一覧!スプレッドシート

グーグルスプレッドシートで、
QUERYが使えるようになると、一気にできることが広がります。

ですが、グーグルの公式の説明文は、英語しかありませんので、
ざっと、query関数について説明していきます。

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

QUERYとは

QUERYとは、

データベース用の関数です。

・データベースから、検索条件に合ったデータを取り出す
・データベースの内容を集計する。

ことなどができます。
といっても、分かりにくいと思いますので、
早速実例を用いて説明していきたいと思います。

今回使用するデータベース

今回使用するデータベースは、
・C列~I列に記載された
・人事のデータ
となっております。

ここから、QUERY関数を用いて、色々とデータ処理を行っていきたいと思います。

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

必要な列だけ取り出す。

まずは、必要な列だけ取り出すやり方を見ていきます。
下記データは7列ですので、まだ見やすいデータですが、実際に扱うデータは、かなり大きな表です。そんな時は

「QUERY+select」

を使います。

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

例えば、下記の通り、所属(D列)と、給料(F列)だけ取り出したい場合、

=QUERY($C$4:$J$10,”select D,F”,-1)

という数式を打ち込みます。
すると、その列だけが抜き出されたデータが生成されます。

deptsalary
Eng1000
Eng500
Eng600
Sales400
Sales350
Marketing800

実際の動きは下記動画をご覧ください。
表示させたいセルに数式を打ち込むと、
データが取り出されていることが分かると思います。

打ち込んでいないセルまで、データが書き出されるので、
慣れるまでは少々扱いにくいですが、数式一つで、高速でデータを拾ってくることができるので、非常に使い勝手がいいです。

特定の条件のデータを抜き出す。

次は特定の条件で抜き出す場合です。

今回は、年収600万円以上の人を抜き出してみます。

使用する関数は、

「where」

です。

whereを使うと、このデータが、

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$4:$J$10,”where F>=600″,-1)

namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
SallyEng13:00:006002005-10-1030FALSE
MikeMarketing13:00:008002005-01-1024TRUE

このようになります。
600万円以上の給料の人が検索できたことが分かります。

次は、複数検索してみましょう

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$4:$J$10,”where D != ‘Eng’ and date ‘2005-01-09’ > G”,-1)

namedeptlunchTimesalaryhireDateageisSenior
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE

上記の通り、andを使うことによって、二つの条件で検索することができます。

データを集計する。

データを集計したい場合は、下記関数を用います。

名前説明
avg()グループの列のすべての値の平均値を返します。
count()グループの指定された列の要素数を返します。ヌルセルはカウントされません。
max()グループの列の最大値を返します。日付は古いものと比較され、文字列は大文字と小文字を区別してアルファベット順に比較されます。
min()グループの列の最小値を返します。日付は古いものと比較され、文字列は大文字と小文字を区別してアルファベット順に比較されます
sum()グループの列のすべての値の合計を返します。

例えば、一番低い給料を求めたい場合は、

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$4:$J$10,”select min(F)”,1)
↓F列の最低値を抽出

min salary
350

このようにデータを抜き出すことができます。

まとめ

このように、QUERY関数を使うことで、
簡単に必要なデータをまとめて、集計することができます。

このQUERYを用いることで、データベースも簡単に構築可能ですので、
ぜひお試しください!!

下記記事では、サンプルもあります!!

スポンサーリンク
スポンサーリンク
GAS google spreadsheet
スポンサーリンク
nujonoaをフォローする
nujonoa_blog

コメント欄

タイトルとURLをコピーしました