スプレッドシートでしか使えない関数はいくつかありますが、代表的なのはなんと言っても「QUERY関数」です。
SQLライクにデータの集計・分析ができるQUERY関数は、スプレッドシートにおけるデータ周りの業務を大きく改善してくれます。
当記事では、
「QUERY関数って？何ができるの？」
「どうやったら使えるの？」
という方向けに、
- QUERY関数の基礎知識
- QUERY関数の構文について
- QUERY関数の関連記事
について解説します。
QUERY関数の基礎知識
まずはQUERY関数の基礎知識からお話します。
QUERY関数は表計算ソフトの中でGoogleスプレッドシートでしか使うことのできない「特別な関数」です。
QUERY関数は以下の公式で構成されています。
=QUERY（範囲,"select 出力データ列 where 条件式 group by グルーピング列 order by ソート条件 limit 出力データ数 offset 開始行 label 列名")
QUERY関数はDB操作に使われるSQL文をベースに作られています。
QUERY関数を使えばデータのフィルタリングやソート、グルーピングなどが驚くほど簡単にできるようになります。
QUERY関数の構文
QUERY関数の構文は以下8つの要素に分解して考えると理解しやすいです。
- 範囲の指定・・・参照するデータの範囲
- select文・・・出力データ列の指定
- where句・・・フィルタリング
- group by句・・・グルーピング、集計
- order by句・・・ソート
- limit句・・・データ量の指定
- offset句・・・繰り下げ行の指定
- label句・・・列名の指定
それぞれの要素について解説します。
範囲の指定
QUERY関数で参照する「データの範囲」を指定する要素です。
この要素で参照していないところにあるデータは、どう頑張っても引っ張ってくることはできません。
列や行を追加すると、気づいたら指定範囲が足りなくなっていることなどあるので注意しましょう。
別のスプレッドシートファイルからデータを参照したい場合は「IMPORTRANGE関数」を使えば引っ張ってくることができます。
複数のシートからデータを引っ張ってきて結合したい場合は、データ範囲を以下のように指定することで、複数シートを結合して1つのシートとしてQUERY関数を使用することができます。
- 縦の結合：{範囲A;範囲B;範囲C…}
- 横の結合：{範囲A,範囲B,範囲C…}
複数範囲・複数シートの結合方法については下記の記事で詳しく解説しています。
select文：出力列の指定
select句では「出力するデータ列」ができます。
例えば、以下のようなデータセットがあるとします。
このデータセットに対して、以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select C,A")
以下のような結果が得られます。
select句で指定列が、指定した順番どおりに出力されていることがわかりますね。
このようにselect文では、指定したデータ範囲内で「どの列のデータを出力するか」ということを指定することができます。
where句：フィルタリング
where句では「データのフィルタリング」を指定します。
例えば、以下のようなデータセットがあるとします。
このデータセットに対して、以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select * where C='果物'")
以下のような結果が得られます。
種別が「果物」だけにフィルタリングして出力されていることが分かります。
where句については下記の記事で詳しく解説しています。
group by句：グルーピング・集計
group by句では「データのグルーピング・集計」ができます。
例えば、以下のようなデータセットがあるとします。
このデータセットに対して、以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select C,AVG(B) group by C")
以下のような結果が得られます。
種別ごとにグルーピングされて、各種別の平均単価が計算されていることが分かります。
group by句については下記の記事で詳しく解説しています。
order by句：ソート
order by句では「データのソート」ができます。
例えば、以下のようなデータセットがあるとします。
このデータセットに対して、以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select * order by B desc")
以下のような結果が得られます。
単価が高い順（降順）にソートされていることが分かります。ちなみに、安い順（昇順）にソートしたい場合は、「desc」の部分を「asc」に書き換えればOKです。
order by句については下記の記事で詳しく解説しています。
limit句：出力量の制限
limit句では「データの出力量」を指定できます。
例えば、以下のようなデータセットがあるとします。
このデータセットに対して、以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select * offset 5")
以下のような結果が得られます。
データの出力量が5行に制限されていることが分かります。
limit句については下記の記事で詳しく解説しています。
offset句：行の繰り下げ
offset句では「出力データの開始行」を指定できます。
例えば、以下のようなデータセットがあるとします。
このデータセットに対して、以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select * offset 5")
以下のような結果が得られます。
データの開始行が5行繰り下げられていることが分かります。
offset句については下記の記事で詳しく解説しています。
label句：列名の変更
label句では「出力データの列名を変更」することができます。
もともとのデータセットでは「単価」という列名だったとしても、label句で「価格」と指定すれば、出力データでは「価格」と表示されるようになります。
先ほどのデータセットを再び用意して、
以下のQUERY関数を実行すると、
=QUERY(A1:C11,"select A,B label B '価格'")
以下のような結果が得られます。
列の先頭に記述される列名が「単価」→「価格」へと変わりました。
label句はgroup by句などを利用したときに「sum 単価」などにも適用することができるので、見た目を気にする人はlabel句を使って「sum 単価」→「売上」などに変更してみてください。
まとめ
今回はQUERY関数の使い方について解説しました。
QUERY関数は他の関数と比べてとても多機能な関数です。初めは苦労するかもしれませんが、慣れてきたら業務でとても役立つので習得することをおすすめします。
では、また。
QUERY関数の解説本【kindle unlimitedで無料】
