Query関数のオプション
Query関数を使用してデータの検索・抜き出しなどを行うことができますが、その際にオプションを利用することにより、さらに複雑なデータ分析に利用できるようになります。
Query関数の初歩については「条件に当てはまるデータを抜き出す「Query関数」の初歩」で解説していますので、併せてご覧ください。
データを昇順・降順で並び替え「order by」
Query関数で抜き出したデータを並び替えて表示させるときには「order by」オプションを利用します。
=query(元データの対象範囲,“order by 列番号 昇順か降順か“,見出しを表示させるか)
下のような表でD列の数量を基準に並び替えを行って抽出するには
=query(A12:D18,“order by D asc”,true)
のように数式を作成します。ascではなくdescとすると降順に並び替えられます。
表示させる値をグループにして集計する「group by」
上で出てきた表の中から「いちご の数量」を集計する といった場合には「group by」オプションを利用します。
=query(抽出するデータ範囲,”select 列番号, 集計用関数(集計対象の列番号) group by グループ化する列番号”)
先ほどの表から「りんご、いちご、みかん」のそれぞれの合計数量を集計するには以下のように数式を作成します。数量の合計なのでsum関数を利用します。
＊データの件数を数えるときには「count関数」を使うなどもOKです。
=QUERY(A12:D18,“select A, sum(D) group by A”)
この数式で集計するとB列の見出しが「sum数量」となってしまいました。この見出しを変更する方法はこの後で紹介します。
集計後の見出し名を変更できる「label」オプション
前項では集計したB列の見出しが「sum数量」となってしまいました。このようにならないように、また、集計後に見出しを元のものから変更したい場合などに利用できます。query関数の中にオプションとして入力します。
label 変更前の見出し ‘変更後の見出し’
「sum数量」 と表示された見出しを 「商品別数量」に変更する
=QUERY(A12:D18,“select A, sum(D) group by A label sum(D) ‘商品別合計'”)
group byをさらに細かくグループ分けできるpivot
「group by」を使用して果物名ごとに集計しましたが、今度はpivotを利用して「果物名ごとに、産地別の数量」を集計します。
＊pivot（ピボット）：「回転軸」という意味ですが、「集計の軸となる項目」と捉えるとわかりやすくなります。
=query(抽出するデータ範囲,”select 列番号, 集計用関数(集計対象の列番号) group by グループ化する列番号 pivot 条件にする列番号”)
説明だとちょっとわかりにくいので、実際に利用して動作を確認してみましょう。
=QUERY(A12:D18,“select A, sum(D) group by A pivot B”)
一列に並んでいた表を「果物名」と「産地」で見やすく抽出することができました。
上記の数式では「A12からD18の範囲を果物名ごとにグループ化して、さらに産地ごとにまとめています。
表示形式を変更するformat
集計した結果の小数点以下の桁数の表示や単位の表示、日付の表示形式の変更などを行うときにはformatを使用します。
“format 設定する列番号 ‘表示形式'”
元データのC列に「円」、D列に「個」をつけます。C列は小数点以下１桁まで表示させます（複数設定できます）。
=QUERY(A12:D18,“format C ‘0.0円’, D ‘0個'”)
他にも
数字の桁数を設定する ”format 列番号 ‘000’”
小数点以下の表示桁数を設定する ”format 列番号 ‘0.00’”
日付データの表示形式を変更する ”format 列番号 ‘yyyy年mm月dd日'”
（2018/01/01と入力されているデータを2018年01月01日に）
などを利用して表示形式を変更できます。
表示に制限をかけるlimit
Query関数を使用してデータを抽出する際に「limit」オプションを使用すると抽出するデータの数を制限することができます。
=query(抽出するデータの範囲, “limit データ数”)
order by オプションで数量の多い順に並び替え、上位4個のデータを表示する場合は以下のようになります。
=query(A12:D18, “order by D desc limit 4”)
抽出したデータから大きいものをいくつか取り出す、小さいものをいくつか取り出す、日付でデータを並べ替えて最新のものを取り出す など様々な場面に応用できます。
limitとは反対の動作をするoffset
offsetオプションを使用すると指定した数値分のデータを除外して表示させることができます。
=query(抽出する範囲, “offset 除外したいデータ数”)
limitオプションのときと同じようにデータを大きい順で並び替えて上位3個を省いた結果を表示させるには以下のような数式になります。
=query(A12:D18, “order by D desc offset 3”)
query関数のさまざまなオプションを利用してデータの集計・分析に大いに役立てましょう。
