QUERYで並べ替えとグループ化を行う

絞り込みを使うと目的の行を表示できますが、実際のレポートには順序と合計も必要です。QUERYでは、SQL形式のORDER BY句とGROUP BY句を追加して、この2つにも対応できます。

これらを使えば、最も売れている地域はどこか、取引を金額の大きい順に並べるといった質問にも、1つの数式で答えられます。

ORDER BY句では、1列または複数列を基準に結果を並べ替えます。WHEREがある場合は、その後ろに置きます。

デフォルトでは昇順（小さい順、AからZ）に並べ替えます。この数式では、すべての行を売上の少ない順に表示します。

列の後ろにDESCを追加すると、大きい順に並べ替えられます。昇順を明示したい場合はASCを使います。

売上金額の大きいものが先に表示されるため、成績上位者の一覧に最適です。LIMITと組み合わせれば、すっきりした上位3件の一覧を作成できます。

ORDER BYに複数の列をカンマ区切りで指定すると、同順位の行をさらに並べ替えられます。Sheetsは最初の列で並べ替えた後、値が同じ行について次の列を使います。

ここでは、結果を地域名のアルファベット順に並べ、各地域内では売上金額の大きい順に表示します。

GROUP BYを使うと、同じ値を持つ行を1つの集計行にまとめられます。カテゴリごとの合計を作成するための機能です。

使うには、SELECTでグループ化する列と、別の列に適用するSUM、COUNT、AVGなどの集計関数を組み合わせます。

この数式では、地域ごとの売上を合計します。SUM(D)でSales列を合計し、GROUP BY Aで地域ごとに1行を作成します。

結果は小さなピボットテーブルのようになります。1つの関数だけで、Eastの合計とWestの合計を表示できます。

COUNTに置き換えると、合計ではなく行数を数えられます。これにより、地域ごとに成立した取引の件数がわかります。

COUNT(D)を使うと、空でないSalesセルを数えられるため、グループごとの行数をすぐに確認できます。

各グループの平均値を求めるにはAVGを使います。ここでは、地域ごとの取引金額の平均を求めます。

集計関数は組み合わせることもできます。同じクエリでSUM(D)とAVG(D)を両方選択すれば、合計と平均を横に並べて表示できます。

よくあるエラーがあります。SELECT内で集計関数に含まれていない各列は、GROUP BYにも指定しなければなりません。

SELECT A, B, SUM(D) GROUP BY Aが失敗するのは、Bが集計もグループ化もされていないためです。AとBの両方でグループ化するか、SELECTの列一覧からBを削除してください。

句を組み合わせて、集計結果を順位付けできます。グループ化した後、集計値を基準に並べ替えると、最大のグループを先頭に表示できます。

ここでは、各地域の売上合計を合計の大きい順に表示します。そのままランキングとして使える結果です。

グループ化した列のヘッダーは、sum Salesのように見づらくなります。LABEL句を追加すると、レポート向けにわかりやすい名前へ変更できます。

ここでは、合計列の名前をTotal Salesに変更します。ラベルのテキストには、絞り込みの値と同じようにシングルクォートを使います。

並べ替えとグループ化を理解できているか確認しましょう。

これでQUERYの結果を思いどおりに整形できるようになりました。

• ORDER BY col [ASC|DESC]で並べ替えます。追加の列を指定すれば同順位も並べ替えられます
• GROUP BYとSUM、COUNT、AVGを組み合わせると、カテゴリごとの集計を作成できます
• 選択した集計対象外の列は、GROUP BYにも指定する必要があります
• LABELで集計列のヘッダー名を変更できます

これで、並べ替え、グループ化、ラベル付けを1つの数式で行えるレポートが完成します。