[Googleスプレッドシート]複数カラムのデータを、グループ別に列に展開する
表の集計で使えるピボットテーブルですが、GoogleスプレッドシートのQuery関数のオプションで使用できるようなので試してみました。
Contents
やりたいこと
元データ
ユーザーごとに、日別に二種類のデータが入ったテーブル。(値は適当です)
| user_id | stay_time | payment_amount | date |
|---|---|---|---|
| hoge | 65 | 307 | 2019-01-01 |
| fuga | 5 | 999 | 2019-01-01 |
| piyo | 87 | 144 | 2019-01-01 |
| hoge | 55 | 307 | 2019-01-01 |
| fuga | 51 | 999 | 2019-01-01 |
| piyo | 10 | 144 | 2019-01-01 |
| hoge | 77 | 140 | 2019-01-02 |
| fuga | 67 | 128 | 2019-01-02 |
| piyo | 89 | 566 | 2019-01-02 |
| hoge | 29 | 307 | 2019-01-02 |
| fuga | 25 | 999 | 2019-01-02 |
| piyo | 82 | 144 | 2019-01-02 |
| hoge | 26 | 603 | 2019-01-03 |
| fuga | 27 | 224 | 2019-01-03 |
| piyo | 24 | 750 | 2019-01-03 |
| hoge | 93 | 603 | 2019-01-03 |
| fuga | 88 | 224 | 2019-01-03 |
| piyo | 77 | 750 | 2019-01-03 |
出力結果
日付別で列展開した表にしたい。
| _user_id | 2019-1-1 課金額合計 | 2019-1-1 滞在時間合計 | 2019-1-2 課金額合計 | 2019-1-2 滞在時間合計 | 2019-1-3 課金額合計 | 2019-1-3 滞在時間合計 |
|---|---|---|---|---|---|---|
| fuga | 1998 | 56 | 1127 | 92 | 224 | 27 |
| hoge | 614 | 120 | 447 | 106 | 603 | 26 |
| piyo | 288 | 97 | 710 | 171 | 750 | 24 |
TL;DR
以下のクエリで実現できます。
=TRANSPOSE(SORT(TRANSPOSE(QUERY(A1:D16,"SELECT A, SUM(B), SUM(C) GROUP BY A pivot D label A '_user_id', SUM(B) '滞在時間合計', SUM(C) '課金額合計'")),1,1))
詳しく解説
まずはuser_idごとに集計
=QUERY(A1:D19,"SELECT A, SUM(B), SUM(C) GROUP BY A")
結果
user_idごとのB,C列の合計値を出します。
pivotで日付ごとにグルーピングして列に展開
=QUERY($A$1:$D$19,"SELECT A, SUM(B), SUM(C) GROUP BY A pivot D")
結果
pivot 列名とつなげることで、列名でグルーピングして列に展開した表が作れます。
基本ここで終わりなのですが、やれていないことは以下:
- 列名をカスタマイズしたい
- sum 列名になるのを、合計滞在時間など任意の値にしたい。
- 列を日付順にソートしたい
日付1_列B, 日付2_列B, 日付1_列C, 日付2_列C...となっているが、日付1_列B, 日付1_列C、日付2_列B, 日付2_列C...としたい
列名をカスタマイズしたい : labelを使用
label 列名 '任意の文字列'と書くことで列名を指定できます。
=QUERY($A$1:$D$19,"SELECT A, SUM(B), SUM(C) GROUP BY A pivot D")
結果
列を日付順にソートしたい : TRANSPOSE + SORT + TRANSPOSE
ここで一気に関数が増えます。
=TRANSPOSE(SORT(TRANSPOSE(QUERY($A$2:$D$20,"SELECT A, SUM(B), SUM(C) GROUP BY A pivot D label A '_user_id', SUM(B) '滞在時間合計', SUM(C) '課金額合計'")),1,1))
結果
pivotで出力される列のソート機能は無いようなので、TRANSPOSEで行列の転置を行い、SORTで並べ替えた上で、再度TRANSPOSEという力技で解決します。
また、ただSORTするとuser_idの列の位置まで動いてしまうため、labelで_user_idと指定してSORTされても最も左に位置するようにしています。これも力技…
列名のソートは、QUERYの結果をさらにQUERYで囲い、Select Col番号で順番を指定する方法(参考)もありますが、日付が増えて列数が多くるとさすがにしんどいので今回は使いませんでした。
他に良いやり方があれば、ぜひ教えていただけると幸いです。