スプレッドシートのQUERY関数で、条件に一致するデータをまとめて抽出する方法

こんにちは、色んな効率化が大好きなたぬ（@tanuhack）です！

膨大なシートやデータの中から、「自分が欲しいデータだけをサクッと抽出することが出来たら、分析がもっと捗りそうなのに」と思ったことはありませんか？

持ってきたいデータが1つだけだと、VLOOKUP関数を使えば解決します。

しかし、それが2つ3つと複数だった場合、VLOOKUP関数を使って解決することはできません。

そんなとき役に立つのが、これから紹介するQUERY関数です。

少しだけ難易度高めな関数ですが、覚えておいて損はありません。

使いこなせるようになった暁には、どんなデータでも自分が思うがままに抽出できるようになれることをお約束します。

注意

注意
QUERY関数は、EXCELには用意されていません。

1 OUERY関数の使い方
2 クエリ言語一覧
3 別のシートの表をQUERY関数で抽出する方法
- 3.1 IMPORTRANGE関数を使う
4 まとめ

OUERY関数の使い方

QUERY関数の構文

QUERY(データ, クエリ, [見出し])

データ	クエリを実行するセルの範囲
クエリ	実行するクエリを『Google Visualization API』のクエリ言語で指定。後ほど詳しく解説
[見出し]	※省略可。[データ]の上部にある見出し行の番号。指定しない場合や-1と指定した場合は、[データ]の内容に基づいて推測される。

クエリの部分は"（ダブルクオーテーション）で、"クエリ"のように囲みます。

ダブルクオーテーションの中で、さらに文字列を設定する場合は、'（シングルクォーテーション）で、"クエリ '文字列' "のようにします。

ここまでで「クエリって何？」という人が続出だと思うので、次の章から、この記事の核心に触れていきますね。

クエリ言語一覧

全部で10種類存在します。（※2018年7月1日現在）

特に重要なものには、赤文字で太字。その次に重要なものには太字をつけました。

全種類覚える必要はありません。使いたいときに調べて使うことが出来るようになっていれば大丈夫です。

クエリ言語一覧

SELECT｜表示する列を選択
WHERE｜条件に一致する行のみを返す
GROUP BY｜行全体に値を集計
PIVOT｜列の一意の値を新しい列に展開
OREDR BY｜並べ替え
LIMIT｜表示する行数を制限
OFFSET｜任意の行数をスキップ
LABEL｜列見出しを上書き
FORMAT｜表示される数値を整形
OPTIONS

詳細はこちらから確認できます。全文英語なので、気になる人だけどうぞ。

理解を深めるために、この記事で使うサンプルのスプレッドシートを用意しました。

こちらから自分のスプレッドシートにコピーして使ってください。

SELECT｜表示する列を選択

表示する列を設定します。

B列だけ表示	SELECT B
B列とE列を表示	SELECT B,E
全ての列を表示	SELECT * もしくはSELECTを記述しない

例）B,E列だけ抽出

動作確認はこちらからどうぞ

WHERE｜条件に一致する行のみを返す

表示される列に条件を設定することができます。

原則として、SELECTで表示する列を決定したあとにWHEREを記述しなければいけません。

例）F列が１以上のB,F列だけ抽出

query_where1 — クエリ言語｜"SELECT B,F WHERE F>0"

動作確認はこちらからどうぞ

複数の条件を設定する場合

AND	かつ（複数の条件を全て満たす場合True）
OR	または（複数の条件のいずれかを満たす場合True）

query_where2 — ANDの例：F列が１以上かつ４以下のB,F列だけ抽出（SELECT B,F WHERE F>0 AND F<5）

QUERY関数の６個のWHEREオプションで、データ抽出を捗らせる方法！

GROUP BY｜行全体に値を集計

列の値をグループ化して、グループ内の最大値や合計値を求めることができます。

GROUP BYを使う際には、集計関数と呼ばれるものが必要です。全部で５つあります。

SUM(列)	列の合計値を表示する
COUNT(列)	列の個数を表示する
MAX(列)	列の最大値を表示する
MIN(列)	列の最小値を表示する
AVG(列)	列の平均値を表示する

GROUP BYの使い方

SELECT E,COUNT(E) GROUP BY E

例1）表の中に各生息地ごとのポケモンが何体ずついるか知りたい

query_orderby — クエリ言語｜"SELECT E,COUNT(E) GROUP BY E"

例2）表の中の各生息地ごとのポケモンを何体ずつ持っているか知りたい

query_groupby2 — クエリ言語｜"SELECT E,SUM(F) GROUP BY E"

PIVOT｜列の一意の値を新しい列に展開

注意

注意
PIVOTオプションを使う場合は、GROUP BYの集約関数を使っておく必要があります。

例えば、前述した例1）表の中に各生息地ごとのポケモンが何体ずついるか知りたいで抽出した表をさらに細分化したい場合、PIVOTを使います。

query_pivot — クエリ言語｜"SELECT E,COUNT(E) GROUP BY E PIVOT F"

[SELECT E,COUNT(E) GROUP BY E PIVOT F]の解説

まずH列に、E列の一意な値をすべて表示させ、そのI列以降にF列の一意の値をすべて展開します。

そして、『アローラ0が0、アローラ1が4、アローラ2が0…』のようにクロス集計させた表が完成します。

挫折不可避ですがPIVOTは、めったに使うことはないと思うので「こんなのもあるんだ」くらいの認識で大丈夫です。

ORDER BY｜並べ替え

抽出されるデータを任意の列単位でソートできます。

E列を昇順でソート	ORDER BY E ASC
E列を降順でソート	ORDER BY E DESC
E列を昇順でソートして、F列を降順でソート	ORDER BY E ASC,F DESC