例えば都道府県別の飲食店のリストみたいなものがあって、都道府県別のカテゴリー別で店舗数を出したいみたいな時、都道府県とカテゴリーと複数のカラムでグルーピングする必要があります。
シート名店舗リスト
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を書きたい…
- GAS SpreadSheet 複数のシートのデータをまとめて表示したい - かもメモ
- GAS SpreadSheet IMPORTRANGE で 外部シートを読み込もうとして 循環依存が検出されました。なエラーにハマる - かもメモ
- Google SpreadSheet UTF-8 な CSV をスプレッドシートで開いたら日本語が文字化けするときのTIPS - かもメモ
- GAS SpreadSheet range 「Exception: 範囲の行数には 1 以上を指定してください。」にハマる - かもメモ
- Google スプレッドシート 自動的に生年月日の列から年齢を計算したい - かもメモ
- Googleスプレッドシート 他のシートから特定のセルを条件付きで取ってきて表示させたい - かもメモ
- Googleスプレッドシート QUERY関数の条件(WHERE)での空白セルの扱い方 - かもメモ
- Google スプレッドシート nヵ月前の日付をチェックしたい - かもメモ
- Googleスプレッドシート 月末までの日付で検索した一覧を取得したい - かもメモ
知見が溜まってきた!
転生したらスプレッドシートだった件…タイトルすごすぎて興味出てきた…