スプレッドシート上でデータを見やすくするために横持ちのデータにすることはよくあると思う
ただ、横持ちのデータだとそのデータを使って集計なりフィルタなりは行いづらい
横持ちのデータを縦持ちのデータに直して集計なりなんなりの処理をしやすくして別のアウトプットをしたいというケースがあったため残しておく
参考記事がわかりやすいが他にも少し試したので記事に残しておく
イメージ
これを
こうする
ほぼ参考サイトそのまま使うことができた
変換
最終的には次のようにすることで実現できる
=ARRAYformula(split(flatten(TEXT(B1:F1, "yyyy-mm-dd")&":"&A2:A5&":"&B2:F5),":"))
- ARRAYFORMULAで各行、各列、各データの範囲を指定して各組み合わせの情報をデータ中に存在しえない文字を間に挟んで結合させる(今のケースでは
:) - 結合したデータたち1列に並べる
- 結合するときに使った特定文字列で分割する(
:)
ARRAYFORMULAを使って各値を結合する
=ARRAYformula(TEXT(B1:F1, "yyyy-mm-dd")&":"&A2:A5&":"&B2:F5)
ARRAYFORMULAが便利なのは知っていたがこういう使い方ができるのは知らなかった
縦、横、データ範囲を結合させると2023-01-01:a:101のように横のデータx縦のデータx対象のデータというようにつなげることができる
日付のデータを指定すると数字が出力されてしまうのでTEXTで文字列化してから結合させている
結合したデータたちを1列に並べる
=ARRAYformula(flatten(TEXT(B1:F1, "yyyy-mm-dd")&":"&A2:A5&":"&B2:F5))
FLATTENで縦並びにする
特定文字列で分割する
=ARRAYformula(split(flatten(TEXT(B1:F1, "yyyy-mm-dd")&":"&A2:A5&":"&B2:F5),":"))
あとはSPLITするだけ、例だと:がデリミタの役割
おわり
少なくともこのパターンの横持ちデータであれば縦持ちデータに変換できる
この方法も力技感がまぁあるが標準にある関数だけで実現できるので覚えておいて損はないと思う
他の方法だと独自にunpivot関数を定義したり、もしくはGASでコード書いたりという方法でも良かもしれない
グループ化的なものも扱えるか
色々いじってたら情報を追加しても対応できるのか?ということで試してみた
よくあるパターンだと各行のグループ的なものも扱いたいというようなパターン
試してみたができそう
セルを結合させたりしなければ大丈夫そう
結合させるとこんな感じになった
参考
Unpivot In Google Sheets With Formulas. Turn Wide Data Into Tall Data.
