今回はスプレッドシートのQUERY関数の説明です。
SQLに触れたことがある人にはお馴染みの関数だと思いますが、スプレッドシートでも使うことができます。
残念ながらエクセルにはありませんが、使い方次第ではすごい力を発揮しますので覚えておいて損はないと思います！
QUERY関数とは
QUERY関数とは、ある範囲から特定のデータのみを抽出するための関数です。
基本形：=query(データの範囲, “クエリの条件”, 見出し)
データの範囲とは、QUERY関数で抽出の対象とする列もしくはセルの範囲になります。
クエリの条件では、どんなデータを抽出したいのかを指定します。ここが一番肝心ですので、後ほど詳しく説明します。
見出しは省略可能です。
今回は以下のようなデータを例にしたいと思います。
A列に購入者、B列に商品名、C列に個数、D列に単価となります。
ポチップ
クエリの条件
クエリの条件の一番シンプルな形は、“select 範囲”となります。
“select B”で、B列を選択してみます。
複数の列を選択する場合は、”select B,C”のようにカンマで区切ります。
すると、画像のように商品名が並びます。
次はもう少し高度な条件にして、購入者が田中さんの商品名を抽出してみます。
条件式は、“select B where A=’田中'”になります。
続いて、購入者がいない商品名を抽出してみます。
先ほどの式のA=の部分を空白にします。
最後は購入者が空白でない商品名を拾う場合です。
式は、“select B where A is not null”になります。
一つ前の空白を抽出する時に、“select B where A is null“も使えます！
抽出方法のオプション
QUERY関数には抽出方法にはオプションがあります。
合計したり、平均を求めたりなど、様々な形でデータを拾うことができます。
代表的なものを下に挙げておきます。
- count：個数を数える
- sum：合計を求める
- avg：平均を求める
- max：最大値を求める
- min：最小値を求める
countを使った例を載せます。
鈴木さんが購入した商品の種類を求めてみます。
式は、“select B count(B) where A=’鈴木'”になります。
鈴木さんの購入商品はメロンとバナナなので2と表示されました。
抽出結果の表示オプション
抽出結果に対してもオプションがあります。
グループ毎にまとめたり、降べきの順に並べたりすることができます。
- group by：グループ毎にまとめる
- order by asc：小さい順に並べる
- order by desc：大きい順に並べる
今回は購入者ごとに買った商品の数（C列）の集計を取りたいと思います。
式は、“select A, sum(C) where A is not null group by A”となります。
※select 列の後のカンマを忘れないようにして下さい。
各購入者ごとの商品の個数が集計されています。
※空白行を除かない場合（where A is not nullなし）はこうなります。
空白の行が一行できてしまいます。
まとめ
冒頭でも書いた通り、QUERY関数はエクセルでは使えない関数なので知らない人も多いと思います。
スプレッドシートではデータを検出したり、引っ張ってくることが簡単にできるようになっていますので、QUERY関数以外にも色々調べてみてはいかがでしょうか。
