かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

Google SpreadSheet 複数のカラムでグルーピングしたい。ついでに複数カラムで並び替えたい

例えば都道府県別の飲食店のリストみたいなものがあって、都道府県別のカテゴリー別で店舗数を出したいみたいな時、都道府県とカテゴリーと複数のカラムでグルーピングする必要があります。

シート名店舗リスト

A B C
1 店舗名 都道府県 カテゴリー
2 店舗 A 北海道 ラーメン
3 店舗 B 群馬 そば
4 店舗 C 群馬 そば
5 店舗 D 北海道 そば
6 店舗 E 香川 うどん
7 店舗 F 北海道 ラーメン
8 店舗 G 北海道 ラーメン
9 店舗 H 群馬 うどん
10 店舗 I 北海道 うどん
11 店舗 J 群馬 ラーメン
12 店舗 K 香川 うどん

このデータから都道府県別かつカテゴリー別の店舗数を出したい

QUERY は 複数カラムで GROUP BY できる

スプレッドシートでは GROUP BY A, B のように書かけば、A 列でグルーピングしたものの中で B 列の値でグルーピングすることができます。
今回の例では、都道府県とカテゴリーとでそれぞれ GROUP BY すれば良いので次のようなクエリを書けば OK

= QUERY('店舗リスト'!A:C, "SELECT B, C, count(A) WHERE A is not null GROUP BY B, C LABEL count(A) '店舗数'",1)

↓ 結果

A B C
1 都道府県 カテゴリー 店舗数
2 北海道 うどん 1
3 北海道 そば 1
4 北海道 ラーメン 3
5 群馬 うどん 1
6 群馬 そば 2
7 群馬 ラーメン 1
8 香川 うどん 2

₍ ᐢ. ̫ .ᐢ ₎ できた!

カテゴリーの順番がバラバラで見づらいのでいい感じにソートしたい…
カテゴリーの列 ORDER BY すると都道府県の並び順が崩れてしまいます。なので、都道府県で並び替えた上で、その中でカテゴリーで並び替えたい

ORDER BY も複数カラムで実行することができる

ORDER BY {カラム1} DESC|ASC, {カラム2} DESC|ASC

と指定することで、カラム1 で並び替えたものの中を、カラム2 で並び替えることができます。

今回は都道府県 (B列) で並び替えた後に、カテゴリー (C列) で並び替えるので ORDER BY B ASC, C DESC のように書けばOK!

先程の QUERY を次のように編集します

=QUERY('店舗リスト'!A:C, "SELECT B, C, count(A) WHERE A is not null GROUP BY B, C ORDER BY B ASC, C DESC LABEL count(A) '店舗数'",1)

↓ 結果

A B C
1 都道府県 カテゴリー 店舗数
2 北海道 ラーメン 3
3 北海道 そば 1
4 北海道 うどん 1
5 群馬 ラーメン 1
6 群馬 そば 2
7 群馬 うどん 1
8 香川 うどん 2

カテゴリーの並び順が揃って見やすくなりました!
ORDER BY で指定する順番を間違えると意図しない並び方になるので注意が必要です。
カンタンですね! ( ˙꒳​˙ᐢ ) ワーイ

スプレッドシートだとある程度のことは QUERY を使うと実現することができます。ちょっと難しそうかな〜ということも QUERY のネストや今回の複数カラムでの GROUP BY や ORDER BY を使うと意外とできちゃったりするので覚えておくと少し幸せになれます!

それではハッピースプレッドシートライフを! (おれは JavaScriptを書きたい…


知見が溜まってきた!

転生したらスプレッドシートだった件…タイトルすごすぎて興味出てきた…