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

こんにちは、色んな効率化が大好きなたぬ(@tanuhack)です!

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

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

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

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

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

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

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

OUERY関数の使い方

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

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

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

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

クエリ言語一覧

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

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

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

クエリ言語一覧
  1. SELECT|表示する列を選択
  2. WHERE|条件に一致する行のみを返す
  3. GROUP BY|行全体に値を集計
  4. PIVOT|列の一意の値を新しい列に展開
  5. OREDR BY|並べ替え
  6. LIMIT|表示する行数を制限
  7. OFFSET|任意の行数をスキップ
  8. LABEL|列見出しを上書き
  9. FORMAT|表示される数値を整形
  10. OPTIONS

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

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

query_sample

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

SELECT|表示する列を選択

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

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

例)B,E列だけ抽出

query_select1
クエリ言語|"SELECT B,E"

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

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

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

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

例)F列が1以上のB,F列だけ抽出

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

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

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

AND かつ(複数の条件を全て満たす場合True)
OR または(複数の条件のいずれかを満たす場合True)
query_where2
ANDの例:F列が1以上かつ4以下のB,F列だけ抽出(SELECT B,F WHERE F>0 AND F<5)
query_where2
ORの例:あくタイプのポケモンをB,C,D列だけ抽出(SELECT B,C,D WHERE C='あく' OR D='あく')
QUERY関数の6個のWHEREオプションで、データ抽出を捗らせる方法!

GROUP BY|行全体に値を集計

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

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

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

例)E列を昇順でソートして、F列を降順でソートした後、B,E,F列を抽出

query_orderby1
クエリ言語|"SELECT B,E,F ORDER BY E ASC,F DESC"

LIMIT|表示する行数を制限

抽出される行数を制限することができます。

例えば、[ORDER BY]と組み合わせれば、上位5個だけ表示させたりできます。

例)もっているポケモンの数が多い順に、上位5位までソートさせる

query_limit
クエリ言語|"SELECT B,F ORDER BY F DESC LIMIT 5"

OFFSET|任意の行数をスキップ

抽出される行数をスキップして表示させることができます。

例)表の上から10体飛ばして表示させる

query_offset
クエリ言語|"SELECT B,C,D OFFSET 10"

LABEL|列見出しを上書き

抽出される列の見出しを好きな名前で上書きすることが出来ます。

[GROUP BY]を使うと、へんな名前が勝手に付くので、[GROUP BY]を使う場合は[LABEL]もセットで使うといい感じです。

query_label1

例)sum 持ってる数を上書きする

query_label2
クエリ言語|"SELECT E,SUM(F) GROUP BY E LABEL SUM(F) '持ってる数'"

ポイントは、LABELのところをSUM(F)としてあげるところです。

FORMAT|表示される数値を整形

表示される数値(日付、時刻など)の表示形式を整えることが出来ます。

例)F列を少数第2位まで表示させる

query_format
クエリ言語|"SELECT B,F FORMAT F '0.00'"

この場合、"0.00"という表示形式を使って、少数第2位まで表示させてます。

[aside]他の表示形式も知りたい人は、過去記事でまとめているので合わせてどうぞ
【必見】スプレッドシートを共有するときはTEXT関数で表示形式を整えよう [/aside]

別のシートの表をQUERY関数で抽出する方法

query_importantrange

別のシートから値を参照するためには、『IMPORTRANGE』というスプレッドシートにしかない関数を使用します。

知っていたらドヤれる!スプレッドシート独自の関数を10個まとめてみた
構文
IMPORTRANGE("スプレッドシートキー", "範囲の文字列")

『スプレッドシートキー』というところに値を参照したいスプレッドシートのURLを入力します。

※スプレッドシートキーとは、スプレッドシートのURLを指した名称で、以下の例でいう「××××」の部分のことです。

(例:https://docs.google.com/spreadsheets/d/××××/edit#gid=0)

IMPORTRANGE関数を使う

1つだけ注意点があります。

いままで、列を選択するときはAやBなど列のアルファベットを記入していましたが、インポートする表の左からCol1、Col2のように指定する必要しなければなりません。

query_importantrange2
=QUERY(IMPORTRANGE("スプレッドシートキー","B1:F22"),"SELECT Col1,Col2",-1)

まとめ

今回は、データの集計に役立つQUERY関数を紹介しました。

いろいろクエリ言語を紹介しましたが、最低限覚えておくと便利なのは、以下の4つです。

QUERYは、スプレッドシートの中でも非常に強力な関数の一つだと思ってます。

この記事をきっかけに、少しでもQUERY関数の魅力に気づいてもらえると嬉しいです。

それでは!